I don't know if anyone from mysql is monitoring this list, but I think
there is something goofy going on with your email servers. I just now got
this email below. Uhm. I wrote that bug over a year and a half ago, and the
reply by Susanne was the day after. WTF?
...better late than never I suppose, but maybe you should look into your
mail server Q and see if there are more "stuck" emails?!
-----Original Message-----
From: Bug Database [mailto:do-not-reply@stripped]
Sent: Tuesday, July 27, 2010 12:43 PM
To: daevid@stripped
Subject: #42774 [Com]: mysqldump command line switch to ignore temporary
tables
ATTENTION! Do NOT reply to this email!
To reply, use the web interface found at
http://bugs.mysql.com/?id=42774&edit=2
Comment by: Bugs System
Reported by: Daevid Vincent
Category: Client
Severity: S4 (Feature request)
Status: Verified
Version: 5.0.51a
OS: Linux
OS Details: Ubuntu
Tags: IGNORE, temporary, mysqldump
Defect Class: D5 (Feature request)
[12 Feb 2009 12:53] Susanne Ebrecht
Many thanks for writing a bug report.
Changed category because mysqldump is CLI.
Verified as described.
The problem is related to general indexing.
------------------------------------------------------------------------
[11 Feb 2009 21:05] Daevid Vincent
Description:
We have a live database with tables that are over 500M rows. We cannot
take the server down to do a backup, or in this case to delete
temporary tables. However we use temporary tables constantly to take
offloads (in SQL format, create a temp database/tables, then normalize
and re-insert that offload data into our main database). This happens
every minute or so and is increasing as we expand.
We would like a way to ignore temp tables from mysqldump's command
line.
/*!40000 ALTER TABLE `offload_temp_1d071827a37c402d8294d4ee65d86e4d`
DISABLE KEYS */;
/*!40000 ALTER TABLE `offload_temp_1d071827a37c402d8294d4ee65d86e4d`
ENABLE KEYS */;
/*!40000 ALTER TABLE `offload_temp_55cd7b89c3def439a9727821717428ec`
DISABLE KEYS */;
/*!40000 ALTER TABLE `offload_temp_55cd7b89c3def439a9727821717428ec`
ENABLE KEYS */;
So as you can see, we end up with HUGE amounts of temporary tables
being backed up and then on occasion re-imported. Then we don't know
which temp tables are stale vs. active (the active ones do remove
themselves, but our naming convention doesn't have dates)
How to repeat:
mysqldump --opts to dump out our entire database.
Suggested fix:
mysqldump --ignore-temp-tables
------------------------------------------------------------------------
--
http://bugs.mysql.com/42774
