Upgrade Fails: Problem upgrading with script, more than one row returned by a subquery used as an expression, upgrade_57.sql

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

The exact stack trace is:

ERROR - Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dbControlFactory' defined in ServletContext resource [/WEB-INF/applicationContext.xml]: Instantiation of bean failed;
nested exception is org.springframework.beans.BeanInstantiationException: Could not instantiate bean class [com.cenqua.crucible.hibernate.DBControlFactoryImpl]: Constructor threw exception; nested exception is com.cenqua.crucible.hibernate.CruDBException: Problem upgrading with script /Users/pkamal/crucible/fecru-2.1.0/sql/POSTGRESQL/upgrade/upgrade_57.sql: SQL script error on line 9: "UPDATE cru_comment SET cru_reply_to_comment_id =   ( SELECT cru_reply.cru_comment_id     FROM cru_reply     WHERE cru_reply.cru_reply_id = cru_comment.cru_comment_id)   WHERE EXISTS       ( SELECT cru_reply.cru_comment_id         FROM cru_reply         WHERE cru_reply.cru_reply_id= cru_comment.cru_comment_id);"
(ERROR: more than one row returned by a subquery used as an expression), please contact http://www.atlassian.com/support/
	at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:254)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:925)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:835)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:440)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409)
	at java.security.AccessController.doPrivileged(Native Method)
        .....
        .....Caused by: org.springframework.beans.BeanInstantiationException: Could not instantiate bean class [com.cenqua.crucible.hibernate.DBControlFactoryImpl]: Constructor threw exception;
nested exception is com.cenqua.crucible.hibernate.CruDBException: Problem upgrading with script /Users/pkamal/crucible/fecru-2.1.0/sql/POSTGRESQL/upgrade/upgrade_57.sql: SQL script error on line 9: "UPDATE cru_comment SET cru_reply_to_comment_id =   ( SELECT cru_reply.cru_comment_id     FROM cru_reply     WHERE cru_reply.cru_reply_id = cru_comment.cru_comment_id)   WHERE EXISTS       ( SELECT cru_reply.cru_comment_id         FROM cru_reply         WHERE cru_reply.cru_reply_id= cru_comment.cru_comment_id);"
(ERROR: more than one row returned by a subquery used as an expression), please contact http://www.atlassian.com/support/
	at org.springframework.beans.BeanUtils.instantiateClass(BeanUtils.java:115)
	at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:87)
	at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:248)
	... 41 more

Cause

The upgrade script fails as it found a comment that is in reply to two other comments when it should only have one.

Resolution

Upgrading from 2.0.x to 2.1.x

Manually Update the Database to Remove Invalid Data

Run the following queries:

select * from cru_reply where cru_reply_id in (select distinct cru_reply_id from cru_reply group by cru_reply_id having count(cru_reply_id) > 1);

That should return something like:

+----------------+--------------+
| cru_comment_id | cru_reply_id
+----------------+--------------+
|           3532 |         3551 |
|           3547 |         3551 |
+----------------+--------------+

Next, run the following query – it should return the rows from above that do not have the reply relationship set up correctly:

select * from cru_reply r where cru_reply_id in (select distinct cru_reply_id from cru_reply group by cru_reply_id having count(cru_reply_id) > 1)
 AND not exists (select * from cru_comment c where r.cru_comment_id = cru_reply_to_comment_id
 AND r.cru_reply_id = c.cru_comment_id);

An example of the query result:

+----------------+--------------+
| cru_comment_id | cru_reply_id
+----------------+--------------+
|           3532 |         3551 |
+----------------+--------------+
1 row in set (0.49 sec)

Confirm that this returns half of the rows returned from the first query and each of the values of cru_reply_id are unique. If this is the case these rows can be removed from the DB:

delete from cru_reply r where cru_reply_id in (select distinct cru_reply_id from cru_reply group by cru_reply_id having count(cru_reply_id) > 1)
 AND not exists (select * from cru_comment c where r.cru_comment_id = cru_reply_to_comment_id
 AND r.cru_reply_id = c.cru_comment_id);

 

Use a SQL Script to Update the Database to Remove Invalid Data

Create a patch.sql file with the following commands:

