// persistence strategies have a name and a definition and are referred to in the "Items" section
// 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
* 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).
// global groups
chartpersist* : strategy = everyMinute
statepersist* : strategy = everyChange, restoreOnStartup
The log file gets message to say there is an error when I have statepersist tag set on the items. How do I get resolve this?
2019-04-13 11:21:39.203 [ome.event.ItemCommandEvent] - Item 'GR_Temp_Mode' received command ON
2019-04-13 11:21:39.207 [vent.ItemStateChangedEvent] - GR_Temp_Mode changed from NULL to ON
2019-04-13 11:22:00.354 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'GR_Temp_Mode' in database with statement 'INSERT INTO Item135 (TIME, VALUE) VALUES(?,?) ON DUPLICATE KEY UPDATE VALUE=?;': Data truncated for column 'Value' at row 1
I have rebooted many times that does not clear the issue.
Rebooting is not going to help here indeed. What the error basically says is that the value that GR_Temp_Mode has, doesn’t fit in the Value column. What data type and length does that column have?
By the way, the cause of this issue might very well be this:
You realize that a Number and a Switch have different data types, especially when looking from a MySQL perspective?
Yes I realise that my desire to change my approach from the item being a Number to a Swirtch has a knock on effect, but not sure how to clear it out and start again with the items as switches.
I have just tried with one of them to drop the table from the db and see if it would recreated, alas that too does not seem to work, as I now get this log error for that item with the missing table.
2019-04-13 13:11:00.579 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Could not store item 'HO_Temp_Mode' in database with statement 'INSERT INTO Item44 (TIME, VALUE) VALUES(?,?) ON DUPLICATE KEY UPDATE VALUE=?;': Table 'OpenHAB.Item44' doesn't exist
I have changed the MySQL tables that correspond to the OH items I modified from Number to Switch to be value type char rather than double and this seems to corrected the issue.
I also created a new table for the one I deleted and that seems fine too.
The question remains on how should a item be modofed to be a different element type without all this drama?
If someone can provide the correct process I should have followed that would be great
If you look at that problem from the perspective of the database and more special from the /your intended use of the database (display on a chart?) it seems obvious that there is no “easy” way around it. How should a database automatically convert the changed datatype in order the keep any consistency? Image the change from boolean to a number.
If you change the datatype, you have fo take care. At least IMHO!
I can see that from a database but that is why there is code wrapped around it that interfaces betewwqen the user and the database to look after the user (and protect the database) I am fortunate that I have just enough skills to go into a SQL manager, and change values, to get my self out of the mess.
Does this mean that once we have associated an item type with a name and a db we should abandon the name and create a new one rather than change its type?
I am all about the user experience rather than difficulties placed on the database.
Thanks thanks as I got there in the end, just wanted to make sure I was not missing something easy like a process do X followed by Y then restart and all is well, type of thing.
BTW, my intended use was for state persistence (restore on start).
Then the previous error makes sense indeed, as there is no way to fit a string (“ON” or “OFF”) in a double, giving you the error you had.
Other users have used this approach:
From a look at the code (found here, if you are interested), another option seems to be that you remove the entries for the specific items from the Items table (while OpenHAB is stopped). Once OpenHAB is restarted, the persistence service should automatically make a new table for the “new” items (the new version of the items) and adding that table to the Items table in the process.