MySQL Bugs: #28415: Some ALTER TABLE statements no longer work under LOCK TABLES

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

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;

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().

Approved by email.

Pushed into 5.1.19-beta

Noted in 5.1.19 changelog.

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