Bug #78020 ALTER TABLE sorting skipped when change PK and drop last column of old PK
Submitted: 11 Aug 2015 11:52 Modified: 27 Aug 2015 17:42
Reporter: zhang yingqiang (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.7.8, 5.7.9 OS:Any
Assigned to: CPU Architecture:Any
Tags: online ddl; sorting skipped; change PK

[11 Aug 2015 11:52] zhang yingqiang 
Description:
in MySQL 5.7.8 we fix Bug#21103101 which was introduced in MySQL 5.7.5   
    http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-8.html
But this bugfix was not completely perfect. In this scenario, the root cause is as follows.
    In function innobase_pk_order_preserved， if the last column of old PK is dropped, we only mark pk_col_dropped, without judging the corresponding new column, and return true(skip order). The "SINGLE COLUMN PRIMARY KEY" in Bug#21103101 is just a special case.

How to repeat:
#case 2
create table t1(a int not null, b int not null, c int not null, primary key(a,b)) engine = innodb;

insert into t1 select 1,1,2;
insert into t1 select 1,2,1;

#alter table t1 drop primary key, add primary key(a,c), drop b, lock=none;

show create table t1;
select * from t1;

#case 3
drop table t1;
create table t1(a int not null, b int not null, c int not null, primary key(a,b)) engine = innodb;

insert into t1 select 1,1,2;
insert into t1 select 2,2,1;

alter table t1 drop a, drop b, add primary key(c), lock=none;

show create table t1;
select * from t1;

drop table t1;

Suggested fix:
diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
index 12abfa5..de3b49d 100644
--- a/storage/innobase/handler/handler0alter.cc
+++ b/storage/innobase/handler/handler0alter.cc
@@ -2902,13 +2902,7 @@ innobase_pk_order_preserved(
 
                        old_field++;
                        new_field++;
-               } else if (old_col_no == ULINT_UNDEFINED) {
-
-                       if (old_n_fields == 1) {
-                               /* Dropping single column primary key
-                               requires sorting. */
-                               return(false);
-                       }
+               } else if (old_col_no == ULINT_UNDEFINED && old_field + 1 < old_n_fields) {
 
                        pk_col_dropped = true;
                        old_field++;
[11 Aug 2015 12:06] zhang yingqiang 
should remove the # in case 2's DDL sql like this:

#case 2
create table t1(a int not null, b int not null, c int not null, primary
key(a,b)) engine = innodb;

insert into t1 select 1,1,2;
insert into t1 select 1,2,1;

alter table t1 drop primary key, add primary key(a,c), drop b,
lock=none;

show create table t1;
select * from t1;

#case 3
drop table t1;
create table t1(a int not null, b int not null, c int not null, primary
key(a,b)) engine = innodb;

insert into t1 select 1,1,2;
insert into t1 select 2,2,1;

alter table t1 drop a, drop b, add primary key(c), lock=none;

show create table t1;
select * from t1;

drop table t1;
[11 Aug 2015 12:06] MySQL Verification Team 
Hello Zhang,

Thank you for the report and contribution.
Observed that 5.7.8, 5.7.9 debug builds are affected.

Thanks,
Umesh
[11 Aug 2015 12:06] MySQL Verification Team 
// 5.7.8

// Startup and build details

bin/mysql_install_db --insecure --basedir=/export/umesh/server/binaries/mysql-5.7.8-rc --datadir=/export/umesh/server/binaries/mysql-5.7.8-rc/78020 -v
bin/mysqld-debug --no-defaults --basedir=/export/umesh/server/binaries/mysql-5.7.8-rc --datadir=/export/umesh/server/binaries/mysql-5.7.8-rc/78020 --core-file --socket=/tmp/mysql_ushastry.sock  --port=15000 --log-error=/export/umesh/server/binaries/mysql-5.7.8-rc/78020/log.err 2>&1 &

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8-rc: cat docs/INFO_SRC
commit: ae3b133e5f7b13d1edf7acf7eee6af2c2b4014e2
date: 2015-07-20 14:02:16 +0200
build-date: 2015-07-20 14:16:07 +0200
short: ae3b133
branch: mysql-5.7.8-rc-release

MySQL source 5.7.8

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8-rc: bin/mysql  -uroot -S /tmp/mysql_ushastry.sock                                                                                       Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.8-rc-debug MySQL Community Server - Debug (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop database test;
Query OK, 1 row affected (0.01 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table t1(a int not null, b int not null, c int not null, primary key(a,b)) engine = innodb;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> insert into t1 select 1,1,2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1 select 1,2,1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table t1 drop primary key, add primary key(a,c), drop b, lock=none;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

(gdb) bt
#0  0x00007fcfa3b9d771 in pthread_kill () from /lib64/libpthread.so.0
#1  0x000000000178dcb9 in my_write_core (sig=6) at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/mysys/stacktrace.c:247
#2  0x0000000000dfe1c8 in handle_fatal_signal (sig=6) at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/sql/signal_handler.cc:220
#3  <signal handler called>
#4  0x00007fcfa27a35c9 in raise () from /lib64/libc.so.6
#5  0x00007fcfa27a4cd8 in abort () from /lib64/libc.so.6
#6  0x0000000001acb353 in ut_dbg_assertion_failed (expr=0x21c2a60 "cmp_rec_rec(rec, old_rec, offsets, old_offsets, m_index) > 0",
    file=0x21c2970 "/export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/storage/innobase/btr/btr0bulk.cc", line=181)
    at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/storage/innobase/ut/ut0dbg.cc:67
#7  0x0000000001b1cde0 in PageBulk::insert (this=0x7fcf34023068, rec=0x7fcf340245ad "\200", offsets=0x7fcf340245c8)
    at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/storage/innobase/btr/btr0bulk.cc:180
#8  0x0000000001b1e915 in BtrBulk::insert (this=0x7fcf3400f278, tuple=0x7fcf34a49d18, level=0)
    at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/storage/innobase/btr/btr0bulk.cc:866
#9  0x00000000019b5e56 in BtrBulk::insert (this=0x7fcf3400f278, tuple=0x7fcf34a49d18)
    at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/storage/innobase/include/btr0bulk.h:309
#10 0x00000000019e2091 in row_merge_insert_index_tuples (trx_id=1813, index=0x7fcf34026520, old_table=0x7fcf34044b40, fd=-1, block=0x0, row_buf=0x7fcf34025d40, btr_bulk=0x7fcf3400f278,
    stage=0x0) at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/storage/innobase/row/row0merge.cc:3273
#11 0x00000000019df87b in row_merge_read_clustered_index (trx=0x7fcf9971a8b0, table=0x7fcf3403fb50, old_table=0x7fcf34044b40, new_table=0x7fcf34010a70, online=true, index=0x7fcf34022330,
    fts_sort_idx=0x0, psort_info=0x0, files=0x7fcf3401f4c8, key_numbers=0x7fcf34022338, n_index=1, add_cols=0x0, col_map=0x7fcf34022380, add_autoinc=18446744073709551615, sequence=...,
    block=0x7fcf762b5000 <Address 0x7fcf762b5000 out of bounds>, skip_pk_sort=true, tmpfd=0x7fcf765f00ec, stage=0x7fcf3401f418)
    at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/storage/innobase/row/row0merge.cc:2293
#12 0x00000000019e488a in row_merge_build_indexes (trx=0x7fcf9971a8b0, old_table=0x7fcf34044b40, new_table=0x7fcf34010a70, online=true, indexes=0x7fcf34022330, key_numbers=0x7fcf34022338,
    n_indexes=1, table=0x7fcf3403fb50, add_cols=0x0, col_map=0x7fcf34022380, add_autoinc=18446744073709551615, sequence=..., skip_pk_sort=true, stage=0x7fcf3401f418)
    at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/storage/innobase/row/row0merge.cc:4384
