| 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: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.0 | OS: | |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
[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.
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.