MySQL Bugs: #61898: Error 1535 after on online schema change with switch to 'statement' repl.on runt

Bug #61898	Error 1535 after on online schema change with switch to 'statement' repl.on runt
Submitted:	18 Jul 2011 15:30	Modified:	29 Dec 2011 16:15
Reporter:	Ricardo Cescon	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.57	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	1535, mixed, online schema, replication, row, statement

Description:
Changing binlog format on runtime from 'mixed' to 'statement' has no effect!

If you use this way to make "online schema changes" on a Master-Master replication, you get the replication broken.
Only change my.cnf and restart both nodes before the schema change - work for us.

How to repeat:
see http://forums.mysql.com/read.php?26,427035,427035#msg-427035

Can this scenario be fixed or explained by anything written here?
http://dev.mysql.com/doc/refman/5.1/en/replication-features-differing-tables.html

Hello Shane,

the MySQL documentation makes my really mad!

The variable "slave_type_conversions" is described in your link (http://dev.mysql.com/doc/refman/5.1/en/replication-features-differing-tables.html) for MySQL 5.1

but this variable was added in MySQL 5.5.3. 
see: http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html

I tried also with MySQL 5.1.57, after set this variable to my.cnf I can't start the MySQL server not anymore!

Thank you for the feedback.

From your forum post is not actually clear what you are doing. Please run queries like in example follow and send us result:

mysql> flush logs;
Query OK, 0 rows affected (0.58 sec)

mysql> show master status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| mysqld511-bin.005067 |      106 |              |                  |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> set binlog_format='row';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.14 sec)

mysql> show binlog events in 'mysqld511-bin.005067';
+----------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| Log_name             | Pos | Event_type  | Server_id | End_log_pos | Info                                        |
+----------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| mysqld511-bin.005067 |   4 | Format_desc |       511 |         106 | Server ver: 5.1.59-debug-log, Binlog ver: 4 |
| mysqld511-bin.005067 | 106 | Query       |       511 |         174 | BEGIN                                       |
| mysqld511-bin.005067 | 174 | Table_map   |       511 |         215 | table_id: 17 (test.t1)                      |
| mysqld511-bin.005067 | 215 | Write_rows  |       511 |         249 | table_id: 17 flags: STMT_END_F              |
| mysqld511-bin.005067 | 249 | Xid         |       511 |         276 | COMMIT /* xid=16 */                         |
+----------------------+-----+-------------+-----------+-------------+---------------------------------------------+
5 rows in set (0.00 sec)

-- this showed row-based format is used

mysql> set binlog_format='statement';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.07 sec)

mysql> show binlog events in 'mysqld511-bin.005067';
+----------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| Log_name             | Pos | Event_type  | Server_id | End_log_pos | Info                                        |
+----------------------+-----+-------------+-----------+-------------+---------------------------------------------+
| mysqld511-bin.005067 |   4 | Format_desc |       511 |         106 | Server ver: 5.1.59-debug-log, Binlog ver: 4 |
| mysqld511-bin.005067 | 106 | Query       |       511 |         174 | BEGIN                                       |
| mysqld511-bin.005067 | 174 | Table_map   |       511 |         215 | table_id: 17 (test.t1)                      |
| mysqld511-bin.005067 | 215 | Write_rows  |       511 |         249 | table_id: 17 flags: STMT_END_F              |
| mysqld511-bin.005067 | 249 | Xid         |       511 |         276 | COMMIT /* xid=16 */                         |
| mysqld511-bin.005067 | 276 | Query       |       511 |         344 | BEGIN                                       |
| mysqld511-bin.005067 | 344 | Query       |       511 |         431 | use `test`; insert into t1 values(1)        |
| mysqld511-bin.005067 | 431 | Xid         |       511 |         458 | COMMIT /* xid=21 */                         |
+----------------------+-----+-------------+-----------+-------------+---------------------------------------------+
8 rows in set (0.00 sec)

-this showed we nicely switched to statement-based format

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Switch binlog on runtime not work for me! It not switch without changing it in my.cnf and restart, see also http://forums.mysql.com/read.php?26,427035,427035#msg-427035

Ricardo,

thank you for the feedback.

But I still need repeatable test case similar to one which I provided in my earlier example. Just copy-paste queries you run in MySQL command line client together with result which MySQL server sends to you and indicate which results do you expect to get. Make sure you change binary log format using user with SUPER privilege.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".