[jira] Commented: (AMQ-1762) Field Type for ID column on table activemq_msgs should be increased from integer to bigint for postgres

["Mario Lukica (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/activemq/browse/AMQ-1762?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=44413#action_44413 ] 

Mario Lukica commented on AMQ-1762:
-----------------------------------

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

Here is the stack trace:
{noformat}
 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
{noformat}

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.

> Field Type for ID column on table activemq_msgs should be increased from integer to bigint for postgres
> -------------------------------------------------------------------------------------------------------
>
>                 Key: AMQ-1762
>                 URL: https://issues.apache.org/activemq/browse/AMQ-1762
>             Project: ActiveMQ
>          Issue Type: Bug
>          Components: Message Store
>    Affects Versions: 5.1.0
>         Environment: Linux, with Postgres/EnterpriseDB
>            Reporter: Yves Sandfort
>
> 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.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.