Q: MySQL SQLException: Cannot convert value '0000-00-00 00:00:00' to TIMESTAMP.
A: The stacktrace is
Caused by: java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 3 to TIMESTAMP.
at com.mysql.jdbc.ResultSet.getTimestampFromString(ResultSet.java:6369)
at com.mysql.jdbc.ResultSet.getTimestampInternal(ResultSet.java:6394)
at com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.java:2268)
... 40 more
By default, the TIMESTAMP column is defaulted to the current time when it is not set explictly or set to null.
However, if the TIMESTAMP column is allowed NULL and defaulted to '0000-00-00 00:00:00', then JDBC driver
(JConnector) 3.1 or later will throw an exception when converting this value to a timestamp.
To fix this problem, first do (for pre-version 6.0 of Bugzero)
mysql> desc reminder;
and make sure you have something like
| remind_ts | timestamp | NO | | CURRENT_TIMESTAMP | |
Then, do
mysql> update reminder set remind_ts=null where remind_ts='0000-00-00 00:00:00';
This shall set the value to the current time.
For version Bugzero 6 and after, do
mysql> desc person;
and if you have
| ts | timestamp | NO | | CURRENT_TIMESTAMP | |
Then, do
mysql> update person set ts=null where ts='0000-00-00 00:00:00';
Another option is, if you are using JConnector 3.1.4 or later, to add a parameter
zeroDateTimeBehavior=convertToNull
in the jdbc url.
(This exception shall not happen for JConnector 3.0 or earlier, or Bugzero v6.5 or later).
* Reference brought to you by
Bugzero, it's more than just defect tracking software!
|
Home -
FAQs
|
|