MySQL Forums
Forum List  »  Stored Procedures

Scheduled procedure causing too many open files error
Posted by: F Kemp
Date: January 23, 2013 01:11PM

Hi, thanks in advance for any advice.

I created a procedure for monitoring and it looks like it is locking up the database, causing errors about "too many open files". Our limit is set really high and the DBA checked the open files last time we got that error and we were not close to the limit. He thinks it might be a memory issue and is checking into our errors some more.

In the mean time, is there anything I need to do/add in my stored procedure to make sure I am not leaving files open after reading them?

The procedure is scheduled to run once an hour and it runs for just a few seconds. It queries between 800-1000 tables. Because we have varying numbers/names of tables to read I am building the statements dynamically and then using prepare/execute inside a cursor to query the tables and update a separate monitoring table. It seems to work correctly, but we can't have it locking up the DB. Here is a version of that part of the sql:


insert into querytable (Query_Text)
select concat('update monitortable m set m.LatestTime= (select max(Updated) from schema.', t.TABLE_NAME, ''');') words
from information_schema.TABLES t where t.TABLE_NAME like "%_xyz";

-- cursor to execute saved queries --------------------------------------------

OPEN sp_monitor_cur;

loop1:LOOP

FETCH sp_monitor_cur INTO query;
IF done THEN
LEAVE loop1;
END IF;

set @sql = query;

prepare stmt from @sql;

execute stmt;

END LOOP;

CLOSE sp_monitor_cur;

--------------------------------------------------------------------

Thanks again!

Options: ReplyQuote


Subject
Views
Written By
Posted
Scheduled procedure causing too many open files error
2969
January 23, 2013 01:11PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.