Bug 2229095

Summary:	CertificateCleanupJob fails with foreign key constraint violation on table cp_certificate
Product:	Red Hat Satellite	Reporter:	Alexey Masolov <amasolov>
Component:	Candlepin	Assignee:	Barnaby Court <bcourt>
Status:	NEW ---	QA Contact:	Satellite QE Team <sat-qe-bz-list>
Severity:	urgent	Docs Contact:
Priority:	unspecified
Version:	6.11.5	CC:	alsouza, hyu, jpasqual, nmoumoul, rlavi, saydas, wpoteat
Target Milestone:	Unspecified	Keywords:	Triaged, WorkAround
Target Release:	Unused	Flags:	hyu: needinfo? (nmoumoul)
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:
Fixed In Version:		Doc Type:	If docs needed, set a value
Doc Text:		Story Points:	---
Clone Of:
Clones:	2231503 (view as bug list)		Environment:
Last Closed:		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:
Bug Depends On:
Bug Blocks:	2231503

Description Alexey Masolov 2023-08-04 06:16:55 UTC

Description of problem:

On a very loaded system "CertificateCleanupJob" job shows following error:

2023-08-03 17:45:22,829 [thread=Thread-3 (ActiveMQ-client-global-threads)] [job=8a35b3cf89b9b7aa0189bb44de043b1b, job_key=CertificateCleanupJob, org=, csid=] WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 23503
2023-08-03 17:45:22,829 [thread=Thread-3 (ActiveMQ-client-global-threads)] [job=8a35b3cf89b9b7aa0189bb44de043b1b, job_key=CertificateCleanupJob, org=, csid=] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: update or delete on table "cp_cert_serial" violates foreign key constraint "fk16370c54b9410fc" on table "cp_certificate"
  Detail: Key (id)=(1298353109733755480) is still referenced from table "cp_certificate".
2023-08-03 17:45:22,840 [thread=Thread-3 (ActiveMQ-client-global-threads)] [job=8a35b3cf89b9b7aa0189bb44de043b1b, job_key=CertificateCleanupJob, org=, csid=] ERROR org.candlepin.async.JobManager - Job "CertificateCleanupJob" failed in 20722815ms

Version-Release number of selected component (if applicable):
Satellite 6.11

Comment 4 William Poteat 2023-08-04 14:17:41 UTC

Can I get a database dump for this?

Comment 15 William Poteat 2023-08-15 13:53:01 UTC

Then certificates that are not yet expired but have been revoked can be used to retrieve content.

What specific performance is currently affected? Deleting rows with no concrete justification is not advisable.

If there are rows that the job is not removing, they are there for a reason.