Persistance not working at all

Add-ons Persistence Services
, ,
1

Hello,

I have a problem several times.
At first I used rrd4j-persistance to store temperature, light-State and rollershutter-state. But after a time, I don’t know after a shutdown or during operations, the service stops.
Now, I tested the mysql-service. But with the same result after 3 weeks.

In the past, I restarted the whole system and it restart working. But mostly I had to delete all the persistence files and restart the system. After that, the persistence will store all new values, but the old ones are lost.

Actually I get messages like that:
2021-05-11 19:47:09.277 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not create table for item ‘squeezeKitchenFavorite’ with statement ‘CREATE TABLE Item7632 (Time DATETIME, Value VARCHAR(20000), PRIMARY KEY(Time));’: Column length too big for column ‘Value’ (max = 16383); use BLOB or TEXT instead

2021-05-11 19:47:09.279 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Item ‘squeezeKitchenFavorite’ was not added to the table - removing index

2021-05-11 19:47:09.317 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item ‘squeezeKitchenFavorite’ in database with statement ‘INSERT INTO Item7632 (TIME, VALUE) VALUES(?,?) ON DUPLICATE KEY UPDATE VALUE=?;’: Table ‘OpenHAB.Item7632’ doesn’t exist

Can someone help me. What will be the problem and how can I restart / get rework the service?

Thanks in advance!

2

What version of openHAB are you using?

3

openHAB 2.5.12 Release Build

but I had the same problems with the previous 2.5 builds, too.

4

Okay, we’ll forget rrd4j since you’re not using it and provide no evidence of any problem.

Let’s look at your SQL.
What does this mean

What do you actually see or not see?

What type of Item is this?
Are you using JDBC persistence plug-in?
What’s in your jdbc.cfg and jdbc.persist files?

5

What does this mean.
Some values will be stored and some not.

What do you actually see or not see?
Some values are displayed in my trendviews at Openhab, but the most not.

Normal types likes Number, Switch and Rollershutter should be stored.

I have installed " MySQL Persistence" by Paperui.
attached you will finde the mysql.cfg:

# the database url (127.0.0.1 is the same as localhost)
url=jdbc:mysql://127.0.0.1:3306/OpenHAB?serverTimezone=Europe/Berlin
# the database user
user=openhab
# the database password
password=openhabian
# the reconnection counter
reconnectCnt=3
sqltype.STRING   =   TEXT
# uncomment next line if you store texts in persistence
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 "localtime=true".
# (optional, defaults to false)
localtime=true

and the mysql.persist

// 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 * * * * ?"
   	everyHour   : "0 0 * * * ?"
   	everyDay    : "0 0 0 * * ?"
   	default = everyChange
}

/* 
 * Each line in this section defines for which item(s) which strategy(ies) should be applied.
 * You can list single items, use "*" for all items or "groupitem*" for all members of a group
 * item (excl. the group item itself).
 */

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