Bug #35220 ALTER TABLE too picky on reserved word "foreign"
Submitted: 11 Mar 2008 15:25 Modified: 18 Jun 2010 12:58
Reporter: Daniel Jaenecke Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0, 5.1, 6.0 OS:Any
Assigned to: CPU Architecture:Any
Triage: D3 (Medium)

[11 Mar 2008 15:25] Daniel Jaenecke
Description:
ALTER TABLE fails on DROPing columns which start with "foreign"; for other reserved words this is no problem. This does not occur for MyISAM tables but for InnoDB.

Additionally the resulting error message is confusing (wrong?) and not helpful at all.

How to repeat:
CREATE TABLE foo( id INT NOT NULL, foreign_col INT NOT NULL, join_col INT NOT NULL, col_foreign INT NOT NULL ) Engine=InnoDB;
-- Query OK, 0 rows affected (0.00 sec)

ALTER TABLE foo DROP join_col;
-- Query OK, 0 rows affected (0.01 sec)

ALTER TABLE foo DROP col_foreign;
-- Query OK, 0 rows affected (0.00 sec)

ALTER TABLE foo DROP foreign_col;
-- ERROR 1025 (HY000): Error on rename of '[snip]/foo' to '[snip]/#sql2-2eb9-5ddd77' (errno: 152)

ALTER TABLE foo DROP `foreign_col`;
-- Query OK, 0 rows affected (0.00 sec)
[11 Mar 2008 21:03] Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[12 Mar 2008 17:05] Heikki Tuuri
Hmm... our internal simple parser for FOREIGN KEY ... seems to be too simple.

Assigned to Vasil.
[13 Mar 2008 20:49] Omer Barnir
workaround: Rename the table before dropping it
[13 Mar 2008 21:52] Daniel Jaenecke
This bug is not about dropping the table but columns in it; and it works by enclosing the column name in ``, so there is no such workaround required. 
Still this is unexpected behavior and especially the resulting error message is extremly confusing.
[27 Mar 2008 14:14] 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/44514

ChangeSet@1.2582, 2008-03-27 15:13:10+01:00, df@pippilotta.erinye.com +11 -0
  apply snapshot innodb-5.1-ss2387
  
  ------------------------------------------------------------------------
  r2361 | sunny | 2008-03-12 09:08:09 +0200 (Wed, 12 Mar 2008) | 3 lines
  Changed paths:
     M /branches/5.1/include/srv0srv.h
     M /branches/5.1/os/os0file.c
     M /branches/5.1/srv/srv0srv.c
     M /branches/5.1/srv/srv0start.c
  
  branches/5.1: Remove the innodb_flush_method fdatasync option since it was
  not being used and there was a potential it could mislead users.
  
  ------------------------------------------------------------------------
  r2367 | marko | 2008-03-17 10:23:03 +0200 (Mon, 17 Mar 2008) | 5 lines
  Changed paths:
     M /branches/5.1/handler/ha_innodb.cc
  
  branches/5.1: ha_innobase::check_if_incompatible_data(): Check
  HA_CREATE_USED_ROW_FORMAT before comparing row_type.  Previously,
  the comparison was incorrectly guarded by the presence of an
  AUTO_INCREMENT attribute.
  
  ------------------------------------------------------------------------
  r2374 | vasil | 2008-03-18 09:35:30 +0200 (Tue, 18 Mar 2008) | 11 lines
  Changed paths:
     M /branches/5.1/dict/dict0dict.c
     A /branches/5.1/mysql-test/innodb_bug35220.result
     A /branches/5.1/mysql-test/innodb_bug35220.test
  
  branches/5.1:
  
  Fix Bug#35220 ALTER TABLE too picky on reserved word "foreign".
  
  In ALTER TABLE, change the internal parser to search for
  ``FOREIGN[[:space:]]'' instead of only ``FOREIGN'' when parsing
  ALTER TABLE ... DROP FOREIGN KEY ...; otherwise it could be mistaken
  with ALTER TABLE ... DROP foreign_col;
  
  Approved by:    Heikki
  
  ------------------------------------------------------------------------
  r2379 | vasil | 2008-03-19 18:48:00 +0200 (Wed, 19 Mar 2008) | 10 lines
  Changed paths:
     M /branches/5.1/os/os0file.c
  
  branches/5.1:
  
  Fix Bug#34823:
  fsync() occasionally returns ENOLCK and causes InnoDB to restart mysqld
  
  Create a wrapper to fsync(2) that retries the operation if the error is
  ENOLCK. Use that wrapper instead of fsync(2).
  
  Approved by:    Heikki
  
  ------------------------------------------------------------------------
  r2380 | sunny | 2008-03-21 05:03:56 +0200 (Fri, 21 Mar 2008) | 9 lines
  Changed paths:
     M /branches/5.1/include/trx0undo.h
     M /branches/5.1/trx/trx0trx.c
     M /branches/5.1/trx/trx0undo.c
  
  branches/5.1: Fix for Bug# 35352. We've added a heuristic that checks
  the size of the UNDO slots cache lists (insert and upate). If either of
  cached lists has more than 500 entries then we add any UNDO slots that are
  freed, to the common free list instead of the cache list, this is to avoid
  the case where all the free slots end up in only one of the lists on startup
  after a crash.
  
  Tested with test case for 26590 and passes all mysql-test(s).
  
  ------------------------------------------------------------------------
  r2383 | vasil | 2008-03-26 09:35:22 +0200 (Wed, 26 Mar 2008) | 4 lines
  Changed paths:
     M /branches/5.1/include/row0mysql.h
  
  branches/5.1:
  
  Fix typo in comment.
  
  ------------------------------------------------------------------------
  r2384 | vasil | 2008-03-26 18:26:54 +0200 (Wed, 26 Mar 2008) | 20 lines
  Changed paths:
     A /branches/5.1/mysql-test/innodb_bug34300.result
     A /branches/5.1/mysql-test/innodb_bug34300.test
     M /branches/5.1/row/row0sel.c
  
  branches/5.1:
  
  Fix Bug#34300 Tinyblob & tinytext fields currupted after export/import and alter in 5.1
  
  Copy the BLOB fields, that are stored internally, to a safe place
  (prebuilt->blob_heap) when converting a row from InnoDB format to
  MySQL format in row_sel_store_mysql_rec().
  
  The bug was introduced in:
  
   ------------------------------------------------------------------------
   r587 | osku | 2006-05-23 15:35:58 +0300 (Tue, 23 May 2006) | 3 lines
  
   Optimize BLOB selects by using prebuilt->blob_heap directly instead of first
   reading BLOB data to a temporary heap and then copying it to
   prebuilt->blob_heap.
   ------------------------------------------------------------------------
  
  Approved by:    Heikki
  
  ------------------------------------------------------------------------
  r2386 | vasil | 2008-03-27 07:45:02 +0200 (Thu, 27 Mar 2008) | 22 lines
  Changed paths:
     M /branches/5.1/mysql-test/innodb.result
  
  branches/5.1:
  
  Merge change from MySQL (this fixes the failing innodb test):
