9 Replies Latest reply on Mar 23, 2006 5:57 PM by Jeremy Levy

    JBoss 4.0.3 + mySQL and TIMERS and HILOSEQUENCES

    Patrick Brannigan Newbie

      I changed the database to mySQL and removed all references to the Hypersonic database. But now I get an error with the EJBTimerService and KeyGeneratorFactory starting up.

      If the tables TIMERS and HILOSEQUENCES exists I get the following report:

      2005-10-16 18:21:33,910 ERROR [org.jboss.deployment.scanner.URLDeploymentScanner] Incomplete Deployment listing:

      --- MBeans waiting for other MBeans ---
      ObjectName: jboss.ejb:service=EJBTimerService,persistencePolicy=database
      State: FAILED
      Reason: java.sql.SQLException: Table 'TIMERS' already exists
      I Depend On:
      jboss.jca:service=DataSourceBinding,name=DefaultDS

      ObjectName: jboss:service=KeyGeneratorFactory,type=HiLo
      State: FAILED
      Reason: java.sql.SQLException: Table 'HILOSEQUENCES' already exists
      I Depend On:
      jboss:service=TransactionManager
      jboss.jca:service=DataSourceBinding,name=DefaultDS

      --- MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM ---
      ObjectName: jboss:service=KeyGeneratorFactory,type=HiLo
      State: FAILED
      Reason: java.sql.SQLException: Table 'HILOSEQUENCES' already exists
      I Depend On:
      jboss:service=TransactionManager
      jboss.jca:service=DataSourceBinding,name=DefaultDS

      ObjectName: jboss.ejb:service=EJBTimerService,persistencePolicy=database
      State: FAILED
      Reason: java.sql.SQLException: Table 'TIMERS' already exists
      I Depend On:
      jboss.jca:service=DataSourceBinding,name=DefaultDS

      If I drop the tables the server starts up just fine. Is there a config setting that allows dropping of those tables on shutdown?...or is there a way it will not error out when the tables exists and just use the?

      Any help is appreciated.
      -tc

        • 1. Re: JBoss 4.0.3 + mySQL and TIMERS and HILOSEQUENCES
          NotLongerVisible NotLongerVisible Newbie

          hello,

          i have the same error also with the version JBoss 4.0.3SP1. Is this just a Warning and works well or is this an Error ? (sry for this stupid Question, but i evaluate JBoss in the moment and dont want to spend time in things that can not work....)

          stefan h.

          • 2. Re: JBoss 4.0.3 + mySQL and TIMERS and HILOSEQUENCES
            Darran Lofthouse Master

            Unless you are using the KeyGeneratorFactory you can remove the file 'uuid-key-generator.sar' from the deploy folder and it will no longer be a problem.

            I have just configured a JBoss 4.0.3SP1 installation to use MySQL with no Hypersonic and I am not getting any errors regarding the TIMERS table.

            Can you describe what steps you have taken to migrate to MySQL and post the error you are seeing.

            Did you have any problems with JMS as part of your migration?

            • 3. Re: JBoss 4.0.3 + mySQL and TIMERS and HILOSEQUENCES
              NotLongerVisible NotLongerVisible Newbie

              hello darranl,

              thank you for your help. removing the uuid-key-generator.sar removes only the warning about th HILOSEQUENCES table. the warnings/exceptions about the TIMERS table still comes up each restart. for me, it looks like there is a bug when the JMS comes up and checks for the tables.

              here is my setup :
              os : slowlaris 9 04/04
              jdk : jdk1.5.0_02
              mysql : mysql-max-4.1.14-sun-solaris2.9-sparc-64bit
              mysql connector/j : mysql-connector-java-3.1.11
              jboss : jboss-4.0.3SP1.zip

              the setup procedure :

              # (g)unzip (and untar) the jboss package
              $ unzip jboss-4.0.3SP1.zip

              # gunzip and untar the mysql connector/j package
              $ gunzip mysql-connector-java-3.1.11.tar.gz
              $ tar -xvf mysql-connector-java-3.1.11.tar

              # copy the mysql connector in the server/all/lib folder
              $ cp mysql-connector-java-3.1.11/mysql-connector-java-3.1.11-bin.jar jboss-4.0.3SP1/server/all/lib/

              # configure jboss to run with mySql as Default Database
              $ cp jboss-4.0.3SP1/docs/examples/jca/mysql-ds.xml jboss-4.0.3SP1/server/all/deploy/

              # edit jdbc connection
              #
              $ vi jboss-4.0.3SP1/server/all/deploy/mysql-ds.xml
              .

              <local-tx-datasource>
              <jndi-name>DefaultDS</jndi-name>
              <connection-url>jdbc:mysql://localhost:3306/jbossdb</connection-url>
              <driver-class>com.mysql.jdbc.Driver</driver-class>
              <user-name>nobody</user-name>
              jboss
              .
              .

              # remove hibernate datasource
              $ rm jboss-4.0.3SP1/server/all/deploy/hsqldb-ds.xml

              # configure jms
              $ cp jboss-4.0.3SP1/docs/examples/jms/mysql-jdbc2-service.xml jboss-4.0.3SP1/server/all/deploy-hasingleton/jms/

              # edit mysql-jdbc2-service.xml and change to DataSource DefaultDS
              # correct the table Alias bug
              $ vi jboss-4.0.3SP1/server/all/deploy-hasingleton/jms/mysql-jdbc2-service.xml
              .
              .
              <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=DefaultDS
              .
              .

              .
              SELECT_MAX_TX = SELECT MAX(TXID) FROM (SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS UNION SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES) AS TR_TABLE
              .
              .


              # remove default hibernate jms service description
              $ rm jboss-4.0.3SP1/server/all/deploy-hasingleton/jms/hsqldb-jdbc2-service.xml

              # make MySQL as standard database
              $ vi jboss-4.0.3SP1/server/all/conf/standardjaws.xml
              .
              .
              .

              java:/DefaultDS
              <type-mapping>mySQL</type-mapping>
              false
              .
              .

              # (optional, )
              # vi jboss-4.0.3SP1/server/all/conf/standardjbosscmp-jdbc.xml

              # start/stop scripts need the x flag, zip does not know this ;-)
              $ chmod +x jboss-4.0.3SP1/bin/run.sh
              $ chmod +x jboss-4.0.3SP1/bin/shutdown.sh


              # setup ownership etc.
              $ chown -R nobody:nogroup jboss-4.0.3SP1

              # install and configure start/stop script


              # startup the jboss instance :
              $ /etc/init.d/jboss4.server start

              # this calls jboss-4.0.3SP1/bin/run.sh -c all


              an now parts from the log :


              =========================================================================

              JBoss Bootstrap Environment

              JBOSS_HOME: /opt/jboss-4.0.3SP1

              JAVA: /opt/jdk1.5.0_02/bin/sparcv9/java

              JAVA_OPTS: -server -Xms128m -Xmx128m -Dprogram.name=run.sh

              CLASSPATH: /opt/jboss-4.0.3SP1/bin/run.jar:/opt/jdk1.5.0_02/lib/tools.jar

              =========================================================================

              19:45:37,775 INFO [Server] Starting JBoss (MX MicroKernel)...
              19:45:37,782 INFO [Server] Release ID: JBoss [Zion] 4.0.3SP1 (build: CVSTag=JBoss_4_0_3_SP1 date=200510231054)
              19:45:37,794 INFO [Server] Home Dir: /opt/jboss-4.0.3SP1
              19:45:37,795 INFO [Server] Home URL: file:/opt/jboss-4.0.3SP1/
              19:45:37,801 INFO [Server] Patch URL: null
              19:45:37,802 INFO [Server] Server Name: all
              19:45:37,803 INFO [Server] Server Home Dir:
              .
              .
              .
              java.sql.SQLException: Table 'TIMERS' already exists
              .
              .
              19:49:00,491 WARN [ServiceController] Problem starting service jboss:service=KeyGeneratorFactory,type=HiLo
              java.sql.SQLException: Table 'HILOSEQUENCES' already exists
              at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2926)
              at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
              at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
              at com.mysql.jdbc.Connection.execSQL(Connection.java:2972)
              at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
              at com.mysql.jdbc.Statement.executeUpdate(Statement.java:929)
              .
              .
              .
              19:49:06,247 ERROR [URLDeploymentScanner] Incomplete Deployment listing:

              --- MBeans waiting for other MBeans ---
              ObjectName: jboss.ejb:service=EJBTimerService,persistencePolicy=database
              State: FAILED
              Reason: java.sql.SQLException: Table 'TIMERS' already exists
              I Depend On:
              jboss.jca:service=DataSourceBinding,name=DefaultDS

              ObjectName: jboss:service=KeyGeneratorFactory,type=HiLo
              State: FAILED
              Reason: java.sql.SQLException: Table 'HILOSEQUENCES' already exists
              I Depend On:
              jboss:service=TransactionManager
              jboss.jca:service=DataSourceBinding,name=DefaultDS

              --- MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM ---
              ObjectName: jboss:service=KeyGeneratorFactory,type=HiLo
              State: FAILED
              Reason: java.sql.SQLException: Table 'HILOSEQUENCES' already exists
              I Depend On:
              jboss:service=TransactionManager
              jboss.jca:service=DataSourceBinding,name=DefaultDS

              ObjectName: jboss.ejb:service=EJBTimerService,persistencePolicy=database
              State: FAILED
              Reason: java.sql.SQLException: Table 'TIMERS' already exists
              I Depend On:
              jboss.jca:service=DataSourceBinding,name=DefaultDS

              • 4. Re: JBoss 4.0.3 + mySQL and TIMERS and HILOSEQUENCES
                Patrick Brannigan Newbie

                Just a note. It does seem to be a problem with MySQL. I took the same steps to setup JBoss 4.0.3 with Oracle as the default database and it worked fine. But I do have the above issue with MySQL.

                • 5. Re: JBoss 4.0.3 + mySQL and TIMERS and HILOSEQUENCES
                  NotLongerVisible NotLongerVisible Newbie

                  hello darranl, tc,

                  darranl, can you post your mysql / connector/j version ? i try to downgrade (because i fail to bild jboss from source in the moment....)

                  thanx in advance !

                  stefan h.

                  • 6. Re: JBoss 4.0.3 + mySQL and TIMERS and HILOSEQUENCES
                    NotLongerVisible NotLongerVisible Newbie

                    hello darranl, tc,

                    mysql-connector-java-3.1.10-bin.jar works for me, no warnings/exceptions.

                    stefan h.

                    • 7. Re: JBoss 4.0.3 + mySQL and TIMERS and HILOSEQUENCES
                      Darran Lofthouse Master

                      The version of the driver I have been using was 3.1.10 and I also tried 3.1.11

                      I was originaly using MySQL 4.1.10 but after correcting the JMS issue JBoss was lockin up for me so I upgraded to 4.1.12 and my deadlock was gone.

                      This is all running on Windows XP.

                      Which table type have you been using with MySQL? All of my tests were with InnoDB tables.

                      I have been meaning to upgrade my MySQL version so I will try 4.1.14 tomorrow and see how it works for me.

                      I am not sure if I understand your last message, are you saying you have found a combination that works for you now?

                      • 8. Re: JBoss 4.0.3 + mySQL and TIMERS and HILOSEQUENCES
                        Ivan Kozhuharov Newbie

                        Nop it is the same situation with:
                        mysql-connector-java-3.1.11-bin.jar and MySQL v4.1.14

                        And here is mine handmade how-to:

                        This works with jboss-4.0.3SP1 and mysql-connector-java-3.1.11-bin.jar and MySQL v4.1.14
                        tested on Debian x86 and x86_64

                        replace
                        <your_user> with a user name /no "<>' unless you want them/
                        <your_password> with a password :) /no "<>' unless you want them/
                        <your_db_name> with DB name I use something like "jboss_do_not_mess_with" /no "<>' unless you want them/

                        pre-install:
                        1. on Debian make sure your /etc/hosts file has a line like:
                        127.0.0.1 localhost localhost
                        (note no "localhost.localdomain")
                        reload if you change it.
                        2. mysql -uroot (-p if you already have password)
                        3. mysql> create table <your_db_name>;
                        4. GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON <your_db_name>.* TO <your_user>@localhost IDENTIFIED BY '<your_password>';
                        5. mysql>flush privileges;
                        6. mysql>quit
                        7. test it:
                        mysql> -u<your_user> -p
                        <your_password>
                        mysql>use <your_db_name>;
                        mysql>show tables; /there shouldn't be any/
                        mysql>quit

                        switching from Hypersonic to MySQL:
                        1. copy the driver to /lib/
                        2. remove /deploy/hsqldb-ds.xml, remove /data/hypersonic directory (just in case)
                        3. copy docs/examples/mysql-ds.xml to /deploy/mysql-ds.xml
                        4. edit /deploy/mysql-ds.xml to insert your host/database/username/password
                        5. edit /deploy/mysql-ds.xml change MySqlDS to DefaultDS
                        6. copy /docs/examples/jms/mysql-jdbc2-service.xml deploy/jms/mysql-jdbc2-service.xml
                        7. edit deploy/jms/mysql-jdbc2-service.xml change MySqlDS to DefaultDS (line 47)
                        8. edit deploy/jms/mysql-jdbc2-service.xml change :
                        SELECT_MAX_TX = SELECT MAX(TXID) FROM (SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS UNION SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES) /line 53/ to:
                        SELECT_MAX_TX = SELECT MAX(TXID) FROM (SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS UNION SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES) AS TR_TABLE
                        9. rename hqsql-jdbc-state-service.xml to mysql-jdbc-state-service.xml (???)
                        10. edit /conf/login-config.xml change :
                        <application-policy name = "HsqlDbRealm"> to <application-policy name = "MySqlDbRealm">
                        11. edit /conf/login-config.xml insert your username and password into :
                        ...
                        <module-option name = "principal">username</module-option>
                        <module-option name = "userName">username</module-option>
                        <module-option name = "password">password</module-option>
                        ...
                        12. edit /conf/standardjaws.xml change :
                        <type-mapping>HypersonicSQL</type-mapping>
                        to:
                        <type-mapping>mySQL</type-mapping>

                        • 9. Re: JBoss 4.0.3 + mySQL and TIMERS and HILOSEQUENCES
                          Jeremy Levy Newbie

                          I was having the same problem and solved it by upgrading to mysql-connector-java-3.1.12