View previous topic :: View next topic |
Author |
Message |
benst Captain
Joined: May 25, 2004 Posts: 72 Location: The Netherlands
|
Posted: Tue Oct 27, 2009 5:15 am Post subject: Error message when opening mysql database |
|
Hi,
Using NeoOffice 3.0.1p0 with mysql-connector-java-5.1.10:
I have some MySQL tables with "0000-00-00 00:00:00" as TIMESTAMP, and when opening these tables in Base I get the following error: "cannot convert value '0000-00-00 00:00:00' from column to TIMESTAMP", and it opens an empty document.
From googling this error message, it seems that setting zeroDateTimeBehavior=convertToNull in the connection string might fix this, but I have no idea how to do this.
Thanks for your help,
Ben |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
Posted: Tue Oct 27, 2009 8:11 am Post subject: |
|
I have some MySQL databases on my Mac OS X 10.4.11 machine so I will see if I can reproduce the problem that you are seeing. If I can, I will then investigate how to get "zeroDateTimeBehavior=convertToNull" in the connection string.
I will post again soon with any results that I get.
Patrick |
|
Back to top |
|
|
benst Captain
Joined: May 25, 2004 Posts: 72 Location: The Netherlands
|
Posted: Tue Oct 27, 2009 8:51 am Post subject: |
|
Thanks for looking into it.
For your information, the db I'm talking to is MySQL 5.1.39 on a FreeBSD box.
Ben |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
Posted: Tue Oct 27, 2009 9:02 am Post subject: |
|
OK. After I installed mysql-connector-java-5.1.10, I get the same error as you. Fortunately, adding "zeroDateTimeBehavior=convertToNull" to the connection string fixed the problem for me.
Here are the steps that I used to add "zeroDateTimeBehavior=convertToNull" to the connection string. Can you tell us if these steps work for you?:
1. Open your .odb file in NeoOffice and select the Edit :: Database :: Properties menu
2. In the Database Properties dialog that appears, add a "?" character onto the end of the text in the "Datasource URL" field. Please note, however, that if any character other than the last character in the text is already a "?", then add a "&" character.
3. Add "zeroDateTimeBehavior=convertToNull" onto the end of the text in the "Datasource URL" field and press the OK button.
4. Close and reopen your .odb file to force NeoOffice to reconnect to the database with the new Datasource URL.
Patrick |
|
Back to top |
|
|
benst Captain
Joined: May 25, 2004 Posts: 72 Location: The Netherlands
|
Posted: Wed Oct 28, 2009 4:29 am Post subject: |
|
Hmm, I must be missing something, because I don't see the datasource url field. I have two tabs in the database properties dialog: Advanced Properties (with name of the MySQL database and user name fields) and Additional Settings (with Host name, Port number, MySQL JDBC driver class, and char set fields).
Ben |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
Posted: Wed Oct 28, 2009 9:32 am Post subject: |
|
I know why you are not seeing the Datasource URL field: you setup your database using the "MySQL (JDBC)" connection type instead of the "JDBC" connection type. The "MySQL (JDBC)" connection type is really the same as the "JDBC" connection type but it has custom properties dialogs to make setup more streamlined.
So, in your case, the steps are different. Here are the steps that should work for databases with the "MySQL (JDBC)" connection type. Do these steps work for you?:
1. Open the .odb file and select the Edit :: Database :: Properties menu.
2. In the dialog that appears, click on the Advanced Properties tab, append "?zeroDateTimeBehavior=convertToNull" onto the end of the "Name of the MySQL database" field, and click the OK button.
3. Save the database and reopen it.
Patrick |
|
Back to top |
|
|
benst Captain
Joined: May 25, 2004 Posts: 72 Location: The Netherlands
|
Posted: Wed Oct 28, 2009 9:52 am Post subject: |
|
Thanks Patrick, that works.
Many thanks for the quick response, much appreciated.
Best regards,
Ben |
|
Back to top |
|
|
|