Cannot convert value '0000-00-00 00:00:00' ...

[ngervasi]

On two different servoy servers, both using MySQL 5.0 as backend I'm sporadically seeing this log entry:

Code: Select all

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 3 to TIMESTAMP.
       at com.mysql.jdbc.ResultSet.getTimestampFromBytes(ResultSet.java:6886)
       at com.mysql.jdbc.ResultSet.getTimestampInternal(ResultSet.java:6921)
       at com.mysql.jdbc.ResultSet.getTimestamp(ResultSet.java:6245)
       at org.apache.commons.dbcp.DelegatingResultSet.getTimestamp(DelegatingResultSet.java:150)
       at org.apache.commons.dbcp.DelegatingResultSet.getTimestamp(DelegatingResultSet.java:150)
       at org.apache.commons.dbcp.DelegatingResultSet.getTimestamp(DelegatingResultSet.java:150)
       at com.servoy.j2db.dataprocessing.BufferedDataSet.<init>(Unknown Source)
       at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
       at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(Unknown Source)
       at sun.reflect.GeneratedMethodAccessor226.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:585)
       at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:294)
       at sun.rmi.transport.Transport$1.run(Transport.java:153)
       at java.security.AccessController.doPrivileged(Native Method)
       at sun.rmi.transport.Transport.serviceCall(Transport.java:149)
       at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:466)
       at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:707)
       at java.lang.Thread.run(Thread.java:595)

I can't understand what is originating this entry because everything seems to work fine. It happens only when using webclient.

Anyone?

[rgansevles]

Nicola,

This seems to be a database issue.
Did you search the mysql forums? It looks like a known upgrade issue.

Rob

[JoHaN]

You should check your mask in the mysql database on that field. It's not corresponding with the value you'd tried to enter. If you know the used mask you can make your value correspond with it.

[ngervasi]

It's weird, there are no problems in the entered data, just the entry in the log.
The column that (I guess) is generating the log entry is the third on this table and is defined like all my other datetime columns:

Code: Select all

+---------------------+-------------+------+-----+---------+-------+
| Field               | Type        | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+-------+
| ddtid               | int(11)     | NO   | PRI | 0       |       |
| timestamp_ddt       | varchar(50) | YES  |     | NULL    |       |
| data_ddt            | datetime    | YES  |     | NULL    |       |

Looking at the MySQL forums there are threads about this error but they states that the problem arise only with the 3.1+ jdbc driver and datetime columns set with the '0000-00-00 00:00:00' as default value; I scanned all my tables and the only column like that is this one:

Code: Select all

+--------------------------+--------------+------+-----+---------------------+-------+
| Field                    | Type         | Null | Key | Default             | Extra |
+--------------------------+--------------+------+-----+---------------------+-------+
| articoli_id              | int(11)      | NO   | PRI | 0                   |       |
| articoli_timestamp       | datetime     | NO   |     | 0000-00-00 00:00:00 |       |

But it's not "column 3" as stated in the error, it should be column 2 or am I missing something?
Maybe the "3" refers to the third column selected in the query and not the third column in the table?
Anyway I changed the column to use NULL as default value and will continue to monitor the server.

[ngervasi]

I leave this note for future reference.
I think I solved the problem, the column was indeed the third column of my "ddt" table but it wasn't a structure problem but a data one:

Code: Select all

mysql> select data_ddt, ddtid from ddt where data_ddt='0000-00-00 00:00:00';
+---------------------+-------+
| data_ddt            | ddtid |
+---------------------+-------+
| 0000-00-00 00:00:00 |  4365 |
| 0000-00-00 00:00:00 |  5512 |
+---------------------+-------+
2 rows in set (0.00 sec)

It was not a MySQL issue but a Servoy one: I don't know in which circumstances Servoy wrote "0000-00-00 00:00:00" to the data_ddt column and newer jdbc drivers for MySQL are less tolerant than before and require a NULL value.
There is a way to configure the driver to not throw an exception (documented on MySQL forum) but I preferred to NULL those two records and stick to the standard driver configuration.

One question arise now: what is the expected behavior when a user leaves a datetime field blank in servoy? I think it should write a NULL value to the DB but how did it manage to write a "0000-00-00 00:00:00" value?

[ROCLASI]

Hi Nicola,

My guess it was the old driver.
MySQL is notorious for accepting invalid dates without throwing an error (hey, try the 30th of february...).
I guess they made it more strict.

[ngervasi]

Yes Robert, but it was Servoy that inserted the bad date...

[ROCLASI]

Are you sure?

Code: Select all

var d = new Date('0000-00-00 00:00:00');
application.output(d);
--> Thu Jan 01 01:00:00 CET 1970

Looks to me that if Servoy indeed passed a value of the type Date it would be something like that.

Anyway, I still would go for the driver being the culprit. Maybe translating null to '0000-00-00 00:00:00'.

[ngervasi]

Maybe you're right.
Anyway now I'm using the new driver and I've corrected those two records so I think everything is back to normal.
See you in Amsterdam for the usual late night drink sessions...