Description:
CREATE VIEW statements are sometimes improperly stored in binlog. This occured with lengthy view definitions. MySQL Replication will break due to a wrong query in binlog.
This bug occurred with 5.0.67. 5.0.32 and 5.1.26 are not affected by this bug.
How to repeat:
mysql> CREATE DATABASE test_view;
Query OK, 1 row affected (0.00 sec)
mysql> GRANT ALL ON test_view.* TO test_view IDENTIFIED BY 'test_view';
Query OK, 0 rows affected (0.00 sec)
user@laptop:~$ perl -e 'print "CREATE ALGORITHM=UNDEFINED DEFINER=`test_view`@`%` SQL SECURITY DEFINER VIEW `test_view`.`test_view` AS select 1 AS A1"; for (2..450) { print ", 1 AS A$_" } print "\n"' | mysql test_view
mysql> SHOW CREATE VIEW test_view.test_view\G
*************************** 1. row ***************************
View: test_view
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`test_view`@`%` SQL SECURITY DEFINER VIEW `test_view`.`test_view` AS select 1 AS `A1`,1 AS `A2`,1 AS `A3`,1 AS `A4`,1 AS `A5`,1 AS `A6`,1 AS `A7`,1 AS `A8`,1 AS `A9`,1 AS `A10`,1 AS `A11`,1 AS `A12`,1 AS `A13`,1 AS `A14`,1 AS `A15`,1 AS `A16`,1 AS `A17`,1 AS `A18`,1 AS `A19`,1 AS `A20`...
The query in the binlog :
mysql@laptop:/usr/local/mysql/data$ /usr/local/mysql/bin/mysqlbinlog binlog.000005
...
# at 313
#081125 22:02:00 server id 1 end_log_pos 5771 Query thread_id=2 exec_time=0 error_code=0
use test_view/*!*/;
SET TIMESTAMP=1227646920/*!*/;
CREATE ALGORITHM=UNDEFINED DEFINER=`test_view`@`%` SQL SECURITY DEFINER VIEW `test_view` AS CREATE ALGORITHM=UNDEFINED DEFINER=`test_view`@`%` SQL SECURITY DEFINER VIEW `test_view` AS CREATE ALGORITHM=UNDEFINED DEFINER=`test_view`@`%` SQL SECURITY DEFINER VIEW `test_view` AS CREATE ALGORITHM=UNDEFINED DEFINER=`test_view`@`%` SQL SECURITY DEFINER VIEW `test_view` AS ...
Description: CREATE VIEW statements are sometimes improperly stored in binlog. This occured with lengthy view definitions. MySQL Replication will break due to a wrong query in binlog. This bug occurred with 5.0.67. 5.0.32 and 5.1.26 are not affected by this bug. How to repeat: mysql> CREATE DATABASE test_view; Query OK, 1 row affected (0.00 sec) mysql> GRANT ALL ON test_view.* TO test_view IDENTIFIED BY 'test_view'; Query OK, 0 rows affected (0.00 sec) user@laptop:~$ perl -e 'print "CREATE ALGORITHM=UNDEFINED DEFINER=`test_view`@`%` SQL SECURITY DEFINER VIEW `test_view`.`test_view` AS select 1 AS A1"; for (2..450) { print ", 1 AS A$_" } print "\n"' | mysql test_view mysql> SHOW CREATE VIEW test_view.test_view\G *************************** 1. row *************************** View: test_view Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`test_view`@`%` SQL SECURITY DEFINER VIEW `test_view`.`test_view` AS select 1 AS `A1`,1 AS `A2`,1 AS `A3`,1 AS `A4`,1 AS `A5`,1 AS `A6`,1 AS `A7`,1 AS `A8`,1 AS `A9`,1 AS `A10`,1 AS `A11`,1 AS `A12`,1 AS `A13`,1 AS `A14`,1 AS `A15`,1 AS `A16`,1 AS `A17`,1 AS `A18`,1 AS `A19`,1 AS `A20`... The query in the binlog : mysql@laptop:/usr/local/mysql/data$ /usr/local/mysql/bin/mysqlbinlog binlog.000005 ... # at 313 #081125 22:02:00 server id 1 end_log_pos 5771 Query thread_id=2 exec_time=0 error_code=0 use test_view/*!*/; SET TIMESTAMP=1227646920/*!*/; CREATE ALGORITHM=UNDEFINED DEFINER=`test_view`@`%` SQL SECURITY DEFINER VIEW `test_view` AS CREATE ALGORITHM=UNDEFINED DEFINER=`test_view`@`%` SQL SECURITY DEFINER VIEW `test_view` AS CREATE ALGORITHM=UNDEFINED DEFINER=`test_view`@`%` SQL SECURITY DEFINER VIEW `test_view` AS CREATE ALGORITHM=UNDEFINED DEFINER=`test_view`@`%` SQL SECURITY DEFINER VIEW `test_view` AS ...