MySQL Bugs: #43975: Slave crashes if RBR has as destination a view rather than a table

Bug #43975	Slave crashes if RBR has as destination a view rather than a table
Submitted:	31 Mar 2009 8:07	Modified:	30 Jan 2013 14:40
Reporter:	Henrik Ingo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S1 (Critical)
Version:	5.1.33, mysql-5.1.32-ndb-6.4.3-innodb-linux-i686	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any

Description:
When using RBR a slave crashes if there is an insert to an object that is a view, but that was a table on the master. This (quite naturally) works for stmt based replication.

How to repeat:
using MyISAM table on master (thus stmt based repl) I can do this:

mysql> show create table t_1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| t_1   | CREATE TABLE `t_1` (
  `a` int(11) NOT NULL,
  `b` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t_2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| t_2   | CREATE TABLE `t_2` (
  `a` int(11) NOT NULL,
  `b` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t_1 values (2, 'abc');
Query OK, 1 row affected (0.00 sec)

mysql>

And on the slave I have *views* t_1 and t_2 from the table t:

mysql> show create table t;
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t_1;
+------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View

| character_set_client | collation_connection |
+------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| t_1  | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `t_1` AS select `t`.`a` AS `a`,`t`.`b` AS `b` from
`t` | utf8                 | utf8_general_ci      |
+------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> show create table t_2;
+------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View

| character_set_client | collation_connection |
+------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| t_2  | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `t_2` AS select `t`.`a` AS `a`,`t`.`b` AS `b` from
`t` | utf8                 | utf8_general_ci      |
+------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql>

The insert statement replicates its data through view t_1 into the table
t, as you might expect.

Now if the tables on master are ndb tables (thus row based repl), the
slave crashes with signal 11.

Suggested fix:
Slave should not crash. At least it should handle this appropriately and report an error.

Preferably this should work with RBR as it does with stmt-repl. I have a use case where this would be very convenient. (It is related to the fact that you cannot drop-create partitions in NDB.)

Henrik,

we need to know if it is only by using MCCGE or if it is also by using MySQL 6.0 (or 5.1).

Susanne, I don't wish to sound unpolite or anything, but I kind of thought you are able to test different versions better than myself. If you are going to wait for me to test this, there is a risk it will take until after the UC before you get an answer.

I happened to do this with NDB, but if I had to guess I would bet my money on that it is a row based replication thing and less dependent on the originating engine.

(Also about the severity, this is not a critical issue for me or any of my customers, I just assumed a crash is automatically critical.)

Not cluster specific.  Verified as described on 5.1.33 with myisam and RBR.

mysqld.exe!mysql_make_view()[sql_view.cc:1409]
mysqld.exe!open_new_frm()[sql_base.cc:8610]
mysqld.exe!open_unireg_entry()[sql_base.cc:3889]
mysqld.exe!open_table()[sql_base.cc:2934]
mysqld.exe!open_tables()[sql_base.cc:4586]
mysqld.exe!open_and_lock_tables_derived()[sql_base.cc:4989]
mysqld.exe!simple_open_n_lock_tables()[mysql_priv.h:1532]
mysqld.exe!Rows_log_event::do_apply_event()[log_event.cc:7147]
mysqld.exe!Log_event::apply_event()[log_event.h:1059]
mysqld.exe!apply_event_and_update_pos()[slave.cc:1987]
mysqld.exe!exec_relay_log_event()[slave.cc:2130]
mysqld.exe!handle_slave_sql()[slave.cc:2801]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:295]
mysqld.exe!_threadstart()[thread.c:277]
kernel32.dll!BaseThreadStart()

on the master:
-------------
drop table if exists `t_1`;
CREATE TABLE `t_1` (`a` int(11) NOT NULL,`b` varchar(25) DEFAULT NULL,PRIMARY KEY (`a`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
reset master;

on the slave:
-------------
drop table if exists `t`;
CREATE TABLE `t` (`a` int(11) NOT NULL,`b` varchar(25) DEFAULT NULL,PRIMARY KEY (`a`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
create or replace view `t_1` as select * from `t`;

now setup replication from scratch:
------------------------------------
reset slave;
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3306, MASTER_USER='root', MASTER_PASSWORD='';
start slave;

on the master:
--------------
insert into t_1 values (2, 'abc');

Fixed in 5.1+. Documented in the 5.1.69, 5.5.31, 5.6.11, and 5.7.1 changelogs as follows: 

        It was possible to replicate from a table to a same-named view using
        statement-based logging, while using row-based logging instead led
        to a failure on the slave. Now the target object type is checked prior
        to performing any DML, and an error is given if the target on the slave
        is not actually a table. This is true regardless of the binary logging
        format in use.

Closed.