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:
LOCK TABLES foo WRITE;
DROP INDEX `keyX` ON `foo`;
CREATE UNIQUE INDEX `keyX` ON `foo` (`column1`,`column2`);
UNLOCK TABLES;

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?
TIA,
--Rob


__________________________________________________ ____________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__________________________________________________ ____________________

Rob, seems like you want to ensure that no writes occur in between the
drop index and create index statements, yes?

It's not pretty, but you could stop the mysql service and start it
back up with --skip-networking, then access it from localhost to
perform your changes. If you have processes updating from localhost
this won't be effective, though.

Another thought might be to create your new index first, then drop the
old, as in:

CREATE UNIQUE INDEX `keyXX` ON `foo` (`column1`,`column2`);
DROP INDEX `keyX` ON `foo`;

The index having a different name should only be a problem if you've
used optimizer hints in your SQL - some people do, some don't.

Someone else may have a better thought.

Dan

On 10/3/06, Rob Desbois <robert.desbois@chronos.co.uk> wrote:
> 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:
> LOCK TABLES foo WRITE;
> DROP INDEX `keyX` ON `foo`;
> CREATE UNIQUE INDEX `keyX` ON `foo` (`column1`,`column2`);
> UNLOCK TABLES;
>
> 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?
> TIA,
> --Rob
>
>
> __________________________________________________ ____________________
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> __________________________________________________ ____________________
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>
>

Rob Desbois wrote:
> 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:
> LOCK TABLES foo WRITE;
> DROP INDEX `keyX` ON `foo`;
> CREATE UNIQUE INDEX `keyX` ON `foo` (`column1`,`column2`);
> UNLOCK TABLES;
>
> 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?
> TIA,
> --Rob
>
>
> __________________________________________________ ____________________
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> __________________________________________________ ____________________

Any reason you can't create then drop? This isn't a primary key - you
can have more than one unique index. The index name could change, but
that really shouldn't matter.

CREATE UNIQUE INDEX `keyY` ON `foo` (`column1`,`column2`);
DROP INDEX `keyX` ON `foo`;



--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Dan,
I do need to prevent writes from occurring between the DROP and CREATE.
The primary purpose of this is to prevent the CREATE from failing due to duplicate entries being found during key creation.

As CREATE INDEX has no IGNORE option, I had thought I would have to do this a nasty way, but it turns out that even though CREATE INDEX is mapped to the equivalent ALTER TABLE statement, not all of the options available in ALTER TABLE are mapped. So, if I change my queries to the following:

DROP INDEX `keyX` ON `foo`;
ALTER IGNORE TABLE `foo` ADD UNIQUE KEY `keyX`(`column1`, `column2`);

Then, if any rows are duplicated between the two, they will be silently dropped during the key creation.
Thanks for your help!

--Rob


> Rob, seems like you want to ensure that no writes occur in between the
drop index and create index statements, yes?

It's not pretty, but you could stop the mysql service and start it
back up with --skip-networking, then access it from localhost to
perform your changes. If you have processes updating from localhost
this won't be effective, though.

Another thought might be to create your new index first, then drop the
old, as in:

CREATE UNIQUE INDEX `keyXX` ON `foo` (`column1`,`column2`);
DROP INDEX `keyX` ON `foo`;

The index having a different name should only be a problem if you've
used optimizer hints in your SQL - some people do, some don't.

Someone else may have a better thought.

Dan

On 10/3/06, Rob Desbois <robert.desbois@chronos.co.uk> wrote:
> 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:
> LOCK TABLES foo WRITE;
> DROP INDEX `keyX` ON `foo`;
> CREATE UNIQUE INDEX `keyX` ON `foo` (`column1`,`column2`);
> UNLOCK TABLES;
>
> 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?
> TIA,
> --Rob
>
>
> __________________________________________________ ____________________
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> __________________________________________________ ____________________
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>
>

__________________________________________________ ____________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__________________________________________________ ____________________


<

---------- Original Message ----------

FROM: "Dan Buettner" <drbuettner@gmail.com>
TO: "Rob Desbois" <robert.desbois@chronos.co.uk>
DATE: Tue, 3 Oct 2006 11:03:58 -0500

SUBJECT: Re: (Windows) drop / create index and lock tables

Rob, seems like you want to ensure that no writes occur in between the
drop index and create index statements, yes?

It's not pretty, but you could stop the mysql service and start it
back up with --skip-networking, then access it from localhost to
perform your changes. If you have processes updating from localhost
this won't be effective, though.

Another thought might be to create your new index first, then drop the
old, as in:

CREATE UNIQUE INDEX `keyXX` ON `foo` (`column1`,`column2`);
DROP INDEX `keyX` ON `foo`;

The index having a different name should only be a problem if you've
used optimizer hints in your SQL - some people do, some don't.

Someone else may have a better thought.

Dan

On 10/3/06, Rob Desbois <robert.desbois@chronos.co.uk> wrote:
> 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:
> LOCK TABLES foo WRITE;
> DROP INDEX `keyX` ON `foo`;
> CREATE UNIQUE INDEX `keyX` ON `foo` (`column1`,`column2`);
> UNLOCK TABLES;
>
> 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?
> TIA,
> --Rob
>
>
> __________________________________________________ ____________________
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> __________________________________________________ ____________________
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=drbuettner@gmail.com
>
>

__________________________________________________ ____________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__________________________________________________ ____________________


__________________________________________________ ____________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__________________________________________________ ____________________