gocd: Dashboard does not show all pipelines after upgrade to v18.4.0

Issue Type

Bug Report

Summary

After an upgrade from v18.3.0 to v18.4.0 we can no longer see our pipelines on the dashboard. For all our pipeline definitions we use the YAML Configuration Plugin, so this makes everything in code.

Environment

Basic environment details

Go Version: 18.4.0 (6640-1fb2812cfa45eee8502e420443f2b46d81c51f98)
JAVA Version: 1.8.0_131-8u131-b11-2ubuntu1.16.04.3-b11
OS: Linux 4.4.0-1057-aws amd64

Steps to Reproduce

Create a backup from a running server (v18.3.0)
Upload backup to a remote location
Create a new VM and install the latest version of GoCD (v18.4.0)
Restore from the backup and start the server

Expected Results

Everything is restored without issues and:

Users/Roles are still there
Configuration is still present
Plugin configuration is still present
All pipelines are visible in the dashboard

Actual Results

Users/Roles are still there
Configuration is still present
Plugin configuration is still present
Maximum of 3 pipelines are visible (the rest is NOT present)

Possible Fix

This might be related to the pipeline history, but still not sure.

Log snippets

2018-05-09 12:30:20,614 ERROR [77@MessageListener for ScheduleCheckListener] ScheduleCheckListener:47 - SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; Error: executeQueryForObject returned too many results.; nested ex
ception is java.sql.SQLException: Error: executeQueryForObject returned too many results.
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; Error: executeQueryForObject returned too many results.; nested exception is java.sql.SQLException: Error: executeQuer
yForObject returned too many results.
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:203)
        at com.thoughtworks.go.server.transaction.SqlMapClientDaoSupport$SqlMapClientTemplate.execute(SqlMapClientDaoSupport.java:112)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:268)
        at com.thoughtworks.go.server.transaction.SqlMapClientDaoSupport$SqlMapClientTemplate.access$501(SqlMapClientDaoSupport.java:39)
        at com.thoughtworks.go.server.transaction.SqlMapClientDaoSupport$SqlMapClientTemplate$4.execute(SqlMapClientDaoSupport.java:97)
        at com.thoughtworks.go.server.transaction.SqlMapClientDaoSupport$SqlMapClientTemplate.executeInternal(SqlMapClientDaoSupport.java:120)
        at com.thoughtworks.go.server.transaction.SqlMapClientDaoSupport$SqlMapClientTemplate.queryForObject(SqlMapClientDaoSupport.java:95)
        at com.thoughtworks.go.server.dao.PipelineSqlMapDao.pauseState(PipelineSqlMapDao.java:855)
        at com.thoughtworks.go.server.service.PipelinePauseService.pipelinePauseInfo(PipelinePauseService.java:117)
        at com.thoughtworks.go.server.service.PipelinePauseService.isPaused(PipelinePauseService.java:122)
        at com.thoughtworks.go.server.service.PipelinePauseChecker.check(PipelinePauseChecker.java:34)
        at com.thoughtworks.go.server.service.CompositeChecker.check(CompositeChecker.java:34)
        at com.thoughtworks.go.server.service.SchedulingCheckerService.canAutoTriggerProducer(SchedulingCheckerService.java:126)
        at com.thoughtworks.go.server.service.AutoBuild.canProduce(AutoBuild.java:82)
        at com.thoughtworks.go.server.scheduling.BuildCauseProducerService.newProduceBuildCause(BuildCauseProducerService.java:152)
        at com.thoughtworks.go.server.scheduling.BuildCauseProducerService.newProduceBuildCause(BuildCauseProducerService.java:148)
        at com.thoughtworks.go.server.scheduling.BuildCauseProducerService.autoSchedulePipeline(BuildCauseProducerService.java:110)
        at com.thoughtworks.go.server.scheduling.ScheduleCheckListener.onMessage(ScheduleCheckListener.java:44)
        at com.thoughtworks.go.server.scheduling.ScheduleCheckListener.onMessage(ScheduleCheckListener.java:25)
        at com.thoughtworks.go.server.messaging.activemq.JMSMessageListenerAdapter.runImpl(JMSMessageListenerAdapter.java:73)
        at com.thoughtworks.go.server.messaging.activemq.JMSMessageListenerAdapter.run(JMSMessageListenerAdapter.java:53)
        at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Error: executeQueryForObject returned too many results.
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForObject(MappedStatement.java:124)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:518)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:493)
        at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:106)
        at org.springframework.orm.ibatis.SqlMapClientTemplate$1.doInSqlMapClient(SqlMapClientTemplate.java:270)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:200)
        ... 21 common frames omitted
