Hi Arjen,
From your description, it looks like you didn't restart replication (didn't issue STOP SLAVE/START SLAVE) after changing the binlog_format. If so, what you observe is totally expected. Binlog format obeys the standard global/session variable behavior, changing it globally only affects new connections, not the existing ones. Your slave threads are already running with the statement format, and are not supposed to pick up the change.
For the reference: http://dev.mysql.com/doc/refman/5.5/en/using-system-variables.html ("If you change a global system variable...").
Please comment if you disagree.
Disagree 
Sorry I wasn't being explicitly clear on what I tried: my sentence "Only a mysqld restart (with binlog_format=MIXED) resolved this." implied that other normal avenues had also been attempted.
STOP SLAVE; START SLAVE; did not change the behaviour.
Hence the bug report.
Please repeat the exercise and attach the output, similar to the one below. As you can see, it works for me just as expected, and as it has worked for quite a while (the scenario is used in tests sometimes). Or, maybe I misunderstood the use case, in this case please point at the essential difference.
|
Master |
MariaDB [test]> select @@binlog_format; |
+-----------------+ |
| @@binlog_format |
|
+-----------------+ |
| STATEMENT |
|
+-----------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [test]> create table t1 (i int); |
Query OK, 0 rows affected (1.52 sec) |
|
|
MariaDB [test]> insert into t1 values (1); |
Query OK, 1 row affected (0.14 sec)
|
|
|
MariaDB [test]> set binlog_format=row; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [test]> insert into t1 values (2); |
Query OK, 1 row affected (0.07 sec)
|
|
Master |
MariaDB [test]> show binlog events;
|
+----------------------+-----+-------------------+-----------+-------------+------------------------------------------------------+ |
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
|
+----------------------+-----+-------------------+-----------+-------------+------------------------------------------------------+ |
| wheezy-64-bin.000001 | 4 | Format_desc | 1 | 248 | Server ver: 10.0.11-MariaDB-debug-log, Binlog ver: 4 |
|
| wheezy-64-bin.000001 | 248 | Gtid_list | 1 | 273 | [] |
|
| wheezy-64-bin.000001 | 273 | Binlog_checkpoint | 1 | 316 | wheezy-64-bin.000001 |
|
| wheezy-64-bin.000001 | 316 | Gtid | 1 | 354 | GTID 0-1-1 |
|
| wheezy-64-bin.000001 | 354 | Query | 1 | 440 | use `test`; create table t1 (i int) | |
| wheezy-64-bin.000001 | 440 | Gtid | 1 | 478 | BEGIN GTID 0-1-2 | |
| wheezy-64-bin.000001 | 478 | Query | 1 | 566 | use `test`; insert into t1 values (1) | |
| wheezy-64-bin.000001 | 566 | Xid | 1 | 593 | COMMIT /* xid=15 */ | |
| wheezy-64-bin.000001 | 593 | Gtid | 1 | 631 | BEGIN GTID 0-1-3 | |
| wheezy-64-bin.000001 | 631 | Table_map | 1 | 672 | table_id: 70 (test.t1) |
|
| wheezy-64-bin.000001 | 672 | Write_rows_v1 | 1 | 706 | table_id: 70 flags: STMT_END_F |
|
| wheezy-64-bin.000001 | 706 | Xid | 1 | 733 | COMMIT /* xid=17 */ | |
+----------------------+-----+-------------------+-----------+-------------+------------------------------------------------------+ |
12 rows in set (0.00 sec) |
|
Slave |
MariaDB [test]> change master to master_host='127.0.0.1', master_port=3306, master_user='root'; |
Query OK, 0 rows affected (0.79 sec) |
|
|
MariaDB [test]> select @@binlog_format; |
+-----------------+ |
| @@binlog_format |
|
+-----------------+ |
| STATEMENT |
|
+-----------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [test]> start slave;
|
Query OK, 0 rows affected (0.01 sec) |
|
|
MariaDB [test]> select * from t1; |
+------+ |
| i |
|
+------+ |
| 1 |
|
+------+ |
1 row in set (0.00 sec) |
|
Slave |
MariaDB [test]> show slave status \G
|
...
|
Slave_IO_Running: Yes
|
Slave_SQL_Running: No |
...
|
Last_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.' |
...
|
|
|
MariaDB [test]> set global binlog_format=mixed; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [test]> start slave;
|
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [test]> show slave status \G
|
...
|
Slave_IO_Running: Yes
|
Slave_SQL_Running: Yes
|
...
|
Last_Errno: 0
|
Last_Error:
|
...
|
|
Master |
MariaDB [test]> select @@version; |
+---------------------------+ |
| @@version |
|
+---------------------------+ |
| 10.0.11-MariaDB-debug-log |
|
+---------------------------+ |
1 row in set (0.00 sec) |
|
Slave |
MariaDB [test]> select @@version; |
+---------------------------+ |
| @@version |
|
+---------------------------+ |
| 10.0.11-MariaDB-debug-log |
|
+---------------------------+ |
1 row in set (0.00 sec) |
Enable multi-threaded slave?
My slave server was running with 8 threads.
Thank you, it does make the difference. When the slave is running wih parallel threads (even when slave_parallel_threads=1), these threads don't exit on a replication failure or on STOP SLAVE, so they can't pick up the modified binlog_format.
One possible workaround is to run set global slave_parallel_threads=0, which will make those threads exit immediately. After that you can set it back to the previous value if needed, new threads will be created and they will use the new global value of binlog_format. But I'll pass it to knielsen to confirm that it works as designed, and that it's the best possible workaround. Probably, it's also worth documenting somewhere.
Yes, Elena is correct.
Changing a GLOBAL variable does not affect the value of the corresponding
SESSION variable of running threads.
And with parallel replication, the worker threads keep running until
the slave_parallel_threads value is changed.
(There is a feature request to have all worker threads be destroyed when the
last SQL driver thread is stopped, and re-created when the SQL driver thread
is started again. If this is implemented, I suppose the behaviour will be a
bit better).
Hi Ian,
Could you please add a note about the specifics described above to the documentation, wherever you think it fits best (unless it's already there)?
Excellent, glad we got it repeatable, and good came of it!
Indeed I think it'd be good if the worker threads get destroyed when the SQL thread is shut down - in my mind they came together, but from SHOW PROCESSLIST I know they stay.
This has now been documented at https://mariadb.com/kb/en/mariadb/documentation/log-files/binary-log/binary-log-formats/ with a warning about changing the value at https://mariadb.com/kb/en/mariadb/documentation/replication-cluster-multi-master/replication/replication-and-binary-log-server-system-variables/#binlog_format
Will close, as there's already a feature request for destroying the worker threads.