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

Posted Nov 03, 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

Discussions

Comments are currently turned off

Latest Discussions

  • Carlos: Thank you!
    May 16, 2014 | Replies (0)
  • Jessica: Great info, thanks!
    May 15, 2014 | Replies (1)
  • Walter: Thanks, you totally saved me a bunch of searching around just now. I was setting up a percona xtradb...
    Feb 25, 2014 | Replies (0)
  • Avi: I've found the reason of the problem in my case. It's a bug as described here :http://bugs.mysql.com/bug.php?id=68892Shortly:...
    Dec 12, 2013 | Replies (0)
  • Avi: I've also met that behavior twice on 5.6.12 slaves. However, i didn't find the root cause of that....
    Dec 12, 2013 | Replies (0)