JDBC Postgres, no new item0xxx tables

First, thanks to the folks that have worked so hard to make OpenHAB such a great product, and well as those that support the forums. It’s such a great product and a great community.

I’ve been using OpenHAB since about version 2.5.x or so, and so, I’m still using .item files and so forth to manage my items (for better or worse).

I have all the following in my jdbc.persist file:

Strategies {
  default = everyChange
}

Items {
    // persist items on every change and every minute
    * : strategy = everyChange, everyMinute    
}

I have a really simple jdbc.cfg

url=jdbc:postgresql://postgres01.internal:5432/openhabdb?serverTimezone=America/New_York

# required database user
user=xxxx

# required database password
password=xxxx

Things have been working normally up until recently. My items table has:
Itemid: 517
Itemname: WhateverOldItem

My other tables are named contiguously from item0001 to item0517 with appropriate time and value.

However, recently, I’ve added a few more things and so, a few more items (via .items file). My items table has:
Itemid: 528
Itemname: NewItem

However, the are no new item05xx tables. My expectation is that there would be table item0518, item0519, and so forth to item0528. Subsequently, there are these error messages in /var/log/openhab/openhab.log

2022-01-08 11:40:22.772 [ERROR] [org.knowm.yank.Yank                 ] - Error in SQL query!!!
java.sql.SQLException: ERROR: relation "item0522" does not exist
  Position: 13 Query: INSERT INTO item0522 (TIME, VALUE) VALUES( NOW(), CAST( ? as DOUBLE PRECISION) ) Parameters: [75.047]
	at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392) ~[?:?]
	at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491) ~[?:?]
	at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457) ~[?:?]
	at org.knowm.yank.Yank.execute(Yank.java:194) ~[?:?]
	at org.knowm.yank.Yank.execute(Yank.java:177) ~[?:?]
	at org.openhab.persistence.jdbc.db.JdbcPostgresqlDAO.doStoreItemValue(JdbcPostgresqlDAO.java:141) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:156) ~[?:?]
	at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:143) ~[?:?]
	at org.openhab.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:152) ~[?:?]
	at org.openhab.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:473) ~[?:?]
	at org.openhab.core.items.GenericItem.lambda$1(GenericItem.java:259) ~[?:?]
	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:829) [?:?]

In one case, I manually added the item0520 table and it appears to work fine, and so, that is a “manual” fix, but I’d really prefer not to have to manually add tables from every item from here on out.

And thoughts?

(I will try to find and add some jdbc binding logs)

openhab> bundle:list -s | grep -i jdbc
291 │ Active │  80 │ 3.1.0                 │ org.openhab.persistence.jdbc
306 │ Active │  80 │ 9.4.1212              │ org.postgresql.jdbc42
openhab>      

Here are the JDBC logs:

2022-01-08 12:07:49.723 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createItemTable
2022-01-08 12:07:49.723 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doCreateItemTable sql=CREATE TABLE IF NOT EXISTS item0529 (time TIMESTAMP NOT NULL, value VARCHAR, PRIMARY KEY(time))
2022-01-08 12:07:49.736 [ERROR] [org.knowm.yank.Yank                 ] - Error in SQL query!!!
java.sql.SQLException: ERROR: permission denied for table awsdms_ddl_audit
  Where: SQL statement "insert into public.awsdms_ddl_audit
            values
            (
            default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
            )"
PL/pgSQL function awsdms_intercept_ddl() line 6 at SQL statement Query: CREATE TABLE IF NOT EXISTS item0529 (time TIMESTAMP NOT NULL, value VARCHAR, PRIMARY KEY(time)) Parameters: []
        at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392) ~[?:?]
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491) ~[?:?]
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457) ~[?:?]
        at org.knowm.yank.Yank.execute(Yank.java:194) ~[?:?]
        at org.knowm.yank.Yank.execute(Yank.java:177) ~[?:?]
        at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doCreateItemTable(JdbcBaseDAO.java:323) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.createItemTable(JdbcMapper.java:143) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.getTable(JdbcMapper.java:279) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:150) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:143) ~[?:?]
        at org.openhab.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:152) ~[?:?]
        at org.openhab.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:473) ~[?:?]
        at org.openhab.core.items.GenericItem.lambda$1(GenericItem.java:259) ~[?:?]
        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:829) [?:?]
