| Bug #12129 | mysqldump on the master breaks replication and creates "interesting" dump | ||
|---|---|---|---|
| Submitted: | 23 Jul 2005 10:41 | Modified: | 25 Oct 2005 10:44 |
| Reporter: | Victoria Reznichenko | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.0.11-beta-debug-log | OS: | Any (any) |
| Assigned to: | Bugs System | CPU Architecture: | Any |
[23 Jul 2005 10:46]
MySQL Verification Team
just changed synopsis a bit.
[27 Aug 2005 19:09]
Patrick Galbraith
There are some other bugs logged about mysqldump not handling (such as 10322) that list mysqldump not being able to handle views properly, and I need to see if this bug has any dependency or relation
[14 Oct 2005 23:52]
Patrick Galbraith
I have a fix for BUG# 12838 that I want to test against this.
[19 Oct 2005 5:41]
Patrick Galbraith
Patch for BUG# 12838 fixes this: patg@krsna:~/mysql-build/mysql-5.0.test1/client> ./mysqldump --master-data=2 -u root -S /tmp/mysqld-5.0-master.sock test > test.sql mysqldump: mysqldump: Couldn't execute 'CREATE TEMPORARY TABLE `v0` SELECT * FROM `v0` WHERE 0': Can't execute the query because you have a conflicting read lock (1223) With patch for BUG# 12838 patg@krsna:~/mysql-build/mysql-5.0.test1/client> ./mysqldump --master-data=2 -u root -S /tmp/mysqld-5.0-master.sock test > test.sql (no errors)
[25 Oct 2005 10:44]
Sergei Golubchik
duplicate of BUG#12838
Description: If schema contains VIEWs mysqldump breaks replication on the slave and dumps VIEW as: DROP TABLE IF EXISTS `v1`; DROP VIEW IF EXISTS `v1`; CREATE TABLE `v1` ( `id` int(11) default NULL, `str` varchar(10) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- View structure for view `v1` -- DROP TABLE IF EXISTS `v1`; DROP VIEW IF EXISTS `v1`; CREATE ALGORITHM=UNDEFINED VIEW `test`.`v1` AS select `test`.`t1`.`id` AS `id`,`test`.`t1`.`str` AS `str` from `test`.`t1`; Here is the output of SHOW SLAVE STATUS: mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: repl Master_Port: 3503 Connect_Retry: 60 Master_Log_File: toyou-bin.000001 Read_Master_Log_Pos: 1146 Relay_Log_File: toyou-relay-bin.000002 Relay_Log_Pos: 1078 Relay_Master_Log_File: toyou-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: mysql.% Last_Errno: 1146 Last_Error: Error 'Table 'test.v1' doesn't exist' on query. Default database: 'test'. Query: 'CREATE TEMPORARY TABLE `v1` SELECT * FROM `v1` WHERE 0' Skip_Counter: 0 Exec_Master_Log_Pos: 941 Relay_Log_Space: 1283 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL 1 row in set (0.00 sec) How to repeat: 1. Set up replication (I have only replicate-wild-ignore-table=mysql.% option) 2. Create table and view: create table t1(id int, str varchar(10)); create view v1 as select * from t1; 3. dump database: mysqldump --master-data=2 test > /tmp/repltest.sql Check dumpfile and SHOW SLAVE STATUS.