SQL persistence issue

Hi all,
As background I currently have RRD and MySQL persistence in play on my setup.

I changed a bunch of items from type Number to type Switch. these items had statepersist tag set which I later removed to temporary stop the error.

here are the items

Switch BR1_Temp_Mode		"Temperature Mode" 				(gTempMode, gBedroomThermostat)		//["homekit:HeatingCooling"]
Switch BR2_Temp_Mode		"Temperature Mode" 				(gTempMode, gBedroom2Thermostat)	//["homekit:HeatingCooling"]
Switch BR3_Temp_Mode		"Temperature Mode" 				(gTempMode, gBedroom3Thermostat)	//["homekit:HeatingCooling"]
Switch BR4_Temp_Mode		"Temperature Mode" 				(gTempMode, gBedroom4Thermostat)	//["homekit:HeatingCooling"]
Switch DR_Temp_Mode			"Temperature Mode" 				(gTempMode, gDiningroomThermostat)	//["homekit:HeatingCooling"]
Switch LR_Temp_Mode			"Temperature Mode" 				(gTempMode, gLivingroomThermostat)	//["homekit:HeatingCooling"]
Switch FR_Temp_Mode			"Temperature Mode" 				(gTempMode, gFamilyroomThermostat)	//["homekit:HeatingCooling"]
Switch GR_Temp_Mode			"Temperature Mode" 				(gTempMode, gGamesroomThermostat)	//["homekit:HeatingCooling"]
Switch HO_Temp_Mode			"Temperature Mode" 				(gTempMode, gOfficeThermostat)		//

Mysql.persist file

// 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

 * 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 {
	// 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


Now onto your question.
Value is double


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.

I don’t know of any better way and I would suggest such.
The procedure linked by @RolfV is IMHO nothing for a “normal” user.

While thats sound simpler. I will have to try it next time.

As OH is supposed to be lookingh to broaden its attraction to more than nerd/geeks then these are the areas we need to call out, so improvements can be made.

Thanks to you all for the assistance. If I get around to testing the process described above I will post the results.