Bug #15152 downgrade, load(MySQL 4.1) a dump(made in 5.0) fails because of triggers
Submitted: 22 Nov 2005 21:23 Modified: 2 May 2006 22:11
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0 OS:
Assigned to: Paul Dubois CPU Architecture:Any
Triage: D4 (Minor)

[22 Nov 2005 21:23] Matthias Leich
Description:
Downgrading from MySQL 5.0 to 4.1 via
1. MySQL 5.0: ... /mysqldump --user=root --all-databases \
                     --add-drop-table > outfile
2. MySQL 5.0: stop server
3. Delete all database files.
4. MySQL 4.1: start server
5. MySQL 5.0: ... /mysql --user=root < outfile
6. MySQL 4.1: fix privileges, because the dump contains privileges/system
                      tables in MySQL 5.0 style
7. MySQL 4.1: flush privileges
will fail in step 5., if the dump of the databases contains triggers.

Example:
    Output of mysqldump:
    ERROR at line 1460: DELIMITER must be followed by a 'delimiter' 
               character or string
    ERROR 1064 (42000) at line 1464: You have an error in your SQL 
               syntax; check the manual that corresponds to your MySQL 
               server version for the right syntax to use near 'END */' at line 1

    Content of the dump:
line 1459  /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;
line 1460  DELIMITER ;;
line 1461  /*!50003 SET SESSION SQL_MODE="" */;;
line 1462  /*!50003 CREATE TRIGGER `t1_bi` BEFORE INSERT ON `t1_idx_1` 
               FOR EACH ROW BEGIN
line 1463  INSERT INTO test.t_aux SET f1 = 1;
line 1464  END */;;
line 1465
line 1466  DELIMITER ;
line 1467  /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL Network GPL version 
     binary releases (tar archives for glibc6)
         4.1.15  and 5.0.15

How to repeat:
Please do the following
1. clone the   /home/bk/mysql-test-extra-5.0   tree
    This tree contains the Upgrade/Downgrade testsuite.
2. remove the test      suite/updown/t/y32.test
    This test removes the problematic triggers
    created in test   suite/updown/t/y29.test .
3. follow the instructions for the Upgrade/Downgrade testsuite

If the test y32 is omitted
- the triggers will be not dropped
- loading the dump  (Shellscript updown.sh line 311) will fail
       This is the interesting operation.
- several following testcase (z07, z09, z10, z11, ....) will also
  fail, because of the errors during loading the dump
  Please ignore these errors  or drop the tests
       suite/updown/t/z*.test

Suggested fix:
There are several alternatives:
1. Implement a option for mysqldump (5.0) which omits dumping
    of problematic objects (trigger, stored procedures, maybe views).
2. Modify mysqldump (4.1) so that it ignores such problematic lines.
3. Alter the manual
    2.11.1. Downgrading to 4.1
        ....
    If the preceding procedure fails, then you should be able to do the following
    instead:
   1. Start MySQL 5.0.4 (or newer).
   2. Remove all existing triggers      <=====
   3. Run mysqldump --opt --add-drop-table mysql > /tmp/mysql.dump.
   4. Stop the MySQL server.
   5. Start MySQL 4.1 with the --skip-grant option.
   6. Run mysql mysql < /tmp/mysql.dump.
   7. Run mysqladmin flush-privileges.

I would take alternative 3, because downgrades are most probably rare.
[1 May 2006 18:07] Paul Dubois
Wouldn't it be easier to use --skip-triggers?
[2 May 2006 7:39] Matthias Leich
Yes, of course. I cannot remember if this option was already
available in November 2005.
[2 May 2006 22:11] Paul Dubois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

I've updated the downgrade instructions in the 5.0 manual
to point out the --skip-routines and --skip-triggers options
to prevent dumping of stored routines and triggers.

For views, the procedure's more problematic because there
is no --skip-views option (perhaps there should be). For this
case, the instructions point out that you should remove all
views before using mysqldump.
[2 May 2006 22:19] Paul Dubois
A similar issue occurs for events when downgrading from
5.1 to 5.0, so I will add a note about using --skip-events
to the downgrading section in the 5.1 manual.