Event Handlers are lost after upgrade to 2.8.4

[BG]

Hello Phil,
I am sorry if I am being dense. I could not get the script to work. I tried to use the JSON import function from the "Configuration Import/Export" page on Mango but got an error: "JSON parse error: line=1, column=1: Value is not null, true, false, or a number"

I also tried the script as an executable from the Linux shell. but got various errors initially and when I tried different options.

Any advice would be greatly appreciated.

Cheers

Brian

[BG]

Hello Again Phil,
I managed to sort the problem another way.

I cleared the eventHandlers table from the MySQL database.
Using the configuration back up, I isolated the "Event Handlers" section and copied that section into the import page on Mango.

It has repopulated the eventHandlers table with all of the correct information.

Initially I wasn't sure if clearing that table would cause other issues but did it as an experiment.

Thank you for your time on this.

Cheers

Brian

[phildunlap]

Hi Brian,

I spent some time reading the code and didn't see how to achieve what you describe in XIDs changing, and I did several more upgrades of every kind of event handler, but got nothing. Can you share the contents of your Upgrade12 log in Mango/logs/ ? Or do you happen to have the log from the time of the upgrade?

If you have recreated your event handlers from your MySQL dump, it is possible that the table schema is not right any longer. I would encourage you to do SHOW CREATE TABLE eventHandlers; and share the result.

There was a change to the event handlers table in upgrade 12 to add the eventHandlerType column as we anticipate adding additional sorts of event handlers,

[mihairosu]

Hey Phil,

Is there a plan to fix the missing Event Handlers with an update perhaps?

If not, I'll go back to manually re-creating them.

Please let me know what the best course of action is.

[phildunlap]

I have not been able to isolate or replicate, nor does it appear our credentials to your system are still valid so i cannot check it directly. If we can identify a problem we will definitely do what we can to fix it.

[phildunlap]

Hi guys,

Mihai helped me to identify the problem. Here's a python script one could use to mitigate the effect of the bug. The IDs from the pointEventDetectors were not maintained in the conversion to a different table schema to allow expanding on eventDetectors. This was fixed in the code, but the only way to fix it in people who have upgraded and experienced this problem requires a database backup to have been made. You can find these in Mango/backup/core-database-H2 if you have an H2 database doing backups, or you hopefully have created a MySQL dump yourself (although these are automatically dumped in the backup in the current version).

People affected: Anyone who upgraded to 2.8.x and have eventHandlers connected to eventDetectors where there are empty IDs in the pointEventDetectors (old) table. So, if you have IDs 1, 2, 3, 4, 5 you are unaffected, but if you deleted an data point's event detector at some point and had 1, 4, 16, 26, 44 you are likely affected. You can check by trying to export your Event Handlers and trying to reimport them. Do some fail because there is no detectorXID?

This python script can read the H2 backup script or a mysql dump and generate some SQL statements to set things right. To use, unzip your H2 backup (if applicable), change the variable pointing to the 'script.sql' file in the Python, run the Python script, paste the result into your SQL console or H2 web console, start or restart Mango. Truely sorry for the inconvenience!

import re

#This python script reads SQL from an database dump (unzipped H2 backup, mysql dump) and generates statements to change IDs based on XIDs
# in the eventDetectors table, due to an error in 2.8.0, 2.8.2, and 2.8.4. The backup database must be a pre-2.8 backup to restore the IDs
# these statements are written to the console and "changeDetectorId.sql" in the directory run.

restoreScript = open("/path/to/Mango/backup/core-database-H2/script.sql")
outputFile = open("changeDetectorId.sql", "w+")

atInsert = False
pairs = []
for line in restoreScript :
	if not atInsert : 
		if re.search("INSERT INTO PUBLIC.POINTEVENTDETECTORS", line) is not None :
			atInsert = True
		elif re.search("INSERT INTO `pointEventDetectors` VALUES", line) is not None :
			#It's a mysql dump, everything is on this line.'
			for match in re.findall("\((\d+),('.*?'),", line) :
				print match
				pairs.append(match)
			break
		continue
	else :
		lineData = re.search("^\((\d+), ('.*?'),", line)
		if lineData is None :
			break
		pairs.append((lineData.group(1), lineData.group(2))) #these will be sorted and we'll want to reverse'
		
restoreScript.close()

pairs.reverse()
for pair in pairs :
	text = "UPDATE eventDetectors SET id=%d WHERE xid=%s;" % (int(pair[0]), pair[1])
	print text
	outputFile.write(text+"\n")
	
outputFile.close()

[mihairosu]

Hey Phil,

Here are the steps I took:

Unzipped the latest H2 database, and the file was indeed named script.sql.

I ran your script and it generated the file changeDetectorID.sql, but it was blank.

There were no error messages. I also made sure the file had execute permissions, even tried running it with sudo.

[phildunlap]

Hmm. This would have been the H2 backup from before the upgrade?

I would wager if you search for the text "INSERT INTO PUBLIC.POINTEVENTDETECTORS" in your backup you either won't find anything, or it will have an empty list of detectors.

If instead searching the file for "INSERT INTO PUBLIC.EVENTDETECTORS" finds something then you know you have a backup from after the upgrade.

[mihairosu]

Oh, no this is an H2 AFTER the upgrade. I don't have any backups from before the upgrade. I got a 10 file limit, daily backup going.

What do you suggest I do?

[phildunlap]

As I recall from the other thread, your XIDs are very meaningful. So I'd try some steps somewhat like this,

0. Accept our sincere apology this has happened.
1. SQL: SELECT id, xid, alias, eventTypeRef2 FROM eventHandlers WHERE eventTypeName='DATA_POINT';
2. Copy the table from (1) into a notepad
3. SQL: SELECT id, xid FROM eventDetectors WHERE sourceTypeName='DATA_POINT' ORDER BY ID DESC;
4. For each event detector in that list, see if the XID(s) for the handler(s) can be resolved for the XID for the detector. If someone is unlike you and instead has random XIDs, this could be nearly impossible. For each match, run UPDATE eventHandlers SET eventTypeRef2=[your matched detector id here] WHERE xid='[the handler xid]';

[mihairosu]

0. No worries, it happens.

Sounds good. I'll do that at some point.

[phildunlap]

One could also reimport their eventHandlers JSON from a backup prior to the upgrade (adding more solutions for completeness).

[mihairosu]

I tried

SELECT id, xid, alias, typeref2 FROM eventHandlers WHERE eventTypeName='DATA_POINT';

in the SQL console with Submit Query and this is the error I'm getting:

org.h2.jdbc.JdbcSQLException: Column "TYPEREF2" not found; SQL statement: SELECT id, xid, alias, typeref2 FROM eventHandlers WHERE eventTypeName='DATA_POINT'; [42122-181]

[phildunlap]

Yes, my mistake. It should have been "eventTypeRef2". I will update those posts.

[mihairosu]

I just wanted to double check that I understood correctly before I go ahead. Is this right?

[phildunlap]

Very useful image!

Not quite, the square brackets don't belong. Delete them and it looks good.

You may wish to restart Mango when you're done.

[mihairosu]

Everything looks good now!