environment: rhel6 + mysql5.6 + pt-osc 3.0.3
how to repeat:
mysql> show create table test02;
±-------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±-------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test02 | CREATE TABLE “test02” (
“id” int(11) NOT NULL DEFAULT ‘0’,
[COLOR=#FF0000] “name” varchar(10) DEFAULT NULL COMMENT ‘姓名’,
“col1” int(10) DEFAULT NULL COMMENT ‘字段1’,
PRIMARY KEY (“id”)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
±-------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
You have new mail in /var/spool/mail/root
[root@rhel01 ~]# pt-online-schema-change --host=localhost --port=3306 --user=root --password=xxx --progress time,30 --print --alter=“modify column col1 int(10) comment ‘[COLOR=#FF0000]呵呵’” D=test02,t=test02 --execute
No slaves found. See --recursion-method if host rhel01 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering test02.test02…
Creating new table…
CREATE TABLE test02._test02_new (
id int(11) NOT NULL DEFAULT ‘0’,
[COLOR=#FF0000]name varchar(10) DEFAULT NULL COMMENT ‘??’,
[COLOR=#FF0000]col1 int(10) DEFAULT NULL COMMENT ‘??1’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Created new table test02._test02_new OK.
Altering new table…
[COLOR=#FF0000]ALTER TABLE test02._test02_new modify column col1 int(10) comment ‘呵呵’
Altered test02._test02_new OK.
2017-10-12T17:45:31 Creating triggers…
CREATE TRIGGER pt_osc_test02_test02_del AFTER DELETE ON test02.test02 FOR EACH ROW DELETE IGNORE FROM test02._test02_new WHERE test02._test02_new.id <=> OLD.id
CREATE TRIGGER pt_osc_test02_test02_upd AFTER UPDATE ON test02.test02 FOR EACH ROW BEGIN DELETE IGNORE FROM test02._test02_new WHERE !(OLD.id <=> NEW.id) AND test02._test02_new.id <=> OLD.id;REPLACE INTO test02._test02_new (id, name, col1) VALUES (NEW.id, NEW.name, NEW.col1);END
CREATE TRIGGER pt_osc_test02_test02_ins AFTER INSERT ON test02.test02 FOR EACH ROW REPLACE INTO test02._test02_new (id, name, col1) VALUES (NEW.id, NEW.name, NEW.col1)
2017-10-12T17:45:31 Created triggers OK.
2017-10-12T17:45:31 Copying approximately 1 rows…
INSERT LOW_PRIORITY IGNORE INTO test02._test02_new (id, name, col1) SELECT id, name, col1 FROM test02.test02 LOCK IN SHARE MODE /pt-online-schema-change 66382 copy table/
2017-10-12T17:45:31 Copied rows OK.
2017-10-12T17:45:31 Analyzing new table…
2017-10-12T17:45:31 Swapping tables…
RENAME TABLE test02.test02 TO test02._test02_old, test02._test02_new TO test02.test02
2017-10-12T17:45:31 Swapped original and new tables OK.
2017-10-12T17:45:31 Dropping old table…
DROP TABLE IF EXISTS test02._test02_old
2017-10-12T17:45:31 Dropped old table test02._test02_old OK.
2017-10-12T17:45:31 Dropping triggers…
DROP TRIGGER IF EXISTS test02.pt_osc_test02_test02_del;
DROP TRIGGER IF EXISTS test02.pt_osc_test02_test02_upd;
DROP TRIGGER IF EXISTS test02.pt_osc_test02_test02_ins;
2017-10-12T17:45:31 Dropped triggers OK.
Successfully altered test02.test02.
You have new mail in /var/spool/mail/root
[root@rhel01 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 73
Server version: 5.6.27-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use test02;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show create table test02;
±-------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±-------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test02 | CREATE TABLE “test02” (
“id” int(11) NOT NULL DEFAULT ‘0’,
[COLOR=#FF0000]“name” varchar(10) DEFAULT NULL COMMENT ‘??’,
“col1” int(10) DEFAULT NULL COMMENT ‘呵呵’,
PRIMARY KEY (“id”)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
±-------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)