MySQL Bugs: #3116: Windows bug with ALTER TABLE and LOCK

Bug #3116	Windows bug with ALTER TABLE and LOCK
Submitted:	9 Mar 2004 8:07	Modified:	27 Sep 2008 12:25
Reporter:	Miguel Solorzano	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.18	OS:	Windows (Windows 2k/XP)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
I have been experiencing a small problem with altering a table, once it
has been locked.  If you alter a table (adding a column) while that table
is locked, subsequent calls to that table result in MySQL erroring, saying
the table was not locked.

I have reproduced this under Windows 2000, running the following versions
of  MySQL Server:  3.23.58-nt, 4.0.3-beta-nt, and 4.0.18-nt.  (The bug
does NOT show itself under any Linux versions.)

How to repeat:
C:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE IF NOT EXISTS locktest;
Query OK, 1 row affected (0.08 sec)

mysql> USE locktest;
Database changed
mysql> DROP TABLE IF EXISTS locktest;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE locktest (a TINYINT, b TINYINT);
Query OK, 0 rows affected (0.12 sec)

mysql> LOCK TABLE locktest WRITE;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO locktest SET a=1, b=2;
Query OK, 1 row affected (0.03 sec)

mysql> ALTER TABLE locktest ADD c TINYINT;
Query OK, 1 row affected (0.12 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO locktest set a=2, b=3, c=4;
ERROR 1100: Table 'locktest' was not locked with LOCK TABLES

This is an effect of the fact that windows doesn't allow one to drop a file that is in use. To go around this problem, MySQL will, on Windows, unlock any table that you do ALTER TABLE on.

We may try to fix this in the future, but this doesn't have a high priority.

Needs to be re-verified against current 6.0, where we implemented LOCK TABLES mode differently.

Not repeatable on 6.0:

c:\dbs>c:\dbs\6.0\bin\mysql -uroot --port=3600 --prompt="mysql 6.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.8-alpha-nt-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 6.0 > CREATE DATABASE IF NOT EXISTS locktest;
Query OK, 1 row affected (0.02 sec)

mysql 6.0 > USE locktest;
Database changed
mysql 6.0 > DROP TABLE IF EXISTS locktest;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql 6.0 > CREATE TABLE locktest (a TINYINT, b TINYINT);
Query OK, 0 rows affected (0.09 sec)

mysql 6.0 > LOCK TABLE locktest WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql 6.0 > INSERT INTO locktest SET a=1, b=2;
Query OK, 1 row affected (0.08 sec)

mysql 6.0 > ALTER TABLE locktest ADD c TINYINT;
Query OK, 1 row affected (0.16 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 6.0 > INSERT INTO locktest set a=2, b=3, c=4;
Query OK, 1 row affected (0.02 sec)

mysql 6.0 >