pt-online-schema-change的bug

    mysql5.6和mysql5.7對online DDL做了大幅度功能增強，但是仍然存在主庫執行DDL，從庫存在大幅延遲的情況，故目前生產環境還是通過pt-online-schema-change工具來實現online DDL。但是pt-online-schema-change的使用是否就沒有限制呢？

    先看看官方文檔對pt-online-schema-change的工作原理的描述：

    pt-online-schema-change works by creating an empty copy of the table to alter, modifying it as desired, and then
copying rows from the original table into the new table. When the copy is complete, it moves away the original table
and replaces it with the new one. By default, it also drops the original table.
    The data copy process is performed in small chunks of data, which are varied to attempt to make them execute in
a specific amount of time (see --chunk-time). This process is very similar to how other tools, such as pt-tablechecksum,
work. Any modifications to data in the original tables during the copy will be reflected in the new table,
because the tool creates triggers on the original table to update the corresponding rows in the new table. The use of
triggers means that the tool will not work if any triggers are already defined on the table.
    When the tool finishes copying data into the new table, it uses an atomic RENAME TABLE operation

    接下來通過實驗的方式看看pt-online-schema-change是如何工作的，記得打開mysql的general log。通過查看general日志驗證pt-online-schema-change的工作機理。

    shell>pt-online-schema-change -u linzj -h 192.168.110.131 -p linzj --alter='add column vid3 int' --execute D=sbtest,t=sbtest

    1 創建一個和你要執行 alter 操作的表一樣的空表結構：

                       11 Query     CREATE TABLE `sbtest`.`_sbtest_new` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `vid` int(11) DEFAULT NULL,
  `vid2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8

     2、執行表結構修改

170407 15:45:46    11 Query     ALTER TABLE `sbtest`.`_sbtest_new` add column vid3 int

     3、在原表上創建觸發器，如果表中已經定義了觸發器這個工具就不能工作了。

                   11 Query     CREATE TRIGGER `pt_osc_sbtest_sbtest_del` AFTER DELETE ON `sbtest`.`sbtest` FOR EACH ROW DELETE IGNORE FROM `sbtest
`.`_sbtest_new` WHERE `sbtest`.`_sbtest_new`.`id` <=> OLD.`id`
                   11 Query     CREATE TRIGGER `pt_osc_sbtest_sbtest_upd` AFTER UPDATE ON `sbtest`.`sbtest` FOR EACH ROW REPLACE INTO `sbtest`.`_sb
test_new` (`id`, `k`, `c`, `pad`, `vid`, `vid2`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`, NEW.`vid`, NEW.`vid2`)
                   11 Query     CREATE TRIGGER `pt_osc_sbtest_sbtest_ins` AFTER INSERT ON `sbtest`.`sbtest` FOR EACH ROW REPLACE INTO `sbtest`.`_sb
test_new` (`id`, `k`, `c`, `pad`, `vid`, `vid2`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`, NEW.`vid`, NEW.`vid2`)

     4、按主鍵or唯一索引進行排序，分成若干chunk進行數據copy

                   11 Query     EXPLAIN SELECT * FROM `sbtest`.`sbtest` WHERE 1=1
                   11 Query     SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) ORDER BY `id` LIMIT 1 /*first lo
wer boundary*/
                   11 Query     SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX (`PRIMARY`) WHERE `id` IS NOT NULL ORDER BY
 `id` LIMIT 1 /*key_len*/
                   11 Query     EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest` FORCE INDEX (`PRIMARY`) WHERE `id` >= '1' /*key_le
n*/
                   11 Query     EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) OR
DER BY `id` LIMIT 999, 2 /*next chunk boundary*/
                   11 Query     SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) ORDER BY `
id` LIMIT 999, 2 /*next chunk boundary*/
                   11 Query     SHOW WARNINGS
                   11 Query     SHOW GLOBAL STATUS LIKE 'Threads_running'
                   11 Query     EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001'))
 ORDER BY `id` LIMIT 19329, 2 /*next chunk boundary*/
                   11 Query     SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) ORDER B
Y `id` LIMIT 19329, 2 /*next chunk boundary*/
                   11 Query     EXPLAIN SELECT `id`, `k`, `c`, `pad`, `vid`, `vid2` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '
1001')) AND ((`id` <= '20330')) LOCK IN SHARE MODE /*explain pt-online-schema-change 17219 copy nibble*/
                   11 Query     INSERT LOW_PRIORITY IGNORE INTO `sbtest`.`_sbtest_new` (`id`, `k`, `c`, `pad`, `vid`, `vid2`) SELECT `id`, `k`, `c`
, `pad`, `vid`, `vid2` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) AND ((`id` <= '20330')) LOCK IN SHARE MODE /*pt-onlin
e-schema-change 17219 copy nibble*/

    5、rename表，默認刪除舊表

                   11 Query     RENAME TABLE `sbtest`.`sbtest` TO `sbtest`.`_sbtest_old`, `sbtest`.`_sbtest_new` TO `sbtest`.`sbtest`
                   11 Query     DROP TABLE IF EXISTS `sbtest`.`_sbtest_old`

    那這樣的話，如果我們在使用pt-online-schema-change工具在線online DDL某個表的時候，同時對該表的主鍵or唯一索引字段進行DML，是否會存在異常呢？

    實驗場景如下：

    第一個窗口：

