| Bug #77073 | Killed ALTER TABLE on master, table still gets altered on master not on slave | ||
|---|---|---|---|
| Submitted: | 18 May 2015 15:00 | Modified: | 15 Jun 2015 7:29 |
| Reporter: | Jericho Rivera | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.5, 5.5.45 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[18 May 2015 15:09]
Jericho Rivera
Just adding that I used Yoshinori's tran_insert.pl script to populate the table.
[20 May 2015 13:23]
Pura Vida
The result is the same without this step: "On the slave, kill the ALTER TABLE query after 10 minutes".
[20 May 2015 13:33]
Jericho Rivera
@Pura, apologies on that. I meant to say "On the *master*, kill the ALTER TABLE query after 10 minutes", and not on the slave
[26 May 2015 13:23]
MySQL Verification Team
master [localhost] {msandbox} (test) > insert into t2 select * from t1;
Query OK, 8388608 rows affected (10 min 4.16 sec)
Records: 8388608 Duplicates: 0 Warnings: 0
master [localhost] {msandbox} (test) > show processlist;
+----+----------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 1 | rsandbox | localhost:44685 | NULL | Binlog Dump | 4570 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 2 | msandbox | localhost | test | Sleep | 1819 | | NULL |
| 3 | msandbox | localhost | test | Query | 0 | NULL | show processlist |
+----+----------+-----------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.07 sec)
master [localhost] {msandbox} (test) > alter table test.t2 add primary key (id);
ERROR 2013 (HY000): Lost connection to MySQL server during query
master [localhost] {msandbox} (test) > show create table t2;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: test
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`id` bigint(20) DEFAULT NULL,
`aa` char(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.19 sec)
master [localhost] {msandbox} (test) >
slave1 [localhost] {msandbox} (test) > show create table t2;
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`id` bigint(20) DEFAULT NULL,
`aa` char(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
slave1 [localhost] {msandbox} (test) >
[26 May 2015 13:26]
MySQL Verification Team
I can't reproduce this using 5.1.73 5.5.43 5.6.24 5.7.7 Tried both using CTRL+C on the active client and using kill threadid from second connection, on each version, twice, waiting ~10 minutes before killing. Never reproduced the problem. When you are killing the alter thread - what is the state thread is in? all best Bogdan Kecman
[26 May 2015 13:30]
Jericho Rivera
Here's the thread state once alter thread is killed on the master: mysql> select now(); show processlist; +---------------------+ | now() | +---------------------+ | 2015-05-14 04:06:20 | +---------------------+ 1 row in set (0.00 sec) +----+------+-----------+------+-------------+------+-----------------------------------------------------------------------+------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+-------------+------+-----------------------------------------------------------------------+------------------------------------------+ | 38 | repl | u2:47680 | NULL | Binlog Dump | 2041 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 40 | root | localhost | NULL | Killed | 597 | manage keys | alter table test.t1 add primary key (id) | | 41 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+------+-----------+------+-------------+------+-----------------------------------------------------------------------+------------------------------------------+ 3 rows in set (0.00 sec)
[3 Jun 2015 19:53]
Pura Vida
This bug affected me. It happened to me twice: first it happened to production accidently, and I reproduced it on staging on first try. My version is: 5.5.40-36.1-log. Thanks.
[15 Jun 2015 7:29]
MySQL Verification Team
Thank you for the feedback. Observed this during my tests and confirmed that 5.5.45 is affected. Not seen this issue 5.6.26 with few attempts.
[15 Jun 2015 7:29]
MySQL Verification Team
test results
Attachment: 77073_5_5_45.results (application/octet-stream, text), 16.27 KiB.
Description: Killing an ALTER TABLE ... ADD PRIMARY KEY query will not prevent the table from being 'altered', Master table (InnoDB) is altered but Slave table is not. No filtering involved on my.cnf. Alter table event is not written on binary log of master. How to repeat: On master, created table t1 and populated with 50M rows: mysql> show create table test.t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL, `value` int(11) DEFAULT NULL, `value2` varchar(10000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> select now(); alter table test.t1 add primary key (id); +---------------------+ | now() | +---------------------+ | 2015-05-14 03:56:23 | +---------------------+ 1 row in set (0.00 sec) Wait for about 8-10 minutes before killing the alter query On the slave, kill the ALTER TABLE query after 10 minutes mysql> show processlist; kill 40; select now(); +----+------+-----------+------+-------------+------+-----------------------------------------------------------------------+------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+-------------+------+-----------------------------------------------------------------------+------------------------------------------+ | 38 | repl | u2:47680 | NULL | Binlog Dump | 2030 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 40 | root | localhost | NULL | Query | 586 | manage keys | alter table test.t1 add primary key (id) | | 41 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+------+-----------+------+-------------+------+-----------------------------------------------------------------------+------------------------------------------+ 3 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) +---------------------+ | now() | +---------------------+ | 2015-05-14 04:06:09 | +---------------------+ 1 row in set (0.00 sec) On master, check table structure mysql> show create table test.t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL, `value` int(11) DEFAULT NULL, `value2` varchar(10000) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (37.85 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000694 | 107 | +------------------+-----------+ 1 row in set (0.00 sec) root@u1:~# mysqlbinlog /var/log/mysql/mysql-bin.000694 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #150514 3:33:01 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.43-0ubuntu0.14.04.1-log created 150514 3:33:01 # Warning: this binlog is either in use or was not closed properly. BINLOG ' bRdUVQ8BAAAAZwAAAGsAAAABAAQANS41LjQzLTB1YnVudHUwLjE0LjA0LjEtbG9nAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== '/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; There were no changes on the slave table mysql> show create table test.t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL, `value` int(11) DEFAULT NULL, `value2` varchar(10000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) Innodb options: innodb_buffer_pool_size = 1G innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_file_per_table = 1 Not repeatable on MySQL 5.6 at least tested on latest release version Suggested fix: No idea, but have it work the same way it does in MySQL 5.6 where once alter is killed the table will not be modified.