> > I'm trying to realize fail-safe billing database mirroring from other =
> > set of tables (DBFs) and found nice way to do that:
> > 1) Convert all data into "temporary" tables named like regular tables =
> > with '_tmp' suffix;
> > 2) Issue "FLUSH TABLES" query and compress newly created tables;
> > 3) Provide single 'RENAME TABLE ...' to rename all existing tables to =
> > ones with '_old' suffix and rename newly generated tables with '_tmp' =
> > suffix to regular (without suffix).
> > Conversion working on daily basis, and most problem is to make it some =
> > faster (it works and lives on P5-200 with 64 RAM). Common data for two =
> > fiscal monthes (current and last closed) is about 1 million records for
> > each month. Conversion time runs from 15 minutes at new month began to =
> > about 30 minutes at the end of a month.
> > Visible soultion is to not convert closed month on daily basis in =
> > current month, just convert it last time when month is just closed. To =
> > realize it I'm trying to create MERGE table which hold all converted =
> > month, and convert only current (or, closed and current at "month is =
> > just closed" situation) month's datas.
> > The problem which I figured out is just renaming real tables owned by
> > MERGE. I can't guarantee what no queries to real (not temporary) tables
> > will appear while I've renaming tables, and renaming tables owned by
> > MERGE can bring many problems like incorrect datas. Using LOCK TABLE
> > <merge name> READ is not possible because RENAME can't be issued until
> > MERGE's member table(s) are locked. :(
> > Can you say something to help me?
> Can't you for now do:
> ALTER TABLE merge_table UNION=(empty_copy_of_table);
What did you mean saying "empty_copy_of_table"? Does I needs to create
temporary empty table and include it into merge?
> RENAME ...
> ALTER TABLE merge_table UNION=(old-union)
Nice idea but it's not fault-safe (because client/network can crash
somewhere between ALTER, RENAME and second ALTER, and we will have incorrect
Also, I can't issue 'ALTER TABLE' because all members of MERGE are
compressed (equal to read-only), and MERGE itself looks like read-only. :(
> We will look in 4.0 to see what we can do to handle MERGE tables
> better in this case.
As I said before, tracking renaming members of MERGE and keep MERGE
specification intact IMHO would helpful in this situation.