ActiveObjects's integer ID when there are more than Integer.MAX_VALUE records (2 billion records)

ActiveObjects suggests to extend Entity, which defines:

public interface Entity extends RawEntity<Integer>
{
    @AutoIncrement
    @NotNull
    @PrimaryKey("ID")
    public int getID();
}

But those IDs are Integer! What happens when you reach Integer.MAX_VALUE, i.e. 2 billion (2,147,483,647 exactly) ?

  • Can we define our own Entity, with ID column as long ?
  • How can we migrate our customers from int to long, as a plugin vendor? Can we just swap from Entity to our own LongEntity, and, if we test in advance, assume that the ID column was correctly switched to long? Seems like a high-risk upgrade.
  • Why aren’t IDs long if it was possible from the start? Is there a drawback in terms of performance?

As for a scenario: We have properties associated to requirements which can be a baseline. If a customer has 50 properties per requirement, 100 requirements per page, 10000 pages, requirements were baselined 500 times, we reach the limit. I know it sounds weird, but we have to think about Data Center customers where they sometimes copy a space every two weeks.

Related:

I regularly extend RawEntity myself, haven’t had any issues with such a thing.

Hi @aragot,

extending from RawEntity works great. The open question will be: Can you upgrade the field from int to long, or do you need an upgrade task which copies the value from “old ID” to the “new ID” field. You reference AO-273, which is exactly what I was aware of as well, making this answer a “I do not know which way works safely”.

What I would recommend for now:

  • Change the field from Integer to Long.
  • Write a testcase using ActiveObjectsTestRunner. Run it against all supported databases and verify that the field has the correct size, e.g. that you can store IDs larger than Integer.MAX_VALUE. You should find a few more examples in the product’s source code or dependency sources.
  • If you need to write an AOUpgradeTask, then write a test case to ensure your upgrade task works on all supported databases.

@dennis.fischer I confirm:

  • It is possible to almost seamlessly change the ID from int to long (and swapping the underlying object from Entity to OurLongEntity),
  • However, in Postgres, ActiveObjects will leave the old sequence which can only generate integers. A manual operation from DBAs will be required, once they reach 2 billion items, but at least this is a very easy operation.
  • Of course this kind of things absolutely requires testing with all DBMS.
2 Likes

My conclusion: It is not possible to change the type of a primary key, because of MySQL. It is however possible to extend from RawEntity.

If there is a foreign key to an integer ID, then MySQL can’t change the type of both columns together. mysql - Cannot change column used in a foreign key constraint - Stack Overflow

com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
	- name:MySQL
	- version:5.7.34
	- minor version:7
	- major version:5
Driver:
	- name:MySQL Connector Java
	- version:mysql-connector-java-5.1.48 ( Revision: 29734982609c32d3ab7e5cac2e6acee69ff6b4aa )

java.sql.SQLException: Cannot change column 'LIST_ITEM_ID': used in a foreign key constraint 'fk_ao_32f7ce_test8_list_item_id'
	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:54)

It is also mentionned in https://community.atlassian.com/t5/Confluence-questions/How-to-get-past-the-following-quot-Cannot-change-column-xxx-used/qaq-p/117216 .