Percona-Tookit工具包之pt-online-schema-change
2018-06-22 00:49:58来源：未知 阅读 ()
Preface
As we all know,it's really a troublesome thing to DBA in scenario of changing table structure online.Since MySQL 5.6,it support online ddl operations.On the other hand,we also use third party tool such as gh-ost.Let's learn another famous tool of Percona-Toolkit,that is pt-online-schema-change.
Introduce
pt-osc is a tool which is commonly used in Percona-Toolkit suits which .It bases on triggers,by creating temporary middle table to dipose the table structure changing issue.MySQL 8.0.13 will support a new feature of instant adding columns(but only add,not change).Therefore,ps-osc still has its value in the "online ddl change" field.
Procedure
1 ###Commonly used parameter introduce.### 2 connect parameter: 3 -h hostname -P port -u username -p password -S socket 4 5 output parameter: 6 --print -- Show whole procedure of ddl operation on the screen. 7 8 main parameter: 9 --alter -- Specify the ddl statement using quotation mark behind. 10 11 alternative parameter: 12 --dry-run -- Only operate the new table instead of original one. 13 --execute -- Directly do all opertion both new and original table. 14 15 other parameter: 16 --no-check-alter -- Don't parse your ddl statment which follows behind. 17 --ask-pass -- You cannot use -p without a string to input password,so you need this parameter. 18 19 ###Create test table.### 20 (root@localhost mysql3306.sock)[zlm]09:38:11>create table test_ddl( 21 -> id int unsigned auto_increment, 22 -> name varchar(20) not null default '', 23 -> tel tinyint unsigned not null default 0, 24 -> primary key(id) 25 -> ) engine=innodb charset=utf8mb4; 26 Query OK, 0 rows affected (0.01 sec) 27 28 ###Insert records into table.### 29 (root@localhost mysql3306.sock)[zlm]09:39:28>insert into test_ddl(name,tel) values('zlm',13866668888); 30 ERROR 1264 (22003): Out of range value for column 'tel' at row 1 31 (root@localhost mysql3306.sock)[zlm]09:41:07>insert into test_ddl(name,tel) values('zlm',256); 32 ERROR 1264 (22003): Out of range value for column 'tel' at row 1 33 (root@localhost mysql3306.sock)[zlm]09:41:18>insert into test_ddl(name,tel) values('zlm',255); -- Scope of unsigned tinyint is 0~255. 34 Query OK, 1 row affected (0.00 sec) 35 36 (root@localhost mysql3306.sock)[zlm]09:41:22>select * from test_ddl; 37 +----+------+-----+ 38 | id | name | tel | 39 +----+------+-----+ 40 | 11 | zlm | 255 | 41 +----+------+-----+ 42 1 row in set (0.00 sec) 43 44 ###Change column "tel" from tinyint to bigint.### 45 [root@zlm2 09:52:25 ~] 46 #pt-online-schema-change -hzlm2 -P3306 -uroot --ask-pass --alter "modify tel bigint default 0" --print --execute --no-check-alter D=zlm,t=test_ddl 47 Enter MySQL password: 48 49 ###Check slave information.### 50 No slaves found. See --recursion-method if host zlm2 has slaves. 51 Not checking slave lag because no slaves were found and --check-slave-lag was not specified. 52 53 ###Summary of operations.### 54 Operation, tries, wait: 55 analyze_table, 10, 1 56 copy_rows, 10, 0.25 57 create_triggers, 10, 1 58 drop_triggers, 10, 1 59 swap_tables, 10, 1 60 update_foreign_keys, 10, 1 61 Altering `zlm`.`test_ddl`... 62 63 ###Create new table.### 64 Creating new table... 65 CREATE TABLE `zlm`.`_test_ddl_new` ( 66 `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 67 `name` varchar(20) NOT NULL DEFAULT '', 68 `tel` tinyint(3) unsigned NOT NULL DEFAULT '0', 69 PRIMARY KEY (`id`) 70 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 71 Created new table zlm._test_ddl_new OK. 72 73 ###Alter column in new table.### 74 Altering new table... 75 ALTER TABLE `zlm`.`_test_ddl_new` modify tel bigint default 0 76 Altered `zlm`.`_test_ddl_new` OK. -- If you specify "dry-run",it will end here. 77 78 ###Create Triggers.### 79 2018-06-20T10:38:43 Creating triggers... 80 2018-06-20T10:38:43 Created triggers OK. 81 82 ###Copy rows into new table.### 83 2018-06-20T10:38:43 Copying approximately 1 rows... 84 INSERT LOW_PRIORITY IGNORE INTO `zlm`.`_test_ddl_new` (`id`, `name`, `tel`) SELECT `id`, `name`, `tel` FROM `zlm`.`test_ddl` LOCK IN SHARE MODE /*pt-online-schema-change 4190 copy table*/ 85 2018-06-20T10:38:43 Copied rows OK. 86 87 ###Swap oraiginal table with new table.### 88 2018-06-20T10:38:43 Analyzing new table... 89 2018-06-20T10:38:43 Swapping tables... 90 RENAME TABLE `zlm`.`test_ddl` TO `zlm`.`_test_ddl_old`, `zlm`.`_test_ddl_new` TO `zlm`.`test_ddl` 91 2018-06-20T10:38:43 Swapped original and new tables OK. 92 93 ###Drop original table.### 94 2018-06-20T10:38:43 Dropping old table... 95 DROP TABLE IF EXISTS `zlm`.`_test_ddl_old` 96 2018-06-20T10:38:43 Dropped old table `zlm`.`_test_ddl_old` OK. 97 98 ###Drop triggers.### 99 2018-06-20T10:38:43 Dropping triggers... 100 DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_del` 101 DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_upd` 102 DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_ins` 103 2018-06-20T10:38:43 Dropped triggers OK. 104 Successfully altered `zlm`.`test_ddl`. 105 106 ###Check table ddl.### 107 (root@localhost mysql3306.sock)[zlm]09:58:32>show create table test_ddl; 108 +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 109 | Table | Create Table | 110 +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 111 | test_ddl | CREATE TABLE `test_ddl` ( 112 `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 113 `name` varchar(20) NOT NULL DEFAULT '', 114 `tel` bigint(20) DEFAULT '0', -- The column "tel" has be changed into bigint type now. 115 PRIMARY KEY (`id`) 116 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 | 117 +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 118 1 row in set (0.01 sec) 119 120 ###Check general log file for detail.### 121 [root@zlm2 10:38:54 /data/mysql/mysql3306/data] 122 #cat zlm2.log 123 124 mysqld, Version: 5.7.21-log (MySQL Community Server (GPL)). started with: 125 Tcp port: 3306 Unix socket: /tmp/mysql3306.sock 126 Time Id Command Argument 127 2018-06-20T08:38:37.476403Z 16 Query show global variables like '%general_log%' 128 2018-06-20T08:38:43.828985Z 19 Connect root@localhost on zlm using TCP/IP 129 2018-06-20T08:38:43.829297Z 19 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout' 130 2018-06-20T08:38:43.832850Z 19 Query SET SESSION innodb_lock_wait_timeout=1 131 2018-06-20T08:38:43.833041Z 19 Query SHOW VARIABLES LIKE 'lock\_wait_timeout' 132 2018-06-20T08:38:43.836907Z 19 Query SET SESSION lock_wait_timeout=60 133 2018-06-20T08:38:43.837752Z 19 Query SHOW VARIABLES LIKE 'wait\_timeout' 134 2018-06-20T08:38:43.842090Z 19 Query SET SESSION wait_timeout=10000 135 2018-06-20T08:38:43.842222Z 19 Query SELECT @@SQL_MODE 136 2018-06-20T08:38:43.842350Z 19 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/ 137 2018-06-20T08:38:43.842453Z 19 Query SELECT @@server_id /*!50038 , @@hostname*/ 138 2018-06-20T08:38:43.843311Z 20 Connect root@localhost on zlm using TCP/IP 139 2018-06-20T08:38:43.843620Z 20 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout' 140 2018-06-20T08:38:43.847069Z 20 Query SET SESSION innodb_lock_wait_timeout=1 141 2018-06-20T08:38:43.847442Z 20 Query SHOW VARIABLES LIKE 'lock\_wait_timeout' 142 2018-06-20T08:38:43.851645Z 20 Query SET SESSION lock_wait_timeout=60 143 2018-06-20T08:38:43.851904Z 20 Query SHOW VARIABLES LIKE 'wait\_timeout' 144 2018-06-20T08:38:43.853942Z 20 Query SET SESSION wait_timeout=10000 145 2018-06-20T08:38:43.854029Z 20 Query SELECT @@SQL_MODE 146 2018-06-20T08:38:43.854055Z 20 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/ 147 2018-06-20T08:38:43.854152Z 20 Query SELECT @@server_id /*!50038 , @@hostname*/ 148 2018-06-20T08:38:43.854356Z 19 Query SHOW VARIABLES LIKE 'wsrep_on' 149 2018-06-20T08:38:43.857148Z 19 Query SHOW VARIABLES LIKE 'version%' 150 2018-06-20T08:38:43.860325Z 19 Query SHOW ENGINES 151 2018-06-20T08:38:43.861002Z 19 Query SHOW VARIABLES LIKE 'innodb_version' 152 2018-06-20T08:38:43.864957Z 19 Query SHOW VARIABLES LIKE 'innodb_stats_persistent' 153 2018-06-20T08:38:43.867873Z 19 Query SELECT @@SERVER_ID 154 2018-06-20T08:38:43.868114Z 19 Query SHOW GRANTS FOR CURRENT_USER() 155 2018-06-20T08:38:43.868774Z 19 Query SHOW FULL PROCESSLIST 156 2018-06-20T08:38:43.869245Z 19 Query SHOW SLAVE HOSTS 157 2018-06-20T08:38:43.869849Z 19 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 158 2018-06-20T08:38:43.874152Z 19 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 159 2018-06-20T08:38:43.877509Z 19 Query SELECT CONCAT(@@hostname, @@port) 160 2018-06-20T08:38:43.877973Z 19 Query SHOW TABLES FROM `zlm` LIKE 'test\_ddl' 161 2018-06-20T08:38:43.878229Z 19 Query SELECT VERSION() 162 2018-06-20T08:38:43.879011Z 19 Query SHOW TRIGGERS FROM `zlm` LIKE 'test\_ddl' 163 2018-06-20T08:38:43.879506Z 19 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ 164 2018-06-20T08:38:43.879602Z 19 Query USE `zlm` 165 2018-06-20T08:38:43.879706Z 19 Query SHOW CREATE TABLE `zlm`.`test_ddl` 166 2018-06-20T08:38:43.880123Z 19 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ 167 2018-06-20T08:38:43.880979Z 19 Query EXPLAIN SELECT * FROM `zlm`.`test_ddl` WHERE 1=1 168 2018-06-20T08:38:43.881846Z 19 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='zlm' AND referenced_table_name='test_ddl' 169 2018-06-20T08:38:43.893011Z 19 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ 170 2018-06-20T08:38:43.893622Z 19 Query USE `zlm` 171 2018-06-20T08:38:43.893798Z 19 Query SHOW CREATE TABLE `zlm`.`test_ddl` 172 2018-06-20T08:38:43.893954Z 19 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ 173 2018-06-20T08:38:43.894730Z 19 Query CREATE TABLE `zlm`.`_test_ddl_new` ( 174 `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 175 `name` varchar(20) NOT NULL DEFAULT '', 176 `tel` int(11) DEFAULT '0', 177 PRIMARY KEY (`id`) 178 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 179 2018-06-20T08:38:43.914358Z 19 Query ALTER TABLE `zlm`.`_test_ddl_new` modify tel bigint default 0 180 2018-06-20T08:38:43.934771Z 19 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ 181 2018-06-20T08:38:43.934930Z 19 Query USE `zlm` 182 2018-06-20T08:38:43.935044Z 19 Query SHOW CREATE TABLE `zlm`.`_test_ddl_new` 183 2018-06-20T08:38:43.936057Z 19 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ 184 2018-06-20T08:38:43.936926Z 19 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'DELETE' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'zlm' AND EVENT_OBJECT_TABLE = 'test_ddl' 185 2018-06-20T08:38:43.937687Z 19 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'UPDATE' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'zlm' AND EVENT_OBJECT_TABLE = 'test_ddl' 186 2018-06-20T08:38:43.939328Z 19 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'INSERT' AND ACTION_TIMING = 'AFTER' AND TRIGGER_SCHEMA = 'zlm' AND EVENT_OBJECT_TABLE = 'test_ddl' 187 2018-06-20T08:38:43.940991Z 19 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'DELETE' AND ACTION_TIMING = 'BEFORE' AND TRIGGER_SCHEMA = 'zlm' AND EVENT_OBJECT_TABLE = 'test_ddl' 188 2018-06-20T08:38:43.942390Z 19 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'UPDATE' AND ACTION_TIMING = 'BEFORE' AND TRIGGER_SCHEMA = 'zlm' AND EVENT_OBJECT_TABLE = 'test_ddl' 189 2018-06-20T08:38:43.943844Z 19 Query SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_MANIPULATION = 'INSERT' AND ACTION_TIMING = 'BEFORE' AND TRIGGER_SCHEMA = 'zlm' AND EVENT_OBJECT_TABLE = 'test_ddl' 190 2018-06-20T08:38:43.944624Z 19 Query CREATE TRIGGER `pt_osc_zlm_test_ddl_del` AFTER DELETE ON `zlm`.`test_ddl` FOR EACH ROW DELETE IGNORE FROM `zlm`.`_test_ddl_new` WHERE `zlm`.`_test_ddl_new`.`id` <=> OLD.`id` 191 2018-06-20T08:38:43.949413Z 19 Query CREATE TRIGGER `pt_osc_zlm_test_ddl_upd` AFTER UPDATE ON `zlm`.`test_ddl` FOR EACH ROW BEGIN DELETE IGNORE FROM `zlm`.`_test_ddl_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `zlm`.`_test_ddl_new`.`id` <=> OLD.`id`;REPLACE INTO `zlm`.`_test_ddl_new` (`id`, `name`, `tel`) VALUES (NEW.`id`, NEW.`name`, NEW.`tel`);END 192 2018-06-20T08:38:43.954329Z 19 Query CREATE TRIGGER `pt_osc_zlm_test_ddl_ins` AFTER INSERT ON `zlm`.`test_ddl` FOR EACH ROW REPLACE INTO `zlm`.`_test_ddl_new` (`id`, `name`, `tel`) VALUES (NEW.`id`, NEW.`name`, NEW.`tel`) 193 2018-06-20T08:38:43.959241Z 19 Query EXPLAIN SELECT * FROM `zlm`.`test_ddl` WHERE 1=1 194 2018-06-20T08:38:43.961358Z 19 Query EXPLAIN SELECT `id`, `name`, `tel` FROM `zlm`.`test_ddl` LOCK IN SHARE MODE /*explain pt-online-schema-change 4190 copy table*/ 195 2018-06-20T08:38:43.962004Z 19 Query INSERT LOW_PRIORITY IGNORE INTO `zlm`.`_test_ddl_new` (`id`, `name`, `tel`) SELECT `id`, `name`, `tel` FROM `zlm`.`test_ddl` LOCK IN SHARE MODE /*pt-online-schema-change 4190 copy table*/ 196 2018-06-20T08:38:43.962769Z 19 Query SHOW WARNINGS 197 2018-06-20T08:38:43.963561Z 19 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 198 2018-06-20T08:38:43.966643Z 19 Query ANALYZE TABLE `zlm`.`_test_ddl_new` /* pt-online-schema-change */ 199 2018-06-20T08:38:43.968221Z 19 Query RENAME TABLE `zlm`.`test_ddl` TO `zlm`.`_test_ddl_old`, `zlm`.`_test_ddl_new` TO `zlm`.`test_ddl` 200 2018-06-20T08:38:43.983005Z 19 Query DROP TABLE IF EXISTS `zlm`.`_test_ddl_old` 201 2018-06-20T08:38:43.990393Z 19 Query DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_del` 202 2018-06-20T08:38:43.991005Z 19 Query DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_upd` 203 2018-06-20T08:38:43.991346Z 19 Query DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_ins` 204 2018-06-20T08:38:43.991966Z 19 Query SHOW TABLES FROM `zlm` LIKE '\_test\_ddl\_new' 205 2018-06-20T08:38:43.999016Z 20 Quit 206 2018-06-20T08:38:44.000302Z 19 Quit
Supplement
1 ###Create a test table "test_ddl_no_pk" without primary key.### 2 (root@localhost mysql3306.sock)[zlm]04:17:53>create table test_ddl_no_pk( 3 -> id int unsigned, 4 -> name varchar(20) not null default '', 5 -> tel tinyint unsigned not null default 0 6 -> ) engine=innodb charset=utf8mb4; 7 Query OK, 0 rows affected (0.12 sec) 8 9 (root@localhost mysql3306.sock)[zlm]04:18:11>insert into test_ddl_no_pk(id,name,tel) values(1,'zlm','110'),(2,'aaron8219','119'); 10 Query OK, 2 rows affected (0.00 sec) 11 Records: 2 Duplicates: 0 Warnings: 0 12 13 ###Insert two records into above table.### 14 (root@localhost mysql3306.sock)[zlm]04:18:26>select * from test_ddl_no_pk; 15 +------+-----------+-----+ 16 | id | name | tel | 17 +------+-----------+-----+ 18 | 1 | zlm | 110 | 19 | 2 | aaron8219 | 119 | 20 +------+-----------+-----+ 21 2 rows in set (0.00 sec) 22 23 ###Execute pt-osc again.### 24 [root@zlm2 04:14:14 ~] 25 #pt-online-schema-change -hzlm2 -P3306 -uroot --ask-pass --alter "modify tel bigint default 0" --print --execute --no-check-alter D=zlm,t=test_ddl_no_pk 26 Enter MySQL password: 27 No slaves found. See --recursion-method if host zlm2 has slaves. 28 Not checking slave lag because no slaves were found and --check-slave-lag was not specified. 29 Operation, tries, wait: 30 analyze_table, 10, 1 31 copy_rows, 10, 0.25 32 create_triggers, 10, 1 33 drop_triggers, 10, 1 34 swap_tables, 10, 1 35 update_foreign_keys, 10, 1 36 Altering `zlm`.`test_ddl_no_pk`... 37 Creating new table... 38 CREATE TABLE `zlm`.`_test_ddl_no_pk_new` ( 39 `id` int(10) unsigned DEFAULT NULL, 40 `name` varchar(20) NOT NULL DEFAULT '', 41 `tel` tinyint(3) unsigned NOT NULL DEFAULT '0' 42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 43 Created new table zlm._test_ddl_no_pk_new OK. 44 Altering new table... 45 ALTER TABLE `zlm`.`_test_ddl_no_pk_new` modify tel bigint default 0 46 Altered `zlm`.`_test_ddl_no_pk_new` OK. 47 2018-06-21T04:19:42 Dropping new table... 48 DROP TABLE IF EXISTS `zlm`.`_test_ddl_no_pk_new`; -- There're no create triggers,copy rows and swap table operations any more but directly drop new table operation. 49 2018-06-21T04:19:42 Dropped new table OK. 50 `zlm`.`test_ddl_no_pk` was not altered. 51 The new table `zlm`.`_test_ddl_no_pk_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger. -- Indicate the reason why it stop working.
Summary
- pt-ocs depends on tirggers,there mustn't be triggers on original table.
- Primary key or unique key is indispensable when use it.Otherwise,it won't work normally.
- Although pt-ocs won't block online transactions but still recommend do online ddl operations in off-peak period.
标签：
版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有
相关文章
- Percona-Tookit工具包之pt-table-usage 2018-07-23
- Percona-Tookit工具包之pt-summary 2018-07-22
- Percona-Tookit工具包之pt-slave-find 2018-07-20
- Percona-Tookit工具包之pt-slave-restart 2018-07-19
- Percona-Tookit工具包之pt-slave-find 2018-07-18
IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash
热门词条
最新资讯
热门关注
热门标签