MySQL Bugs: #50927: Delete server fails on foreign key error to rule

Bug #50927	Delete server fails on foreign key error to rule_schedules table
Submitted:	4 Feb 2010 19:29	Modified:	1 Mar 2010 13:38
Reporter:	Mark Leith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Server	Severity:	S3 (Non-critical)
Version:	2.1.0.1096	OS:	Any
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
If you do *something which I do not know*, it is possible that when you try to delete a server in the UI, you can end up with the following:

2010-02-04 16:03:39,744  INFO [delete-server-server-01:com.mysql.inventory] Deleting inventory {mysql}.{StatementAnalysisSupport}.{a59af9a1-95ba-4b72-8ef6-b43354974722}
2010-02-04 16:03:40,052  INFO [delete-server-server-01:com.mysql.inventory] Deleting inventory {mysql}.{Server}.{a59af9a1-95ba-4b72-8ef6-b43354974722}
2010-02-04 16:03:40,612  WARN [delete-server-server-01:org.hibernate.util.JDBCExceptionReporter] SQL Error: 1451, SQLState: 23000
2010-02-04 16:03:40,612 ERROR [delete-server-server-01:org.hibernate.util.JDBCExceptionReporter] Cannot delete or update a parent row: a foreign key constraint fails (`mem`.`rule_schedules`, CONSTRAINT `FK317EC739E9BA6AFF` FOREIGN KEY (`instance_id`) REFERENCES `inventory_instances` (`instance_id`))
2010-02-04 16:03:40,620 ERROR [delete-server-server-01:com.mysql.infra.com.mysql.merlin.server.ReportingRunnable] running command com.mysql.etools.monitor.bo.Manager$2
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
	at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:172)
	at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
	at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
	at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
	at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
	at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
	at com.mysql.etools.monitor.pom.hib.HibernateExecutor.asTransaction(HibernateExecutor.java:190)
	at com.mysql.etools.monitor.pom.hib.SchemaMaintenanceExecutor.asTransaction(SchemaMaintenanceExecutor.java:194)
	at com.mysql.etools.monitor.pom.hib.HibPersistence.asTransaction(HibPersistence.java:504)
	at com.mysql.etools.monitor.pom.hib.HibPersistence.remove(HibPersistence.java:450)
	at com.mysql.etools.monitor.pom.hib.HibInventoryObject.delete(HibInventoryObject.java:669)
	at com.mysql.etools.monitor.pom.InvObj.delete(InvObj.java:94)
	at com.mysql.etools.monitor.pom.InvObj.deleteHierarchy(InvObj.java:102)
	at com.mysql.etools.monitor.bo.Server.deleteInventory(Server.java:927)
	at com.mysql.etools.monitor.bo.Manager$2.run(Manager.java:564)
	at com.mysql.merlin.server.ReportingRunnable.run(ReportingRunnable.java:65)
	at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`mem`.`rule_schedules`, CONSTRAINT `FK317EC739E9BA6AFF` FOREIGN KEY (`instance_id`) REFERENCES `inventory_instances` (`instance_id`))
	at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2016)
	at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1452)
	at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
	at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
	at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
	... 18 more
2010-02-04 16:03:40,628 ERROR [delete-server-server-01:com.mysql.infra.com.mysql.merlin.server.ReportingRunnable] Thu Feb 04 16:03:40 CET 2010 [ FAILED ] Duration: 00:00:02.498 com.mysql.etools.monitor.bo.Manager$2@422f932d

How to repeat:
Focus your computer towards sun spot activity, whilst chanting unintelligibly to the Sun God Ra.

Suggested fix:
Delete with extreme prejudice. :)

Mark Matthews writes: 
The fix ensures that rule_schedules is unhooked from the to-be-deleted server, even if there are schedules unknown to rule manager (left over from migration, purge by hand?).

Keith Russell writes: 
Patch installed in versions => 2.1.2.1149.

Marcos Palacios writes: 
Tested with Monitor build 2.1.2.1149. Tried several ways to get the error to happen, using the steps below. I do not see the error.

Steps before trying to delete the server:
1- You have and agent and service manager working fine
2- Send lots of quan data
3- Stop the agent
4- Delete the mysql instance from the UI
5- Right away start the agent again
6- Look at the dashboard log and wait until it is not deleting quan data any more.
7- Stop the agent
8- There will be no mysql instance on the UI
9- Restart tomcat
10- The mysql instance is "back" (Even though the agent is not pinging in)

A note has been added to the 2.1.2 changelog: 

 The pagination of errors within &merlin_client; for &merlin_server; logging made it difficult to identify the true source of the error.