[MDEV-18396] InnoDB table/index stats are incorrect if DISABLE/ENABLE keys is used Created: 2019-01-28  Updated: 2019-01-31  Resolved: 2019-01-31

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Jan Lindström (Inactive) Assignee: Jan Lindström (Inactive)
Resolution: Not a Bug Votes: 0
Labels: None

Attachments: File innodb_index_cardinality.opt     File innodb_index_cardinality.test    
Issue Links:
Problem/Incident
causes MDEV-18226 Index cardinality is not synced acros... Closed

 Description   

I did not test earlier releases but they could be also effected. I have attached mtr test case.

If ENABLE/DISABLE keys clause is commented I see correct results (note that mysqldump will include those ENABLE/DISABLE keys clause by default):

jan@jan-laptop-asus:~/mysql/10.2-sst/mysql-test$ ./mtr innodb_index_cardinality
 
Logging: ./mtr  innodb_index_cardinality
 
vardir: /home/jan/mysql/10.2-sst/mysql-test/var
 
Checking leftover processes...
 
Removing old var directory...
 
Creating var directory '/home/jan/mysql/10.2-sst/mysql-test/var'...
 
Checking supported features...
 
MariaDB Version 10.2.22-MariaDB-debug
 
 - SSL connections supported
 
 - binaries are debug compiled
 
Collecting tests...
 
Installing system database...
 
 
 
==============================================================================
 
 
 
TEST                                      RESULT   TIME (ms) or COMMENT
 
--------------------------------------------------------------------------
 
 
 
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
 
create database cardtest02;
 
use cardtest02;
 
DROP TABLE IF EXISTS `cardtest_tbl`;
 
Warnings:
 
Note	1051	Unknown table 'cardtest02.cardtest_tbl'
 
