Public bug reported: When persistent statistics is used for InnoDB it still updates cardinality with values which are incorrect.
Can be related to https://bugs.launchpad.net/percona-server/+bug/1484311 (but exists in 5.7.10) and http://bugs.mysql.com/bug.php?id=75428 How to repeat: Option file: --innodb_stats_auto_recalc=0 --innodb_stats_method=nulls_equal --innodb_stats_on_metadata=0 --innodb_stats_persistent=1 --innodb_stats_persistent_sample_pages=1000 --innodb_stats_sample_pages=8 --innodb_stats_transient_sample_pages=8 Test file: --source include/have_innodb.inc create table sale ( id int primary key auto_increment, customer_id int not null, product_id int not null, sale_time datetime not null, sale_value decimal(10,2) not null, filler varchar(250) not null, key(customer_id, sale_time), key(product_id) ) engine innodb STATS_AUTO_RECALC=0 STATS_SAMPLE_PAGES=1000; insert into sale (customer_id,product_id,sale_time,sale_value,filler) values (1,1,NOW(),100,LPAD('X',250,'X')), (1,2,NOW(),200,LPAD('X',250,'X')), (1,3,NOW(),300,LPAD('X',250,'X')), (2,4,NOW(),100,LPAD('X',250,'X')), (2,5,NOW(),200,LPAD('X',250,'X')), (3,6,NOW(),100,LPAD('X',250,'X')); insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale; insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale; insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale; insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale; insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale; insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale; show indexes from sale; select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale; select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale; analyze table sale; show indexes from sale; insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale where id < 441/5; show indexes from sale; select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale; select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale; insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale where id < 581/2; show indexes from sale; select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale; select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale; Actual result: show indexes from sale; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment sale 0 PRIMARY 1 id A 128 NULL NULL BTREE sale 1 customer_id 1 customer_id A 128 NULL NULL BTREE sale 1 customer_id 2 sale_time A 128 NULL NULL BTREE sale 1 product_id 1 product_id A 128 NULL NULL BTREE select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale; count(distinct customer_id) count(distinct sale_time) count(distinct product_id) 3 2 6 select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale; count(*) count(distinct customer_id) count(distinct customer_id, sale_time) count(distinct product_id) 384 3 6 6 analyze table sale; Table Op Msg_type Msg_text test.sale analyze status OK show indexes from sale; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment sale 0 PRIMARY 1 id A 384 NULL NULL BTREE sale 1 customer_id 1 customer_id A 6 NULL NULL BTREE sale 1 customer_id 2 sale_time A 12 NULL NULL BTREE sale 1 product_id 1 product_id A 12 NULL NULL BTREE insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale where id < 441/5; show indexes from sale; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment sale 0 PRIMARY 1 id A 461 NULL NULL BTREE sale 1 customer_id 1 customer_id A 7 NULL NULL BTREE sale 1 customer_id 2 sale_time A 14 NULL NULL BTREE sale 1 product_id 1 product_id A 14 NULL NULL BTREE select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale; count(distinct customer_id) count(distinct sale_time) count(distinct product_id) 3 3 6 select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale; count(*) count(distinct customer_id) count(distinct customer_id, sale_time) count(distinct product_id) 461 3 9 6 insert into sale (customer_id, product_id, sale_time, sale_value, filler) select customer_id, product_id, NOW(), sale_value, filler from sale where id < 581/2; show indexes from sale; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment sale 0 PRIMARY 1 id A 694 NULL NULL BTREE sale 1 customer_id 1 customer_id A 10 NULL NULL BTREE sale 1 customer_id 2 sale_time A 21 NULL NULL BTREE sale 1 product_id 1 product_id A 21 NULL NULL BTREE select count(distinct customer_id), count(distinct sale_time), count(distinct product_id) from sale; count(distinct customer_id) count(distinct sale_time) count(distinct product_id) 3 4 6 select count(*), count(distinct customer_id), count(distinct customer_id, sale_time), count(distinct product_id) from sale; count(*) count(distinct customer_id) count(distinct customer_id, sale_time) count(distinct product_id) 694 3 12 6 Expected result: all calls of show indexes from sale; (except first one) should return show indexes from sale; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment sale 0 PRIMARY 1 id A 384 NULL NULL BTREE sale 1 customer_id 1 customer_id A 6 NULL NULL BTREE sale 1 customer_id 2 sale_time A 12 NULL NULL BTREE sale 1 product_id 1 product_id A 12 NULL NULL BTREE ** Affects: mysql-server Importance: Unknown Status: Unknown ** Affects: percona-server Importance: Undecided Status: Confirmed ** Affects: percona-server/5.6 Importance: Undecided Status: Confirmed ** Affects: percona-server/5.7 Importance: Undecided Status: Confirmed ** Tags: i64760 ** Also affects: percona-server/5.6 Importance: Undecided Status: Confirmed ** Also affects: percona-server/5.7 Importance: Undecided Status: New ** Bug watch added: MySQL Bug System #80178 http://bugs.mysql.com/bug.php?id=80178 ** Also affects: mysql-server via http://bugs.mysql.com/bug.php?id=80178 Importance: Unknown Status: Unknown ** Changed in: percona-server/5.7 Status: New => Confirmed -- You received this bug notification because you are a member of Ubuntu Server/Client Support Team, which is subscribed to MySQL. Matching subscriptions: Ubuntu Server/Client Support Team https://bugs.launchpad.net/bugs/1538765 Title: Wrong statistics for InnoDB if persistent statistics used To manage notifications about this bug go to: https://bugs.launchpad.net/mysql-server/+bug/1538765/+subscriptions -- Mailing list: https://launchpad.net/~enterprise-support Post to : [email protected] Unsubscribe : https://launchpad.net/~enterprise-support More help : https://help.launchpad.net/ListHelp
