MySQL Bugs: #50946: fast index creation still seems to copy the table

Bug #50946	fast index creation still seems to copy the table
Submitted:	5 Feb 2010 15:45	Modified:	18 Jun 2010 1:21
Reporter:	Matthew Lord	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.1.42	OS:	Linux
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
|  5 | root | localhost | test | Query   |  518 | copy to tmp table | create index cnt_index on itest(cnt) |

-rw-rw---- 1 mysql mysql 9.1K 2010-02-04 19:55 itest.frm 
-rw-rw---- 1 mysql mysql 2.5G 2010-02-04 20:08 itest.ibd 
-rw-rw---- 1 mysql mysql 9.1K 2010-02-05 09:29 #sql-219d_5.frm 
-rw-rw---- 1 mysql mysql 1.3G 2010-02-05 09:38 #sql-219d_5.ibd 

CREATE TABLE `itest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `device_time_id` int(10) unsigned NOT NULL,
  `customer_Id` int(10) unsigned NOT NULL,
  `device_id` int(10) unsigned NOT NULL,
  `event_id` int(10) unsigned NOT NULL,
  `industry_info_id` int(10) unsigned NOT NULL,
  `event_response_id` int(10) unsigned NOT NULL,
  `protocol_id` tinyint(11) unsigned NOT NULL,
  `src_location_id` int(10) unsigned NOT NULL,
  `src_ip` int(10) unsigned NOT NULL DEFAULT '0',
  `dst_ip` int(10) unsigned NOT NULL DEFAULT '0',
  `dst_port_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `priority_id` tinyint(11) unsigned NOT NULL,
  `iss_time_id` int(10) unsigned NOT NULL,
  `cnt` int(10) unsigned NOT NULL DEFAULT '0',
  `first_time_id` int(10) unsigned NOT NULL,
  `last_time_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `device_time_id` (`device_time_id`,`customer_Id`),
  KEY `iss_time_id` (`iss_time_id`),
  KEY `customer_Id` (`customer_Id`),
  KEY `ids_daily_idx_1` (`event_id`),
  KEY `ids_daily_idx_2` (`src_ip`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

How to repeat:
## -- start mysqld with the innodb plugin enabled along with barracuda
## -- format just to be triple sure that the plugin is used.

use test;

CREATE TABLE `itest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `device_time_id` int(10) unsigned NOT NULL,
  `customer_Id` int(10) unsigned NOT NULL,
  `device_id` int(10) unsigned NOT NULL,
  `event_id` int(10) unsigned NOT NULL,
  `industry_info_id` int(10) unsigned NOT NULL,
  `event_response_id` int(10) unsigned NOT NULL,
  `protocol_id` tinyint(11) unsigned NOT NULL,
  `src_location_id` int(10) unsigned NOT NULL,
  `src_ip` int(10) unsigned NOT NULL DEFAULT '0',
  `dst_ip` int(10) unsigned NOT NULL DEFAULT '0',
  `dst_port_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `priority_id` tinyint(11) unsigned NOT NULL,
  `iss_time_id` int(10) unsigned NOT NULL,
  `cnt` int(10) unsigned NOT NULL DEFAULT '0',
  `first_time_id` int(10) unsigned NOT NULL,
  `last_time_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `device_time_id` (`device_time_id`,`customer_Id`),
  KEY `iss_time_id` (`iss_time_id`),
  KEY `customer_Id` (`customer_Id`),
  KEY `ids_daily_idx_1` (`event_id`),
  KEY `ids_daily_idx_2` (`src_ip`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into itest (id) values (NULL);

##-- shell

cnt=0; while test "$cnt" -lt "20"; do mysql -e "insert into itest (id) select NULL from itest" test; cnt=$(($cnt+1)); done;

create index cnt_index on itest(cnt);

##-- now monitor the DATADIR/test directory and you'll see the temporary .frm
##-- file along with the growing temporary .ibd file 

Suggested fix:
From the documentation we should only need a temporary sort file:
http://www.innodb.com/doc/innodb_plugin-1.0/innodb-create-index.html#innodb-create-index-i...

"To add a secondary index to an existing table, InnoDB scans the table, and sorts the rows using memory buffers and temporary files in order by the value(s) of the secondary index key column(s)."

Thank you for the bug report. Verified on Windows X64 with source server built.

Apparently, MySQL decides that the table needs to be copied even before asking ha_innobase::check_if_incompatible_data():

#0  Field_num::is_equal (this=0x88079f0, new_field=0x87febc0) at field.cc:8887
#1  0x082c940f in compare_tables (thd=0x86c7478, new_db=0x87fda40 "test", new_name=0x87fd830 "itest", create_info=0xb03699f8, table_list=0x87fd868, alter_info=0xb0369adc, order_num=0, order=0x0, ignore=false) at sql_table.cc:5741
#2  mysql_alter_table (thd=0x86c7478, new_db=0x87fda40 "test", new_name=0x87fd830 "itest", create_info=0xb03699f8, table_list=0x87fd868, alter_info=0xb0369adc, order_num=0, order=0x0, ignore=false) at sql_table.cc:6850

(gdb) p new_field->length
$16 = 11
(gdb) p max_display_length()
$17 = 4

This will make the condition (new_field->length <= max_display_length()) fail.

Thus, I am reclassifying this as a MySQL DDL bug and removing the assignment from me.

Bug #52064 set as a duplicate of this one.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/103754

3404 Ramil Kalimullin	2010-03-19
      Fix for bug#50946: fast index creation still seems to copy the table 
      
      Problem: ALTER TABLE ADD INDEX may lead to table copying if there's
      numeric field(s) with non-default display width modificator specified.
      
      Fix: compare numeric field's storage lenghts when we decide whether 
      they can be considered 'equal' for table alteration purposes.
      
      Note: no test case as there's no EXPLAIN ALTER TABLE command.
      Manually tested.
     @ sql/field.cc
        Fix for bug#50946: fast index creation still seems to copy the table
          - check numeric field's pack lengths instead of it's display lenghts
        coparing fiedls equality for table alteration purposes.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/103814

3404 Ramil Kalimullin	2010-03-19
      Fix for bug#50946: fast index creation still seems to copy the table 
      
      Problem: ALTER TABLE ADD INDEX may lead to table copying if there's
      numeric field(s) with non-default display width modificator specified.
      
      Fix: compare numeric field's storage lenghts when we decide whether 
      they can be considered 'equal' for table alteration purposes.
     @ mysql-test/r/alter_table.result
        Fix for bug#50946: fast index creation still seems to copy the table
          - test result.
     @ mysql-test/t/alter_table.test
        Fix for bug#50946: fast index creation still seems to copy the table
          - test case.
     @ sql/field.cc
        Fix for bug#50946: fast index creation still seems to copy the table
          - check numeric field's pack lengths instead of it's display lenghts
        comparing fields equality for table alteration purposes.
     @ sql/sql_table.cc
        Fix for bug#50946: fast index creation still seems to copy the table
          - check compare_tables() result for testing purposes.

I execute the inserted test and an addition test with CREATE INDEX in 5.1-bugteam, which also run into the assertion. I haven't seen a temporary index file anymore, so that the patch can be pushed.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/106459

3357 Ramil Kalimullin	2010-04-25
      Fix for bug#50946: fast index creation still seems to copy the table 
      
      Problem: ALTER TABLE ADD INDEX may lead to table copying if there's
      numeric field(s) with non-default display width modificator specified.
      
      Fix: compare numeric field's storage lenghts when we decide whether 
      they can be considered 'equal' for table alteration purposes.
     @ mysql-test/r/error_simulation.result
        Fix for bug#50946: fast index creation still seems to copy the table
          - test result.
     @ mysql-test/t/error_simulation.test
        Fix for bug#50946: fast index creation still seems to copy the table
          - test case.
     @ sql/field.cc
        Fix for bug#50946: fast index creation still seems to copy the table
          - check numeric field's pack lengths instead of it's display lenghts
        comparing fields equality for table alteration purposes.
     @ sql/sql_table.cc
        Fix for bug#50946: fast index creation still seems to copy the table
          - check compare_tables() result for testing purposes.

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:ramil@mysql.com-20100425110640-iy0jbnovtsshm0tl) (merge vers: 5.1.47) (pib:16)

Noted in 5.1.47 changelog.

InnoDB fast index creation could incorrectly use a table copy in some
cases.  

Setting report to Need Merge pending further pushes.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100429203306-tg0wz4y2xyx8edrl) (merge vers: 5.5.5-m3) (pib:16)

Noted in 5.5.5, 6.0.14 changelogs.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)