How I Fixed My SonarQube Server After a Failed Update

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.