MySQL 5.7+ SQLSTATE[HY000]: General error: 2006 MySQL server has gone away - caused by plugin Actions

[oraclerob]

Upgrading MySQL to 5.7 on a previous working Piwik has broken it. Most of the dashboard widgets now error with:

Referrer Websites
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away - caused by plugin Actions
Widget
Overview
SQLSTATE[HY000]: General error: 2006 MySQL server has gone away - caused by plugin Actions

As per the FAQ I have increased the following parameters:
wait_timeout=100000
max_allowed_packet=128M

Restarted and so on. Even tried reimporting the database and removing the character sets as per another solution.

[oraclerob]

MySQL logs show the following:

2015-12-21T02:49:29.398682Z 49 [Note] Aborted connection 49 to db: 'piwik' user: 'root' host: 'localhost' (Got an error reading communication packets)

[oraclerob]

Sorry forgot to post the versions tested that product the error.
2.0.15 and 2.15.1-b10

[mattab]

@oraclerob please try using our beta 11 (just released), which should work well with mysql 5.7 ? Also try to restart mysql after changing the settings. And also try setting max_allowed_packet to 512M ?

[oraclerob]

Thanks for the quick response. As requested I have made the change:
max_allowed_packet=512M

Restarted MySQL and upgraded to:
About Piwik 2.15.1-b11

This has not resolved the problems. Some of the dashboard is working - so I believe it is making MySQL connections correctly - however the only working items on the dashboard are:
Visitors in real time
Visitor Log
Returning visitors over time

Every other item on the dashboard is broken.

[oraclerob]

Switching my Piwik 2.15.1-b11 install back to MySQL 5.0.21 immediately fixes the problem. I'm also running on Windows with Php v7.0. This config however works properly on MySQL 5.0.21 so I'm inclined to think that there is an issue with piwik on MySQL 5.7.

[tsteur]

Can you try setting 127.0.0.1 instead of localhost? It might sound weird but I once had such an issue a while ago (not with MySQL 5.7)

[SS10house]

I am having the same issue with a clean install of PIWIK 2.15.1-b11 with Mysql 5.7
I have followed all troubleshooting steps here and cannot solve.

I just did a clean install of 2.15.1-b11, specifying "127.0.0.1" instead of "localhost"
issue is still there

Edit: I should not have bothered. Turns out my issue was an out of memory error, solution was to allocate more ram to server.

[tsteur]

@SS10house glad to hear it works 👍

@oraclerob I just tried to reproduce with PHP7 + MySQL 5.7 but works fine for me (under Linux, not Windows). Maybe memory is a problem similar to @SS10house ? Can you try to specify an IP for database in config/config.ini.php instead of localhost? Probably won't change anything but worth a try

[oraclerob]

Tried the suggestion no luck and increased php memory to 512MB from 256MB. I would be interested if @SS10house is using windows with PHP7 though. I'm see the following warning in my PHP logs if this is a clue:
[22-Dec-2015 23:47:28 UTC] PHP Warning: mt_srand() expects parameter 1 to be integer, float given in xxxxxxxxxxxx\piwik\core\Common.php on line 589

[tsteur]

Can you change this line https://github.com/piwik/piwik/blob/2.15.0/core/Common.php#L589 to mt_srand((int)$seed); and see if you get more information?

[oraclerob]

I've made that change and it has fixed the warning message. The problem is still not fixed. I'm checking out if there is a way I can enable more verbose logging so I can find the php module/query that is causing the issue. If you know of an option within piwik or mysql please let me know.

[oraclerob]

I’ve been able to get a stack trace of the errror, posted below:

ERROR: Failed to get data from API: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away - caused by plugin Actions

#0 xxxxxxxxxxxxx\piwik\core\ArchiveProcessor\Loader.php(126): Piwik\ArchiveProcessor\PluginsArchiver->callAggregateAllPlugins('4', '0')

#1 xxxxxxxxxxxxx\piwik\core\ArchiveProcessor\Loader.php(74): Piwik\ArchiveProcessor\Loader->prepareAllPluginsArchive('4', '0')

#2 xxxxxxxxxxxxx\piwik\core\Archive.php(901): Piwik\ArchiveProcessor\Loader->prepareArchive('VisitsSummary')

#3 xxxxxxxxxxxxx\piwik\core\Archive.php(715): Piwik\Archive->prepareArchive(Array, Object(Piwik\Site), Object(Piwik\Period\Day))

#4 xxxxxxxxxxxxx\piwik\core\Archive.php(667): Piwik\Archive->cacheArchiveIdsAfterLaunching(Array, Array)

#5 xxxxxxxxxxxxx\piwik\core\Archive.php(605): Piwik\Archive->getArchiveIds(Array)

