JDBC Mysql persistence in OH3

mhilbush · December 10, 2020, 11:04pm

Is anyone successfully using jdbc-mysql persistence in OH3? I’m trying to configure OH3 to access a mysql server on a remote host, and I can’t get it to work. I keep getting this in my log. I’ve confirmed permissions are correct on the DB side, and that I can connect to the database remotely from the openHAB host using the command line.

2020-12-10 17:56:51.918 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::store:  No connection to database. Cannot persist item 'XXXXXXX (Type=DateTimeItem, State=2020-12-10T17:56:52.000-0500, Label=null, Category=clock)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:1

I’ve installed the jdbc-mysql addon.

My jdbc.cfg looks like this.

# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
url=jdbc:mysql://remote.host.name:3306/openhab3?serverTimezone=America/New_York

# the database user
user=myUser

# the database password
password=myPassword

# the reconnection counter
#reconnectCnt=1

# the connection timeout (in seconds)
#waitTimeout=

tableUseRealItemNames=true

mhilbush · December 11, 2020, 12:23pm

So I put the jdbc persistence bundle in debug mode. Now I’m even more confused. Everything appears to work all the way up to the last line where it fails to store the item.

2020-12-10 17:27:57.676 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::activate: persistence service activated
2020-12-10 17:27:57.680 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::updateConfig
2020-12-10 17:27:57.708 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::JdbcConfiguration
2020-12-10 17:27:57.710 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: configuration size = 14
2020-12-10 17:27:57.715 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: user=openhab
2020-12-10 17:27:57.717 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: password exists? true
2020-12-10 17:27:57.719 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: url=jdbc:mysql://my.host.address:3306/openhab3?serverTimezone=America/New_York
2020-12-10 17:27:57.721 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: found serviceName = 'mysql'
2020-12-10 17:27:57.723 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: Init Data Access Object Class: 'org.openhab.persistence.jdbc.db.JdbcMysqlDAO'
2020-12-10 17:27:57.737 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::initSqlTypes: Initialize the type array
2020-12-10 17:27:57.740 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::initSqlQueries: 'JdbcMysqlDAO'
2020-12-10 17:27:57.748 [DEBUG] [hab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initSqlTypes: Initialize the type array
2020-12-10 17:27:57.761 [DEBUG] [hab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initSqlQueries: 'JdbcMysqlDAO'
2020-12-10 17:27:57.763 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: dBDAO ClassName=org.openhab.persistence.jdbc.db.JdbcMysqlDAO
2020-12-10 17:27:57.780 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: errReconnectThreshold=1
2020-12-10 17:27:57.782 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: tableNamePrefix=Item
2020-12-10 17:27:57.783 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: tableUseRealItemNames=true
2020-12-10 17:27:57.803 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: tableIdDigitCount=0
2020-12-10 17:27:57.804 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: enableLogTime false
2020-12-10 17:27:57.969 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: load JDBC-driverClass was successful: 'com.mysql.jdbc.Driver'
2020-12-10 17:27:57.971 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::updateConfig: configuration complete. service=jdbc
2020-12-10 17:27:57.973 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB
2020-12-10 17:27:57.975 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection isDriverAvailable: true
2020-12-10 17:27:57.985 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource
2020-12-10 17:27:58.192 [WARN ] [.zaxxer.hikari.util.DriverDataSource] - Registered driver with driverClassName=com.mysql.jdbc.Driver was not found, trying direct instantiation.
2020-12-10 17:28:00.070 [DEBUG] [ence.jdbc.internal.JdbcConfiguration] - JDBC::setDbConnected true
2020-12-10 17:28:00.072 [DEBUG] [hab.persistence.jdbc.db.JdbcMysqlDAO] - JDBC::initAfterFirstDbConnection: Initializing step, after db is connected.
2020-12-10 17:28:00.284 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbMajorVersion = '5'
2020-12-10 17:28:00.286 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbMinorVersion = '7'
2020-12-10 17:28:00.298 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - driverMajorVersion = '8'
2020-12-10 17:28:00.300 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - driverMinorVersion = '0'
2020-12-10 17:28:00.302 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbProductName = 'MySQL'
2020-12-10 17:28:00.304 [DEBUG] [ab.persistence.jdbc.utils.DbMetaData] - dbProductVersion = '5.7.32-0ubuntu0.18.04.1'
2020-12-10 17:28:00.307 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB asking db for name as absolutely first db action, after connection is established.
2020-12-10 17:28:00.656 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::checkDBAcessability, first try connection: true
2020-12-10 17:28:00.658 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service.
2020-12-10 17:28:00.659 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::updateConfig: configuration complete for service=jdbc.
2020-12-10 17:28:01.194 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service.
2020-12-10 17:28:01.196 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service.
2020-12-10 17:28:01.198 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::getName: returning name 'jdbc' for queryable persistence service.
2020-12-10 17:28:16.414 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::pingDB
2020-12-10 17:28:16.415 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection isDriverAvailable: true
2020-12-10 17:28:16.444 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::checkDBAcessability, first try connection: true
2020-12-10 17:28:16.450 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::store:  No connection to database. Cannot persist item 'gZmAlarm (Type=GroupItem, BaseType=SwitchItem, Members=5, State=OFF, Label=Zoneminder
 Status, Category=null)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:1

mhilbush · December 11, 2020, 12:59pm

I found the issue.

The shadow config file userdata/config/org/openhab/jdbc.config contained this line. Burned again by shadow config files… LOL

reconnectCnt="1"

So I blew away the file, and it started working. Kind of.

Log file now getting spammed with this. I guess the bundle doesn’t like Image item types and isn’t afraid to let you know it!

2020-12-11 07:39:45.133 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_DrivewayImage
2020-12-11 07:39:45.322 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_FrontDoorImage
2020-12-11 07:39:50.106 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_LivingImage
2020-12-11 07:39:50.147 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_PatioImage
2020-12-11 07:39:50.193 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_BasementImage
2020-12-11 07:39:50.942 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_KitchenImage
2020-12-11 07:39:55.214 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_DrivewayImage
2020-12-11 07:39:55.410 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_FrontDoorImage
2020-12-11 07:40:00.183 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_LivingImage
2020-12-11 07:40:00.224 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_PatioImage
2020-12-11 07:40:00.270 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_BasementImage
2020-12-11 07:40:01.005 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_KitchenImage
2020-12-11 07:40:05.320 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_DrivewayImage
2020-12-11 07:40:05.503 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for ZM_FrontDoorImage
20

It also appears to have issues with timestamps.

2020-12-11 07:36:20.093 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: Table created for item 'E2_Forecast4_DateTime' with dataType TIMESTAMP(3) in SQL database.
2020-12-11 07:36:20.094 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=e2_forecast4_datetime_0009; newTableName=null;
2020-12-11 07:36:20.095 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType DATETIMEITEM for Item E2_Forecast4_DateTime
2020-12-11 07:36:20.097 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'E2_Forecast4_DateTime' as Type 'DATETIMEITEM' in 'e2_forecast4_datetime_0009' with state '2020-12-15T00:00:00.000-0500'
2020-12-11 07:36:20.098 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: '2020-12-15T00:00:00.000-0500'
2020-12-11 07:36:20.100 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO setValueTypes dbType=TIMESTAMP(3); javaType=class java.sql.Timestamp;
2020-12-11 07:36:20.101 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: DateTimeItem: '2020-12-15 00:00:00.0'
2020-12-11 07:36:20.102 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doStoreItemValue sql=INSERT INTO e2_forecast4_datetime_0009 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? value='2020-12-15 00:00:00.0'
2020-12-11 07:36:20.109 [ERROR] [org.knowm.yank.Yank                 ] - Error in SQL query!!!
java.sql.SQLException: Table 'openhab3.e2_forecast4_datetime_0009' doesn't exist Query: INSERT INTO e2_forecast4_datetime_0009 (TIME, VALUE) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? Parameters: [2020-12-15 00:00:00.0, 2020-12-15 00:00:00.0]
        at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392) ~[bundleFile:?]
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491) ~[bundleFile:?]
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457) ~[bundleFile:?]
        at org.knowm.yank.Yank.execute(Yank.java:194) [bundleFile:?]
        at org.knowm.yank.Yank.execute(Yank.java:177) [bundleFile:?]
        at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doStoreItemValue(JdbcBaseDAO.java:327) [bundleFile:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:149) [bundleFile:?]
        at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:140) [bundleFile:?]
        at org.openhab.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:140) [bundleFile:?]
        at org.openhab.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:461) [bundleFile:?]
        at org.openhab.core.items.GenericItem.lambda$1(GenericItem.java:259) [bundleFile:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
        at java.lang.Thread.run(Thread.java:834) [?:?]

DanielMalmgren · December 11, 2020, 12:59pm

I compared your jdbc.cfg to mine and its exactly identical (the only thing that differs is the host name and the time zone). I wonder though, since it seems to be a group item you’re persisting, do you get the same problem for all items, independent of type? The only problem I have is persistence of QuantityType items, (which I think is fixed in latest snapshot), all others work good.

mhilbush · December 11, 2020, 1:00pm

See above. I got it working, but having some other issues…

DanielMalmgren · December 11, 2020, 1:02pm

A step in the right direction! I wonder if this might be something for @cweitkamp to take a look at…

mhilbush · December 11, 2020, 1:06pm

I’ll look into the second error first. It says it created the table.

2020-12-11 07:36:20.093 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: Table created for item 'E2_Forecast4_DateTime' with dataType TIMESTAMP(3) in SQL database.

But the table isn’t in the database.

mhilbush · December 11, 2020, 1:11pm

Ah, here’s the actual error creating the table.

2020-12-11 07:36:19.956 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: no table found for item 'E1_Runtime_LastModified' in sqlTables
2020-12-11 07:36:19.957 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createNewEntryInItemsTable
2020-12-11 07:36:19.958 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doCreateNewEntryInItemsTable sql=INSERT INTO items (ItemName) VALUES ('E1_Runtime_LastModified')
2020-12-11 07:36:19.964 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: getTableName with rowId=7 itemName=E1_Runtime_LastModified
2020-12-11 07:36:19.965 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType DATETIMEITEM for Item E1_Runtime_LastModified
2020-12-11 07:36:19.966 [DEBUG] [penhab.persistence.jdbc.model.ItemVO] - JDBC:ItemVO tableName=e1_runtime_lastmodified_0007; newTableName=E1_Runtime_LastModified;
2020-12-11 07:36:19.967 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createItemTable
2020-12-11 07:36:19.969 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doCreateItemTable sql=CREATE TABLE IF NOT EXISTS e1_runtime_lastmodified_0007 (time TIMESTAMP(3) NOT NULL, value TIMESTAMP(3), PRIMARY KEY(time))
2020-12-11 07:36:19.995 [ERROR] [org.knowm.yank.Yank                 ] - Error in SQL query!!!
java.sql.SQLException: Invalid default value for 'value' Query: CREATE TABLE IF NOT EXISTS e1_runtime_lastmodified_0007 (time TIMESTAMP(3) NOT NULL, value TIMESTAMP(3), PRIMARY KEY(time)) Parameters: []
        at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392) ~[bundleFile:?]
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491) ~[bundleFile:?]
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457) ~[bundleFile:?]
        at org.knowm.yank.Yank.execute(Yank.java:194) [bundleFile:?]
        at org.knowm.yank.Yank.execute(Yank.java:177) [bundleFile:?]
        at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doCreateItemTable(JdbcBaseDAO.java:317) [bundleFile:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.createItemTable(JdbcMapper.java:136) [bundleFile:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.getTable(JdbcMapper.java:274) [bundleFile:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:143) [bundleFile:?]
        at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:140) [bundleFile:?]
        at org.openhab.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:140) [bundleFile:?]
        at org.openhab.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:461) [bundleFile:?]
        at org.openhab.core.items.GenericItem.lambda$1(GenericItem.java:259) [bundleFile:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
        at java.lang.Thread.run(Thread.java:834) [?:?]

mhilbush · December 11, 2020, 1:21pm

Found this, which I’ll try.

mhilbush · December 11, 2020, 1:26pm

Yes, my sql_mode contains NO_ZERO_IN_DATE and NO_ZERO_DATE

show variables like 'sql_mode' ;

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

mhilbush · December 11, 2020, 1:36pm

Adding the following to the [mysqld] section of mysqld.cnf fixed the table creation issue (after restarting mysql).

sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

cweitkamp · December 11, 2020, 3:46pm

I do not know why, but your problem sounds somehow familiar to me. Just ftr I am using jdbc persistence on OH3 too. But MariaDB instead of MySQL. I double checked my own sql_mode variable and it does not contain the mentioned string.

mhilbush · December 11, 2020, 4:38pm

Thanks. Yeah those zero date settings were added in 5.7, so they are relatively new. In any event removing those from my config resolved the issue with creating tables for DateTime items.

Now if I can figure out what to do about these messages spamming my log file continuously.

2020-12-11 11:33:12.592 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for Zm_FrontDoorImage
2020-12-11 11:33:13.607 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for Zm_KitchenImage
2020-12-11 11:33:13.648 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for Zm_LivingImage
2020-12-11 11:33:13.888 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for Zm_DrivewayImage
2020-12-11 11:33:14.701 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for Zm_PatioImage
2020-12-11 11:33:17.669 [WARN ] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: No sqlType found for ItemType IMAGEITEM, use ItemType for STRINGITEM as Fallback for Zm_BasementImage

mhilbush · December 11, 2020, 4:50pm

@cweitkamp Do you think IMAGEITEM should be added to the sqlTypes HashMap? Since it adds the item to the db as a VARCHAR anyway, it would seem to make sense to not complain about it. WDYT?

github.com

openhab/openhab-addons/blob/main/bundles/org.openhab.persistence.jdbc/src/main/java/org/openhab/persistence/jdbc/db/JdbcBaseDAO.java#L146


    sqlDeleteItemsEntry = "DELETE FROM items WHERE ItemName=#itemname#";
    sqlGetItemIDTableNames = "SELECT itemid, itemname FROM #itemsManageTable#";
    sqlGetItemTables = "SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='#jdbcUriDatabaseName#' AND NOT table_name='#itemsManageTable#'";
    sqlCreateItemTable = "CREATE TABLE IF NOT EXISTS #tableName# (time #tablePrimaryKey# NOT NULL, value #dbType#, PRIMARY KEY(time))";
    sqlInsertItemValue = "INSERT INTO #tableName# (TIME, VALUE) VALUES( #tablePrimaryValue#, ? ) ON DUPLICATE KEY UPDATE VALUE= ?";
}
/**
 * INFO: http://www.java2s.com/Code/Java/Database-SQL-JDBC/StandardSQLDataTypeswithTheirJavaEquivalents.htm
 */
