Bug #84654 Cardinality reset to 0 with persistent statistics when AUTO_INCREMENT touched

• Submitted: 24 Jan 2017 22:44
• Modified: 7 Nov 2018 6:47
• Reporter: Sveta Smirnova (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.6, 5.7
• OS: Any
• 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] MySQL Verification Team

Thank you for the bug report.

[7 Nov 2018 6:47] Sveta Smirnova

Not repeatable with 8.0.13