Mysql Binding, Mysql Persistence - connection is not valid

mysql
Tags: #<Tag:0x00007f01487a0d28>

(Bob Veitch) #81

Should this be loading:

2018-11-26 17:25:39.786 [INFO ] [el.core.internal.ModelRepositoryImpl] - Loading model 'rrd4j.persist'

(Angelos) #82

if you are not using rrd4j persistence: then no
uninstall the addon and remove the persistence/rrd4j.persist and services/rrd4j.cfg files


(Bob Veitch) #83

Okay, I removed rrd4j, and restarted the service and still have the following error:

2018-11-26 17:38:05.119 [WARN ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: no driver available!

2018-11-26 17:38:05.128 [WARN ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: no driver available!

(Bob Veitch) #84

Here’s my config:

############################ JDBC Persistence Service ##################################
# I N S T A L L   J D B C   P E R S I S T E N C E   S E R V I C E
#
# https://github.com/openhab/openhab/wiki/JDBC-Persistence
#
# Tested databases/url-prefix: jdbc:derby, jdbc:h2, jdbc:hsqldb, jdbc:mariadb, jdbc:mysql, jdbc:postgresql, jdbc:sqlite
#
# derby, h2, hsqldb, sqlite can be embedded,
# If no database is available it will be created, for example the url 'jdbc:h2:./testH2' creates a new DB in OpenHab Folder.
#
# Create new database, for example on a MySQL-Server use:
# CREATE DATABASE 'yourDB' CHARACTER SET utf8 COLLATE utf8_general_ci;

# D A T A B A S E  C O N F I G
# Some URL-Examples, 'service' identifies and activates internally the correct jdbc driver.
# required database url like 'jdbc:<service>:<host>[:<port>;<attributes>]'
# url=jdbc:derby:./testDerby;create=true
# url=jdbc:h2:./testH2
# url=jdbc:hsqldb:./testHsqlDb
# url=jdbc:mariadb://192.168.0.1:3306/testMariadb
# url=jdbc:mysql://192.168.0.1:3306/testMysql
# url=jdbc:postgresql://192.168.0.1:5432/testPostgresql
# url=jdbc:sqlite:./testSqlite.db
# url=
url=jdbc:mysql://localhost:3306/OpenHAB

# required database user
user=openhab

# required database password
password=openhab

# E R R O R   H A N D L I N G
# optional when Service is deactivated (optional, default: 0 -> ignore)
#errReconnectThreshold=

# I T E M   O P E R A T I O N S
# optional tweaking SQL datatypes
# see: https://mybatis.github.io/mybatis-3/apidocs/reference/org/apache/ibatis/type/JdbcType.html
# see: http://www.h2database.com/html/datatypes.html
# see: http://www.postgresql.org/docs/9.3/static/datatype.html
# defaults:
#sqltype.CALL          =   VARCHAR(200)
#sqltype.COLOR         =   VARCHAR(70)
#sqltype.CONTACT       =   VARCHAR(6)
#sqltype.DATETIME      =   DATETIME
#sqltype.DIMMER        =   TINYINT
#sqltype.LOCATION      =   VARCHAR(30)
#sqltype.NUMBER        =   DOUBLE
#sqltype.ROLLERSHUTTER =   TINYINT
#sqltype.STRING        =   VARCHAR(65500)
#sqltype.SWITCH        =   VARCHAR(6)

# For Itemtype "Number" default decimal digit count (optional, default: 3)
#numberDecimalcount=

# T A B L E   O P E R A T I O N S
# Tablename Prefix String (optional, default: "item")
# for Migration from MYSQL-Bundle set to 'Item'.
#tableNamePrefix=Item

# Tablename Prefix generation, using Item real names or "item" (optional, default: false -> "item")
# If true, 'tableNamePrefix' is ignored.
#tableUseRealItemNames=

# Tablename Suffix length (optional, default: 4 -> 0001-9999)
# for Migration from MYSQL-Bundle set to 0.
#tableIdDigitCount=

# Rename existing Tables using tableUseRealItemNames and tableIdDigitCount (optional, default: false)
# USE WITH CARE! Deactivate after Renaming is done!
#rebuildTableNames=true

# D A T A B A S E  C O N N E C T I O N S
# Some embeded Databases can handle only one Connection (optional, default: configured per database in packet org.openhab.persistence.jdbc.db.* )
# see: https://github.com/brettwooldridge/HikariCP/issues/256
# jdbc.maximumPoolSize = 1
# jdbc.minimumIdle = 1

# T I M E K E E P I N G
# (optional, default: false)
#enableLogTime=true


(Angelos) #85

something is wrong with your system
installing the persistence addon should have deployed the necessary files (which include the required drivers)…
the driver should have been pulled it from here: http://mvnrepository.com/artifact/org.mariadb.jdbc/mariadb-java-client

I don’t know what to tell you… :frowning:

does your OH2 system have proper internet access?


(Bob Veitch) #86

Yes, as I use the remote openhab connection.


(Angelos) #87

ok… last effort:

systemctl stop openhab2
cd /usr/share/openhab2/addons/
wget "https://downloads.mariadb.com/Connectors/java/connector-java-2.3.0/mariadb-java-client-2.3.0.jar"
systemctl start openhab2

(Bob Veitch) #88

I’m trying paperUI one more time…

Which one should I try?

JDBC Persistence MariaDB
JDBC Persistence MySQL


(Angelos) #89

start clean with JDBC:MariaDB
if the JDBC::openConnection: no driver available! is still there: download the jar that I linked above to your addons directory


(Bob Veitch) #90

Well I downloaded the jar and I still have the erorr?

==> /var/log/openhab2/openhab.log <==

2018-11-26 18:52:09.854 [WARN ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: no driver available!

2018-11-26 18:52:09.860 [WARN ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: no driver available!

2018-11-26 18:52:09.866 [WARN ] [jdbc.internal.JdbcPersistenceService] - JDBC::store:  No connection to database. Cannot persist item 'ntp_ntp_local_string (Type=StringItem, State=2018-11-26 18:52:09 CST)'! Will retry connecting to database when error count:0 equals errReconnectThreshold:0

(Bob Veitch) #91

Something new?

2018-11-26 19:15:51.584 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource