[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

Elena Stepanova added a comment - 2017-09-18 23:21

Reproducible on 10.x.
Not reproducible on MySQL 5.6, 5.7, MariaDB 5.5.

The same test case suitable for MTR

--source include/have_innodb.inc

--source include/have_partition.inc

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

/*!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;

select count(*) from t where d ='2017-09-15';

ALTER TABLE t CHANGE b c smallint(5) unsigned , ADD KEY idx_d_a (d, a);

analyze table t;

select count(*) from t where d ='2017-09-15';

select count(*) from t force index(primary) where d ='2017-09-15';

alter table t engine = InnoDB;

select count(*) from t where d ='2017-09-15';

# Cleanup

drop table t;

Release builds produce a wrong result as described. Debug builds fail with the assertion failure:

10.0 a870099817
2017-09-19 02:19:19 7ff78f817700 InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `idx_d_a` of table `test`.`t` /* Partition `p20170915` */. Last data field length 8 bytes,
InnoDB: key ptr now exceeds key end by 5 bytes.
InnoDB: Key value in the MySQL format:
len 3; hex 2fc30f; asc / ;
2017-09-19 02:19:19 7ff78f817700 InnoDB: Assertion failure in thread 140701241276160 in file row0sel.cc line 2497
InnoDB: Failing assertion: 0

#5 0x00007ff78d4eb3fa in abort () from /lib/x86_64-linux-gnu/libc.so.6
#6 0x00007ff786060fdc in row_sel_convert_mysql_key_to_innobase (tuple=0x7ff779415278, buf=0x7ff7797b03f0 "", buf_len=11, index=0x7ff7795d10f8, key_ptr=0x7ff779798118 "\245\245\245\245\245\245\245\245/\303\017", '\245' <repeats 13 times>, "\003", key_len=3, trx=0x7ff779552278) at /data/src/10.0/storage/innobase/row/row0sel.cc:2497
#7 0x00007ff785f4d88e in ha_innodb::records_in_range (this=0x7ff779566288, keynr=1, min_key=0x7ff78f814020, max_key=0x7ff78f814040) at /data/src/10.0/storage/innobase/handler/ha_innodb.cc:11141
#8 0x0000000000df43ab in ha_partition::records_in_range (this=0x7ff77950b088, inx=1, min_key=0x7ff78f814020, max_key=0x7ff78f814040) at /data/src/10.0/sql/ha_partition.cc:7645
#9 0x00000000007a4eba in handler::multi_range_read_info_const (this=0x7ff77950b088, keyno=1, seq=0x7ff78f814120, seq_init_param=0x7ff78f814150, n_ranges_arg=0, bufsz=0x7ff78f814730, flags=0x7ff78f814734, cost=0x7ff78f814700) at /data/src/10.0/sql/multi_range_read.cc:94
#10 0x000000000095b7c3 in check_quick_select (param=0x7ff78f8149d0, idx=0, index_only=true, tree=0x7ff779798390, update_tbl_stats=true, mrr_flags=0x7ff78f814734, bufsize=0x7ff78f814730, cost=0x7ff78f814700) at /data/src/10.0/sql/opt_range.cc:10748
#11 0x00000000009543ba in get_key_scans_params (param=0x7ff78f8149d0, tree=0x7ff779798130, index_read_must_be_used=false, update_tbl_stats=true, read_time=3434.5177501826147) at /data/src/10.0/sql/opt_range.cc:7369
#12 0x000000000094b74e in SQL_SELECT::test_quick_select (this=0x7ff7795f3088, thd=0x7ff781b69070, keys_to_use=..., prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false) at /data/src/10.0/sql/opt_range.cc:3184
#13 0x0000000000688d36 in get_quick_record_count (thd=0x7ff781b69070, select=0x7ff7795f3088, table=0x7ff7795c9070, keys=0x7ff7794fb788, limit=18446744073709551615) at /data/src/10.0/sql/sql_select.cc:3355
#14 0x000000000068adcf in make_join_statistics (join=0x7ff7794fad88, tables_list=..., conds=0x7ff7794fb3e0, keyuse_array=0x7ff7794fb0b0) at /data/src/10.0/sql/sql_select.cc:3963
#15 0x0000000000681ded in JOIN::optimize_inner (this=0x7ff7794fad88) at /data/src/10.0/sql/sql_select.cc:1345
#16 0x0000000000680ce6 in JOIN::optimize (this=0x7ff7794fad88) at /data/src/10.0/sql/sql_select.cc:1029
#17 0x0000000000688ab1 in mysql_select (thd=0x7ff781b69070, rref_pointer_array=0x7ff781b6d388, tables=0x7ff7794fa388, wild_num=0, fields=..., conds=0x7ff7794fab58, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7ff7794fad68, unit=0x7ff781b6ca08, select_lex=0x7ff781b6d0f8) at /data/src/10.0/sql/sql_select.cc:3304
#18 0x000000000067ee2e in handle_select (thd=0x7ff781b69070, lex=0x7ff781b6c940, result=0x7ff7794fad68, setup_tables_done_option=0) at /data/src/10.0/sql/sql_select.cc:373
#19 0x000000000065355a in execute_sqlcom_select (thd=0x7ff781b69070, all_tables=0x7ff7794fa388) at /data/src/10.0/sql/sql_parse.cc:5302
#20 0x000000000064bae5 in mysql_execute_command (thd=0x7ff781b69070) at /data/src/10.0/sql/sql_parse.cc:2562
#21 0x00000000006561da in mysql_parse (thd=0x7ff781b69070, rawbuf=0x7ff7794fa088 "select count(*) from t where d ='2017-09-15'", length=44, parser_state=0x7ff78f816640) at /data/src/10.0/sql/sql_parse.cc:6578
#22 0x0000000000648d07 in dispatch_command (command=COM_QUERY, thd=0x7ff781b69070, packet=0x7ff7875ef071 "select count(*) from t where d ='2017-09-15'", packet_length=44) at /data/src/10.0/sql/sql_parse.cc:1305
#23 0x0000000000648021 in do_command (thd=0x7ff781b69070) at /data/src/10.0/sql/sql_parse.cc:999
#24 0x0000000000767cd8 in do_handle_one_connection (thd_arg=0x7ff781b69070) at /data/src/10.0/sql/sql_connect.cc:1377
#25 0x0000000000767a4a in handle_one_connection (arg=0x7ff781b69070) at /data/src/10.0/sql/sql_connect.cc:1292
#26 0x0000000000ac7cfa in pfs_spawn_thread (arg=0x7ff781b19670) at /data/src/10.0/storage/perfschema/pfs.cc:1860
#27 0x00007ff78f454494 in start_thread (arg=0x7ff78f817700) at pthread_create.c:333
#28 0x00007ff78d59f93f in clone () from /lib/x86_64-linux-gnu/libc.so.6

The test case is related to ~~MDEV-10535~~ (possibly the underlying problem also is). 10.0.23 works all right. 10.0.24-26 fail with ~~MDEV-10535~~. 10.0.27+ fail as described here.

Elena Stepanova added a comment - 2017-09-18 23:21 Reproducible on 10.x. Not reproducible on MySQL 5.6, 5.7, MariaDB 5.5. The same test case suitable for MTR --source include/have_innodb.inc --source include/have_partition.inc 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 /*!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; select count (*) from t where d = '2017-09-15' ; ALTER TABLE t CHANGE b c smallint (5) unsigned , ADD KEY idx_d_a (d, a); analyze table t; select count (*) from t where d = '2017-09-15' ; select count (*) from t force index ( primary ) where d = '2017-09-15' ; alter table t engine = InnoDB; select count (*) from t where d = '2017-09-15' ; # Cleanup drop table t; Release builds produce a wrong result as described. Debug builds fail with the assertion failure: 10.0 a870099817 2017-09-19 02:19:19 7ff78f817700 InnoDB: Warning: using a partial-field key prefix in search. InnoDB: index `idx_d_a` of table `test`.`t` /* Partition `p20170915` */. Last data field length 8 bytes, InnoDB: key ptr now exceeds key end by 5 bytes. InnoDB: Key value in the MySQL format: len 3; hex 2fc30f; asc / ; 2017-09-19 02:19:19 7ff78f817700 InnoDB: Assertion failure in thread 140701241276160 in file row0sel.cc line 2497 InnoDB: Failing assertion: 0 #5 0x00007ff78d4eb3fa in abort () from /lib/x86_64-linux-gnu/libc.so.6 #6 0x00007ff786060fdc in row_sel_convert_mysql_key_to_innobase (tuple=0x7ff779415278, buf=0x7ff7797b03f0 "", buf_len=11, index=0x7ff7795d10f8, key_ptr=0x7ff779798118 "\245\245\245\245\245\245\245\245/\303\017", '\245' <repeats 13 times>, "\003", key_len=3, trx=0x7ff779552278) at /data/src/10.0/storage/innobase/row/row0sel.cc:2497 #7 0x00007ff785f4d88e in ha_innodb::records_in_range (this=0x7ff779566288, keynr=1, min_key=0x7ff78f814020, max_key=0x7ff78f814040) at /data/src/10.0/storage/innobase/handler/ha_innodb.cc:11141 #8 0x0000000000df43ab in ha_partition::records_in_range (this=0x7ff77950b088, inx=1, min_key=0x7ff78f814020, max_key=0x7ff78f814040) at /data/src/10.0/sql/ha_partition.cc:7645 #9 0x00000000007a4eba in handler::multi_range_read_info_const (this=0x7ff77950b088, keyno=1, seq=0x7ff78f814120, seq_init_param=0x7ff78f814150, n_ranges_arg=0, bufsz=0x7ff78f814730, flags=0x7ff78f814734, cost=0x7ff78f814700) at /data/src/10.0/sql/multi_range_read.cc:94 #10 0x000000000095b7c3 in check_quick_select (param=0x7ff78f8149d0, idx=0, index_only=true, tree=0x7ff779798390, update_tbl_stats=true, mrr_flags=0x7ff78f814734, bufsize=0x7ff78f814730, cost=0x7ff78f814700) at /data/src/10.0/sql/opt_range.cc:10748 #11 0x00000000009543ba in get_key_scans_params (param=0x7ff78f8149d0, tree=0x7ff779798130, index_read_must_be_used=false, update_tbl_stats=true, read_time=3434.5177501826147) at /data/src/10.0/sql/opt_range.cc:7369 #12 0x000000000094b74e in SQL_SELECT::test_quick_select (this=0x7ff7795f3088, thd=0x7ff781b69070, keys_to_use=..., prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false) at /data/src/10.0/sql/opt_range.cc:3184 #13 0x0000000000688d36 in get_quick_record_count (thd=0x7ff781b69070, select=0x7ff7795f3088, table=0x7ff7795c9070, keys=0x7ff7794fb788, limit=18446744073709551615) at /data/src/10.0/sql/sql_select.cc:3355 #14 0x000000000068adcf in make_join_statistics (join=0x7ff7794fad88, tables_list=..., conds=0x7ff7794fb3e0, keyuse_array=0x7ff7794fb0b0) at /data/src/10.0/sql/sql_select.cc:3963 #15 0x0000000000681ded in JOIN::optimize_inner (this=0x7ff7794fad88) at /data/src/10.0/sql/sql_select.cc:1345 #16 0x0000000000680ce6 in JOIN::optimize (this=0x7ff7794fad88) at /data/src/10.0/sql/sql_select.cc:1029 #17 0x0000000000688ab1 in mysql_select (thd=0x7ff781b69070, rref_pointer_array=0x7ff781b6d388, tables=0x7ff7794fa388, wild_num=0, fields=..., conds=0x7ff7794fab58, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7ff7794fad68, unit=0x7ff781b6ca08, select_lex=0x7ff781b6d0f8) at /data/src/10.0/sql/sql_select.cc:3304 #18 0x000000000067ee2e in handle_select (thd=0x7ff781b69070, lex=0x7ff781b6c940, result=0x7ff7794fad68, setup_tables_done_option=0) at /data/src/10.0/sql/sql_select.cc:373 #19 0x000000000065355a in execute_sqlcom_select (thd=0x7ff781b69070, all_tables=0x7ff7794fa388) at /data/src/10.0/sql/sql_parse.cc:5302 #20 0x000000000064bae5 in mysql_execute_command (thd=0x7ff781b69070) at /data/src/10.0/sql/sql_parse.cc:2562 #21 0x00000000006561da in mysql_parse (thd=0x7ff781b69070, rawbuf=0x7ff7794fa088 "select count(*) from t where d ='2017-09-15'", length=44, parser_state=0x7ff78f816640) at /data/src/10.0/sql/sql_parse.cc:6578 #22 0x0000000000648d07 in dispatch_command (command=COM_QUERY, thd=0x7ff781b69070, packet=0x7ff7875ef071 "select count(*) from t where d ='2017-09-15'", packet_length=44) at /data/src/10.0/sql/sql_parse.cc:1305 #23 0x0000000000648021 in do_command (thd=0x7ff781b69070) at /data/src/10.0/sql/sql_parse.cc:999 #24 0x0000000000767cd8 in do_handle_one_connection (thd_arg=0x7ff781b69070) at /data/src/10.0/sql/sql_connect.cc:1377 #25 0x0000000000767a4a in handle_one_connection (arg=0x7ff781b69070) at /data/src/10.0/sql/sql_connect.cc:1292 #26 0x0000000000ac7cfa in pfs_spawn_thread (arg=0x7ff781b19670) at /data/src/10.0/storage/perfschema/pfs.cc:1860 #27 0x00007ff78f454494 in start_thread (arg=0x7ff78f817700) at pthread_create.c:333 #28 0x00007ff78d59f93f in clone () from /lib/x86_64-linux-gnu/libc.so.6 The test case is related to MDEV-10535 (possibly the underlying problem also is). 10.0.23 works all right. 10.0.24-26 fail with MDEV-10535 . 10.0.27+ fail as described here.

Jan Lindström (Inactive) added a comment - 2017-09-21 07:30

With latest 10.1 test case causes a crash:

2017-09-21 10:28:15 7f7ed1f43b00  InnoDB: Warning: using a partial-field key prefix in search.

InnoDB: index `idx_d_a` of table `test`.`t` /* Partition `p20170915` */. Last data field length 8 bytes,

InnoDB: key ptr now exceeds key end by 5 bytes.

InnoDB: Key value in the MySQL format:

 len 3; hex 2fc30f; asc /  ;

2017-09-21 10:28:15 7f7ed1f43b00  InnoDB: Assertion failure in thread 140182665050880 in file row0sel.cc line 2499

stack:

#0  __pthread_kill (threadid=<optimized out>, signo=6) at ../sysdeps/unix/sysv/linux/pthread_kill.c:57

#1  0x00005638f9b9d34e in my_write_core (sig=6) at /home/jan/mysql/10.1-bugs/mysys/stacktrace.c:477

#2  0x00005638f9538d5b in handle_fatal_signal (sig=6) at /home/jan/mysql/10.1-bugs/sql/signal_handler.cc:296

#3  <signal handler called>

#4  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51

#5  0x00007f7ecf2073fa in __GI_abort () at abort.c:89

#6  0x00007f7ec80238be in row_sel_convert_mysql_key_to_innobase (tuple=0x7f7eb8044578, buf=0x7f7eb82b63f0 "", buf_len=11, index=0x7f7eb8271e78, key_ptr=0x7f7eb81db118 "\245\245\245\245\245\245\245\245/\303\017", '\245' <repeats 13 times>, "\003", key_len=3, trx=0x7f7eb8068a78) at /home/jan/mysql/10.1-bugs/storage/innobase/row/row0sel.cc:2499

#7  0x00007f7ec7ef77f5 in ha_innodb::records_in_range (this=0x7f7eb82e4088, keynr=1, min_key=0x7f7ed1f3fcf0, max_key=0x7f7ed1f3fd10) at /home/jan/mysql/10.1-bugs/storage/innobase/handler/ha_innodb.cc:13076

#8  0x00005638f9b6a676 in ha_partition::records_in_range (this=0x7f7eb812e088, inx=1, min_key=0x7f7ed1f3fcf0, max_key=0x7f7ed1f3fd10) at /home/jan/mysql/10.1-bugs/sql/ha_partition.cc:7643

#9  0x00005638f9479d32 in handler::multi_range_read_info_const (this=0x7f7eb812e088, keyno=1, seq=0x7f7ed1f3fdd0, seq_init_param=0x7f7ed1f3fe00, n_ranges_arg=0, bufsz=0x7f7ed1f40390, flags=0x7f7ed1f4038c, cost=0x7f7ed1f40400) at /home/jan/mysql/10.1-bugs/sql/multi_range_read.cc:94

#10 0x00005638f9668c32 in check_quick_select (param=0x7f7ed1f405b0, idx=0, index_only=true, tree=0x7f7eb81db1b8, update_tbl_stats=true, mrr_flags=0x7f7ed1f4038c, bufsize=0x7f7ed1f40390, cost=0x7f7ed1f40400) at /home/jan/mysql/10.1-bugs/sql/opt_range.cc:10117

#11 0x00005638f966159e in get_key_scans_params (param=0x7f7ed1f405b0, tree=0x7f7eb81db130, index_read_must_be_used=false, update_tbl_stats=true, read_time=3434.5177501826147) at /home/jan/mysql/10.1-bugs/sql/opt_range.cc:6787

#12 0x00005638f9658266 in SQL_SELECT::test_quick_select (this=0x7f7eb8100788, thd=0x7f7ec53bd070, keys_to_use=..., prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false, remove_false_parts_of_where=true) at /home/jan/mysql/10.1-bugs/sql/opt_range.cc:2596

#13 0x00005638f934393f in get_quick_record_count (thd=0x7f7ec53bd070, select=0x7f7eb8100788, table=0x7f7eb8084670, keys=0x7f7eb80ff840, limit=18446744073709551615) at /home/jan/mysql/10.1-bugs/sql/sql_select.cc:3486

#14 0x00005638f9345e03 in make_join_statistics (join=0x7f7eb80fedb0, tables_list=..., keyuse_array=0x7f7eb80ff0f0) at /home/jan/mysql/10.1-bugs/sql/sql_select.cc:4106

#15 0x00005638f933c139 in JOIN::optimize_inner (this=0x7f7eb80fedb0) at /home/jan/mysql/10.1-bugs/sql/sql_select.cc:1367

#16 0x00005638f933b050 in JOIN::optimize (this=0x7f7eb80fedb0) at /home/jan/mysql/10.1-bugs/sql/sql_select.cc:1045

#17 0x00005638f93436b9 in mysql_select (thd=0x7f7ec53bd070, rref_pointer_array=0x7f7ec53c1500, tables=0x7f7eb80fe370, wild_num=0, fields=..., conds=0x7f7eb80feb20, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f7eb80fed90, unit=0x7f7ec53c0b70, select_lex=0x7f7ec53c1270) at /home/jan/mysql/10.1-bugs/sql/sql_select.cc:3435

#18 0x00005638f933919f in handle_select (thd=0x7f7ec53bd070, lex=0x7f7ec53c0aa8, result=0x7f7eb80fed90, setup_tables_done_option=0) at /home/jan/mysql/10.1-bugs/sql/sql_select.cc:372

#19 0x00005638f9308e53 in execute_sqlcom_select (thd=0x7f7ec53bd070, all_tables=0x7f7eb80fe370) at /home/jan/mysql/10.1-bugs/sql/sql_parse.cc:5912

#20 0x00005638f92fed74 in mysql_execute_command (thd=0x7f7ec53bd070) at /home/jan/mysql/10.1-bugs/sql/sql_parse.cc:2982

#21 0x00005638f930c597 in mysql_parse (thd=0x7f7ec53bd070, rawbuf=0x7f7eb80fe088 "select count(*) from t where d ='2017-09-15'", length=44, parser_state=0x7f7ed1f42460) at /home/jan/mysql/10.1-bugs/sql/sql_parse.cc:7333

#22 0x00005638f92faeb5 in dispatch_command (command=COM_QUERY, thd=0x7f7ec53bd070, packet=0x7f7ec7378071 "select count(*) from t where d ='2017-09-15'", packet_length=44) at /home/jan/mysql/10.1-bugs/sql/sql_parse.cc:1484

#23 0x00005638f92f9c87 in do_command (thd=0x7f7ec53bd070) at /home/jan/mysql/10.1-bugs/sql/sql_parse.cc:1106

#24 0x00005638f9431f9d in do_handle_one_connection (thd_arg=0x7f7ec53bd070) at /home/jan/mysql/10.1-bugs/sql/sql_connect.cc:1349

#25 0x00005638f9431ce3 in handle_one_connection (arg=0x7f7ec53bd070) at /home/jan/mysql/10.1-bugs/sql/sql_connect.cc:1261

#26 0x00005638f985ea13 in pfs_spawn_thread (arg=0x7f7ec73adbf0) at /home/jan/mysql/10.1-bugs/storage/perfschema/pfs.cc:1860

#27 0x00007f7ed1c60494 in start_thread (arg=0x7f7ed1f43b00) at pthread_create.c:333

#28 0x00007f7ecf2bbaff in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

Jan Lindström (Inactive) added a comment - 2017-09-21 07:30 With latest 10.1 test case causes a crash: 2017-09-21 10:28:15 7f7ed1f43b00 InnoDB: Warning: using a partial-field key prefix in search. InnoDB: index `idx_d_a` of table `test`.`t` /* Partition `p20170915` */. Last data field length 8 bytes, InnoDB: key ptr now exceeds key end by 5 bytes. InnoDB: Key value in the MySQL format: len 3; hex 2fc30f; asc / ; 2017-09-21 10:28:15 7f7ed1f43b00 InnoDB: Assertion failure in thread 140182665050880 in file row0sel.cc line 2499 stack: #0 __pthread_kill (threadid=<optimized out>, signo=6) at ../sysdeps/unix/sysv/linux/pthread_kill.c:57 #1 0x00005638f9b9d34e in my_write_core (sig=6) at /home/jan/mysql/10.1-bugs/mysys/stacktrace.c:477 #2 0x00005638f9538d5b in handle_fatal_signal (sig=6) at /home/jan/mysql/10.1-bugs/sql/signal_handler.cc:296 #3 <signal handler called> #4 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51 #5 0x00007f7ecf2073fa in __GI_abort () at abort.c:89 #6 0x00007f7ec80238be in row_sel_convert_mysql_key_to_innobase (tuple=0x7f7eb8044578, buf=0x7f7eb82b63f0 "", buf_len=11, index=0x7f7eb8271e78, key_ptr=0x7f7eb81db118 "\245\245\245\245\245\245\245\245/\303\017", '\245' <repeats 13 times>, "\003", key_len=3, trx=0x7f7eb8068a78) at /home/jan/mysql/10.1-bugs/storage/innobase/row/row0sel.cc:2499 #7 0x00007f7ec7ef77f5 in ha_innodb::records_in_range (this=0x7f7eb82e4088, keynr=1, min_key=0x7f7ed1f3fcf0, max_key=0x7f7ed1f3fd10) at /home/jan/mysql/10.1-bugs/storage/innobase/handler/ha_innodb.cc:13076 #8 0x00005638f9b6a676 in ha_partition::records_in_range (this=0x7f7eb812e088, inx=1, min_key=0x7f7ed1f3fcf0, max_key=0x7f7ed1f3fd10) at /home/jan/mysql/10.1-bugs/sql/ha_partition.cc:7643 #9 0x00005638f9479d32 in handler::multi_range_read_info_const (this=0x7f7eb812e088, keyno=1, seq=0x7f7ed1f3fdd0, seq_init_param=0x7f7ed1f3fe00, n_ranges_arg=0, bufsz=0x7f7ed1f40390, flags=0x7f7ed1f4038c, cost=0x7f7ed1f40400) at /home/jan/mysql/10.1-bugs/sql/multi_range_read.cc:94 #10 0x00005638f9668c32 in check_quick_select (param=0x7f7ed1f405b0, idx=0, index_only=true, tree=0x7f7eb81db1b8, update_tbl_stats=true, mrr_flags=0x7f7ed1f4038c, bufsize=0x7f7ed1f40390, cost=0x7f7ed1f40400) at /home/jan/mysql/10.1-bugs/sql/opt_range.cc:10117 #11 0x00005638f966159e in get_key_scans_params (param=0x7f7ed1f405b0, tree=0x7f7eb81db130, index_read_must_be_used=false, update_tbl_stats=true, read_time=3434.5177501826147) at /home/jan/mysql/10.1-bugs/sql/opt_range.cc:6787 #12 0x00005638f9658266 in SQL_SELECT::test_quick_select (this=0x7f7eb8100788, thd=0x7f7ec53bd070, keys_to_use=..., prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false, remove_false_parts_of_where=true) at /home/jan/mysql/10.1-bugs/sql/opt_range.cc:2596 #13 0x00005638f934393f in get_quick_record_count (thd=0x7f7ec53bd070, select=0x7f7eb8100788, table=0x7f7eb8084670, keys=0x7f7eb80ff840, limit=18446744073709551615) at /home/jan/mysql/10.1-bugs/sql/sql_select.cc:3486 #14 0x00005638f9345e03 in make_join_statistics (join=0x7f7eb80fedb0, tables_list=..., keyuse_array=0x7f7eb80ff0f0) at /home/jan/mysql/10.1-bugs/sql/sql_select.cc:4106 #15 0x00005638f933c139 in JOIN::optimize_inner (this=0x7f7eb80fedb0) at /home/jan/mysql/10.1-bugs/sql/sql_select.cc:1367 #16 0x00005638f933b050 in JOIN::optimize (this=0x7f7eb80fedb0) at /home/jan/mysql/10.1-bugs/sql/sql_select.cc:1045 #17 0x00005638f93436b9 in mysql_select (thd=0x7f7ec53bd070, rref_pointer_array=0x7f7ec53c1500, tables=0x7f7eb80fe370, wild_num=0, fields=..., conds=0x7f7eb80feb20, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f7eb80fed90, unit=0x7f7ec53c0b70, select_lex=0x7f7ec53c1270) at /home/jan/mysql/10.1-bugs/sql/sql_select.cc:3435 #18 0x00005638f933919f in handle_select (thd=0x7f7ec53bd070, lex=0x7f7ec53c0aa8, result=0x7f7eb80fed90, setup_tables_done_option=0) at /home/jan/mysql/10.1-bugs/sql/sql_select.cc:372 #19 0x00005638f9308e53 in execute_sqlcom_select (thd=0x7f7ec53bd070, all_tables=0x7f7eb80fe370) at /home/jan/mysql/10.1-bugs/sql/sql_parse.cc:5912 #20 0x00005638f92fed74 in mysql_execute_command (thd=0x7f7ec53bd070) at /home/jan/mysql/10.1-bugs/sql/sql_parse.cc:2982 #21 0x00005638f930c597 in mysql_parse (thd=0x7f7ec53bd070, rawbuf=0x7f7eb80fe088 "select count(*) from t where d ='2017-09-15'", length=44, parser_state=0x7f7ed1f42460) at /home/jan/mysql/10.1-bugs/sql/sql_parse.cc:7333 #22 0x00005638f92faeb5 in dispatch_command (command=COM_QUERY, thd=0x7f7ec53bd070, packet=0x7f7ec7378071 "select count(*) from t where d ='2017-09-15'", packet_length=44) at /home/jan/mysql/10.1-bugs/sql/sql_parse.cc:1484 #23 0x00005638f92f9c87 in do_command (thd=0x7f7ec53bd070) at /home/jan/mysql/10.1-bugs/sql/sql_parse.cc:1106 #24 0x00005638f9431f9d in do_handle_one_connection (thd_arg=0x7f7ec53bd070) at /home/jan/mysql/10.1-bugs/sql/sql_connect.cc:1349 #25 0x00005638f9431ce3 in handle_one_connection (arg=0x7f7ec53bd070) at /home/jan/mysql/10.1-bugs/sql/sql_connect.cc:1261 #26 0x00005638f985ea13 in pfs_spawn_thread (arg=0x7f7ec73adbf0) at /home/jan/mysql/10.1-bugs/storage/perfschema/pfs.cc:1860 #27 0x00007f7ed1c60494 in start_thread (arg=0x7f7ed1f43b00) at pthread_create.c:333 #28 0x00007f7ecf2bbaff in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

Marko Mäkelä added a comment - 2017-09-21 11:40

This looks like it could be a duplicate of ~~MDEV-13668~~, which reports that the bug fix in ~~MDEV-9469~~ was incorrect.

Marko Mäkelä added a comment - 2017-09-21 11:40 This looks like it could be a duplicate of MDEV-13668 , which reports that the bug fix in MDEV-9469 was incorrect.

Jan Lindström (Inactive) added a comment - 2017-09-22 12:27

https://github.com/MariaDB/server/commit/53091385f7a9d965998d0add6edcbd65ab84db04

Jan Lindström (Inactive) added a comment - 2017-09-22 12:27 https://github.com/MariaDB/server/commit/53091385f7a9d965998d0add6edcbd65ab84db04

Jan Lindström (Inactive) added a comment - 2017-09-22 16:28

Update: above fixes also:

commit aa2340e91030e02c286f105c52985528bc4af66a

Author: Jan Lindström <jan.lindstrom@mariadb.com>

Date:   Fri Sep 22 19:26:26 2017 +0300

    MDEV-13640: ALTER TABLE CHANGE and ADD INDEX on auto_increment column fails with "Incorrect key file for table..."

    Previous fixes fix also this case. Added test case.

Jan Lindström (Inactive) added a comment - 2017-09-22 16:28 Update: above fixes also: commit aa2340e91030e02c286f105c52985528bc4af66a Author: Jan Lindström <jan.lindstrom@mariadb.com> Date: Fri Sep 22 19:26:26 2017 +0300 MDEV-13640: ALTER TABLE CHANGE and ADD INDEX on auto_increment column fails with "Incorrect key file for table..." Previous fixes fix also this case. Added test case.

Marko Mäkelä added a comment - 2017-10-09 06:27

The submitted fix appears to revert almost all of the ~~MDEV-7367~~ change (which is the right direction, because that fix looks wrong to me), but it also includes a code change that conflicts with ~~MDEV-13671~~.

Marko Mäkelä added a comment - 2017-10-09 06:27 The submitted fix appears to revert almost all of the MDEV-7367 change (which is the right direction, because that fix looks wrong to me), but it also includes a code change that conflicts with MDEV-13671 .

Marko Mäkelä added a comment - 2017-10-09 06:28

Please revert the ~~MDEV-7367~~ code changes entirely, and do not modify code outside InnoDB or XtraDB. ~~MDEV-13671~~ should be fixed inside InnoDB and XtraDB.

Marko Mäkelä added a comment - 2017-10-09 06:28 Please revert the MDEV-7367 code changes entirely, and do not modify code outside InnoDB or XtraDB. MDEV-13671 should be fixed inside InnoDB and XtraDB.

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.