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

[3.0.0-b1] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away #10744

Closed
apapillon opened this issue Oct 16, 2016 · 15 comments
Closed
Labels
answered For when a question was asked and we referred to forum or answered it.

Comments

@apapillon
Copy link

Since update to 3.0.0-b1, when I login, I got this error : SQLSTATE[HY000]: General error: 2006 MySQL server has gone away.

My SQL log :

DEBUG Piwik\Plugin\Manager[2016-10-12 20:28:14] [c16cc] Loaded plugins: CorePluginsAdmin, CoreAdminHome, CoreHome, WebsiteMeasurable, Diagnostics, CoreVisualizations, Proxy, API, Widgetize, Transitions, LanguagesManager, Actions, Dashboard, MultiSites, Referrers, UserLanguage, DevicesDetection, Goals, SEO, Events, UserCountry, VisitsSummary, VisitFrequency, VisitTime, VisitorInterest, ExampleAPI, RssWidget, Feedback, Monolog, Login, UsersManager, SitesManager, Installation, CoreUpdater, CoreConsole, ScheduledReports, UserCountryMap, Live, CustomVariables, PrivacyManager, ImageGraph, Annotations, MobileMessaging, Overlay, SegmentEditor, Insights, Morpheus, Contents, BulkTracking, Resolution, DevicePlugins, Heartbeat, Intl, ProfessionalServices, UserId, CustomPiwikJs, Provider, BotTracker, FreeMobileMessaging

DEBUG Piwik\Profiler[2016-10-12 20:28:14] [c16cc] <hr />

<strong>SQL Profiler</strong>
<hr />
<strong>Summary</strong><br/>
Executed 13 queries in 0.019 seconds(Average query length: 0.001 seconds)<br />
Queries per second: 682.3<br />
Longest query length: 0.007 seconds (
<code>
INSERT INTO `piwik_option` (option_name, option_value, autoload)  VALUES (?, ?, ?)  ON DUPLICATE KEY UPDATE option_value = ?
</code>
)

DEBUG Piwik\Profiler[2016-10-12 20:28:14] [c16cc] <hr />
<strong>Breakdown by query</strong><br/>
Executed <b>1</b> time in <b>7.1ms</b>  
<pre>
    INSERT INTO `piwik_option` (option_name, option_value, autoload) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE option_value = ?
</pre>
Executed <b>1</b> time in <b>5.1ms</b>
<pre>
    INSERT INTO piwik_session (id,modified,lifetime,data) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE modified = ?,lifetime = ?,data = ?
</pre>
Executed <b>1</b> time in <b>3.4ms</b>  
<pre>
    connect
</pre>
Executed <b>3</b> times in <b>2.3ms</b>  (average = <b>0.8ms</b>)
<pre>
    SELECT option_value FROM `piwik_option` WHERE option_name = ?
</pre>
Executed <b>2</b> times in <b>1.4ms</b>  (average = <b>0.7ms</b>)
<pre>
    SELECT language FROM piwik_user_language WHERE login = ? 
</pre>
Executed <b>1</b> time in <b>0.5ms</b>  
<pre>
    SELECT * FROM piwik_site WHERE idsite = ?
</pre>
Executed <b>1</b> time in <b>0.4ms</b>  
<pre>
    SELECT data FROM piwik_session WHERE id = ? AND modified + lifetime >= ?
</pre>
Executed <b>1</b> time in <b>0.4ms</b>  
<pre>
    SELECT option_value, option_name FROM `piwik_option` WHERE autoload = 1
</pre>
Executed <b>1</b> time in <b>0.3ms</b>  
<pre>
    SELECT DATABASE()
</pre>
Executed <b>1</b> time in <b>0.2ms</b>  
<pre>
    SELECT * FROM piwik_user WHERE login = ?
</pre>
DEBUG Piwik\Profiler[2016-10-12 20:28:14] [c16cc] Total queries = 13 (total sql time = 0.02s)
@Firesphere
Copy link

That sounds like your database server is crashing and restarted. What's your hosting type?

@apapillon
Copy link
Author

My database server is done. It hosting by OVH Web hosting and I can connect to this database with phpmyadmin.

@zebig
Copy link

zebig commented Oct 19, 2016

Hello. I had same error messages (hosting by OVH).
Resolved using http://www.mydomain.ext instead of http://mydomain.ext

@apapillon
Copy link
Author

That not resolve my issue. I don't known what I can make to resolve this issue.

@zebig
Copy link

zebig commented Oct 20, 2016

It seems that this issue occurs only for "superusers", in attempt to load "administration page" or "profile page". In this case, PIWIK does not access quicky enough to the page and the error message appears.
"Normal users" access quicky to their profile page.
I don't know if that can help to understand what appends.

@apapillon
Copy link
Author

I confirm only "superuser" have this issue. Other users with "admin access" for all site have not this issue.

@tsteur tsteur closed this as completed Oct 30, 2016
@tsteur tsteur reopened this Oct 30, 2016
@tsteur
Copy link
Member

tsteur commented Oct 30, 2016

Have you had a look at http://piwik.org/faq/troubleshooting/faq_183/ ? Maybe it helps to fix the issue. The queries look normal otherwise.

@apapillon
Copy link
Author

