Bug #31231 mysql_alter_table() tries to drop a non-existing table
Submitted: 27 Sep 2007 11:18 Modified: 22 Oct 2008 15:10
Reporter: Marko Mäkelä Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: Martin Skold CPU Architecture:Any

[27 Sep 2007 11:18] Marko Mäkelä
Description:
In smart ALTER TABLE, a .frm file is created for the temporary
table, but the table is not created in the storage engine, because
create_info->frm_only == true.  However, here in mysql_alter_table()
MySQL will attempt to remove the table also from the storage engine:

err1:
  if (new_table)
  {
    /* close_temporary_table() frees the new_table pointer. */
    close_temporary_table(thd, new_table, 1, 1);
  }
  else
    VOID(quick_rm_table(new_db_type, new_db, tmp_name, FN_IS_TMP));

How to repeat:
Implement the smart ALTER TABLE interface in a storage engine.  Note that after calling handler::add_index(), mysql_alter_table() will try to drop a temporary table that it did not create in the first place.

Suggested fix:
Add the parameter bool frm_only to quick_rm_table, and pass create_info->frm_only to it in mysql_alter_table().
[26 Nov 2007 17:21] Heikki Tuuri
Marko, please tell Martin Skold precisely what is wrong.
[30 Nov 2007 13:22] Marko Mäkelä
This bug report should be self-contained.
[8 Jan 2008 8:47] Marko Mäkelä
Suggested fix for Bug #31231

Attachment: bug31231.patch (text/x-diff), 1.48 KiB.

[19 Feb 2008 17:08] Heikki Tuuri
This is needed in 5.1.
[27 Feb 2008 16: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/43079

ChangeSet@1.2649, 2008-02-27 17:07:37+01:00, mskold@mysql.com +5 -0
  mysql_priv.h:
    bug #31231  mysql_alter_table() tries to drop a non-existing table: added FRM_ONLY flag
  ha_ndbcluster.cc:
    bug#31233 mysql_alter_table() fails to drop UNIQUE KEY: Removed check for non-pk tables, not needed when mysql_alter_table checks apropriate flags
  ndb_alter_table3.test, ndb_alter_table3.result:
    bug#31233 mysql_alter_table() fails to drop UNIQUE KEY: added test cases
  sql_table.cc:
    bug #31231  mysql_alter_table() tries to drop a non-existing table
    Don't invoke handler for tables defined with FRM_ONLY flag.
    bug#31233 mysql_alter_table() fails to drop UNIQUE KEY
    When a table is defined without an explicit primary key
    mysql will choose the first found unique index defined over
    non-nullable fields (if such an index exists). This means
    that if such an index is added (the first) or dropped (the last)
    through an alter table, this equals adding or dropping a primary key.
    The implementation for on-line add/drop index did not consider
    this semantics. This patch ensures that only handlers with the
    correctly defined flags (see handler.h for explanation of the flags):
    HA_ONLINE_ADD_PK_INDEX
    HA_ONLINE_ADD_PK_INDEX_NO_WRITES
    HA_ONLINE_DROP_PK_INDEX
    HA_ONLINE_DROP_PK_INDEX_NO_WRITES
    are invoked for such on-line operations. All others handlers must
    perform a full (offline) alter table.
[28 Feb 2008 8:54] Marko Mäkelä
The patch http://lists.mysql.com/commits/43079 seems to fix both Bug #31231 and Bug #31233.
[30 Mar 2008 19:57] Jon Stephens
Fix is available in 5.1.23-ndb-6.3.11.
[19 Jun 2008 6:57] Vasil Dimov
Hmm, this bug is present in MySQL-5.1, the patch was pushed only in the -ndb branch.

Why it was not pushed in 5.1? Any plans to do that?
[3 Jul 2008 9:39] Martin Skold
Since there were bzr problems with the merging, this has to be resolved
before ndb clone can be merged to main trunk.
[17 Sep 2008 12:11] 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/54239

2836 Martin Skold	2008-09-15 [merge]
      Null merge, patch will be merged from mysql-6.0-ndb

 2835 Georgi Kodinov	2008-09-12
       Bug#38342 -backup_myisam1 test fails on Windows
       The problem is that  this test, uses file locking and myisam unlocks 
       the same file region twice. Unix my_lock implementation based on fcntl() 
       does not return error in this case, but Windows UnlockFile() returns 
       ERROR_NOT_LOCKED.
            
       Fixed by ignoring ERROR_NOT_LOCKED from UnlockFile(). This fix does 
       not make myisam  better, but at least the implementation of my_lock() 
       compatible to *nix.
[17 Sep 2008 12:12] 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/54240

2739 Martin Skold	2008-09-15
      bug #31231  mysql_alter_table() tries to drop a non-existing table
      bug#31233 mysql_alter_table() fails to drop UNIQUE KEY
[17 Sep 2008 12:12] 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/54241

2836 Martin Skold	2008-09-15 [merge]
      Null merge, patch will be merged from mysql-6.0-ndb

-- 
MySQL Code Commits Mailing List
For list archives: http://lists.mysql.com/commits
To unsubscribe:    http://lists.mysql.com/commits?unsub=commits@bugs.mysql.com
[17 Sep 2008 12:12] 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/54242

2739 Martin Skold	2008-09-15
      bug #31231  mysql_alter_table() tries to drop a non-existing table
      bug#31233 mysql_alter_table() fails to drop UNIQUE KEY
[8 Oct 2008 7:56] Jon Stephens
Hi,

AFAICT, there are no user-facing changes to document (which is why there was no Cluster-6.3 changelog entry for this bug).

If this is correct, then this bug can be closed following the merge to 5.1.

Otherwise, please indicate what user-facing change(s) need to be noted in the docs.

Thanks!
[9 Oct 2008 18:17] Bugs System
Pushed into 5.1.30  (revid:martin.skold@mysql.com-20080915091956-kv0n9bexs08cdlup) (version source revid:kpettersson@mysql.com-20080915214458-ejxj7ltezohabr3z) (pib:4)
[15 Oct 2008 15:01] Paul DuBois
This is actually pushed to 5.1.29, not 5.1.30.
[17 Oct 2008 16:41] Bugs System
Pushed into 6.0.8-alpha  (revid:martin.skold@mysql.com-20080915091956-kv0n9bexs08cdlup) (version source revid:kpettersson@mysql.com-20080915213305-1ljm3tx7tgsdrne9) (pib:5)
[22 Oct 2008 15:10] Paul DuBois
No changelog entry needed.
[28 Oct 2008 21:01] Bugs System
Pushed into 5.1.29-ndb-6.2.17  (revid:martin.skold@mysql.com-20080915091956-kv0n9bexs08cdlup) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:20] Bugs System
Pushed into 5.1.29-ndb-6.3.19  (revid:martin.skold@mysql.com-20080915091956-kv0n9bexs08cdlup) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[1 Nov 2008 9:45] Bugs System
Pushed into 5.1.29-ndb-6.4.0  (revid:martin.skold@mysql.com-20080915091956-kv0n9bexs08cdlup) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)