CREATE TABLE `cardtest_tbl` (
 
`id` bigint(18) NOT NULL AUTO_INCREMENT,
 
`course` bigint(18) NOT NULL,
 
`name` varchar(200) DEFAULT NULL,
 
`intro` longtext DEFAULT NULL,
 
`introformat` smallint(4) DEFAULT 0,
 
`timecreated` bigint(18) NOT NULL,
 
`timemodified` bigint(18) NOT NULL,
 
`grademethod` varchar(255) DEFAULT NULL,
 
`grade` decimal(10,5) DEFAULT NULL,
 
`updategradeoncompletion` tinyint(2) DEFAULT 1,
 
`competencyoncompletion` varchar(255) DEFAULT NULL,
 
`evaluationmethod` varchar(255) DEFAULT NULL,
 
`completionmethod` varchar(255) DEFAULT NULL,
 
`alloweddates` varchar(1024) DEFAULT NULL,
 
`allowedslots` varchar(1024) DEFAULT NULL,
 
`permissions` smallint(4) DEFAULT NULL,
 
`notifications` varchar(1024) DEFAULT NULL,
 
`type` varchar(255) DEFAULT NULL,
 
PRIMARY KEY (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=1324 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED COMMENT='assessment table retrofitted from MySQL';
 
# Inserting data...
 
use cardtest02;
 
show indexes from cardtest_tbl;
 
Table	cardtest_tbl
 
Non_unique	0
 
Key_name	PRIMARY
 
Seq_in_index	1
 
Column_name	id
 
Collation	A
 
Cardinality	301
 
Sub_part	NULL
 
Packed	NULL
 
Null	
Index_type	BTREE
 
Comment	
Index_comment	
select * from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl';
 
TABLE_CATALOG	def
 
TABLE_SCHEMA	cardtest02
 
TABLE_NAME	cardtest_tbl
 
TABLE_TYPE	BASE TABLE
 
ENGINE	InnoDB
 
VERSION	10
 
ROW_FORMAT	Compressed
 
TABLE_ROWS	301
 
AVG_ROW_LENGTH	27
 
DATA_LENGTH	8192
 
MAX_DATA_LENGTH	0
 
INDEX_LENGTH	0
 
DATA_FREE	0
 
AUTO_INCREMENT	1324
 
CREATE_TIME	2019-01-28 16:11:41
 
UPDATE_TIME	2019-01-28 16:11:41
 
CHECK_TIME	NULL
 
TABLE_COLLATION	utf8_general_ci
 
CHECKSUM	NULL
 
CREATE_OPTIONS	row_format=COMPRESSED
 
TABLE_COMMENT	assessment table retrofitted from MySQL
 
select count(*) from cardtest_tbl;
 
count(*)	301
 
use test;
 
drop database cardtest02;
 
innodb.innodb_index_cardinality 'innodb' [ pass ]   5096
 
--------------------------------------------------------------------------
 
The servers were restarted 0 times
 
Spent 5.096 of 10 seconds executing testcases
 
 
 
Completed: All 1 tests were successful.

If I remove the comment from ENABLE/DISABLE keys clause, I see incorrect results on index cardinality, table_rows and avg_row_length.

jan@jan-laptop-asus:~/mysql/10.2-sst/mysql-test$ ./mtr innodb_index_cardinality
 
Logging: ./mtr  innodb_index_cardinality
 
vardir: /home/jan/mysql/10.2-sst/mysql-test/var
 
Checking leftover processes...
 
Removing old var directory...
 
Creating var directory '/home/jan/mysql/10.2-sst/mysql-test/var'...
 
Checking supported features...
 
MariaDB Version 10.2.22-MariaDB-debug
 
 - SSL connections supported
 
 - binaries are debug compiled
 
Collecting tests...
 
Installing system database...
 
 
 
==============================================================================
 
 
 
TEST                                      RESULT   TIME (ms) or COMMENT
 
--------------------------------------------------------------------------
 
 
 
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
 
create database cardtest02;
 
use cardtest02;
 
DROP TABLE IF EXISTS `cardtest_tbl`;
 
Warnings:
 
Note	1051	Unknown table 'cardtest02.cardtest_tbl'
 
CREATE TABLE `cardtest_tbl` (
 
`id` bigint(18) NOT NULL AUTO_INCREMENT,
 
`course` bigint(18) NOT NULL,
 
`name` varchar(200) DEFAULT NULL,
 
`intro` longtext DEFAULT NULL,
 
`introformat` smallint(4) DEFAULT 0,
 
`timecreated` bigint(18) NOT NULL,
 
`timemodified` bigint(18) NOT NULL,
 
`grademethod` varchar(255) DEFAULT NULL,
 
`grade` decimal(10,5) DEFAULT NULL,
 
`updategradeoncompletion` tinyint(2) DEFAULT 1,
 
`competencyoncompletion` varchar(255) DEFAULT NULL,
 
`evaluationmethod` varchar(255) DEFAULT NULL,
 
`completionmethod` varchar(255) DEFAULT NULL,
 
`alloweddates` varchar(1024) DEFAULT NULL,
 
`allowedslots` varchar(1024) DEFAULT NULL,
 
`permissions` smallint(4) DEFAULT NULL,
 
`notifications` varchar(1024) DEFAULT NULL,
 
`type` varchar(255) DEFAULT NULL,
 
PRIMARY KEY (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=1324 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED COMMENT='assessment table retrofitted from MySQL';
 
ALTER TABLE `cardtest_tbl` DISABLE KEYS;
 
Warnings:
 
Note	1031	Storage engine InnoDB of the table `cardtest02`.`cardtest_tbl` doesn't have this option
 
# Inserting data...
 
ALTER TABLE `cardtest_tbl` ENABLE KEYS;
 
Warnings:
 
Note	1031	Storage engine InnoDB of the table `cardtest02`.`cardtest_tbl` doesn't have this option
 
use cardtest02;
 
show indexes from cardtest_tbl;
 
Table	cardtest_tbl
 
Non_unique	0
 
Key_name	PRIMARY
 
Seq_in_index	1
 
Column_name	id
 
Collation	A
 
Cardinality	4
 
Sub_part	NULL
 
Packed	NULL
 
Null	
Index_type	BTREE
 
Comment	
Index_comment	
select * from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl';
 
TABLE_CATALOG	def
 
TABLE_SCHEMA	cardtest02
 
TABLE_NAME	cardtest_tbl
 
TABLE_TYPE	BASE TABLE
 
ENGINE	InnoDB
 
VERSION	10
 
ROW_FORMAT	Compressed
 
TABLE_ROWS	4
 
AVG_ROW_LENGTH	2048
 
DATA_LENGTH	8192
 
MAX_DATA_LENGTH	0
 
INDEX_LENGTH	0
 
DATA_FREE	0
 
AUTO_INCREMENT	1324
 
CREATE_TIME	2019-01-28 16:12:28
 
UPDATE_TIME	2019-01-28 16:12:28
 
CHECK_TIME	NULL
 
TABLE_COLLATION	utf8_general_ci
 
CHECKSUM	NULL
 
CREATE_OPTIONS	row_format=COMPRESSED
 
TABLE_COMMENT	assessment table retrofitted from MySQL
 
select count(*) from cardtest_tbl;
 
count(*)	301
 
use test;
 
drop database cardtest02;
 
innodb.innodb_index_cardinality 'innodb' [ pass ]   5100
 
--------------------------------------------------------------------------
 
The servers were restarted 0 times
 
Spent 5.100 of 10 seconds executing testcases
 
 
 
Completed: All 1 tests were successful.



 Comments   
Comment by Jan Lindström (Inactive) [ 2019-01-29 ]

I have tested that MyISAM is not effected.

Comment by Marko Mäkelä [ 2019-01-29 ]

This bug would have to be fixed outside InnoDB.
Why would you want to fix this in MariaDB Server 10.2 (only)?
Wouldn't it be better to phase out the InnoDB internal statistics and to use engine-independent table statistics (EITS) instead?

I tried the following test case:

--source include/have_innodb.inc
 
create table t1(a int, key(a)) engine=innodb;
 
insert into t1 values (1);
 
--enable_info
 
alter table t1 disable keys;
 
--disable_info
 
show create table t1;
 
drop table t1;

The output is rather clear:

10.2 97930df13c0e403940969ebb47398760b59f753c

 
create table t1(a int, key(a)) engine=innodb;
 
insert into t1 values (1);
 
alter table t1 disable keys;
 
affected rows: 0
 
Warnings:
 
Note	1031	Storage engine InnoDB of the table `test`.`t1` doesn't have this option
 
show create table t1;
 
Table	Create Table
 
t1	CREATE TABLE `t1` (
 
  `a` int(11) DEFAULT NULL,
 
  KEY `a` (`a`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
drop table t1;

Neither ha_innobase::check_if_supported_inplace_alter() nor ha_innobase::rename_table() was invoked. So, the DISABLE KEYS is invoking neither the ALGORITHM=INPLACE nor the ALGORITHM=COPY logic.

I would not want to extend the persistent InnoDB data dictionary to allow metadata about disabled keys to be stored, because I would like to get rid of the InnoDB data dictionary (MDEV-11655) at some point. What is the use case for that? Bulk-loading would be better fixed by MDEV-515.

Comment by Jan Lindström (Inactive) [ 2019-01-30 ]

Why upper layer would somehow disable InnoDB clustered index (i.e. primary key in my example) and how that would effect InnoDB internal statistics calculation ? Or does it mean that result from that statistics calculation is not "published" to upper layer ? This is user reported problem on https://jira.mariadb.org/browse/MDEV-18226 but it has nothing to do with Galera.

Comment by Elena Stepanova [ 2019-01-31 ]

The problem allegedly revealed by the test case has nothing to do with DISABLE KEYS / ENABLE KEYS, you'll have the same effect if you replace ENABLE KEYS with, for example, COMMENT 'foo', while DISABLE KEYS makes no difference at all:

create database cardtest02;
 
use cardtest02;
 
DROP TABLE IF EXISTS `cardtest_tbl`;
 
Warnings:
 
Note	1051	Unknown table 'cardtest02.cardtest_tbl'
 
CREATE TABLE `cardtest_tbl` (
 
`id` bigint(18) NOT NULL AUTO_INCREMENT,
 
`course` bigint(18) NOT NULL,
 
`name` varchar(200) DEFAULT NULL,
 
`intro` longtext DEFAULT NULL,
 
`introformat` smallint(4) DEFAULT 0,
 
`timecreated` bigint(18) NOT NULL,
 
`timemodified` bigint(18) NOT NULL,
 
`grademethod` varchar(255) DEFAULT NULL,
 
`grade` decimal(10,5) DEFAULT NULL,
 
`updategradeoncompletion` tinyint(2) DEFAULT 1,
 
`competencyoncompletion` varchar(255) DEFAULT NULL,
 
`evaluationmethod` varchar(255) DEFAULT NULL,
 
`completionmethod` varchar(255) DEFAULT NULL,
 
`alloweddates` varchar(1024) DEFAULT NULL,
 
`allowedslots` varchar(1024) DEFAULT NULL,
 
`permissions` smallint(4) DEFAULT NULL,
 
`notifications` varchar(1024) DEFAULT NULL,
 
`type` varchar(255) DEFAULT NULL,
 
PRIMARY KEY (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=1324 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED COMMENT='assessment table retrofitted from MySQL';
 
# Inserting data...
 
ALTER TABLE `cardtest_tbl` COMMENT 'foo';
 
use cardtest02;
 
show indexes from cardtest_tbl;
 
Table	cardtest_tbl
 
Non_unique	0
 
Key_name	PRIMARY
 
Seq_in_index	1
 
Column_name	id
 
Collation	A
 
Cardinality	3
 
Sub_part	NULL
 
Packed	NULL
 
Null	
Index_type	BTREE
 
Comment	
Index_comment	
select * from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl';
 
TABLE_CATALOG	def
 
TABLE_SCHEMA	cardtest02
 
TABLE_NAME	cardtest_tbl
 
TABLE_TYPE	BASE TABLE
 
ENGINE	InnoDB
 
VERSION	10
 
ROW_FORMAT	Compressed
 
TABLE_ROWS	3
 
AVG_ROW_LENGTH	2730
 
DATA_LENGTH	8192
 
MAX_DATA_LENGTH	0
 
INDEX_LENGTH	0
 
DATA_FREE	0
 
AUTO_INCREMENT	1324
 
CREATE_TIME	2019-01-31 04:00:56
 
UPDATE_TIME	2019-01-31 04:00:56
 
CHECK_TIME	NULL
 
TABLE_COLLATION	utf8_general_ci
 
CHECKSUM	NULL
 
CREATE_OPTIONS	row_format=COMPRESSED
 
TABLE_COMMENT	foo
 
select count(*) from cardtest_tbl;
 
count(*)	301
 
use test;
 
drop database cardtest02;

In fact, the test case just has an insufficient sleep time. My wild uneducated guess would be that ALTER "queues" the second statistics recalculation, so at the end it takes twice as much time as it would normally have. If you double the sleep time, you'll get expected results.

DROP TABLE IF EXISTS `cardtest_tbl`;
 
Warnings:
 
Note	1051	Unknown table 'cardtest02.cardtest_tbl'
 
CREATE TABLE `cardtest_tbl` (
 
`id` bigint(18) NOT NULL AUTO_INCREMENT,
 
`course` bigint(18) NOT NULL,
 
`name` varchar(200) DEFAULT NULL,
 
`intro` longtext DEFAULT NULL,
 
`introformat` smallint(4) DEFAULT 0,
 
`timecreated` bigint(18) NOT NULL,
 
`timemodified` bigint(18) NOT NULL,
 
`grademethod` varchar(255) DEFAULT NULL,
 
`grade` decimal(10,5) DEFAULT NULL,
 
`updategradeoncompletion` tinyint(2) DEFAULT 1,
 
`competencyoncompletion` varchar(255) DEFAULT NULL,
 
`evaluationmethod` varchar(255) DEFAULT NULL,
 
`completionmethod` varchar(255) DEFAULT NULL,
 
`alloweddates` varchar(1024) DEFAULT NULL,
 
`allowedslots` varchar(1024) DEFAULT NULL,
 
`permissions` smallint(4) DEFAULT NULL,
 
`notifications` varchar(1024) DEFAULT NULL,
 
`type` varchar(255) DEFAULT NULL,
 
PRIMARY KEY (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=1324 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED COMMENT='assessment table retrofitted from MySQL';
 
ALTER TABLE `cardtest_tbl` DISABLE KEYS;
 
Warnings:
 
Note	1031	Storage engine InnoDB of the table `cardtest02`.`cardtest_tbl` doesn't have this option
 
# Inserting data...
 
ALTER TABLE `cardtest_tbl` ENABLE KEYS;
 
Warnings:
 
Note	1031	Storage engine InnoDB of the table `cardtest02`.`cardtest_tbl` doesn't have this option
 
# Using sleep 10 instead of sleep 5
 
use cardtest02;
 
show indexes from cardtest_tbl;
 
Table	cardtest_tbl
 
Non_unique	0
 
Key_name	PRIMARY
 
Seq_in_index	1
 
Column_name	id
 
Collation	A
 
Cardinality	301
 
Sub_part	NULL
 
Packed	NULL
 
Null	
Index_type	BTREE
 
Comment	
Index_comment	
select * from information_schema.tables WHERE TABLE_NAME = 'cardtest_tbl';
 
TABLE_CATALOG	def
 
TABLE_SCHEMA	cardtest02
 
TABLE_NAME	cardtest_tbl
 
TABLE_TYPE	BASE TABLE
 
ENGINE	InnoDB
 
VERSION	10
 
ROW_FORMAT	Compressed
 
TABLE_ROWS	301
 
AVG_ROW_LENGTH	81
 
DATA_LENGTH	24576
 
MAX_DATA_LENGTH	0
 
INDEX_LENGTH	0
 
DATA_FREE	0
 
AUTO_INCREMENT	1324
 
CREATE_TIME	2019-01-31 04:03:47
 
UPDATE_TIME	2019-01-31 04:03:47
 
CHECK_TIME	NULL
 
TABLE_COLLATION	utf8_general_ci
 
CHECKSUM	NULL
 
CREATE_OPTIONS	row_format=COMPRESSED
 
TABLE_COMMENT	assessment table retrofitted from MySQL
 
select count(*) from cardtest_tbl;
 
count(*)	301
 
use test;
 
drop database cardtest02;

Same happens if you keep sleep=5, but add another one before ALTER.

Generated at Thu Feb 08 08:43:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.