Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

mysql max user connection reached, triggers errors in widgets #2574

Closed
anonymous-matomo-user opened this issue Jul 19, 2011 · 17 comments
Closed
Labels
Bug For errors / faults / flaws / inconsistencies etc.
Milestone

Comments

@anonymous-matomo-user
Copy link

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
Copy link
Contributor

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
Copy link
Contributor

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

@robocoder
Copy link
Contributor

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

@mattab
Copy link
Member

mattab commented Jul 22, 2011

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
Copy link
Contributor

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
Copy link
Contributor

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

@anonymous-matomo-user
Copy link
Author

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

Please reopen!

@robocoder
Copy link
Contributor

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

@mattab
Copy link
Member

mattab commented Aug 10, 2011

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

@anonymous-matomo-user
Copy link
Author

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
Copy link
Member

mattab commented Aug 10, 2011

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
Copy link
Author

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
Copy link
Author

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

Same remarks about random.
No special steps to replicate.

@mattab
Copy link
Member

mattab commented Aug 10, 2011

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
Copy link
Member

mattab commented Aug 10, 2011

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
Copy link
Contributor

I'll fix this tonight when I get home.

@robocoder
Copy link
Contributor

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

This issue was closed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Bug For errors / faults / flaws / inconsistencies etc.
Projects
None yet
Development

No branches or pull requests

3 participants