SQL persistence issue

[idkpmiller (Paul Miller)]

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?

LOG

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.

Thanks

Paul

[RolfV (Rolf Vermeer)]

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:

@idkpmiller

I changed a bunch of items from type Number to type Switch.

You realize that a Number and a Switch have different data types, especially when looking from a MySQL perspective?

[idkpmiller (Paul Miller)]

Hi,
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

Thanks
Paul

[idkpmiller (Paul Miller)]

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

Thanks

Paul

[opus (Jürgen Baginski)]

@idkpmiller

The question remains on how should a item be modofed to be a different element type without all this drama?

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!

[idkpmiller (Paul Miller)]

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).

Regards
Paul

[RolfV (Rolf Vermeer)]

@idkpmiller

Value is double

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.

@idkpmiller

The question remains on how should a item be modofed to be a different element type without all this drama?

Other users have used this approach:

(Mysql) persistence: string items / number items change

I never got the recreate feature working, probably this is some compatibility issue with portgreSQL. I ended up with a small python script to create views for the tables I am interested in with a human friendly name. The same approach should work with changing the type of the entries: Stop openhab Rename the existing table to some temporary name Create a new table with the same name schema used by the persistence service and the correct types for the columns Select the entries from the exist…

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.

[opus (Jürgen Baginski)]

@idkpmiller

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 don’t know of any better way and I would suggest such.
The procedure linked by @RolfV is IMHO nothing for a “normal” user.

[idkpmiller (Paul Miller)]

@RolfV

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.

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.

Thanks

Paul