ActiveMQ 5.8 mysql persistency - 'MSG' column type BLOB instead of LONGBLOB ,

[Alin]

Hi,
I have ActiveMQ 5.8 configured to persist the messages on a MySQL db. Everything relater to this configuration is OK, being able to run my process, except from some situation when I'm sending very big messaged to the queue. At the point I'm getting an error like the one below:

Caused by: java.io.IOException: Data truncation: Data too long for column 'MSG' at row 1
       at org.apache.activemq.util.IOExceptionSupport.create(IOExceptionSupport.java:45)
       at org.apache.activemq.store.jdbc.TransactionContext.close(TransactionContext.java:141)
       at org.apache.activemq.store.jdbc.JDBCMessageStore.addMessage(JDBCMessageStore.java:129)
       at org.apache.activemq.store.memory.MemoryTransactionStore.addMessage(MemoryTransactionStore.java:327)
       at org.apache.activemq.store.memory.MemoryTransactionStore$1.asyncAddQueueMessage(MemoryTransactionStore.java:154)
       at org.apache.activemq.broker.region.Queue.doMessageSend(Queue.java:748)
       at org.apache.activemq.broker.region.Queue.send(Queue.java:721)
       at org.apache.activemq.broker.region.AbstractRegion.send(AbstractRegion.java:406)
       at org.apache.activemq.broker.region.RegionBroker.send(RegionBroker.java:392)
       at org.apache.activemq.broker.jmx.ManagedRegionBroker.send(ManagedRegionBroker.java:282)
       at org.apache.activemq.broker.BrokerFilter.send(BrokerFilter.java:129)
       at org.apache.activemq.broker.CompositeDestinationBroker.send(CompositeDestinationBroker.java:96)
       at org.apache.activemq.broker.TransactionBroker.send(TransactionBroker.java:317)
       at org.apache.activemq.broker.MutableBrokerFilter.send(MutableBrokerFilter.java:135)
       at org.apache.activemq.broker.TransportConnection.processMessage(TransportConnection.java:499)
       at org.apache.activemq.command.ActiveMQMessage.visit(ActiveMQMessage.java:749)
       at org.apache.activemq.broker.TransportConnection.service(TransportConnection.java:329)
       at org.apache.activemq.broker.TransportConnection$1.onCommand(TransportConnection.java:184)
       at org.apache.activemq.transport.MutexTransport.onCommand(MutexTransport.java:50)
       at org.apache.activemq.transport.WireFormatNegotiator.onCommand(WireFormatNegotiator.java:113)
       at org.apache.activemq.transport.AbstractInactivityMonitor.onCommand(AbstractInactivityMonitor.java:288)
       at org.apache.activemq.transport.TransportSupport.doConsume(TransportSupport.java:83)
       at org.apache.activemq.transport.tcp.TcpTransport.doRun(TcpTransport.java:214)
       at org.apache.activemq.transport.tcp.TcpTransport.run(TcpTransport.java:196)
       at java.lang.Thread.run(Thread.java:724)
Caused by: java.lang.Throwable: java.sql.BatchUpdateException: Data truncation: Data too long for column 'MSG' at row 1
       at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2054)
       at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1467)
       at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
       at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
       at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
       at org.apache.activemq.store.jdbc.TransactionContext.executeBatch(TransactionContext.java:106)
       at org.apache.activemq.store.jdbc.TransactionContext.executeBatch(TransactionContext.java:84)
       at org.apache.activemq.store.jdbc.TransactionContext.close(TransactionContext.java:132)
       ... 23 more
Caused by: java.lang.Throwable: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'MSG' at row 1
       at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4185)
       at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
       at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
       at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
       at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
       at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
       at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
       at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2006)
       ... 30 more



I've checked the activeMQ db and realized that the column 'MSG' type, under ACTIVEMQ_MSGS table  is BLOB which may accept only 65535 chars.

My question is how can I force ActiveMq to define the 'MSG' column type as LONGBLOB ?


Thanks

[Alin]

The MySQL server version I'm using is 5.6.12

[ceposta]

Check this wiki:

http://activemq.apache.org/jdbc-support.html

<persistenceAdapter>

    <journaledJDBC useJournal="false">

      <statements>

        <statements binaryDataType ="LONGBLOB"/>

      </statements>

    </journaledJDBC>

  </persistenceAdapter>


On Thu, Sep 26, 2013 at 11:36 AM, Alin <[hidden email]> wrote:

> The MySQL server version I'm using is 5.6.12
>
>
>
> --
> View this message in context:
> http://activemq.2283324.n4.nabble.com/ActiveMQ-5-8-mysql-persistency-MSG-column-type-BLOB-instead-of-LONGBLOB-tp4671864p4671865.html
> Sent from the ActiveMQ - User mailing list archive at Nabble.com.
>



--
*Christian Posta*
http://www.christianposta.com/blog
twitter: @christianposta

[Alin]

Thanks, it worked!

This is what i have on my side in case anyone else will stumble over this issues:

<broker brokerName="jdbcBroker" xmlns="http://activemq.apache.org/schema/core">
        <persistenceAdapter>
            <jdbcPersistenceAdapter lockKeepAlivePeriod="1000" lockAcquireSleepInterval="2000" dataSource="#mysql-ds">
                                <databaseLocker>
                    <lease-database-locker/>
                </databaseLocker>
                                <statements>
                                        <statements binaryDataType ="LONGBLOB"/>
                                </statements>
                        </jdbcPersistenceAdapter>
                </persistenceAdapter>
        <transportConnectors>
            <transportConnector name="default" uri="tcp://0.0.0.0:61616"/>
        </transportConnectors>
    </broker>