目的

线上一张表的字段长度变更

`sGuid` varchar(255) DEFAULT NULL COMMENT 'sGuid'

=》

`sGuid` varchar(512) DEFAULT NULL COMMENT 'sGuid'

 

方法

pt-online-schema-change --user=xxxx--password=xxxxxx --host=127.0.0.1 --port=3306 \--charset=utf8mb4 D=db_main,t=tb_main \--alter "modify sGuid varchar(512) DEFAULT NULL COMMENT 'sGuid'" \--no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none \--critical-load="Threads_running=50" --max-load="Threads_running=100" \--print --execute

执行时报错：

2019-07-25T11:03:00 Error copying rows from `db_main`.`tb_main` to `db_main`.`_tb_main_new`: 2019-07-25T11:03:00 Copying rows caused a MySQL error 1300:    Level: Warning     Code: 1300  Message: Invalid utf8 character string: 'CE'

 

在 percona 官网找到原因：  ，这里直接贴出原因。

pt-online-schema-change could emit utf8 errors for binary PK:Workaround specify latin1 charset for pt-o-s-c:pt-online-schema-change --execute --charset=latin1 --chunk-size 2 --alter 'engine=innodb' D=test,t=brokenutf8alterpt-o-s-c still processes the table correctly (utf8mb4 symbols stored in additional varchar field are valid after pt-o-s-c run). 4-byte changes also processed correctly by pt-o-s-c triggers.Possible fix: use binary or hex literals instead of '?' substitution.

 

解决

用 latin1 字符集代替 utf8

pt-online-schema-change --user=xxxx--password=xxxxxx --host=127.0.0.1 --port=3306 \--charset=latin1 D=db_main,t=tb_main \--alter "modify sGuid varchar(512) DEFAULT NULL COMMENT 'sGuid'" \--no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none \--critical-load="Threads_running=50" --max-load="Threads_running=100" \--print --execute