(Mysql) persistence: string items / number items change

Tags: #<Tag:0x00007f616f874128> #<Tag:0x00007f616f874010> #<Tag:0x00007f616f886da0>

I changed some items from string to number, because of a typo. The items are already persisted as String in MySQL in my case, therefore, they are saved in varchar(20000). As I changed them to Number in my items-file, they still are saved as varchar(20000).
I assume, the persistence won’t be changed, if the already persisted item changed ist type? So you don’t mess up the persistence, if e.g. I loose persisted information if you Change from String - varchar(20000) to Switch - char(3)
But is there an easy way to force an update? We’re talking 30+ items… I’d have to identify and change manually in MySQL :wink:

I was helping someone trying to get recreateTable (or something like that, I can’t find the thread anymore and I posted to it just yesterday) on his MySQL working. I wasn’t much help on that but it would appear that there is something in place to do that.

At a minimum you should be able to drop the table for those Items and maybe touch the mysql.persist file and the tables should be recreated as the proper datatype.

I don’t have anything more to offer sadly.

1 Like

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:

  1. Stop openhab
  2. Rename the existing table to some temporary name
  3. Create a new table with the same name schema used by the persistence service and the correct types for the columns
  4. Select the entries from the existing table, convert the types and write them to the new table.

Probably this could be done with a fancy SQL statement as well, but using Python was quicker for me. For postgeSQL I used the Psycopg2 module, a similar module should be available for MySQL.

2 Likes