Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-18396

InnoDB table/index stats are incorrect if DISABLE/ENABLE keys is used

    XMLWordPrintable

    Details

      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.
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              jplindst Jan Lindström
              Reporter:
              jplindst Jan Lindström
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.