In Tanzania, we have a large database with billions of data.
The change to “bigint” implementation on database tables has been a hail mary on our instance considering this amount of data.
Problem is that not all tables that require “bigint” have been implemented. A case in point is a table that holds reserved values(sequentialnumbercounter) which affects a lot of our implementation including HMIS and Covid-related implementations. In fact, this table in our database has reached a point of no return.
Our temporary solution is to alter that id column type to bigint as follows:
ALTER TABLE sequentialnumbercounter ALTER COLUMN id TYPE bigint;
Beware though, this poses a whole another challenge. When DHIS2 starts up it performs schema validation in which case it fails to run due to this change in the schema. This brings us to our follow up solution each time we restart the instance as follows:
- Stop the DHIS2 instance
- Backup the sequentialnumbercounter table
- Delete all the data from the sequentialnumbercounter table
- Alter the id column to type int
- Start the instance
- Check whether this instance is up and running
- Alter the id column to type bigint as above
- Finally restore sequentialnumbercounter the table data backup
We are working to address this flow. If you are interested comment so we can remember to share the code to restart your instance with this workaround. Any ideas will be great.
Now! Before you say everything is hunky-dory. Notice there is a window of opportunity for entry of rows to the sequentialnumbercounter table after the instance is running and before the backup is restored. YOU DO NOT WANT THAT. That is why this is just a temporary solution of which for developers can follow up on recent issue that we have been testing during development at https://community.dhis2.org/t/error-generate-and-reserve-value-endpoint-api/46890.
Cheers,