MySQL Bugs: #35220: ALTER TABLE too picky on reserved word "foreign"

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

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)

Thank you for a bug report. Verified just as described.

Hmm... our internal simple parser for FOREIGN KEY ... seems to be too simple.

Assigned to Vasil.

workaround: Rename the table before dropping it

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.

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):

Pushed into 5.1.24-rc

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.

Pushed into 6.0.5-alpha

Noted in 6.0.5 changelog.

Merged into 6.0.6-alpha, according to Tim. Please put it back to queued waiting for pushing into 5.0 (r2475).

Moved 6.0.5 changelog entry to 6.0.6

Setting report to Patch Queued pending push into 5.0.x.

Pushed into 6.0.7-alpha  (revid:timothy.smith@sun.com-20080808002524-u4i8m34lkjj2ouwr) (version source revid:timothy.smith@sun.com-20080813063149-5o63l7vlvbm6nuhm) (pib:3)

Pushed into 5.0.68  (revid:timothy.smith@sun.com-20080808002524-u4i8m34lkjj2ouwr) (version source revid:timothy.smith@sun.com-20080813061111-bt9vnarlk41r3yif) (pib:3)

Pushed into 5.1.28  (revid:timothy.smith@sun.com-20080808002524-u4i8m34lkjj2ouwr) (version source revid:timothy.smith@sun.com-20080813061206-ew8rffhoqwi9wm5p) (pib:3)

Noted in 5.0.68 changelog.

Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)

Pushed into 6.0.7-alpha  (revid:timothy.smith@sun.com-20080808002524-u4i8m34lkjj2ouwr) (version source revid:hakan@mysql.com-20080725175322-8wgujj5xuzrjz3ke) (pib:3)

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)

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

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:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

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

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)