2018-05-09 12:30:20,615 ERROR [77@MessageListener for ScheduleCheckListener] JMSMessageListenerAdapter:77 - Exception thrown in message handling by listener com.thoughtworks.go.server.scheduling.ScheduleCheckListener@5ac90239
java.lang.RuntimeException: org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; Error: executeQueryForObject returned too many results.; nested exception is java.sql.SQLException: Error: executeQueryForObject returned too many results.
        at com.thoughtworks.go.server.scheduling.ScheduleCheckListener.onMessage(ScheduleCheckListener.java:48)
        at com.thoughtworks.go.server.scheduling.ScheduleCheckListener.onMessage(ScheduleCheckListener.java:25)
        at com.thoughtworks.go.server.messaging.activemq.JMSMessageListenerAdapter.runImpl(JMSMessageListenerAdapter.java:73)
        at com.thoughtworks.go.server.messaging.activemq.JMSMessageListenerAdapter.run(JMSMessageListenerAdapter.java:53)
        at java.lang.Thread.run(Thread.java:748)
Caused by: org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; Error: executeQueryForObject returned too many results.; nested exception is java.sql.SQLException: Error: executeQueryForObject returned too many results.
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:203)
        at com.thoughtworks.go.server.transaction.SqlMapClientDaoSupport$SqlMapClientTemplate.execute(SqlMapClientDaoSupport.java:112)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:268)
        at com.thoughtworks.go.server.transaction.SqlMapClientDaoSupport$SqlMapClientTemplate.access$501(SqlMapClientDaoSupport.java:39)
        at com.thoughtworks.go.server.transaction.SqlMapClientDaoSupport$SqlMapClientTemplate$4.execute(SqlMapClientDaoSupport.java:97)
        at com.thoughtworks.go.server.transaction.SqlMapClientDaoSupport$SqlMapClientTemplate.executeInternal(SqlMapClientDaoSupport.java:120)
        at com.thoughtworks.go.server.transaction.SqlMapClientDaoSupport$SqlMapClientTemplate.queryForObject(SqlMapClientDaoSupport.java:95)
        at com.thoughtworks.go.server.dao.PipelineSqlMapDao.pauseState(PipelineSqlMapDao.java:855)
        at com.thoughtworks.go.server.service.PipelinePauseService.pipelinePauseInfo(PipelinePauseService.java:117)
        at com.thoughtworks.go.server.service.PipelinePauseService.isPaused(PipelinePauseService.java:122)
        at com.thoughtworks.go.server.service.PipelinePauseChecker.check(PipelinePauseChecker.java:34)
        at com.thoughtworks.go.server.service.CompositeChecker.check(CompositeChecker.java:34)
        at com.thoughtworks.go.server.service.SchedulingCheckerService.canAutoTriggerProducer(SchedulingCheckerService.java:126)
        at com.thoughtworks.go.server.service.AutoBuild.canProduce(AutoBuild.java:82)
        at com.thoughtworks.go.server.scheduling.BuildCauseProducerService.newProduceBuildCause(BuildCauseProducerService.java:152)
        at com.thoughtworks.go.server.scheduling.BuildCauseProducerService.newProduceBuildCause(BuildCauseProducerService.java:148)
        at com.thoughtworks.go.server.scheduling.BuildCauseProducerService.autoSchedulePipeline(BuildCauseProducerService.java:110)
        at com.thoughtworks.go.server.scheduling.ScheduleCheckListener.onMessage(ScheduleCheckListener.java:44)
        ... 4 common frames omitted
