Mysql Binding, Mysql Persistence - connection is not valid

mysql
Tags: #<Tag:0x00007f01490bdbb8>

(Tomax Me) #1

UPDATED: after a fresh reinstall of Mysql, user and pass are changed. See some posts bellow the latest state and error too.

Mysql Binding installed. The file mysql.cfg:

url=jdbc:mysql://127.0.0.1:3306/OpenHAB
user=openhab
password=openhabian

Mysql Persistence installed. The file mysql.persist:

Strategies {
	everyMinute	: "0 * * * * ?"
	every5Minutes : "0 */5 * * * ?"
   	everyHour   : "0 0 * * * ?"
   	everyDay    : "0 0 0 * * ?"

       default = everyChange
}

Items { bathroom: strategy = everyChange }

Mysql is active on RaspberryPi. Checked.
I tried to replace the url in mysql.cfg file with: url=mysql://127.0.0.1:3306/OpenHAB (or: localhost). No results but errors.

Now I toggling ON/OFF the testing wallPlug (bathroom) but no result / no error either in LogViewer relating to Persistence service. RaspberryPi restarted.

How should I proceed to checkout what’s wrong?


(Vincent Regaud) #2

What does the log say when you start OH regarding the persistence service?


(Tomax Me) #3

I’ve got that error after restart and when the toggle ON/OFF the test wallplug.

2018-07-19 11:26:58.203 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!

2018-07-19 11:26:58.208 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Error count exceeded 1. Disconnecting database.

Updated:
But I tried right now again with 127.0.0.1 instead of localhost and … nothing in log. I get back to localhost… again nothing in log. This looks strange.

Updated:
I’ve restarted mysql service from CMD.
Now I’ve got a different error wich tells me that is something wrong with database:

2018-07-19 11:35:30.019 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!

2018-07-19 11:35:30.024 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!

2018-07-19 11:35:30.037 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'bathroom' in database with statement 'INSERT INTO Item1 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': No operations allowed after connection closed.

(Vincent Regaud) #4

I assume that mysql is on the same machine as OH

Can you publish your mysql.cfg, please?


(Tomax Me) #5

The same machine, indeed (RaspberryPi).
File mysql.cfg:

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

# the database user
user=openhabian

# the database password
password=yes

# the reconnection counter
reconnectCnt=3

Latest log error:

2018-07-19 11:40:40.918 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://localhost:3306/OpenHAB, user=openhabian, password=yes

java.sql.SQLException: Access denied for user 'openhabian'@'localhost' (using password: YES)

	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078) [252:org.openhab.persistence.mysql:1.12.0]

	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190) [252:org.openhab.persistence.mysql:1.12.0]

	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122) [252:org.openhab.persistence.mysql:1.12.0]

	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:927) [252:org.openhab.persistence.mysql:1.12.0]

	at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1709) [252:org.openhab.persistence.mysql:1.12.0]

	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1252) [252:org.openhab.persistence.mysql:1.12.0]

	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2486) [252:org.openhab.persistence.mysql:1.12.0]

	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2519) [252:org.openhab.persistence.mysql:1.12.0]

	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2304) [252:org.openhab.persistence.mysql:1.12.0]

	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834) [252:org.openhab.persistence.mysql:1.12.0]

	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) [252:org.openhab.persistence.mysql:1.12.0]

	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) [252:org.openhab.persistence.mysql:1.12.0]

	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416) [252:org.openhab.persistence.mysql:1.12.0]

	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346) [252:org.openhab.persistence.mysql:1.12.0]

	at java.sql.DriverManager.getConnection(DriverManager.java:664) [?:?]

	at java.sql.DriverManager.getConnection(DriverManager.java:247) [?:?]

	at org.openhab.persistence.mysql.internal.MysqlPersistenceService.connectToDatabase(MysqlPersistenceService.java:521) [252:org.openhab.persistence.mysql:1.12.0]

	at org.openhab.persistence.mysql.internal.MysqlPersistenceService.store(MysqlPersistenceService.java:383) [252:org.openhab.persistence.mysql:1.12.0]

2018-07-19 11:40:40.930 [WARN ] [sql.internal.MysqlPersistenceService] - mySQL: No connection to database. Cannot persist item 'bathroom (Type=SwitchItem, State=OFF)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:3

(Vincent Regaud) #6

There you go:
Access denied for user 'openhabian'@'localhost'

Can you log in the database from the command line?
What users are set-up?


(Tomax Me) #7

THESE ARE AFTER A FRESH MYSQL REINSTALL.

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

Troubleshooting the plugin issue following this tutorial (option 1) in order to log as root without password:

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

Log is:

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

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

2018-07-19 13:05:30.687 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'bathroom' in database with statement 'INSERT INTO Item1 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': No operations allowed after connection closed.

(Angelos) #8

is that (‘yes’) a replacement word for the actual password of the DB user openhabian?

There is no username openhabian in the DB… (there is another username called openhab :slight_smile:)


(Tomax Me) #9

I’ve reinstalled a fresh mysql meanwhile. So no pass "yes’.
The root user has no pass now.
The user ‘openhab’ has pass=openhab.


(Angelos) #10

then… your mysql.cfg should have:

# the database user
user=openhab

# the database password
password=openhab

(Tomax Me) #11

Exactly!
With: url=jdbc:mysql://localhost:3306/OpenHAB


(Angelos) #12

well… does it work now that you changed the username in the mysql.cfg ?


(Tomax Me) #13

Nope. This is the log error:

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

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

2018-07-19 13:23:29.182 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'bathroom' in database with statement 'INSERT INTO Item1 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': No operations allowed after connection closed.

(Angelos) #14

post again the current mysql.cfg contents plz


(Tomax Me) #15
url=jdbc:mysql://localhost:3306/OpenHAB

# the database user
user=openhab
# remember: user: root, pass: root (for mysql)

# the database password
password=openhab

(Angelos) #16

can you login using the command line using this username/password combo to the MySQL ?

mysql -u openhab -p

(Tomax Me) #17

Yes.


(Angelos) #18
mysql -p -u root
GRANT ALL ON OpenHAB.* TO 'openhab'@'localhost';
FLUSH PRIVILEGES;

(Tomax Me) #19

I did once more time now, not result.


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

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

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

2018-07-19 13:36:27.910 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Error count exceeded 3. Disconnecting database.

(Angelos) #20

oops… my bad… there is no DB OpenHAB created (yet) due to the connection errors.
try:

GRANT ALL ON *.* TO 'openhab'@'localhost' IDENTIFIED BY 'openhab';
FLUSH PRIVILEGES;