Mysql Database Error 22 or 24

Document ID : KB000048783
Last Modified Date : 14/02/2018
Show Technical Document Details

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