MySQL and Colfusion - Cannot convert value '0000-00-00 00:00:00' from column 6 to TIMESTAMP

Nov 3, 2006
I was upgrading the MySQL java connector from 3.0.17 to mysql-connector-java-5.0.4-bin.jar on my Coldfusion server. When I tried running a script I received this error:

Cannot convert value '0000-00-00 00:00:00' from column 6 to TIMESTAMP


I had this problem connecting to both MySQL 4.1.15 and MySQL 5.0.24a.

After a quick search, I found this in the MySQL Bugs Database
http://bugs.mysql.com/bug.php?id=18308

To fix it, I added the 2 connection strings they mentioned, to the JDBC URL in the MySQL Administrator like this:

jdbc:mysql://xxx.xxx.xxx.xxx:[PORT]/[Databasename]?noDatetimeStringSync=true&zeroDateTimeBehavior=convertToNull


After submitting the changes, I reloaded my CF page and it started working again.

I found a bunch of other connection options you can use on this page, that I might mess around with at a later time:
http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html

Comments

New Comment