shell>pt-online-schema-change -u linzj -h 192.168.110.131 -p linzj --alter='add column vid3 int' --execute D=sbtest,t=sbtest
Found 2 slaves:
  mysql2
  ansible
Will check slave lag on:
  mysql2
  ansible
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `sbtest`.`sbtest`...
Creating new table...
Created new table sbtest._sbtest_new OK.
Waiting forever for new table `sbtest`.`_sbtest_new` to replicate to mysql2...
Altering new table...
Altered `sbtest`.`_sbtest_new` OK.
2017-04-07T14:52:50 Creating triggers...
2017-04-07T14:52:50 Created triggers OK.
2017-04-07T14:52:50 Copying approximately 986400 rows...
Copying `sbtest`.`sbtest`:  86% 00:04 remain
2017-04-07T14:53:27 Copied rows OK.
2017-04-07T14:53:27 Swapping tables...
2017-04-07T14:53:27 Swapped original and new tables OK.
2017-04-07T14:53:27 Dropping old table...
2017-04-07T14:53:27 Dropped old table `sbtest`.`_sbtest_old` OK.
2017-04-07T14:53:27 Dropping triggers...
2017-04-07T14:53:27 Dropped triggers OK.
Successfully altered `sbtest`.`sbtest`.

    第二個窗口：

root@localhost:mysql3306.sock  15:44:  [sbtest]>select count(*) from sbtest;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.17 sec)

root@localhost:mysql3306.sock  15:44:  [sbtest]>update sbtest set id=9999999 where id =110;            
Query OK, 1 row affected (1.33 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost:mysql3306.sock  15:45:  [sbtest]>update sbtest set id=9999998 where id =111;
Query OK, 1 row affected (0.84 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost:mysql3306.sock  15:46:  [sbtest]>update sbtest set id=9999997 where id =112;
Query OK, 1 row affected (0.75 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost:mysql3306.sock  15:46:  [sbtest]>select count(*) from sbtest;
+----------+
| count(*) |
+----------+
|  1000003 |
+----------+
1 row in set (0.70 sec)

root@localhost:mysql3306.sock  15:46:  [sbtest]>select * from sbtest order by id desc limit 5;
+---------+---+---+----------------------------------------------------+------+------+------+
| id      | k | c | pad                                                | vid  | vid2 | vid3 |
+---------+---+---+----------------------------------------------------+------+------+------+
| 9999999 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL |
| 9999998 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL |
| 9999997 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL |
| 1000000 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL |
|  999999 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL |
+---------+---+---+----------------------------------------------------+------+------+------+
5 rows in set (0.00 sec)

root@localhost:mysql3306.sock  15:46:  [sbtest]>select * from sbtest where id in (110,111,112);
+-----+---+---+----------------------------------------------------+------+------+------+
| id  | k | c | pad                                                | vid  | vid2 | vid3 |
+-----+---+---+----------------------------------------------------+------+------+------+
| 110 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL |
| 111 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL |
| 112 | 0 |   | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt | NULL | NULL | NULL |
+-----+---+---+----------------------------------------------------+------+------+------+
3 rows in set (0.02 sec)

同時對表的主鍵or唯一索引進行修改的話，這時候就會出現新表的數據比舊表數據多的情況發現。這應該算是pt-online-schema-change工具的一個bug，為何會出現這種情況，請仔細觀察下pt-online-schema-change工具在原表創建的3個觸發器的定義就可以很容易發現了。

    建議大家，在使用pt-online-schema-change的時候，暫停對表主鍵or唯一索引列的數據更新。

pt_online_schema_change典型的用法：

1）添加一列，并不真正執行
pt-online-schema-change –alter “add column c1 int” D=mydb,t=mytable –dry-run

2）更新存儲引擎為InnoDB，不刪除原表
pt-online-schema-change –alter “ENGINE=InnoDB” –no-drop-old-table –print –statistics –execute D=mydb,t=mytable –execute

3）復制環境下，忽略日志篩選和Slave復制延遲，刪除表字段
pt-online-schema-change –no-check-replication-filters –recursion-method=none –alter “drop company_type,drop channel_code” h=192.168.10.14,P=3370,u=user1,p=pass1,D=db1,t=table1 –print –statistics –execute

4）更新被子表引用到的父表
pt-online-schema-change –alter “add newcol int” h=192.168.10.14,P=3370,u=user1,p=pass1,D=db1,t=table1 –alter-foreign-keys-method auto –print –statistics –execute

5）在我們的雙主復制環境中，設定了忽略mysql庫的復制，不是很在乎復制的延遲，有時有外鍵影響，希望盡量保留原表數據，必要時自行刪除。
pt-online-schema-change –no-check-replication-filters –recursion-method=none –alter “drop newcol” h=192.168.10.14,P=3370,u=user1,p=pass1,D=db1,t=table1 –alter-foreign-keys-method auto –no-drop-old-table –print –statistics –execute