Field Type for ID column on table activemq_msgs should be increased from integer to bigint for postgres

• Key: AMQ-1762
• Type: Bug
• Status: Open
• Priority: Major
• Assignee: Unassigned
• Reporter: Yves Sandfort
• Component/s: Message Store
• Affects Version/s: 5.1.0
• Fix Version/s: 5.3.0

Description

As the postgres size for an int is limited to: 2147483647 you can easily reach that number on a loaded ActiveMQ server within weeks. This is very hard to nail issue, as you get an exception like: Batch entry 0 INSERT INTO ACTIVEMQ_MSGS(ID, MSGID_PROD, MSGID_SEQ, CONTAINER, EXPIRATION, MSG) VALUES (2152101302, ID:cnmacoll2.ms.de.cdip.net-2970-1212478745177-0:1397:1:1, 1, queue://CNMACollector_1000474_1000474, 0, <stream of 820 bytes>) was aborted.

Which gives you no clue about the real issue, so we searched for all kinds of issues around.

However we figured out it was the ID column.

Not sure if this happens on other plattforms too, but at least on all PostgreSQL versions.

While checking this, someone should make sure that not the ID inside the java code will be the next issue.

Comments

[Mario Lukica]

I think the same problem exists in 4.1.2 with DerbyDB 10.1.1.0.

Here is the stack trace:

 WARN  [JournalMessageStore ] Message could not be added to long term store: Failed to broker message: ID:conn1-34774-1214943118671-4:888842:1:1:380 in container: SQL Exception: The resulting value is outside the range for the data type INTEGER.
java.io.IOException: Failed to broker message: ID:mgw1-34774-1214943118671-4:888842:1:1:380 in container: SQL Exception: The resulting value is outside the range for the data type INTEGER.
        at org.apache.activemq.util.IOExceptionSupport.create(IOExceptionSupport.java:31)
        at org.apache.activemq.store.jdbc.JDBCMessageStore.addMessage(JDBCMessageStore.java:72)
        at org.apache.activemq.store.journal.JournalMessageStore$3.execute(JournalMessageStore.java:260)
        at org.apache.activemq.util.TransactionTemplate.run(TransactionTemplate.java:44)
        at org.apache.activemq.store.journal.JournalMessageStore.checkpoint(JournalMessageStore.java:247)
        at org.apache.activemq.store.journal.JournalMessageStore.checkpoint(JournalMessageStore.java:221)
        at org.apache.activemq.store.journal.JournalPersistenceAdapter$4.call(JournalPersistenceAdapter.java:356)
        at edu.emory.mathcs.backport.java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:306)
        at edu.emory.mathcs.backport.java.util.concurrent.FutureTask.run(FutureTask.java:135)
        at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1061)
        at edu.emory.mathcs.backport.java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:575)
        at java.lang.Thread.run(Thread.java:595)
Caused by: SQL Exception: The resulting value is outside the range for the data type INTEGER.
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedResultSet.noStateChangeException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.setLong(Unknown Source)
        at org.apache.activemq.store.jdbc.adapter.DefaultJDBCAdapter.doAddMessage(DefaultJDBCAdapter.java:170)
        at org.apache.activemq.store.jdbc.JDBCMessageStore.addMessage(JDBCMessageStore.java:69)
        ... 10 more

Cause of the exception:
DefaultJDBCAdapter.doAddMessage:
s.setLong(1,messageID.getBrokerSequenceId());

In this case error message explicitely points to INTEGER column range violation.

Message id ID:conn1-34774-1214943118671-4:888842:1:1:380 does not show brokerSequenceId, so I cannot verify it.

[Bruce Snyder]

Attaching a patch to add a small change to the ID column type for PostgreSQL from INTEGER to BIGINT.