pt-online-schema-change 修改主键导致数据删除失败的问题调查

pt-online-schema-change在线DDL工具可以做到DDL操作不锁表,不影响线上操作。对于线上超过100W的大表,一般情况下都用这个工具做DDL,最重要的考虑点还是“不影响线上操作
pt-online-schema-change内部操作流程
1)创新新的临时表,临时表为DDL后的目标表结构
2)在原表上创建增删改三个触发器,当原表有数据DML操作时,通过触发器同步数据到新的临时表
3)把原表的数据分批倒入到新的临时表
4)新表,老表做表名称互换操作
5)删除修改后表的触发器
 
删除失败验证步骤
1)创建表,ID自增,作为主键,10条记录
mysql> CREATE TABLE `zxy_test` ( -> `id` bigint(20) NOT NULL AUTO_INCREMENT, -> `FUSERID` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`), -> KEY `idx` (`FUSERID`) -> ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.00 sec) mysql> insert into zxy_test values(1,1),(2,2),(3,3),(4,4); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0

2)把这个表的主键从ID改成FUSERID,用pt这个工具,在改的过程中,对原表做增删改的操作。表记录数很小,不做真实的ddl全部操作,print出pt操作过程,人为的把DDL时间延长,以求有足够的时间做中间操作。模拟pt工具,创建new表,创建触发器

pt执行的命令是:pt-online-schema-change --user=root --password='XXXX' --host=10.3.172.112 D=test,t=zxy_test  --alter "DROP ID,ADD PRIMARY KEY(FUSERID);" --charset=utf8 --no-check-replication-filters  --execute
mysql> CREATE TABLE `test`.`_zxy_test_new` (
-> `id` bigint(20) NOT NULL AUTO_INCREMENT,
-> `FUSERID` int(11) DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> KEY `idx` (`FUSERID`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE `test`.`_zxy_test_new` DROP ID,ADD PRIMARY KEY(FUSERID);
Query OK, 0 rows affected (1.30 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> CREATE TRIGGER `pt_osc_test_zxy_test_del` AFTER DELETE ON `test`.`zxy_test` FOR EACH ROW DELETE IGNORE FROM `test`.`_zxy_test_new` WHERE `test`.`_zxy_test_new`.`id` <=> OLD.`id`;
Query OK, 0 rows affected (0.00 sec) mysql> CREATE TRIGGER `pt_osc_test_zxy_test_upd` AFTER UPDATE ON `test`.`zxy_test` FOR EACH ROW REPLACE INTO `test`.`_zxy_test_new` (`fuserid`) VALUES (NEW.`fuserid`);
Query OK, 0 rows affected (0.01 sec) mysql> CREATE TRIGGER `pt_osc_test_zxy_test_ins` AFTER INSERT ON `test`.`zxy_test` FOR EACH ROW REPLACE INTO `test`.`_zxy_test_new` (`fuserid`) VALUES (NEW.`fuserid`);
Query OK, 0 rows affected (0.00 sec) mysql>

3)开始对原表的数据做增删改操作,操作记录会通过触发器同步到新表

新增的记录:新增没有问题
mysql> select * from zxy_test;
+----+---------+
| id | FUSERID |
+----+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+---------+
4 rows in set (0.00 sec) mysql> select * from _zxy_test_new;
Empty set (0.00 sec) mysql> insert into zxy_test values(5,5);
Query OK, 1 row affected (0.00 sec) mysql> select * from zxy_test;
+----+---------+
| id | FUSERID |
+----+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+----+---------+
5 rows in set (0.00 sec) mysql> select * from _zxy_test_new;
+---------+
| FUSERID |
+---------+
| 5 |
+---------+
1 row in set (0.00 sec)

修改记录:修改没有问题

mysql> select * from zxy_test;
+----+---------+
| id | FUSERID |
+----+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+----+---------+
5 rows in set (0.00 sec) mysql> select * from _zxy_test_new;
+---------+
| FUSERID |
+---------+
| 5 |
+---------+
1 row in set (0.00 sec) mysql> update zxy_test set fuserid=10 where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from zxy_test;
+----+---------+
| id | FUSERID |
+----+---------+
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 1 | 10 |
+----+---------+
5 rows in set (0.00 sec) mysql> select * from _zxy_test_new;
+---------+
| FUSERID |
+---------+
| 5 |
| 10 |
+---------+
2 rows in set (0.00 sec)

删除记录,删除记录失败,原表数据不变,新表数据不变

mysql> select * from zxy_test;
+----+---------+
| id | FUSERID |
+----+---------+
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 1 | 10 |
+----+---------+
5 rows in set (0.00 sec) mysql> select * from _zxy_test_new;
+---------+
| FUSERID |
+---------+
| 5 |
| 10 |
+---------+
2 rows in set (0.00 sec) mysql> delete from zxy_test where fuserid=2;
ERROR 1054 (42S22): Unknown column 'test._zxy_test_new.id' in 'where clause'
mysql> select * from zxy_test;
+----+---------+
| id | FUSERID |
+----+---------+
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 1 | 10 |
+----+---------+
5 rows in set (0.00 sec) mysql> select * from _zxy_test_new;
+---------+
| FUSERID |
+---------+
| 5 |
| 10 |
+---------+
2 rows in set (0.00 sec)

看报错信息“ERROR 1054 (42S22): Unknown column 'test._zxy_test_new.id' in 'where clause'”,提示test._zxy_test_new.id 不存在,修改后的表是没有id字段的,这个错误是由delete触发器报出。delete触发器命令:CREATE TRIGGER `pt_osc_test_zxy_test_del` AFTER DELETE ON `test`.`zxy_test` FOR EACH ROW DELETE IGNORE FROM `test`.`_zxy_test_new` WHERE `test`.`_zxy_test_new`.`id` <=> OLD.`id`; 在删除原表记录的时候,通过主键去定位被触发的记录,在new表里删除,这样做的目的是保证删除触发删除的记录在两个表里绝对是一致的。

最后贴出pt执行时,在MYSQL里面执行的全部SQL命令,关键信息蓝色加粗,完全对应文章开篇的步骤
13134 Query SHOW TABLES FROM `test` LIKE 'zxy\_test'
13134 Query SHOW TRIGGERS FROM `test` LIKE 'zxy\_test'
13134 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
13134 Query USE `test`
13134 Query SHOW CREATE TABLE `test`.`zxy_test`
13134 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
13134 Query EXPLAIN SELECT * FROM `test`.`zxy_test` WHERE 1=1
13134 Query SHOW INDEXES FROM `test`.`zxy_test` WHERE Key_name = 'idx'
13134 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE constraint_schema='test' AND referenced_table_name='zxy_test'
151125 15:36:59 13134 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
13134 Query USE `test`
13134 Query SHOW CREATE TABLE `test`.`zxy_test`
13134 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
13134 Query CREATE TABLE `test`.`_zxy_test_new` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`FUSERID` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx` (`FUSERID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4
13134 Query ALTER TABLE `test`.`_zxy_test_new` DROP ID,ADD PRIMARY KEY(FUSERID)
151125 15:37:01 13134 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
13134 Query USE `test`
13134 Query SHOW CREATE TABLE `test`.`_zxy_test_new`
13134 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
13134 Query CREATE TRIGGER `pt_osc_test_zxy_test_del` AFTER DELETE ON `test`.`zxy_test` FOR EACH ROW DELETE IGNORE FROM `test`.`_zxy_test_new` WHERE `test`.`_zxy_test_new`.`id` <=> OLD.`id`
13134 Query CREATE TRIGGER `pt_osc_test_zxy_test_upd` AFTER UPDATE ON `test`.`zxy_test` FOR EACH ROW REPLACE INTO `test`.`_zxy_test_new` (`fuserid`) VALUES (NEW.`fuserid`)
13134 Query CREATE TRIGGER `pt_osc_test_zxy_test_ins` AFTER INSERT ON `test`.`zxy_test` FOR EACH ROW REPLACE INTO `test`.`_zxy_test_new` (`fuserid`) VALUES (NEW.`fuserid`)
13134 Query EXPLAIN SELECT * FROM `test`.`zxy_test` WHERE 1=1
13134 Query SHOW INDEXES FROM `test`.`zxy_test` WHERE Key_name = 'idx'
13134 Query INSERT LOW_PRIORITY IGNORE INTO `test`.`_zxy_test_new` (`fuserid`) SELECT `fuserid` FROM `test`.`zxy_test` /*pt-online-schema-change 27995 copy table*/
13134 Query SHOW WARNINGS
13134 Query SHOW GLOBAL STATUS LIKE 'Threads_running'
13134 Query RENAME TABLE `test`.`zxy_test` TO `test`.`_zxy_test_old`, `test`.`_zxy_test_new` TO `test`.`zxy_test`
13134 Query DROP TABLE IF EXISTS `test`.`_zxy_test_old`
13134 Query DROP TRIGGER IF EXISTS `test`.`pt_osc_test_zxy_test_del`
13134 Query DROP TRIGGER IF EXISTS `test`.`pt_osc_test_zxy_test_upd`
13134 Query DROP TRIGGER IF EXISTS `test`.`pt_osc_test_zxy_test_ins`
13134 Query SHOW TABLES FROM `test` LIKE '\_zxy\_test\_new'

结论:

用PT工具做主键删除的DDL,在DDL执行的过程中,如果有对原表的增删改操作,增改操作正常运行,删除操作会失败。

 

上一篇:Ubuntu下安装Node.js


下一篇:利用socket实现在线购买电脑零配件