Hello community,
I am running OpenHAB v2.5.6 in a docker container on my x86 NAS. Just to weeks ago I successfully activated the OpenWeatherMap binding and added all appropriate items via items file definition. Last weekend I just added a new thermostat and suddenly I get persistence errors from my mysql database that refuses to create just the String items??? All other items get created flawlessly.
I found in another topic that someone created the database entries manually with type TEXT to solve the issue but I am not so skilled in mysql administration to know how to do this and I would expect that OpenHAB should be able to create all type of new items appropriately.
As it has worked just two weeks ago with the OpenWeatherMap items am confused what is different and why it causes the errors right now.
Find the log output below:
21:20:01.575 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Could not create table for item 'jreHeizS' with statement 'CREATE TABLE Item19346 (Time DATETIME, Value VARCHAR(65500), PRIMARY KEY(Time));': Column length too big for column 'Value' (max = 21844); use BLOB or TEXT instead
21:20:01.576 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Item 'jreHeizS' was not added to the table - removing index
21:20:01.581 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Could not store item 'jreHeizS' in database with statement 'INSERT INTO Item19346 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': Table 'openhab.Item19346' doesn't exist
21:20:01.594 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Could not create table for item 'aktCD3CegBZmode' with statement 'CREATE TABLE Item19347 (Time DATETIME, Value VARCHAR(65500), PRIMARY KEY(Time));': Column length too big for column 'Value' (max = 21844); use BLOB or TEXT instead
21:20:01.594 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Item 'aktCD3CegBZmode' was not added to the table - removing index
21:20:01.599 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Could not store item 'aktCD3CegBZmode' in database with statement 'INSERT INTO Item19347 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': Table 'openhab.Item19347' doesn't exist
21:20:01.822 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Could not create table for item 'aktFD3CogGZmode' with statement 'CREATE TABLE Item19348 (Time DATETIME, Value VARCHAR(65500), PRIMARY KEY(Time));': Column length too big for column 'Value' (max = 21844); use BLOB or TEXT instead
21:20:01.823 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Item 'aktFD3CogGZmode' was not added to the table - removing index
21:20:01.859 [ERROR] [ysql.internal.MysqlPersistenceService] - mySQL: Could not store item 'aktFD3CogGZmode' in database with statement 'INSERT INTO Item19348 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': Table 'openhab.Item19348' doesn't exist
EDIT:
in the mysql.cfg respectively jdbc.cfg the STRING object size is set to 65500
# Tweak String VAR size
sqltype.string=VARCHAR(65500)
Thank you for any hints and ideas!
Cheers Justus
