Mysql Binding, Mysql Persistence - connection is not valid

mysql
Tags: #<Tag:0x00007f014b893d18>

(Bob Veitch) #61

I just checked and I don’t have a jdbc.persist file?


(Angelos) #62

and… after you fix it (and hopefully, MySQL Server will not spit the same VARCHAR error)…

you may want to reconsider your persistence setup

restoreOnStartup performance on MySQL is far worse than MapDB
Go with a dual persistence setup: MapDB for restore (default persistence) and MySQL for historical data


(Angelos) #63

I will quote from the docs now because you should have read this part already :slight_smile:

Did you install the proper JDBC Persistence Add-on?


(Bob Veitch) #64

Well its a little late for that now as I already deleted it?
Can I just re-create it?


(Angelos) #65

of course, just create and edit the config file


(Bob Veitch) #66


(Bob Veitch) #67

So if I create a new file, with the following:

persistence/mysql.persist:

Strategies {
        default = everyChange
}

Items {
        * : strategy = everyChange, restoreOnStartup
}

(Angelos) #68

when you installed the JDBC persistence service, it should have deployed a template file: persistence/jdbc.persist

You can edit that one and you don’t need anymore the persistence/mysql.persist


(Bob Veitch) #69

Will this work? for jdbc.persist

Strategies {
        default = everyChange
}

Items {
        * : strategy = everyChange, restoreOnStartup
}

(Angelos) #70

yes, it will work but it’s “too much”
this will store every Item’s state when it changes
this will also restore every Item’s state when OH2 starts up

use this for now to see if your persistence is storing to MySQL and you can optimize it later

It’s better to create a Group in your Items file, make the Items that you want to persist members of this Group and then:

Strategies {
        default = everyChange
}

Items {
        gGroupName* : strategy = everyChange, restoreOnStartup
}

(Bob Veitch) #71

Okay thanks for your help…

I’ll check it out tomorrow.

Any idea how to connect to the DB via toad, every time I try i get an error:

Unable to connect to any of the specified MySQL hosts.


(Angelos) #72

now we are going outside the scope of openHAB :slight_smile:

check /etc/mysql/my.cnf if the service is bound to listen to all interfaces (not only localhost)

netstat -atn |grep 3306


(Bob Veitch) #73

Copy that, Thanks.
I did read that it should be 0.0.0.0 and not localhost? I’ll have to dig into that a bit more.


(Angelos) #74

yup :slight_smile:


(Bob Veitch) #75

Shit, I just checked my log and I now have a :slight_smile:

2018-11-26 16:34:31.374 [WARN ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: no driver available!



(Angelos) #76

your system is giving you trouble today. maybe take a break and attack it tomorrow ? :slight_smile:

post your services/jdbc.cfg plz


(Angelos) #77

can you see JDBC Persistence MariaDB in PaperUI addons?
try to use this one. Maybe some MySQL packages (like mysql-client) are not installed on your system?


(Bob Veitch) #78

I didn’t use the “JDBC Persistence MariaDB” is used " JDBC Persistence"


(Bob Veitch) #79

Which one should I use:

JDBC Persistence MariaDB

JDBC Persistence MySQL


(Angelos) #80

try the JDBC:MariaDB now (since the JDBC:MySQL didn’t work for you)