Mysql non native, select only if table exists

Question

This question has been published several times, but the proposed solutions are not ideal in the following situation. In the first query, I select the names of the tables that I know exist when this first query is executed. Then, going through them, I want to query the number of records in the selected tables, but only if they still exist. The problem is that during the loop some tables are discarded by another script. For instance:

SELECT tablename FROM table -- returns say 100 tables while (%tables){ SELECT COUNT(*) FROM $table -- by the time it gets to the umpteenth table, it been dropped -- so the SELECT COUNT(*) fails } 

And, I think, because it runs cron, it fails, and I receive an email from cron stating that it failed.

DBD :: mysql :: st execute failed: Table "xxx" does not exist in / usr / local / lib / perl / 5.10.1 / Mysql.pm line 175.

Script uses the deprecated Mysql.pm perl module.

Answer 1

Obviously, you need to protect the table to make sure that it will not be deleted before your query is executed. Keep in mind that if you start with some kind of table lock to avoid a possible crash - a DROP TABLE query issued from somewhere else will fail with some kind of locking error, or at least wait for your SELECT will not end. Dropping a table is actually often not used, so in most cases the design of the schema is preserved while the server is running - what you observe is a very rare behavior. In the general case, preventing table exclusion due to another query is simply not supported, however in the comments to the document below you can find some trick using semaphore tables to achieve it.

http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html

"Table locking only protects against inappropriate reads or writes by other sessions. A session that holds a lock, even a read lock, can perform table-level operations such as DROP TABLE. Truncate operations are not transactional, so an error occurs if a session tries to perform one operation during an active transaction or hold a table lock. "

"If you need to do something with tables that are not normally supported by read or write locks (like deleting or truncating a table), and you can collaborate, you can try this: use a semaphore table and create two sessions per process. In the first "if necessary, get a read or write lock on the semaphore table. In the second session, do whatever you need to do with all the other tables."

Answer 2

You should be able to protect your perl code from failure by placing it in an eval block. Something like that:

 eval { # try doing something with DBD::mysql }; if ( $@ ) { # oops, mysql code failed. # probably need to try it again } 

Or even put this in a while while loop

If you used a better server, like Postgres, the right solution would be to wrap everything in a transaction. But in MySQL dropping table is not transaction protected.