Sonar upgrade from 9.9 to 10.3 - DB error

arun2022 · March 20, 2024, 5:04pm

Must-share information (formatted with Markdown):

which versions are you using (SonarQube, Scanner, Plugin, and any relevant extension) - 9.9-community and postgres - 10.1
how is SonarQube deployed: Docker -
what are you trying to achieve - Upgrade from 9.9-community to 10.3-community
what have you tried so far to achieve this

Do not share screenshots of logs – share the text itself (bonus points for being well-formatted)!

2024.03.20 15:29:30 ERROR ce[][o.s.c.t.CeWorkerImpl] Failed to pop the queue of analysis reports
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$2"
  Position: 445
### The error may exist in org.sonar.db.ce.CeQueueMapper
### The error may involve org.sonar.db.ce.CeQueueMapper.selectEligibleForPeek-Inline
### The error occurred while setting parameters
### SQL: select cq.uuid as ceTaskUuid, cq.created_at as createdAt           from       ce_queue cq             left join ce_queue cq2 on cq.entity_uuid=cq2.entity_uuid and cq2.status <>'PENDING'     where       cq.status='PENDING'and cq.started_at is null       and cq2.uuid is null                 and cq.task_type <> 'ISSUE_SYNC'                       order by       cq.created_at asc,       cq.uuid asc               offset ? rows fetch next ? rows only
### Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$2"
  Position: 445
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:156)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:142)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:75)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)
        at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:142)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
        at jdk.proxy2/jdk.proxy2.$Proxy47.selectEligibleForPeek(Unknown Source)
        at org.sonar.db.ce.CeQueueDao.selectEligibleForPeek(CeQueueDao.java:198)
        at org.sonar.ce.queue.NextPendingTaskPicker.findPendingTask(NextPendingTaskPicker.java:59)
        at org.sonar.ce.queue.InternalCeQueueImpl.peek(InternalCeQueueImpl.java:83)
        at org.sonar.ce.taskprocessor.CeWorkerImpl.tryAndFindTaskToExecute(CeWorkerImpl.java:170)
        at org.sonar.ce.taskprocessor.CeWorkerImpl.findAndProcessTask(CeWorkerImpl.java:153)
        at org.sonar.ce.taskprocessor.CeWorkerImpl$TrackRunningState.get(CeWorkerImpl.java:135)
        at org.sonar.ce.taskprocessor.CeWorkerImpl.call(CeWorkerImpl.java:87)
        at org.sonar.ce.taskprocessor.CeWorkerImpl.call(CeWorkerImpl.java:53)
        at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:131)
        at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:75)
        at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:82)
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
        at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
        at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$2"
  Position: 445
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
        at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:177)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:80)
        at org.apache.ibatis.executor.ReuseExecutor.doQuery(ReuseExecutor.java:62)
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:333)
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:158)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:110)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:90)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:154)
        ... 24 common frames omitted

arun2022 · March 20, 2024, 5:08pm

I tried upgrading and DB logs showed to run url/setup. After running the setup, I was able to log-in Sonar, but at the top of the page reindexing message was there and it didn’t complete that at all.

Reindexing in progress. Most features are available. Some details only show upon completion. More info
0 out of 39 projects reindexed.
See Background Tasks for more information.

ganncamp · March 21, 2024, 4:14pm

Hi,

Do you still see the reindexing message?

Ann

arun2022 · March 21, 2024, 4:24pm

yes Ann. I even tried to upgrade again using my backup and got the same issue.

Also tried like this flow 9.9.1-community → 9.9.4 (LTS) → 10.3-community

9.9.4 was successful, but 10.3 failed.

ganncamp · March 21, 2024, 4:28pm

Hi,

What errors do you see in your server logs?

Ann

arun2022 · March 21, 2024, 4:43pm

so once I update to 10.3, it asks me to run the setup to upgrade the DB first

2024.03.21 16:34:42 INFO  web[][o.s.s.p.ServerFileSystemImpl] SonarQube home: /opt/sonarqube
2024.03.21 16:34:42 INFO  web[][o.s.s.u.SystemPasscodeImpl] System authentication by passcode is disabled
2024.03.21 16:34:42 INFO  web[][o.s.c.e.CoreExtensionsLoader] Loaded core extensions: Community Branch Plugin
2024.03.21 16:34:42 WARN  web[][o.s.s.p.DatabaseServerCompatibility] The database must be manually upgraded. Please backup the database and browse /setup. For more information: https://docs.sonarsource.com/sonarqube/latest/setup/upgrading
2024.03.21 16:34:42 WARN  app[][startup] ################################################################################
2024.03.21 16:34:42 WARN  app[][startup] The database must be manually upgraded. Please backup the database and browse /setup. For more information: https://docs.sonarsource.com/sonarqube/latest/setup/upgrading
2024.03.21 16:34:42 WARN  app[][startup] ################################################################################
2024.03.21 16:34:43 INFO  web[][o.s.s.p.ServerPluginManager] Deploy C# Code Quality and Security / 9.13.0.79967 / 0b84a168e0d212178722ac9865627046c515b212
2024.03.21 16:34:43 INFO  web[][o.s.s.p.ServerPluginManager] Deploy Clean as You Code / 2.2.2.656 / a856086cd01263bf020d3a6aa62763cb76b74120

After upgrading DB, I can see the UI up and login also. Then I see the banner saying like this

Reindexing in progress. Most features are available. Some details only show upon completion. More info
0 out of 39 projects reindexed.
See Background Tasks for more information.

Below is the sonar and postgres logs

sonar

