Bug #28415 Some ALTER TABLE statements no longer work under LOCK TABLES
Submitted: 14 May 2007 12:48 Modified: 17 May 2007 14:04
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.1.19-bk OS:Any
Assigned to: Dmitry Lenev CPU Architecture:Any

[14 May 2007 12:48] Dmitry Lenev
Description:
Some of ALTER TABLE statements, which used to work okay under LOCK TABLES in 5.0, no longer work in 5.1.19-bk.
This problem affects those ALTER TABLE statements which are executed using so called "fast alter table" algorithm (i.e. those ALTER TABLE statements which can be executed by changing .FRM files only and without copying data). It is repeatable for InnoDB tables on Linux but after inspecting code I believe that it is also repeatable for all engines on Windows.

See How-to-repeat section for more info:

How to repeat:
# Script for mysql test suite which shows the problem

-- source include/have_innodb.inc

# Should be repeatable for all engines on Windows
create table t1 (i int) engine=innodb;
lock table t1 write;
# This statement produces an error in 5.1 and works fine in 5.0
alter table t1 modify i int default 10;
[14 May 2007 18:38] 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/26631

ChangeSet@1.2517, 2007-05-14 22:38:26+04:00, dlenev@mockturtle.local +3 -0
  Fix for bug #28415 "Some ALTER TABLE statements no longer work under LOCK
  TABLES" and failures of alter_table.test on Windows which occured after
  pushing fix for bugs #20662, #20903, #24508, #24738 (various problems
  with CREATE TABLE SELECT).
  
  ALTER TABLE statements which were handled using "fast" alter table
  optimization were not properly working under LOCK TABLES if table
  was transactional (for all table types under Windows).
  
  Code implementing "fast" version of ALTER TABLE tried to open and
  lock table using open_ltable() after renaming .FRM files (which
  corresponds to renaming tables in normal case) in some cases
  (for transactional tables or on Windows). This caused problems
  under LOCK TABLES and conflicted with name-lock taken by 
  ALTER TABLE RENAME on target tables.
  
  This patch solves this issue by using reopen_name_locked_table()
  instead of open_ltable().
[14 May 2007 19:58] Konstantin Osipov
Approved by email.
[16 May 2007 13:49] Bugs System
Pushed into 5.1.19-beta
[17 May 2007 14:04] Paul DuBois
Noted in 5.1.19 changelog.

Some ALTER TABLE statements that worked in MySQL 5.0 did not work in 
5.1.