Re: new mysqldump features (patch)

[Tim Bunce]

On Thu, Aug 21, 2003 at 12:47:04PM -0700, John David Duncan wrote:
> > 1. Need to be able to specify the format of the temp table names to
> > 	be sure name clashes can be avoided (a user may have tables
> > 	that begin with underscore, for example).
> 
> Hi Tim,
> 
> My puzzle with that is what syntax to use. It could look like
> "--rename-tables=_new_%s", which is powerful enough to let people name the
> temp table whatever they need to, but the %s there seems a little
> bothersome so I made the initial decision to code in an
> arcane-enough-seeming temp name.  Perhaps there should be two options,
> like --load-atomic and --rename-prefix=xyz, even though they are
> interdependent?
> 
> > 2. "rename-tables" isn't very self-explanatory. I'd suggest
> > 	--load-atomic which describes what it's doing rather than how.
> 
> Yes, I think --load-atomic sounds better, too.

Perhaps --load-atomic-prefix=... to both enable the mode and specify
a prefix for the temp name. A plain string (not sprintf format) should
suffice.

> > 3. Put all the new tables in place at once (fast), then delete the
> > 	old (slow). This can be done using the RENAME command:
> > 	RENAME	zip TO _zip_old;
> > 	RENAME _zip TO zip;
> >    Although RENAME is atomic and one could rename all the tables
> >    (thus avoiding the need to lock tables) I'm not sure how RENAME
> >    behaves if some of the tables don't exist.
> 
> RENAME is faster than DROP?

RENAME is effectively instant (once locks have been aquired).
But drop table can be very slow (for InnoDB) so doing using rename
to get all the new tables in place quickly and then droping the old
tabless _after_ releasing the locks would be much better.

> I also don't know what happens if you try to rename or drop an innodb
> table that holds a foreign key.

Um, er, neither do I.

Tim.