jdbcPersistenceAdapter myth of ActiveMQ solved

I faced this problem last week and I had some talks with my colleagues but there was no solution :( – jdbcPersistenceAdapter did not click well with my MySQL database.

This was what that happened if I configured ActiveMQ with JDBC persistence, a clean database (with no tables) did not stop the problem from occuring:

17:05:16.359 ERROR [org.apache.activemq.broker.BrokerService:start] Failed to start ActiveMQ JMS Message Broker. Reason: java.io.IOException: Failed to get last broker message id: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'activemq.activemq_acks' doesn't exist
java.io.IOException: Failed to get last broker message id: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'activemq.activemq_acks' doesn't exist
at org.apache.activemq.util.IOExceptionSupport.create(IOExceptionSupport.java:31)
at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.getLastMessageBrokerSequenceId(JDBCPersistenceAdapter.java:145)
at org.apache.activemq.broker.region.DestinationFactoryImpl.getLastMessageBrokerSequenceId(DestinationFactoryImpl.java:148)
at org.apache.activemq.broker.region.RegionBroker.(RegionBroker.java:104)
at org.apache.activemq.broker.BrokerService.createRegionBroker(BrokerService.java:1270)
at org.apache.activemq.broker.BrokerService.createBroker(BrokerService.java:1209)
at org.apache.activemq.broker.BrokerService.getBroker(BrokerService.java:508)
at org.apache.activemq.broker.BrokerService.start(BrokerService.java:394)
at org.apache.activemq.xbean.XBeanBrokerService.afterPropertiesSet(XBeanBrokerService.java:47)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1201)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1171)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:425)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:251)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:156)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:248)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:160)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:287)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:352)
at org.apache.xbean.spring.context.ResourceXmlApplicationContext.(ResourceXmlApplicationContext.java:88)
at org.apache.xbean.spring.context.ResourceXmlApplicationContext.(ResourceXmlApplicationContext.java:76)
at org.apache.xbean.spring.context.ResourceXmlApplicationContext.(ResourceXmlApplicationContext.java:72)
at org.apache.xbean.spring.context.ResourceXmlApplicationContext.(ResourceXmlApplicationContext.java:68)
at org.apache.activemq.xbean.BrokerFactoryBean.afterPropertiesSet(BrokerFactoryBean.java:84)
at
...
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'activemq.activemq_acks' doesn't exist
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3256)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1313)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1448)
at org.enhydra.jdbc.core.CorePreparedStatement.executeQuery(CorePreparedStatement.java:92)
at org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter.doGetLastMessageBrokerSequenceId(DefaultJDBCAdapter.java:148)
at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.getLastMessageBrokerSequenceId(JDBCPersistenceAdapter.java:142)
... 76 more

So I started a thread in the ActiveMQ mailing list. I have also tried doing the same thing in my Ubuntu box but faced no problem. Spent a little bit more time on it, and realized that two out of the three tables were actually created, i.e. activemq_locks and activemq_msgs. And this information managed to give someone a clue on what's going on:

I just hit the same thing as well

Specified key was too long; max key length is 1000 bytes is the error not allowing the creation of ACTIVEMQ_ACKS

You are probably using utf8/some other multibyte encoding as the collation in your database....

Switch it to latin1 or ASCII... The varchar fields that the key is composed of add up to less than 1000 characters but with a multibyte encoding the key length is over 1000 bytes.

That's the case in my mysql setup here anyway.

- Alex_ynr

Bingo! :-)

mysql> status
--------------
...
Server characterset:    utf8
Db     characterset:    utf8
...

mysql> drop database activemq;
Query OK, 2 rows affected (0.06 sec)

mysql> create database activemq char set latin1;
Query OK, 1 row affected (0.00 sec)

mysql> use activemq;
Database changed
mysql> status
...
Server characterset:    utf8
Db     characterset:    latin1
...

mysql> show tables;
+--------------------+
| Tables_in_activemq |
+--------------------+
| activemq_acks      |
| activemq_lock      |
| activemq_msgs      |
+--------------------+
3 rows in set (0.00 sec)

- yc