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:
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.