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)

Most upvoted comments

New findings!

Consider this table:

drop table if exists tbl;
CREATE TABLE `tbl` (
`c1` varchar(12) NOT NULL DEFAULT '',
`c2` varchar(12) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
`ts` datetime DEFAULT current_timestamp(),
UNIQUE KEY `c1c2_uidx` (`c1`, `c2`)
) ENGINE InnoDB,
CHARSET utf8mb4,
COLLATE utf8mb4_unicode_ci;

insert into tbl (c1, c2) values (left(sha1(rand()),12), left(sha1(rand()),12));
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;
insert into tbl (c1, c2) select left(sha1(rand()),12), left(sha1(rand()),12) from tbl;

Because c2 is ascii, vstreamer uses convert(c2 using utf8mb4 as follows:

select /*+ MAX_EXECUTION_TIME(3600000) */ c1, convert(c2 using utf8mb4) as c2, ts from tbl force index (`c1c2_uidx`) order by c1, c2;

Let’s EXPLAIN this:

mysql> explain select /*+ MAX_EXECUTION_TIME(3600000) */ c1, convert(c2 using utf8mb4) as c2, ts from tbl force index (`c1c2_uidx`) order by c1, c2;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+
|  1 | SIMPLE      | tbl   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1910748 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+----------------+

Using filesort is 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:

mysql> explain select /*+ MAX_EXECUTION_TIME(3600000) */ c1, convert(c2 using utf8mb4), ts from tbl force index (`c1c2_uidx`) order by c1, c2;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | tbl   | NULL       | index | NULL          | c1c2_uidx | 64      | NULL | 950012 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------+

So, in the aliased variation, order by c1, c2 really means order 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:

mysql> explain select /*+ MAX_EXECUTION_TIME(3600000) */ c1, convert(c2 using utf8mb4) as c2, ts from tbl force index (`c1c2_uidx`) order by tbl.c1, tbl.c2;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | tbl   | NULL       | index | NULL          | c1c2_uidx | 64      | NULL | 1910748 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+---------+----------+-------+

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:

mysql> select * from chartest;
+----+------------+
| id | name       |
+----+------------+
|  1 | matt lord  |
|  2 | mått lord  |
|  3 | ⭐          |
+----+------------+
3 rows in set (0.00 sec)

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from chartest;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | matt lord |
|  2 | m�tt lord  |
|  3 | ?         |
+----+-----------+
3 rows in set (0.00 sec)

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.