Bug #46000 using index called GEN_CLUST_INDEX crashes server
Submitted: 7 Jul 2009 10:57 Modified: 18 Jun 2010 22:58
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0.84, 5.1.37 OS:Any
Assigned to: Satya B CPU Architecture:Any
Tags: GEN_CLUST_INDEX
Triage: Triaged: D1 (Critical)

[7 Jul 2009 10:57] Shane Bester
Description:
Version: '5.1.37-debug'  socket: ''  port: 3306  Source distribution
090707 12:55:42  InnoDB: Assertion failure in thread 6996 in file .\row\row0sel.c line 2246
InnoDB: Failing assertion: key_len == DATA_ROW_ID_LEN
InnoDB: We intentionally generate a memory trap.

mysqld.exe!row_sel_convert_mysql_key_to_innobase()[row0sel.c:2246]
mysqld.exe!ha_innobase::records_in_range()[ha_innodb.cc:5830]
mysqld.exe!check_quick_keys()[opt_range.cc:7561]
mysqld.exe!check_quick_select()[opt_range.cc:7336]
mysqld.exe!get_key_scans_params()[opt_range.cc:4871]
mysqld.exe!SQL_SELECT::test_quick_select()[opt_range.cc:2378]
mysqld.exe!get_quick_record_count()[sql_select.cc:2435]
mysqld.exe!make_join_statistics()[sql_select.cc:2844]
mysqld.exe!JOIN::optimize()[sql_select.cc:955]
mysqld.exe!mysql_select()[sql_select.cc:2372]
mysqld.exe!handle_select()[sql_select.cc:268]
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:5013]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2208]
mysqld.exe!mysql_parse()[sql_parse.cc:5933]
mysqld.exe!dispatch_command()[sql_parse.cc:1213]
mysqld.exe!do_command()[sql_parse.cc:854]
mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:293]
mysqld.exe!_threadstart()[thread.c:277]
kernel32.dll!FlsSetValue()

How to repeat:
drop table if exists `a`;
create table `a`(`id` int,key `GEN_CLUST_INDEX`(`id`))engine=innodb;
select * from `a` where `id`=1;
[7 Jul 2009 11:02] Valeriy Kravchuk
Thank you for the bug report. Verified just as described...
[8 Jul 2009 15:05] Mikhail Izioumtchenko
Vasil, could you have a look? Valeriy, all three DWI triage value seem exaggerated,
how is this Critical - unaccepatable - substantial?
[9 Jul 2009 9:57] Calvin Sun
also see bug#44369
[21 Aug 2009 0:16] Jimmy Yang
InnoDB creates a default primary index called "GEN_CLUST_INDEX" in create_clustered_index_when_no_primary(). If you create another index also called "GEN_CLUST_INDEX", both indices created successfully, but with the same name.

When you do a select, the chosen index's metadata structure is picked by its name in ha_innobase::records_in_range():

 index = dict_table_get_index_noninline(prebuilt->table, key->name); <== key->name is the index name.

In our case, it should select the user index, however, it got the default primary index's metadata.

The default primary index has 4 columns (DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR, id), the user index has 2 columns (id, DB_ROW_ID). Thus there is a column length assertion failure in row_sel_convert_mysql_key_to_innobase(), for the column mismatch

