MySQL Forums
Forum List  »  InnoDB

issue with rename table to new database
Posted by: Alex McIntosh
Date: November 05, 2012 10:09AM

Hi all,

hope you can help with this one... I'm looking to archive a large amount of data (250m rows), and leave just the most recent 3 months worth of data. I've got the old data moved to another server right now and I'm looking to remove it from the live table and leave just the 3 months data I want to keep.

After checking some other posts I decided the best way to do would be to extract out the 3 months that I want to keep, import the extract to a new database, drop the old table and then use rename table to move the new table back into the live database. I tested this and it worked fine.

The issue I'm having is, when I did the last test today before deploying this to live tomorrow morning the table left the new database, but didn't go back into the original database. The IBD file for the table still existed in the old database, but the table didn't show and if I tried to create a table with the same name it said the table already existed. Any ideas?

I pulled the following from the error log:

121105 14:05:56 InnoDB: Error: table tempjourney/journey does not exist in the InnoDB internal data dictionary though MySQL is trying to rename the table...

Before running the rename the first time I stopped MySQL, deleted the ib_data file and ib_log file, then restarted mysql. I was told this was the only way to shrink down the ib_data file. Is this what caused the error? And if if so, is there a way to resolve the issue?

If worst comes to worst I can run a mysql dump on newly created table, drop the original table and re-import, but I have to keep the timings down so this wouldn't be my preferred option.

Hope you can help.



Options: ReplyQuote

Written By
issue with rename table to new database
November 05, 2012 10:09AM

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.