Log Message java.sql.SQLException: Numeric Overflow

Symptoms

Viewtracker can't track any more new data n your Confluence and the following log message can be found in the application log atlassian-confluence.log.

TEXT

[...]
trackViewInternal com.atlassian.activeobjects.internal.ActiveObjectsSqlException: 
There was a SQL exception thrown by the Active Objects library:

Database:

name:Oracle
version:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
minor version:0
major version:19

Driver:

name:Oracle JDBC driver
version:12.2.0.1.0

java.sql.SQLException: Numeric Overflow
[...]

Possible cause

The primary key column ID of the app table AO_05769A_VISIT_ENTITY is managed by Confluence itself (Active Object) and uses NUMBER(11) as a datatype.

This data type for the ID column is restricted to 2.1 billion entries, or more accurately: The identifier number cannot be higher than 2 147 483 598 - this is what is now reached and why the database can't create a new primary key value.

Viewtracker doesn't need these id's, but they are mandatory by Confluence. The database now doesn't allow any new insert, as the new autoincremented number is too big for this datatype. We could reproduce your issue.

This issue could be solved.

How could this happen?

Due to multiple system migrations or CSV imports, we saw cases where this database-related restriction was reached.

Verify this assumption

By executing the following SQL, you will see that high ID.

SQL

Select max(id from AO_05769A_VISIT_ENTITY;

If the id is by 2 147 483 598 - you are affected and we need to reassign the autoincrement id.

Resolution

First, check exactly how many entries are in the AO_05769A_VISIT_ENTITY table and note down the number. This could be done by

SQL

Select count(id) from AO_05769A_VISIT_ENTITY;

If you really do have 2 147 483 598 entries in the database (which we do not expect), it’s time to remove some history data (shown below). If the number is much lower (normal case by 100 - 200 million entries), you don’t need to remove any data.

This reassigning is not that easy and we recommend the following procedure:

Do a backup!
Don't let Confluence run at this moment
Take enough time as we copy 2 times 35 million entries in your database. This will take some time, depending on your database power.
Test this in a test instance, if possible.

Steps in short:

Stop Confluence and create a backup
Copy all entries in a temporary table
Remove all entries from AO_05769A_VISIT_ENTITY
Recreate the sequence so that the id start new from 1 (important!)
Reimport all data.
Start Confluence and test the tracking feature.

We tested all these steps in our instances and it worked perfectly - it just needed some time.

Steps in detail (for Oracle):

Copy all entries in a temporary table

SQL

create table AO_05769A_VISIT_ENTITY_SIC
(
	CONTENT_ID NUMBER(20) default 0 not null,
	DEVICE_TYPE NUMBER(11) default 0,
	ID NUMBER(11) not null
		primary key,
	PROTECTED_USER NUMBER(1),
	USER_KEY VARCHAR2(255),
	VISIT_TIME TIMESTAMP(6) not null
);


INSERT INTO AO_05769A_VISIT_ENTITY_SIC (ID, CONTENT_ID, DEVICE_TYPE, 
  PROTECTED_USER, USER_KEY, VISIT_TIME)
SELECT ID, CONTENT_ID, DEVICE_TYPE, PROTECTED_USER, USER_KEY, VISIT_TIME
FROM AO_05769A_VISIT_ENTITY
where VISIT_TIME  > date '2012-10-31';

The where statement could be used to "skip" really old data you don't need anymore. We thought it was maybe a good moment to remove not needed data to improve the reporting possibilities. Just change the date in this format "YYYY-MM-DD". But the whole where - part is optional.

Remove all entries from AO_05769A_VISIT_ENTITY

SQL

delete from AO_05769A_VISIT_ENTITY;

Recreate the index

SQL

drop sequence AO_05769A_VISIT_ENTITY_ID_SEQ;
CREATE SEQUENCE "AO_05769A_VISIT_ENTITY_ID_SEQ" 
INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1;

Reimport all data.

SQL

INSERT INTO AO_05769A_VISIT_ENTITY ( CONTENT_ID, DEVICE_TYPE, 
  PROTECTED_USER, USER_KEY, VISIT_TIME)
SELECT  CONTENT_ID, DEVICE_TYPE, PROTECTED_USER, USER_KEY, VISIT_TIME
from AO_05769A_VISIT_ENTITY_SIC;

We realize that it's maybe a bit scary to do these steps, but this is the only way to reset the sequence to a lower number so that the database allows inserting entries again.