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