General error: 2006 MySQL server has gone away during archive run


#1

Hi all,
I’m having issues with the ‘error: 2006 MySQL server has gone away’ that is referenced often in these forums. Unfortunately, I’ve tried adjusting both values as advised in the FAQ @ How do I fix the error “Mysql Server has gone away”? - Analytics Platform - Matomo but neither seem to be addressing the issue.

I recently upgraded from 2.0.3 to 2.4.1 and added some segments so I dropped all the archive and blob tables and re-archived using “–force-all-websites --force-all-periods=315576000 --force-date-last-n=1000”. I ran this in a loop until the exit status was 0 which took 5 runs to achieve. After this, I tried just running the regularly scheduled archive run without the 3 force options but as you can see in the below results the issue persists.

I have tried increasing max_allowed_packet to as high as 100MB and wait_timeout was already set to the default of 8 hours which is nowhere near as long as this job is running so I don’t think it should be coming into play.

I also increased the innodb_buffer_pool_size which significantly reduced the amount of time it takes before the error is reached due to less disk I/O as more works is done in this RAM pool than on disk. Previously it would take ~25minutes to reach the error whereas now it takes ~10minutes.

It always seems to error out at roughly the same time - which is immediately after reporting being done with the monthly archives. I’m wondering if possibly it is trying to re-use a mysql connection it expects to be open but has in fact been closed?

Any help would be greatly appreciated,
-Fleish

INFO CoreConsole[2014-08-26 22:06:01] [b33e0] ---------------------------
INFO CoreConsole[2014-08-26 22:06:01] [b33e0] INIT
INFO CoreConsole[2014-08-26 22:06:01] [b33e0] Piwik is installed at: https://somehost.somedomain.sometld/index.php
INFO CoreConsole[2014-08-26 22:06:01] [b33e0] Running Piwik 2.4.1 as Super User
INFO CoreConsole[2014-08-26 22:06:02] [b33e0] ---------------------------
INFO CoreConsole[2014-08-26 22:06:02] [b33e0] NOTES
INFO CoreConsole[2014-08-26 22:06:02] [b33e0] - Reports for today will be processed at most every 60 seconds. You can change this value in Piwik UI > Settings > General Settings.
INFO CoreConsole[2014-08-26 22:06:02] [b33e0] - Reports for the current week/month/year will be refreshed at most every 3600 seconds.
INFO CoreConsole[2014-08-26 22:06:02] [b33e0] - Archiving was last executed without error 5 hours 21 min ago
INFO CoreConsole[2014-08-26 22:06:03] [b33e0] - Will process 1 websites with new visits since 5 hours 21 min , IDs: 1
INFO CoreConsole[2014-08-26 22:06:03] [b33e0] ---------------------------
INFO CoreConsole[2014-08-26 22:06:03] [b33e0] START
INFO CoreConsole[2014-08-26 22:06:03] [b33e0] Starting Piwik reports archiving…
INFO CoreConsole[2014-08-26 22:06:40] [b33e0] Will pre-process the following 6 Segments for this website (id = 1): actions==1, deviceType==Desktop, deviceType==smartphone,deviceType==tablet,deviceType==feature%20phone, countryCode!=us, countryCode==us, visitDuration<11
INFO CoreConsole[2014-08-26 22:07:47] [b33e0] Archived website id = 1, period = day, xxx visits in last last2 days, xxx visits today, Time elapsed: 104.516s
INFO CoreConsole[2014-08-26 22:07:47] [b33e0] Will pre-process the following 6 Segments for this website (id = 1): actions==1, deviceType==Desktop, deviceType==smartphone,deviceType==tablet,deviceType==feature%20phone, countryCode!=us, countryCode==us, visitDuration<11
INFO CoreConsole[2014-08-26 22:09:40] [b33e0] Archived website id = 1, period = week, xxx visits in last last3 weeks, xxx visits this week, Time elapsed: 113.055s
INFO CoreConsole[2014-08-26 22:09:40] [b33e0] Will pre-process the following 6 Segments for this website (id = 1): actions==1, deviceType==Desktop, deviceType==smartphone,deviceType==tablet,deviceType==feature%20phone, countryCode!=us, countryCode==us, visitDuration<11
INFO CoreConsole[2014-08-26 22:15:16] [b33e0] Archived website id = 1, period = month, xxx visits in last last3 months, xxx visits this month, Time elapsed: 335.925s
ERROR CoreConsole[2014-08-26 22:15:16] [b33e0] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away