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

[5 Feb 2010 15:45] Matthew Lord
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)."
[9 Feb 2010 12:28] MySQL Verification Team
Thank you for the bug report. Verified on Windows X64 with source server built.
[2 Mar 2010 20:19] Marko Mäkelä
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.
[17 Mar 2010 12:35] Ramil Kalimullin
Bug #52064 set as a duplicate of this one.
[19 Mar 2010 5:49] Bugs System
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.
[19 Mar 2010 11:16] Bugs System
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.
[6 Apr 2010 8:25] Horst Hunger
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.
[25 Apr 2010 11:09] Bugs System
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.
[5 May 2010 15:17] Bugs System
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)
[13 May 2010 0:21] Paul Dubois
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.
[28 May 2010 6:14] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:42] Bugs System
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)
[28 May 2010 7:10] Bugs System
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)
[29 May 2010 2:25] Paul Dubois
Noted in 5.5.5, 6.0.14 changelogs.
[17 Jun 2010 12:21] Bugs System
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)
[17 Jun 2010 13:08] Bugs System
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)
[17 Jun 2010 13:49] Bugs System
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)