Error during update v7.9.3 (LTS) to v8.9.9 (LTS)

kbolivard · June 30, 2022, 4:23pm

Hello,
I’m working on updating sonarqube v7.9.3 to sonarqube v8.9.9.
The database update step seems to work fine.
But then I have this error in the file web.log :

2022.06.30 17:19:48 INFO  web[][o.s.p.ProcessEntryPoint] Starting web
2022.06.30 17:19:49 INFO  web[][o.a.t.u.n.NioSelectorPool] Using a shared selector for servlet write/read
2022.06.30 17:19:49 INFO  web[][o.s.c.e.CoreExtensionsLoader] Loaded core extensions: developer-edition, developer-scanner, developer-server, license
2022.06.30 17:19:49 INFO  web[][o.s.s.e.EsClientProvider] Connected to local Elasticsearch: [http://localhost:9001]
2022.06.30 17:19:50 INFO  web[][o.s.s.p.LogServerVersion] SonarQube Server / 8.9.9.56886 / f7ef403a1f66dde923eda25b58df08d40a16e043
2022.06.30 17:19:50 INFO  web[][o.sonar.db.Database] Create JDBC data source for jdbc:postgresql://PGSSOND1/dpsond1
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerFileSystemImpl] SonarQube home: /logiciel/sonarqube/sonarqube-8.9.9.56886
2022.06.30 17:19:51 INFO  web[][o.s.s.u.SystemPasscodeImpl] System authentication by passcode is disabled
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin ABAP Code Quality and Security / 3.9.1.3127 / a62ddf6ddd7379d398a58d32c9931a2feef61e24
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin C# Code Quality and Security / 8.22.0.31243 / e3cee7838d992e31dcdd90cf4f7406bb20535e8e
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin CFamily Code Quality and Security / 6.20.5.49286 / 1de27cf105c23a2bdeef4d5c9ce908192f518af9
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin CSS Code Quality and Security / 1.4.2.2002 / faa7d4f1407df67df7ada53caf677ab783721173
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin Flex Code Quality and Security / 2.6.1.2564 / bb723840701bda72510b7a47742811d20daad331
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin Go Code Quality and Security / 1.8.3.2219 / d6ad7a5a47fc4785d2e80918fb7424be46e38a7f
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin HTML Code Quality and Security / 3.4.0.2754 / 38f7ff864ae15152c9f1efc3014594f7e7ca7b6e
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin JaCoCo / 1.1.1.1157 / 83478572b9f23efac29de15e30c7758bbb0c0e47
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin Java Code Quality and Security / 6.15.1.26025 / 1b1e96715bfa9f6a4ae24e95cc5b91f0edce609f
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin JavaScript/TypeScript Code Quality and Security / 7.4.4.15624 / 481b2e69339b016b5d7d1eb27f0abf20dd6bd961
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin Kotlin Code Quality and Security / 1.8.3.2219 / d6ad7a5a47fc4785d2e80918fb7424be46e38a7f
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin PHP Code Quality and Security / 3.17.0.7439 / 44c7760147080c157fa0ff579772f92d3c8e1ebf
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin PL/SQL Code Quality and Security / 3.6.1.3873 / 342f7fcf17ecb7fbf827a2aacf630be1f4157625
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin Python Code Quality and Security / 3.4.1.8066 / 22139ec73fb2f32044f66477ea52734415683668
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin Ruby Code Quality and Security / 1.8.3.2219 / d6ad7a5a47fc4785d2e80918fb7424be46e38a7f
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin Scala Code Quality and Security / 1.8.3.2219 / d6ad7a5a47fc4785d2e80918fb7424be46e38a7f
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin Swift Code Quality and Security / 4.3.1.4892 / 2b249272bc4430519bdab769886b12c9a82084b5
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin T-SQL Code Quality and Security / 1.5.1.4340 / 11f3de5739b539749d6c2848bda8fc90135d91b6
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin VB.NET Code Quality and Security / 8.22.0.31243 / e3cee7838d992e31dcdd90cf4f7406bb20535e8e
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin Vulnerability Analysis / 8.9.0.11439 / 04498e7b336a73db9508145d1e8cc44b9330528d
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin Vulnerability Rules for C# / 8.9.0.11439 / 04498e7b336a73db9508145d1e8cc44b9330528d
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin Vulnerability Rules for JS / 8.9.0.11439 / 04498e7b336a73db9508145d1e8cc44b9330528d
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin Vulnerability Rules for Java / 8.9.0.11439 / 04498e7b336a73db9508145d1e8cc44b9330528d
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin Vulnerability Rules for PHP / 8.9.0.11439 / 04498e7b336a73db9508145d1e8cc44b9330528d
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin Vulnerability Rules for Python / 8.9.0.11439 / 04498e7b336a73db9508145d1e8cc44b9330528d
2022.06.30 17:19:51 INFO  web[][o.s.s.p.ServerPluginManager] Deploy plugin XML Code Quality and Security / 2.2.0.2973 / 16002945f0725643a7b42f090572795dd8b72a0f
2022.06.30 17:19:52 INFO  web[][o.s.s.p.d.m.c.PostgresCharsetHandler] Verify that database charset supports UTF8
2022.06.30 17:19:52 INFO  web[][o.s.s.p.w.MasterServletFilter] Initializing servlet filter org.sonar.server.platform.web.WebServiceFilter@21967d85 [pattern=UrlPattern{inclusions=[/api/system/migrate_db.*, ...], exclusions=[/api/components/update_key, ...]}]
2022.06.30 17:19:52 INFO  web[][o.s.s.a.EmbeddedTomcat] HTTP connector enabled on port 9000
2022.06.30 17:19:53 INFO  web[][A.A.A.A.A.C] JavaScript/TypeScript frontend is enabled
2022.06.30 17:19:53 INFO  web[][o.s.s.p.UpdateCenterClient] Update center: https://update.sonarsource.org/update-center.properties (HTTP proxy: **********:****)
2022.06.30 17:19:55 INFO  web[][o.s.s.s.LogServerId] Server ID: B9BE0753-18ddab37a78c4ca
2022.06.30 17:19:55 WARN  web[][o.s.s.a.LogOAuthWarning] For security reasons, OAuth authentication should use HTTPS. You should set the property 'Administration > Configuration > Server base URL' to a HTTPS URL.
2022.06.30 17:19:55 INFO  web[][org.sonar.INFO] Security realm: LDAP
2022.06.30 17:19:55 INFO  web[][o.s.a.l.LdapSettingsManager] User mapping: LdapUserMapping{baseDn=*******, request=(&(objectClass=user)(sAMAccountName={0})), realNameAttribute=cn, emailAttribute=mail}
2022.06.30 17:19:55 INFO  web[][o.s.a.l.LdapSettingsManager] Groups will not be synchronized, because property 'ldap.group.baseDn' is empty.
2022.06.30 17:19:55 INFO  web[][o.s.a.l.LdapContextFactory] Test LDAP connection on ldap://*******:***: OK
2022.06.30 17:19:55 INFO  web[][org.sonar.INFO] Security realm started
2022.06.30 17:19:55 ERROR web[][o.s.s.p.Platform] Background initialization failed. Stopping SonarQube
org.apache.ibatis.exceptions.PersistenceException:
### Error committing transaction.  Cause: org.apache.ibatis.executor.BatchExecutorException: org.sonar.db.notification.NotificationQueueMapper.insert (batch index #1) failed. Cause: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO notifications (uuid,data,created_at)
    VALUES ('AYG1MoQNZP4GmwjHQ3ZS',?,1656602395661) was aborted: ERROR: record "new" has no field "id"
  Where: SQL statement "SELECT NEW.id IS null"
