How to modify the field length of a table online with pt-online-schema-change
Pt-online-schema-change dependency conditions:
The table of the operation must have a primary key, otherwise the execution will report an error
The experiments are as follows:
MySQL [mysql] > create database chenfeng
Query OK, 1 row affected (0.00 sec)
MySQL [mysql] > use chenfeng
Database changed
Create a table test with a primary key:
MySQL [chenfeng] > create table test
-> (id int (10) not null auto_increment
-> k int (10) not null default'0'
-> c char (120) not null default'
-> primary key (id))
-> engine=innodb default charset=utf8
Query OK, 0 rows affected (0.03 sec)
MySQL [chenfeng] > desc test
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (10) | NO | PRI | NULL | auto_increment |
| | k | int (10) | NO | | 0 | |
| | c | char (120) | NO | |
+-+ +
3 rows in set (0.01sec)
MySQL [chenfeng] >
MySQL [chenfeng] > show create table test\ G
* * 1. Row *
Table: test
Create Table: CREATE TABLE `test` (
`id`int (10) NOT NULL AUTO_INCREMENT
`k`int (10) NOT NULL DEFAULT'0'
`c` char (120) NOT NULL DEFAULT''
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
[root@chenfeng] # pt-online-schema-change-- alter= "modify c varchar not null default''"-- user=root-- password=123456 Drunchenfengle tasking test-- charset=utf8-- execute
No slaves found. See-- recursion-method if host chenfeng 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 `chenfeng`.`test`...
Creating new table...
Created new table chenfeng._test_new OK.
Altering new table...
Altered `chenfeng`.` _ test_ new` OK.
2016-10-07T18:57:36 Creating triggers...
2016-10-07T18:57:36 Created triggers OK.
2016-10-07T18:57:36 Copying approximately 1 rows...
2016-10-07T18:57:36 Copied rows OK.
2016-10-07T18:57:36 Analyzing new table...
2016-10-07T18:57:36 Swapping tables...
2016-10-07T18:57:36 Swapped original and new tables OK.
2016-10-07T18:57:36 Dropping old table...
2016-10-07T18:57:36 Dropped old table `chenfeng`.` _ test_ old` OK.
2016-10-07T18:57:36 Dropping triggers...
2016-10-07T18:57:36 Dropped triggers OK.
Successfully altered `chenfeng`.`test`.
[root@chenfeng ~] #
MySQL [(none)] > use chenfeng
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 [chenfeng] > show tables
+-+
| | Tables_in_chenfeng |
+-+
| | test |
+-+
1 row in set (0.00 sec)
MySQL [chenfeng] > desc test
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (10) | NO | PRI | NULL | auto_increment |
| | k | int (10) | NO | | 0 | |
| | c | varchar (150) | NO | |
+-+ +
3 rows in set (0.00 sec)
Varchar is the result we want.