[31 Mar 2008 14:53] Bugs System
Pushed into 5.1.24-rc
[1 Apr 2008 19:00] Paul Dubois
Noted in 5.1.24 changelog.

For InnoDB tables, ALTER TABLE DROP failed if the name of the column
to be dropped began with "foreign".

Resetting report to Patch queued waiting for push into 6.0.x.
[3 Apr 2008 13:01] Bugs System
Pushed into 6.0.5-alpha
[7 Apr 2008 17:33] Paul Dubois
Noted in 6.0.5 changelog.
[24 Jun 2008 21:44] Calvin Sun
Merged into 6.0.6-alpha, according to Tim. Please put it back to queued waiting for pushing into 5.0 (r2475).
[25 Jun 2008 23:25] Paul Dubois
Moved 6.0.5 changelog entry to 6.0.6

Setting report to Patch Queued pending push into 5.0.x.
[13 Aug 2008 8:28] Bugs System
Pushed into 6.0.7-alpha  (revid:timothy.smith@sun.com-20080808002524-u4i8m34lkjj2ouwr) (version source revid:timothy.smith@sun.com-20080813063149-5o63l7vlvbm6nuhm) (pib:3)
[13 Aug 2008 8:34] Bugs System
Pushed into 5.0.68  (revid:timothy.smith@sun.com-20080808002524-u4i8m34lkjj2ouwr) (version source revid:timothy.smith@sun.com-20080813061111-bt9vnarlk41r3yif) (pib:3)
[13 Aug 2008 8:37] Bugs System
Pushed into 5.1.28  (revid:timothy.smith@sun.com-20080808002524-u4i8m34lkjj2ouwr) (version source revid:timothy.smith@sun.com-20080813061206-ew8rffhoqwi9wm5p) (pib:3)
[13 Aug 2008 15:45] Paul Dubois
Noted in 5.0.68 changelog.
[28 Aug 2008 20:15] Bugs System
Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)
[13 Sep 2008 21:05] Bugs System
Pushed into 6.0.7-alpha  (revid:timothy.smith@sun.com-20080808002524-u4i8m34lkjj2ouwr) (version source revid:hakan@mysql.com-20080725175322-8wgujj5xuzrjz3ke) (pib:3)
[5 May 2010 15:10] 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 14:07] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[28 May 2010 6:04] 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:33] 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:01] 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 23:19] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 12:08] 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:55] 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:36] 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)