Mysql Database Error 22 or 24
Article ID: 48783
Updated On:
Products
Issue/Introduction
Description:
DETAILS
Can't create/write to file #sql105c_452d_0.MYD' (Errcode: 22) Error_code: 1
Can't create table 'temporary.mytable' (errno: 22) Error_code: 1005
Please note that error 22 is really unexpected/awkward in this context - it means:
OS error code 22: Invalid argument
http://bugs.mysql.com/bug.php?id=47821
This is usually due to having an issue with too many open files or mysql is out of resources, or the table may be corrupt due to a resource issue, and thus mysql isn't able to write/create/update temp tables.
errcode 24 = too many open files
The issue with too many open files can be caused by a few things. This means there are too many open files or connections to mysql and it can't handle the request to open another mysql file.
http://dev.mysql.com/doc/refman/5.0/en/not-enough-file-handles.html
For example, NV uses merge tables to store data, which basically means it uses multiple tables for each dataset and poll instance, rollup, etc.
This is to decrease load time when loading data in the web since it doesn't have to parse as many long tables for specific subsets of data.
When a merge/partitioned table is opened and placed in the table_cache, all of the underlying partitions are opened by mysql. So, at the worst case when this occurs the table_cache must be decreased, along with limiting the number of open files in mysql.
Solution:
The workaround is to alter some configuration parameters in the mysql INI file.
In the file, find the [mysqld] section, and insert/update these entries:
open_files_limit=2048 open-files=2048 table_cache=128 tmp_table_size= 16M max_heap_table_size = 16M table_open_cache = 128 max_connections = 250 query_cache_limit=2M query_cache_size=24M
The rationale behind this is:
-
Less open tables => less opened files.
- Less connections => less temporary tables => less opened files.
Restart the Mysql service to apply the changes.
The settings should also help with error 24 as well.
INI file location
MYSQL 4.1 (RA 8.3, NPC 5.1, SA 8.3):
Open the my.cnf file located in the root of the C:\ drive using notepad. (Start->Run, type "notepad c:\my.cnf")
For NV 6.1, Look at: D:\NetVoyant\db\my.ini.
MYSQL 5.1 (RA 9.0, NPC 6.0, SA 9.0, NV 7.0):
The file is located here:
D:\NETQOS\mysql51\my.ini
Environment
Component:
Feedback