private void initSqlTypes() {
    logger.debug("JDBC::initSqlTypes: Initialize the type array");
    sqlTypes.put("CALLITEM", "VARCHAR(200)");
    sqlTypes.put("COLORITEM", "VARCHAR(70)");
    sqlTypes.put("CONTACTITEM", "VARCHAR(6)");
    sqlTypes.put("DATETIMEITEM", "TIMESTAMP");
    sqlTypes.put("DIMMERITEM", "TINYINT");
    sqlTypes.put("LOCATIONITEM", "VARCHAR(30)");
    sqlTypes.put("NUMBERITEM", "DOUBLE");
    sqlTypes.put("ROLLERSHUTTERITEM", "TINYINT");
    sqlTypes.put("STRINGITEM", "VARCHAR(65500)");// jdbc max 21845

cweitkamp · December 11, 2020, 5:20pm

Yes, why not. Location and Player are missing too. They can be stored as VARCHAR as well. Implementation to support them will close a long pending feature request.

mhilbush · December 11, 2020, 5:30pm

Location is already there (line 153), but Player is not.

I can do a PR for Player and Image.

cweitkamp · December 11, 2020, 7:40pm

That would be appreciated. Please scan the code briefly to find other lines which maybe have to be changed accordingly. Thanks.

mhilbush · December 11, 2020, 7:49pm

Sure. I found a few spots to change. If I add the change centrally in JdbcBaseDAO, I need to also change in some of the other DB-specific DAOs. Haven’t found anything else yet.

I have a version I’m testing now.

edolis · December 20, 2020, 5:14pm

thanks for the informative and exhaustive post - I am trying to set up this persistence as well and I’ll be building on your experience.
BTW, did you observe this issue https://github.com/openhab/openhab-core/issues/1973 yourself after installing JDBC MySQL?
EDIT: forget it, I have been told that after installing the persistence you have to go to settings and select a default persistence otherwise everything is disconnectd. Leaving the comment for anyone having the same issue in the future

EDIT 2: I have installed successfully and data are being pushed to mySQL. Issue is that it looks like data cannot be used any longer to populate charts (Anlyze or custom made). Do you experience the same issue or it’s just my setup?
Apprecite your patience

nelson.aponte · December 26, 2020, 8:07pm

Hi everyone.

My error is not the same one reported above, but it’s also related to JDBC-MySQL/MariaDB in OH3.
After I updated my existing configuration from 2.5 to 3.0 a few days ago, and setup the persistence-jdbc-mariadb, since the mysql driver I was using on 2.5 stopped being supported, I started having this problem:

12/26/2020 22:51:26.204 [ERROR] [tence.internal.PersistenceManagerImpl] - Exception occurred while querying persistence service 'jdbc': class java.lang.String cannot be cast to class java.lang.Number (java.lang.String and java.lang.Number are in module java.base of loader 'bootstrap') 
java.lang.ClassCastException: class java.lang.String cannot be cast to class java.lang.Number (java.lang.String and java.lang.Number are in module java.base of loader 'bootstrap')
at org.openhab.persistence.jdbc.db.JdbcBaseDAO.getState(JdbcBaseDAO.java:469) ~[?:?]
at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doGetHistItemFilterQuery(JdbcBaseDAO.java:340) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcMapper.getHistItemFilterQuery(JdbcMapper.java:162) ~[?:?]
at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.query(JdbcPersistenceService.java:202) ~[?:?]
at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
   	
jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
at org.openhab.core.internal.common.AbstractInvocationHandler.invokeDirect(AbstractInvocationHandler.java:154) [bundleFile:?]
at org.openhab.core.internal.common.Invocation.call(Invocation.java:52) [bundleFile:?]
at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
at java.lang.Thread.run(Thread.java:834) [?:?]

It’s the same reported on this thread:

Surprisingly that thread was created back on March but there seems to be no solution.

I tried uninstalling persistence-jdbc-mariadb and installing persistence-jdbc-mysql but nothing works.

Does anybody know how to fix it?

I’m running openHABian on a Raspberry Pi 3.