mySQL Cleanup?

Hello Guys.
I’m running persistence through MySql - but when setting it up i’ve played around with it, and that created a lot of tables inside the SQL database, where many of them isn’t in use anymore…

If i wanna have a “clean start” could i simply delete all tables in the SQL and the OH will create the correct tables again for the items that should be logged?

What I typically do is drop the table and delete the corresponding ROW in the Items table. The most common scenario for doing this is when I change the name of an item that’s being logged to mysql. So for example, if I drop the table Item23, I also delete the row in the Items table whose ItemId=23.

As per @mhilbush you can drop individual tables and rows in table ‘Items’ which works OK.

But if you want that “clean start” then certainly you can expand that option and drop all the ‘ItemNN’ tables and just clear all the row data from the table ‘Items’. I’d shut down OH first though otherwise it’ll have some runtime references which might confuse it.

Yes ok… i understand that :slight_smile:

Is there a smart way to identify which item_id is linked to item_name? I can see them in the “Items” under “ItemId” and “Itemname” - but else they are only listed as “item0001” and so on

The Items table is the mapping data for Id <=> Name, not sure what you really mean by smart though.

You are poking around in the database so it’s manual but not to tricky if you’re just doing a few tables. If you’ve got a lot of tables to clear but want to retain some then again you’ve got to decide what a lot of tables really means to you.

You could also dump the Items table to excel and quickly create some SQL statements/script to do it. A simple bit of string concatenation to DROP tables and delete data rows from Items etc whether it’s worth scripting depends on how many tables and UI tools you might be using.

Always start with a backup first though!

If you use a recent version of the JDBC persistence, there is a configuration option called tableUseRealItemNames, which will give the tables friendly names. Could make things easier, especially if you are starting from scratch, but there is also a rebuildTableNames option to rename them in-place. I had been using MySQL persistence since OH1, but it was a trivial shift to JDBC. Well, the documentation is a little dicey, but if you have a backup then you can’t hurt anything.

1 Like