MySQL Bugs: #38283: mysqldump dependency failures for stored procedures and triggers

Bug #38283	mysqldump dependency failures for stored procedures and triggers
Submitted:	22 Jul 2008 14:01	Modified:	30 Aug 2008 3:56
Reporter:	Kevin Benton (Candidate Quality Contributor)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	5.1.24	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
When dumping from a MySQL host, we observed create procedure calls being defined *after* create trigger calls, causing problems reference issues when triggers call those stored procedures.  We did not see an immediate way to get around this except to hand-edit the SQL code to put the sprocs before the triggers.

How to repeat:
mysqldump output:

...

/*!50003 CREATE*/ /*!50003 TRIGGER `trig__update_mv_user_feedback_count_1` AFTER INSERT ON `feedback`

FOR EACH ROW

BEGIN

  CALL proc__update_mv_user_feedback_count(NEW.id_user);

END */;;
DELIMITER ;

...

DELIMITER ;;
/*!50003 CREATE*/ /*!50003 PROCEDURE `proc__update_mv_user_feedback_count`(IN p_id_user int)
BEGIN

  DECLARE var_per_feedback_count int;

	SELECT

	(IFNULL(ROUND((

	(SELECT COUNT(DISTINCT P.id_promo) FROM promo P

	INNER JOIN distribution D ON D.id_promo = P.id_promo

	INNER JOIN distribution_user DU ON DU.id_distribution = D.id_distribution

	INNER JOIN feedback F ON F.id_promo = P.id_promo

	WHERE DU.id_user = U.id_user AND F.id_user = U.id_user)

	/

	(SELECT COUNT(DISTINCT P.id_promo) FROM promo P

	INNER JOIN distribution D ON D.id_promo = P.id_promo

	INNER JOIN distribution_user DU ON DU.id_distribution = D.id_distribution

	WHERE DU.id_user = U.id_user)

	)*100), 0)) INTO var_per_feedback_count

	FROM `user` U

  WHERE U.id_user = p_id_user;

  UPDATE mv_user_feedback_count

  SET per_feedback_count = var_per_feedback_count

  WHERE id_user = p_id_user;

END */;;
DELIMITER ;

...

Suggested fix:
My hope would be that mysqldump would not rely on SET FOREIGN_KEY_CHECKS=0 and the like, rather, it would discover dependencies first, then provide output so that the most dependent tables, data, functions, procedures and triggers would be handled before items that were less or not dependent.  I would also hope that triggers would *always* be output last in a mysqldump session because I want to make sure that triggers don't run again on the data because those triggers have already run.

Please be aware that STRICT sql_mode was on during these events.

Thank you for a problem report. What exact mysqldump command line options you had used?

Valeriy, I'm waiting for the person who did that dump to get back to me with those command-line options.  I've asked that person to update this bug personally.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".