#6 xxxxxxxxxxxxx\piwik\core\Archive.php(332): Piwik\Archive->get(Array, 'numeric')

#7 xxxxxxxxxxxxx\piwik\plugins\VisitsSummary\API.php(35): Piwik\Archive->getDataTableFromNumeric(Array)

#8 [internal function]: Piwik\Plugins\VisitsSummary\API->get('1', 'day', '2015-11-23,2015...', false, Array)

#9 xxxxxxxxxxxxx\piwik\core\API\Proxy.php(208): call_user_func_array(Array, Array)

#10 xxxxxxxxxxxxx\piwik\plugins\API\API.php(407): Piwik\API\Proxy->call('\Piwik\Plugins...', 'get', Array)

#11 [internal function]: Piwik\Plugins\API\API->get('1', 'day', '2015-11-23,2015...', false, Array)

#12 xxxxxxxxxxxxx\piwik\core\API\Proxy.php(208): call_user_func_array(Array, Array)

#13 xxxxxxxxxxxxx\piwik\core\Plugin\Visualization.php(259): Piwik\API\Proxy->call('\Piwik\Plugins...', 'get', Array)

#14 xxxxxxxxxxxxx\piwik\core\Plugin\Visualization.php(180): Piwik\Plugin\Visualization->loadDataTableFromAPI()

#15 xxxxxxxxxxxxx\piwik\core\Plugin\ViewDataTable.php(414): Piwik\Plugin\Visualization->buildView()

#16 xxxxxxxxxxxxx\piwik\core\Plugin\Controller.php(248): Piwik\Plugin\ViewDataTable->render()

#17 xxxxxxxxxxxxx\piwik\plugins\VisitsSummary\Controller.php(126): Piwik\Plugin\Controller->renderView(Object(Piwik\Plugins\CoreVisualizations\Visualizations\JqplotGraph\Evolution))

#18 xxxxxxxxxxxxx\piwik\plugins\VisitsSummary\Controller.php(42): Piwik\Plugins\VisitsSummary\Controller->getEvolutionGraph(false, Array, 'getIndexGraph')

#19 [internal function]: Piwik\Plugins\VisitsSummary\Controller->index()

#20 xxxxxxxxxxxxx\piwik\core\FrontController.php(499): call_user_func_array(Array, Array)

#21 xxxxxxxxxxxxx\piwik\core\FrontController.php(99): Piwik\FrontController->doDispatch('VisitsSummary', 'index', Array)

#22 xxxxxxxxxxxxx\piwik\core\dispatch.php(34): Piwik\FrontController->dispatch()

#23 xxxxxxxxxxxxx\piwik\index.php(27): require_once('XXXXXXXXXXXXXX...')

#24 {main}

