11

i’m run on Macos 10.15.5, with mariadb 10.4.13. Since i did “brew upgrade” I have this error : Out of resources when opening file './pluto/_connection.MYD' (Errcode: 24 "Too many open files")

I tried to modify this file /usr/local/etc/my.cnf to add this line open_files_limit = 60000 but it doesn’t work, open_files_limit variable still blocked on the value 256.

I tried this line : sudo ulimit -n 1024, but each time I restart the value returns to 256

Do you have any help to bring me to help me solve my problem?

Vika
  • 115
  • 1
  • 8
  • I'm running into this exact same issue. It's basically made my MariaDB useless. I can't run anything. I've tried adding this to my.cnf, but doesn't seem to work. Did you find a solution? [mysqld_safe] open_files_limit=1024 – Adam Christianson Jun 28 '20 at 22:38
  • same issue here! limit is 256 and i can't change it – andreas Jul 29 '20 at 16:03
  • Same problem after upgrade to Catalina 10.15.7 with mariadb 10.2. show variables had open_files_limit = 256. I played around with settings, and somehow that got changed to 20000, which happens to be the soft ulimit. Seems mariadb is ignoring my.cnf values, and taking ulimit values, but I cannot control this, because all settings are back to what they were before the problem occurred. Scenario seems to be (1) open_files_limit gets set to 256 for an unknown reason: (2) tweak my.cnf but make no actual changes; (3) restart maraidb; (4) loop through steps 2 and 3 until open_files_limit is 20000.Odd – Jeffrey Simon Oct 11 '20 at 17:44

5 Answers5

18

Have you tried looking for other ways to modify the open files limit on MacOS Catalina? A quick search led me to here which suggests the following for modifying it for the system while it is running:

sudo launchctl limit maxfiles <soft limit> <hard limit>

This site and this superuser question suggests to modify /Library/LaunchDaemons/limit.maxfiles.plist to permanantly increase it.

<?xml version="1.0" encoding="UTF-8"?>  
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN"  
        "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">  
  <dict>
    <key>Label</key>
    <string>limit.maxfiles</string>
    <key>ProgramArguments</key>
    <array>
      <string>launchctl</string>
      <string>limit</string>
      <string>maxfiles</string>
      <string><your soft limit here></string>
      <string><your hard limit here></string>
    </array>
    <key>RunAtLoad</key>
    <true/>
    <key>ServiceIPC</key>
    <false/>
  </dict>
</plist>

Then, finish up by changing permissions and verifying the change.

sudo chown root:wheel /Library/LaunchDaemons/limit.maxfiles.plist
sudo launchctl load -w /Library/LaunchDaemons/limit.maxfiles.plist
launchctl limit maxfiles
zac
  • 353
  • 1
  • 9
  • I had to do it this way. Works! – andreas Jul 29 '20 at 18:17
  • 1
    Which limits needs to be used? I tried 500/1000 thinking it's per app, and got crashed macos :D was forced to delete this file in recovery mode, because nothing worked. – spiilmusic Nov 02 '20 at 21:57
  • 20 hours working for a solution, the sudo command made the trick on catalina 10.15.7 – Alejandro Giraldo Jan 27 '21 at 06:21
  • Nice!!! My database imports finally work again. I'm using 10000/25000 for softlimit/hardlimit on MacBook Pro 3.3Ghz i7 16GB RAM with no issues so far. Maybe that's overkill, but it doesn't seem risky on my local machine. If you're running a real public server and not a dev environment you should probably tweak it more carefully. – squarecandy Apr 27 '21 at 00:19
  • Thanks! in my case, not work by editing `my.cnf` only. i need to execute `sudo launchctl limit maxfiles 9100 9200`, then `brew services restart mariadb`. Now mariadb works well :) – Erlang Parasu Jun 17 '22 at 10:50
6
  • MacOS Catalina 10.15.x
  • Mariadb installed with homebrew

