Bug #84654 Cardinality reset to 0 with persistent statistics when AUTO_INCREMENT touched
Submitted: 24 Jan 2017 22:44 Modified: 25 Jan 2017 0:46
Reporter: Sveta Smirnova (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6, 5.7 OS:Any
Assigned to: CPU Architecture:Any

[24 Jan 2017 22:44] 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
[25 Jan 2017 0:46] Miguel Solorzano
Thank you for the bug report.