PL/pgSQL function trigger_fct_notifications_idt() line 3 at IF  Call getNextException to see other errors in the batch.
### Cause: org.apache.ibatis.executor.BatchExecutorException: org.sonar.db.notification.NotificationQueueMapper.insert (batch index #1) failed. Cause: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO notifications (uuid,data,created_at)
    VALUES ('AYG1MoQNZP4GmwjHQ3ZS',?,1656602395661) was aborted: ERROR: record "new" has no field "id"
  Where: SQL statement "SELECT NEW.id IS null"
PL/pgSQL function trigger_fct_notifications_idt() line 3 at IF  Call getNextException to see other errors in the batch.
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:226)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:217)
        at org.sonar.db.DbSessionImpl.commit(DbSessionImpl.java:42)
        at org.sonar.db.BatchSession.commit(BatchSession.java:168)
        at org.sonar.db.notification.NotificationQueueDao.insert(NotificationQueueDao.java:50)
        at org.sonar.server.notification.DefaultNotificationManager.scheduleForSending(DefaultNotificationManager.java:79)
        at org.sonar.server.authentication.DefaultAdminCredentialsVerifierImpl.sendEmailToAdmins(DefaultAdminCredentialsVerifierImpl.java:105)
        at org.sonar.server.authentication.DefaultAdminCredentialsVerifierImpl.start(DefaultAdminCredentialsVerifierImpl.java:61)
        at org.sonar.core.platform.StartableCloseableSafeLifecyleStrategy.start(StartableCloseableSafeLifecyleStrategy.java:40)
        at org.picocontainer.injectors.AbstractInjectionFactory$LifecycleAdapter.start(AbstractInjectionFactory.java:84)
        at org.picocontainer.behaviors.AbstractBehavior.start(AbstractBehavior.java:169)
        at org.picocontainer.behaviors.Stored$RealComponentLifecycle.start(Stored.java:132)
        at org.picocontainer.behaviors.Stored.start(Stored.java:110)
        at org.picocontainer.DefaultPicoContainer.potentiallyStartAdapter(DefaultPicoContainer.java:1016)
        at org.picocontainer.DefaultPicoContainer.startAdapters(DefaultPicoContainer.java:1009)
        at org.picocontainer.DefaultPicoContainer.start(DefaultPicoContainer.java:767)
        at org.sonar.core.platform.ComponentContainer.startComponents(ComponentContainer.java:136)
        at org.sonar.server.platform.platformlevel.PlatformLevel.start(PlatformLevel.java:90)
        at org.sonar.server.platform.platformlevel.PlatformLevel4.start(PlatformLevel4.java:581)
        at org.sonar.server.platform.PlatformImpl.start(PlatformImpl.java:213)
        at org.sonar.server.platform.PlatformImpl.startLevel34Containers(PlatformImpl.java:187)
        at org.sonar.server.platform.PlatformImpl.access$500(PlatformImpl.java:46)
        at org.sonar.server.platform.PlatformImpl$1.lambda$doRun$0(PlatformImpl.java:120)
        at org.sonar.server.platform.PlatformImpl$AutoStarterRunnable.runIfNotAborted(PlatformImpl.java:370)
        at org.sonar.server.platform.PlatformImpl$1.doRun(PlatformImpl.java:120)
        at org.sonar.server.platform.PlatformImpl$AutoStarterRunnable.run(PlatformImpl.java:354)
        at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: org.apache.ibatis.executor.BatchExecutorException: org.sonar.db.notification.NotificationQueueMapper.insert (batch index #1) failed. Cause: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO notifications (uuid,data,created_at)
    VALUES ('AYG1MoQNZP4GmwjHQ3ZS',?,1656602395661) was aborted: ERROR: record "new" has no field "id"
  Where: SQL statement "SELECT NEW.id IS null"
PL/pgSQL function trigger_fct_notifications_idt() line 3 at IF  Call getNextException to see other errors in the batch.
        at org.apache.ibatis.executor.BatchExecutor.doFlushStatements(BatchExecutor.java:149)
        at org.apache.ibatis.executor.BaseExecutor.flushStatements(BaseExecutor.java:129)
        at org.apache.ibatis.executor.BaseExecutor.flushStatements(BaseExecutor.java:122)
        at org.apache.ibatis.executor.BaseExecutor.commit(BaseExecutor.java:242)
        at org.apache.ibatis.executor.CachingExecutor.commit(CachingExecutor.java:119)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:223)
        ... 26 common frames omitted