#13 0x00000000018eadab in ha_innobase::inplace_alter_table (this=0x7fcf34044150, altered_table=0x7fcf3403fb50, ha_alter_info=0x7fcf765f1700)
    at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/storage/innobase/handler/handler0alter.cc:5587
#14 0x0000000001513c9d in handler::ha_inplace_alter_table (this=0x7fcf34044150, altered_table=0x7fcf3403fb50, ha_alter_info=0x7fcf765f1700)
    at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/sql/handler.h:3247
#15 0x000000000150bb2f in mysql_inplace_alter_table (thd=0x7fcf34000bc0, table_list=0x7fcf34005e60, table=0x7fcf340437b0, altered_table=0x7fcf3403fb50, ha_alter_info=0x7fcf765f1700,
    inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, target_mdl_request=0x7fcf765f2090, alter_ctx=0x7fcf765f0c60)
    at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/sql/sql_table.cc:7137
#16 0x0000000001510862 in mysql_alter_table (thd=0x7fcf34000bc0, new_db=0x7fcf340063e8 "test", new_name=0x0, create_info=0x7fcf765f2b40, table_list=0x7fcf34005e60, alter_info=0x7fcf765f2c30)
    at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/sql/sql_table.cc:9107
#17 0x000000000166d56f in Sql_cmd_alter_table::execute (this=0x7fcf34006528, thd=0x7fcf34000bc0) at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/sql/sql_alter.cc:316
#18 0x000000000147f33d in mysql_execute_command (thd=0x7fcf34000bc0) at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/sql/sql_parse.cc:4549
#19 0x00000000014812c1 in mysql_parse (thd=0x7fcf34000bc0, parser_state=0x7fcf765f45e0) at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/sql/sql_parse.cc:5255
#20 0x000000000147617b in dispatch_command (thd=0x7fcf34000bc0, com_data=0x7fcf765f4e10, command=COM_QUERY)
    at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/sql/sql_parse.cc:1272
