Mysql Binding, Mysql Persistence - connection is not valid

mysql
Tags: #<Tag:0x00007f0144ba68e0>

(Bob Veitch) #41

String           TimePeriodOfDay     "Time of Day [%s]"

(Angelos) #42

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…


(Bob Veitch) #43

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


(Angelos) #44

yes


(Bob Veitch) #45

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


(Angelos) #46

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


(Bob Veitch) #47

I didn’t have mqtt persistence installed?


(Angelos) #48

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


(Bob Veitch) #49

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


(Angelos) #50

: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…


(Bob Veitch) #51

I had mysql Persistence installed but not Mqtt?


(Angelos) #52

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


(Bob Veitch) #53

copy that, done


(Bob Veitch) #54

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


(Angelos) #55

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

systemctl restart openhab2

(Bob Veitch) #56

thanks, for next time


(Bob Veitch) #57

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


(Angelos) #58

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


(Bob Veitch) #59

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


(Angelos) #60

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)