MySQL is trying to drop table

[chong]

Hi, I'm running MySQL 1.2.3rc on a machine for a number of months.

Recently, the database is experiencing a lot of "disappearing tables". Tables from my own database, as well as the proc table from mysql database becomes missing. The .frm file in the data folder for the tables are also gone.

Looking into the error log, I discovered that someone (or something) is trying to drop the tables:

070104 12:16:02 InnoDB: Warning: MySQL is trying to drop table `table_name_here`
InnoDB: though there are still open handles to it.
InnoDB: Adding the table to the background drop queue.
070104 12:17:45 InnoDB: Database was not shut down normally!

No codes in my script tries to drop regular tables (temp tables, yes), so this made me really puzzled.

Anyone facing something similar?

[NogDog]

I've not experienced that, but assuming it's possible for someone on the outside to access your system, I would recommend that:
1. You change the password of the root database user.
2. For any non-database-maintenance access to the database (e.g.: via scripts/programs), always use a database user account who has just the minimum privileges necessary to perform what needs to be done (e.g.: in most cases the user probably needs no more than DELETE, INDEX, INSERT, SELECT, and UPDATE privileges).
3. Along with the privileges in (2), you can specify which databases (and even specific tables) are accessible by a given user, too.

more info (http://dev.mysql.com/doc/refman/4.1/en/privilege-system.html)

[chong]

No good, have changed the user and password, without granting the new user with drop privileges (altho it has create tmp table privilege, which means it can drop temp tables)

Tables are still randomly dropped.

070117 11:37:39 InnoDB: Warning: MySQL is trying to drop table `xxx/tbl_items`
InnoDB: though there are still open handles to it.
InnoDB: Adding the table to the background drop queue.
070117 11:37:39 [Warning] Could not remove tmp table: '.\xxx\tbl_catalog', error: 2
070117 11:37:39 InnoDB: Warning: MySQL is trying to drop table `xxx/tbl_items_fiction`
InnoDB: though there are still open handles to it.
InnoDB: Adding the table to the background drop queue.

Anyway to find out why MySQL is trying to drop the table, or where the command is issued from? The timings correspond exactly to the time the script is executed, but the user has no privileges to drop tables!

[chazzy]

mysql drops a temp table at the close of a connection. if you're using PHP, that means the end of one script.