2022-01-08 12:07:49.739 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: Table created for item 'F1_ServerCabinet_FanSwitch_TestTestTest' with dataType VARCHAR in SQL database.
2022-01-08 12:07:49.739 [DEBUG] [.openhab.persistence.jdbc.dto.ItemVO] - JDBC:ItemVO tableName=item0529; newTableName=null; 
2022-01-08 12:07:49.739 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType SWITCHITEM for Item F1_ServerCabinet_FanSwitch_TestTestTest
2022-01-08 12:07:49.739 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'F1_ServerCabinet_FanSwitch_TestTestTest' as Type 'SWITCHITEM' in 'item0529' with state 'ON'
2022-01-08 12:07:49.739 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: 'ON'
2022-01-08 12:07:49.739 [DEBUG] [.openhab.persistence.jdbc.dto.ItemVO] - JDBC:ItemVO setValueTypes dbType=VARCHAR; javaType=class java.lang.String;
2022-01-08 12:07:49.739 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: other: item.getState().toString(): 'ON'
2022-01-08 12:07:49.740 [DEBUG] [ersistence.jdbc.db.JdbcPostgresqlDAO] - JDBC::doStoreItemValue sql=INSERT INTO item0529 (TIME, VALUE) VALUES( NOW(), CAST( ? as VARCHAR) ) value='ON'
2022-01-08 12:07:49.740 [ERROR] [org.knowm.yank.Yank                 ] - Error in SQL query!!!
java.sql.SQLException: ERROR: relation "item0529" does not exist
  Position: 13 Query: INSERT INTO item0529 (TIME, VALUE) VALUES( NOW(), CAST( ? as VARCHAR) ) Parameters: [ON]
        at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392) ~[?:?]
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491) ~[?:?]
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457) ~[?:?]
        at org.knowm.yank.Yank.execute(Yank.java:194) ~[?:?]
        at org.knowm.yank.Yank.execute(Yank.java:177) ~[?:?]
        at org.openhab.persistence.jdbc.db.JdbcPostgresqlDAO.doStoreItemValue(JdbcPostgresqlDAO.java:141) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:156) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:143) ~[?:?]
        at org.openhab.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:152) ~[?:?]
        at org.openhab.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:473) ~[?:?]
        at org.openhab.core.items.GenericItem.lambda$1(GenericItem.java:259) ~[?:?]
        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:829) [?:?]
2022-01-08 12:07:49.743 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'F1_ServerCabinet_FanSwitch_TestTestTest' as 'ON' in SQL database at Sat Jan 08 12:07:49 EST 2022 in 26 ms.

This is interesting. I am using AWS DMS to replicate this database to an Aurora PostgresDB in AWS. I think the easy fix might be to add the permissions to the DMS table, but why is OpenHAB trying to modify that table?

That table, awsdms_ddl_audit, is owned by a different user “dms”.

I have provided user “openhab” with permissions to insert,select, update and delete (dwar), and I’m still getting the following errors:

2022-01-08 12:31:52.876 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::storeItemValue: item=F1_ServerCabinet_FanSwitch_TestTestTest_202201081225 (Type=SwitchItem, State=ON, Label=Fan Switch, Category=switch, Tags=[Switchable], Groups=[F1_ServerCabinet, gSwitch])
2022-01-08 12:31:52.876 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: no table found for item 'F1_ServerCabinet_FanSwitch_TestTestTest_202201081225' in sqlTables
2022-01-08 12:31:52.876 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createNewEntryInItemsTable
2022-01-08 12:31:52.877 [DEBUG] [ersistence.jdbc.db.JdbcPostgresqlDAO] - JDBC::doCreateNewEntryInItemsTable sql=INSERT INTO items (itemname) SELECT itemname FROM items UNION VALUES ('F1_ServerCabinet_FanSwitch_TestTestTest_202201081225') EXCEPT SELECT itemname FROM items
2022-01-08 12:31:52.882 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: getTableName with rowId=530 itemName=F1_ServerCabinet_FanSwitch_TestTestTest_202201081225
2022-01-08 12:31:52.883 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType SWITCHITEM for Item F1_ServerCabinet_FanSwitch_TestTestTest_202201081225
2022-01-08 12:31:52.883 [DEBUG] [.openhab.persistence.jdbc.dto.ItemVO] - JDBC:ItemVO tableName=item0530; newTableName=F1_ServerCabinet_FanSwitch_TestTestTest_202201081225; 
2022-01-08 12:31:52.883 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::createItemTable
2022-01-08 12:31:52.883 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doCreateItemTable sql=CREATE TABLE IF NOT EXISTS item0530 (time TIMESTAMP NOT NULL, value VARCHAR, PRIMARY KEY(time))
2022-01-08 12:31:52.896 [ERROR] [org.knowm.yank.Yank                 ] - Error in SQL query!!!
java.sql.SQLException: ERROR: permission denied for sequence awsdms_ddl_audit_c_key_seq
  Where: SQL statement "insert into public.awsdms_ddl_audit
            values
            (
            default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
            )"
