Event Handlers are lost after upgrade to 2.8.4

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