Mysql Binding, Mysql Persistence - connection is not valid

robster_mn · November 26, 2018, 8:54pm

Yes, and I’m still getting the following?

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

and

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.

Dim · November 26, 2018, 8:56pm

post your services/mysql.cfg & persistence/mysql.persist plz

robster_mn · November 26, 2018, 9:00pm

Okay here you go…

mysql.cfg:

pid=org.openhab.mysql
# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
url=jdbc:mysql://localhost:3306/OpenHAB

# the database user
user=openhab

# the database password
password=openhab

# the reconnection counter
#reconnectCnt=

# the connection timeout (in seconds)
#waitTimeout=

# Use MySQL Server time to store item values (=false) or use openHAB Server time (=true).
# For new installations, its recommend to set "localtime=true".
# (optional, defaults to false)
#localtime=true

persistence/mysql.persist:

Strategies {
        default = everyChange
}

Items {
        * : strategy = everyChange, restoreOnStartup
}

Dim · November 26, 2018, 9:01pm

change this to

pid:org.openhab.mysql

and try again

it was a typo on my part on a previous post (I corrected it also above, thanx)

robster_mn · November 26, 2018, 9:15pm

Okay, I think that may have fixed it, I’m getting some different errors now, but not as bad?

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
2018-11-26 15:09:46.421 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Item 'TimePeriodOfDay' was not added to the table - removing index
2018-11-26 15:09:46.431 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'TimePeriodOfDay' in database with statement 'INSERT INTO Item37 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': Table 'OpenHAB.Item37' doesn't exist

Another question:?

Do you know If I can connect to the mysql db via TOAD from my laptop on the same network?
https://www.toadworld.com/products#mysql

Dim · November 26, 2018, 9:18pm

what is your current collation?

See: Persistence isnt working - #23 by binderth

I don’t see a reason why not. In theory, the MySQL server running on your Linux host will accept connections from the network.

robster_mn · November 26, 2018, 9:25pm

I don’t know, just starting out here, trying to get this set up?

Dim · November 26, 2018, 9:26pm

can you post the Item definition of the TimePeriodOfDay plz

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