Getting started with persistence

Add-ons Persistence Services
1

I’m trying to get persistence working with OH2.

I’ve installed the JDBC Persistence MariaDB extension and edited the jdbc.cfg services file, and openhab.log tells me it’s connecting, and a table called ‘items’ has been created in the database.

I have created a file in the persistence directory called mariadb.persistence (details below), but no entries are appearing in the table, and i’ve no idea why. Can anyone help me with this please?

// persistence strategies have a name and a definition and are referred to in the "Items" section
Strategies {
	// if no strategy is specified for an item entry below, the default list will be used
	everyMinute	: "0 * * * * ?"
	every5Minutes : "0 */5 * * * ?"
   	everyHour   : "0 0 * * * ?"
   	everyDay    : "0 0 0 * * ?"
   	default = everyChange
}

Items {
    // persist all items once a day and on every change and restore them from the db at startup
    * : strategy = everyChange, everyMinute, everyDay, restoreOnStartup
}
1 Like
2

Hi Neil,

Looks like the exact same Problem I ran into here Persistence isnt working
I do have the exact same symptoms like you describe. If I’m home again, I want to try to get my hands on the DB-logs to see if there’s some error in the sqls here.

3

hi Thomas, yes, it looks just about the same, tho with different persistence drivers.

I’ve just gone to find my own log files and there’s nothing appearing in the MariaDB error log, and the query log shows no attempts at inserting data into the table (tho does do a ‘create table if not exist’ at OH2 startup) - so i don’t have any idea for what to do next.

4

From hunting around i found two problems with what I’d set-up.

Firstly, from here:-


… it appears that “you have to put a configuration file named .persist (e.g. db4o.persist) in the folder ${openhab.home}/configurations/persistence”

As my persistence file was called mariadb.persist, that might have been a problem. I’ve renamed it to jdbc.persist to match the name of the persistence driver file in the services folder.

Secondly, that persistence file wasn’t in the ‘live’ persistence folder as it should have been (my NAS makes accessing the live folders awkward).

And that’s sorted out my problems, because my items table has now been populated with 153 rows (each named item from my item files), plus 153 tables named ‘itemXXXX’ where XXXX is a number from 0001 to 0153, with some of those tables being populated with values.

Sorted. :slight_smile:

1 Like
5

yes! That did the trick!

But: I do have a strange error. I suppose, if an item is set as String, the persistance layer would like to store it as VARCHAR(20000) in the MySQL database. I get the error in the log:

2016-12-01 21:32:00.246 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not create table for item 'Moon_Phase' with statement 'CREATE TABLE Item309 (Time DATETIME, Value VARCHAR(20000), PRIMARY KEY(Time));': Column length too big for column 'Value' (max = 16383); use BLOB or TEXT instead
2016-12-01 21:32:00.249 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Item 'Moon_Phase' was not added to the table - removing index
2016-12-01 21:32:00.262 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'Moon_Phase' in database with statement 'INSERT INTO Item309 (TIME, VALUE) VALUES(NOW(),?) ON DUPLICATE KEY UPDATE VALUE=?;': Table 'openHAB.Item309' doesn't exist

But: If I insert this via MySQL Workbench, the Query is successful. Do you encountered that too? And do you know the collation of the database, you’re using?

edit:
I think with latin1_german1_ci the persistance works, but i don’t get a reset on the persisted items. See the parallel thread for this: Persistence isnt working