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

[18 Jul 2011 15:30] Ricardo Cescon
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
[19 Jul 2011 20:58] Shane Bester
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
[20 Jul 2011 7:49] Ricardo Cescon
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!
[9 Aug 2011 19:50] Sveta Smirnova
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
[9 Sep 2011 23:00] Bugs System
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".
[10 Oct 2011 23:00] Bugs System
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".
[11 Oct 2011 8:42] Ricardo Cescon
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
[29 Nov 2011 16:15] Sveta Smirnova
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.
[30 Dec 2011 7:00] Bugs System
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".