Mysql Binding, Mysql Persistence - connection is not valid

Add-ons Persistence Services
61

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

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

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?

64

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

65

of course, just create and edit the config file

66

image
image.png1236×175 8.54 KB

1 Like
67

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

persistence/mysql.persist:

Strategies {
        default = everyChange
}

Items {
        * : strategy = everyChange, restoreOnStartup
}
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

69

Will this work? for jdbc.persist

Strategies {
        default = everyChange
}

Items {
        * : strategy = everyChange, restoreOnStartup
}
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
}
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.

image
image331×567 9.61 KB

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

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.

74

yup :slight_smile:

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!
76

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

post your services/jdbc.cfg plz

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?

78

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

79

Which one should I use:

JDBC Persistence MariaDB

JDBC Persistence MySQL

80

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