Description:
This is coming from https://jira.mariadb.org/browse/MDEV-10790.
The issue is similar to http://bugs.mysql.com/bug.php?id=82968 , but this time, it's about index cardinalities. Suggested fix for the above bug doesn't fix this one.
The bug is a race condition in updating index cardinalities. Cardinalities are reset and then updated, so an unlucky query may see a very wrong index cardinality values.
How to repeat:
* Apply the attached patch. The patch adds a few debug printouts, and a way to stop index cardinality update at an "interesting" point.
* Run the commands (my comments start with ##)
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.15-debug Source distribution
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> use test;
Database changed
MySQL [test]> create table ten(a int) engine=myisam;
Query OK, 0 rows affected (0.04 sec)
MySQL [test]> insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
MySQL [test]>
MySQL [test]> create table one_k(a int) engine=myisam;
Query OK, 0 rows affected (0.03 sec)
MySQL [test]> insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
Query OK, 1000 rows affected (0.01 sec)
Records: 1000 Duplicates: 0 Warnings: 0
MySQL [test]>
MySQL [test]> create table t0 (a int) engine=myisam;
Query OK, 0 rows affected (0.02 sec)
MySQL [test]> insert into t0 values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
MySQL [test]>
MySQL [test]> create table t1 (
-> pk int primary key,
-> col1 int not null,
-> col2 int not null,
-> key(col1)
-> ) engine= innodb;
Query OK, 0 rows affected (0.25 sec)
MySQL [test]>
MySQL [test]> insert into t1 select a,a,a from one_k;
Query OK, 1000 rows affected (0.23 sec)
Records: 1000 Duplicates: 0 Warnings: 0
MySQL [test]> analyze table t1;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.08 sec)
MySQL [test]> explain select * from t0, t1 where t1.col1=t0.a;
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | t0 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
| 1 | SIMPLE | t1 | NULL | ref | col1 | col1 | 4 | test.t0.a | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)
## ^^ Observe t1.rows=1 which is a precise index statistics. Good so far.
## Now, make innodb's statistics update stop in the middle of update:
MySQL [test]> system touch /tmp/dict_stats_analyze_index_must_stop
## Do something to cause auto statistics update (we are running
## with innodb_stats_auto_recalc=ON, which is the default).
MySQL [test]> insert into t1 select 5000+pk,5000+col1, 5000+col2 from t1 limit 2000;
Query OK, 1000 rows affected (0.38 sec)
Records: 1000 Duplicates: 0 Warnings: 0
## OPTIONAL: You can check the server stderr and see this line there:
## AAA: dict_stats_analyze_index called dict_stats_empty_index for col1
## Now, the test query again:
MySQL [test]> flush tables;
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> explain select * from t0, t1 where t1.col1=t0.a;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | t0 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 1 | SIMPLE | t1 | NULL | ALL | col1 | NULL | NULL | NULL | 2000 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)
## Note rows=2000 ! The real value is rows=1, both before and after the update.
## The value one is seeing is about equal to table_rows
## Let the stats calculation finish:
MySQL [test]> system rm /tmp/dict_stats_analyze_index_must_stop
# Wait a few seconds, and check again:
MySQL [test]> flush tables;
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> explain select * from t0, t1 where t1.col1=t0.a;
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | t0 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
| 1 | SIMPLE | t1 | NULL | ref | col1 | col1 | 4 | test.t0.a | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)
# Everything's back to normal.
Suggested fix:
None yet. Index statistics should be updated atomically, somehow?
