error 1366 on invalid character

[cenkore]

I believe i have run into the same issue #290 ,But my table is gbk-encoded and contains utf8 characters.

For reproduce it , I add postpone file ,and update a row after row-copy complete .

Actually the column of name's value is 涪陵北.

v1.0.40

table structure

CREATE TABLE `recur_gbk` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(512) DEFAULT NULL,
  `addcol` varchar(255) DEFAULT NULL COMMENT '添加普通列测试',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=gbk

gh-ost command

gh-ost --host=10.28.77.43 --database="ghostdb" --table="recur_gbk" --debug --alter="ADD COLUMN addcol1 VARCHAR(255) DEFAULT NULL COMMENT '添加普通列测试'" --concurrent-rowcount --conf="/usr/local/gh-ost.cnf" --port=55944 --execute --postpone-cut-over-flag-file="/tmp/r.postpone" --initially-drop-old-table

log

2018-01-02 19:03:44 INFO starting gh-ost 1.0.40
2018-01-02 19:03:44 INFO Migrating `ghostdb`.`recur_gbk`
2018-01-02 19:03:44 INFO connection validated on 10.28.77.43:55944
2018-01-02 19:03:44 INFO User has ALL privileges
2018-01-02 19:03:44 INFO binary logs validated on 10.28.77.43:55944
2018-01-02 19:03:44 INFO Restarting replication on 10.28.77.43:55944 to make sure binlog settings apply to replication thread
2018-01-02 19:03:44 DEBUG Replication restarted
2018-01-02 19:03:44 INFO Inspector initiated on SVR14883DE630:55944, version 5.7.18-ctrip-log
2018-01-02 19:03:44 INFO Table found. Engine=InnoDB
2018-01-02 19:03:44 DEBUG Estimated number of rows via STATUS: 19
2018-01-02 19:03:44 DEBUG Validated no foreign keys exist on table
2018-01-02 19:03:44 DEBUG Validated no triggers exist on table
2018-01-02 19:03:44 INFO Estimated number of rows via EXPLAIN: 19
2018-01-02 19:03:44 DEBUG Potential unique keys in recur_gbk: [PRIMARY (auto_increment): [id]; has nullable: false]
2018-01-02 19:03:44 INFO Recursively searching for replication master
2018-01-02 19:03:44 DEBUG Looking for master on 10.28.77.43:55944
2018-01-02 19:03:44 DEBUG Master of 10.28.77.43:55944 is 10.28.77.42:55944
2018-01-02 19:03:44 DEBUG Looking for master on 10.28.77.42:55944
2018-01-02 19:03:44 INFO Master found to be 10.28.77.42:55944
2018-01-02 19:03:44 INFO log_slave_updates validated on 10.28.77.43:55944
2018-01-02 19:03:44 INFO connection validated on 10.28.77.43:55944
2018-01-02 19:03:44 DEBUG Streamer binlog coordinates: SVR14883DE630-bin.001903:55888
2018/01/02 19:03:44 binlogsyncer.go:76: [info] create BinlogSyncer with config 10.28.77.43:55944[replica-server-id:99999]
2018-01-02 19:03:44 INFO Connecting binlog streamer at SVR14883DE630-bin.001903:55888
2018/01/02 19:03:44 binlogsyncer.go:242: [info] begin to sync binlog from position (SVR14883DE630-bin.001903, 55888)
2018/01/02 19:03:44 binlogsyncer.go:135: [info] register slave for master server 10.28.77.43:55944
2018/01/02 19:03:44 binlogsyncer.go:569: [info] rotate to (SVR14883DE630-bin.001903, 55888)
2018-01-02 19:03:44 DEBUG Beginning streaming
2018-01-02 19:03:44 INFO rotate to next log name: SVR14883DE630-bin.001903
2018-01-02 19:03:44 INFO connection validated on 10.28.77.42:55944
2018-01-02 19:03:44 INFO connection validated on 10.28.77.42:55944
2018-01-02 19:03:44 INFO will use time_zone='SYSTEM' on applier
2018-01-02 19:03:44 INFO Examining table structure on applier
2018-01-02 19:03:44 INFO Applier initiated on SVR14306DE630:55944, version 5.7.18-ctrip-log
2018-01-02 19:03:44 INFO Droppping table `ghostdb`.`_recur_gbk_del`
2018-01-02 19:03:44 INFO Table dropped
2018-01-02 19:03:44 INFO Droppping table `ghostdb`.`_recur_gbk_ghc`
2018-01-02 19:03:44 INFO Table dropped
2018-01-02 19:03:44 INFO Creating changelog table `ghostdb`.`_recur_gbk_ghc`
2018-01-02 19:03:44 INFO Changelog table created
2018-01-02 19:03:44 INFO Creating ghost table `ghostdb`.`_recur_gbk_gho`
2018-01-02 19:03:44 INFO Ghost table created
2018-01-02 19:03:44 INFO Altering ghost table `ghostdb`.`_recur_gbk_gho`
2018-01-02 19:03:44 DEBUG ALTER statement: alter /* gh-ost */ table `ghostdb`.`_recur_gbk_gho` ADD COLUMN addcol1 VARCHAR(255) DEFAULT NULL COMMENT '添加普通列测试'
2018-01-02 19:03:44 INFO Ghost table altered
2018-01-02 19:03:44 INFO Waiting for ghost table to be migrated. Current lag is 0s
2018-01-02 19:03:44 INFO Intercepted changelog state GhostTableMigrated
2018-01-02 19:03:44 INFO Handled changelog state GhostTableMigrated
2018-01-02 19:03:44 DEBUG ghost table migrated
2018-01-02 19:03:44 DEBUG Potential unique keys in _recur_gbk_gho: [PRIMARY (auto_increment): [id]; has nullable: false]
2018-01-02 19:03:44 INFO Chosen shared unique key is PRIMARY
2018-01-02 19:03:44 INFO Shared columns are id,Name,addcol
2018-01-02 19:03:44 INFO Listening on unix socket file: /tmp/gh-ost.ghostdb.recur_gbk.sock
2018-01-02 19:03:44 DEBUG Reading migration range according to key: PRIMARY
2018-01-02 19:03:44 INFO Migration min values: [1]
2018-01-02 19:03:44 DEBUG Reading migration range according to key: PRIMARY
2018-01-02 19:03:44 INFO Migration max values: [31]
2018-01-02 19:03:44 INFO Waiting for first throttle metrics to be collected
2018-01-02 19:03:44 INFO First throttle metrics collected
2018-01-02 19:03:44 DEBUG Operating until row copy is complete
Copy: 19/19 100.0%; Applied: 0; Backlog: 0/1000; Time: 1m0s(total), 1s(copy); streamer: SVR14883DE630-bin.001903:315958; State: postponing cut-over; ETA: due
2018-01-02 19:04:57 ERROR Error 1366: Incorrect string value: '\xB8\xA2\xC1\xEA\xB1\xB1' for column 'Name' at row 1; query=
 			update /* gh-ost `ghostdb`.`_recur_gbk_gho` */
 					`ghostdb`.`_recur_gbk_gho`
				set
					`id`=?, `Name`=?, `addcol`=?
				where
 					((`id` = ?))
 		; args=[1 ¸¢ª±± 1 1]
