Issue Summary
Doing a LOAD DATA LOCAL INFILE and shutting down a slave server causes the LOAD DATA LOCAL INFILE to be aborted and the connection to be closed.
Original description
Reset one Node of the Cluster is closing all established connections to other Nodes trough maxctrl
the Node with restarted mysql service is afterwards never reached when client connects
trough MaxScale
I scenario
Client connections was established to server1 and server2 trough MaxScale.
After restarting mysqld on server2 , clients connected to server1 also received
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
and all connections was closed.
II scenario
Start table data load trough MaxScale.
As it's used 'readwritesplit' maxscale router , data loading started on server1 which is
considered Master by maxctrl at the moment
restart mysqld on server3
Data load to server1 was interrupted ,client connection is closed with
Error: 2013, Lost connection to MySQL server during query, when using table: fact_tick
The load become partial and invalid.
It is expected resetting slave Node not to break the operations on master node or any other slave node.
With the given 2nd scenario the restarted slave server is expected to sync with Master by IST .
I scenario
[root@t4w1 ~]# maxctrl list servers
|
┌─────────┬─────────────────┬──────┬─────────────┬─────────────────────────┬──────┐
|
│ Server │ Address │ Port │ Connections │ State │ GTID │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ server1 │ 192.168.104.193 │ 3306 │ 151 │ Slave, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ server2 │ 192.168.104.195 │ 3306 │ 151 │ Slave, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ server3 │ 192.168.104.196 │ 3306 │ 151 │ Master, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ server4 │ 172.20.2.208 │ 3306 │ 0 │ Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ server5 │ 172.20.2.206 │ 3306 │ 0 │ Running │ │
|
└─────────┴─────────────────┴──────┴─────────────┴─────────────────────────┴──────┘
|
|
[root@t4w5 ~]# service mysql restart
|
Restarting mysql (via systemctl): [ OK ]
|
[root@t4w5 ~]#
|
|
[root@t4w1 ~]# maxctrl list servers
|
┌─────────┬─────────────────┬──────┬─────────────┬─────────────────────────┬──────┐
|
│ Server │ Address │ Port │ Connections │ State │ GTID │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ server1 │ 192.168.104.193 │ 3306 │ 0 │ Slave, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ server2 │ 192.168.104.195 │ 3306 │ 0 │ Slave, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ server3 │ 192.168.104.196 │ 3306 │ 0 │ Master, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ server4 │ 172.20.2.208 │ 3306 │ 0 │ Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ server5 │ 172.20.2.206 │ 3306 │ 0 │ Running │ │
|
└─────────┴─────────────────┴──────┴─────────────┴─────────────────────────┴──────┘
|
logs from maxscale
2018-06-15 18:14:29 error : Monitor was unable to connect to server [192.168.104.195]:3306 : "Lost connection to MySQL server at 'handshake: reading initial communication packet', system error: 107"
|
2018-06-15 18:14:29 notice : Server changed state: server2[192.168.104.195:3306]: slave_down. [Slave, Synced, Running] -> [Down]
|
2018-06-15 18:14:33 notice : Server changed state: server2[192.168.104.195:3306]: slave_up. [Down] -> [Slave, Synced, Running]
|
|
|
II scenario
Start data table load from client trough MaxScale
[root@t4w1 ~]# maxctrl list servers
|
┌─────────┬─────────────────┬──────┬─────────────┬─────────────────────────┬──────┐
|
│ Server │ Address │ Port │ Connections │ State │ GTID │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ server1 │ 192.168.104.193 │ 3306 │ 1 │ Master, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ server2 │ 192.168.104.195 │ 3306 │ 1 │ Slave, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ server3 │ 192.168.104.196 │ 3306 │ 1 │ Slave, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ server4 │ 172.20.2.208 │ 3306 │ 0 │ Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ server5 │ 172.20.2.206 │ 3306 │ 0 │ Running │ │
|
└─────────┴─────────────────┴──────┴─────────────┴─────────────────────────┴──────┘
|
[root@t4w1 ~]#
|
|
|
|
Load started on server1 which is considered to be Master at the moment
|
server1
|
MariaDB [(none)]> show processlist ;
|
+----+-------------+-----------------------+---------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+-------------+-----------------------+---------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
|
| 1 | system user | | NULL | Sleep | 1525 | WSREP aborter idle | NULL | 0.000 |
|
| 2 | system user | | NULL | Sleep | 199 | Committed 27502 | NULL | 0.000 |
|
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
|
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
|
| 5 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
|
| 6 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
|
| 7 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
|
| 12 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 |
|
| 13 | root | 192.168.104.191:46590 | NULL | Sleep | 0 | | NULL | 0.000 |
|
| 19 | root | 192.168.104.191:46672 | boxoffice_mdb | Query | 14 | Reading file | LOAD DATA LOCAL INFILE '/home/zdrav1/Pro/MDBEL22/MDBE-level-2/Auto_Pr_Check/insert-data-tables/boxo | 0.000 |
|
+----+-------------+-----------------------+---------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
|
10 rows in set (0.000 sec)
|
|
MariaDB [(none)]>
|
|
restart any slave server -- it was restarted server3
|
|
[root@t4w6 ~]# service mysql restart
|
Restarting mysql (via systemctl): [ OK ]
|
[root@t4w6 ~]#
|
|
Client connection was reset and load failed
|
|
Loading MammothDB Table fact_tickets.csv
|
Mon Jun 18 17:06:57 EEST 2018
|
|
mysqlimport: Error: 2013, Lost connection to MySQL server during query, when using table: fact_tickets
|
|
Logs from MaxScale
|
2018-06-18 17:11:37 error : Monitor was unable to connect to server [192.168.104.196]:3306 : "Lost connection to MySQL server at 'handshake: reading initial communication packet', system error: 107"
|
2018-06-18 17:11:37 notice : Server changed state: server3[192.168.104.196:3306]: slave_down. [Slave, Synced, Running] -> [Down]
|
2018-06-18 17:11:43 notice : Server changed state: server3[192.168.104.196:3306]: slave_up. [Down] -> [Slave, Synced, Running]
|
|
|
|
info.log.tar.gz