MySQL Bugs: #80400: semisync replication - STOP SLAVE SQL_THREAD

Bug #80400	semisync replication - STOP SLAVE SQL_THREAD - prevents master data modification
Submitted:	17 Feb 2016 1:02	Modified:	8 Feb 2018 22:54
Reporter:	Daniel Black	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.6.29, 5.6.31, 5.6.33, 5.7.20	OS:	Fedora (23)
Assigned to:		CPU Architecture:	Any
Tags:	replication, semisync

Description:
Client connections started on a mysql master in a semisync replication immediately after a STOP SLAVE SQL_THREAD is issued on the slave are in a state where they cannot modify data.

Connections a little while later on the master do not suffer any limitation.

Observed: on Fedora 23 x86_64 and also observed on ubuntu-15.10 ppc64le

How to repeat:
scripts as attached

1. ./mysql_server.sh master (+ small wait till start)
2. ./mysql_server.sh slave
3. ./test.sh

4. mysql -S /tmp/oltp_master.sock -u root -pmysql

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.29-log |
+------------+
1 row in set (0.00 sec)

Show processlist (observe transactions aren't blocked due to locks)

| 53 | root | localhost       | oltp1 | Execute     |    0 | updating                                                              | DELETE from oltp1 where id=10016194                                                                  |
| 54 | root | localhost       | oltp1 | Execute     |    0 | updating                                                              | DELETE from oltp1 where id=10047267                                                                  |
| 55 | root | localhost       | oltp1 | Execute     |    0 | update                                                                | INSERT INTO oltp1 values(10689590,0,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')        |
| 56 | root | localhost       | oltp1 | Execute     |    0 | updating                                                              | UPDATE oltp1 set c='31108932-92606255-292751031-47933951-963981232-953892102-997163617-321550449-666 |
| 57 | root | localhost       | oltp1 | Execute     |    0 | updating                                                              | DELETE from oltp1 where id=9974716                                                                   |
| 58 | root | localhost       | oltp1 | Execute     |  102 | update                                                                | INSERT INTO oltp1 values(9961663,0,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')         |

6. show master status;

mysql> show master status;
+-----------------+-----------+--------------+------------------+-------------------+
| File            | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+-----------+--------------+------------------+-------------------+
| mysqlbin.000004 | 114115290 |              |                  |                   |
+-----------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> select sum(id+k),avg(id+k) from oltp1.oltp1;
+---------------+-------------+
| sum(id+k)     | avg(id+k)   |
+---------------+-------------+
| 1748450935000 | 935000.5000 |
+---------------+-------------+
1 row in set (0.87 sec)

(arbitrary mechanism to show data isn't being updated)

select sleep(40);

mysql> show master status; select sum(id+k),avg(id+k) from oltp1.oltp1;
+-----------------+-----------+--------------+------------------+-------------------+
| File            | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+-----------+--------------+------------------+-------------------+
| mysqlbin.000004 | 114115290 |              |                  |                   |
+-----------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

+---------------+-------------+
| sum(id+k)     | avg(id+k)   |
+---------------+-------------+
| 1748450935000 | 935000.5000 |
+---------------+-------------+
1 row in set (0.87 sec)

Observed no change in binlog position or sum/avg. So all sysbench sql threads are failing.

This new connection to the database however is free to change data successfully

mysql> delete from oltp1.oltp1 where id > 500000 limit 3;
Query OK, 3 rows affected, 1 warning (0.04 sec)

mysql> select sum(id+k),avg(id+k) from oltp1.oltp1;
+---------------+-------------+
| sum(id+k)     | avg(id+k)   |
+---------------+-------------+
| 1748449434994 | 935001.1979 |
+---------------+-------------+
1 row in set (0.87 sec)

mysql> show master status;
+-----------------+-----------+--------------+------------------+-------------------+
| File            | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+-----------+--------------+------------------+-------------------+
| mysqlbin.000004 | 114115505 |              |                  |                   |
+-----------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql_server.sh

Attachment: mysql_server.sh (application/x-shellscript, text), 1.89 KiB.

test.sh

Attachment: test.sh (application/x-shellscript, text), 2.18 KiB.

Hello Daniel,

Thank you for the report and test case.
Verified as described with 5.6.31 release build on OL7.

Thanks,
Umesh

Posted by developer:
 
Status was set incorrectly. Setting to VERIFIED.

Not sure why the category was changed to Documentation either. I'm hoping the solution isn't going to be to document "don't do a new connection immediately after doing a stop slave".

mysql_server_5.7.sh

Attachment: mysql_server_5.7.sh (application/x-shellscript, text), 1.88 KiB.

reproducible in 5.7.20 mysql_server_5.7.sh added due to changed initialization of server.

#While performing the data/table replication between two servers (master-slave), #tried importing the slave dump .sql data into a table (dump copied from #master). Now in slave,

#System details:
mysql -V
mysql  Ver 14.14 Distrib 5.5.54, for Linux (x86_64) using readline 5.1

#Command line:
mysql -u -root -p NokiaLTE_Performace_T < NokiaLTE_Performace_T.sql 

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SSH-2.0-OpenSSH_5.3' at line 1

#what is the fix for this error?

Ravi, you appear to be connecting to a openssh port rather than a MySQL port from your client. Its also unclear that your scenario has anything to do with this bug report. I suggest getting help on Freenode IRC server #mysql channel.