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

[3 May 2007 16:58] Tatiana Azundris Nuernberg
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.
[3 May 2007 18:42] Miguel Solorzano
Thank you for the bug report.
[14 May 2007 6:53] Bugs System
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.
[14 May 2007 7:02] Bugs System
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
[14 May 2007 12:55] Tatiana Azundris Nuernberg
pushed to 5.0.42-maint, 5.1.18-maint as discussed with tim
[16 May 2007 8:14] Bugs System
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.
[22 May 2007 17:00] Bugs System
Pushed into 5.1.19-beta
[22 May 2007 17:03] Bugs System
Pushed into 5.0.44
[8 Jun 2007 21:52] Peter Lavin
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.
[17 Jul 2007 7:59] Sveta Smirnova
There is new similar bug #29788 which is repatable with current BK sources.