• 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.
    • BGB
      BG
      last edited by

      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

      1 Reply Last reply Reply Quote 0
      • BGB
        BG
        last edited by

        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

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

          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,

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

            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.

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

              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.

              1 Reply Last reply Reply Quote 0
              • 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