Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 10.11, 11.4, 11.6(EOL), 11.7(EOL)
-
None
Description
InnoDB persistent statistics behaves quite unexpectedly upon ALTER .. COPY (and differently non-copying ALTER).
Below is an original report demonstrating it via a vector key on 11.7. See comments for a generic test case without vectors, applicable to older versions
With vector key:
|
7fce19bd215ac0671855044520092aa4210049d1 |
MariaDB [test]> create or replace table t (pk int primary key, a int, v vector(1) not null, vector(v)) engine=InnoDB; |
Query OK, 0 rows affected (0.110 sec) |
|
|
MariaDB [test]> select table_name from mysql.innodb_table_stats where database_name = 'test'; |
+------------+ |
| table_name |
|
+------------+ |
| t |
|
| t#i#01 |
|
+------------+ |
2 rows in set (0.001 sec) |
|
|
MariaDB [test]> select table_name, index_name, stat_name from mysql.innodb_index_stats where database_name = 'test'; |
+------------+-----------------+--------------+ |
| table_name | index_name | stat_name |
|
+------------+-----------------+--------------+ |
| t | PRIMARY | n_diff_pfx01 | |
| t | PRIMARY | n_leaf_pages | |
| t | PRIMARY | size | |
| t#i#01 | GEN_CLUST_INDEX | n_diff_pfx01 |
|
| t#i#01 | GEN_CLUST_INDEX | n_leaf_pages |
|
| t#i#01 | GEN_CLUST_INDEX | size | |
| t#i#01 | layer | n_diff_pfx01 |
|
| t#i#01 | layer | n_diff_pfx02 |
|
| t#i#01 | layer | n_leaf_pages |
|
| t#i#01 | layer | size | |
| t#i#01 | tref | n_diff_pfx01 |
|
| t#i#01 | tref | n_leaf_pages |
|
| t#i#01 | tref | size | |
+------------+-----------------+--------------+ |
13 rows in set (0.001 sec) |
MariaDB [test]> alter table t add index (a); |
Query OK, 0 rows affected (0.140 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select table_name from mysql.innodb_table_stats where database_name = 'test'; |
Empty set (0.001 sec) |
|
|
MariaDB [test]> select table_name, index_name, stat_name from mysql.innodb_index_stats where database_name = 'test'; |
Empty set (0.001 sec) |
I suppose it is expected that separate records are created for the index pseudo-table t#i#01; however, it is unclear why an innocent operation on the table (in this case an addition of another index, but also removal of any of the indexes) clears all table/index statistics.
It doesn't happen without vector key in otherwise identical circumstances; instead, statistical records for the new index are added, as would be reasonable to expect:
MariaDB [test]> create or replace table t (pk int primary key, a int, v vector(1) not null) engine=InnoDB; |
Query OK, 0 rows affected (0.080 sec) |
|
|
MariaDB [test]> select table_name from mysql.innodb_table_stats where database_name = 'test'; |
+------------+ |
| table_name |
|
+------------+ |
| t |
|
+------------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> select table_name, index_name, stat_name from mysql.innodb_index_stats where database_name = 'test'; |
+------------+------------+--------------+ |
| table_name | index_name | stat_name |
|
+------------+------------+--------------+ |
| t | PRIMARY | n_diff_pfx01 | |
| t | PRIMARY | n_leaf_pages | |
| t | PRIMARY | size | |
+------------+------------+--------------+ |
3 rows in set (0.001 sec) |
MariaDB [test]> alter table t add index (a); |
Query OK, 0 rows affected (0.043 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> select table_name from mysql.innodb_table_stats where database_name = 'test'; |
+------------+ |
| table_name |
|
+------------+ |
| t |
|
+------------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> select table_name, index_name, stat_name from mysql.innodb_index_stats where database_name = 'test'; |
+------------+------------+--------------+ |
| table_name | index_name | stat_name |
|
+------------+------------+--------------+ |
| t | PRIMARY | n_diff_pfx01 | |
| t | PRIMARY | n_leaf_pages | |
| t | PRIMARY | size | |
| t | a | n_diff_pfx01 |
|
| t | a | n_diff_pfx02 |
|
| t | a | n_leaf_pages |
|
| t | a | size | |
+------------+------------+--------------+ |
7 rows in set (0.001 sec) |
Attachments
Activity
Indeed, thanks.
I've updated the description a little bit to avoid too much confusion, but kept there the original test case with the vector key so that it was clear what these comments refer to.
Here is a test case without vectors:
--source include/have_innodb.inc
|
|
|
create or replace table t (pk int primary key, a int) engine=InnoDB; |
select table_name from mysql.innodb_table_stats where database_name = 'test'; |
select table_name, index_name, stat_name from mysql.innodb_index_stats where database_name = 'test'; |
alter table t add index (a), algorithm=copy; |
select table_name from mysql.innodb_table_stats where database_name = 'test'; |
select table_name, index_name, stat_name from mysql.innodb_index_stats where database_name = 'test'; |
|
|
# Cleanup
|
drop table t; |
|
10.6 b9f9d804f2f6bedc79df2e38ab6a60933684ca24 |
select table_name from mysql.innodb_table_stats where database_name = 'test'; |
table_name
|
t
|
select table_name, index_name, stat_name from mysql.innodb_index_stats where database_name = 'test'; |
table_name index_name stat_name
|
t PRIMARY n_diff_pfx01 |
t PRIMARY n_leaf_pages |
t PRIMARY size |
alter table t add index (a), algorithm=copy; |
select table_name from mysql.innodb_table_stats where database_name = 'test'; |
table_name
|
select table_name, index_name, stat_name from mysql.innodb_index_stats where database_name = 'test'; |
table_name index_name stat_name
|
It doesn't seem to be related to vector, alter table ... algorithm=copy loses stats as well.