Mysql Binding, Mysql Persistence - connection is not valid

TomaxMe · July 19, 2018, 10:44am

[13:41:22] openhabian@openHABianPi:~$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> GRANT ALL ON *.* TO 'openhab'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

2018-07-19 13:43:19.729 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!

2018-07-19 13:43:19.733 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!

2018-07-19 13:43:19.741 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Error count exceeded 2. Disconnecting database.

==> /var/log/openhab2/events.log <==

2018-07-19 13:43:19.749 [vent.ItemStateChangedEvent] - bathroom changed from OFF to ON

==> /var/log/openhab2/openhab.log <==

2018-07-19 13:43:19.753 [WARN ] [sql.internal.MysqlPersistenceService] - mySQL: No connection to database. Cannot persist item 'bathroom (Type=SwitchItem, State=ON)'! Will retry connecting to database when error count:2 equals errReconnectThreshold:2

TomaxMe · July 19, 2018, 10:50am

It seems that there is already the databases OpenHAB:

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| OpenHAB            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

There is also created the user ‘openhab’:

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

Dim · July 19, 2018, 10:53am

check the file /var/lib/openhab2/config/org/openhab/mysql.config for stale settings (just in case)
If yes, delete it and restart OH2 service

TomaxMe · July 19, 2018, 10:54am

This is the file content (to delete the content?):

password="openhab"
reconnectCnt="2"
service.pid="org.openhab.mysql"
url="jdbc:mysql://localhost:3306/OpenHAB"
user="openhab"

Dim · July 19, 2018, 10:56am

no need. it seems that there are no old/wrong parameters in there.

I can’t pinpoint the problem…
you have a username/password combo that seem to be working ok (and you can login to mysql using them)
for some reason the persistence addon doesn’t want to connect to the MySQL service…

Dim · July 19, 2018, 11:02am

try (again ) the localhost IP:

mysql.cfg:

pid:org.openhab.mysql
url=jdbc:mysql://127.0.0.1/OpenHAB
user=openhab
password=openhab
reconnectCnt=1

TomaxMe · July 19, 2018, 11:04am

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

mySQL: Connection is not valid!

Dim · July 19, 2018, 11:07am

just to check: from the linux shell:

netstat -atn |grep 3306
lsof -i :3306

TomaxMe · July 19, 2018, 11:08am


[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:~$

TomaxMe · July 19, 2018, 2:34pm

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?

robster_mn · November 26, 2018, 5:55pm

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:

any ideas?
Thanks

Dim · November 26, 2018, 8:38pm

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

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

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