Caused by: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO notifications (uuid,data,created_at)
    VALUES ('AYG1MoQNZP4GmwjHQ3ZS',?,1656602395661) was aborted: ERROR: record "new" has no field "id"
  Where: SQL statement "SELECT NEW.id IS null"
PL/pgSQL function trigger_fct_notifications_idt() line 3 at IF  Call getNextException to see other errors in the batch.
        at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
        at org.postgresql.core.ResultHandlerDelegate.handleError(ResultHandlerDelegate.java:52)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:559)
        at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:887)
        at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:910)
        at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1638)
        at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242)
        at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242)
        at org.apache.ibatis.executor.BatchExecutor.doFlushStatements(BatchExecutor.java:123)
        ... 31 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: record "new" has no field "id"
  Where: SQL statement "SELECT NEW.id IS null"
PL/pgSQL function trigger_fct_notifications_idt() line 3 at IF
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
        ... 38 common frames omitted
2022.06.30 17:19:56 INFO  web[][o.s.p.ProcessEntryPoint] Hard stopping process
2022.06.30 17:19:56 WARN  web[][o.a.c.l.WebappClassLoaderBase] The web application [sonar] appears to have started a thread named [Thread-2] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:\n java.base@11.0.15/java.net.SocketInputStream.socketRead0(Native Method)\n java.base@11.0.15/java.net.SocketInputStream.socketRead(SocketInputStream.java:115)\n java.base@11.0.15/java.net.SocketInputStream.read(SocketInputStream.java:168)\n java.base@11.0.15/java.net.SocketInputStream.read(SocketInputStream.java:140)\n java.base@11.0.15/java.io.BufferedInputStream.fill(BufferedInputStream.java:252)\n java.base@11.0.15/java.io.BufferedInputStream.read1(BufferedInputStream.java:292)\n java.base@11.0.15/java.io.BufferedInputStream.read(BufferedInputStream.java:351)\n java.naming@11.0.15/com.sun.jndi.ldap.Connection.run(Connection.java:855)\n java.base@11.0.15/java.lang.Thread.run(Thread.java:829)

