9 Replies Latest reply on Aug 3, 2009 5:29 PM by 807581

    Can non-clustered MySQL database be used as an enhanced cluster store?

    807581
      I see from the documentation that MySQL Cluster edition can be used as a JDBC store for the cluster, but is it possible to use "regular" MySQL with InnoDB tables as the JDBC store for the cluster? Aside from MySQL not being a properly clustered database, are there any technical issues with using such a configuration?
        • 1. Re: Can non-clustered MySQL database be used as an enhanced cluster store?
          807581
          Short answer: No, there's no technical reason you cannot use a standard MySQL instance.
          Clustered SQL databases give you replication, which enhances your resilience against failure. The databases we list, are those that we have performed our system quality assurance tests against. If you were to purchase a support contract, we could help you with your complete solution, if you use one of the database combinations we list. We do test standard MySQL for use in non-HA clusters. You might want to look at the database FAQs for more details about setting this type of configuration up [http://wiki.glassfish.java.net/Wiki.jsp?page=OpenMQJDBCQuestions]
          A possible rule of thumb is: If your database provides you with enough nines of up-time and data protection, you can consider it for use with Open MQ's JDBC HA implementation. We make no special SQL queries though we may have optimizations for certain databases (these would be to accommodate for certain capabilities in one database or another.) All the database instances must be accessible to all the broker nodes as they will need to access them during a take-over operation.
          You're welcome to try any JDBC database you want to use. You might look at some of the history here, for other community members' experiences with other databases. I'm sure the community would be interested in your results.
          Cheers,
          -- Ed Bratt

          Edited by: ebratt on Mar 3, 2009 7:14 AM
          • 2. Re: Can non-clustered MySQL database be used as an enhanced cluster store?
            807581
            This is what I have configured in the property file: /opt/glassfish/domains/myDomain/imq/instances/imqbroker/props/config.properties:
            imq.brokerid=myInstance
            imq.cluster.clusterid=myCluster
            imq.cluster.transport=tcp
            imq.instanceconfig.version=300
            imq.cluster.ha=true
            imq.cluster.port=7676
            imq.persist.store=jdbc
            imq.persist.jdbc.mysql.user=user
            imq.persist.jdbc.dbVendor=mysql
            imq.persist.jdbc.mysql.password=password
            imq.persist.jdbc.mysql.property.url=jdbc:mysql://mydbserver:3306/my_mq
            Then I set the following property for the command line:
            export IMQ_VARHOME=/opt/glassfish/domains/myDomain/imq
            Set up the database with the following command:
            /opt/glassfish/imq/bin/imqdbmgr create tbl
            Then I run the imqbrokerd command:
            /opt/glassfish/imq/bin/imqbrokerd
            Then I got the following output on the console:
            [09/Mar/2009:13:00:27 CET] IMQ_HOME=/opt/glassfish/imq
            [09/Mar/2009:13:00:27 CET] IMQ_VARHOME=/opt/glassfish/domains/myDomain/imq
            [09/Mar/2009:13:00:27 CET] Linux 2.6.18-92.1.22.el5xen i386 <myhost> (1 cpu) root
            [09/Mar/2009:13:00:27 CET] Java Heap Size: max=194432k, current=194432k
            [09/Mar/2009:13:00:27 CET] [B1060]: Loading persistent data...
            [09/Mar/2009:13:00:28 CET] Using plugged-in persistent store:
            version=410
            brokerid=myInstance
            database connection url=jdbc:mysql://mydbserver:3306/my_mq
            database user=user
            [09/Mar/2009:13:00:28 CET] WARNING [B3044]: Internal Error:
            com.sun.messaging.jmq.jmsserver.util.BrokerException: [B4221]: Failed to load all HA cluster information from store
            at com.sun.messaging.jmq.jmsserver.persist.jdbc.BrokerDAOImpl.getAllBrokerInfos(BrokerDAOImpl.java:1077)
            at com.sun.messaging.jmq.jmsserver.persist.jdbc.JDBCStore.getAllBrokerInfos(JDBCStore.java:2764)
            at com.sun.messaging.jmq.jmsserver.persist.Store.getAllBrokerInfos(Store.java:1212)
            at com.sun.messaging.jmq.jmsserver.cluster.ha.HAClusterManagerImpl$HAMap.updateHAMapForState(HAClusterManagerImpl.java:1498)
            at com.sun.messaging.jmq.jmsserver.cluster.ha.HAClusterManagerImpl$HAMap.updateHAMap(HAClusterManagerImpl.java:1485)
            at com.sun.messaging.jmq.jmsserver.cluster.ha.HAClusterManagerImpl.getKnownBrokers(HAClusterManagerImpl.java:486)
            at com.sun.messaging.jmq.jmsserver.cluster.ha.HAClusterManagerImpl.getConfigBrokers(HAClusterManagerImpl.java:511)
            at com.sun.messaging.jmq.jmsserver.service.HAMonitorService.<init>(HAMonitorService.java:437)
            at com.sun.messaging.jmq.jmsserver.Broker._start(Broker.java:869)
            at com.sun.messaging.jmq.jmsserver.Broker.start(Broker.java:417)
            at com.sun.messaging.jmq.jmsserver.Broker.start(Broker.java:388)
            at com.sun.messaging.jmq.jmsserver.Broker.main(Broker.java:1945)
            Caused by: java.lang.NullPointerException
            at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1103)
            at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5531)
            at com.sun.messaging.jmq.jmsserver.persist.jdbc.BrokerDAOImpl.loadData(BrokerDAOImpl.java:1192)
            at com.sun.messaging.jmq.jmsserver.persist.jdbc.BrokerDAOImpl.getAllBrokerInfos(BrokerDAOImpl.java:1047)
            ... 11 more
            [09/Mar/2009:13:00:28 CET] ERROR [B3199]: Error starting monitor service:
            com.sun.messaging.jmq.jmsserver.util.BrokerException: [B4218]: Failed to load HA cluster information for broker myInstance
            at com.sun.messaging.jmq.jmsserver.persist.jdbc.BrokerDAOImpl.getBrokerInfo(BrokerDAOImpl.java:1008)
            at com.sun.messaging.jmq.jmsserver.persist.jdbc.JDBCStore.getBrokerInfo(JDBCStore.java:2739)
            at com.sun.messaging.jmq.jmsserver.cluster.ha.HAClusterManagerImpl$HAClusteredBrokerImpl.getTakeoverBroker(HAClusterManagerImpl.java:1170)
            at com.sun.messaging.jmq.jmsserver.service.HAMonitorService.<init>(HAMonitorService.java:440)
            at com.sun.messaging.jmq.jmsserver.Broker._start(Broker.java:869)
            at com.sun.messaging.jmq.jmsserver.Broker.start(Broker.java:417)
            at com.sun.messaging.jmq.jmsserver.Broker.start(Broker.java:388)
            at com.sun.messaging.jmq.jmsserver.Broker.main(Broker.java:1945)
            Caused by: java.lang.NullPointerException
            at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1103)
            at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5531)
            at com.sun.messaging.jmq.jmsserver.persist.jdbc.BrokerDAOImpl.loadData(BrokerDAOImpl.java:1192)
            at com.sun.messaging.jmq.jmsserver.persist.jdbc.BrokerDAOImpl.getBrokerInfo(BrokerDAOImpl.java:996)
            ... 7 more

            Anyone having an idea what went wrong?
            • 3. Re: Can non-clustered MySQL database be used as an enhanced cluster store?
              807581
              Hello tronda,

              I think you may be hitting an issue with the mysql jdbc driver.

              see http://bugs.mysql.com/bug.php?id=41484

              Which driver version are you using?

              Another user reported a similar problem and found that everything worked ok after changing from mysql connector version 5.1.7 to 5.0.8.

              Gordon
              • 4. Re: Can non-clustered MySQL database be used as an enhanced cluster store?
                807581
                Great. I downgraded to 5.0.8 driver and now I get a new error message. Hurray - new error ;-)

                Here's the error message I now get:
                Java Runtime: 1.6.0_12 Sun Microsystems Inc. /usr/java/jdk1.6.0_12/jre
                [09/Mar/2009:14:04:31 CET]    IMQ_HOME=/opt/glassfish/imq
                [09/Mar/2009:14:04:31 CET] IMQ_VARHOME=/opt/glassfish/domains/myDomain/imq
                [09/Mar/2009:14:04:31 CET] Linux 2.6.18-92.1.22.el5xen i386 <myhost> (1 cpu) root
                [09/Mar/2009:14:04:31 CET] Java Heap Size: max=194432k, current=194432k
                [09/Mar/2009:14:04:31 CET] Arguments: -Dimq.varhome=/opt/glassfish/domains/myDomain/imq/
                [09/Mar/2009:14:04:31 CET] [B1060]: Loading persistent data...
                [09/Mar/2009:14:04:32 CET] Using plugged-in persistent store:
                        version=410
                        brokerid=myInstance
                        database connection url=jdbc:mysql://mydbserver:3306/my_mq
                        database user=user
                [09/Mar/2009:14:04:32 CET] ERROR [B4210]: Could not start cluster service because Address already in use:
                java.net.BindException: Address already in use
                        at java.net.PlainSocketImpl.socketBind(Native Method)
                        at java.net.PlainSocketImpl.bind(PlainSocketImpl.java:359)
                        at java.net.ServerSocket.bind(ServerSocket.java:319)
                        at java.net.ServerSocket.bind(ServerSocket.java:277)
                        at com.sun.messaging.jmq.net.MQServerSocketFactory.createServerSocket(MQServerSocketFactory.java:92)
                        at com.sun.messaging.jmq.jmsserver.multibroker.fullyconnected.ClusterServiceListener.initTCPListener(ClusterImpl.java:1796)
                        at com.sun.messaging.jmq.jmsserver.multibroker.fullyconnected.ClusterServiceListener.<init>(ClusterImpl.java:1699)
                        at com.sun.messaging.jmq.jmsserver.multibroker.fullyconnected.ClusterImpl.start(ClusterImpl.java:1043)
                        at com.sun.messaging.jmq.jmsserver.multibroker.CommonProtocol.startClusterIO(CommonProtocol.java:248)
                        at com.sun.messaging.jmq.jmsserver.multibroker.ClusterBroadcaster.startClusterIO(ClusterBroadcaster.java:143)
                        at com.sun.messaging.jmq.jmsserver.core.cluster.ClusterBroadcaster.startClusterIO(ClusterBroadcaster.java:116)
                        at com.sun.messaging.jmq.jmsserver.Broker._start(Broker.java:1360)
                        at com.sun.messaging.jmq.jmsserver.Broker.start(Broker.java:417)
                        at com.sun.messaging.jmq.jmsserver.Broker.start(Broker.java:388)
                        at com.sun.messaging.jmq.jmsserver.Broker.main(Broker.java:1945)
                [09/Mar/2009:14:04:32 CET] [B1047]: Shutting down broker...
                [09/Mar/2009:14:04:32 CET] [B1077]: Broadcast good-bye to all connections ...
                [09/Mar/2009:14:04:32 CET] [B1078]: Flushing good-bye messages ...
                [09/Mar/2009:14:04:32 CET] [B1048]: Shutdown of broker complete.
                I found this error message a bit strange since I've only given 7676 as a parameter and with netstat -a didn't return any processes on 7676. Are there some port which should have been set manually? Heartbeat configuration? Other?
                • 5. Re: Can non-clustered MySQL database be used as an enhanced cluster store?
                  807581
                  I think from the stack trace that the error is being thrown when trying to create the cluster manager service.

                  The documentation say the following:
                  -------
                  imq.cluster.port : Port number for cluster connection service

                  A value of 0 specifies that the port number should be allocated dynamically by the Port Mapper. You might need to set a different value, for instance, to specify a static port number for connecting to the broker through a firewall.

                  ...

                  Each connection service is available at a particular port, specified by host name (or IP address)
                  and port number. You can explicitly specify a static port number for a service or have the
                  broker’s PortMapper assign one dynamically. The PortMapper itself resides at the broker’s
                  primary port, which is normally located at the standard port number 7676. (If necessary, you
                  can use the broker configuration property imq.portmapper.port to override this with a
                  different port number.) By default, each connection service registers itself with the PortMapper
                  when it starts up. When a client creates a connection to the broker, the Message Queue client
                  runtime first contacts the PortMapper, requesting a port number for the desired connection
                  service.
                  -------
                  Are you explicitly setting the value of imq.cluster.port or are you using the default option of letting the portmapper choose a value for you?

                  Gordon
                  • 6. Re: Can non-clustered MySQL database be used as an enhanced cluster store?
                    807581
                    I set the imq.cluster.port to 7676 which of course crashed with the broker. Thought the broker port and the cluster port were the same. I changed the config.properties to set the imq.cluster.port to 7979 and then the broker started.

                    Thanks for all the help
                    • 7. Re: Can non-clustered MySQL database be used as an enhanced cluster store?
                      807581
                      Hi,

                      I have a similar problem like the one presented above with the mysql driver. I downgradet it from 5.1.8->5.1.6 ->5.0.8 and still have the problem.
                      Any ideas why it still happens ? Thanks (


                      Java Runtime: 1.6.0_0 Sun Microsystems Inc. /usr/lib64/jvm/java-1.6.0-openjdk-1.6.0/jre
                      [24/Jul/2009:15:23:54 EDT] [B1202]: Broker ora_brokerA is running in HA mode as part of the ora_agent cluster
                      [24/Jul/2009:15:23:54 EDT] [B1004]: Starting the portmapper service using tcp [ 7676, 50, * ] with min threads 1 and max threads of 1
                      [24/Jul/2009:15:23:54 EDT] [B1060]: Loading persistent data...
                      [24/Jul/2009:15:23:54 EDT] Using plugged-in persistent store:
                      version=410
                      brokerid=ora_brokerA
                      database connection url=jdbc:mysql://localhost:3306/HA_mq
                      database user=bogdan
                      [24/Jul/2009:15:23:54 EDT] [B1170]: Auto Creation of plugged-in persistent store is enabled
                      [24/Jul/2009:15:23:54 EDT] [B1193]: Broker has started using a store session of 7,573,966,804,523,492,864 and a broker session of 8,665,808,239,653,526,528
                      [24/Jul/2009:15:23:54 EDT] [B1204]: Starting HA Monitor Service
                      [24/Jul/2009:15:23:54 EDT] [B1206]: Monitor service initializing for brokerid ora_brokerA at mq://127.0.0.2:7676/
                      [24/Jul/2009:15:23:54 EDT] [B1205]: Starting HA Heartbeat Service
                      [24/Jul/2009:15:23:54 EDT] [B1187]: Heartbeat service binds to high.hlevca.com/127.0.0.2:7676
                      [24/Jul/2009:15:23:54 EDT] [B1004]: Starting the heartbeat service using udp ( high.hlevca.com/127.0.0.2:7676 ) with min threads 1 and max threads of 1
                      [24/Jul/2009:15:23:54 EDT] WARNING [B3044]: Internal Error:
                      com.sun.messaging.jmq.jmsserver.util.BrokerException: [B4221]: Failed to load all HA cluster information from store
                      at com.sun.messaging.jmq.jmsserver.persist.jdbc.BrokerDAOImpl.getAllBrokerInfos(BrokerDAOImpl.java:1077)
                      at com.sun.messaging.jmq.jmsserver.persist.jdbc.JDBCStore.getAllBrokerInfos(JDBCStore.java:2764)
                      at com.sun.messaging.jmq.jmsserver.persist.Store.getAllBrokerInfos(Store.java:1212)
                      at com.sun.messaging.jmq.jmsserver.cluster.ha.HAClusterManagerImpl$HAMap.updateHAMapForState(HAClusterManagerImpl.java:1498)
                      at com.sun.messaging.jmq.jmsserver.cluster.ha.HAClusterManagerImpl$HAMap.updateHAMap(HAClusterManagerImpl.java:1485)
                      at com.sun.messaging.jmq.jmsserver.cluster.ha.HAClusterManagerImpl.getKnownBrokers(HAClusterManagerImpl.java:486)
                      at com.sun.messaging.jmq.jmsserver.cluster.ha.HAClusterManagerImpl.getConfigBrokers(HAClusterManagerImpl.java:511)
                      at com.sun.messaging.jmq.jmsserver.multibroker.fullyconnected.ClusterImpl.initBrokerList(ClusterImpl.java:367)
                      at com.sun.messaging.jmq.jmsserver.multibroker.fullyconnected.ClusterImpl.<init>(ClusterImpl.java:233)
                      at com.sun.messaging.jmq.jmsserver.multibroker.ClusterBroadcaster.<init>(ClusterBroadcaster.java:105)
                      at com.sun.messaging.jmq.jmsserver.multibroker.ClusterBroadcaster.<init>(ClusterBroadcaster.java:89)
                      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
                      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
                      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
                      at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
                      at com.sun.messaging.jmq.jmsserver.core.cluster.ClusterBroadcaster.<init>(ClusterBroadcaster.java:87)
                      at com.sun.messaging.jmq.jmsserver.core.cluster.ClusterBroadcaster.<init>(ClusterBroadcaster.java:71)
                      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
                      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
                      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
                      at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
                      at com.sun.messaging.jmq.jmsserver.Broker._start(Broker.java:915)
                      at com.sun.messaging.jmq.jmsserver.Broker.start(Broker.java:417)
                      at com.sun.messaging.jmq.jmsserver.Broker.start(Broker.java:388)
                      at com.sun.messaging.jmq.jmsserver.Broker.main(Broker.java:1945)
                      Caused by: java.lang.NullPointerException
                      at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1111)
                      at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5548)
                      at com.sun.messaging.jmq.jmsserver.persist.jdbc.BrokerDAOImpl.loadData(BrokerDAOImpl.java:1192)
                      at com.sun.messaging.jmq.jmsserver.persist.jdbc.BrokerDAOImpl.getAllBrokerInfos(BrokerDAOImpl.java:1047)
                      • 8. Re: Can non-clustered MySQL database be used as an enhanced cluster store?
                        807581
                        Hi,

                        I found the problem I was picking up a newer driver on the classpath. I does not crash anymore, however, I am getting some exceptions I assume at hearbeat monitoring


                        [26/Jul/2009:20:10:47 EDT] [B1184]: Unsuspected /10.0.0.4:7676 [brokerID=ora_brokerB, brokerSession=6614418657414713600] (seq#=0, ts=1248653447852, interval=2, len=209)
                        [26/Jul/2009:20:10:48 EDT] WARNING [B2122]: Heartbeat timeout from /10.0.0.4:7676 [brokerID=ora_brokerB, brokerSession=6614418657414713600] (seq#=0, ts=1248653447852, interval=2, len=209):
                        java.io.IOException: Invalid argument
                        [26/Jul/2009:20:10:48 EDT] [B1288]: Broker ora_brokerB is indoubt, start to monitor
                        [26/Jul/2009:20:10:54 EDT] WARNING [B2122]: Heartbeat timeout from /10.0.0.4:7676 [brokerID=ora_brokerB, brokerSession=6614418657414713600] (seq#=0, ts=1248653447852, interval=2, len=209):
                        java.io.IOException: Invalid argument
                        [26/Jul/2009:20:11:00 EDT] WARNING [B2122]: Heartbeat timeout from /10.0.0.4:7676 [brokerID=ora_brokerB, brokerSession=6614418657414713600] (seq#=0, ts=1248653447852, interval=2, len=209):
                        java.io.IOException: Invalid argument
                        [26/Jul/2009:20:11:06 EDT] WARNING [B2122]: Heartbeat timeout from /10.0.0.4:7676 [brokerID=ora_brokerB, brokerSession=6614418657414713600] (seq#=0, ts=1248653447852, interval=2, len=209):
                        java.io.IOException: Invalid argument
                        • 9. Re: Can non-clustered MySQL database be used as an enhanced cluster store?
                          807581
                          The "java.io.IOException: Invalid argument" exception was from DatagramSocket.send(). What OS/system (version as well) were you using ? Have you tried different versions of that OS or JDK ?

                          There is a similar exception reported in the following Java bug on a RH Linux dual stack machine,
                          http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4453100

                          If that resembles your machine enviornment, have you tried set Java system property java.net.preferIPv4Stack=true ?