MySQL Bugs: #35365: set statement in init_slave not executed if slave is restarted

Bug #35365	set statement in init_slave not executed if slave is restarted
Submitted:	18 Mar 2008 6:23	Modified:	19 Mar 2008 10:22
Reporter:	Rizwan Maredia	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1.22	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	init_slave

Description:
SET statement in init_slave not execute if slave is restarted. The documentation says that:

This variable is similar to init_connect, but is a string to be executed by a slave server each time the SQL thread starts. The format of the string is the same as for the init_connect variable.

Setting global system variables in init_slave works but temporary session variables do not.

How to repeat:
This code can be tested using mysql testing framework.

Sample Code:
 
source include/master-slave.inc;

save_master_pos;
connection slave;
SET @@global.init_slave = "SET @a=5";
sync_with_master;

stop slave;
--wait_for_slave_to_stop
reset slave;
start slave;

SHOW VARIABLES LIKE 'init_slave';
SELECT @a;

Output:

stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
SET @@global.init_slave = "SET @a=5";
stop slave;
reset slave;
start slave;
SHOW VARIABLES LIKE 'init_slave';
Variable_name	Value
init_slave	SET @a=5
SELECT @a;
@a
NULL

Suggested fix:
Setting temporary session variables in init_slave should be called on slave restart. If init_slave can only be used to set system variables then this exception should be documented.

This is expected behavior. User variables are local to the client, and the slave's replication sql thread is just another client to the slave. So if you set a user variable during initialization of the slave's replication sql thread, it is not visible to any other client on the slave.

If you want communication between the initialization code of the slave's replication sql thread and other clients on the slave, you can do it by updating a table.

I tried inserting a row in a table but its is also not working.

****************************************
source include/master-slave.inc;
CREATE TABLE t1(a int);
save_master_pos;
connection slave;
SET @@global.init_slave = "INSERT INTO t1 VALUES(1);SET GLOBAL max_connections=222";
sync_with_master;
stop slave;
--wait_for_slave_to_stop
reset slave;
--sleep 1
start slave;
--sleep 1
SHOW VARIABLES LIKE 'init_slave';
SELECT * FROM t1;
SHOW VARIABLES LIKE 'max_connections';
***************************************

The outcome would be no rows in table and max_connection set to default 500.

There are couple of other issues with init_slave. If in the above example I set max_connections before insert statement then max_connection is updated, but still insert does not work.

Also inserting in a nonexistent table does not give error. For example insert into t12345 and unexpectedly the test case passes.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

With query `SET @a=5` you can only set session variable. Slave thread is executing in separate connection, so test is not valid. Set some global variable and check it with SELECT @@global.XXX to be sure query in init_slave is executed.