FLUSH TABLES stays in the processlist even after execution

Question

Here is my code from Stored Procedures in Mysql

FLUSH TABLES;
TRUNCATE TABLE tbl_cdrload;
TRUNCATE TABLE tbl_cdrdetails_temp;

Then i execute a query to load a file in to database as below

LOAD DATA LOCAL INFILE filename INTO TABLE tbl_cdrload
FIELDS TERMINATED BY LINES TERMINATED BY '\n';

I executed this set of code one after the other several times using a Windows service. After some time, the Windows service stops automatically. When I open MySQL Administrator, I could see the FLUSH TABLES still running in the processlist. Can anyone explain why so and if this is the reason for the service to stop?

Answer 1

I am very iffy when it comes to TRUNCATE TABLE.

FLUSH TABLES actually closes the file handles on all open tables and reopens them with new file handles.

It could be the two commands colliding into one another, especially if there are a lot of open file handles in mysqld.

SUGGESTION #1

Please replace

FLUSH TABLES;
TRUNCATE TABLE tbl_cdrload; 
TRUNCATE TABLE tbl_cdrdetails_temp; 

with

CREATE TABLE tbl_cdrload_new LIKE tbl_cdrload;
ALTER TABLE tbl_cdrload RENAME tbl_cdrload_old;
ALTER TABLE tbl_cdrload_new RENAME tbl_cdrload;
CREATE TABLE tbl_cdrdetails_temp_new LIKE tbl_cdrdetails_temp;
ALTER TABLE tbl_cdrdetails_temp RENAME tbl_cdrdetails_temp_old;
ALTER TABLE tbl_cdrdetails_temp_new RENAME tbl_cdrdetails_temp;
DROP TABLE tbl_cdrload_old;
DROP TABLE tbl_cdrdetails_temp_old;

SUGGESTION #2

Please add bulk_insert_buffer_size to my.ini

[mysqld]
bulk_insert_buffer_size=256M

and restart mysql

C:\> net stop mysql
C:\> net start mysql

If you cannot restart mysql at this time, please run this command

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 256;

UPDATE 2012-07-31 18:29 EDT

As for the Windows service stopping, you will have to look into the MySQL error log. It will reveal what kind of shutdown (or crash scenario) was executed. In the Linux version of mysql, the mysqld process is controlled by a startup script called mysqld_safe. It has an infinite loop that does this:

• LOOP
  • Execute mysqld
  • When mysqld terminates, get return code
  • If ReturnCode is 0, Exit Loop
  • If ReturnCode > 0 and Severity OK, Loop or Terminate
  • If ReturnCode > 0 and Severity Bad, Terminate
• END LOOP

This mechanism does not exist in MySQL for Windows. If the Windows service for MySQL crashes, that's it. You must check the error log.