[MDEV-35251] ALTER .. COPY removes and doesn't re-create InnoDB table/index statistics - Jira

Elena Stepanova created issue - 2024-10-24 19:54

Elena Stepanova made changes - 2024-10-24 19:54

Field	Original Value	New Value
Link		This issue is caused by ~~MDEV-34939~~ [ ~~MDEV-34939~~ ]

Elena Stepanova made changes - 2024-10-24 19:55

Description

InnoDB persistent statistics on a table with a vector key behaves quite unexpectedly (and differently from tables without a vector key). Compare:

With vector key:
{code:sql|title=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)
{code}
{code:sql}
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)
{code}

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, upon ALTER statistical records for the new index are added, as would be reasonable to expect:
{code:sql}
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)
{code}
{code:sql}
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)
{code}

InnoDB persistent statistics on a table with a vector key behaves quite unexpectedly (and differently from tables without a vector key). Compare:

With vector key:
{code:sql|title=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)
{code}
{code:sql}
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)
{code}

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:
{code:sql}
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)
{code}
{code:sql}
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)
{code}

Sergey Vojtovich added a comment - 2024-11-07 15:32 - edited

It doesn't seem to be related to vector, alter table ... algorithm=copy loses stats as well.

Sergey Vojtovich added a comment - 2024-11-07 15:32 - edited It doesn't seem to be related to vector, alter table ... algorithm=copy loses stats as well.

Elena Stepanova added a comment - 2024-11-07 15:52 - edited

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

Elena Stepanova added a comment - 2024-11-07 15:52 - edited 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

Elena Stepanova made changes - 2024-11-07 15:52

Component/s		Data Definition - Alter Table [ 10114 ]
Component/s	Vector search [ 20205 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.11 [ 27614 ]
Fix Version/s		11.4 [ 29301 ]
Fix Version/s		11.6 [ 29515 ]
Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.11 [ 27614 ]
Affects Version/s		11.4 [ 29301 ]
Affects Version/s		11.6 [ 29515 ]
Affects Version/s		11.7 [ 29815 ]
Affects Version/s	N/A [ 14700 ]
Summary	Operations on table with vector key remove InnoDB table/index statistics	ALTER .. COPY removes and doesn't re-create InnoDB table/index statistics

Elena Stepanova made changes - 2024-11-07 15:52

Assignee

Sergei Golubchik [ serg ]

Marko Mäkelä [ marko ]

Elena Stepanova made changes - 2024-11-07 15:53

Description

InnoDB persistent statistics on a table with a vector key behaves quite unexpectedly (and differently from tables without a vector key). Compare:

With vector key:
{code:sql|title=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)
{code}
{code:sql}
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)
{code}

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:
{code:sql}
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)
{code}
{code:sql}
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)
{code}

InnoDB persistent statistics behaves quite unexpectedly upon ALTER .. COPY (and differently non-copying ALTER). Compare:

With vector key:
{code:sql|title=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)
{code}
{code:sql}
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)
{code}

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:
{code:sql}
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)
{code}
{code:sql}
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)
{code}

Elena Stepanova made changes - 2024-11-07 15:56

Description

InnoDB persistent statistics behaves quite unexpectedly upon ALTER .. COPY (and differently non-copying ALTER). Compare:

With vector key:
{code:sql|title=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)
{code}
{code:sql}
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)
{code}

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:
{code:sql}
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)
{code}
{code:sql}
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)
{code}

InnoDB persistent statistics behaves quite unexpectedly upon ALTER .. COPY (and differently non-copying ALTER).
Below is an original test case 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:
{code:sql|title=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)
{code}
{code:sql}
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)
{code}

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:
{code:sql}
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)
{code}
{code:sql}
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)
{code}

Elena Stepanova made changes - 2024-11-07 15:56

Description

InnoDB persistent statistics behaves quite unexpectedly upon ALTER .. COPY (and differently non-copying ALTER).
Below is an original test case 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:
{code:sql|title=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)
{code}
{code:sql}
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)
{code}

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:
{code:sql}
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)
{code}
{code:sql}
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)
{code}

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:
{code:sql|title=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)
{code}
{code:sql}
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)
{code}

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:
{code:sql}
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)
{code}
{code:sql}
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)
{code}

Elena Stepanova made changes - 2024-11-12 22:54

Link

This issue is caused by ~~MDEV-34939~~ [ ~~MDEV-34939~~ ]

Julien Fritsch made changes - 2024-11-22 14:56

Fix Version/s

11.6(EOL) [ 29515 ]

Julien Fritsch made changes - 2025-02-14 11:39

Fix Version/s

11.7(EOL) [ 29815 ]

MariaDB Server

ALTER .. COPY removes and doesn't re-create InnoDB table/index statistics

Details

Description

Attachments

Activity

People

Dates

Git Integration