After update to 2.4.0 stable I get jdbc driver class is deprecated

Add-ons Persistence Services
1

Hi,

just updated to 2.4.0 stable from milestone 8 where all was working very well, although persistence of zwave parameters such as power consumption doesn’t work as well. I now receive the following error:

“Loading class com.mysql.jdbc.Driver'. This is deprecated. The new driver class iscom.mysql.cj.jdbc.Driver’. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.”

I am running openhab 2.4.0 stable on a Mac with Mojave OS X and mySQL as the persistence DB.

Any thoughts?

thanks and regards,

Dave.

2

Deprecation warnings are just warnings. It is notification that the class being used may go away in the future at some point. There is nothing you can do to fix this. It needs to be fixed in the code. IIRC there is already a PR submitted to address this. Ignore it for now but if it really bugs you update to the first milestone build that gets released in about a month.

3

Thanks Rich,

I think I omitted a “small” but very important point: OpenHab can’t reach the DB at all. Downgrade back to Milestone 8 and the problem goes away.

14:20:41.385 [WARN ] [ysql.internal.MysqlPersistenceService] - mySQL: No connection to database. Cannot persist item 'light_study (Type=DimmerItem, State=100)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:0

14:20:41.388 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://localhost:3306/OpenHAB, user=openhab2

More information on this, I really needed to read the reams of console messages more carefully, it seems there is an issue with the timezone being passed. I have no idea what the difference is between milestone 8 and stable.

java.sql.SQLException: The server time zone value 'AEDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

I’ll try looking more closely at timezone configs in both OpenHab and mySQL, although mySQL has not changed.

Kind Regards,

David.

4

I’m not super knowledgeable about timezones but usually they are listed as country/city instead of the acronym like it is shown there. For example, the acronym for my current timezone is MST but as far as OH and Linux is concerned my timezone is America/Denver. Maybe something along those lines is happening. Beyond that I’ve no ideas.

5

Thanks again Rich,

I’ve confirmed my time zone is set correctly in OH:

19:49:23.619 [INFO ] [e.core.internal.i18n.I18nProviderImpl] - Time zone set to 'Australia/Melbourne'.

and that AEDT is a valid designation: “Australian Eastern Daylight Time”. It is also the same settings as configured in OH 2.4.0 Milestone 8. I just did an in-place update to get to 2.4.0 stable, so all configs should have transferred across.

I am currently using mySQL Persistence add-in, should I try the “JDBC Persistence MySQL” add-in instead?

It’s looking more like a code issue than a config issue, but I am no expert. I cannot find any discrepancies in the configs between M8 and stable. I’ll pop an issue in GitHub as well and see what response I get there.

kind regards,

David.

6

I did not get this message, I use mariadb jdbc-connection.

7

I’ve seen a number of PRs and issues on the MySQL / JDBC code in the last few days. Perhaps this is a known issue. I don’t use JDBC so I’m not going to be of much more help.

8

Have the same problem after my upgrade today (release 2.3.0->2.4.0). MySql persistence have worked for years with OH.

“2018-12-22 19:06:11.417 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://127.0.0.1/openhab, user=openhab
java.sql.SQLException: The server time zone value ‘Västeuropa, normaltid’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.”

9

looks like that your OH2 system is pulling the TZ info from your O/S which is set to something else (Swedish obviously) than en_US language and it goes crazy

try the following:
set within $OPENHAB_CONF/services/runtime.cfg the following (change to adapt to your system):

##################### LOCALE ####################

org.eclipse.smarthome.core.i18nprovider:language=en
org.eclipse.smarthome.core.i18nprovider:region=GR
org.eclipse.smarthome.core.i18nprovider:measurementSystem=SI
org.eclipse.smarthome.core.i18nprovider:timezone=Europe/Athens
org.eclipse.smarthome.core.i18nprovider:location=xx.xxx,yy.yyy,zz.zzz
10

I have already tried that,:roll_eyes: No success.
As I understand the error message it complains about the Sql server time zone value to be wrong or…?

11

Actually… it looks like you are right… the error is referring to the remote (mySQL Server) TZ setting.

can you modify the mySQL Server TZ to something else?

12

Unfortunately I don’t know how to change the SQL server time zone. According to the error message it may be possible to “configure … the JDBC driver (via the serverTimezone configuration property)” but I don’t know how to that either.

13

You may be able to fix this in the JDBC URL/connectionstring:

jdbc:mysql://localhost/db?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
1 Like
14

In my case, the MySQL system variable “time_zone” is set to “SYSTEM”, therefore the MySQL instance is using the OS timezone setting. On my system (OS X - Mojave), this is set to AEST (Australian Eastern Standard Time).
OH (2.4.0 stable) is somehow translating “Australia/Melbourne” to AEDT (Australian Eastern Daylight Time), which is a valid timezone value however this fails.
OH (2.4.0 M8) does not fail - does anyone know how to see/view the parameters passed from OH to MySQL via jdbc? I’d like to review what the difference between M8 and stable.

15

I put that string into the jdbc.cfg file, is it correct? Anyway, it didn’t solved the problem

16

On a suggestion from Helmut on my github issue, I’ve switched over to jdbc for MySQL binding. I just left the database ‘as-was’ and configured the jdbc driver to be backwards compatible. I did have to disable SSL in the command parameters:

url=jdbc:mysql://localhost:3306/OpenHAB?useSSL=false

All seems to be working now. It is a very strange thing indeed that it worked in M8 and not stable, and I do not like leaving this unanswered - however the fix, at this stage, is to move to the jdbc binding for MySQL and not use the MySQL Persistence binding.

Hope this helps.

17

I was able to keep using the MySQL persistence binding by importing the timezone tables into the MySQL server, then adjusting the DB timezone to match that of OH.

Look here for the info on importing timezone tables.

then edit /etc/mysql/my.cnf and add:

[mysqld]
default-time-zone=‘Australia/Melbourne’

and restart.

18

Thanks Jeremy, well done. I will probably not revert to MySQL binding now but thank you for posting a solution to the issue. I’m still a little confused as to what changed between 2.4 Milestone 8 (which works) and 2.4 stable, which doesn’t!

Thanks again, Dave.

19

No worries. I tried to migrate to JDBC binding, but was facing other errors then, hence I’m sticking with MySQL for now.

20

I just upgraded from 2.4 Snapshot 1407 to 2.5 Snapshot 1511 and found that the jdbc mysql persistence configuration was the most breaking change of all. For whatever reason you now need a timezone configuration (which was not needed for years…).
In a Windows environment it was pretty hard to get it back running as you need to populate the time_zone tables in mysql with a downloadable script. As maybe others users may encounter the same trouble I just would like to share the solution with you. The script can be found here:

https://dev.mysql.com/downloads/timezones.html

After downloading you have to execute it, but NOT in the Openhab DB environment but on the global mysql level. After the tables have been populated with the TimeZoneData I also modified my database URL and added the Timezone to

url=jdbc:mysql://127.0.0.1:3306/OpenHAB?useSSL=false&serverTimezone=Europe/Berlin

After that, the jdbc mysql persistence also worked with Snapshot 1511.

1 Like