Mysql Binding, Mysql Persistence - connection is not valid

robster_mn · November 26, 2018, 9:28pm


String           TimePeriodOfDay     "Time of Day [%s]"

Dim · November 26, 2018, 9:32pm

Can you try to migrate to JDBC persistence (using MySQL as the DB)

Still trying to find out why this command doesn’t work:

CREATE TABLE Item37 (Time DATETIME, Value VARCHAR(20000), PRIMARY KEY(Time))

It tries to create a table with 3 columns and the 2nd column should have a VARCHAR type and a length of 20k characters… but your MySQL server complaints that the VARCHAR type cannot have more than 16383 characters…

robster_mn · November 26, 2018, 9:36pm

So just uninstall the "MySQL Persistence " in PaperUI and instll the JDBC for my sql Persistence?

Dim · November 26, 2018, 9:36pm

yes

robster_mn · November 26, 2018, 9:37pm

Copy that, just finished,
Do I have drop the db? or restart the service?

Dim · November 26, 2018, 9:39pm

I would drop the DB… from my understanding, it is empty… correct?
stop OH2, drop DB, clean old mqtt persistence config files, fix jdbc config files, start OH2

robster_mn · November 26, 2018, 9:43pm

I didn’t have mqtt persistence installed?

Dim · November 26, 2018, 9:44pm

yes you did, that’s why you should uninstall it and clean (aka remove) the config files (persistence/mysql.persist and mysql.cfg)

robster_mn · November 26, 2018, 9:47pm

I just looked at paperUI and it showed that it wasn’t installed?

Dim · November 26, 2018, 9:49pm

what is this then? (logs from your posts above):

2018-11-26 14:53:17.796 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!

2018-11-26 14:54:33.941 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'Azimuth' in database with statement 'INSERT INTO Item4 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': No operations allowed after connection closed.

018-11-26 15:09:46.418 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not create table for item 'TimePeriodOfDay' with statement 'CREATE TABLE Item37 (Time DATETIME, Value VARCHAR(20000), PRIMARY KEY(Time));': Column length too big for column 'Value' (max = 16383); use BLOB or TEXT instead

that’s strange…

robster_mn · November 26, 2018, 9:50pm

I had mysql Persistence installed but not Mqtt?

Dim · November 26, 2018, 9:51pm

sorry… typo
instead of mqtt.cfg, I wanted to type mysql.cfg

robster_mn · November 26, 2018, 9:51pm

copy that, done

robster_mn · November 26, 2018, 9:54pm

Deleted the two configs and dropped the DB and just rebooted?

Dim · November 26, 2018, 9:56pm

sounds good.
no need for reboots, just restart the OH2 service:

systemctl restart openhab2

robster_mn · November 26, 2018, 9:56pm

thanks, for next time

robster_mn · November 26, 2018, 9:57pm

Do I have to config anything new, now that I changed Persistence methods?

Dim · November 26, 2018, 9:59pm

in theory: no (after you setup your persistence/jdbc.persist)

you should set your default persistence in PaperUI -> Configuration -> System -> Persistence

If you use in your rules specific persistence service calls, make sure to update the rules also (this is rare)

Check: https://www.openhab.org/addons/persistence/jdbc/#jdbc-persistence also for general conf options

robster_mn · November 26, 2018, 10:04pm

Do I have to setup the jdbc.persist config ? UN & PW???

Dim · November 26, 2018, 10:05pm

credentials go into services/jdbc.cfg
strategies go into persistence/jdbc.persist

check the docs link above (there is also a dedicated section for: https://www.openhab.org/addons/persistence/jdbc/#migration-from-mysql-to-jdbc-persistence-services)