| Bug #28244 | CREATE VIEW breaks replication when view exists | ||
|---|---|---|---|
| Submitted: | 4 May 2007 14:50 | Modified: | 6 Jun 2007 15:11 |
| Reporter: | Baron Schwartz (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.0.38 | OS: | Linux (Gentoo) |
| Assigned to: | Gleb Shchepa | CPU Architecture: | Any |
| Tags: | binlog, qc, replication, VIEW | ||
[4 May 2007 18:50]
Sveta Smirnova
Thank you for the report. Verified as described. All versions are affected.
[29 May 2007 9:32]
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/27543 ChangeSet@1.2506, 2007-05-29 14:47:58+05:00, gshchepa@gleb.loc +3 -0 Fixed bug #28244. When the same VIEW was created at the master side twice, malformed (truncated after the word 'AS') query string as forwarded to client side, so error messages on the master and client was different, and replication was broken. The mysql_register_view() was failed too early: fields of `view' output argument of this function call was not filled yet with correct data. Checking of existence of VIEW .frm file has been moved down to the place, when `view' argument is completely filled with proper data.
[29 May 2007 9:41]
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/27546 ChangeSet@1.2506, 2007-05-29 14:57:00+05:00, gshchepa@gleb.loc +3 -0 Fixed bug #28244. When the same VIEW was created at the master side twice, malformed (truncated after the word 'AS') query string was forwarded to client side, so error messages on the master and client was different, and replication was broken. The mysql_register_view() functioin call was failed too early: fields of `view' output argument of this function was not filled yet with correct data required for query replication. Checking of existence of VIEW .frm file has been moved down to the place, when `view' argument is completely filled with proper data.
[31 May 2007 12: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/27808 ChangeSet@1.2506, 2007-05-31 17:30:56+05:00, gshchepa@gleb.loc +3 -0 Fixed bug #28244. When the same VIEW was created at the master side twice, malformed (truncated after the word 'AS') query string was forwarded to client side, so error messages on the master and client was different, and replication was broken. The mysql_register_view function call failed too early: fields of `view' output argument of this function was not filled yet with correct data required for query replication. The mysql_register_view function also copied pointers to local buffers into a memory allocated by the caller.
[31 May 2007 21:07]
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/27849 ChangeSet@1.2515, 2007-06-01 02:25:11+05:00, gshchepa@gleb.loc +1 -0 sql_view.cc: Additional patch for bug #28244 to workaround valgrind warnings.
[4 Jun 2007 21:21]
Bugs System
Pushed into 5.1.20-beta
[4 Jun 2007 21:23]
Bugs System
Pushed into 5.0.44
[6 Jun 2007 15:11]
MC Brown
A note has been added to the 5.0.44 and 5.1.20 changelog.
Description: Running CREATE VIEW on the master when the view already exists breaks replication. The statement is logged to the binlog and gets to the slave, where it fails with a syntax error. Apologies for reporting this against 5.0.38, but I searched and it doesn't look like it's been reported against any newer version. To my coworker's consternation, I can reproduce this again and again :-) How to repeat: /* On the master */ mysql> use rktemp; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A mysql> select version(); +------------+ | version() | +------------+ | 5.0.38-log | +------------+ 1 row in set (0.00 sec) mysql> create table test(a int); Query OK, 0 rows affected (0.56 sec) mysql> show tables like '%test%'; +---------------------------+ | Tables_in_rktemp (%test%) | +---------------------------+ | test | +---------------------------+ 1 row in set (0.00 sec) mysql> create view vw_test as select * from test; Query OK, 0 rows affected (0.01 sec) mysql> create view vw_test as select * from test; ERROR 1050 (42S01): Table 'vw_test' already exists /* On the slave */ mysql> show tables like '%test%'; +---------------------------+ | Tables_in_rktemp (%test%) | +---------------------------+ | test | | vw_test | +---------------------------+ 1 row in set (0.00 sec) mysql> show create view vw_test\G *************************** 1. row *************************** View: vw_test Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`developer`@`%` SQL SECURITY DEFINER VIEW `vw_test` AS select `test`.`a` AS `a` from `test` 1 row in set (0.01 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 5.0.38 | +-----------+ 1 row in set (0.00 sec) /* In the mysqld.err log on the slave */ 070504 10:23:46 [ERROR] Slave: Query caused different errors on master and slave. Error on master: 'Table '%-.64s' already exists' (1050), Error on slave: '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 '' at line 1' (1064). Default database: 'rktemp'. Query: 'CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY INVOKER VIEW `vw_test` AS ', Error_code: 0 070504 10:23:46 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'usa-bin.000241' position 388883697 /* In the binlog on the master */ mysql> show binlog events in 'usa-bin.000241' from 388883697 limit 1\G *************************** 1. row *************************** Log_name: usa-bin.000241 Pos: 388883697 Event_type: Query Server_id: 11 End_log_pos: 388883842 Info: use `rktemp`; CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY INVOKER VIEW `vw_test` AS 1 row in set (0.28 sec) /* The entire binlog event on the master */ baron@usa ~ $ mysqlbinlog --start-position=388883697 --stop-position=388883842 /var/lib/mysql/usa-bin.000241 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 388883697 #070504 10:23:46 server id 11 end_log_pos 388883842 Query thread_id=28546exec_time=0 error_code=1050 use rktemp/*!*/; SET TIMESTAMP=1178288626/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY INVOKER VIEW `vw_test` AS /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /* The relay-log event on the slave */ baron@fresno /var/lib/mysql $ mysqlbinlog --start-position=355760382 --stop-position=355760527 fresno-relay-bin.000042 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 355760382 #070504 10:23:46 server id 11 end_log_pos 388883842 Query thread_id=28546exec_time=0 error_code=1050 use rktemp/*!*/; SET TIMESTAMP=1178288626/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY INVOKER VIEW `vw_test` AS /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;