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.
TRUNCATE TABLE tbl_cdrload;
TRUNCATE TABLE tbl_cdrdetails_temp;
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;
Please add bulk_insert_buffer_size to my.ini
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:
- 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.