Could you please help me to resolve this issue ?
Thank you.

OS: RHEL 7.9
DB: PostgreSQL 11.16

ganncamp · June 30, 2022, 6:01pm

Hi,

The Upgrade Guide recommends rebuilding your indices (indexes?) as an “additional” step. I don’t suppose you did that? I ask because lately other folks have gotten strange DB errors that went away after they rebuilt the index.

So if you haven’t, can you try it?

Ann

kbolivard · July 1, 2022, 7:11am

Hello G Ann,

Thank you for your reply.
Indeed, I did not rebuild the index. I’ll try it and get back to you soon.

Kenny.

kbolivard · July 4, 2022, 2:25pm

Hello,

Rebuilding the indexes did not fix the problem. I still have the same error.
Do you have any other solution?

Kenny.

ganncamp · July 5, 2022, 5:13pm

Hi Kenny,

I’ve referred this for more expert attention.

Ann

Lukasz_Jarocki · July 7, 2022, 1:25pm

Warm welcome @kbolivard , thanks for reaching to us.

In order to troubleshoot the issue could you provide us a little bit more information:

Do you have access and could you share the full web.log that includes information about database migration operations that were executed when you updated from 7.9 to 8.9? It is fine if you share it in the private message. If the full log is not possible to share, then at least it would be great to receive part of this log starting from line similar to this one:

2022.07.07 15:09:52 INFO  web[][DbMigrations] Executing DB migrations...

Do you have access to database that is used by SonarQube 8.9? If that’s the case could you share with us the list of columns in the notifications table?
Could you share with us the result of the following query?

select * from schema_migrations where "version" like '340%'

kbolivard · July 8, 2022, 3:31pm

Hi Lukasz
Stupid question: how to send a private message?