row_sel_convert_mysql_key_to_innobase
{
....
       if (dfield_get_type(dfield)->mtype == DATA_SYS) {
                /* A special case: we are looking for a position in the
                generated clustered index which InnoDB automatically added
                to a table with no primary key: the first and the only
                ordering column is ROW_ID which InnoDB stored to the key_ptr
                buffer. */

                ut_a(key_len == DATA_ROW_ID_LEN); <====== key_len is 5.

                dfield_set_data(dfield, key_ptr, DATA_ROW_ID_LEN);

                dtuple_set_n_fields(tuple, 1);

                return;
        }

My understanding is the Innodb does not allow duplicated index name on the same table. Otherwise, it will be problem. So there will be a check on dup index during create index time and that should include "GEN_CLUST_INDEX".
[10 Sep 2009 0:51] Jimmy Yang
We will block create any user index with "GEN_CLUST_INDEX" name. 

However during the investigation, there seems an issue in mysql_alter_table() when mysql_create_table_no_lock() call fails. The temp table are not dropped when create index operation fails. This prevents all subsequent create index operations:

mysql> create index GEN_CLUST_INDEX on a(id);
ERROR 1005 (HY000): Can't create table 'test.#sql-62fe_1' (errno: -1)
 
If we do not drop the temp table table 'test.#sql-62fe_1', all subseqent create index will fail.

mysql> create index idx on a(id);
ERROR 1005 (HY000): Can't create table 'test.#sql-62fe_1' (errno: 121)

In fact, if we make small change in mysql_alter_table(), it shall fix this:

mysql_alter_table()
{
  error= mysql_create_table_no_lock(thd, new_db, tmp_name,
                                    create_info,
                                    alter_info,
                                    1, 0);
  reenable_binlog(thd);
  if (error)
-    goto err;
+    goto err1 

}

Of course, there could be reasons that the code "goto err" instead of "goto err1" is being used. However, in our case, the temp table did not get dropped as it should be.

We will drop this temp table in our innodb code. If any changes from mysql layer that does not require us to do the drop, please inform us.

Thanks
Jimmy
[5 Oct 2009 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/85734

3155 Satya B	2009-10-05
      Applying InnoDB snapshot 5.1-ss5921, Part 1. Fixes BUG#46000
      
      1. BUG#46000 - using index called GEN_CLUST_INDEX crashes server
      
      Detailed revision comments:
      
      r5895 | jyang | 2009-09-15 03:39:21 +0300 (Tue, 15 Sep 2009) | 5 lines
      branches/5.1: Disallow creating index with the name of
      "GEN_CLUST_INDEX" which is reserved for the default system
      primary index. (Bug #46000) rb://149 approved by Marko Makela.
[6 Oct 2009 9:00] Bugs System
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:joro@sun.com-20091006071047-omas21geg3vdx043) (merge vers: 5.1.40) (pib:11)
[6 Oct 2009 9:30] Satya B
Patch queued to mysql-5.1-pe-stage. Bug still exists in 5.1 Innodb
plugin/ 5.4 Innobase

Disabled the test for Innodb Plugin temporarily until the patch for
Innodb Plugin is applied.
[14 Oct 2009 14:40] Bugs System
Pushed into 5.1.41 (revid:joro@sun.com-20091014143611-cphb0enjlx6lpat1) (version source revid:satya.bn@sun.com-20091009125218-w3xkjtkhtqret6l1) (merge vers: 5.1.40) (pib:13)
[14 Oct 2009 15:43] Paul Dubois
Noted in 5.1.40 changelog.

InnoDB did not disallow creation of an index with the name
GEN_CLUST_INDEX, which is used internally.

Setting report to NDI pending push into 5.5.x+.
[22 Oct 2009 6:34] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)
[22 Oct 2009 7:05] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091019131022-2o2ymjfjjoraq833) (merge vers: 5.5.0-beta) (pib:13)
[27 Oct 2009 15:28] Jimmy Yang
Fix has been ported to plugin with slight different changes, due to different code path (which uses ha_innobase::add_index())for creating index in plug when comparing to 5.1.

Thanks
Jimmy
[4 Nov 2009 9:25] Bugs System
Pushed into 5.1.41 (revid:joro@sun.com-20091104092152-qz96bzlf2o1japwc) (version source revid:kristofer.pettersson@sun.com-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)
[5 Nov 2009 1:52] Paul Dubois
Noted in 5.1.41 changelog.

Setting report to NDI pending push to 5.5.x.
[11 Nov 2009 6:51] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091110093407-rw5g8dys2baqkt67) (version source revid:alik@sun.com-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)
[11 Nov 2009 6:59] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091109115615-nuohp02h8mdrz8m2) (version source revid:svoj@sun.com-20091105122958-jyqjx9xus8v4e0yd) (merge vers: 5.5.0-beta) (pib:13)
[11 Nov 2009 14:38] Paul Dubois
Noted in 5.5.0, 6.0.14 changelogs.
[25 Nov 2009 9:59] 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/91554

2852 Satya B	2009-11-25
      Applying InnoDB snapshot 5.0-ss6230, part 2. Fixes BUG#46000
      
      BUG#46000 - using index called GEN_CLUST_INDEX crashes server
      
      Detailed revision comments:
      
      r6180 | jyang | 2009-11-17 10:54:57 +0200 (Tue, 17 Nov 2009) | 7 lines
      branches/5.0: Merge/Port fix for bug #46000 from branches/5.1
      -r5895 to branches/5.0. Disallow creating index with the
      name of "GEN_CLUST_INDEX" which is reserved for the default
      system primary index. Minor adjusts on table name screening
      format for added tests.
[2 Dec 2009 8:01] Bugs System
Pushed into 5.0.89 (revid:joro@sun.com-20091202075830-mzl79q7mc1v72pf1) (version source revid:satya.bn@sun.com-20091125095925-871384fcnwwa2yqt) (merge vers: 5.0.89) (pib:13)
[2 Dec 2009 8:05] Bugs System
Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:davi.arnaut@sun.com-20091125130912-d7hrln14ef7y5d7i) (merge vers: 5.1.42) (pib:13)
[15 Dec 2009 3:02] Paul Dubois
Noted in 5.0.89 changelog.

Already fixed in 5.1.x.
[16 Dec 2009 8:37] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:44] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091126114659-f3imubfuye9fn7qp) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:50] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[18 Dec 2009 10:31] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:47] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:02] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:16] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[12 Mar 2010 14:09] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:25] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:39] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[5 May 2010 15:11] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 17:45] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[28 May 2010 5:48] 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:18] 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 6:45] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 15:11] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[15 Jun 2010 8:13] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:28] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 11:48] 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 12:26] 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:13] 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)