Mysql Binding, Mysql Persistence - connection is not valid

mysql
Tags: #<Tag:0x00007f014b0afea8>

(Tomax Me) #21
[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

(Tomax Me) #22

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)


(Angelos) #23

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


(Tomax Me) #24

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"


(Angelos) #25

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…


(Angelos) #26

try (again :slight_smile:) the localhost IP:

mysql.cfg:

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

(Tomax Me) #27

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

mySQL: Connection is not valid!

(Angelos) #28

just to check: from the linux shell:

netstat -atn |grep 3306
lsof -i :3306

(Tomax Me) #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:~$


(Tomax Me) #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?


(Bob Veitch) #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


(Angelos) #32

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

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


(Bob Veitch) #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.



(Angelos) #34

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


(Bob Veitch) #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
}


(Angelos) #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)


(Bob Veitch) #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


(Angelos) #38

what is your current collation?

See: Persistence isnt working

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


(Bob Veitch) #39

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


(Angelos) #40

can you post the Item definition of the TimePeriodOfDay plz