Mysql Binding, Mysql Persistence - connection is not valid

Add-ons Persistence Services
27

Tried right now, restarted mysql… but nothing new :(.

mySQL: Connection is not valid!
28

just to check: from the linux shell:

netstat -atn |grep 3306
lsof -i :3306
29 

[14:05:35] openhabian@openHABianPi:~$ netstat -atn |grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN
[14:08:02] openhabian@openHABianPi:~$ lsof -i :3306
[14:08:11] openhabian@openHABianPi:~$
30

Refreshed all installation: binding, mysql.
Refresh all files: persistence, mysql.cfg

I’ve got the same error but one time only (after: sudo systemctl restart mysql), not each time I toggle ON/OFF some buttons. Maybe could be that a hint to follow?

31

I’m having a similar issue:

2018-11-26 11:39:46.087 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://localhost:3306/openhab, user=openhab

config:

the database url like ‘jdbc:mysql://:/’ (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=

DB and Users, information:

MariaDB [(none)]> SHOW DATABASES;
±-------------------+
| Database |
±-------------------+
| OpenHAB |
| information_schema |
| mysql |
| openhab |
| performance_schema |
±-------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> SELECT User,Host FROM mysql.user;
±--------±----------+
| User | Host |
±--------±----------+
| openhab | localhost |
| root | localhost |
±--------±----------+
2 rows in set (0.00 sec)

any ideas?
Thanks

32

did you try any of the troubleshooting steps in this thread?

when posting configs, use Code fences: How to use code fences

33

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.
34

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

35

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
}
36

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)

37

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

38

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.

39

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

40

can you post the Item definition of the TimePeriodOfDay plz

41 

String           TimePeriodOfDay     "Time of Day [%s]"
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…

43

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

44

yes

45

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

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