#21 0x0000000001474b8e in do_command (thd=0x7fcf34000bc0) at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/sql/sql_parse.cc:852
#22 0x00000000015a2c18 in handle_connection (arg=0x4a60c90) at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/sql/conn_handler/connection_handler_per_thread.cc:300
#23 0x0000000001819bef in pfs_spawn_thread (arg=0x4b7cc00) at /export/home/pb2/build/sb_0-15961582-1437395640.67/mysql-5.7.8-rc/storage/perfschema/pfs.cc:2178
#24 0x00007fcfa3b98df3 in start_thread () from /lib64/libpthread.so.0
#25 0x00007fcfa286447d in clone () from /lib64/libc.so.6
(gdb)
[11 Aug 2015 12:07] MySQL Verification Team 
// 5.7.9

(gdb) bt
#0  0x00007f66c52dd771 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000001766664 in my_write_core (sig=6) at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/mysys/stacktrace.c:247
#2  0x0000000000e0516e in handle_fatal_signal (sig=6) at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/sql/signal_handler.cc:220
#3  <signal handler called>
#4  0x00007f66c3cd85c9 in raise () from /lib64/libc.so.6
#5  0x00007f66c3cd9cd8 in abort () from /lib64/libc.so.6
#6  0x0000000001ac7383 in ut_dbg_assertion_failed (expr=0x2233c68 "cmp_rec_rec(rec, old_rec, offsets, old_offsets, m_index) > 0",
    file=0x2233b70 "/export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/storage/innobase/btr/btr0bulk.cc", line=181)
    at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/storage/innobase/ut/ut0dbg.cc:67
#7  0x0000000001b18320 in PageBulk::insert (this=0x7f664c926718, rec=0x7f664c924a4d "\200", offsets=0x7f664c924a68)
    at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/storage/innobase/btr/btr0bulk.cc:180
#8  0x0000000001b19da6 in BtrBulk::insert (this=0x7f664c923f38, tuple=0x7f664ca2ddd8, level=0)
    at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/storage/innobase/btr/btr0bulk.cc:866
#9  0x00000000019b34fc in BtrBulk::insert (this=0x7f664c923f38, tuple=0x7f664ca2ddd8)
    at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/storage/innobase/include/btr0bulk.h:309
#10 0x00000000019df33a in row_merge_insert_index_tuples (trx_id=1805, index=0x7f664c927070, old_table=0x7f664c011040, fd=-1, block=0x0, row_buf=0x7f664c926890, btr_bulk=0x7f664c923f38,
    stage=0x0) at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/storage/innobase/row/row0merge.cc:3274
#11 0x00000000019dcb26 in row_merge_read_clustered_index (trx=0x7f66bac4f8b0, table=0x7f664c9216f0, old_table=0x7f664c011040, new_table=0x7f664c01b6a0, online=true, index=0x7f664c922af0,
    fts_sort_idx=0x0, psort_info=0x0, files=0x7f664c9266a8, key_numbers=0x7f664c922af8, n_index=1, add_cols=0x0, col_map=0x7f664c922b40, add_autoinc=18446744073709551615, sequence=...,
    block=0x7f66977fa000 <Address 0x7f66977fa000 out of bounds>, skip_pk_sort=true, tmpfd=0x7f6697b3502c, stage=0x7f664c9265b8)
    at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/storage/innobase/row/row0merge.cc:2294
