MySQL Bugs: #19736: VIEW: column names not quoted properly when view is replicated

Bug #19736	VIEW: column names not quoted properly when view is replicated
Submitted:	11 May 2006 18:10	Modified:	9 Nov 2006 14:58
Reporter:	Timothy Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.22	OS:	Linux (linux, any)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
A view that is created correctly on the master is created incorrectly on the slave.  The query stored in the .frm file for the view on the slave is not quoted correctly.  It causes any query which tries to access the view on the slave (even just DESC view_name) to return a syntax error.

Maybe this bug has the same root cause as bug #19419 ("View that the column name is different by master and slave is made"), but I chose to open a new bug report in case they have different causes.

How to repeat:
DROP TABLE IF EXISTS test;
CREATE TABLE IF NOT EXISTS test (id serial, data char(1));

DROP VIEW IF EXISTS repl_breaker_v;

CREATE VIEW repl_breaker_v AS
SELECT
id, data
FROM
test t
WHERE NOT EXISTS ( SELECT 1 FROM test t1 );

Run on master. Look at results on slave after replicating.

The query appears correctly in the relay log:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 2198898
#060511 11:25:32 server id 6 end_log_pos 2198863 Query thread_id=701 exec_time=0 error_code=0
use tmp;
SET TIMESTAMP=1147361132;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
SET @@session.sql_mode=0;
SET @@session.auto_increment_increment=30, @@session.auto_increment_offset=6;
/*!\C latin1 */;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8;
DROP VIEW IF EXISTS repl_breaker_v;
# at 2198999
#060511 11:25:32 server id 6 end_log_pos 2199100 Query thread_id=701 exec_time=0 error_code=0
SET TIMESTAMP=1147361132;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `repl_breaker_v` AS SELECT
id, data
FROM
test t
WHERE NOT EXISTS ( SELECT 1 FROM test t1 );
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

But running it on the slave gives a syntax error:

> desc repl_breaker_v \G
ERROR 1064 (42000): 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 '1 from tmp.test t1))' at line 1
$ diff -u {m,s}/data/rep1/repl_breaker_v.frm
--- m/data/rep1/repl_breaker_v.frm      Thu May 11 11:09:30 2006
+++ s/data/rep1/repl_breaker_v.frm      Thu May 11 11:09:30 2006
@@ -1,6 +1,6 @@
 TYPE=VIEW
-query=select `t`.`id` AS `id`,`t`.`data` AS `data` from `rep1`.`test` `t` where not(exists(select 1 AS `1` from `rep1`.`test` `t1`))
-md5=daa58b592b8ce1c450340187a5f6f197
+query=select t.id AS id,t.`data` AS `data` from rep1.test t where not(exists(select 1 AS 1 from rep1.test t1))
+md5=51bd151e1f2a2d134698fc0ed6003cf7
 updatable=0
 algorithm=0
 definer_user=tim

Suggested fix:

Unknown at this time.  It's possible to manually edit the .frm file and quote the offending column name.  For example, with the above test case I changed the .frm file on the slave to be:

TYPE=VIEW
query=select t.id AS id,t.`data` AS `data` from rep1.test t where not(exists(select 1 AS `1` from rep1.test t1))
md5=51bd151e1f2a2d134698fc0ed6003cf7
updatable=0
algorithm=0
definer_user=tim
definer_host=localhost
suid=1
with_check_option=0
revision=1
timestamp=2006-05-11 11:09:30
create-version=1
source=SELECT\nid, data\nFROM\ntest t\nWHERE NOT EXISTS ( SELECT 1 FROM test t1 )

I.e., I added back-quotes around the `1` on the query= line.  Then it worked OK.

I tested this on MySQL 5.1, and it did not have the problem.  The .frm file is still not the same on the master and the slave, but at least the troublesome column name is being quoted:

$ diff -u {m,s}/data/rep1/repl_br*.frm
--- m/data/rep1/repl_breaker_v.frm      Thu May 11 12:55:16 2006
+++ s/data/rep1/repl_breaker_v.frm      Thu May 11 12:55:16 2006
@@ -1,6 +1,6 @@
 TYPE=VIEW
-query=select `t`.`id` AS `id`,`t`.`data` AS `data` from `rep1`.`test` `t` where not(exists(select 1 AS `1` from `rep1`.`test` `t1`))
-md5=daa58b592b8ce1c450340187a5f6f197
+query=select t.id AS id,t.`data` AS `data` from rep1.test t where not(exists(select 1 AS `1` from rep1.test t1))
+md5=505b30b9e46f72e8e79523f6a544d956
 updatable=0
 algorithm=0
 definer_user=tim

mysql> show variables like 'version%';
+-------------------------+---------------------------------------------------+
| Variable_name           | Value                                             |
+-------------------------+---------------------------------------------------+
| version                 | 5.1.10-beta-debug                                 | 
| version_comment         | Latest delta: 2006/05/05 1.2382 jmiller@mysql.com | 
| version_compile_machine | i386                                              | 
| version_compile_os      | unknown-freebsd6.1                                | 
+-------------------------+---------------------------------------------------+

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/14788

ChangeSet@1.2348, 2006-11-03 12:42:55+04:00, ramil@mysql.com +2 -0
  Fix for bug #19736 VIEW: column names not quoted properly when view is replicated
  
  When we write 'query=...' string to a frm file for views on a slave, 
  indentifiers are not properly quoted due to missing OPTION_QUOTE_SHOW_CREATE 
  flag in the thd->options.
  
  Fix: properly set thd->options for the slave thread.

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/14797

ChangeSet@1.2348, 2006-11-03 15:27:37+04:00, ramil@mysql.com +2 -0
  Fix for bug #19736 VIEW: column names not quoted properly when view is replicated
  
  When we write 'query=...' string to a frm file for views on a slave, 
  indentifiers are not properly quoted due to missing OPTION_QUOTE_SHOW_CREATE 
  flag in the thd->options.
  
  Fix: properly set thd->options for the slave thread.

Noted in 5.0.30, 5.1.13 changelogs.