Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
I have three questions : 1) What version of MySQL? 2) Are the tables you are truncating InnoDB or MyISAM? 3) What is the the size of your bulk insert buffer? (run show global variables like 'bulk%'; –  RolandoMySQLDBA May 2 '12 at 19:55
    
MySQL version 5.1, Tables are MyISAM and bulk insert buffer size is 8388608 –  Jay May 2 '12 at 19:58

1 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.

share|improve this answer
    
"it could be the two commands colliding into one another, especially if there are a lot of open file handles in mysqld"...can u explain further...i am new to dba –  Jay May 2 '12 at 20:22
    
and one more thing if i run the command SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 256; will dere be any need to restart mysql –  Jay May 2 '12 at 20:26
    
Windows OS has a limit on the number of file handle that can be open at any one time. Please run SHOW VARIABLES LIKE 'open_files_limit'; This number of computed by mysqld when it started up. That's a the maximum number allowed for mysql by the OS with mysql running. Whatever number of files are open must be closed and reopened. That's a lot of deallocating and reallocating of RAM. –  RolandoMySQLDBA May 2 '12 at 20:27
    
ok but i still cant understand the colliding part.. –  Jay May 2 '12 at 20:29
    
and is dere any way to check how many file handles are open at a particular time in mysql –  Jay May 2 '12 at 20:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.