Importing database with mysql < foo.sql fails with: [ERROR] Error in accept: Too many open files

Agree with the posts above. This looks like a false negative. This is what worked for me:

brew services stop mariadb

And make sure you do not have any mysql processes running: ps aux | grep mysql.

mkdir /usr/local/var/run/mysqld

Edit Mariadb config file /usr/local/etc/my.cnf and tell it to use this directory.

[client-server]
socket = /usr/local/var/run/mysqld/mysql.sock

[mysqld_safe]
pid-file = /usr/local/var/run/mysqld/mysqld.pid

Strictly speaking you do you only need to define the pid file, though I personally like to store both pid and socket files inside the /usr/local/var/run - structure. I do the same for PHP (/usr/local/var/run/php) installed from Homebrew.

Start you database server and you are done.

brew services start mariadb
steinmb
  • 91
  • 1
  • 4
2

I was solving the same issue on MacOS after upgrading from 10.15.5 to 10.15.6, mariadb installed via homebrew, version 10.5.5.

Turns out the problem is not open files limit, but lack of permissions to create PID file.

To fix it I created ~/.my.cnf file with the following content.

[mysqld_safe]
pid-file    = /var/run/mysqld/mysqld.pid

And create the mysqld.pid file using following commands (taken from this serverfault issue)

mkdir /var/run/mysqld
touch /var/run/mysqld/mysqld.pid
chown -R mysql:mysql /var/run/mysqld

That fixed the problem. MySQL/MariaDB is not complaining about opening too many files anymore.

I also needed to kill all running mysql processes (to get theri PIDs run ps aux | grep mysql and then kill {proces_number}. But maybe you don't need this step.

fandasson
  • 2,158
  • 21
  • 20
  • It doesn't work `(Errcode: 24 "Too many open files")' when trying to dump tablespaces` on Catalina 10.15.6, i was using `mysqldump` – TomSawyer Sep 14 '20 at 07:17
  • 1
    Thank you! This one worked for me. I have MariaDB installed with homebrew, added the `[mysqld_safe]...` lines into `/usr/local/etc/my.cnf` and ran `brew services restart mariadb`... waited like 15 seconds after it said it was restarted, refreshed the page and no longer stalling with that error in the log. – Kerry Johnson Oct 30 '20 at 16:08
  • 1
    TY, works well. But brew services running as user, so you need `chown -R username:staff /var/run/mysqld` or use `/usr/local/var/run/mysqld` directory – spiilmusic Nov 03 '20 at 08:27
1

I was able to resolve this issue by increasing the system limits by following the suggestions pointed out in this thread: https://discourse.brew.sh/t/mariadb-too-many-open-files/8110 and outlined in the GIST here: https://gist.github.com/tombigel/d503800a282fcadbee14b537735d202c

Adam Christianson
  • 376
  • 3
  • 12
0

I have been having the same problem using mariadb-10.2 installed via Macports on macOS 10.15.7. Checking open_files_limit shows it has reverted for an unknown reason to 256. I had tried many of the methods described in this question and elsewhere; including making sure that the ulimit is adequate (20000) and launchctl limit maxfiles is adequate (20000 and 200000), and in my.cnf there is reasonable setting for open_file_limit (3000) These parameters remain at these values and do not get changed when mariadb reverts to 256 but are remaining at the expected values.

Not a solution to the root cause but a workaround is to restart mariadb. After that, the open_files_limit for mariadb gets reset to the ulimit of 20000. Note that it does not get set to the 3000 value in in my.cnf but to the 20000 value. This solves the problem for a while.

Also note that for mariadb installed from MacPorts that the way to restart is given by the following two commands:

  1. sudo port unload mariadb-10.2-server
  2. sudo port load mariadb-10.2-server

It won't work to just perform the load, even though the console output says that it was restarted. It might be restarted but I have proven that the only way that open_files_limit gets reset is to first do the unload.

Jeffrey Simon
  • 918
  • 3
  • 11
  • 25