Okay, first of all I’m sorry if the answer for my issue can be found in an already existing thread. I’ve spent several hours combing through threads trying several different things but I just can’t seem to get this thing to work. I’m pretty much totally new to MySQL in general so forgive me for being slow. I can’t get OpenHAB to connect with my database.
I’m running Openhabian snapshot 2.2 on a Pi3. I have MySQL as well as phpMyAdmin installed. I created a database named openhab. Created a new user and gave that user all the permissions to the database. By that I mean I used the mysql command GRANT ALL PRIVILEGES ON openhab.* TO ‘user_name_here’@‘localhost’;
In the process of trying to get it going I also unlocked all the privledges to the openhab database in phpMyAdmin for the user I created. Still nothing. My mysql.cfg file is as follows
# the database url like 'jdbc:mysql://<host>:<port>/<database>' (without quotes)
url=jdbc:mysql://localhost:3306/openhab
# the database user
user=user_name_here
# the database password
password=password_here
# the reconnection counter
#reconnectCnt=5
# the connection timeout (in seconds)
#waitTimeout=10
# 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
on the url line I have tried replacing localhost with the ip address of my pi, as well as 127.0.0.1
For user I have tried both the user I created and root, neither worked.
I’m out of ideas, any guidance would be appreciated. The following is an excerpt from the openhab log that I think would be relevant. Sorry if I missed something, my mind is mush at this point.
2017-12-08 13:30:24.560 [WARN ] [sql.internal.MysqlPersistenceService] - mySQL: No connection to database. Can not persist item 'BatteryFrontDoor (Type=NumberItem, State=100)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:5
2017-12-08 13:48:40.750 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://(ip_address_of_pi_here:3306/openhab, user=user_I_created, password=password_I_created
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:?]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) [?:?]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [?:?]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) [?:?]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) [212:org.openhab.persistence.mysql:1.11.0.201712071817]
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121) [212:org.openhab.persistence.mysql:1.11.0.201712071817]
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:357) [212:org.openhab.persistence.mysql:1.11.0.201712071817]
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2482) [212:org.openhab.persistence.mysql:1.11.0.201712071817]
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2519) [212:org.openhab.persistence.mysql:1.11.0.201712071817]
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2304) [212:org.openhab.persistence.mysql:1.11.0.201712071817]
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834) [212:org.openhab.persistence.mysql:1.11.0.201712071817]
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) [212:org.openhab.persistence.mysql:1.11.0.201712071817]
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:?]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) [?:?]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [?:?]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) [?:?]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) [212:org.openhab.persistence.mysql:1.11.0.201712071817]
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416) [212:org.openhab.persistence.mysql:1.11.0.201712071817]
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346) [212:org.openhab.persistence.mysql:1.11.0.201712071817]
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) [212:org.openhab.persistence.mysql:1.11.0.201712071817]
at org.openhab.persistence.mysql.internal.MysqlPersistenceService.store(MysqlPersistenceService.java:383) [212:org.openhab.persistence.mysql:1.11.0.201712071817]
at org.openhab.core.persistence.internal.PersistenceServiceDelegate.store(PersistenceServiceDelegate.java:55) [210:org.openhab.core.compat1x:2.2.0.201712081403]
at org.eclipse.smarthome.core.persistence.internal.PersistenceManagerImpl.handleStateEvent(PersistenceManagerImpl.java:131) [114:org.eclipse.smarthome.core.persistence:0.10.0.201712081218]
at org.eclipse.smarthome.core.persistence.internal.PersistenceManagerImpl.stateChanged(PersistenceManagerImpl.java:432) [114:org.eclipse.smarthome.core.persistence:0.10.0.201712081218]
at org.eclipse.smarthome.core.items.GenericItem$1.run(GenericItem.java:234) [109:org.eclipse.smarthome.core:0.10.0.201712081218]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:?]
at java.lang.Thread.run(Thread.java:748) [?:?]
Caused by: java.net.ConnectException: Connection refused (Connection refused)
at java.net.PlainSocketImpl.socketConnect(Native Method) ~[?:?]
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350) ~[?:?]
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206) ~[?:?]
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188) ~[?:?]
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) ~[?:?]
at java.net.Socket.connect(Socket.java:589) ~[?:?]
at java.net.Socket.connect(Socket.java:538) ~[?:?]
at java.net.Socket.<init>(Socket.java:434) ~[?:?]
at java.net.Socket.<init>(Socket.java:244) ~[?:?]
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:259) ~[?:?]
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:307) ~[?:?]
... 23 more
2017-12-08 13:48:40.777 [WARN ] [sql.internal.MysqlPersistenceService] - mySQL: No connection to database. Can not persist item 'Sunroom_Temp (Type=NumberItem, State=35.6)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:5
2017-12-08 14:09:01.212 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Connection is not valid!