pt-osc 变更时遇到 “MySQL error 1300” 报错问题解决

.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

执行时报错：

<span style="color: #800080;">2019</span>-<span style="color: #800080;">07</span>-25T11:<span style="color: #800080;">03</span>:<span style="color: #800080;">00</span> Error copying rows from `db_main`.`tb_main` to `db_main`.`_tb_main_new`: <span style="color: #800080;">2019</span>-<span style="color: #800080;">07</span>-25T11:<span style="color: #800080;">03</span>:<span style="color: #800080;">00</span> Copying rows caused a MySQL error <span style="color: #800080;">1300</span><span style="color: #000000;">:
    Level: Warning
     Code: </span><span style="color: #800080;">1300</span><span style="color: #000000;">
  Message: Invalid utf8 character </span><span style="color: #0000ff;">string</span>: <span style="color: #800000;">‘</span><span style="color: #800000;">CE</span><span style="color: #800000;">‘</span>

 

在 percona 官网找到原因： https://jira.percona.com/browse/PT-1528 ，这里直接贴出原因。

<span style="color: #000000;">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=brokenutf8alter
pt-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.</span>

 

解决

用 latin1 字符集代替 utf8

pt-online-schema-change --user=xxxx--password=xxxxxx --host=<span style="color: #800080;">127.0</span>.<span style="color: #800080;">0.1</span> --port=<span style="color: #800080;">3306</span><span style="color: #000000;"> </span>--charset=latin1 D=db_main,t=<span style="color: #000000;">tb_main </span>--alter <span style="color: #800000;">"</span><span style="color: #800000;">modify sGuid varchar(512) DEFAULT NULL COMMENT ‘sGuid‘</span><span style="color: #800000;">"</span><span style="color: #000000;"> </span>--no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=<span style="color: #000000;">none </span>--critical-load=<span style="color: #800000;">"</span><span style="color: #800000;">Threads_running=50</span><span style="color: #800000;">"</span> --max-load=<span style="color: #800000;">"</span><span style="color: #800000;">Threads_running=100</span><span style="color: #800000;">"</span><span style="color: #000000;"> </span>--print --execute

 

pt-osc 变更时遇到 “MySQL error 1300” 报错问题解决

标签：Fix   character   level   host   ken   sql   targe   ble   line