select * from cru_reply r where cru_reply_id in (select distinct cru_reply_id from cru_reply group by cru_reply_id having count(cru_reply_id) > 1); 

select * from cru_reply r where cru_reply_id in (select distinct cru_reply_id from cru_reply group by cru_reply_id having count(cru_reply_id) > 1) 
 AND not exists (select * from cru_comment c where r.cru_comment_id = cru_reply_to_comment_id 
 AND r.cru_reply_id = c.cru_comment_id);

Then, run the following command after the Fisheye/Crucible server has been shutdown, replacing %FISHEYE_HOME% and %FISHEYE_INST% with the fully qualified paths for your FISHEYE_HOME and FISHEYE_INST. The examples below are specific to the built-in Fisheye/Crucible database, HSQL DB:

java -Xms512m -Xmx512m -jar %FISHEYE_HOME%/lib/hsqldb-1.8.0.10.jar --inlineRC "URL=jdbc:hsqldb:file:%FISHEYE_INST%/var/data/crudb/crucible;shutdown=true,USER=sa,PASSWORD=" patch.sql

Confirm that the resulting data first identifies duplicate replies and second identifies half the number of rows of the first with each of the rows having a unique value for cru_reply_id.

Then, create patch2.sql file with the following commands (NOTE: the commit; is important):

patch2.sql

delete from cru_reply r where cru_reply_id in (select distinct cru_reply_id from cru_reply group by cru_reply_id having count(cru_reply_id) > 1)
 AND not exists (select * from cru_comment c where r.cru_comment_id = cru_reply_to_comment_id AND r.cru_reply_id = c.cru_comment_id);
commit;

Ensure a successful backup (a DB backup and backup of FISHEYE_INST) has been created prior to continuing.

Lastly, run the second patch file:

java -Xms512m -Xmx512m -jar %FISHEYE_HOME%/lib/hsqldb-1.8.0.10.jar --inlineRC "URL=jdbc:hsqldb:file:%FISHEYE_INST%/var/data/crudb/crucible;shutdown=true,USER=sa,PASSWORD=" patch2.sql

The upgrade should now proceed successfully.

 

When upgrading from 1.6.x to 2.1.x

Use a SQL Script to Update the Database to Remove Invalid Data

Create a patch.sql file with the following commands:

select * from reply r where reply_id in (select distinct reply_id from reply group by reply_id having count(reply_id) > 1);

select * from reply r where reply_id in (select distinct reply_id from reply group by reply_id having count(reply_id) > 1)
 AND not exists (select * from comment c where r.comment_id = reply_to_comment_id
 AND r.reply_id = c.comment_id);

Then, run the following command after the Fisheye/Crucible server has been shutdown, replacing %FISHEYE_HOME% and %FISHEYE_INST% with the fully qualified paths for your FISHEYE_HOME and FISHEYE_INST. The examples below are specific to the built-in Fisheye/Crucible database, HSQL DB:

java -Xms512m -Xmx512m -jar %FISHEYE_HOME%/lib/hsqldb-1.8.0.10.jar --inlineRC "URL=jdbc:hsqldb:file:%FISHEYE_INST%/var/data/crudb/crucible;shutdown=true,USER=sa,PASSWORD=" patch.sql

Confirm that the resulting data first identifies duplicate replies and second identifies half the number of rows of the first with each of the rows having a unique value for reply_id.

Then, create patch2.sql file with the following commands (NOTE: the commit; is important):

delete from reply r where reply_id in (select distinct reply_id from reply group by reply_id having count(reply_id) > 1)
 AND not exists (select * from comment c where r.comment_id = reply_to_comment_id
 AND r.reply_id = c.comment_id);
commit;

Ensure a successful backup (a DB backup and backup of FISHEYE_INST) has been created prior to continuing.

 

Lastly, run the second patch file:

java -Xms512m -Xmx512m -jar %FISHEYE_HOME%/lib/hsqldb-1.8.0.10.jar --inlineRC "URL=jdbc:hsqldb:file:%FISHEYE_INST%/var/data/crudb/crucible;shutdown=true,USER=sa,PASSWORD=" patch2.sql

The upgrade should now proceed successfully.

 

Last modified on Jul 31, 2018

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.