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: 0mysql> 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> View Code

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) View Code

修改記錄：修改沒有問題

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: 0mysql> 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) View Code

刪除記錄，刪除記錄失敗，原表數據不變，新表數據不變

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) View Code

看報錯信息“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執行的過程中，如果有對原表的增刪改操作，增改操作正常運行，刪除操作會失敗。

?

轉載于:https://www.cnblogs.com/zuoxingyu/p/4996652.html

總結

以上是生活随笔為你收集整理的pt-online-schema-change 修改主键导致数据删除失败的问题调查的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。