MySQL Forums
Forum List  »  InnoDB

REFERENCES table is changed when rename the parent table
Posted by: mahmoud tuweiq
Date: June 25, 2014 06:24AM

Hi

i am working on 5.5.37 mysql and AWS RDS instance

i have two tables
objects (parent table have more 5 million rows) and object_units( child table have more than 8 million rows)
there is forgein key between them

CONSTRAINT `fk_object_units_1` FOREIGN KEY (`id_object`) REFERENCES `objects` (`id_object`),

and i am trying to modify the parent table to modify column and add new column ,
to avoid "Waiting for table metadata lock" thread status that happend when run alter statement ,
i create new table, it's structure have the changes that i need named by objects_new ,
and i copy the data from table objects to table objects_new , and that working fine with me .

in the last step i did the following rename command:
rename table objects to objects_old ,objects_new to objects ;

then implicitly the reference on child table become objects_old as the following:

CONSTRAINT `fk_object_units_1` FOREIGN KEY (`id_object`) REFERENCES `objects_old` (`id_object`),
so new table " objects" is not more the perant of child , the parent is objects_old , so my "work arround" is failed


my question is
i need step of process to do my modifications on parent table without locking the table and minimum side-effects on DB load and performance ?

Options: ReplyQuote


Subject
Views
Written By
Posted
REFERENCES table is changed when rename the parent table
1607
June 25, 2014 06:24AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.