Mysql DB do not connect to openhab


(Nakh Home) #1

Hi Folks

mysql db do not connect to openhab

what am i missing?

018-04-01 16:06:14.012 [INFO ] [basic.internal.servlet.WebAppServlet] - Started Basic UI at /basicui/app
2018-04-01 16:06:14.493 [INFO ] [assic.internal.servlet.WebAppServlet] - Started Classic UI at /classicui/app
2018-04-01 16:06:14.599 [INFO ] [arthome.ui.paper.internal.PaperUIApp] - Started Paper UI at /paperui
2018-04-01 16:06:14.700 [INFO ] [b.core.service.AbstractActiveService] - Exec Refresh Service has been started
2018-04-01 16:06:17.538 [INFO ] [ui.habmin.internal.servlet.HABminApp] - Started HABmin servlet at /habmin
2018-04-01 16:06:17.680 [INFO ] [panel.internal.HABPanelDashboardTile] - Started HABPanel at /habpanel
2018-04-01 16:06:18.600 [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, password=openhab
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) [250:org.openhab.persistence.mysql:1.11.0]
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121) [250:org.openhab.persistence.mysql:1.11.0]
	at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:357) [250:org.openhab.persistence.mysql:1.11.0]
	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2482) [250:org.openhab.persistence.mysql:1.11.0]
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2519) [250:org.openhab.persistence.mysql:1.11.0]
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2304) [250:org.openhab.persistence.mysql:1.11.0]
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834) [250:org.openhab.persistence.mysql:1.11.0]
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) [250:org.openhab.persistence.mysql:1.11.0]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:?]
  • i have well installed mysql :slight_smile: thanks to this guide

  • /etc/openhab2/services/mysql.cfg:
############################ mySQL Persistence Service ##################################

# the database url like 'jdbc:mysql://<host>:<port>/<database>'
mysql:url=jdbc:mysql://localhost/openhab

# the database user
mysql:user=openhab

# the database password
mysql:password=openhab

# the reconnection counter
mysql:reconnectCnt=1

# the connection timeout (in seconds)
mysql:waitTimeout=15

# optional tweaking of mysql datatypes
# example as described in https://github.com/openhab/openhab/issues/710
# mysql:sqltype.string=VARCHAR(20000)

# Use MySQL Server time to store item values (=false) or use openHAB Server time (=true).
# For new installations, its recommend to set "mysql:localtime=true".
# (available since 1.9, optional, defaults to false)
#mysql:localtime=true
  • /var/lib/openhab2/config/org/openhab/mysql.config which is autogenerated
password="openhab"
reconnectCnt="1"
service.pid="org.openhab.mysql"
url="jdbc:mysql://localhost:3306/openhab"
user="openhab"
Strategies {
    everyMinute : "0 * * * * ?"
    everyHour   : "0 0 * * * ?"
    everyDay    : "0 0 0 * * ?"

    default = everyChange
}
Items {
    PersistentValues* : strategy = everyChange, restoreOnStartup 

}```

(hr2) #2

What openHAB und java-version do you use?


(Nakh Home) #5

java version “1.8.0_161”

openHAB 2.2.0 Release Build


(Vincent Regaud) #6

Why do you have mysql configurations parameters in the mqtt.cfg file?


(Nakh Home) #7

Typo, i have edited my post.

now,i am not getting anymore the connect.
there’s only a row “Loading model ‘mysql.persist’”


(Vincent Regaud) #8
  1. Stop openhab
  2. delete the file: /var/lib/config/org/openhab/mysql.config
  3. edit your file mysgl.cfg in conf/services and remove the “mysql:” at the begining of each parameter
  4. restart openhab

(Nakh Home) #9

Thanks

but i have another error :slight_smile:

2018-04-02 11:45:32.991 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc://localhost/openhab, user=openhab, password=openhab

java.sql.SQLException: No suitable driver found for jdbc://localhost/openhab

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

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

	at org.openhab.persistence.mysql.internal.MysqlPersistenceService.connectToDatabase(MysqlPersistenceService.java:521) [250:org.openhab.persistence.mysql:1.11.0]
your code goes here

(Nakh Home) #10

i have deleted the file and updated back the file to the file below and restarted the server and nothing in the log

############################ mySQL Persistence Service ##################################

# the database url like 'jdbc://<host>:<port>/<database>'
url=jdbc:mysql://localhost:3306/openhab

# the database user
user=openhab

# the database password
password=openhab

# the reconnection counter
reconnectCnt=1

# the connection timeout (in seconds)
waitTimeout=15

# optional tweaking of mysql datatypes
# example as described in https://github.com/openhab/openhab/issues/710
# sqltype.string=VARCHAR(20000)

# Use MySQL Server time to store item values (=false) or use openHAB Server time (=true).
# For new installations, its recommend to set "localtime=true".
# (available since 1.9, optional, defaults to false)
#localtime=true

(Vincent Regaud) #11

Try replacing localhost with 127.0.0.1
Sometimes it works


(Vincent Regaud) #12

Did it work?


(Nakh Home) #13

to be honest., i have moved to mapdb and influxdb