ganncamp · July 8, 2022, 3:38pm

Hi,

That’s not a stupid question at all!

In fact a privileged user needs to initiate a private thread. I’ve done so & added Lukasz to it.

HTH,
Ann

Lukasz_Jarocki · July 11, 2022, 9:11am

Thank you @kbolivard for providing additional information, it was helpful to analyze the issue you are experiencing.

From my analysis it seems that your SonarQube database was converted some time ago from Oracle to PostgreSQL. This conversion has likely left over some trigger(s) in your database schema which are not needed to run SonarQube 8.9 on PostgreSQL and, even worse, can cause SonarQube to behave unexpectedly.

All in all, it is very likely that the issue you are experiencing is caused by an unexpected trigger on your database schema that is referring to columns that don’t exist anymore.

Because of this I would ask you to:

Could you please confirm, that some time ago you (or your company) were running SonarQube on Oracle database and you migrated to PostgreSQL? Did you use SonarQube DB Copy Tool to do it or you had some unofficial way of doing it (in example ora2pg)?
If this is the case, remove this trigger trigger_fct_notifications_idt from your database schema
If this helps (you are not receiving above error anymore), please share the results of the following query: select trigger_name from information_schema.triggers as I am worried that your PostgreSQL may contain more unneeded objects after conversion from Oracle.

kbolivard · July 12, 2022, 9:06am

Indeed, I can confirm that I migrated the database from Oracle to Postgres… when upgrading from 5.6.7 LTS → 6.7.5 LTS in 2018 (we are longtime Sonarqube users ).

Deleting the trigger solved the error but, as expected, I have a new error on another:

### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: record "new" has no f                                                   ield "id"
  Where: SQL statement "SELECT NEW.id IS null"
PL/pgSQL function trigger_fct_metrics_idt() line 3 at IF
### The error may exist in org.sonar.db.metric.MetricMapper
### The error may involve org.sonar.db.metric.MetricMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO metrics (     uuid, name, description, direction, domain, short_name, qualitati                                                   ve, val_type, user_managed, enabled, worst_value,     best_value, optimized_best_value, hidden, dele                                                   te_historical_data, decimal_scale)     VALUES (     ?, ?, ?, ?,     ?, ?, ?,     ?, ?, ?,     ?, ?,                                                        ?, ?, ?,     ?     )
### Cause: org.postgresql.util.PSQLException: ERROR: record "new" has no field "id"
  Where: SQL statement "SELECT NEW.id IS null"
PL/pgSQL function trigger_fct_metrics_idt() line 3 at IF
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
...

Here is the result of the query select trigger_name from information_schema.triggers :

"active_rule_parameters_idt"
"ce_queue_idt"
"active_rules_idt"
"ce_activity_idt"
"events_idt"
"group_roles_idt"
"groups_idt"
"duplications_index_idt"
"file_sources_idt"
"manual_measures_idt"
"metrics_idt"
"perm_templates_groups_idt"
"perm_templates_users_idt"
"permission_templates_idt"
"project_qprofiles_idt"
"project_measures_idt"
"projects_idt"
"quality_gate_conditions_idt"
"quality_gates_idt"
"rules_parameters_idt"
"rules_profiles_idt"
"snapshots_idt"
"user_roles_idt"
"user_tokens_idt"
"users_idt"
"rules_idt"

Lukasz_Jarocki · July 13, 2022, 7:24am

Hi @kbolivard

As these triggers are not needed on PostgreSQL SQ 8.9 LTS schema it is safe to remove them, this should hopefully let you enjoy new features in SonarQube 8.9 LTS

And to be clear for the future readers of this thread: it is not recommended that our users modify by hand the SQ schema and it should not be needed. But in your case it seems like the most reasonable option. Just make sure you backup your DB before doing any modifications to it.

kbolivard · July 13, 2022, 3:57pm

Hi @Lukasz_Jarocki ,

Good news: removing all unneeded triggers solved my problem. My 8.9 LTS server starts fine !
Thanks for your help.

system · July 20, 2022, 3:57pm

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.