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/ |
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 |
[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 |
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 |
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 |
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); |
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.