Caused by: java.sql.SQLException: Error: executeQueryForObject returned too many results.
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForObject(MappedStatement.java:124)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:518)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:493)
        at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:106)
        at org.springframework.orm.ibatis.SqlMapClientTemplate$1.doInSqlMapClient(SqlMapClientTemplate.java:270)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:200)
        ... 21 common frames omitted

–>

Screenshots

As you can see on the screenshots the pipelines on the dashboard are just a small part of all the other pipelines (seen in the Admin section)

screen shot 2018-05-09 at 14 34 23

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 32 (22 by maintainers)

Commits related to this issue

Fixed an issue with PipelineLabelCorrector which did not handle removing duplicate entries in db for config-repo pipelines as well as deleted pipelines - #4723 — committed to jyotisingh/gocd by jyotisingh 6 years ago
Fixed an issue with PipelineLabelCorrector which did not handle removing duplicate entries in db for config-repo pipelines as well as deleted pipelines - #4723 — committed to jyotisingh/gocd by jyotisingh 6 years ago
Fixed an issue with PipelineLabelCorrector which did not handle removing duplicate entries in db for config-repo pipelines as well as deleted pipelines - #4723 — committed to jyotisingh/gocd by jyotisingh 6 years ago
Fixed an issue with PipelineLabelCorrector which did not handle removing duplicate entries in db for config-repo pipelines as well as deleted pipelines - #4723 — committed to jyotisingh/gocd by jyotisingh 6 years ago
Fixed an issue with PipelineLabelCorrector which did not handle removing duplicate entries in db for config-repo pipelines as well as deleted pipelines - #4723 — committed to jyotisingh/gocd by jyotisingh 6 years ago
Fixed an issue with PipelineLabelCorrector which did not handle removing duplicate entries in db for config-repo pipelines as well as deleted pipelines - #4723 — committed to jyotisingh/gocd by jyotisingh 6 years ago
Fixed an issue with PipelineLabelCorrector which did not handle removing duplicate entries in db for config-repo pipelines as well as deleted pipelines - #4723 — committed to jyotisingh/gocd by jyotisingh 6 years ago
Fixed an issue with PipelineLabelCorrector which did not handle removing duplicate entries in db for config-repo pipelines as well as deleted pipelines - #4723 — committed to jyotisingh/gocd by jyotisingh 6 years ago

Most upvoted comments

Noticed a similar issue on one of the internal dogfood environments, hence reopening the issue.

The log entry showed the following:

2018-05-18 00:48:40,150 WARN  [Dashboard-Queue-Processor] MultiplexingQueueProcessor:68 - Failed to handle action in Dashboard queue
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; Error: executeQueryForObject returned too many results.; nested exception is java.sql.SQLException: Error: executeQueryForObject returned too many results.
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:203)
        at com.thoughtworks.go.server.transaction.SqlMapClientDaoSupport$SqlMapClientTemplate.execute(SqlMapClientDaoSupport.java:112)
        at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:268)
        at com.thoughtworks.go.server.transaction.SqlMapClientDaoSupport$SqlMapClientTemplate.access$501(SqlMapClientDaoSupport.java:39)
        at com.thoughtworks.go.server.transaction.SqlMapClientDaoSupport$SqlMapClientTemplate$4.execute(SqlMapClientDaoSupport.java:97)
        at com.thoughtworks.go.server.transaction.SqlMapClientDaoSupport$SqlMapClientTemplate.executeInternal(SqlMapClientDaoSupport.java:120)
        at com.thoughtworks.go.server.transaction.SqlMapClientDaoSupport$SqlMapClientTemplate.queryForObject(SqlMapClientDaoSupport.java:95)
        at com.thoughtworks.go.server.dao.PipelineSqlMapDao.pauseState(PipelineSqlMapDao.java:784)
        at com.thoughtworks.go.server.service.PipelinePauseService.pipelinePauseInfo(PipelinePauseService.java:118)
        at com.thoughtworks.go.server.dashboard.GoDashboardCurrentStateLoader.pipelineModelFor(GoDashboardCurrentStateLoader.java:163)
        at com.thoughtworks.go.server.dashboard.GoDashboardCurrentStateLoader.createGoDashboardPipeline(GoDashboardCurrentStateLoader.java:155)
        at com.thoughtworks.go.server.dashboard.GoDashboardCurrentStateLoader.access$200(GoDashboardCurrentStateLoader.java:53)
        at com.thoughtworks.go.server.dashboard.GoDashboardCurrentStateLoader$1$1.visit(GoDashboardCurrentStateLoader.java:115)
        at com.thoughtworks.go.config.merge.MergePipelineConfigs.accept(MergePipelineConfigs.java:524)
        at com.thoughtworks.go.server.dashboard.GoDashboardCurrentStateLoader$1.visit(GoDashboardCurrentStateLoader.java:109)
        at com.thoughtworks.go.domain.PipelineGroups.accept(PipelineGroups.java:115)
        at com.thoughtworks.go.config.BasicCruiseConfig.accept(BasicCruiseConfig.java:951)
        at com.thoughtworks.go.server.dashboard.GoDashboardCurrentStateLoader.allPipelines(GoDashboardCurrentStateLoader.java:106)
        at com.thoughtworks.go.server.service.GoDashboardService.updateCacheForAllPipelinesIn(GoDashboardService.java:92)
        at com.thoughtworks.go.server.dashboard.GoDashboardConfigChangeHandler.call(GoDashboardConfigChangeHandler.java:40)
        at com.thoughtworks.go.server.dashboard.GoDashboardActivityListener$2.call(GoDashboardActivityListener.java:120)
        at com.thoughtworks.go.server.messaging.MultiplexingQueueProcessor$1.run(MultiplexingQueueProcessor.java:63)
Caused by: java.sql.SQLException: Error: executeQueryForObject returned too many results.
        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForObject(MappedStatement.java:124)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:518)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:493)

Issue details:

PipelineLabelCorrector was added as part of 18.4 to find and remove duplicates from PipelineLabelCounts table. The duplicates would be present in db if the case of the pipelinename was changed. https://github.com/gocd/gocd/pull/4690 took care of removing such duplicates as well as disallowing insertion of duplicates in future. As a part of that PR, checks were added to ensure that only one entry was present in db for a given pipeline.

This, however, was handled for pipelines which were part of the config, but not for the ones which were deleted from the config. The same is also true for the pipelines defined in config-repos, meaning such pipelines wouldn’t be available as part of allPipelineConfigs until the first MDU of the config-repo completes which in many cases would be after the corrector kicks in. As such, duplicate entries for such pipelines are not deleted and hence a bunch of things break.

In terms of the impact, even if one of the pipelines had a duplicate entry in db, all other pipelines defined in that config-repo would not show up. The impact of this is not only isolated to the dashboard alone though. It also affects the schedule, pause, unpause of the pipelines with duplicate entries.

Since we never got access to the logs or db from the original reporter, I cannot say with certainty if they faced the exact same issue. However, it seems very similar to what we noticed.

jyotisingh on May 18, 2018

@arvindsv @jyotisingh Restoring from our backup doesn’t seem to work when upgrading to both v18.4.0 and v18.5.0.

The solution we found to keep on going for now is to use only config-repo and config-dir from our backup and discard the database (it’s probably corrupted somehow). This made all pipelines visible again and everything is up and running. The only part missing is the pipeline history.

btw, I created a support case so we’ll see what happens.

szamfirov on May 14, 2018

Hi, I’m working with @szamfirov on the same instance / configuration. I’ve just updated to the latest version and the deltas are applied successfully.

At this moment the only pipeline which appears in the dashboard in the one which is created manually. The other pipelines are visible in the admin interface but don’t appear in the dashboard.

Is there any possibility the query the HSQL database so we can provide more information for this issue?

hdanes on May 14, 2018