#12 0x00000000019e1b4c in row_merge_build_indexes (trx=0x7f66bac4f8b0, old_table=0x7f664c011040, new_table=0x7f664c01b6a0, online=true, indexes=0x7f664c922af0, key_numbers=0x7f664c922af8,
    n_indexes=1, table=0x7f664c9216f0, add_cols=0x0, col_map=0x7f664c922b40, add_autoinc=18446744073709551615, sequence=..., skip_pk_sort=true, stage=0x7f664c9265b8)
    at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/storage/innobase/row/row0merge.cc:4385
#13 0x00000000018e8760 in ha_innobase::inplace_alter_table (this=0x7f664c01d650, altered_table=0x7f664c9216f0, ha_alter_info=0x7f6697b36160)
    at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/storage/innobase/handler/handler0alter.cc:5603
#14 0x00000000014f71eb in handler::ha_inplace_alter_table (this=0x7f664c01d650, altered_table=0x7f664c9216f0, ha_alter_info=0x7f6697b36160)
    at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/sql/handler.h:3248
#15 0x00000000014ef3ee in mysql_inplace_alter_table (thd=0x7f664c000bc0, table_list=0x7f664c005eb0, table=0x7f664c020c00, altered_table=0x7f664c9216f0, ha_alter_info=0x7f6697b36160,
    inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, target_mdl_request=0x7f6697b35ba0, alter_ctx=0x7f6697b366f0)
    at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/sql/sql_table.cc:7142
#16 0x00000000014f3ef7 in mysql_alter_table (thd=0x7f664c000bc0, new_db=0x7f664c006438 "test", new_name=0x0, create_info=0x7f6697b37580, table_list=0x7f664c005eb0, alter_info=0x7f6697b374d0)
    at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/sql/sql_table.cc:9118
#17 0x00000000016488ff in Sql_cmd_alter_table::execute (this=0x7f664c006578, thd=0x7f664c000bc0) at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/sql/sql_alter.cc:316
#18 0x0000000001464c65 in mysql_execute_command (thd=0x7f664c000bc0, first_level=true) at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/sql/sql_parse.cc:4593
#19 0x0000000001466bb4 in mysql_parse (thd=0x7f664c000bc0, parser_state=0x7f6697b38df0) at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/sql/sql_parse.cc:5307
#20 0x000000000145c530 in dispatch_command (thd=0x7f664c000bc0, com_data=0x7f6697b39e00, command=COM_QUERY)
    at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/sql/sql_parse.cc:1284
#21 0x000000000145b3c9 in do_command (thd=0x7f664c000bc0) at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/sql/sql_parse.cc:852
#22 0x0000000001585552 in handle_connection (arg=0x3d85cf0) at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/sql/conn_handler/connection_handler_per_thread.cc:300
#23 0x000000000181ce77 in pfs_spawn_thread (arg=0x3ef3190) at /export/home2/pb2/build/sb_0-16070066-1438619274.01/mysqlcom-pro-5.7.9/storage/perfschema/pfs.cc:2191
#24 0x00007f66c52d8df3 in start_thread () from /lib64/libpthread.so.0
#25 0x00007f66c3d9947d in clone () from /lib64/libc.so.6
[20 Aug 2015 6:26] Marko Mäkelä 
I changed the bug title, because this is not limited to online operations (ALTER TABLE...LOCK=NONE). The bug is in an optmization of ALGORITHM=INPLACE. The old-style ALTER TABLE...ALGORITHM=COPY is unaffected by this.
[20 Aug 2015 6:59] zhang yingqiang 
Agree above.
This bug affect online rebuild table scenario.
[27 Aug 2015 2:29] Shaohua Wang 
Posted by developer:
 
Rules we cannot skip sorting:
  1. Removing existing PK columns somewhere else than at the end of the PK;
  2. Adding existing columns to the PK, except at the end of the PK when no columns are removed from the PK;
  3. Changing the order of existing PK columns;
  4. Decreasing the prefix length just like removing existing PK columns follows rule(1), increasing the prefix length just like adding existing
PK columns follows rule(2).
[27 Aug 2015 17:42] Daniel Price 
Posted by developer:
 
Fixed as of the upcoming 5.7.9, 5.8.0 release, and here's the changelog entry:

Sorting was skipped by an ALTER TABLE statement that changed the primary
key and dropped the last column of the previous primary key.