ActiveMQ持久化消息到数据库的坑——Table 'activemq.ACTIVEMQ_ACKS' doesn't exist.

问题描述

想使用数据库持久化ActiveMQ消息,在activemq.xml中配置如下:

<persistenceAdapter>
            <!--<kahaDB directory="${activemq.data}/kahadb"/>-->
            <jdbcPersistenceAdapter dataSource="#Mysql-ds" createTablesOnStartup="true"/>
</persistenceAdapter>

createTablesOnStartup="true" 的配置表明在启动时,ActiveMq会自动在数据库中建表。
  启动AcitiveMq(下面的操作都是在activemq根目录下进行):

./activemq start //启动activemq

查看日志文件

tail -f /data/activemq.log

报错如下

Failure Details: Table 'activemq.activemq_acks' doesn't exist | org.apache.activemq.store.jdbc.JDBCPersistenceAdapter | ActiveMQ JDBC PA Scheduled Task
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'activemq.activemq_acks' doesn't exist
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)[:1.8.0]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)[:1.8.0]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)[:1.8.0]
	at java.lang.reflect.Constructor.newInstance(Constructor.java:408)[:1.8.0]
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)[mysql-connector-java-5.1.38.jar:5.1.38]
	at com.mysql.jdbc.Util.getInstance(Util.java:387)[mysql-connector-java-5.1.38.jar:5.1.38]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)[mysql-connector-java-5.1.38.jar:5.1.38]
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)[mysql-connector-java-5.1.38.jar:5.1.38]
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)[mysql-connector-java-5.1.38.jar:5.1.38]
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)[mysql-connector-java-5.1.38.jar:5.1.38]
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)[mysql-connector-java-5.1.38.jar:5.1.38]
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)[mysql-connector-java-5.1.38.jar:5.1.38]
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)[mysql-connector-java-5.1.38.jar:5.1.38]
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)[mysql-connector-java-5.1.38.jar:5.1.38]
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009)[mysql-connector-java-5.1.38.jar:5.1.38]
	at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094)[mysql-connector-java-5.1.38.jar:5.1.38]
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994)[mysql-connector-java-5.1.38.jar:5.1.38]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)[commons-dbcp2-2.1.1.jar:2.1.1]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)[commons-dbcp2-2.1.1.jar:2.1.1]
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)[commons-dbcp2-2.1.1.jar:2.1.1]
	at org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter.doDeleteOldMessages(DefaultJDBCAdapter.java:832)[activemq-jdbc-store-5.15.9.jar:5.15.9]
	at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.cleanup(JDBCPersistenceAdapter.java:352)[activemq-jdbc-store-5.15.9.jar:5.15.9]
	at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter$3.run(JDBCPersistenceAdapter.java:330)[activemq-jdbc-store-5.15.9.jar:5.15.9]
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)[:1.8.0]
	at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)[:1.8.0]
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)[:1.8.0]
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)[:1.8.0]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)[:1.8.0]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)[:1.8.0]
	at java.lang.Thread.run(Thread.java:744)[:1.8.0]

并且在数据库中只创建了两张表(正常需要创建三张表:ACTIVEMQ_ACKS、ACTIVEMQ_LOCK、ACTIVEMQ_MSGS)

分析问题

  查看ActiveMQ官网发现这样的解释:

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.

  意思是key最多只能1000个字符,utf8等中文编码会占用过多的字符,所以要采用latin1 或者ASCII编码。

  查看mysql数据库编码:

解决问题

需要调整编码,于是重新建库设置默认编码方式为latin1

mysql> create databases activemq default character set latin1;

  再次查看编码方式

再次启动ActiveMQ,发现不在报错,成功启动,并且数据库中自动创建了如下三张表。

大功告成~

下次启动错误一定要记得先看日志,先记录一下,以防下次忘记

参考:CSDN---ActiveMQ持久化消息到数据库的坑

点赞

发表评论

电子邮件地址不会被公开。必填项已用 * 标注