MySQL Bugs: #28223: mysqldump --compact --routines restores from @OLD_SQL

Bug #28223	mysqldump --compact --routines restores from @OLD_SQL_MODE w/o ever setting it
Submitted:	3 May 2007 16:58	Modified:	8 Jun 2007 21:52
Reporter:	Tatiana Azundris Nuernberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	5.0/5.1	OS:	Any
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any

Description:
mysqldump --compact --routines restores from @OLD_SQL_MODE w/o ever setting it

How to repeat:
CREATE PROCEDURE p1() SELECT 'HELLO';

$ mysqldump --compact --routines test

DELIMITER ;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `p1`()
SELECT 'HELLO' */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
DELIMITER ;

Suggested fix:
Achieve symmetry by not restoring the undefined OLD_SQL_MODE, or perhaps preferably, by setting it properly.

Thank you for the bug report.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26564

ChangeSet@1.2478, 2007-05-14 08:53:40+02:00, tnurnberg@blasphemy.mysql.com +2 -0
  Bug#28223: mysqldump --compact --routines restores from @OLD_SQL_MODE w/o ever setting it
  
  mysqldump generated output that set OLD_SQL_MODE twice, to different values
  (for triggers), or not at all (for routines) in some cases.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26569

ChangeSet@1.2486, 2007-05-14 09:02:40+02:00, tnurnberg@blasphemy.mysql.com +2 -0
  Bug#28223: mysqldump --compact --routines restores from @OLD_SQL_MODE w/o ever setting it
  
  mysqldump generated output that set OLD_SQL_MODE twice, to different values
  (for triggers), or not at all (for routines) in some cases.
  ---
  Merge blasphemy.mysql.com:/home/tnurnberg/28223/50-28223
  into  blasphemy.mysql.com:/home/tnurnberg/28223/51-28223

pushed to 5.0.42-maint, 5.1.18-maint as discussed with tim

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26779

ChangeSet@1.2479, 2007-05-16 10:14:29+02:00, msvensson@pilot.blaudden +2 -0
  Bug#28223: mysqldump --compact --routines restores from @OLD_SQL_MODE w/o ever setting it
    - mysqldump generated output that set OLD_SQL_MODE twice, to different values
      (for triggers), or not at all (for routines) in some cases.

Pushed into 5.1.19-beta

Pushed into 5.0.44

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented in  5.1.19-beta and 5.0.44 changelogs.

There is new similar bug #29788 which is repatable with current BK sources.