Bug #44369 InnoDB: Does not uniformly disallow disallowed column names

• Submitted: 20 Apr 2009 18:37
• Modified: 20 Jun 2010 1:04
• Reporter: Paul DuBois
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 4.1+
• OS: Any
• Assigned to: Satya B
• CPU Architecture: Any

[20 Apr 2009 18:37] Paul DuBois

Description:
See Bug#18934, which caused InnoDB to reject tables containing columns with names that match the names of internal columns, such as DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR and DB_MIX_ID.

But it's possible to use lowercase versions of those column names and InnoDB does not complain:

mysql> create table t (DB_ROW_ID int) engine innodb;
ERROR 1005 (HY000): Can't create table 'test.t' (errno: -1)

mysql> create table t (db_row_id int) engine innodb;
Query OK, 0 rows affected (0.13 sec)

But column names are not case sensitive, so this is a bug.

How to repeat:
See above.

[21 Apr 2009 4:10] Valeriy Kravchuk

Thank you for the bug report.

[21 Apr 2009 13:28] Mikhail Izioumtchenko

Vasil, please have a look

[27 Apr 2009 11:40] Marko Mäkelä

Column names may be case insensitive in the MySQL layer, but inside the InnoDB storage engine, they are case sensitive.

You know, there are two different data dictionaries: the *.frm files form the MySQL data dictionary, and the system tables inside the InnoDB system tablespace (ibdata* files) form the InnoDB data dictionary.

I think that this could be best addressed in the documentation. It would be a pointless artificial limitation to prevent the creation of lower-case or mixed-case column names Db_tRx_Id etc. when only the upper-case column names DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID are confusing InnoDB.

[27 Apr 2009 15:49] Paul DuBois

Per Marko's comment, I am changing this to a documentation bug and assigning it to myself.

[27 Apr 2009 15:52] Paul DuBois

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

[28 Apr 2009 7:18] Marko Mäkelä

Sergei Golubchik pointed out that InnoDB could internally convert the reserved column names to lower case. Assuming that column names are case-insensitive in the MySQL data dictionary, this should not have any bad consequences or side effects. We would have to be careful with the foreign key constraints (parser and error reporting).

[28 Apr 2009 10:55] Sergei Golubchik

reopened, because of last comment.

[22 Aug 2009 0:37] Jimmy Yang

The strcmp in dict_col_name_is_reserved() shall be changed to stricmp()to make them case insensitive.

[24 Aug 2009 18:55] Jimmy Yang

There is still a debate on what we should do in this case, and a decision need to be made for this case. we have 2 options:

1. Blocking creating columns with reserved name in case insensitive way. This seems to be the request from the bug report.

Since mysql's column is case insensitive, it would be confusing for user if we block only capitalized reserved name from innodb, and allow lower cased name to be used. So block creating columns with innodb reserved names case insensitively:

The change involves using innobase_strcasecmp() to substitute strcmp() in dict_col_name_is_reserved() of dict0dict.c.

2. Keep the behavior as it is now. Since blocking the name case insensitively might be a overkill, block more than necessary.

This will be discussed in the next bug triage meeting. And a decision to be made for which way to go.

thanks
Jimmy

[5 Oct 2009 11:18] 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/85738

3156 Satya B	2009-10-05
      Applying InnoDB snapshot 5.1-ss5921, part 2. Fixes BUG#44369
      
      BUG#44369 - InnoDB: Does not uniformly disallow disallowed column names
      
      Detailed revision comments:
      
      r5741 | jyang | 2009-09-03 07:16:01 +0300 (Thu, 03 Sep 2009) | 5 lines
      branches/5.1: Block creating table with column name conflicting
      with Innodb reserved key words. (Bug #44369) rb://151 approved
      by Sunny Bains.
      
      
      r5760 | jyang | 2009-09-04 07:07:34 +0300 (Fri, 04 Sep 2009) | 3 lines
      branches/5.1: This is to revert change 5741. A return status for
      create_table_def() needs to be fixed.
      
      r5834 | jyang | 2009-09-11 00:43:05 +0300 (Fri, 11 Sep 2009) | 5 lines
      branches/5.1: Block creating table with column name conflicting
      with Innodb reserved key words. (Bug #44369) rb://151 approved
      by Sunny Bains.

[6 Oct 2009 9:00] Bugs System

Pushed into 5.1.40 (revid:[e-mail]-20091006073316-lea2cpijh9r6on7c) (version source revid:[e-mail]-20091006071047-omas21geg3vdx043) (merge vers: 5.1.40) (pib:11)

[6 Oct 2009 9:23] 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:39] Bugs System

Pushed into 5.1.41 (revid:[e-mail]-20091014143611-cphb0enjlx6lpat1) (version source revid:[e-mail]-20091009125218-w3xkjtkhtqret6l1) (merge vers: 5.1.40) (pib:13)

[14 Oct 2009 15:48] Paul DuBois

Noted in 5.1.40 changelog.

InnoDB did not always disallow creating tables containing columns
with names that match the names of internal columns, such as
DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR, and DB_MIX_ID.

Setting report to NDI pending push into 5.5.x+.

[22 Oct 2009 6:35] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091022063126-l0qzirh9xyhp0bpc) (version source revid:[e-mail]-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)

[22 Oct 2009 7:08] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091022060553-znkmxm0g0gm6ckvw) (version source revid:[e-mail]-20091019131022-2o2ymjfjjoraq833) (merge vers: 5.5.0-beta) (pib:13)

[22 Oct 2009 20:08] Paul DuBois

Noted in 5.5.0, 6.0.14 changelogs.

[18 Dec 2009 10:35] Bugs System

Pushed into 5.1.41-ndb-7.1.0 (revid:[e-mail]-20091218102229-64tk47xonu3dv6r6) (version source revid:[e-mail]-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

[18 Dec 2009 10:50] Bugs System

Pushed into 5.1.41-ndb-6.2.19 (revid:[e-mail]-20091218100224-vtzr0fahhsuhjsmt) (version source revid:[e-mail]-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

[18 Dec 2009 11:05] Bugs System

Pushed into 5.1.41-ndb-6.3.31 (revid:[e-mail]-20091218100616-75d9tek96o6ob6k0) (version source revid:[e-mail]-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

[18 Dec 2009 11:20] Bugs System

Pushed into 5.1.41-ndb-7.0.11 (revid:[e-mail]-20091218101303-ga32mrnr15jsa606) (version source revid:[e-mail]-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

[5 May 2010 15:16] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 17:50] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[28 May 2010 6:02] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:31] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 6:59] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 23:09] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[15 Jun 2010 8:15] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:32] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615080558-cw01bzdqr1bdmmec) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (pib:16)

[17 Jun 2010 12:06] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:51] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:33] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)