vitess: Bug Report: OnlineDDL PK conversion results in table scans
Overview of the Issue
When changing the character set and/or collation of a PK column, OnlineDDL creates a VReplication binlog filter which uses the from (original) charset rather than the to charset. This results in not being able to use the primary key on the shadow table when applying replicated events and can cause vplayer stalls (see https://github.com/vitessio/vitess/issues/15974).
Reproduction Steps
git checkout main && make build
pushd examples/local
./101_initial_cluster.sh
primaryuid=$(vtctldclient GetTablets --keyspace commerce --tablet-type primary --shard "0" | awk '{print $1}' | cut -d- -f2 | bc)
mysql -e "CREATE TABLE t1 (id varchar(128), id2 varchar(32) NOT NULL, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, colstuff varchar(64) NOT NULL, PRIMARY KEY (id,id2))"
for _ in {1..1000}; do mysql commerce -e "insert into t1 (id, id2, colstuff) values ('${RANDOM}${RANDOM}${RANDOM}_id', '${RANDOM}${RANDOM}${RANDOM}_id2', '${RANDOM}${RANDOM}${RANDOM}_colstuff')"; done
command mysql -u root --socket ${VTDATAROOT}/vt_0000000${primaryuid}/mysql.sock -e "set @@global.general_log=ON"
# Change the charset of a PK column.
uuid=$(vtctldclient ApplySchema --ddl-strategy "vitess --postpone-completion" --sql "ALTER TABLE t1 MODIFY id varchar(128) character set ascii" commerce)
sleep 5
command mysql -u root --socket ${VTDATAROOT}/vt_0000000${primaryuid}/mysql.sock -e "select id, workflow, source, pos from _vt.vreplication where workflow = '${uuid}'\G"
command mysql -u root --socket ${VTDATAROOT}/vt_0000000${primaryuid}/mysql.sock -e "update vt_commerce.t1 set colstuff = 'foobar' order by rand() limit 20"
sleep 5
grep _vt_vrp_ ${VTDATAROOT}/vt_0000000${primaryuid}/data/$(hostname -s).log | grep -i update
Example output:
❯ command mysql -u root --socket ${VTDATAROOT}/vt_0000000${primaryuid}/mysql.sock -e "select id, workflow, source, pos from _vt.vreplication where workflow = '${uuid}'\G"
*************************** 1. row ***************************
id: 1
workflow: 7f8b11f8_1dc8_11ef_a47d_920702940ee1
source: keyspace:"commerce" shard:"0" filter:{rules:{match:"_vt_vrp_7f8b11f81dc811efa47d920702940ee1_20240529103405_" filter:"select convert(`id` using utf8mb4) as `id`, `id2` as `id2`, `updated_at` as `updated_at`, `created_at` as `created_at`, `colstuff` as `colstuff` from `t1`" convert_charset:{key:"id" value:{from_charset:"utf8mb4" to_charset:"ascii"}} source_unique_key_columns:"id,id2" target_unique_key_columns:"id,id2" source_unique_key_target_columns:"id,id2" force_unique_key:"PRIMARY"}}
pos: MySQL56/72d489da-1dc8-11ef-8b36-4ae7a99a0651:1-1060
❯ command mysql -u root --socket ${VTDATAROOT}/vt_0000000${primaryuid}/mysql.sock vt_commerce -e "explain update _vt_vrp_7f8b11f81dc811efa47d920702940ee1_20240529103405_ set updated_at='2024-05-29 10:34:09', created_at='2024-05-29 10:33:52', colstuff='foobar' where id=(convert('12927272832499_id' using utf8mb4)) and id2='3024566117854_id2'\G"
*************************** 1. row ***************************
id: 1
select_type: UPDATE
table: _vt_vrp_7f8b11f81dc811efa47d920702940ee1_20240529103405_
partitions: NULL
type: index
possible_keys: NULL
key: PRIMARY
key_len: 260
ref: NULL
rows: 1000
filtered: 100.00
Extra: Using where
Binary Version
❯ vtgate --version
vtgate version Version: 20.0.0-SNAPSHOT (Git revision c666b1442b216413bd36f31926489cc0581af511 branch 'vplayer_batch_trx_timeout') built on Wed May 29 10:36:27 EDT 2024 by matt@pslord.local using go1.22.3 darwin/arm64
Operating System and Environment details
N/A
Log Fragments
N/A
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Comments: 22 (22 by maintainers)
New findings!
Consider this table:
Because
c2isascii,vstreamerusesconvert(c2 using utf8mb4as follows:Let’s
EXPLAINthis:Using filesortis obviously bad, and the copy performance will tank.But, why is this query tanking? Didn’t we ask it to
force index (c1c2_uidx) order by c1, c2?This is where the fun starts. The problem is that we’ve aliased
convert(c2 using utf8mb4) as c2. To prove it, see what happens if we do not make that alias:So, in the aliased variation,
order by c1, c2really meansorder by c1, convert(c2 using utf8mb4), which then cannot use the index.However, that aliasing is really useful, as it hides away the complexity of the conversion. I was thinking we could alias into something else, to then re-alias in code. However, I realized this is easily solved like so:
Qualifying the columns with the table name un-aliases them and uses their original values, and MySQL is then happy to use the index.
Which means the fix is to merely qualify the sorting columns with the table name.
WDYT?
I think you’re looking at the results of selecting those rows/values? I’ll have to look. What you see on SELECT or generally on “display” is determined by the connection/client charset:
In any event, I’m fine moving this to a VReplication bug and I’ll put it in the prioritized queue. I AGREE, it gets very complicated and hairy. That’s why I’m trying to look at it with fresh eyes rather than building on determinations/assumptions made years ago (which may of course turn out to be right/true).
Thank you, assigned myself to look into it.