JBoss Messaging and MySQL

[haagenhasle]

Hi!

I'm using JBoss Messaging as a part of JBossESB 4.2.1GA, and I'm trying to switch from hsqldb to MySQL. I've followed instructions I've gotten in the JBossESB forum, but I have one final (?) problem, and was told to go ask you guys here..

I get this WARN statement when I start the server:

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'juddidb.jbm_postoffice' doesn't exist

I have manually created the juddidb-database before. After getting this warningmessage, I tried to create the jbm_postoffice-table manually as well. I executed the following command in MySQL:

CREATE TABLE JBM_POSTOFFICE (POSTOFFICE_NAME VARCHAR(255), NODE_ID INTEGER, QUEUE_NAME VARCHAR(255),
 COND VARCHAR(1023), SELECTOR VARCHAR(1023), CHANNEL_ID BIGINT, CLUSTERED CHAR(1), ALL_NODES CHAR(1)
, PRIMARY KEY(POSTOFFICE_NAME, NODE_ID, QUEUE_NAME)) ENGINE = INNODB

And I got this in return:

Error code 1071, SQL state 42000: Specified key was too long; max key length is 1024 bytes

Can anyone help me out with this?

Regards, Haagen

[timfox]

The installation instructions in the JBM user guide should tell you what you need to know.

[haagenhasle]

I have followed the instructions from the installation guide as far as I can see. I'm using the mysql-persistence-service.xml. I've replaced the DefaultDS. And now I'm stuck with the warning I described in my previous post.

I would be very helpful if someone could tell me what I'm doing wrong.

Regards, Haagen

[haagenhasle]

I see from my logs that the reason the "Table 'juddidb.jbm_postoffice' doesn't exist" message appears, is initially because JBoss is not able to create the table. The first errormessage in my log is the following:

2007-11-02 16:45:35,335 DEBUG [org.jboss.messaging.core.jmx.MessagingPostOfficeService] Starting jboss.messaging:service=PostOffice
2007-11-02 16:45:35,335 DEBUG [org.jboss.messaging.core.jmx.MessagingPostOfficeService] org.jboss.messaging.core.jmx.MessagingPostOfficeService@1e74fe9 uses XMLJChannelFactory
2007-11-02 16:45:35,382 DEBUG [org.jboss.messaging.core.impl.JDBCSupport] Failed to execute: CREATE TABLE JBM_POSTOFFICE (POSTOFFICE_NAME VARCHAR(255), NODE_ID INTEGER, QUEUE_NAME VARCHAR(255), COND VARCHAR(1023), SELECTOR VARCHAR(1023), CHANNEL_ID BIGINT, CLUSTERED CHAR(1), ALL_NODES CHAR(1), PRIMARY KEY(POSTOFFICE_NAME, NODE_ID, QUEUE_NAME)) ENGINE = INNODB
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Specified key was too long; max key length is 1024 bytes
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1027)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2537)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1564)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1485)
at org.jboss.resource.adapter.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:186)
at org.jboss.messaging.core.impl.JDBCSupport.createSchema(JDBCSupport.java:292)
at org.jboss.messaging.core.impl.JDBCSupport.start(JDBCSupport.java:158)
at org.jboss.messaging.core.impl.postoffice.MessagingPostOffice.start(MessagingPostOffice.java:322)
at org.jboss.messaging.core.jmx.MessagingPostOfficeService.startService(MessagingPostOfficeService.java:414)
at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:289)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:196)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:133)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:142)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:995)
at $Proxy0.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:417)
at org.jboss.system.ServiceController.start(ServiceController.java:435)
at org.jboss.system.ServiceController.start(ServiceController.java:435)
at org.jboss.system.ServiceController.start(ServiceController.java:435)
at org.jboss.system.ServiceController.start(ServiceController.java:435)
at org.jboss.system.ServiceController.start(ServiceController.java:435)
at org.jboss.system.ServiceController.start(ServiceController.java:435)
at org.jboss.system.ServiceController.start(ServiceController.java:435)
at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
at $Proxy27.start(Unknown Source)
at org.jboss.deployment.SimpleSubDeployerSupport.startService(SimpleSubDeployerSupport.java:356)
at org.jboss.deployment.SimpleSubDeployerSupport.start(SimpleSubDeployerSupport.java:127)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:1025)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:819)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:782)
at sun.reflect.GeneratedMethodAccessor50.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:133)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:142)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
at $Proxy9.deploy(Unknown Source)
at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:421)
at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:634)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:263)
at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDeploymentScanner.java:336)
at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:289)
at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:245)
at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
at $Proxy0.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:417)
at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
at $Proxy4.start(Unknown Source)
at org.jboss.deployment.SARDeployer.start(SARDeployer.java:302)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:1025)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:819)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:782)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:766)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:133)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:142)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
at $Proxy5.deploy(Unknown Source)
at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:482)
at org.jboss.system.server.ServerImpl.start(ServerImpl.java:362)
at org.jboss.Main.boot(Main.java:200)
at org.jboss.Main$1.run(Main.java:508)
at java.lang.Thread.run(Thread.java:595)

Has anyone got this working with MySQL?

Regards, Haagen

[timfox]

"haagenhasle" wrote:
I
Has anyone got this working with MySQL?

Of course. MySQL is the database most users use and we test heavily against it.

If you following the installation instructions from the JBM user guide, and make sure you're using a supported DB. (see the wiki for list of supported DBs) this will work for you.

[haagenhasle]

I was merely trying to be polite by asking if someone had got this working with MySQL, I wasn't trying to be rude.

I was using MySQL 4.1.22, which is the version that is specified in the "mysql-persistence-service.xml", as well as the version specified on the wiki.
(By the way, it seems the wiki (http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossMessagingDatabaseCompatibilityMatrix) hasn't been updated since version 1.2.0.)

I now tried upgrading to the lastest 5.0.45, and then it all suddenly worked.. I don't know the reason for this.. Maybe your testing wasn't as good as you thought? ;-)

I don't really understand why you give me an attitude and tell me to RTFM, would it hurt to help a newbie out and actually take a look at the problem I had?

Regards, Haagen

[timfox]

"haagenhasle" wrote:

I don't really understand why you give me an attitude and tell me to RTFM, would it hurt to help a newbie out and actually take a look at the problem I had?

No one has given you "attitude" or told you to RTFM.

Although that might have been an appropriate response if I wasn't such a nice persion ;)

Please bear in mind, that we work day and night to bring you software, that you consume *for free*.

Now the next stage from RTFM is SMD. I suggest you google for that if you don't know what it means ;)