mysql max user connection reached, triggers errors in widgets

[anonymous-matomo-user]

After updating from 1.4 to 1.5, I now have the following message displayed in some widgets of the dashboard:
"The Piwik configuration file couldn't be found and you are trying to access a Piwik page.
You can install Piwik now
If you installed Piwik before and have some tables in your DB, don't worry, you can reuse the same tables and keep your existing data!"

The widgets affected change if I refresh the page. It usually affects Feedburner + Best search engines widgets, but also sometimes Visitors in Real Time, List of External webistes, etc.

[robocoder]

There are two places where you may be redirected to the installer:

• the first is when the configuration file can't be read
• the second is when Piwik can't connect to the database (which we generally attribute to out-of-date database connection info in the config file), e.g., when someone uses cpanel to change their db user password.

The try...catch for the second case is masking the true cause, but from the description, I'll speculate it's an edge case similar to that described in #885, i.e., perhaps the MySQL-based session store doesn't block on session_start()?

Some things to try:

• increase the max number of concurrent MySQL connections
• in core/FrontController.php (around line 88), try changing:

                if(($module !== 'API' || ($action && $action !== 'index'))
                        && !$sessionStarted
                        && (!defined('PIWIK_ENABLE_SESSION_START') || PIWIK_ENABLE_SESSION_START))

to:

                if(!$sessionStarted
                        && (!defined('PIWIK_ENABLE_SESSION_START') || PIWIK_ENABLE_SESSION_START))

[robocoder]

Reference: session_start() blocks https://bugs.php.net/bug.php?id=31464

[robocoder]

The only "fix" appears to be allowing users to go back to using file-based sessions.

[mattab]

Why do you think the code path is triggered with mysql based sessions? Why config file parsing OR DB connection fail since we moved to mysql based sessions?

[robocoder]

With session.save_handler = files, session_start() will causes other requests (associated with the same session) to block (because PHP uses a file-based lock). This causes things like the dashboard to appear to load slower because API requests are being processing serially. Also, the session_start() occurred in index.php before a MySQL connection is established.

With the MySQL-backed session handler, session_start() no longer blocks. Also, on the dashboard, this causes Piwik to open more MySQL connections concurrently (one per widget). (Perhaps uses table, row, or advisory locks?)

A related side-effect is that multiple browser-based archiving processes can be kicked off.

[robocoder]

(In [5052]) fixes #2602, fixes #2574, refs #2548, refs #2597

[anonymous-matomo-user]

Changeset 5052 does not solve the problem. I applied the changes manually but still have the "Configuration file not found issue".

Please reopen!

[robocoder]

Please try using the nightly QA build. http://qa.piwik.org:8080/nightly/

[mattab]

I can't replicate the issue anymore, peutch can you please confirm if the nightly build fixes it for you?

[anonymous-matomo-user]

No, QA build hasn't solved the problem. It seems that the problem is not due to using database sessions instead of file sessions. People (including me) still have the same problem, even when using the QA build (and checking that file session is used rather than db sessions).

See current discussion here: http://forum.piwik.org/read.php?2,78044

Someone suggested it might be due to too many AJAX requests at once.

[mattab]

peutch what PHP version are you using? what OS ? any other info?

can you replicate the issue consistently or is it random?

[anonymous-matomo-user]

OS: CentOS 5.6, PHP 5.2.17 (IUS repo), MySQL 5.0.77

This issue is not really "random", it is only random in that it affects different widgets each time or may (very seldomly) pause for one refresh of the dashboard.

I don't think that the software version matters. The problem occurs because of the widgets making simultaneous Ajax calls and those calls opening simultaneous independant MySQL connections, so that you have several queries running at the same time for the same user. You can reproduce the issue by limiting global max_user_connections in my.cnf or per-user connections in MySQL grants (limiting to 5 or lower should do).

That the issue didn't occur earlier probably means that either some of the widget queries are taking longer now (shorter query runtime lowers the chance of hitting the limit) or that the order of widget queries has changed (slow queries coming first) or the code processes the widgets faster (increases the chance that queries are carried out at the same time) or a combination or something similar.

The key for reproduction is limiting the user connections in MySQL.

[anonymous-matomo-user]

Linux web290.start.ha.ovh.net
PHP Version 5.2.17
Mysql 5.5

Same remarks about random.
No special steps to replicate.

[mattab]

OK got it now, thanks for explaining clearly.

Maybe we should use a "persisted connection", but not sure if this is possible with mysql on all platforms.

Otherwise we could put a little delay, maybe 50ms, between widgets, but it might feel a bit slower on the UI then which could be a shame.

Or, we could catch the error, and automatically retry to load the ajax 500ms later. That might be a better solution?

[mattab]

Note, that the changeset [5065] implements uses of advisory locks in the archiving process, but it doesn't help this use case as expected: these advisory locks require mysql connexion.

I think the "widget ajax retry" after error response caught (special message/http response code?) half a second later might be the best solution.

[robocoder]

I'll fix this tonight when I get home.

[robocoder]

(In [5092]) fixes #2574 - start sessions (and lock session) before creating db connections