~ overflow ~

Problem:
MySQL has reached max connections and you can’t login, even being root.

Solution:
You can either edit my.cnf raising the limit and logging in or you can do this hack:

gdb -p $(cat /var/run/mysqld/mysqld.pid) \
-ex "set max_connections=5000" -batch

What’s the difference?

By changing my.cnf you will need to restart the server, if you got the limit reached long time ago lots of queued connections will instantly flood the server once you reset it, making impossible to access it within a few seconds. So you will need to keep restarting it over and over again until all the queue is gone. In some cases you can’t even restart it, server gets stuck trying to close connections, then you need to kill mysqld, but that can generate broken tables. Sometimes only a full system restart fixes the issue.

By changing the value with the hack, you will be able to raise the max connections without restarting mysqld, which allows you to login and check what’s going on, even run some scripts to clean up idle/stuck connections, making things easier.

Problem:

When importing a .sql with entries with accents, like not regular english, it may lead to issues, like:

‘São Paulo‘ instead of ‘São Paulo‘

Solution:

Even mysqld default charset being latin1, sometimes it don’t work with accents, depending on the imports you’re doing.

So you may need to force it to fallback to utf8, on my case i just added this to the beggining of the .sql file i was importing:

charset utf8 \c

and it worked just fine.

Note: If you are using asian chars (japanese/chinese specific), then utf8 might not be enough to cover all chars.

Problem:

When starting mysqld i got the following error:

Timeout error occurred trying to start MySQL Daemon.

Solution:

After checking mysqld.log i found out that the tunning i did on my.cnf had some options that aren’t compatible with the mysqld version.