MySQL server has gone away

Support & Bugs
1

Hi there,

I’m getting the following error. The odd thing is that MySQL (MariaDB) is not crashing or throwing any errors in the syslog.

INFO [2019-02-19 08:15:20] 6214  ---------------------------
INFO [2019-02-19 08:15:20] 6214  INIT
INFO [2019-02-19 08:15:20] 6214  Running Matomo 3.8.1 as Super User
INFO [2019-02-19 08:15:20] 6214  ---------------------------
INFO [2019-02-19 08:15:20] 6214  NOTES
INFO [2019-02-19 08:15:20] 6214  - Reports for today will be processed at most every 150 seconds. You can change this value in Matomo UI > Settings > General Settings.
INFO [2019-02-19 08:15:20] 6214  - Reports for the current week/month/year will be requested at most every 3600 seconds.
INFO [2019-02-19 08:15:20] 6214  - Archiving was last executed without error 3 min 13s ago
INFO [2019-02-19 08:15:20] 6214  - Will process 1 other websites because some old data reports have been invalidated (eg. using the Log Import script or the InvalidateReports plugin) , IDs: 4
INFO [2019-02-19 08:15:20] 6214  ---------------------------
INFO [2019-02-19 08:15:20] 6214  START
INFO [2019-02-19 08:15:20] 6214  Starting Matomo reports archiving...
INFO [2019-02-19 08:15:20] 6214  - tracking data found for website id 1 since 2019-02-19 08:12:07 UTC (since the last successful archiving)
INFO [2019-02-19 08:15:20] 6214  Will pre-process for website id = 1, period = day, date = last2
INFO [2019-02-19 08:15:20] 6214  - pre-processing all visits
INFO [2019-02-19 08:15:22] 6214  Archived website id = 1, period = day, 0 segments, 2658 visits in last 2 days, 380 visits today, Time elapsed: 1.442s
INFO [2019-02-19 08:15:22] 6214  Will pre-process for website id = 1, period = week, date = last93
INFO [2019-02-19 08:15:22] 6214  - pre-processing all visits
INFO [2019-02-19 08:15:25] 6214  Archived website id = 1, period = week, 0 segments, 1639711 visits in last 93 weeks, 2658 visits this week, Time elapsed: 2.641s
INFO [2019-02-19 08:15:25] 6214  Will pre-process for website id = 1, period = month, date = last52
INFO [2019-02-19 08:15:25] 6214  - pre-processing all visits
INFO [2019-02-19 08:15:37] 6214  Archived website id = 1, period = month, 0 segments, 2162215 visits in last 52 months, 41804 visits this month, Time elapsed: 12.447s
INFO [2019-02-19 08:15:37] 6214  Will pre-process for website id = 1, period = year, date = last7
INFO [2019-02-19 08:15:37] 6214  - pre-processing all visits
ERROR [2019-02-19 08:27:15] 6214  Got invalid response from API request: ?module=API&method=API.get&idSite=1&period=year&date=last7&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:139:"SQLSTATE[HY000]: General error: 2006 MySQL server has gone away, caused by: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away";}'
INFO [2019-02-19 08:27:15] 6214  Archived website id = 1, period = year, 0 segments, 0 visits in last 7 years, 0 visits this year, Time elapsed: 698.277s
INFO [2019-02-19 08:27:15] 6214  Archived website id = 1, 4 API requests, Time elapsed: 714.810s [1/19 done]
WARNING [2019-02-19 08:27:15] 6214  /var/www/piwik/htdocs/libs/Zend/Db/Statement/Pdo.php(228): Warning - PDOStatement::execute(): MySQL server has gone away - Matomo 3.8.1 - Please report this message in the Matomo forums: https://forum.matomo.org (please do a search first as it might have been reported already)
WARNING [2019-02-19 08:27:15] 6214  /var/www/piwik/htdocs/libs/Zend/Db/Statement/Pdo.php(228): Warning - PDOStatement::execute(): Error reading result set's header - Matomo 3.8.1 - Please report this message in the Matomo forums: https://forum.matomo.org (please do a search first as it might have been reported already)



  [Zend_Db_Statement_Exception]
  SQLSTATE[HY000]: General error: 2006 MySQL server has gone away






  [PDOException]
  SQLSTATE[HY000]: General error: 2006 MySQL server has gone away



