Issue in migrating sonarqube(community edition) database from MySQL to Oracle DB

SubhadeepMondal · July 28, 2021, 6:17am

We are currently using sonarqube 7.5 community edition and we are trying to upgrade to sonarqube 8.9-LTS. Since, MySQL is not supported in the LTS 8.9 community edition, we are migrating our database to Oracle 19C.
We tried to use the inbuilt data migration tool in sql developer, but however as the columns and the stored datatypes are varying so unable to proceed for the same. For instance, the groups_users table in sonarqube 7.5 MySQL database stores users_id as integer but in the groups_users table in Oracle 19C stores users_id values as hashes.
We are facing issues in the above aspects, can you please suggest what is the best approach for migrating the database?

Thanks

pierreguillot · July 28, 2021, 7:42am

Hi, we recommend using sonar-db-copy, which we specifically maintain for this usage.

SubhadeepMondal · July 28, 2021, 7:59am

Hello @pierreguillot , The hyperlink which you have sent for the sonar-db-copy is invalid and gives a 404 error.
Also according to our knowledge, the sonar-db-copy tool is only available with the enterprise edition and not with the community edition?

Thanks

pierreguillot · July 28, 2021, 8:22am

Indeed, you are right. My bad.

The community and public version of this tool is mysql-migrator which allow “only” to migrate out from MySQL (while with sonar-db-copy you could migrate from Oracle to SQL Server for example). So mysql-migrator should be good for your use case.

SubhadeepMondal · July 28, 2021, 9:51am

Hello @pierreguillot ,
We checked for the tool you mentioned, but it says in the pre-requisite section that the migration is possible between databases linked to the same version of SonarQube. But in our scenario our SonarQube version is different. We need to migrate the data from SonarQube 7.5 community edition using MySQL database to SonarQube 8.9-LTS community edition.
Please suggest what is the best approach for the same.

Pre-requisite section of the github repository for your reference:

github.com

SonarSource/mysql-migrator/blob/master/README.md#prerequisites

SonarQube MySQL Database Migrator
=================================

You can use the SonarQube MySQL Database Migrator to copy your SonarQube database from your MySQL (source) database to a non-MySQL (target) database.

### Prerequisites

- SonarQube version 6.7LTS to 7.8  
   **Note:** Your source and target databases must be using the same version of SonarQube.
- Currently using MySQL

### Supported Target Databases
| Database                                                    |                                                                                                                                                                                                                                                                   |
| ----------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| [PostgreSQL](http://www.postgresql.org/)                    | ![](/images/check.svg) 10                                                                                                                                                                                                                                         |
|                                                             | ![](/images/check.svg) 9.3 - 9.6                                                                                                                                                                                                                                         |
| [Microsoft SQL Server](http://www.microsoft.com/sqlserver/) | ![](/images/check.svg) 2017 (MSSQL Server 14.0) with bundled Microsoft JDBC driver. Express Edition is supported.                                                                                                                                                 |
|                                                             | ![](/images/check.svg) 2016 (MSSQL Server 13.0) with bundled Microsoft JDBC driver. Express Edition is supported.                                                                                                                                                 |
|                                                             | ![](/images/check.svg) 2014 (MSSQL Server 12.0) with bundled Microsoft JDBC driver. Express Edition is supported.                                                                                                                                                 |
| [Oracle](http://www.oracle.com/database/)                   | ![](/images/check.svg) 19C                                                                                                                                                                                                                                        |

This file has been truncated. show original

Thanks

pierreguillot · July 28, 2021, 10:05am

Yep, so you need to:

Migrate from SonarQube 7.5 community edition using MySQL to SonarQube 7.5 community edition using Oracle
Then upgrade to 7.9
Then upgrade to 8.9

SubhadeepMondal · July 28, 2021, 12:19pm

Hello @pierreguillot , as mentioned earlier in the post, what about the differences in for the same columns for the same tables where the one database has integer and one has hash values.

Thanks

pierreguillot · July 28, 2021, 12:37pm

As the tool documentation suggests, you’ll need to start & stop SonarQube 7.5 on your target database (empty). That will initialise the schema, creating the table, with the correct types. The migration tool then only copy the data.