Recently there is a reported issue regarding with the MySQL database that throws the SQL exception of converting values to timestamp:


(SQL Exception while getting value: (Cannot convert value '0000-00-00 00:00:00' from column 15 to TIMESTAMP.))


At the beginning, I’m not sure what is the problem with the conversion and I really have no idea why there is an exception being thrown about the conversion to timestamp. My first thought will be the user entering any invalid data type that will crash the system or the database.

After a further research, I found out that this error related to MySQL JDBC Driver. What’s wrong with the JDBC driver? It’s being reported in MySQL for the usage of Connector/J version 3.1.8. What I’m trying to suggest to the customer is to upgrade the MySQL Connector version 3.1.12 or 5.1 by replacing the old JDBC driver and check whether the problem still persists.

Fortunately, I provided another alternative solution to him since I’m not confirm whether JDBC replacement can really resolve the problem. He told me that the driver upgrade doesn’t really solve his current problem but the alternative solution able to resolve the errors that appeared in the log file before this.

Another alternative solution that I suggested is to include the ‘zeroDateTimeBehavior=convertToNull’ in the JDBC driver parameter:


jdbc:mysql://localhost/test?zeroDateTimeBehavior=convertToNull


As usual, I will make a further research of the causes and why the solution is needed to be that way. Honestly, I don’t like to give a solution to the customers today and forget what I had suggested to them in the next day and also without any further understanding what is the root cause of the problem as well as why the solution should be in such way. I don’t like to make it as a template answer to match with the suitable question.

I found a useful documentation from MySQL that discussed about configuration properties for Connector/J and it’s not a bug. There will be an exception since version 3.1.4. Ahha! That explains why the customer mentioned that the driver upgrade doesn’t work. The reason is no matter he upgrades to which version, this “exception” will keeps occurring until he includes the magic word “zeroDateTimeBehavior”. This is to enable the driver to handle the DATETIME values that consist of zeroes. You can either set the driver to handle it as an exception, round the value or convert the value to null. Maybe you will have a better understanding when you read this documentation that explains the usage of ‘zeroDateTimeBehavior’:
http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html

If you are interested in replicating this problem, feel free to do this by referring to the following reporting page:
http://bugs.mysql.com/bug.php?id=10717