List:General Discussion« Previous MessageNext Message »
From:Rob Desbois Date:October 3 2006 3:16pm
Subject:(Windows) drop / create index and lock tables
View as plain text  
Hi all,

Say I have the following MyISAM table (example-ified) in a Windows-hosted DB:
CREATE TABLE foo ( column1 CHAR(1), column2 CHAR(1), UNIQUE KEY `keyX`(`column1`));

I have to perform an update of the key to extend it to both columns (it's an example,
ignore the content of the key), and want to ensure data integrity while I recreate it.

The following is what I thought I had to do:
   DROP INDEX `keyX` ON `foo`;
   CREATE UNIQUE INDEX `keyX` ON `foo` (`column1`,`column2`);

After much head-scratching due to "Error Code : 1100  Table 'foo' was not locked with LOCK
TABLES", I discovered that CREATE / DROP INDEX statements are mapped to equivalent ALTER
TABLE statements. This, due to the way ALTER TABLE statements 'work' on windows, renders
this code unusable as the DROP INDEX statement unlocks the table. Before the CREATE INDEX
statement will work I then have to run UNLOCK TABLES, which also makes sense (I thought it
was unlocked??).

So - I can't lock the table whiel I drop then recreate the index, so what's the best way
to do this?

This email has been scanned by the MessageLabs Email Security System.
For more information please visit 
(Windows) drop / create index and lock tablesRob Desbois3 Oct
  • Re: (Windows) drop / create index and lock tablesDan Buettner3 Oct
    • re[2]: (Windows) drop / create index and lock tablesRob Desbois4 Oct