024.03.21 16:39:52 ERROR ce[][o.s.c.t.CeWorkerImpl] Failed to pop the queue of analysis reports
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$2"
  Position: 484
### The error may exist in org.sonar.db.ce.CeQueueMapper
### The error may involve org.sonar.db.ce.CeQueueMapper.selectEligibleForPeek-Inline
### The error occurred while setting parameters
### SQL: select cq.uuid as ceTaskUuid, cq.created_at as createdAt           from       ce_queue cq               left join components c on c.uuid = cq.entity_uuid and c.qualifier <> 'VW'             left join ce_queue cq2 on cq.entity_uuid=cq2.entity_uuid and cq2.status <>'PENDING'     where       cq.status='PENDING'       and cq.started_at is null       and cq2.uuid is null                       order by       cq.created_at asc,       cq.uuid asc               offset ? rows fetch next ? rows only
### Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$2"
  Position: 484
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:156)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:142)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:75)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)
        at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:142)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
        at jdk.proxy2/jdk.proxy2.$Proxy47.selectEligibleForPeek(Unknown Source)
        at org.sonar.db.ce.CeQueueDao.selectEligibleForPeek(CeQueueDao.java:198)
        at org.sonar.ce.queue.NextPendingTaskPicker.findPendingTask(NextPendingTaskPicker.java:59)
        at org.sonar.ce.queue.InternalCeQueueImpl.peek(InternalCeQueueImpl.java:83)
        at org.sonar.ce.taskprocessor.CeWorkerImpl.tryAndFindTaskToExecute(CeWorkerImpl.java:170)
        at org.sonar.ce.taskprocessor.CeWorkerImpl.findAndProcessTask(CeWorkerImpl.java:153)
        at org.sonar.ce.taskprocessor.CeWorkerImpl$TrackRunningState.get(CeWorkerImpl.java:135)
        at org.sonar.ce.taskprocessor.CeWorkerImpl.call(CeWorkerImpl.java:87)
        at org.sonar.ce.taskprocessor.CeWorkerImpl.call(CeWorkerImpl.java:53)
        at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:131)
        at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:75)
        at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:82)
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
        at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
        at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$2"
  Position: 484
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
        at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:177)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:80)
        at org.apache.ibatis.executor.ReuseExecutor.doQuery(ReuseExecutor.java:62)
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:333)
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:158)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:110)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:90)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:154)
        ... 24 common frames omitted
2024.03.21 16:39:54 ERROR ce[][o.s.c.t.CeWorkerImpl] Failed to pop the queue of analysis reports
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$2"
  Position: 445

postgres error

024-03-21 16:40:56.807 UTC [47] ERROR:  syntax error at or near "$2" at character 484
2024-03-21 16:40:56.807 UTC [47] STATEMENT:  select cq.uuid as ceTaskUuid, cq.created_at as createdAt

            from
              ce_queue cq

                left join components c on c.uuid = cq.entity_uuid and c.qualifier <> 'VW'

              left join ce_queue cq2 on cq.entity_uuid=cq2.entity_uuid and cq2.status <>'PENDING'
            where
              cq.status='PENDING'
              and cq.started_at is null
              and cq2.uuid is null



            order by
              cq.created_at asc,
              cq.uuid asc


            offset $1 rows fetch next $2 rows only
2024-03-21 16:40:58.813 UTC [47] ERROR:  syntax error at or near "$2" at character 445
2024-03-21 16:40:58.813 UTC [47] STATEMENT:  select cq.uuid as ceTaskUuid, cq.created_at as createdAt

            from
              ce_queue cq

              left join ce_queue cq2 on cq.entity_uuid=cq2.entity_uuid and cq2.status <>'PENDING'
            where
              cq.status='PENDING'
              and cq.started_at is null
              and cq2.uuid is null

                and cq.task_type <> 'ISSUE_SYNC'



            order by
              cq.created_at asc,
              cq.uuid asc


            offset $1 rows fetch next $2 rows only

ganncamp · March 21, 2024, 4:46pm

Hi,

Sorry, but I can’t help you.

Not only is this plugin very much not supported in this community, but we’ve seen it mess up people’s databases.

And unfortunately, I don’t know how to recover your DB to a usable state from here.

Ann

arun2022 · March 21, 2024, 4:49pm

Thanks Ann, let me try by disabling that plugin and upgrade again using my backup

arun2022 · March 21, 2024, 5:49pm

Hi Ann,

I just tried without the branch plugin and ended up with the same error.

9.9.4-community → 10.3-community

Do I need to change postgres version from 10.1 to something else?

Thanks

ganncamp · March 21, 2024, 6:20pm

Hi,

As I said before:

Ann

arun2022 · March 22, 2024, 1:23am

Actually, without the plugin, I upgraded sonar from 9.9 to 10.2 (including all the minor versions) and restarted by adding the plugin with 10.2, and worked finally.
thanks for the assistance.

Topic		Replies	Views
Sonar upgrade from 9.X to 10.1 - sonar and postgres errors SonarQube Server / Community Build	13	528	May 24, 2024
Database connection dies on report upload SonarQube Server / Community Build java , sonarqube , database	7	1790	July 1, 2021
Errors from sonarqube:10.1.0-community onwards SonarQube Server / Community Build sonarqube	4	260	May 8, 2024
Sonarqube upgrade fro 8.9.10 to9.9.1 LTS SonarQube Server / Community Build migration	1	395	August 18, 2023
Error during update v7.9.3 (LTS) to v8.9.9 (LTS) SonarQube Server / Community Build sonarqube	12	825	July 20, 2022

Sonar upgrade from 9.9 to 10.3 - DB error

Related topics