Cardinality reset to 0 with persistent statistics when AUTO_INCREMENT touched

[Sveta Smirnova]

Description:
Cardinality reset to 0 with persistent statistics when AUTO_INCREMENT added or removed.

Bug is not repeatable with 8.0

How to repeat:
--source include/have_innodb.inc

set global innodb_file_per_table=1;
set global innodb_file_format='Barracuda';

show variables like 'innodb_stats_persistent';

create table t1(id int not null auto_increment primary key, f1 int, f2 int, f3 int, key(f1), key(f2), key(f3)) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_AUTO_RECALC=0 STATS_SAMPLE_PAGES=250 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

insert into t1 values(1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
analyze table t1;

show index from t1;

select * from t1;
show index from t1;

alter table t1 modify id int not null;
show index from t1;

analyze table t1;
show index from t1;

alter table t1 modify id int not null auto_increment;
show index from t1;

Suggested fix:
Do not reset cardinality

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1777