I want to take advantage of the holiday shutdown to do a bit of a tidy-up our OPC tag folder structure which was badly set up initially and contains misplaced tags and spelling mistakes.
As far as I can see I need to do the following:
- Back up the sqlth_te table (which contains the index and tagpaths used by the Historian) and the projects to be fixed.
- Make the changes to the tag names and tagpaths (taking care to note each change).
- Write queries to update the sqlth_te table tagpaths.
- Open the relevant projects and use Find / Replace to update to the new values.
What am I missing here? Has anyone had this go very well or badly wrong?
You could also use the audit log as it captures tag changes including moves/renames
Job done. Here are a few tips:
- I tried using mySQL Workbench to back up the sqlth_te tables but it was making very slow progress. This seems to be a bug in mySQL. In the end I executed the following commands from a CMD window.
C:\>mysqldump -u root -p myDBname sqlth_te > "c:\myDir\myDBname.sqlth_te.sql"
-
I corrected the mis-spelt folder name in the Ignition Tag Browser.
-
I updated the sqlth_te table. In my case one of the tag folders had got an ‘Ò’ in it instead of a regular ‘O’ and we didn’t notice until we had done quite a bit of work. The following query fixed that. See StackOverflow for details on why id <> 0
is required.
UPDATE myDBname.sqlth_te
SET tagpath = REPLACE(tagpath, 'ò', 'o')
WHERE (tagpath LIKE '%ò%' AND id <> 0)
- I opened each project likely to be affected and ran the Find/Replace tool. A couple of points on this:
- Make sure you select Views | All Views and Windows | All Windows.
- The tool is case sensitive. I had to repeat the job for ‘Ò’ and ‘ò’ as Ignition converts some of the paths to lowercase in some places.
Save and launch your application to check.