10 Replies Latest reply on Sep 17, 2009 5:07 PM by joe.marques

    Jopr 2.3.0 not updating database schema?

    Alexandre Gomes Newbie

      Hello all,

      During installation of 2.3.0 version, I set the same database used by a previously installed version 2.2.0 and chose to keep existing data. Everything went well, until I started to navigate in web gui.

      First, all the alerts templates were gone. Then, every time I clicked on a JBoss AS resource instance, I got

      java.sql.SQLException: ORA-00904: "ALERTDEFIN0_"."GROUP_ALERT_DEF_ID": invalid identifier


      I seems some schema update is needed. Did I miss any installation step (yes, I read http://jopr.org/confluence/display/JOPR2/Upgrading+the+Jopr+Server)?

      The complete stack trace is available on http://pastebin.com/m76a36a74

      thanks in advance

        • 1. Re: Jopr 2.3.0 not updating database schema?
          mazz Master

          Can you go to your server's logs directory (<jopr-install-dir>/logs) and get the dbupgrade logs. See if there is any oddities/errors in there - if possible, pastebin that too (or upload it somewhere that we can get it).

          Obviously, this should not have happened, the db upgrade should have been able to get the schema upgraded properly. We need to see if the dbupgrade failed for some reason or if its just a bug in the Java code that needs to be fixed.

          • 2. Re: Jopr 2.3.0 not updating database schema?
            Newbie

            Yes, this certainly looks like the dbupgrade didn't complete successfully. The invalid identifier it's referring to is the column prefixed with GROUP_, which should have been added as part of the upgrade steps from 2.2 to 2.3

            • 3. Re: Jopr 2.3.0 not updating database schema?
              mazz Master

              working on a fix - this is goign to be an emergency fix because it looks like you won't be able to upgrade from an earlier version of Jopr to Jopr 2.3.0.

              Do not install 2.3.0 if you are upgrading from an earlier version.

              We'll get a fix out as fast as we can.

              • 4. Re: Jopr 2.3.0 not updating database schema?
                Alexandre Gomes Newbie

                A little late, but always helpful.

                First, we couldn't find dbupgrade in logs folder (Wind2008 + JDK 1.6.0_10). So, we installed Jopr 2.3.0 on another machine (WinXP + JDK 1.6.0_03) and it had better results! All the tables were created and dbupgrade file was right there.

                However, when we tried to run Jopr Server, we got

                java.lang.IllegalArgumentException: Not supported: indent-number


                and, googling around, we found this to be a bug in JDK. So, we updated JDK to update 10, which makes Jopr Server to start up well, but not the Agent:

                Caused by: java.lang.IllegalArgumentException: Plugin [Augeas] is required by plugins [[Apt]] but it does not exist in the dependency graph yet


                For some reason, rhq-augeas-plugin-1.3.0.jar file was not being downloaded from Server to the Agent.

                So, we decided not to work with 2.3.0 anymore and come back to 2.2.0 but, for our surprise, the same Augeas error was poping up.

                Hacking around, we found RHQ_PLUGIN table containing only 2.3.0 and 1.3.0 plugin versions! So, tip #1:

                Remember to unregister plugins when downgrading versions.


                Manually, we cleaned RHQ_PLUGIN table and restarted the Server expecting to have 2.2.0 and 1.2.0 plugins deployed again.

                Everything went well, expect for 4 plugins (can't remember which of them now). When trying to register them, Jopr throws a LockAquisitionException caused by deadlock error in Oracle database.

                We did it some times and the same error repeated every time. We used Oracle tools to monitor locks allocation and most session locks was happening around RHQ_CONFIG and RHQ_CONFIG_PROP_DEF tables.

                Yes, we did

                GRANT SELECT ON sys.dba_pending_transactions TO jon;
                GRANT SELECT ON sys.pending_trans$ TO jon;
                GRANT SELECT ON sys.dba_2pc_pending TO jon;
                GRANT EXECUTE ON sys.dbms_system TO jon;


                As long as the database data has been messed up, our next try will be installing Jopr 2.3.0 from scratch, overwriting the existing schema.

                []s

                • 5. Re: Jopr 2.3.0 not updating database schema?
                  Alexandre Gomes Newbie

                  Well, good and bad news....

                  Installing Jopr 2.3.0 from scratch worked like a charm. However, minutes after everything was configured, we realized version 2.3.1 was just released. So, we decided to upgrade from 2.3.0 to 2.3.1 and, for the sake of Murphy, it didn´t work.


                  Failed to save properties and fully deploy - Jopr Server will not function properly Cause: java.lang.RuntimeException:Cannot run ANT on script [D:\jopr-server-2.3.1\logs\db-upgrade.xml]. Cause: D:\jopr-server-2.3.1\logs\db-upgrade.xml:32: Failed to upgrade - error in spec version [2.58]. Cause: Error executing the task [org.rhq.core.db.ant.dbupgrade.SST_AddColumn] in schema spec version [2.58]. Cause: Failed to add the new column. Cause: java.sql.SQLException: ORA-01430: column being added already exists in table -> org.apache.tools.ant.BuildException:Failed to upgrade - error in spec version [2.58]. Cause: Error executing the task [org.rhq.core.db.ant.dbupgrade.SST_AddColumn] in schema spec version [2.58]. Cause: Failed to add the new column. Cause: java.sql.SQLException: ORA-01430: column being added already exists in table -> org.apache.tools.ant.BuildException:Failed to upgrade - error in spec version [2.58]. Cause: Error executing the task [org.rhq.core.db.ant.dbupgrade.SST_AddColumn] in schema spec version [2.58]. Cause: Failed to add the new column. Cause: java.sql.SQLException: ORA-01430: column being added already exists in table -> org.apache.tools.ant.BuildException:Error executing the task [org.rhq.core.db.ant.dbupgrade.SST_AddColumn] in schema spec version [2.58]. Cause: Failed to add the new column. Cause: java.sql.SQLException: ORA-01430: column being added already exists in table -> org.apache.tools.ant.BuildException:Failed to add the new column. Cause: java.sql.SQLException: ORA-01430: column being added already exists in table -> java.sql.SQLException:ORA-01430: column being added already exists in table [SQLException=ORA-01430: column being added already exists in table ]


                  So, as long as we don´t want to reconfigure everything again, let´s 2.3.0 run...

                  []s

                  • 6. Re: Jopr 2.3.0 not updating database schema?
                    mazz Master

                    Yeah, this is why I mentioned, "Do not install 2.3.0 if you are upgrading from an earlier version."

                    If you did upgrade to it, you will have received those errors you saw in which case you needed to start with a completely empty database before trying to install another version. When you upgraded to 2.3.0, your database was hosed at that point and you needed to do some manual things to get it back (RHQ_SYSTEM_CONFIG for one needed to be changed before you should have tried again, along with as you saw RHQ_PLUGIN and probably other things).

                    This was completely our fault, we hosed the upgrade script.

                    We have since uploaded 2.3.1 to the download site. Use that. Do NOT use 2.3.0.

                    BTW: you cannot "downgrade" Jopr or "unregister" plugins - there is no support for that in the code. Its something we want to do in the future, but once you get a plugin, its there for good unless you do some manual SQL tweeking. Please see
                    http://jira.rhq-project.org/browse/RHQ-2
                    and the somewhat related
                    http://jira.rhq-project.org/browse/RHQ-1.

                    In short, blow away your database completely, create an empty database and install Jopr 2.3.1.

                    Sorry for the problems.

                    • 7. Re: Jopr 2.3.0 not updating database schema?
                      mazz Master

                      Yeah, alegomes - 2.3.0 was completely bad. Totally. Don't use it. Totally our bad - we're falling on our sword.

                      Blow everything away and install 2.3.1 fresh.

                      • 8. Re: Jopr 2.3.0 not updating database schema?
                        Alexandre Gomes Newbie

                        Oh gosh.

                        Except the schema upgrade thing, is there any other issue censuring 2.3.0? I've spent the whole morning installing it on an empty database and setting everything up. If there is nothing else disapproving 2.3.0, I'd rather keep it working instead of spend another day reconfiguring everything again on 2.3.1.

                        thanks

                        • 9. Re: Jopr 2.3.0 not updating database schema?
                          mazz Master

                          I don't think you'll be able to upgrade from 2.3.0 to 2.3.1. But other than that, 2.3.0 should work fine - it was just the db upgrade that was bad.

                          • 10. Re: Jopr 2.3.0 not updating database schema?
                            Newbie

                            I'm going to put together a sql script that you can run if you happen to be on 2.3.0

                            After you run the script, upgrades from 2.3.0 to any other version should work. Stay tuned.