Version error running DB copy tool

jhavero · July 24, 2023, 4:17pm

Must-share information (formatted with Markdown):

which versions are you using (SonarQube, Scanner, Plugin, and any relevant extension): Developer Edition 8.9.10.61524
how is SonarQube deployed: zip, Docker, Helm: zip
what are you trying to achieve: Copy current production instance to test environment (postgres) to test upgrade to LTS
what have you tried so far to achieve this: Ran instructions from DB copy tool guide.

Hi, I am trying to run the DB copy tool for source and destination both running Developer Edition 8.9.10.61524 but I get the version error below. The source was recently downgraded from Enterprise Edition but kept the same version.

java -jar sonar-db-copy-1.6.0.2092-jar-with-dependencies.jar
-urlSrc "jdbc:sqlserver://:61375;databaseName=SonarQube;trustServerCertificate=true"
-userSrc sonar-service
-pwdSrc ****
-urlDest jdbc:postgresql:///sonar
-userDest sonaruser
-pwdDest ****

08:59:04 INFO  **************** CONFIGURATION VERIFICATIONS ****************
08:59:04 INFO  Database SOURCE has been reached at :          jdbc:sqlserver://****:61375;databaseName=SonarQube;trustServerCertificate=true
08:59:05 INFO  Database DESTINATION has been reached at :     jdbc:postgresql://****/sonar
Exception in thread "main" com.sonar.dbcopy.utils.toolconfig.SqlDbCopyException: Problem when verifying version database. Please build your destination database with SonarQube at the same SonarQube source version.
	at com.sonar.dbcopy.prepare.VersionVerifier.lastVersionId(VersionVerifier.java:49)
	at com.sonar.dbcopy.StartApp.main(StartApp.java:79)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The SELECT permission was denied on the object 'schema_migrations', database 'SonarQube', schema 'dbo'.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1676)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:907)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:802)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7627)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3916)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:268)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:242)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:724)
	at com.sonar.dbcopy.prepare.VersionVerifier.lastVersionId(VersionVerifier.java:37)
	... 1 more

ganncamp · July 25, 2023, 2:43pm

Hi,

Your version is past EOL. You should upgrade to either the latest version or the current LTS at your earliest convenience. Your upgrade path is:

xxx → 8.9.10 → 9.9.1 → 10.1 (last step optional)

You may find these resources helpful:

If you have questions about upgrading, feel free to open a new thread for that here.

Regarding your error, it looks like the account you’re running the migration with doesn’t have the permissions it needs to the schema in question.

Ann

jhavero · July 28, 2023, 8:38pm

Hi Ann, we are currently testing the upgrade to 9.9 on a separate server which is why we are running the DB copy tool. We fixed the permission issue you identified and the copy was able to run. However, we got these discrepancy errors. Is this because the source database was active during the copy step?

12:33:37 INFO  webhooks                      3 / 3 (39.87 rps)
12:33:38 INFO  **************** CHECK DISCREPANCIES ****************
12:33:38 ERROR TABLE project_branches has 250 ROWS in SOURCE while 249 ROWS in DESTINATION
12:33:38 ERROR TABLE project_measures has 106310 ROWS in SOURCE while 106055 ROWS in DESTINATION
12:33:38 ERROR TABLE snapshots has 2055 ROWS in SOURCE while 2050 ROWS in DESTINATION
12:33:38 ERROR TABLE webhook_deliveries has 2085 ROWS in SOURCE while 2064 ROWS in DESTINATION
12:33:38 INFO  ************************************************
12:33:38 ERROR ** THE COPY HAS FINISHED UNSUCCESSFULLY !!! WATCH OUT THE LOG !!! **
12:33:38 INFO  ************************************************

ganncamp · July 31, 2023, 12:10pm

Hi,

Are you migrating to a different DB engine along with your upgrade? Because if you’re staying with the same DB flavor, just take a backup of production and restore it in your test DB. No need for DBCopy. That’s only needed if your switching e.g. from Postgres to Oracle.

HTH,
Ann

jhavero · July 31, 2023, 4:27pm

Yes we are planning to switch to postgres. I retried the dbcopy again with no user activity on the source and this time it was successful.

jhavero · July 31, 2023, 10:04pm

I was going to request new license for this new sonarqube server but when I go to license manager it shows the license from the server I copied from and shows the same Server id.

ganncamp · August 1, 2023, 1:27pm

Hi,

Your license key is stored in the DB, so it’s quite normal that you would see the same key in your copy of the DB.

Server Ids are based (in part) on your JDBC URL. I guess you just copied your DB into a test env and gave it the same URL there as in production?

Ann

jhavero · August 1, 2023, 8:15pm

Hi, it wasn’t the same JDBC url as production but I think what happened was during my 2nd attempt at DB copy the JDBC url did not change. Anyway, I resolved this by changing the database name in the JDBC url.

jhavero · August 2, 2023, 3:06pm

Hi, I am verifying the new sonarqube server based off the DB copy before I begin the upgrade to 9.9.
I don’t see any projects listed when I go to the projects tab (see screenshot). I already ran a successful analysis and can select the project/branch from the Background tasks (see screenshot).

ganncamp · August 2, 2023, 7:16pm

Hi,

How many projects were there in your instance before your migration?

Ann

jhavero · August 2, 2023, 7:39pm

Hi Ann, from the Projects Management page there are 13.

ganncamp · August 3, 2023, 12:04pm

Hi,

So you’re seeing all 13 on the Projects Management page in the upgraded instance?

Ann

jhavero · August 3, 2023, 4:28pm

Hi. I’m seeing 13 projects in both the production instance and the DB copy instance from the Projects Management page. They are both at version 8.9.10. I have not started the upgrade on the DB copy instance. I’m not seeing any projects from https:///projects

ganncamp · August 3, 2023, 5:14pm

Hi,

Then this sounds like an Elasticsearch problem. Shut down the server. Delete $SONARQUBE-HOME/data/es7, and restart the server.

HTH,
Ann

jhavero · August 3, 2023, 5:34pm

Thanks that worked. I can see the projects now.