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