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.
@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.
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.
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.
If someone has a clue on how to reproduce this or what could be causing this change, please comment.
I can provide a shared hosting environment where this is reproducible if it helps?
@limitstudios Is your shared hoster by chance in Germany? Maybe it is even the same shared hoster for everyone reporting the issue?
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
This might be a hint in the right direction:
Interesting. If this indeed improves it, might be worth switching to nd_mysqli or nd_pdo_mysql fyi @mattab
This does seem somewhat connected, the options in that forum thread didn't fix it for me; however these steps did
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.
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:
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.
One more user reported back that switching from MySQLi to PDO fixed the issue:
@Findus23 I wonder if PDO has some sort of connection cleanup/closing where as the MySQLi method doesn't do this in some cases?
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]  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.
@ThibaultVlacich which php version do you use, can you try to upgrade to PHP 7.4 latest, this might help?
@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?
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?
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.
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 127.0.0.1 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!