Error Code: 23 Out of resources when opening file

Question

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?

https://bugs.mysql.com/bug.php?id=35889 – Barmar Jul 27 '15 at 05:52 — Barmar, Jul 27 '15 at 05:52

score 20 · Answer 1 · answered Jul 27 '15 at 06:07

20

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

answered Jul 27 '15 at 06:07

Garry

4,493
3
28
48

/etc/my.cnf file where its located? – Jul 27 '15 at 06:09
i run these sql SHOW VARIABLES LIKE 'open%' => result= 6209 – Jul 27 '15 at 06:10

score 2 · Answer 2 · edited Apr 13 '17 at 12:13

2

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

edited Apr 13 '17 at 12:13

Community

1
1

answered Jul 27 '15 at 05:53

GHajba

3,665
5
25
35

score 0 · Answer 3 · answered Jun 10 '17 at 19:15

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.

Error Code: 23 Out of resources when opening file

3 Answers3

Linked