2018-01-02 19:04:58 ERROR Error 1366: Incorrect string value: '\xB8\xA2\xC1\xEA\xB1\xB1' for column 'Name' at row 1; query=
 			update /* gh-ost `ghostdb`.`_recur_gbk_gho` */
 					`ghostdb`.`_recur_gbk_gho`
				set
					`id`=?, `Name`=?, `addcol`=?
				where
 					((`id` = ?))
 		; args=[1 ¸¢ª±± 1 1]

Thank you!

[shlomi-noach]

How do you mean "pick the appropriate encoding"?

[cenkore]

When decode binlog file should choose the respective character encoding for each column.

[shlomi-noach]

Something is strange here because I can't see the message I was replying to. Did you just delete it? Please avoid doing that; I'm multitasking on many issues on many projects and this causes me to lose context. If you can re-port the question to which I replied to, that would be appreciated.

[cenkore]

Sorry for that .

I thought there is no way to pick a appropriate encoding for multi-columns with different character encoding .

Will it help that decode binlog like pymysqlreplication ?

[shlomi-noach]

I'm under the impression we're sidetracking onto something that isn't a problem. gh-ost does recognize column charsets, per column ; it doesn't matter if the table is of one charset and columns are of another charset. It more a matter of which charsets are supportd by gh-ost.

[shlomi-noach]

gbk is supported via #560