MySQL Database Archive

There is the presence of a bug in this case for two reasons:

111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p1' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p2' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p3' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p4' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p5' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p6' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p7' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p8' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p9' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p10' 

REASON #1 : Any table starting with #sql is a temp table. If these tables are still present in any database folder and the datetime stamp, simply delete them.

REASON #2 : Look carefully at the suffix of each table. #P#p? resembles a partition tag. This would indicate an attempt to create a temp table using partitiions. That's sounds insane. There was bug report on this back in Feb 16, 2006 for MySQL 5.1.7-beta (closed Mar 15, 2006). The bug report is based on trying to do this manually. Is mysql attempting to do this internally ?

IMHO I would upgrade mysql away from MySQL 5.1 up to MySQL 5.5

For the table mydb.mytable, run this query:

SELECT update_time
FROM information_schema.tables
WHERE table_schema='mydb'
AND table_name='mytable';

If you want to know what tables have changed in the last 5 minutes, run this:

SELECT table_schema,table_name,update_time
FROM information_schema.tables
WHERE update_time > (NOW() - INTERVAL 5 MINUTE);

Give it a Try !!!

UPDATE 2011-12-21 20:04 EDT

My employer (DB/Wweb hosting comany) has a client with 112,000 InnoDB tables. It is very difficult to read INFORMATION_SCHEMA.TABLES during peak hours. I have an alternate suggestion:

If you have innodb_file_per_table enabled and all the InnoDB tables are stored in .ibd files, there is a way to ascertain the time of the last update (up to the minute).

For the table mydb.mytable, do the following in the operating system:

$ cd /var/lib/mysql/mydb
$ ls -l mytable.ibd | awk '{print $4,$5}'

This timestamp is from the OS. You can't go wrong on this one.

UPDATE 2011-12-21 22:04 EDT [mysqld] innodb_max_dirty_pages_pct=0;

Add this to my.cnf, restart mysql, and all InnoDB tables will experience fast flushes from the buffer pool.

To avoid restarting, just run

mysql> SET GLOBAL innodb_max_dirty_pages_pct=0;

UPDATE 2013-06-27 07:15 EDT

When it comes to retrieving the date and time for a file, ls has the --time-style option:

$ cd /var/lib/mysql/mydb
$ ls -l --time-style="+%s" mytable.ibd | awk '{print $6}'

You can compare the timestamp of the file against UNIX_TIMESTAMP(NOW()).