Tuesday, December 8, 2009

MySQL Server crash after passing MySQL open file limit

The file descriptor limit in Windows operating system restricts simultaneous opening of the files to a defined limit of 2048. The limit further causes severe performance issues for a busy MySQL server, because the table cache value needs to be greater than 2048. In most of these scenarios, MySQL server crashes resulting in corruption of MySQL database. In addition, the database becomes unmountable and its records inaccessible. To access your database records in such scenarios, you will need to restore the data from a valid and complete database backup. But if in case, your database is not available or backup file is corrupted, an advanced third-party MySQL Repair application always helps by performing a complete repair.

As a practical instance, your MySQL Server is already running busy and when you attempt to open some more tables, your server crashes displaying the below error message:

“080726 6:06:11 [ERROR] Error in accept: Too many open files
080726 6:08:16 [ERROR] /home/sbester/mysql/5.1/mysql-5.1.28-linux-i686/bin/mysqld: Can't
open file: './test/t1_tmp.frm' (errno: 24)
080726 6:08:17 [ERROR] /home/sbester/mysql/5.1/mysql-5.1.28-linux-i686/bin/mysqld: Can't
open file: './test/t_@002d123.frm' (errno: 24)....”
Your MySQL server shuts down expectedly after the above error message.

Note: In most cases of improper shutdown of MySQL server, your database becomes corrupt and inaccessible.

To prevent database corruption caused due to above reason, you should exceed the default open file limit to 16384 or more by using max-open-files directive in MySQL. For repairing your corrupted database, you should follow the below steps:

1.Use the below command to check the level of corruption:
mysqlcheck [options] dbname tablename [tablename2... ].
2.Repair your database using the below command:
repair table tablename [,tablename1...][options]

The above resolution proves successful in repairing your database after all logical crash scenarios. But the possibilities of failure of the above method also do exist. In such situations, you will need to use advanced MySQL Database Repair application. Such MySQL Recovery tools perform comprehensive repair after by incorporating intensive scanning and repair techniques.

No comments:

Post a Comment