• Recent
    • Tags
    • Popular
    • Register
    • Login

    Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.

    Radix IoT Website Mango 3 Documentation Website Mango 4 Documentation Website Mango 5 Documentation Website

    Event Handlers are lost after upgrade to 2.8.4

    User help
    3
    21
    10.4k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • phildunlapP
      phildunlap
      last edited by 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()
      
      1 Reply Last reply Reply Quote 0
      • M
        mihairosu
        last edited by

        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.

        1 Reply Last reply Reply Quote 0
        • phildunlapP
          phildunlap
          last edited by 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.

          1 Reply Last reply Reply Quote 0
          • M
            mihairosu
            last edited by

            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?

            1 Reply Last reply Reply Quote 0
            • phildunlapP
              phildunlap
              last edited by phildunlap

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

              1. Accept our sincere apology this has happened.
              2. SQL: SELECT id, xid, alias, eventTypeRef2 FROM eventHandlers WHERE eventTypeName='DATA_POINT';
              3. Copy the table from (1) into a notepad
              4. SQL: SELECT id, xid FROM eventDetectors WHERE sourceTypeName='DATA_POINT' ORDER BY ID DESC;
              5. 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]';
              1 Reply Last reply Reply Quote 0
              • M
                mihairosu
                last edited by

                1. No worries, it happens.

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

                1 Reply Last reply Reply Quote 0
                • phildunlapP
                  phildunlap
                  last edited by

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

                  1 Reply Last reply Reply Quote 0
                  • M
                    mihairosu
                    last edited by 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]
                    
                    
                    1 Reply Last reply Reply Quote 0
                    • phildunlapP
                      phildunlap
                      last edited by

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

                      1 Reply Last reply Reply Quote 0
                      • M
                        mihairosu
                        last edited by

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

                        0_1487021383769_Fixing Event Notifications.png

                        1 Reply Last reply Reply Quote 0
                        • phildunlapP
                          phildunlap
                          last edited by 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.

                          1 Reply Last reply Reply Quote 0
                          • M
                            mihairosu
                            last edited by

                            Everything looks good now!

                            1 Reply Last reply Reply Quote 0
                            • First post
                              Last post