Last week I updated my SonarQube server running on Ubuntu 20.04.4 LTS and ended up in a situation in which all code scans of a certain project ran into a database-related error. In this blog post I’d like to summarize the update process, the error and how I fixed it.
First Things First: Make a Backup
Seriously, make a backup of your database before you update your SonarQube server. It saved me in this case. Backups should be made regularly, but it does not hurt to do another backup to ensure the latest state is saved after shutting down your server.
Determining the Migration Path
Read the upgrade guide to find out which intermediate LTS versions are required to upgrade to your desired version. In my case the migration path was:
7.9.1 -> 7.9.6 LTS -> 8.9.7 LTS
A list of all LTS versions can be found on the downloads page.
Downloading and Extracting New Versions
Downloading and extracting the new versions is pretty straight-forward. In my case each version is stored in separate subfolders, so that I can go back to another version if needed. Of course you can choose other paths on your system, but /opt/sonarqube
seems to be a decent location on Ubuntu (or Linux in general). Make sure that you still have a copy of the old version, especially the config files.
1 2 3 4 5 | cd /opt/sonarqube sudo wget https://binaries.sonarsource.com/Distribution/sonarqube/sonarqube-7.9.6.zip sudo unzip sonarqube-7.9.6.zip sudo rm sonarqube-7.9.6.zip sudo chown -R sonarqube: /opt/sonarqube/sonarqube-7.9.6/ |
Configuring the New Version
The next step is to transfer your configuration settings into the installation of the new version. To that end, compare the conf/sonar.properties
files from the old and the new installation. Copy uncommented lines from the old to the new configuration file. The most important ones are:
sonar.jdbc.username
sonar.jdbc.password
sonar.jdbc.url
Reconfiguring the Service
In case you are using a systemd
service to start and stop your server (which is recommended), the service script located at /etc/systemd/system/sonarqube.service
must be updated to the new version. In my case the script looks like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | [Unit] Description=SonarQube Service After=syslog.target network.target [Service] Type=forking ExecStart=/opt/sonarqube/sonarqube-7.9.6/bin/linux-x86-64/sonar.sh start ExecStop=/opt/sonarqube/sonarqube-7.9.6/bin/linux-x86-64/sonar.sh stop LimitNOFILE=131072 LimitNPROC=8192 User=sonarqube Group=sonarqube Restart=always [Install] WantedBy=multi-user.target |
In order to reload the changes, execute
1 | sudo systemctl daemon-reload |
Wait a few seconds until the changes are reloaded, then start SonarQube:
1 2 | sudo systemctl start sonarqube.service sudo systemctl status sonarqube.service |
The final step is to visit the web interface of your server at ${sonar.url}/setup
to perform the database migration.
Before analyzing code, it is recommended to perform a cleanup of obsolete tuples in the database:
1 2 3 4 | psql -U postgres -h localhost \c sonarqube vacuum full; \q |
The Database Inconsistency
Everything worked for me so far, but when I started analyzing code all analysis processes failed with an error like the following:
org.postgresql.util.PSQLException: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification SQL: insert into live_measures ( uuid, component_uuid, project_uuid, metric_id, value, text_value, variation, measure_data, created_at, updated_at ) values (...) on conflict(component_uuid, metric_id) do update set value = excluded.value, variation = excluded.variation, text_value = excluded.text_value, measure_data = excluded.measure_data, updated_at = excluded.updated_at where live_measures.value is distinct from excluded.value or live_measures.variation is distinct from excluded.variation or live_measures.text_value is distinct from excluded.text_value or live_measures.measure_data is distinct from excluded.measure_data
At first I did not understand what was wrong because there were no apparent errors during the update. So I decided to start over, revert to the old version and to re-import my database backup. It took me some time to figure out the right parameters for the PostgreSQL command line to restore a SQL dump; I posted a separate blog post on this topic.
Unfortunately, the database backup could not be restored because of an error relating to a unique constraint on the columns component_uuid
and metric_id
in relation live_measures
. My table data contained duplicate tuples for combinations of component_uuid
and metric_id
.
Fortunately I found this thread explaining how to find and eliminate the duplicates. After eliminating the duplicates I could add the constraint again:
1 | CREATE UNIQUE INDEX live_measures_component ON public.live_measures USING btree (component_uuid, metric_uuid); |
After fixing the inconsistency, I repeated the update process and code scans were now mostly successful. However, Gradle builds failed with the following error:
Execution failed for task ':sonarqube'. > Unable to load component class org.sonar.scanner.report.ActiveRulesPublisher
I could solve this by deleting the directories data/es6
and temp
in the active SonarQube installation. So in case something went wrong, it is a good idea to delete those folders in order to rebuild the elastic search indices.
Now my intermediate version was running without issues and I repeated the whole update process for the next and final LTS version.
I hope this post will help you to update your SonarQube servers as well.