[MDEV-13838] Wrong result after altering a partitioned table - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.0.27, 10.1.26, 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 10.0.33, 10.1.29, 10.2.10
Component/s: Partitioning, Storage Engine - InnoDB, Storage Engine - XtraDB
Labels:
- innodb
- partitioning

Sprint:
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

MDEV-7367 Updating a virtual column corrupts table which crashes server

Closed

MDEV-9469 'Incorrect key file' on ALTER TABLE

Closed

MDEV-10535 ALTER TABLE causes standalone/wsrep cluster crash

Closed

MDEV-13668 InnoDB unnecessarily rebuilds table when renaming a column and adding index

Closed

MDEV-13671 InnoDB should use case-insensitive column name comparisons like the rest of the server

Closed

Activity

Ascending order - Click to sort in descending order

View 7 older comments

Marko Mäkelä added a comment - 2017-10-09 13:04

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.

Marko Mäkelä added a comment - 2017-10-09 13:04 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.

Jan Lindström (Inactive) added a comment - 2017-10-10 07:24

https://github.com/MariaDB/server/commit/e5163f40891fa85dcec0c3d76321f42b25636352

Jan Lindström (Inactive) added a comment - 2017-10-10 07:24 https://github.com/MariaDB/server/commit/e5163f40891fa85dcec0c3d76321f42b25636352

Marko Mäkelä added a comment - 2017-10-10 13:33

Looks OK to me.

Marko Mäkelä added a comment - 2017-10-10 13:33 Looks OK to me.

Jan Lindström (Inactive) added a comment - 2017-10-10 15:14

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

Jan Lindström (Inactive) added a comment - 2017-10-10 15:14 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

Marko Mäkelä added a comment - 2017-10-10 16:21

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.

Marko Mäkelä added a comment - 2017-10-10 16:21 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.

People

Assignee:: Jan Lindström (Inactive)

Reporter:: Geoff Montee (Inactive)

Votes:: 2 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2017-09-18 17:30

Updated:: 2024-07-08 00:38

Resolved:: 2017-10-10 15:14

Git Integration

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