CODEWITHSUNDEEP
mysqlperl
Y.K.
Y.K.

Reputation: 310

mysql non native, select only if table exists

This type of question has been posted a few times, but the solutions offered are not ideal in the following situation. In the first query, I'm selecting table names that I know exist when this first query is executed. Then while looping through them, I want to query the number of records in the selected tables, but only if they still exist. The problem is, during the loop, some of the tables are dropped by another script. For example : 

SELECT tablename FROM table
-- returns say 100 tables

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

And, I guess because it's run by cron, it fails fataly, and I get sent an email from cron stating it failed.

DBD::mysql::st execute failed: Table 'xxx' doesn't exist at /usr/local/lib/perl/5.10.1/Mysql.pm line 175.

Script is using the deprecated Mysql.pm perl module.

Upvotes: 4

Views: 534

Answers (2)

mvp
mvp

Reputation: 116167

You should be able to protect your perl code from failing by putting it into 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 "while" loop

If you used better server like Postgres, right solution would be to enclose everything into transaction. But, in MySQL dropping table is not protected by transactions.

Upvotes: 1

Piotr Wadas
Piotr Wadas

Reputation: 1884

Obviously you need to secure table to make sure it won't get deleted before you execute your query. Keep in mind, that if you begin with some kind of table lock, to avoid possible drop - the DROP TABLE query issued from some other place will fail with some lock error, or at least will wait until your SELECT finishes. Dropping a table isn't really often used operation, so with most cases the schema design persists during server operation - what you observe is really rare behaviour. In general, preventing table from being dropped during other query just isn't supported, however, in comments for below document you may find some trick with usage of semaphore tables to achieve it.

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

"A table lock protects only against inappropriate reads or writes by other sessions. The session holding the lock, even a read lock, can perform table-level operations such as DROP TABLE. Truncate operations are not transaction-safe, so an error occurs if the session attempts one during an active transaction or while holding a table lock."

"If you need to do things with tables not normally supported by read or write locks (like dropping or truncating a table), and you're able to cooperate, you can try this: Use a semaphore table, and create two sessions per process. In the first session, get a read or write lock on the semaphore table, as appropriate. In the second session, do all the stuff you need to do with all the other tables."

Upvotes: 1

Related Questions