Bug 1006374

Summary: RefreshPoolsJob Exception
Product: [Community] Candlepin Reporter: Dennis Crissman <dcrissman>
Component: candlepinAssignee: candlepin-bugs
Status: CLOSED NOTABUG QA Contact: Katello QA List <katello-qa-list>
Severity: high Docs Contact:
Priority: unspecified    
Version: 0.9CC: dcrissman, jesusr
Target Milestone: ---   
Target Release: ---   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2013-09-26 14:29:46 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Attachments:
Description Flags
adds on delete cascade to 2 of the foreign key constraints none

Description Dennis Crissman 2013-09-10 14:08:35 UTC 
Getting this exception in QA. Please investigate.

[2013-09-09T09:21:51-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:] ERROR org.candlepin.pinsetter.tasks.RefreshPoolsJob - RefreshPoolsJob encountered a problem.
[2013-09-09T09:21:51.650941-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:java.lang.RuntimeException] com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`candlepin`.`cp_pool_products`, CONSTRAINT `fk_pool_provided_product` FOREIGN KEY (`pool_id`) REFERENCES `cp_pool` (`id`))
[2013-09-09T09:21:51.650941-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:461)
[2013-09-09T09:21:51.650950-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2541)
[2013-09-09T09:21:51.650950-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:2715)
[2013-09-09T09:21:51.650955-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.hibernate.action.EntityDeleteAction.execute(EntityDeleteAction.java:96)
[2013-09-09T09:21:51.650955-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:278)
[2013-09-09T09:21:51.651241-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:262)
[2013-09-09T09:21:51.651241-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:171)
[2013-09-09T09:21:51.651245-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
[2013-09-09T09:21:51.651245-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.hibernate.event.def.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:64)
[2013-09-09T09:21:51.651249-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:999)
[2013-09-09T09:21:51.651249-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1633)
[2013-09-09T09:21:51.651252-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
[2013-09-09T09:21:51.651252-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.candlepin.model.PoolCurator.entitlementsIn(PoolCurator.java:307)
[2013-09-09T09:21:51.651254-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.google.inject.persist.jpa.JpaLocalTxnInterceptor.invoke(JpaLocalTxnInterceptor.java:58)
[2013-09-09T09:21:51.651254-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.candlepin.controller.CandlepinPoolManager.deletePool(CandlepinPoolManager.java:938)
[2013-09-09T09:21:51.651257-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.google.inject.persist.jpa.JpaLocalTxnInterceptor.invoke(JpaLocalTxnInterceptor.java:58)
[2013-09-09T09:21:51.651257-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.candlepin.controller.CandlepinPoolManager.refreshPoolsWithoutRegeneration(CandlepinPoolManager.java:182)
[2013-09-09T09:21:51.651260-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.candlepin.controller.Refresher.run(Refresher.java:90)
[2013-09-09T09:21:51.651260-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.candlepin.pinsetter.tasks.RefreshPoolsJob.execute(RefreshPoolsJob.java:75)
[2013-09-09T09:21:51.651263-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.google.inject.persist.jpa.JpaLocalTxnInterceptor.invoke(JpaLocalTxnInterceptor.java:66)
[2013-09-09T09:21:51.651263-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.candlepin.pinsetter.core.TransactionalPinsetterJob.execute(TransactionalPinsetterJob.java:50)
[2013-09-09T09:21:51.651266-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.quartz.core.JobRunShell.run(Unknown Source)
[2013-09-09T09:21:51.651266-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(Unknown Source)
[2013-09-09T09:21:51.651268-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    aused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`candlepin`.`cp_pool_products`, CONSTRAINT `fk_pool_provided_product` FOREIGN KEY (`pool_id`) REFERENCES `cp_pool` (`id`))
[2013-09-09T09:21:51.651268-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at sun.reflect.GeneratedConstructorAccessor357.newInstance(Unknown Source)
[2013-09-09T09:21:51.651271-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
[2013-09-09T09:21:51.651390-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
[2013-09-09T09:21:51.651390-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
[2013-09-09T09:21:51.651395-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.mysql.jdbc.Util.getInstance(Util.java:386)
[2013-09-09T09:21:51.651395-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1040)
[2013-09-09T09:21:51.651398-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
[2013-09-09T09:21:51.651398-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
[2013-09-09T09:21:51.651401-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
[2013-09-09T09:21:51.651401-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
[2013-09-09T09:21:51.651404-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
[2013-09-09T09:21:51.651404-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
[2013-09-09T09:21:51.651480-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2450)
[2013-09-09T09:21:51.651480-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2371)
[2013-09-09T09:21:51.651485-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2355)
[2013-09-09T09:21:51.651485-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:447)
[2013-09-09T09:21:51.651488-04:00] [host:s02.candlepin.qa.ext.phx1.redhat.com] [app:]    ... 22 more
Comment 1 Dennis Crissman 2013-09-10 14:14:36 UTC 
May be related to https://bugzilla.redhat.com/show_bug.cgi?id=1006377
Comment 4 Jesus M. Rodriguez 2013-09-20 17:00:45 UTC 
After much investigation it turns out the cause of this exception is that we have duplicate rows in the cp_pool_products table for a given pool_id. And we do not have a DELETE CASCADE on the table so we rely on hibernate to load the dependent children and delete them one by one. This is great unless there are duplicates in the table, they never get loaded or deleted, thus the foreign key constraint violation in the original comment.
Comment 6 Jesus M. Rodriguez 2013-09-20 20:21:59 UTC 
Created attachment 800691 [details]
adds on delete cascade to 2 of the foreign key constraints