Failed database migrations during upgrade (Oracle, 6.3 → 8.9)

[childrenofbodom (Rick)]

Hi,
I recently upgraded sonar from 6.3 to 6.7 which updated the mysql db correctly.
After that, I made a porting from mysql to oracle with the automatic migration of oracle sql develper, lastly I installed the sonar 7.9.
During the installation, the database upgrade fails because it tries to delete the indexes of some constraints, an example of the error is:

org.sonar.server.platform.db.migration.step.MigrationStepExecutionException: Execution of migration step #2606 ‘Drop DATA_TYPE column from FILE_SOURCES table’ failed
Caused by: java.lang.IllegalStateException: Fail to execute DROP INDEX file_sources_uuid_type
Caused by: java.sql.SQLException: ORA-02429: cannot drop index used for enforcement of unique/primary key

I removed the constraints and recreated the indexes affected by the problem and the migration was completed successfully, (it seems to have already happened to others).
Now I am trying to migrate from 7.9 to LTS 8 and I have this problem:

Caused by: java.lang.IllegalStateException: Fail to execute ALTER TABLE organizations MODIFY (guarded NUMBER(1) NULL)
Caused by: java.sql.SQLException: ORA-01440: column to be modified must be empty to decrease precision or scale

It seems that the software wants to change integer fields (used as boolean) from 3 to 1, which is forbidden by oracle because the table is not empty.

Has anyone encountered the same problems after migrating to oracle? how did you solve?
The upgrade of the db can be done through script and not applicatively?

Thanks

[jacek.poreda]

Hello @childrenofbodom,

Welcome to the community!

I did try to reproduce the issue using AWS RDS oracle 19.0.0.0.ru-2021-10.rur-2021-10.r1 on a clean DB from 7.9 to 8.9, and no exception happened for me (the “organizations” table was not empty).

Could you write more details about what oracle version you are using and what ojdbc driver? I wouldn’t mind if you would share the content of the “organizations” table as well (there should be one row only)

[childrenofbodom (Rick)]

Hello @jacek.poreda ,
in my case I did a sonar upgrade from 6.3 to 6.7 on mysql.
After the upgrade, the “guarded” field of the “organizations” table on mysql has a precision of 3.

Mysql table

I generated the migration scripts to oracle starting from this state, therefore also on oracle the “guarded” field has a precision of 3.

Oracle table

then, I upgraded to version 7.9 and finally to version 8, obtaining the ORA-01440 error.

Oracle forbid changing the precision of a column if it is not empty, in fact if I try to do the same operation directly on the db, I get the same error

I can confirm that I have only one element in the organizations table,

the oracle server version is 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.13.0.0.0 and we use the ojdbc8 driver for connection.

[jacek.poreda]

Thanks a lot for the detailed response.

@childrenofbodom

I generated the migration scripts to oracle starting from this state

I think this is the crucial part; you did not use our tool for that, is that correct?

If you didn’t, I think you could either give it a try or adjust your schema to clean 7.9:

1. Add a new column with correct precision with a prefix (type should match the one from 7.9)
2. Drop the existing one
3. Rename column created in 1. point

[childrenofbodom (Rick)]

Thanks @jacek.poreda, I have not used your tool but I have directly migrated the db from mysql to oracle. I will modify the table by hand.
Thanks a lot.

[jacek.poreda]

@childrenofbodom Sorry, I gave you the link to the wrong tool, it should be this one: https://github.com/SonarSource/sonar-db-copy