Bug #78963 super_read_only aborts STOP SLAVE if relay_log_info_repository=TABLE, dbg crash
Submitted: 26 Oct 2015 12:13 Modified: 22 Apr 2016 14:25
Reporter: Laurynas Biveinis (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.7.9, 5.7.10 OS:Any
Assigned to: CPU Architecture:Any
Tags: replication, super_read_only

[26 Oct 2015 12:13] Laurynas Biveinis
Description:
If relay_log_info_repository=TABLE, STOP SLAVE results in

STOP SLAVE' failed: 1290: The MySQL server is running with the --super-read-only option so it cannot execute this statement

If it's FILE, then STOP SLAVE statement succeeds.

How to repeat:
MTR taken from WebScaleSQL/Facebook patch (rpl.rpl_ignore_super_read_only in their trees):

foo-slave.opt:

--relay_log_info_repository='TABLE'

foo.test:

source include/master-slave.inc;

connection master;
create table t1(a int);
insert into t1 values(1);
sync_slave_with_master;

set @@global.super_read_only=1;
STOP SLAVE;
change master to master_connect_retry=20;
START SLAVE;

connection master;
insert into t1 values(2);
sync_slave_with_master;

select * from t1;
set @@global.super_read_only=0;
set @@global.read_only=0;

connection master;
drop table t1;

source include/rpl_end.inc;

Suggested fix:
Replication should allow updating slave info tables with super_read_only=ON
[26 Oct 2015 12:14] Laurynas Biveinis
The MTR is for release build. A debug build will hit an assert (will report separately).
[26 Oct 2015 12:20] Laurynas Biveinis
Debug build hits assert as follows:

mysqltest: At line 18: query 'STOP SLAVE' failed: 2013: Lost connection to MySQL server during query
...
015-10-26T12:18:22.195968Z 6 [Note] Error reading relay log event for channel '': slave SQL thread was killed
2015-10-26T12:18:22.197708Z 5 [Note] Slave I/O thread killed while reading event for channel ''
2015-10-26T12:18:22.197742Z 5 [Note] Slave I/O thread exiting for channel '', read up to log 'master-bin.000001', position 588
Assertion failed: (! is_set()), function set_ok_status, file /Users/laurynas/percona/mysql-5.7.9/sql/sql_error.cc, line 380.
12:18:22 UTC - mysqld got signal 6 ;
...
0   mysqld-debug                        0x0000000110d50268 my_print_stacktrace + 72
1   mysqld-debug                        0x0000000110bfcd32 handle_fatal_signal + 930
2   libsystem_platform.dylib            0x00007fff96e0152a _sigtramp + 26
3   ???                                 0x00007fff68fa2568 0x0 + 140734954612072
4   libsystem_c.dylib                   0x00007fff999ea37b abort + 129
5   libsystem_c.dylib                   0x00007fff999b19c4 basename + 0
6   mysqld-debug                        0x00000001109a3be2 _ZN16Diagnostics_area13set_ok_statusEyyPKc + 130
7   mysqld-debug                        0x0000000110986272 _Z5my_okP3THDyyPKc + 66
8   mysqld-debug                        0x0000000110cb64b9 _Z10stop_slaveP3THD + 809
9   mysqld-debug                        0x0000000110cb6ccd _Z14stop_slave_cmdP3THD + 189
10  mysqld-debug                        0x0000000110a26b31 _Z21mysql_execute_commandP3THDb + 13521
11  mysqld-debug                        0x0000000110a2213a _Z11mysql_parseP3THDP12Parser_state + 1690
12  mysqld-debug                        0x0000000110a1f0bb _Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command + 4091
13  mysqld-debug                        0x0000000110a214ff _Z10do_commandP3THD + 1311
14  mysqld-debug                        0x0000000110bba40f handle_connection + 495
15  mysqld-debug                        0x0000000111351e7b pfs_spawn_thread + 411
16  libsystem_pthread.dylib             0x00007fff996879b1 _pthread_body + 131
17  libsystem_pthread.dylib             0x00007fff9968792e _pthread_body + 0
18  libsystem_pthread.dylib             0x00007fff99685385 thread_start + 13
[26 Oct 2015 12:31] Umesh Shastry
Hello Laurynas,

Thank you for the report and test case.

Thanks,
Umesh
[26 Oct 2015 12:32] Umesh Shastry
// 5.7.10 release build

[umshastr@hod04]/export/umesh/server/mysql-advanced-5.7.10/mysql-test: ./mtr foo
Logging: ./mtr  foo
MySQL Version 5.7.10
Checking supported features...
 - SSL connections supported
Collecting tests...
Removing old var directory...
Creating var directory '/export/umesh/server/mysql-advanced-5.7.10/mysql-test/var'...
Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
include/master-slave.inc
Warnings:
Note    ####    Sending passwords in plain text without SSL/TLS is extremely insecure.
Note    ####    Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[connection master]
create table t1(a int);
insert into t1 values(1);
set @@global.super_read_only=1;
main.foo                                 [ fail ]
        Test ended at 2015-10-26 13:27:56

CURRENT_TEST: main.foo
mysqltest: At line 9: query 'STOP SLAVE' failed: 1290: The MySQL server is running with the --super-read-only option so it cannot execute this statement
safe_process[10623]: Child process: 10624, exit: 1

 - the logfile can be found in '/export/umesh/server/mysql-advanced-5.7.10/mysql-test/var/log/main.foo/foo.log'
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.000 of 3 seconds executing testcases

Completed: Failed 1/1 tests, 0.00% were successful.

Failing test(s): main.foo

The log files in var/log may give you some hint of what went wrong.

If you want to report this error, please read first the documentation
at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html
[26 Oct 2015 12:34] Umesh Shastry
// with 5.7.10 debug build

#0  0x00007f6db5cd1771 in pthread_kill () from /lib64/libpthread.so.0
#0  0x00007f6db5cd1771 in pthread_kill () from /lib64/libpthread.so.0
#1  0x00000000017b4a45 in my_write_core (sig=6) at /export/home/pb2/build/sb_0-16866284-1445828361.09/mysqlcom-pro-5.7.10/mysys/stacktrace.c:247
#2  0x0000000000da4e68 in handle_fatal_signal (sig=6) at /export/home/pb2/build/sb_0-16866284-1445828361.09/mysqlcom-pro-5.7.10/sql/signal_handler.cc:220
#3  <signal handler called>
#4  0x00007f6db48d75d7 in raise () from /lib64/libc.so.6
#5  0x00007f6db48d8cc8 in abort () from /lib64/libc.so.6
#6  0x00007f6db48d0546 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007f6db48d05f2 in __assert_fail () from /lib64/libc.so.6
#8  0x000000000143007e in Diagnostics_area::set_ok_status (this=0x7f6d50003720, affected_rows=0, last_insert_id=0, message_text=0x0) at /export/home/pb2/build/sb_0-16866284-1445828361.09/mysqlcom-pro-5.7.10/sql/sql_error.cc:380
#9  0x0000000000db94cb in my_ok (thd=0x7f6d50000b70, affected_rows=0, id=0, message=0x0) at /export/home/pb2/build/sb_0-16866284-1445828361.09/mysqlcom-pro-5.7.10/sql/sql_class.h:4506
#10 0x0000000001747d5c in stop_slave (thd=0x7f6d50000b70) at /export/home/pb2/build/sb_0-16866284-1445828361.09/mysqlcom-pro-5.7.10/sql/rpl_slave.cc:642
#11 0x00000000017481a5 in stop_slave_cmd (thd=0x7f6d50000b70) at /export/home/pb2/build/sb_0-16866284-1445828361.09/mysqlcom-pro-5.7.10/sql/rpl_slave.cc:770
#12 0x000000000148107d in mysql_execute_command (thd=0x7f6d50000b70, first_level=true) at /export/home/pb2/build/sb_0-16866284-1445828361.09/mysqlcom-pro-5.7.10/sql/sql_parse.cc:3344
#13 0x0000000001487a59 in mysql_parse (thd=0x7f6d50000b70, parser_state=0x7f6d9bf86690) at /export/home/pb2/build/sb_0-16866284-1445828361.09/mysqlcom-pro-5.7.10/sql/sql_parse.cc:5508
#14 0x000000000147c5ed in dispatch_command (thd=0x7f6d50000b70, com_data=0x7f6d9bf86e00, command=COM_QUERY) at /export/home/pb2/build/sb_0-16866284-1445828361.09/mysqlcom-pro-5.7.10/sql/sql_parse.cc:1425
#15 0x000000000147b4a8 in do_command (thd=0x7f6d50000b70) at /export/home/pb2/build/sb_0-16866284-1445828361.09/mysqlcom-pro-5.7.10/sql/sql_parse.cc:993
#16 0x00000000015b0805 in handle_connection (arg=0x4e4e1b0) at /export/home/pb2/build/sb_0-16866284-1445828361.09/mysqlcom-pro-5.7.10/sql/conn_handler/connection_handler_per_thread.cc:301
#17 0x0000000001bc012d in pfs_spawn_thread (arg=0x4f4cf20) at /export/home/pb2/build/sb_0-16866284-1445828361.09/mysqlcom-pro-5.7.10/storage/perfschema/pfs.cc:2192
#18 0x00007f6db5cccdf5 in start_thread () from /lib64/libpthread.so.0
#19 0x00007f6db499860d in clone () from /lib64/libc.so.6
[26 Oct 2015 12:38] Umesh Shastry
// with relay_log_info_repository='FILE'

[umshastr@hod04]/export/umesh/server/mysql-advanced-5.7.10/mysql-test: ./mtr foo
Logging: ./mtr  foo
MySQL Version 5.7.10
Checking supported features...
 - SSL connections supported
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/export/umesh/server/mysql-advanced-5.7.10/mysql-test/var'...
Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
include/master-slave.inc
Warnings:
Note    ####    Sending passwords in plain text without SSL/TLS is extremely insecure.
Note    ####    Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[connection master]
create table t1(a int);
insert into t1 values(1);
set @@global.super_read_only=1;
STOP SLAVE;
change master to master_connect_retry=20;
START SLAVE;
insert into t1 values(2);
select * from t1;
a
1
2
set @@global.super_read_only=0;
set @@global.read_only=0;
drop table t1;
include/rpl_end.inc
main.foo                                 [ pass ]    265
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.265 of 14 seconds executing testcases

Completed: All 1 tests were successful.
[26 Oct 2015 12:40] Umesh Shastry
// Not checked <= 5.7.8, super_read_only is available >=5.7.8
[22 Apr 2016 14:25] David Moss
Thanks for your feedback, this has been fixed and the following was added to the 5.7.12 changelog:

When a server was running with relay_log_info_repository=TABLE and the --super-read-only option enabled, a statement which caused an update to the slave info tables, such as STOP SLAVE or CHANGE MASTER TO, resulted in a 'STOP SLAVE' failed: 1290: The MySQL server is running error, preventing the statement being executed. The fix ensures that replication operations are permitted regardless of the setting of read_only and super_read_only.
[4 May 2016 5:50] Umesh Shastry
Bug #81297 marked as duplicate of this