Table names out-of-order between replica and primary

[luisfmcalado]

Overview of the Issue

We were executing a reshard from 1 to 2 shards and before the procedure we executed the ValidateSchemaShard for the first shard and the schema is complaining about differences between the replicas and the primary.

It complains about extra table named and we cannot proceed with the reshard.

We executed the following query in both replicas and primary. The tables show in a different order, still we have a setup for running the procedure locally and the tables show in the same order.

I can't emulate the issue locally.

We are using version 6 of vitess and the engine is MariaDB 10.3.20.

[luisfmcalado]

\cc @systay

[derekperkins]

We have definitely seen many similar false positives on ValidateSchemaShard but haven't taken the time to root cause it.

[aquarapid]

Since information_schema.tables is a view consisting of a join with various schema mysql tables (protected in recent MySQL versions), starting with mysql.tables; I would imagine that the order of the output is in PK order of the mysql.tables table, i.e. depends on the insertion order into that table (i.e. the creation order of the tables) on that MySQL instance. So I guess we need some kind of order by on that query (or at least an optional order by for cases where we care about the order).

[eseokoh]

Hello. I'm using v7.0.1 and have the same case. (it takes almost 3 hours to reproduce)
Since it is hard to reproduce, I'm going to show you my case.

mysql> use active:-@master;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SELECT table_name, table_type, data_length, table_rows FROM information_schema.tables WHERE table_schema = 'vt_active' and table_type = 'BASE TABLE';
+------------+------------+-------------+------------+
| TABLE_NAME | TABLE_TYPE | DATA_LENGTH | TABLE_ROWS |
+------------+------------+-------------+------------+
| readers    | BASE TABLE |       16384 |          0 |
| likes      | BASE TABLE |       16384 |          0 |
| authors    | BASE TABLE |       16384 |          0 |
| books      | BASE TABLE |       16384 |          0 |
+------------+------------+-------------+------------+
4 rows in set (0.00 sec)

mysql> explain SELECT table_name, table_type, data_length, table_rows FROM information_schema.tables WHERE table_schema = 'vt_active' and table_type = 'BASE TABLE';
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys      | key        | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | cat   | NULL       | index  | PRIMARY            | name       | 194     | NULL                    |    1 |   100.00 | Using index |
|  1 | SIMPLE      | sch   | NULL       | eq_ref | PRIMARY,catalog_id | catalog_id | 202     | mysql.cat.id,const      |    1 |   100.00 | Using index |
|  1 | SIMPLE      | tbl   | NULL       | ref    | schema_id,type     | type       | 1       | const                   |  182 |    20.00 | Using where |
|  1 | SIMPLE      | col   | NULL       | eq_ref | PRIMARY            | PRIMARY    | 8       | mysql.tbl.collation_id  |    1 |   100.00 | Using index |
|  1 | SIMPLE      | ts    | NULL       | eq_ref | PRIMARY            | PRIMARY    | 8       | mysql.tbl.tablespace_id |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | stat  | NULL       | eq_ref | PRIMARY            | PRIMARY    | 388     | const,mysql.tbl.name    |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
6 rows in set (0.00 sec)

mysql> use active:-80@master;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SELECT table_name, table_type, data_length, table_rows FROM information_schema.tables WHERE table_schema = 'vt_active' and table_type = 'BASE TABLE';
+------------+------------+-------------+------------+
| TABLE_NAME | TABLE_TYPE | DATA_LENGTH | TABLE_ROWS |
+------------+------------+-------------+------------+
| authors    | BASE TABLE |       16384 |          0 |
| books      | BASE TABLE |       16384 |          0 |
| likes      | BASE TABLE |       16384 |          0 |
| readers    | BASE TABLE |       16384 |          0 |
+------------+------------+-------------+------------+
4 rows in set (0.00 sec)

mysql> explain SELECT table_name, table_type, data_length, table_rows FROM information_schema.tables WHERE table_schema = 'vt_active' and table_type = 'BASE TABLE';
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys      | key        | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | cat   | NULL       | index  | PRIMARY            | name       | 194     | NULL                    |    1 |   100.00 | Using index |
|  1 | SIMPLE      | sch   | NULL       | eq_ref | PRIMARY,catalog_id | catalog_id | 202     | mysql.cat.id,const      |    1 |   100.00 | Using index |
|  1 | SIMPLE      | tbl   | NULL       | ref    | schema_id,type     | schema_id  | 8       | mysql.sch.id            |   67 |    54.01 | Using where |
|  1 | SIMPLE      | col   | NULL       | eq_ref | PRIMARY            | PRIMARY    | 8       | mysql.tbl.collation_id  |    1 |   100.00 | Using index |
|  1 | SIMPLE      | ts    | NULL       | eq_ref | PRIMARY            | PRIMARY    | 8       | mysql.tbl.tablespace_id |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | stat  | NULL       | eq_ref | PRIMARY            | PRIMARY    | 388     | const,mysql.tbl.name    |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
6 rows in set (0.01 sec)

Please notice that the order of tables is different from each order because the query plan is different. This misleads GetSchema and VReplication to failure. I think we need "order by 1".