Bug #20014 CREATE VIEW w/ 'version comments' break in replication

• Submitted: 22 May 2006 23:32
• Modified: 19 Jul 2006 11:15
• Reporter: Douglas Fischer
• Status: Duplicate
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.22
• OS: Linux (Linux)
• CPU Architecture: Any

[22 May 2006 23:32] Douglas Fischer

Description:
Because of the manner in which CREATE VIEW commands are written into the binary log, any such commands that begin with a 'version comment' will have the long comment start token removed (however, the long comment end token will remain). As a result, the command will fail the parsing phase on the slave, resulting in a syntax error.

How to repeat:
Create a view with the command wrapped in /*!50001 ... */. This is especially easily accomplished by creating a view, executing a 'mysqldump' on that view (which wraps all versionable commands in version comments), and then sourcing that output. The command will successfully run on the master, but result in a Syntax Error on the slave.

Suggested fix:
The SQLCOM_CREATE_VIEW case in mysql_execute_command() (sql_parse.cc line 4691) is manually re-creating the first part of the query. This re-creation is not accounting for all possible correct create statements - in particular by its nature it is dropping any version comment start tokens. 

Most of the commands perform their replication by simply passing the entire query into the binlog. It appears that the only reason this is done with view creation is to deal with the case where first_table->db != thd-db (?). Perhaps there is a cleaner way to address this than to manually re-write the query?

[23 May 2006 16:42] Douglas Fischer

Another side-effect of the manner in which CREATE VIEW queries are added to the binlog is a column list that is specified as part of the view creation (i.e. CREATE VIEW `foo` (`bar`, `foobar`) AS ... ) will not be replicated. As a result, either the master and slave will have different column names for the view, or in some cases the slave will produce an error on a non-distinct column name, failing to replicate the view. Column names that are specified as aliases in the query are unaffected by this.

[8 Jun 2006 17:43] Valeriy Kravchuk

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.22, and inform about the results. Your second comment looks like a duplicate of a know bug #19736, by the way.

[22 Jun 2006 22:38] Douglas Fischer

Same result (which is not surprising since I don't see anything changed from 5.0.20a to 5.0.22 in the source diffs that would have any impact on this).

Also, the 2nd listed issue is identical to bug #19419.

I have not managed to understand the need/rationale for rewriting the CREATE VIEW statement piecemeal into the binary log instead of simply adding it directly as it was provided (which appears to be the normal manner of placing a query into the binary log). The below patch (which clearly does not address whatever issue requires this rewriting of the query) has worked perfectly for us for the past month, addressing both issues raised in this bug report.

diff -PurN mysql-5.0.21.orig/sql/sql_parse.cc mysql-5.0.21/sql/sql_parse.cc
--- mysql-5.0.21.orig/sql/sql_parse.cc  2006-04-26 14:30:42.000000000 -0400
+++ mysql-5.0.21/sql/sql_parse.cc   2006-05-24 12:08:32.000000000 -0400
@@ -4696,30 +4696,9 @@
       if (!(res= mysql_create_view(thd, thd->lex->create_view_mode)) &&
           mysql_bin_log.is_open())
       {
-        String buff;
-        const LEX_STRING command[3]=
-          {{(char *)STRING_WITH_LEN("CREATE ")},
-           {(char *)STRING_WITH_LEN("ALTER ")},
-           {(char *)STRING_WITH_LEN("CREATE OR REPLACE ")}};
         thd->clear_error();

-        buff.append(command[thd->lex->create_view_mode].str,
-                    command[thd->lex->create_view_mode].length);
-        view_store_options(thd, first_table, &buff);
-        buff.append(STRING_WITH_LEN("VIEW "));
-        /* Test if user supplied a db (ie: we did not use thd->db) */
-        if (first_table->db != thd->db && first_table->db[0])
-        {
-          append_identifier(thd, &buff, first_table->db,
-                            first_table->db_length);
-          buff.append('.');
-        }
-        append_identifier(thd, &buff, first_table->table_name,
-                          first_table->table_name_length);
-        buff.append(STRING_WITH_LEN(" AS "));
-        buff.append(first_table->source.str, first_table->source.length);
-
-        Query_log_event qinfo(thd, buff.ptr(), buff.length(), 0, FALSE);
+        Query_log_event qinfo(thd, thd->query, thd->query_length, FALSE, FALSE);
         mysql_bin_log.write(&qinfo);
       }
       break;

[19 Jul 2006 11:15] Valeriy Kravchuk

Duplicate of bug #20438, already verified.