List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:March 16 2009 11:24am
Subject:Re: InnoDB - CREATE INDEX - Locks table for too long
View as plain text  
Hi,

I am using your procedure on MyISAM tables now and works but RENAME does not
work with locked tables,

(anyway it is already an atomic operation)

=BARON================================================

Try something like this:

create table new_table like old_table;
alter table new_table add key(.....);
insert into new_table select * from old_table where <primary_key>
between ? and ?;

Repeat the last statement until nearly finished, then:

lock tables new_table write, old_table write;
insert into new_table select * from old_table where <primary_key> > ?;
rename table old_table backup, new_table old_table;
unlock tables;

================================================

so the above procedure fails unless I swap like this:

---------------------

...

...

unlock tables;

rename table old_table backup, new_table old_table;

---------------------

The problem is of course that it is no more atomic.

I also thought of "SELECT for UPDATE" that locks the records but does not
prevent from inserting new ones,

any suggestions?

Thanks

Clauido

Thread
Left join does not work with Count() as expectedArtem Kuchin19 Feb
  • Re: Left join does not work with Count() as expectedOlaf Stein19 Feb
    • InnoDB - CREATE INDEX - Locks table for too longClaudio Nanni19 Feb
      • Re: InnoDB - CREATE INDEX - Locks table for too longBaron Schwartz20 Feb
        • Re: InnoDB - CREATE INDEX - Locks table for too longClaudio Nanni21 Feb
        • Re: InnoDB - CREATE INDEX - Locks table for too longClaudio Nanni16 Mar
  • Re: Left join does not work with Count() as expectedPeter Brawley19 Feb
  • RE: Left join does not work with Count() as expectedMartin Gainty19 Feb