core:archive [--url="..."] [--force-all-websites] [--force-all-periods[="..."]] [--force-timeout-for-periods[="..."]] [--skip-idsites[="..."]] [--skip-all-segments] [--force-idsites[="..."]] [--force-periods[="..."]] [--force-date-last-n="..."] [--force-date-range[="..."]] [--force-idsegments="..."] [--concurrent-requests-per-website[="..."]] [--concurrent-archivers[="..."]] [--disable-scheduled-tasks] [--accept-invalid-ssl-certificate] [--php-cli-options[="..."]]

What could be the cause and how can we resolve it?

2

Might be the wait_time setting which I increased from 10 to 30 Minutes for testing.

3

https://matomo.org/faq/troubleshooting/faq_183/

4

Did you fix the mysql error?

5

yes, it was the wait_time setting

6

I am getting this issue as well. I have narrowed it down to PHP 7.2 being my cause. If I move back to PHP 7.1, then the error goes away. This is the error in the CPanel Errors Log: -

6 /home/mar in /home/marathon/subdomains/analytics/libs/Zend/Db/Statement/Mysqli.php on line 77
#4 /home/marathon/subdomains/analytics/core/Session/SaveHandler/DbTable.php(145): Piwik\Session\SaveHandler\DbTable->query('INSERT INTO piw...', Array)
#3 /home/marathon/subdomains/analytics/core/Session/SaveHandler/DbTable.php(113): Zend_Db_Adapter_Abstract->query('INSERT INTO piw...', Array)
#2 /home/marathon/subdomains/analytics/libs/Zend/Db/Adapter/Abstract.php(478): Zend_Db_Adapter_Mysqli->prepare('INSERT INTO piw...')
#1 /home/marathon/subdomains/analytics/libs/Zend/Db/Adapter/Mysqli.php(417): Zend_Db_Statement->__construct(Object(Piwik\Db\Adapter\Mysqli), 'INSERT INTO piw...')
#0 /home/marathon/subdomains/analytics/libs/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli->_prepare('INSERT INTO piw...')
2019-03-26 15:09:13.144722 [NOTICE] [203.206.238.183:63301-Q:14487464421269129577-13] [STDERR] PHP Fatal error:  Uncaught Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: MySQL server has gone away in /home/marathon/subdomains/analytics/libs/Zend/Db/Statement/Mysqli.php:77

Within Matomo 3.9.1, I get Dashboard errors (“Oops, there was a problem during the request) and I cannot run a System Check. It returns the error " Mysqli prepare error: MySQL server has gone away”. I have asked my host to follow the FAQ that deals with that issue, but those settings were already appropriate.

Anyone got any ideas why PHP 7.2 would cause this issue?

7

I’ve had the exact same problem for a week or two now. I don’t have access to my.cnf to follow steps in the FAQ given I’m on a shared server. Just rolled back to PHP 7.1 and it’s fixed. Thanks Mark! No idea why it wouldn’t work on 7.2 or 7.3 though…hopefully this can be addressed soon.

8

Same here. In PHP 7.2 and 7.3 Matomo uses huge amounts of memory and I see “MySQL server has gone away” errors. In PHP 7.1 there’s no problem at all.

Matomo version: 3.9.1
MySQL version: 5.5.62-cll-lve
PHP version: 7.1.27

Perhaps of interest (I haven’t tried to establish a relation to the issue myself); my install was lacking in mainenance:

I had some ‘filesystem integrety issues’:

  • File to delete: error_log
  • File to delete: js/error_log
  • File to delete: misc/internal-docs/content-tracking.md
  • File to delete: misc/others/api_internal_call.php

and I was using the Legacy GeoIP for geolocation.

9

I am having the same problem. I changed the recommended settings(below) and it still persists.

Matomo: 3.9.1
PHP: 7.1.8
MariaDB: 5.5.60

  • max_allowed_packet=512M
  • innodb_log_file_size = 256M
  • wait_timeout = 31536000

In the Mariadb Log I do see:

[Warning] Aborted connection 132167 to db: '{Database}' user: '{User}' host: '{Web Server}' (Got an error reading communication packets)
11

I may have resolved my particular issue by setting mysqli.reconnect = On in my PHP.ini file and restarting PHP-FPM.

12

I had the same problem and I think I fixed it with the php.ini setting:
mysqlnd.net_read_timeout = 31536000

See: https://www.php.net/manual/en/mysqlnd.config.php
" Available since PHP 5.3.0. Before PHP 7.2.0 the default value was “31536000” and the changeability was PHP_INI_SYSTEM"