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:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0.38 OS:Linux (Gentoo)
Assigned to: Gleb Shchepa
Tags: binlog, qc, replication, VIEW

[4 May 2007 14:50] Baron Schwartz
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*/;
[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.