Mysql – Table was not locked with LOCK TABLES when recreating an index

javaMySQL

I have a weird problem when recreating the index. This is what happens. This is done through JDBC, in Java btw.

A table 'stuff' is truncated by a process. The application is reinitalizing a cycle (for some processes) by recreating data in this table. Data is around 700 000 records and changing for each cycle. So happens:

First:

lock tables stuff write, inventory read;

Next step is:

insert into stuff (x,y,z) select x,y,z from inventory;

And here is a trick. We experienced that when this table is recreated this way, we need to recreate an index, because if we don't do this, the processes querying by this field are locked while the index is rebuilt. So this is what is done:

alter table stuff drop index idx_UpdatedTime;
alter table stuff add index idx_UpdatedTime(UpdatedTime);

The interesting thing is what happens: either the first alter or the second fails.

1 – The first one fails, if the index doesn't exist (this isn't a problem, this is expected behaviour). The exception says: "Can't DROP 'idx_UpdatedTime'; check that column/key exists". This is fine. In this case, the index is created by the second alter command. This will lead to point 2- in the next cycle.

2 – If the second one fails, the error says: "Table 'stuff' was not locked with LOCK TABLES". This is only happening when the first alter succeeds. This means that the index gets dropped, but isn't recreated. This will lead to point 1 – in the next cycle.

So, this means that these two behaviors are changing each other. 1,2,1,2,1,2… forever. I don't understand why is this happening, what am I missing.

Best Answer

Try creating a temp table, and then perform a bait-and-switch

ALTER TABLE stuff RENAME oldstuff
CREATE TABLE newstuff LIKE oldstuff;
ALTER TABLE newstuff DROP INDEX idx_UpdatedTime;
INSERT INTO newstuff SELECT * FROM oldstuff;
INSERT INTO newstuff (x,y,z) SELECT x,y,z FROM inventory;
ALTER TABLE newstuff ADD INDEX idx_UpdatedTime(UpdatedTime);
ALTER TABLE newstuff RENAME stuff;
DROP TABLE oldstuff;

This approach may take longer but should establish some continuity for your processing

I renamed stuff to oldstuff to remove any possibility access to the stuff table during this process

Related Question