Clearing Pipeline history...is it possible?

[Jeff]

I'm using GoCD 17.10.0 and converting our existing config.xml pipelines to the Git-based JSON pipeline-as-code format.

Before I get into the details, I presume the issues I'm seeing would not show if I had renamed my pipelines.  However, I do NOT want to change the pipeline names and I'd prefer NOT to clear the entire database, but I am open to doing so.

The rest of the story:

Before I committed the new JSON pipelines to GIT, I removed the XML pipeline definitions from the master config.xml.  My pipelines are now in a weird state.  The label of two of the 3 pipelines reset back to 1, even though it still shows the older 'higher' revisions in the history:

Now when I try to view the status of a job or view the graphical Map of a pipeline instance, I get errors such as:

Sorry, an unexpected error occurred [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.]. :( Please check the server logs for more information.

or 

The top-level (furthest upstream) pipeline revision did not reset to '1' and when I schedule a new one, it does NOT trigger the downstream pipeline.  I presume this is also related to the labels being off:

Is there a way I can just purge the history for these specific pipelines?  

If not, how do I clear the database and start from scratch?

Thanks!

PS - Below are some of the exceptions in the logs:

Last Modified: 2016/06/22 15:22:13

Revision: FileReputationService_ACC/180/Approval/1

UserName: Unknown

EmailAddress: null

Comment: Unknown

PipelineLabel: 180

]

        at com.thoughtworks.go.domain.materials.Modifications.since(Modifications.java:100)

        at com.thoughtworks.go.server.persistence.MaterialRepository.findModificationsSince(MaterialRepository.java:707)

        at com.thoughtworks.go.server.materials.MaterialChecker.findRevisionsSince(MaterialChecker.java:68)

        at com.thoughtworks.go.server.scheduling.BuildCauseProducerService.newProduceBuildCause(BuildCauseProducerService.java:184)

        at com.thoughtworks.go.server.scheduling.BuildCauseProducerService.newProduceBuildCause(BuildCauseProducerService.java:147)

        at com.thoughtworks.go.server.scheduling.BuildCauseProducerService.autoSchedulePipeline(BuildCauseProducerService.java:109)

        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)

AND

2017-09-21 10:24:42,237 ERROR [79@MessageListener for ScheduleCheckListener] BuildCauseProducerService:219 - Error while scheduling pipeline: filereputationservice_ACC

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.loadPipelineInstanceModelByNameAndCounter(PipelineSqlMapDao.java:352)

        at com.thoughtworks.go.server.dao.PipelineSqlMapDao.findPipelineHistoryByNameAndCounter(PipelineSqlMapDao.java:340)

[Jyoti Singh]

Could you check if the pipeline name in the json file had a different case from the one in config.xml? Pipeline names are usually case insensitive, but then there is this bug 710 which looks similar to what you see. As long as you ensure the name is specified using the same case as config.xml you should be ok. You may try this for the one other pipeline just to be verify if this was the case.

Is there a way I can just purge the history for these specific pipelines?  

No. Well, it is, but that involves deleting things from many tables and it would be error prone. 

If not, how do I clear the database and start from scratch?

Assuming you use the default H2 database, stop GoCD server, delete <GoCD installation dir>/db/h2db/cruise.h2.db file, start server. Look for 'Location of GoCD server files' under your OS if you do not know where is GoCD installed.

[Jeff]

Thanks for the hint.  I did inadvertently change case when doing the search/replace when I created the JSON.  I've reverted the naming but I think that just threw it off even more. 

I've made a copy of the database file, copied all the JAR files from cruise.war and configured SQL Workbench to load the file, but I don't see any GoCD related data.  

I'll keep trying but am open to suggestions before I just delete the database.

--
You received this message because you are subscribed to the Google Groups "go-cd" group.
To unsubscribe from this group and stop receiving emails from it, send an email to go-cd+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jeff]

I finally got the database file to load (had to remove the ".h2.db" suffix when referencing the file in the JDBC string) so I can browse it but am still working through understanding what is going. 

At first glance, it seems there are a lot of places that reference a pipeline by name instead of by ID and many of those don't ignore case yet the NAME column in the PIPELINES table has the VARCHAR_IGNORECASE yet still created new entries of the same pipeline with differing cases.  It's a bit confusing.