Random creation of records...

Classic Servoy
1

After upgrading to MySQL 4.1.14-standard I have a very strange problem (probably isn’t really related to the upgrade…). On all forms linked to the table obj_bp I get 16 empty records upon entering my solution (also after coming back from Layout mode).
As soon as I enter any of these forms I get the error “could not load record”

java.sql.SQLException: Value ‘0000-00-00’ can not be represented as java.sql.Timestamp Value ‘0000-00-00’ can not be represented as java.sql.Timestamp

These records, however cannot be found in the database. I can delete them and the solution works until it’s loaded the next time (and again it’s the 16 empty records…).
Debug mode didn’t help. I don’t know where these records could be created…
I’m using com.mysql.jdbc.Driver, but also tried com.mysql.jdbc.NonRegisteringDriver and org.gjt.mm.mysql.Driver.

Thanks for any suggestions,
Reto

PS: Using Servoy 2.1.2 (same on 2.2) on OS X

2

I still don’t know why the records were created, but I found the solution to my problem :D

When upgrading MySQL, ODBC,… I encountered the following behaviour:

Datetimes with all-zero components (‘0000-00-00 …’) can not be represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being read from a ResultSet. Connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards. This behavior can be modified using the ’ zeroDateTimeBehavior ’ configuration property.

In the Preferences I had to modify the Database Server URL for my MySQL connection to “jdbc:mysql://localhost/dap2000?zeroDateTimeBehavior=convertToNull”.

Now it works perfectly and no “ghost records” are created anymore…