@limitstudios opened this Issue on January 24th 2020 Contributor


I've setup quite a few matomo instances at our hosting company and have noticed that as soon as we upgrade a customers PHP to 7.2 or above, the matomo instance starts showing lots of "MySQL has gone away" errors and performance goes down.

As soon as we downgrade back to 7.1 these errors go away and the system functions and performs fine. With PHP 7.1 being end of life, it would probably be a good idea looking to support the newer versions and ensuring performance is on par or better, and that errors like the above don't appear.

I'm a PHP developer myself so happy to work with you guys and provide a test environment to help debug and fix.

@tsteur commented on January 26th 2020 Member

@limitstudios It be great if you look into this and find something. Have you tested if there is again a difference for PHP 7.3? Are you maybe planning to give it a try?

Maybe PHP 7.2 regressed performance in some particular features. It's possible these performance issues only apply when using specific features/configurations. Some issues might only happen on a specific load etc. While we do support newer PHP versions, it's impossible for us to do advanced performance/load tests for different PHP versions unfortunately.

@limitstudios commented on January 27th 2020 Contributor

Hi @tsteur I would imagine there is, from my testing on a live environment it seems to affect 7.2 and 7.3.

I've almost got my profiling working and I'm able to reproduce the performance difference locally so will let you know what I find.

@Findus23 commented on April 20th 2020 Member

There is a suspiciously large amount of people reporting something similar to this:
After updating from PHP<7.2 to any version >=7.2, they get a lot of MySQL has gone away errors to the point where Matomo is unusable.
My initial guess was that someone had a custom fix in the old PHP config and didn't apply it to their new PHP config after updating, but there are too many reports for this to accidentally happen.

Another common thing I see, is that this happens more often on shared hosters, so we can't get more information about the MySQL setup.

Some examples:

If someone has a clue on how to reproduce this or what could be causing this change, please comment.

@limitstudios commented on April 20th 2020 Contributor

I can provide a shared hosting environment where this is reproducible if it helps?

@Findus23 commented on April 20th 2020 Member

@limitstudios Is your shared hoster by chance in Germany? Maybe it is even the same shared hoster for everyone reporting the issue?

@limitstudios commented on April 20th 2020 Contributor

Nope UK based it could be they all have the same CPanel setup and setting the website version from <7.2 to >=7.2 makes something trigger unexpectedly

@Findus23 commented on April 24th 2020 Member
@tsteur commented on April 26th 2020 Member

Interesting. If this indeed improves it, might be worth switching to nd_mysqli or nd_pdo_mysql fyi @mattab

@limitstudios commented on April 26th 2020 Contributor

This does seem somewhat connected, the options in that forum thread didn't fix it for me; however these steps did

  • PHP Extensions Enabled
    • mysqli
    • mysqlnd
    • pdo_mysql

Now when I was getting a lot of the "MySQL has gone away errors" my adapter in config.ini.php was "MYSQLI" when I removed this line, which I think defaults to "PDO_MYSQL" the errors went away under 7.2 and 7.3 (currently on 7.3.17)

And I can access the system config status page and the widgets on the dashboard without any "MySQL has gone away" errors, I'll continue to monitor the beginning of this week when the data collection starts to ramp up again.

@louiswolf commented on May 8th 2020

For me the nd_mysqli solution seems to work for PHP 7.4.5

This might be a hint in the right direction:

I've been fooling around with PHP and PHP-CLI versions to solve an old archiving issue (https://forum.matomo.org/t/mysql-server-has-gone-away/31760/8) that had slipped my mind, but returned after upgrading PHP on several domains.

The issue seems to appear exclusively when there are archiving tasks, breaking both core:archive (mysql server has gone away) and the web interface (all websites).

I also noticed 2 ways in which the archive task (triggered by cron) fails:

  • sometimes it will just hang consuming a bit of memory, which after a few crons will consume a lot of memory.
  • In other cases the cronjob just finishes with errors.

The above seems dependent on which combination of PHP versions I set. I'm using cpanel, where I can set PHP for both domains and CLI. However, I always get one or more Error: Got invalid response from API request: caused by a Mysqli prepare error: MySQL server has goneaway.

Not sure of the above is helpful, but let me know if I can provide you with more information.

@Findus23 commented on May 9th 2020 Member

One more user reported back that switching from MySQLi to PDO fixed the issue:

@limitstudios commented on May 9th 2020 Contributor

@Findus23 I wonder if PDO has some sort of connection cleanup/closing where as the MySQLi method doesn't do this in some cases?

@ThibaultVlacich commented on August 7th 2020

I have this issue, and I can't even install Matomo. I get the error "Error while trying to connect to the database server: SQLSTATE[HY000] [2006] MySQL server has gone away." during the step "3. Database Setup" of the install.

Following this article changed nothing.

EDIT: I am using the official docker image (the Apache variant), with a MySQL 5 server. I tried MariaDB, MySQL 8 with no success.

@mattab commented on August 8th 2020 Member

@ThibaultVlacich which php version do you use, can you try to upgrade to PHP 7.4 latest, this might help?

@ThibaultVlacich commented on August 8th 2020

@mattab I am using the official Docker image, so the version included with it (meaning php 7.4). Just using the official Docker image with no change just doesn't work. So even if the official Docker image doesn't work out-of-the-box, there's a serious issue here.

EDIT: just pull the example available in the repo (here), run docker-compose up, try to setup Matomo, it will fail at step 3.
EDIT 2: I just tried the example on Windows, and it worked without any problem? When I try to run the exact same Docker stack on an Ubuntu 20.04 machine, it doesn't work, and on Windows 10 it does? Really weird.
EDIT 3: And tested on another Ubuntu 20.04 host, and it's working perfectly. The only major diff between the machines that work, and the one that don't, is that the one that don't is the only one that don't have an SSD. Could that be the reason?

@tsteur commented on August 9th 2020 Member

It might help to use eg php nd_mysqli or nd_pdo_mysql https://github.com/matomo-org/matomo/issues/15451#issuecomment-625729287 . Any chance to try that in the one without SSD?

@dejwsz commented on October 28th 2020

In my case, this error disappeared after I changed the adapter from MYSQLI to PDO\MYSQL. I am using Matomo 3.14.1 with PHP 7.4.11 and MySQL 8.0.20-11.3. I tried any other things like this: https://matomo.org/faq/troubleshooting/faq_183/ and nothing helped. I had this error very often during cron archive. Right after I changed the type of the adapter the error isn't there anymore.

@rosa2 commented on February 9th 2022

Hello and a pleasure to be able to give a little in this great community

I was having this problem also.
I followed this guide (one of the first when you search for Debian 10 and Matomo)
There the user is created in mysql

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Then when going to create the database tables, automatically i will get the ip and the error :scream_cat:
Changing the ip by localhost was the solution :)
mysql Ver 15.1 Distrib 10.3.31-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
I hope it helps someone.

Thanks for this great FLOSS software!

Powered by GitHub Issue Mirror