Resolved Database error "Too many open files" from storage engine MyISAM" when using LOCK TABLES

[giuseppewhig]

Hello,
In my server managed with Plesk I have a lot of websites and databases, but in the backup manager there is an issue for only one database:

Not all the data was backed up into /var/lib/psa/dumps/domains/website.it/databases/websitedb_1 successfully. Total space: 1813.00 GB; Available space: 1261.00 GB; Mounted on: /var. mysqldump: Got error: 1030: "Got error 24 "Too many open files" from storage engine MyISAM" when using LOCK TABLES

I tried using the database repair tool and looking for solutions on the internet, but the problem remains. Anyone know how it can be solved?
The problem is that it does not allow you to make a full backup of the db or even to export it.

Thank you very much

 

[IgorG]

Have you tried to apply solution from this KB article MySQL shows the error: Too many open files ?

 

[giuseppewhig]

Yes.
This is the conf file:

root@n:/# cat /etc/mysql/my.cnf
# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
[mysqld]
max_connections = 300
open_files_limit = 16384
bind-address = ::ffff:127.0.0.1
local-infile=0

But also with this configuration I have the same error:

Unable to export a dump of websitedb:

• mysqldump: Got error: 1030: "Got error 24 "Too many open files" from storage engine MyISAM" when using LOCK TABLES
• Traceback (most recent call last):
• File "/opt/psa/admin/sbin/dbbackup", line 6, in <module>
• dbbackup.main()
• File "/opt/psa/lib/modules/python/dbbackup/dbbackup.py", line 97, in main
• backup(options, password)
• File "/opt/psa/lib/modules/python/dbbackup/dbbackup.py", line 68, in backup
• raise Exception("program 'mysqldump' finished with non-zero exit code: %d", p1.returncode)
• Exception: ("program 'mysqldump' finished with non-zero exit code: %d", 2)

This problem there is only with a database that has Tables: 13486 Size: 3.33 GB.
Do you know any other solution i can apply to solve? unfortunately at this time I cannot make backups correctly due to this problem

Thank you very much!

 

[IgorG]

I do not know your OS but I hope that you have applied correct setting related to systemd.
In this case it should be

[Service]
LimitNOFILE=8192

instead of your

open_files_limit = 16384

 

[giuseppewhig]

Yes, I followed the guide and entered the string as required:



I ran the command after restarting mysql to check and everything seems fine, but the problem persists.

root@ns3101776:/# egrep "open files" /proc/$(cat `plesk db -Ne "show variables like 'pid_file'" | awk '{print $2}'`)/limits
Max open files 8192 8192 files

 

[Monty]

When you create a drop-in file in systemd (as you did) then you need to reload systemd config before your changes will take effect:

# systemctl daemon-reload

And then restart mysql.
Does it work now?

 

[giuseppewhig]

I did it. But nothing change and also after reload systemd when I restart mysql always get this:
Warning: mysql.service changed on disk. Run 'systemctl daemon-reload' to reload units.

for completeness of information the operating system is debian 9.12, I have Plesk Onyx 17.8.11 and by running the command to see the file systems of the active databases I receive:

systemctl list-unit-files | grep -E 'mysql | mariadb'
mariadb.service enabled
mariadb @ .service disabled
mysql.service enabled
mysqld.service enabled

 

[Monty]

Well, the warning "Warning: mysql.service changed on disk. Run 'systemctl daemon-reload' to reload units. " should only be printed if you did NOT execute "systemctl daemon-reload".

When you execute systemctl daemon-reload, do you see anything in /var/log/syslog?

Also, check the output of "systemctl show mysql.service", there you should see "LimitNOFILE=<your number>" and other info.

 

[giuseppewhig]

Thanks for your help.
the strange thing is that the warning remains even after executing the command and restarting the service.
in/var/log/syslog no relative error appears:

Mar 25 18:52:18 ns3101776 systemd[1]: Reloading.
Mar 25 18:52:19 ns3101776 postfix/smtpd[17918]: warning: hostname ip-38-42.ZervDNS does not resolve to address 92.118.38.42: Name or service not known
Mar 25 18:52:19 ns3101776 postfix/smtpd[17918]: connect from unknown[92.118.38.42]
Mar 25 18:52:22 ns3101776 postfix/smtpd[8482]: warning: hostname ip-38-58.ZervDNS does not resolve to address 92.118.38.58: Name or service not known
Mar 25 18:52:22 ns3101776 postfix/smtpd[8482]: connect from unknown[92.118.38.58]
Mar 25 18:52:23 ns3101776 plesk_saslauthd[17328]: No such user 'install@ip...' in mail authorization database
Mar 25 18:52:23 ns3101776 plesk_saslauthd[17328]: failed mail authentication attempt for user 'install@ip...' (password len=8)
Mar 25 18:52:23 ns3101776 postfix/smtpd[18063]: warning: unknown[103.141.137.39]: SASL LOGIN authentication failed: authentication failure
Mar 25 18:52:24 ns3101776 systemd[1]: Stopping MariaDB 10.1.44 database server...
Mar 25 18:52:26 ns3101776 systemd[1]: Stopped MariaDB 10.1.44 database server.
Mar 25 18:52:26 ns3101776 systemd[1]: Starting MariaDB 10.1.44 database server...
Mar 25 18:52:27 ns3101776 mysqld[18978]: 2020-03-25 18:52:27 140107482860928 [Note] /usr/sbin/mysqld (mysqld 10.1.44-MariaDB-0+deb9u1) starting as process 18978 ...
Mar 25 18:52:28 ns3101776 systemd[1]: Started MariaDB 10.1.44 database server.
Mar 25 18:52:28 ns3101776 plesk_saslauthd[17328]: No such user 'install@ip-... in mail authorization database
Mar 25 18:52:28 ns3101776 plesk_saslauthd[17328]: failed mail authentication attempt for user 'install@ip-...' (password len=7)
Mar 25 18:52:28 ns3101776 postfix/smtpd[18063]: warning: unknown[103.141.137.39]: SASL LOGIN authentication failed: authentication failure

In systemctl show mysql.service among other things there is:
LimitNOFILE=8192
LimitNOFILESoft=8192

 

[giuseppewhig]

Seems I solved the problem following this: Guide to Raise ulimit open files and MySQL open_files_limit | BaseZap
and apply LimitNOFILE=102400

Thanks anyway