If cells fail to boot, it creates hundred of staled mysql connections

cells fails to boot (Failed to initialize OIDC server: server: no connectors specified), also I can’t fix the source of the issue, I noticed that after a minute or so I started getting:
failed to initialize storage: failed to open migrations: creating dex_migration table: Error 1040: Too many connections.

# mysqladmin  processlist|grep -c pydio

(All of them are in Sleep state, the Command column)

1 Like

I know, I’m bumping a year-old thread, but I have the same issue… with a slight twist: even when Cells is running after successfully booting, it still opens way too many open connections that do not get closed and remain in a sleep state ‘forever’.

The irony is that for months I have been trying to track down the too many connections error to all sorts of other applications, with zero success. Every once in a while, the server I got will struggle to connect to MariaDB — and the WordPress installations I’ve also been running on the same server, as well as other apps, fail with this error. After all, they are not to blame…

When running the many tools to optimise or fine-tune MySQL there is always the mention that ‘too many connections are being aborted’ and the possible cause given for that is recurrent authentication failures. Again, for months I have been fearing that I was being the victim of a mysterious exploit that was attempting to guess MySQL passwords — although I could find nothing on the logs that could give me the slightest clue to the origin of such alleged attacks. I even tried to go very deep into the networking level to try to figure out what’s wrong with the packets sent and received by MariaDB. And again, this was another exercise in vain: there were no mysterious ‘attacks’ or failed authentication attempts coming from anywhere.

Tracking down where exactly in the Cells code database connections are being opened but not closed will be a nightmare; to make matters worse, at the time of writing, I’m having a slight issue compiling the current Cells code on either macOS or Linux — mostly because Cells originally didn’t use Go modules, but rather a popular, pre-modules tool — so I cannot even build a custom version of Cells to help with debugging. I’ve not given up (I used to successfully compile Cells without any problems whatsoever, on earlier Go versions, so I’m sure that, eventually, I’ll manage to do it again), but, in the meantime, I defer to the developers and the community to audit the code and figure out where exactly connections are being opened but never closed, being left in a sleep state.

It would be also interesting to see if more Cells users have encountered the same issue, and, if so, how they dealt with it. Currently, my best choice is to run a cron job every now and then and clean up the sleeping threads; but that’s a crude hack, not addressing the core issue.

No wonder that my poor MariaDB server, although there are not that many processes running on the server, nevertheless consumes almost 5 GBytes (!!!) of real memory (not virtual!) — probably because it has to keep so many open connections that never get closed…

BTW, here go a few examples of how to kill stale MySQL connections periodically, using external tools.

The following script runs twice per second, which seems to be overkill, but you can adjust it according to your wishes:

A long thread with many alternative solutions, some of them as simple as one-liners that can be run directly from cron:

An alternative is to change MySQL settings to let MySQL kill it automatically (if you have access to the MySQL settings, that is; the above solutions will also work if you do not have permission to change these settings directly). Apparently, by default, a thread remains in the sleeping state for 8 hours; this can be adjusted to a much lower number.

This is also the way that database administrators on StackExchange recommend:

sorry if i’m out of bound, just scanning the thread : did you check that mysql always require to raise the max_connection value (based on the RAM consumption), as the default 151 is not ok for production ?

Speaking strictly for myself, I’ve set max_connections to 512 (and max_user_connections to zero, which means: use the value of max_connections instead).

At some point in the past, I even had raised it up to 1000, just to see if the situation improved; however, the price paid in terms of consumed RAM was simply not worth it, for the few (if any) benefits I got.

By contrast, I’m pleased to say that by changing the following settings to

interactive_timeout     = 180
wait_timeout            = 180

(3 minutes, as opposed to the built-in default, which is 8 hours)

Works wonders for me! :blush:

Note that I have a very low-traffic environment; I would guess that the heavier the load, the longer connections ought to persist, to avoid the overhead of closing and creating new connections all the time. Therefore, I would guess that the defaults are reasonable — most MySQL connections are persistent these days, due to the amount of traffic between the many applications and the database server. But not everyone’s needs are the same; that’s why there is no ‘perfect’ configuration (or MySQL would come, by default, with the ‘best’ configuration built-in!) but rather each particular environment needs its own tweaks…

Since, over a year, nobody else but @drzraf and myself seems to be bothered by this particular issue, it’s a good guess that the vast majority of Cells users have completely different needs, for whom this issue is not really important…