From: Thomas Steur [mailto:[email protected]]
Sent: Wednesday, 23 December 2015 8:08 AM
To: piwik/piwik [email protected]
Cc: Rob Mascaro [email protected]
Subject: Re: [piwik] MySQL 5.7+ SQLSTATE[HY000]: General error: 2006 MySQL server has gone away - caused by plugin Actions (#9419)

Can you change this line https://github.com/piwik/piwik/blob/2.15.0/core/Common.php#L589 to mt_srand((int)$seed); and see if you get more information?

—
Reply to this email directly or view it on GitHub #9419 (comment) . https://github.com/notifications/beacon/AKqJzUVgZPBLAQuJVtD4oJ_SIW93dAtDks5pSd1YgaJpZM4G49iY.gif

[sirio3mil]

I get the same error, this is my sofware

• CentOS Linux release 7.2.1511 (Core)
• Server version: 5.7.10 MySQL Community Server (GPL)
• nginx version: nginx/1.9.9
• PHP 7.0.1 (cli) (built: Dec 16 2015 15:23:56) ( NTS )
• Piwik 2.16.0 b1

The error is:

Mysqli prepare error: MySQL server has gone away - caused by plugin Actions

Logs are:

Aborted connection XX to db: 'piwik' user: 'xxxxxxxx' host: 'localhost' (Got an error reading communication packets)
Error in Piwik: Mysqli prepare error: MySQL server has gone away - caused by plugin Actions

my.cnf

local-infile = 1
max_allowed_packet = 512M

config.ini.php

host = "127.0.0.1"
release_channel = "latest_beta"

Regards

[sirio3mil]

This is my error trace

ERROR: Failed to get data from API: Mysqli prepare error: MySQL server has gone away - caused by plugin Actions 0 /piwik/core/ArchiveProcessor/Loader.php(126): Piwik\ArchiveProcessor\PluginsArchiver->callAggregateAllPlugins(102, '0') 1 /piwik/core/ArchiveProcessor/Loader.php(74): Piwik\ArchiveProcessor\Loader->prepareAllPluginsArchive(102, '0') 2 /piwik/core/Archive.php(901): Piwik\ArchiveProcessor\Loader->prepareArchive('Actions') 3 /piwik/core/Archive.php(715): Piwik\Archive->prepareArchive(Array, Object(Piwik\Site), Object(Piwik\Period\Day)) 4 /piwik/core/Archive.php(667): Piwik\Archive->cacheArchiveIdsAfterLaunching(Array, Array) 5 /piwik/core/Archive.php(605): Piwik\Archive->getArchiveIds(Array) 6 /piwik/core/Archive.php(377): Piwik\Archive->get(Array, 'blob', NULL) 7 /piwik/core/Archive.php(468): Piwik\Archive->getDataTable('Actions_actions...', NULL) 8 /piwik/core/Archive.php(497): Piwik\Archive::getDataTableFromArchive('Actions_actions...', '1', 'day', 'yesterday', false, false, NULL, false) 9 /piwik/plugins/Actions/API.php(93): Piwik\Archive::createDataTableFromArchive('Actions_actions...', '1', 'day', 'yesterday', false, false, false, false, false) 10 [internal function]: Piwik\Plugins\Actions\API->getPageUrls('1', 'day', 'yesterday', false, false, false, false, false) 11 /piwik/core/API/Proxy.php(208): call_user_func_array(Array, Array) 12 /piwik/core/Plugin/Visualization.php(259): Piwik\API\Proxy->call('\Piwik\Plugins\...', 'getPageUrls', Array) 13 /piwik/core/Plugin/Visualization.php(180): Piwik\Plugin\Visualization->loadDataTableFromAPI() 14 /piwik/core/Plugin/ViewDataTable.php(414): Piwik\Plugin\Visualization->buildView() 15 /piwik/core/Plugin/Report.php(338): Piwik\Plugin\ViewDataTable->render() 16 /piwik/plugins/CoreHome/Controller.php(78): Piwik\Plugin\Report->render() 17 /piwik/plugins/CoreHome/Controller.php(66): Piwik\Plugins\CoreHome\Controller->renderReportWidget(Object(Piwik\Plugins\Actions\Reports\GetPageUrls)) 18 [internal function]: Piwik\Plugins\CoreHome\Controller->renderReportMenu(Object(Piwik\Plugins\Actions\Reports\GetPageUrls)) 19 /piwik/core/FrontController.php(495): call_user_func_array(Array, Array) 20 /piwik/core/FrontController.php(99): Piwik\FrontController->doDispatch('Actions', 'menuGetPageUrls', Array) 21 /piwik/core/dispatch.php(34): Piwik\FrontController->dispatch() 22 /piwik/index.php(27): require_once('/...') 23 {main}

[oraclerob]

Upgrade to 2.16.0-b1 has not fixed the problem. Now there are more widgets failing with the same error including "Returning visitors over time" which were working previously.

[f0086]

Same problem here with the piwik/docker-piwik docker-compose package.

[leesiongchan]

Same problem and I am using https://github.com/piwik/docker-piwik too.

[rajeshvsure]

I am facing the exact same issue. My problem started after I upgraded from MySQL5.6 to 5.7. I am using Piwik version 2.15. The same works fine with MySQL5.6.

[tsteur]

Here are a view common reasons listed: http://dev.mysql.com/doc/refman/5.7/en/gone-away.html Maybe someone who experience this issue can have a look and test some settings or so?

It also mentions how to log some messages by setting the log_error_verbosity. It would be really helpful to get some more details from the MySQL logs to fix this issue. So far too many things could cause this issue.

[tsteur]

Is everyone using Mysqli like @sirio3mil or are there also problems with PDO?

[mattab]

As per the FAQ I have increased the following parameters:
wait_timeout=100000
max_allowed_packet=128M

This is the FAQ: http://piwik.org/faq/troubleshooting/faq_183/

Maybe try to set max_allowed_packet=512M or 256M?

[oraclerob]

Hi everyone... I have resolved the issue - at least for my site. I think the error reporting in the PDO Db section needs to be improved to the console because it is not showing the real errors - just a high level error. The solution ended up being an issue with the character sets and an issue with the file upload scripts.
1.
MySQL 5.7.5+ has now become strict on this setting. By default this is a programdata windows directory but piwik requires the following (depending on your environment):

secure-file-priv="xxxxxxxxxxxxx/piwik/tmp/assets"

If this is not set - the real error is:
General error: 1290 The MySQL server is running with the --secure-file-priv option so it
cannot execute this statement: LOAD DATA INFILE xxxxxxxxxxxx

But of course all we can see is the 2006 gone away error......

MySQL > 5.7+ now installs by default a utf8 character set - it appears that piwik has some problems with this according to the trace I eventually got -(yes the invalid character is really an '"x'?):

---

SQLSTATE[HY000]: General error: 1300 Invalid utf8 character string: '"x':

LOAD DATA LOCAL INFILE 'xxxxxxxxxxxxxxxxxxxxxxxx/piwik/tmp/assets/piwik_archive_blob_2016_01-1d4cee1593e8aec550f11954bf46042
f.csv'
REPLACE
INTO TABLE
piwik_archive_blob_2016_01
FIELDS TERMINATED BY
' '
ENCLOSED BY
'"'
ESCAPED BY ''
LINES TERMINATED BY
'
'
(idarchive,idsite,date1,date2,period,ts_archived,name,value)

---

In Mysql I was able to change the default "schema" piwik database to "latin1" and like magic everything worked again. This leads me to assume that converting a latin1 db over to utf8 might encounter similar issues as I did. I know character sets are a mine field but I wouldn't expect to see issues going from a latin1 to utf8 dataset (as they in theory should be a subset but hey this is still possible) - the other way around yes of course there should be issues most certainly.
1.
Like I said, the database error logging is not fine grained enough - the following file:
xxxx\piwik\libs\Zend\Db\Adapter\Pdo\Abstract.php line 285 in the exception handler needs to properly log the real error to the console or screen with the sql statement to be useful, something like this:
error_log($e->getMessage() . $sql ,0);

Anyway I posted this in case this might help anyone else out there.

[sirio3mil]

Thanks to @oraclerob, changing default database charset to latin1 and default collation to latin1_general_ci fix the error. Regards.

[cimi]

@oraclerob - thank you!

Same for me, changing the DB schema made the problem go away:

mysql> ALTER DATABASE analytics DEFAULT CHARACTER SET = 'latin1' COLLATE 'latin1_general_ci';
Query OK, 1 row affected (0.00 sec)

Since I am using docker-piwik, I also needed to change docker-compose.yml to make use of my modified mysql config:

db:
  image: mysql
  volumes:
    - ./mysql/runtime:/var/lib/mysql
    - ./my.cnf:/etc/mysql/my.cnf
  env_file:
    - ./.env

To get to the mysql console in the container:

➜  docker-piwik git:(master) docker exec -i -t dockerpiwik_db_1 bash

root@accd90ed38fc:/# mysql -u root -p

I had also added the following configurations into my.cnf:

max_allowed_packet = 512M
secure-file-priv = /tmp

[tsteur]

Thx for that! I can reproduce it by executing

ALTER DATABASE piwik3 CHARACTER SET utf8 COLLATE utf8_general_ci;

[mattab]

Hey guys, would you mind please trying the following patch: https://github.com/piwik/piwik/pull/9520/files

we believe it fixes the issue but it would be great to get confirmation from you guys!

[oraclerob]

Hi there,

I’ve had a look at the code for the patch and I believe it is too much of a hack. Changing the language on the fly is not really the best way to address the issue, but it will work most of the time.

If you must write out csv files to upload (which I believe can be improved) you would be better off to convert the data to base64 of some other non language specific encoding to ensure that the loader does not incur runtime issues with special characters due to different language sets.

Also you have not addressed a very specific issue I raised with the –secure-file-priv option in MySQL which has been strengthened in 5.7. Because you are using the loader to upload the files, there are going to be other users who encounter the same issues I did under windows.

[mattab]

Hi @oraclerob - would it be possible to try our patch? There is a possibility that the patch fully fixes the issue. if you still get errors with the patch please let us know the error message, we'll investigate!

[oraclerob]

The patch fixes the UTF8 character set issue.

However it does not fix the file upload issue. The default installation for MySQL on windows installs the following setting in the my.ini file:

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

The default upload directory in piwik appears to be:

[docroot]/piwik/tmp/assets

Loading the files then fails with:

General error: 1290 The MySQL server is running with the --secure-file-priv option so it
cannot execute this statement: LOAD DATA INFILE xxxxxxxxxxxx

Piwik needs to be able to pick up the secure-file-priv setting in MySQL and make sure you load your files from there. This can be done with the following directive:

SHOW VARIABLES LIKE "secure_file_priv";

I think you need to log another bug.

[mattab]

I think you need to log another bug.

Thanks for the report! Bug was filed @ #9528

[oraclerob]

No worries, if you're on stackoverflow please accept and/or upvote my answer!

[mattab]

This issue should be fixed in #9520 and #9527

it will be available in our next beta 2.16.0-b3

[tsteur]

@oraclerob can you have a look at #9529 re secure_file_priv? Maybe you can also have a look at the description of the PR if that makes any sense

[nisdis]

According to the piwik docs
https://piwik.org/faq/troubleshooting/#faq_194

This is what worked for me:

update to the latest PHP version, or use the mysqli client (there are some known bugs with older PDO clients for mysql)
and/or switch the client to adapter=MYSQLI in the config/config.ini.php