Shulou(Shulou.com)06/01 Report--

Use pt-online-schema-change to change the length of a field in the data table, and when finished, look at the table structure definition, but there is garbled code.

The table structure of the data table zzzz.

Mysql > show create table zzzz\ G

* * 1. Row *

Table: zzzz

Create Table: CREATE TABLE `zzzz` (

`id`int (11) NOT NULL AUTO_INCREMENT

`name`varchar (10) NOT NULL DEFAULT''COMMENT' item name'

`classiy` varchar (10) NOT NULL DEFAULT''COMMENT' item Classification'

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Product infomation'

1 row in set (0.00 sec)

Change the length of the field name to 20. 0.

$pt-online-schema-change-- alter "modify name varchar (20) not null default''comment' item name'"-- nocheck-replication-filters-- recursion-method=processlist hobby 192.168.4.45, pendant 3316, pendant 123456, execute.

Found 1 slaves:

Db02-> 192.168.4.46 purl 3316

Will check slave lag on:

Db02-> 192.168.4.46 purl 3316

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 `test`.`zzzz`.

Creating new table...

Created new table test._zzzz_new OK.

Waiting forever for new table `test`.` _ zzzz_ new` to replicate to db02...

Altering new table...

Altered `test`.` _ zzzz_ new` OK.

2018-01-15T17:31:27 Creating triggers...

2018-01-15T17:31:27 Created triggers OK.

2018-01-15T17:31:27 Copying approximately 1 rows...

2018-01-15T17:31:28 Copied rows OK.

2018-01-15T17:31:28 Analyzing new table...

2018-01-15T17:31:28 Swapping tables...

2018-01-15T17:31:28 Swapped original and new tables OK.

2018-01-15T17:31:28 Dropping old table...

2018-01-15T17:31:28 Dropped old table `test`.` _ zzzz_ old` OK.

2018-01-15T17:31:28 Dropping triggers...

2018-01-15T17:31:29 Dropped triggers OK.

Successfully altered `test`.`zzzz`.

When finished, look at the table structure and find that the Chinese comments of the fields have become garbled.

Mysql > show create table zzzz\ G

* * 1. Row *

Table: zzzz

Create Table: CREATE TABLE `zzzz` (

`id`int (11) NOT NULL AUTO_INCREMENT

`name` varchar (20) NOT NULL DEFAULT''COMMENT'? ‰?

`roomy`varchar (10) NOT NULL DEFAULT''COMMENT'?

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Product infomation'

1 row in set (0.00 sec)

For the solution, use the option-- charset (its documentation description: Default character set. If the value is utf8, sets Perl's binmode on STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any other value sets binmode on STDOUT without the utf8 layer, and runs SET NAMES after connecting to MySQL.), indicating that the character set is utf8mb4.

$pt-online-schema-change-- alter "modify name varchar (20) not null default''comment' item name'"-- charset=utf8mb4-- nocheck-replication-filters-- recursion-method=processlist hobby 192.168.4.45

Look, it's normal.

Mysql > show create table zzzz\ G

* * 1. Row *

Table: zzzz

Create Table: CREATE TABLE `zzzz` (

`id`int (11) NOT NULL AUTO_INCREMENT

`name`varchar (20) NOT NULL DEFAULT''COMMENT' item name'

`roomy`varchar (10) NOT NULL DEFAULT''COMMENT'?

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Product infomation'

1 row in set (0.00 sec)

For more details about the use of pt-online-schema-change tools, please refer to InnoDB Online DDL.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.