General error: 2006 MySQL server has gone away

Support & Bugs
1

Hi,

It’s been almost a week now that piwik archiving script fails during year archiving. Here is what typically happens:


[11:07:58] [root@vweb1 /etc/cron.daily]# /usr/bin/php /var/webs/wwwpiwik/www/console core:archive --url=http://mystats.noa.gr
INFO CoreConsole[2014-06-07 08:10:11] [1be75] ---------------------------
INFO CoreConsole[2014-06-07 08:10:11] [1be75] INIT
INFO CoreConsole[2014-06-07 08:10:11] [1be75] Piwik is installed at: http://mystats.noa.gr/index.php
INFO CoreConsole[2014-06-07 08:10:11] [1be75] Running Piwik 2.3.0 as Super User
INFO CoreConsole[2014-06-07 08:10:12] [1be75] ---------------------------
INFO CoreConsole[2014-06-07 08:10:12] [1be75] NOTES
INFO CoreConsole[2014-06-07 08:10:12] [1be75] - Reports for today will be processed at most every 21600 seconds. You can change this value in Piwik UI > Settings > General Settings.
INFO CoreConsole[2014-06-07 08:10:12] [1be75] - Reports for the current week/month/year will be refreshed at most every 3600 seconds.
INFO CoreConsole[2014-06-07 08:10:12] [1be75] - Archiving was last executed without error 7 days 4 hours ago
INFO CoreConsole[2014-06-07 08:10:30] [1be75] - Will process 1 websites with new visits since 7 days 4 hours , IDs: 1
INFO CoreConsole[2014-06-07 08:10:31] [1be75] ---------------------------
INFO CoreConsole[2014-06-07 08:10:31] [1be75] START
INFO CoreConsole[2014-06-07 08:10:31] [1be75] Starting Piwik reports archiving...
INFO CoreConsole[2014-06-07 08:10:52] [1be75] Archived website id = 1, period = day, 16798 visits in last 2 days, 1646 visits today, Time elapsed: 21.395s
INFO CoreConsole[2014-06-07 08:17:24] [1be75] Archived website id = 1, period = week, 473268 visits in last 9 weeks, 75718 visits this week, Time elapsed: 391.354s
INFO CoreConsole[2014-06-07 08:17:26] [1be75] Archived website id = 1, period = month, 1069681 visits in last 9 months, 91171 visits this month, Time elapsed: 2.212s
ERROR CoreConsole[2014-06-07 09:30:26] [1be75] Got invalid response from API request: http://mystats.noa.gr/index.php?module=API&method=API.get&idSite=1&period=year&date=last7&format=php&token_auth=a0709b20762bae2088c915aa19a461d2&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:63:"SQLSTATE[HY000]: General error: 2006 MySQL server has gone away";}'
ERROR CoreConsole[2014-06-07 09:30:26] [1be75] Got invalid response from API request: http://mystats.noa.gr/index.php?module=API&method=API.get&idSite=1&period=year&date=last7&format=php&token_auth=a0709b20762bae2088c915aa19a461d2&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:63:"SQLSTATE[HY000]: General error: 2006 MySQL server has gone away";}'
INFO CoreConsole[2014-06-07 09:30:26] [1be75] Archived website id = 1, period = year, 0 visits in last 7 years, 0 visits this year, Time elapsed: 4380.614s
INFO CoreConsole[2014-06-07 09:30:26] [1be75] Archived website id = 1, 4 API requests, Time elapsed: 4795.585s [1/1 done]
ERROR CoreConsole[2014-06-07 09:30:27] [1be75] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
ERROR CoreConsole[2014-06-07 09:30:27] [1be75] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

I am running piwik v2.3.0.on CentOS 6.5 x86_64 under NGiNX 1.6.0 with php-fpm, php 5.3.3 and MySQL 5.5.34.

I have followed the recommendation at:How do I fix the error “Mysql Server has gone away”? - Analytics Platform - Matomo and added: “max_allowed_packet = 128M” to MySQL but it did not change anything.

Can you please advise me on how to resolve this situation?

Thanks,
Nick

2

edit: the error is “Mysql server has gone away”. The solution to this can be found in this FAQ: How do I fix the error “Mysql Server has gone away”? - Analytics Platform - Matomo

3

Solutions in the FAQ don’t work for me.

4

Can you expand on what exactly is happening? Are you using the latest version of Piwik?

5

Solution in FAQ should def work. you maybe forgot to restart mysql?

6

I was able to fix this error by adding the following code:

use Piwik\Db;
$db = Db::get();
$db->query( 'SET SESSION wait_timeout = 9000' );

to the console file that resides in the root Matomo directory, before the bit that says:

$console = new Piwik\Console();
$console->run();

I don’t know if there is a better way to do this? i.e. a more software-grade way of working that query into Matomo.

In short this query sets wait_timeout to 9,000 seconds, just for the session. So it avoids you having to set it globally on your system, which may cause issues.

7

I used your code, can not work. could you help me how to fix the mysql error when archive.