PL/pgSQL function awsdms_intercept_ddl() line 6 at SQL statement Query: CREATE TABLE IF NOT EXISTS item0530 (time TIMESTAMP NOT NULL, value VARCHAR, PRIMARY KEY(time)) Parameters: []
        at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392) ~[?:?]
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491) ~[?:?]
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457) ~[?:?]
        at org.knowm.yank.Yank.execute(Yank.java:194) ~[?:?]
        at org.knowm.yank.Yank.execute(Yank.java:177) ~[?:?]
        at org.openhab.persistence.jdbc.db.JdbcBaseDAO.doCreateItemTable(JdbcBaseDAO.java:323) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.createItemTable(JdbcMapper.java:143) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.getTable(JdbcMapper.java:279) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:150) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:143) ~[?:?]
        at org.openhab.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:152) ~[?:?]
        at org.openhab.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:473) ~[?:?]
        at org.openhab.core.items.GenericItem.lambda$1(GenericItem.java:259) ~[?:?]
        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:829) [?:?]
2022-01-08 12:31:52.932 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getTable: Table created for item 'F1_ServerCabinet_FanSwitch_TestTestTest_202201081225' with dataType VARCHAR in SQL database.
2022-01-08 12:31:52.932 [DEBUG] [.openhab.persistence.jdbc.dto.ItemVO] - JDBC:ItemVO tableName=item0530; newTableName=null; 
2022-01-08 12:31:52.933 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::getItemType: Try to use ItemType SWITCHITEM for Item F1_ServerCabinet_FanSwitch_TestTestTest_202201081225
2022-01-08 12:31:52.933 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: item 'F1_ServerCabinet_FanSwitch_TestTestTest_202201081225' as Type 'SWITCHITEM' in 'item0530' with state 'ON'
2022-01-08 12:31:52.933 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: getState: 'ON'
2022-01-08 12:31:52.933 [DEBUG] [.openhab.persistence.jdbc.dto.ItemVO] - JDBC:ItemVO setValueTypes dbType=VARCHAR; javaType=class java.lang.String;
2022-01-08 12:31:52.933 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::storeItemValueProvider: other: item.getState().toString(): 'ON'
2022-01-08 12:31:52.933 [DEBUG] [ersistence.jdbc.db.JdbcPostgresqlDAO] - JDBC::doStoreItemValue sql=INSERT INTO item0530 (TIME, VALUE) VALUES( NOW(), CAST( ? as VARCHAR) ) value='ON'
2022-01-08 12:31:52.934 [ERROR] [org.knowm.yank.Yank                 ] - Error in SQL query!!!
java.sql.SQLException: ERROR: relation "item0530" does not exist
  Position: 13 Query: INSERT INTO item0530 (TIME, VALUE) VALUES( NOW(), CAST( ? as VARCHAR) ) Parameters: [ON]
        at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392) ~[?:?]
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:491) ~[?:?]
        at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:457) ~[?:?]
        at org.knowm.yank.Yank.execute(Yank.java:194) ~[?:?]
        at org.knowm.yank.Yank.execute(Yank.java:177) ~[?:?]
        at org.openhab.persistence.jdbc.db.JdbcPostgresqlDAO.doStoreItemValue(JdbcPostgresqlDAO.java:141) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcMapper.storeItemValue(JdbcMapper.java:156) ~[?:?]
        at org.openhab.persistence.jdbc.internal.JdbcPersistenceService.store(JdbcPersistenceService.java:143) ~[?:?]
        at org.openhab.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:152) ~[?:?]
        at org.openhab.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:473) ~[?:?]
        at org.openhab.core.items.GenericItem.lambda$1(GenericItem.java:259) ~[?:?]
        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:829) [?:?]
2022-01-08 12:31:52.953 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Stored item 'F1_ServerCabinet_FanSwitch_TestTestTest_202201081225' as 'ON' in SQL database at Sat Jan 08 12:31:52 EST 2022 in 77 ms.

I have a feeling the answer is on this page: Using a PostgreSQL database as an AWS DMS source - AWS Database Migration Service

Investigating…

I’m speculating that this solved the issue:

grant all on public.awsdms_ddl_audit to public;
grant all on public.awsdms_ddl_audit_c_key_seq to public;

Leaving this post up for others

SixO