Problems with MySQL persistance

[Claus Leonhardt]

Hi!



I have some problems with the MySQL persistence. I am using MySQL 4.1.12 and
ActiveMQ 3.1 M6. When I start ActiveMQ it can not create all needed tables
problerly. (It seems a key is too big.):



09:06:42 WARN Invalid JDBC adapter class class
(org.activemq.store.jdbc.adapter.StreamJDBCAdaptor). Will use default JDBC
implementation.

09:06:42 INFO Could not create JDBC tables; they could already exist.
Failure was: CREATE TABLE ACTIVEMQ_ACKS(SUB VARCHAR(250) NOT NULL, CONTAINER
VARCHAR(250) NOT NULL, LAST_ACKED_ID INTEGER, SE_ID INTEGER, SE_CLIENT_ID
VARCHAR(250), SE_CONSUMER_NAME VARCHAR(250), SE_SELECTOR VARCHAR(250),
PRIMARY KEY ( SUB, CONTAINER )) Message: Syntax error or access violation,
message from server: "Specified key was too long; max key length is 1000
bytes" SQLState: 42000 Vendor code: 1071

09:06:42 INFO Could not create JDBC tables; they could already exist.
Failure was: CREATE INDEX ACTIVEMQ_ACKS_CIDX ON ACTIVEMQ_ACKS (CONTAINER)
Message: Base table or view not found, message from server: "Table
'activemq.ACTIVEMQ_ACKS' doesn't exist" SQLState: 42S02 Vendor code: 1146

09:06:42 WARN Failed to find last sequence number: java.sql.SQLException:
Base table or view not found, message from server: "Table
'activemq.ACTIVEMQ_ACKS' doesn't exist"

java.sql.SQLException: Base table or view not found, message from server:
"Table 'activemq.ACTIVEMQ_ACKS' doesn't exist"

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1977)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)

at com.mysql.jdbc.Connection.execSQL(Connection.java:2236)

at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1555)

at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingP
reparedStatement.java:91)

at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingP
reparedStatement.java:91)

at
org.activemq.store.jdbc.adapter.DefaultJDBCAdapter.initSequenceGenerator(Def
aultJDBCAdapter.java:155)

at
org.activemq.store.jdbc.JDBCPersistenceAdapter.start(JDBCPersistenceAdapter.
java:232)

at
org.activemq.store.journal.JournalPersistenceAdapter.start(JournalPersistenc
eAdapter.java:174)

at
org.activemq.store.cache.CachePersistenceAdapter.start(CachePersistenceAdapt
er.java:81)

at
org.activemq.broker.impl.DefaultBroker.start(DefaultBroker.java:156)

at
org.activemq.broker.impl.BrokerContainerImpl.start(BrokerContainerImpl.java:
159)

at org.activemq.spring.Main.main(Main.java:72)

09:06:42 WARN Old message cleanup failed due to: java.sql.SQLException:
Base table or view not found, message from server: "Table
'activemq.ACTIVEMQ_ACKS' doesn't exist"

java.sql.SQLException: Base table or view not found, message from server:
"Table 'activemq.ACTIVEMQ_ACKS' doesn't exist"

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1977)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)

at com.mysql.jdbc.Connection.execSQL(Connection.java:2236)

at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1741)

at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1588)

at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(Delegating
PreparedStatement.java:94)

at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(Delegating
PreparedStatement.java:94)

at
org.activemq.store.jdbc.adapter.DefaultJDBCAdapter.doDeleteOldMessages(Defau
ltJDBCAdapter.java:594)

at
org.activemq.store.jdbc.JDBCPersistenceAdapter.cleanup(JDBCPersistenceAdapte
r.java:255)

at
org.activemq.store.jdbc.JDBCPersistenceAdapter.start(JDBCPersistenceAdapter.
java:247)

at
org.activemq.store.journal.JournalPersistenceAdapter.start(JournalPersistenc
eAdapter.java:174)

at
org.activemq.store.cache.CachePersistenceAdapter.start(CachePersistenceAdapt
er.java:81)

at
org.activemq.broker.impl.DefaultBroker.start(DefaultBroker.java:156)

at
org.activemq.broker.impl.BrokerContainerImpl.start(BrokerContainerImpl.java:
159)

at org.activemq.spring.Main.main(Main.java:72)



I there any fix for this?





Regards,
Claus Leonhardt

mobileview AG
-------------------------------------------
Flughafenstr. 54 b
22335 Hamburg
----------------------------------------
Tel.: [phone] - 0
Fax: [phone] - 50

< <mailto:claus.leonhardt-***@public.gmane.org>
mailto:claus.leonhardt-***@public.gmane.org>

Corporate Site

< <http://www.mobileview.com/> http://www.mobileview.com/>

OpenBC



< <http://www.openbc.com/go/invuid/Claus_Leonhardt>
http://www.openbc.com/go/invuid/Claus_Leonhardt>

[Tor-Einar Jarnbjo]

Post by Claus Leonhardt
I there any fix for this?

I already posted a bug report about this to the mailing list a few
months ago, but noone seem to care. There is a workaround though. If you
create a separate database instance for ActiveMQ and define it to use a
single-byte character set instead of some Unicode variant (which you are
obviously using), the key columns will not exceed the maximum index length.

Tor

[Claus Leonhardt]

This works. Thank you.

Mit freundlichen Grüßen,
Claus Leonhardt


-----Ursprüngliche Nachricht-----
Von: Tor-Einar Jarnbjo [mailto:Tor-Einar-***@public.gmane.org]
Gesendet: Montag, 1. August 2005 10:13
An: user-UzBZ5Px8zVVI+***@public.gmane.org
Betreff: Re: [activemq-user] Problems with MySQL persistance

Post by Claus Leonhardt
I there any fix for this?

I already posted a bug report about this to the mailing list a few
months ago, but noone seem to care. There is a workaround though. If you
create a separate database instance for ActiveMQ and define it to use a
single-byte character set instead of some Unicode variant (which you are
obviously using), the key columns will not exceed the maximum index length.

Tor