Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0.27, 10.1.26, 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
10.2.10
Description
After altering a partitioned table, some queries can return invalid results.
To reproduce, first create a partitioned table and insert some data:
drop table if exists t;
|
|
|
CREATE TABLE `t` (
|
`id` bigint(20) unsigned NOT NULL auto_increment,
|
`d` date NOT NULL,
|
`a` bigint(20) unsigned NOT NULL,
|
`b` smallint(5) unsigned DEFAULT NULL,
|
PRIMARY KEY (`id`,`d`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs STATS_SAMPLE_PAGES=200
|
/*!50500 PARTITION BY RANGE COLUMNS(d)
|
(PARTITION p20170913 VALUES LESS THAN ('2017-09-14') ENGINE = InnoDB,
|
PARTITION p20170914 VALUES LESS THAN ('2017-09-15') ENGINE = InnoDB,
|
PARTITION p20170915 VALUES LESS THAN ('2017-09-16') ENGINE = InnoDB,
|
PARTITION p20170916 VALUES LESS THAN ('2017-09-17') ENGINE = InnoDB,
|
PARTITION p20170917 VALUES LESS THAN ('2017-09-18') ENGINE = InnoDB,
|
PARTITION p99991231 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;
|
|
|
insert into t(d,a,b) values ('2017-09-15',rand()*10000,rand()*10);
|
insert into t(d,a,b) values ('2017-09-15',rand()*10000,rand()*10);
|
|
|
replace into t(d,a,b) select '2017-09-15',rand()*10000,rand()*10 from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14;
|
We can see that the table has over 16k rows:
MariaDB [db1]> select count(*) from t where d ='2017-09-15';
|
+----------+
|
| count(*) |
|
+----------+
|
| 16386 |
|
+----------+
|
1 row in set (0.00 sec)
|
But things break if we alter the table:
ALTER TABLE t CHANGE b c smallint(5) unsigned , ADD KEY idx_d_a (d, a);
|
analyze table t;
|
Now that the table has been altered, let's compare the output of these two queries:
select count(*) from t where d ='2017-09-15';
|
select count(*) from t force index(primary) where d ='2017-09-15';
|
Here's the actual output:
MariaDB [db1]> select count(*) from t where d ='2017-09-15';
|
+----------+
|
| count(*) |
|
+----------+
|
| 0 |
|
+----------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [db1]> select count(*) from t force index(primary) where d ='2017-09-15';
|
+----------+
|
| count(*) |
|
+----------+
|
| 16386 |
|
+----------+
|
1 row in set (0.00 sec)
|
We can see that output for the first query is incorrect.
Rebuilding the table seems to fix it:
MariaDB [db1]> alter table t engine = InnoDB;
|
Query OK, 0 rows affected (0.24 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [db1]> select count(*) from t where d ='2017-09-15';
|
+----------+
|
| count(*) |
|
+----------+
|
| 16386 |
|
+----------+
|
1 row in set (0.00 sec)
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
commit fc9ff69578fa8c3d818d6eaaa171b4be49d70814
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Tue Oct 10 10:19:10 2017 +0300
MDEV-13838: Wrong result after altering a partitioned table
Reverted incorrect changes done on MDEV-7367 and MDEV-9469. Fixes properly
also related bugs:
MDEV-13668: InnoDB unnecessarily rebuilds table when renaming a column and adding index
MDEV-9469: 'Incorrect key file' on ALTER TABLE
MDEV-9548: Alter table (renaming and adding index) fails with "Incorrect key file for table"
MDEV-10535: ALTER TABLE causes standalone/wsrep cluster crash
MDEV-13640: ALTER TABLE CHANGE and ADD INDEX on auto_increment column fails with "Incorrect key file for table..."
Root cause for all these bugs is the fact that MariaDB .frm file
can contain virtual columns but InnoDB dictionary does not and
previous fixes were incorrect or unnecessarily forced table
rebuilt. In index creation key_part->fieldnr can be bigger than
number of columns in InnoDB data dictionary. We need to skip not
stored fields when calculating correct column number for InnoDB
data dictionary.
dict_table_get_col_name_for_mysql
Remove
innobase_match_index_columns
Revert incorrect change done on MDEV-7367
innobase_need_rebuild
Remove unnecessary rebuild force when column is renamed.
innobase_create_index_field_def
Calculate InnoDB column number correctly and remove
unnecessary column name set.
innobase_create_index_def, innobase_create_key_defs
Remove unneeded fields parameter. Revert unneeded memset.
prepare_inplace_alter_table_dict
Remove unneeded col_names parameter
index_field_t
Remove unneeded col_name member.
row_merge_create_index
Remove unneeded col_names parameter and resolution.
Effected tests:
innodb-alter-table : Add test case for MDEV-13668
innodb-alter : Remove MDEV-13668, MDEV-9469 FIXMEs
and restore original tests
innodb-wl5980-alter : Remove MDEV-13668, MDEV-9469 FIXMEs
and restore original tests
A tentative merge to 10.2 revealed that an upstream bug fix was inadvertently reverted in a merge of MySQL 5.7.9 into MariaDB Server 10.2.2. I pushed a fix to bb-10.2-marko.
The code changes look OK to me.
Please address my most recent review comments and let me know when buildbot has finished running the revised tests. I would like to have one last look before the push.