[JDBC/MySQL Persistence] Updated connector to version 8.0

[dominikbenner]

Is backwards compatible to V5

[9037568]

Has this change received any community testing? @lewie can you review?

[dominikbenner]

It is the addition to #5699 Currently i removed the v5 driver and load the v8 driver manually

Openhab Thread

[lewie]

@s4ndst0rm, @9037568,

Works very well, tested with connector mysql-connector-java-8.0.13 and mysql ver. 5.7.17:

08:15:47.359 [INFO ] [del.core.internal.ModelRepositoryImpl] - Loading model 'jdbc.persist'
08:15:50.390 [DEBUG] [org.openhab.persistence.jdbc         ] - BundleEvent STARTING - org.openhab.persistence.jdbc
08:15:50.394 [DEBUG] [org.openhab.persistence.jdbc         ] - ServiceEvent REGISTERED - {org.osgi.service.cm.ManagedService}={service.id=384, service.bundleid=221, service.scope=singleton} - org.openhab.persistence.jdbc
08:15:50.403 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::setItemRegistry
08:15:50.406 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::activate: persistence service activated
08:15:50.408 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::updateConfig
08:15:50.413 [DEBUG] [tence.jdbc.internal.JdbcConfiguration] - JDBC::JdbcConfiguration
08:15:50.414 [DEBUG] [tence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: configuration size = 11
08:15:50.418 [DEBUG] [tence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: user=lewie
08:15:50.421 [DEBUG] [tence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: password exists? true
08:15:50.423 [DEBUG] [tence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: url=jdbc:mysql://192.168.0.181:3306/ItemTypeTest4
08:15:50.425 [DEBUG] [tence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: found serviceName = 'mysql'
08:15:50.426 [DEBUG] [tence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: Init Data Access Object Class: 'org.openhab.persistence.jdbc.db.JdbcMysqlDAO'
08:15:50.438 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::initSqlTypes: Initialize the type array
08:15:50.440 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::initSqlQueries: 'JdbcMysqlDAO'
08:15:50.442 [DEBUG] [nhab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initSqlTypes: Initialize the type array
08:15:50.444 [DEBUG] [nhab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initSqlQueries: 'JdbcMysqlDAO'
08:15:50.446 [DEBUG] [tence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: dBDAO ClassName=org.openhab.persistence.jdbc.db.JdbcMysqlDAO
08:15:50.449 [DEBUG] [tence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: numberDecimalcount=1
08:15:50.451 [DEBUG] [tence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: tableUseRealItemNames=false
08:15:50.451 [DEBUG] [tence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: enableLogTime false
08:15:50.470 [DEBUG] [tence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: load JDBC-driverClass was successful: 'com.mysql.jdbc.Driver'
08:15:50.475 [DEBUG] [tence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: configuration complete. service=jdbc
08:15:50.476 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB
08:15:50.477 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection isDriverAvailable: true
08:15:50.479 [INFO ] [.persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
Thu Dec 20 08:15:50 CET 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'.
 You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
08:15:51.026 [DEBUG] [tence.jdbc.internal.JdbcConfiguration] - JDBC::setDbConnected true
08:15:51.027 [DEBUG] [nhab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initAfterFirstDbConnection: Initializing step, after db is connected.
08:15:51.083 [DEBUG] [hab.persistence.jdbc.utils.DbMetaData] - dbMajorVersion = '5'
08:15:51.084 [DEBUG] [hab.persistence.jdbc.utils.DbMetaData] - dbMinorVersion = '7'
08:15:51.085 [DEBUG] [hab.persistence.jdbc.utils.DbMetaData] - driverMajorVersion = '5'
08:15:51.086 [DEBUG] [hab.persistence.jdbc.utils.DbMetaData] - driverMinorVersion = '1'
08:15:51.086 [DEBUG] [hab.persistence.jdbc.utils.DbMetaData] - dbProductName = 'MySQL'
08:15:51.087 [DEBUG] [hab.persistence.jdbc.utils.DbMetaData] - dbProductVersion = '5.7.17'
08:15:51.087 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB asking db for name as absolutely first db action, after connection is established.
08:15:51.171 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::checkDBAcessability, first try connection: true
08:15:51.173 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::createItemsTableIfNot
08:15:51.173 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doCreateItemsTableIfNot sql=CREATE TABLE IF NOT EXISTS items (ItemId INT NOT NULL AUTO_INCREMENT,itemname VARCHAR(500) NOT NULL,PRIMARY KEY (ItemId))
08:15:51.175 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::getItemIDTableNames
08:15:51.176 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doGetItemIDTableNames sql=SELECT itemid, itemname FROM items
08:15:51.188 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service.
08:15:51.189 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::updateConfig: configuration complete for service=jdbc.
08:15:51.240 [DEBUG] [org.openhab.persistence.jdbc         ] - BundleEvent STARTED - org.openhab.persistence.jdbc
initCount = 1
testSwitch.state = NULL
08:16:00.149 [INFO ] [smarthome.event.ItemStateChangedEvent] - testSwitch changed from NULL to OFF
08:16:00.156 [INFO ] [smarthome.event.ItemStateChangedEvent] - testItem changed from NULL to 1
08:16:00.160 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=testSwitch (Type=SwitchItem, State=OFF)
08:16:00.169 [DEBUG] [openhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=item0002; newTableName=null;
08:16:00.170 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType SWITCHITEM for Item testSwitch
08:16:00.171 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'testSwitch' as Type 'SWITCHITEM' in 'item0002' with state 'OFF'
08:16:00.172 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: 'OFF'
08:16:00.173 [DEBUG] [openhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO setValueTypes dbType=VARCHAR(6); javaType=class java.lang.String;
08:16:00.174 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: other: item.getState().toString(): 'OFF'
08:16:00.175 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO item0002 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='OFF'
08:16:00.203 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'testSwitch' as 'OFF' in SQL database at Thu Dec 20 08:16:00 CET 2018 in 54 ms.
08:16:00.216 [DEBUG] [.persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=testItem (Type=NumberItem, State=1)
08:16:00.218 [DEBUG] [openhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=item0001; newTableName=null;
08:16:00.219 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType NUMBERITEM for Item testItem
08:16:00.221 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'testItem' as Type 'NUMBERITEM' in 'item0001' with state '1'
08:16:00.221 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: '1'
08:16:00.222 [DEBUG] [openhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO setValueTypes dbType=DOUBLE; javaType=class java.lang.Double;
08:16:00.238 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: newVal.doubleValue: '1.0'
08:16:00.242 [DEBUG] [enhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO item0001 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='1.0'
08:16:00.254 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'testItem' as '1' in SQL database at Thu Dec 20 08:16:00 CET 2018 in 38 ms.

[9037568]

@s4ndst0rm can you fix the pom.xml conflicts?

[dominikbenner]

@9037568 Done.

[9037568]

Thanks to @lewie for the review. Thanks to @s4ndst0rm for the update!