Mysql Binding, Mysql Persistence - connection is not valid


String           TimePeriodOfDay     "Time of Day [%s]"

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…

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

yes

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

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

I didn’t have mqtt persistence installed?

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

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

:slight_smile:

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…

I had mysql Persistence installed but not Mqtt?

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

copy that, done

1 Like

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

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

systemctl restart openhab2

thanks, for next time

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

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

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

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)