CODEWITHSUNDEEP
mysql
user5039263
user5039263

Reputation:

Error Code: 23 Out of resources when opening file

When I execute a query in MySQL, I get this error:

Error Code: 23
Out of resources when opening file '.\test\sample_table#P#p364.MYD' (Errcode: 24 - Too many open files)

MySQL version details:

VERSION                   5.6.21 
version_comment           MySQL Community SERVER (GPL)
version_compile_machine   x86_64
version_compile_os        Win64

How to solve this problem?

Upvotes: 15

Views: 30378

Answers (3)

jstaab
jstaab

Reputation: 3865

If you happen (like me) to be doing some hacky server maintenance by running a single insert query for 35k rows of data, upping the open_files_limit is not the answer. Try breaking up your statement into multiple bite-sized pieces instead.

Here's some python code for how I solved my problem, to illustrate:

headers = ['field_1', 'field_2', 'field_3']
data = [('some stuff', 12, 89.3), ...] # 35k rows worth

insert_template = "\ninsert into my_schema.my_table ({}) values {};"
value_template = "('{}', {}, {})"

start = 0
chunk_size = 1000
total = len(data)
sql = ''
while start < total:
    end = start + chunk_size

    values = ", \n".join([
        value_template.format(*row) 
        for row in data[start:end]
    ])

    sql += template.format(headers, values)

    start = end

Note that I do not recommend running statements like this as a rule; mine was a quick, dirty job, neglecting proper cleansing and connection management.

Upvotes: 0

Garry
Garry

Reputation: 4533

The mysql error: Out of resources when opening file... (Errcode: 24) indicates that the number of files that msyql is permitted to open has been exceeded.

This limit is controlled by the variable open_files_limit. You can read this in phpMyAdmin (or the MySQL command line utility) with the statement: 

SHOW VARIABLES LIKE 'open%'

To set this variable to a higher number, edit the /etc/my.cnf file and add the lines:

[mysqld]
open_files_limit = 5000

Upvotes: 20

GHajba
GHajba

Reputation: 3691

This answer explains the error code 24 (which is at the end of your error message).

Upvotes: 2

Related Questions