List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:July 3 1999 12:33pm
Subject:Alter table after drop table problem
View as plain text  
>>>>> "Baruch" == Baruch Even <baruch.even@stripped> writes:

Baruch> Hello,
Baruch> I have a trouble with my program (PHP3 & MySQL) which is rather
Baruch> strange.

Baruch> I have in my program a sequence like that:
Baruch> I have a table clients, to update it (a lengthy process), I create a
Baruch> second table clients_update with reduced number of indexes and do the
Baruch> update on it, after that I create a new table clients_temp with full
Baruch> tables insert into from clients_update, then I drop the clients table,
Baruch> rename the clients_temp to clients and drop the clients_update table.

Baruch> Everything is working fine on MySQL 3.22.20 on Linux, now I transferred
Baruch> it to the ISP's system with 3.21.29-gamma on IRIX, and things don't
Baruch> work as expected, one instruction at a time everything works as
Baruch> expected, when I run the program and stop it after each command it
Baruch> seems to work perfectly but only until I do the alter after drop table.
Baruch> The drop alone works, when they are in sequence the clients table stays
Baruch> empty but the clients_temp is gone.

Baruch> I'm baffled as to the reason for this problem, I saw no mention of a
Baruch> bug like that in the changes list of MySQL between the versions.

Baruch> The commands I use are:
Baruch> CREATE TABLE CLIENTS_TEMP ( .. )
Baruch> INSERT INTO CLIENTS_TEMP SELECT * FROM CLIENTS_UPDATE
Baruch> DROP TABLE CLIENTS
Baruch> ALTER TABLE CLIENTS_TEMP rename as CLIENTS
Baruch> DROP TABLE CLIENTS_UPDATE

Baruch> (this are from my head but they are close enough).

Baruch> No error is emitted by MySQL for the commands and they just pass on.

Baruch> One thing worth mentioning is that I do all this when all three tables
Baruch> are locked for writing, and there is a 'GET LOCK "update"' also.

Baruch> I would appreciate any help.

Hi!

I think that 3.21.29 had a bug when you do a ALTER TABLE on a locked
table!

The fix is to not use locks, or upgrade to the latest 3.21 or the
latest 3.22 version.

Regards,
Monty
Thread
Alter table after drop table problemBaruch Even3 Jul
  • Alter table after drop table problemMichael Widenius3 Jul
    • Re: Alter table after drop table problemMihaela IFTIMI-ILIE3 Jul
      • Re: Alter table after drop table problemSasha Pachev3 Jul