I have seen this faq but I can't change my MYSQL configuration. Piwik Db was hosted on OVH web hosting. I have this issue only since 3.0.0-b1 upgrade.

@apapillon
Copy link
Author

apapillon commented Oct 30, 2016

The traceback :

#0 /home/perhonen/piwik/libs/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/perhonen/piwik/core/Db/Adapter/Pdo/Mysql.php(241): Zend_Db_Statement->execute(Array)
#2 /home/perhonen/piwik/libs/Zend/Db/Adapter/Abstract.php(827): Piwik\Db\Adapter\Pdo\Mysql->query('SELECT option_v...', 'apapillon2_defa...')
#3 /home/perhonen/piwik/core/Db.php(259): Zend_Db_Adapter_Abstract->fetchOne('SELECT option_v...', 'apapillon2_defa...')
#4 /home/perhonen/piwik/core/Option.php(179): Piwik\Db::fetchOne('SELECT option_v...', 'apapillon2_defa...')
#5 /home/perhonen/piwik/core/Option.php(46): Piwik\Option->getValue('apapillon2_defa...')
#6 /home/perhonen/piwik/plugins/UsersManager/API.php(175): Piwik\Option::get('apapillon2_defa...')
#7 /home/perhonen/piwik/plugins/UsersManager/API.php(110): Piwik\Plugins\UsersManager\API->getPreferenceValue('apapillon2', 'defaultReport')
#8 /home/perhonen/piwik/plugins/UsersManager/UserPreferences.php(65): Piwik\Plugins\UsersManager\API->getUserPreference('apapillon2', 'defaultReport')
#9 /home/perhonen/piwik/plugins/UsersManager/UserPreferences.php(39): Piwik\Plugins\UsersManager\UserPreferences->getDefaultReport()
#10 /home/perhonen/piwik/plugins/Goals/Menu.php(21): Piwik\Plugins\UsersManager\UserPreferences->getDefaultWebsiteId()
#11 /home/perhonen/piwik/core/Menu/MenuAdmin.php(132): Piwik\Plugins\Goals\Menu->configureAdminMenu(Object(Piwik\Menu\MenuAdmin))
#12 /home/perhonen/piwik/core/Plugin/Controller.php(644): Piwik\Menu\MenuAdmin->getMenu()
#13 /home/perhonen/piwik/plugins/CoreHome/Controller.php(165): Piwik\Plugin\Controller->setGeneralVariablesView(Object(Piwik\View))
#14 /home/perhonen/piwik/plugins/CoreHome/Controller.php(203): Piwik\Plugins\CoreHome\Controller->getDefaultIndexView()
#15 [internal function]: Piwik\Plugins\CoreHome\Controller->index()
#16 /home/perhonen/piwik/core/FrontController.php(518): call_user_func_array(Array, Array)
#17 /home/perhonen/piwik/core/FrontController.php(122): Piwik\FrontController->doDispatch(NULL, NULL, NULL)
#18 /home/perhonen/piwik/core/dispatch.php(34): Piwik\FrontController->dispatch()
#19 /home/perhonen/piwik/index.php(27): require_once('/home/perhonen/...')
#20 {main}

@apapillon
Copy link
Author

I have max_allowed_packet = 16777216 and wait_timeout = 60.
My max_allowed_packet are 16MB and your FAQ recommend 128MB.

@tsteur
Copy link
Member

tsteur commented Nov 1, 2016

Does OVH not allow you to change this value? Try to email them, they might show you a way to do it, or can maybe change it for you?

@zebig
Copy link

zebig commented Nov 1, 2016

Hello
I do not agree to change this parameter max_allowed_packet. This could solve the problem, but I think this is not a good solution, because it masks the reason of errors.
The questions are to know:

  • why the error did not occur before and why it appears after the last update (for me : Piwik 2.16.5) ?
  • why this error concerns only superusers?

What is changed since?

@apapillon
Copy link
Author

apapillon commented Nov 1, 2016

I totally agree with zebig.

I made different tests :

  • another web server with another Mysql server with same database : That
    work fine.
  • another Mysql server with same web server and same database : same issue
    "MySQL server has gone away".

I tested with copy of my database on another MySQL server with
max_allowed_packet at 128MB and same web server, I obtained same issue
"MySQL server has gone away".

I think this issue come from Web server or PHP but not Mysql configuration.

You can find my PHP info on : https://perhonen.fr/projects/info.php
I tested with PHP5.6 with same issue, config : http://fpm5.6-check.cluster003.ovh.net/phpinfo.php

@apapillon
Copy link
Author

Ok I'm so sorry but now I don't have any issue on OVH web hosting with superuser but I don't know why.

@tsteur tsteur added the answered For when a question was asked and we referred to forum or answered it. label Nov 1, 2016
@mattab
Copy link
Member

mattab commented Nov 3, 2016

I do not agree to change this parameter max_allowed_packet. This could solve the problem, but I think this is not a good solution, because it masks the reason of errors.

FYI it doesn't mask the reason of errors, but it actually solves the error properly here. what happens is your packet of data containing your piwik pre-processed reports is too big for the default value in MySQL.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
answered For when a question was asked and we referred to forum or answered it.
Projects
None yet
Development

No branches or pull requests

5 participants