Persistence not working after migration to OH4: SQL error data too long

Setup, Configuration and Use Migration
1

Dear Gurus,

Persistence stopped working after migration to OH4, getting the following errors:

org.openhab.persistence.jdbc.internal.exceptions.JdbcSQLException: 
Error in SQL query!!!; Data truncation: Data too long for column 'value' at row 1 
Query: INSERT INTO Item8 (time, value) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ? Parameters: [2023-09-26 07:10:00.0, 2023-09-26 07:10:00.0]; 
Pool Name= yank-default; 
SQL= INSERT INTO Item8 (time, value) VALUES( NOW(3), ? ) ON DUPLICATE KEY UPDATE VALUE= ?

Persistence did work before migration just fine.

Running mysql, here is the jdbc.conf file:

url=jdbc:mysql://192.168.XX.YYY/openhab?serverTimezone=America/Toronto
user=openhab
password=*****
sqltype.STRING=VARCHAR(15000)
itemsManageTable=Items
tableNamePrefix=Item
tableUseRealItemNames=false
tableIdDigitCount=0

Thanks all!

  • Vadim
2

Anybody? Clearing cache, setting “rebuildTableNames=false” did not help the situation.
Is there a way to force full build-from-scratch of persistence tables?

3

Maybe you need different type there - i.e. text? I don’t remember exact limits of particular database systems, but such limit tells me that you store a fairly large chunk of data there.

4

Don’t think it’s that.
I was able to force recreation of table by changing the table prefixes in the configuration file:

itemsManageTable=OpenhabItems
tableNamePrefix=OpenhabItem

It would have been better to be able to address the underlying issue though.

5

This error message indicates that input given to sql query exceeds column length. You either need to truncate input or extend column in size.