Event Handlers are lost after upgrade to 2.8.4

[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!