1

I know this question has been asked many times but I have tried all of the solutions I've come across and none have worked. I hope someone might be able to shed some light.

I have set the following INI settings to very large values:

  • mysql.connect_timeout
  • default_socket_timeout
  • max_execution_time

I have also called set_time_limit(0) at the beginning of my script. What other PHP INI settings should I try?

I am very sure the settings on the MySQL server are adequate for very large queries because another server with PHP is able to run the script I'm trying to run without a problem. It waits forever for MySQL to finish and then continues. That's exactly what I want in this php:5-apache container of mine.

On the server that works, mysql.connect_timout is only set to 60, but the script maintains a connection to the mysql server for much longer, leading me to believe some other setting is at issue. default_socket_timeout is also set to 60 on this server. What???

I was really hoping it was default_socket_timeout, but no matter what number I set, it does not change how the "Mysql server has gone away" error appears in about 60 seconds.

I also tried using mysql_ping. That didn't make any difference either.

The script is using the old mysql functions. I do want to upgrade this, but first I want to know why it works on the other server and not mine.

Also, I can see that the mysql server is still processing the giant query when I do SHOW PROCESSLIST.

What the heck am I missing? I really think it is an INI setting that this other server has that my container does not, but I am copying all the settings that appear relevant yet. Why can another server with PHP run the script and properly wait however long it needs for mysql yet I can't seem to make that happen in the docker container?

Here are the working server's mysql-related and time-related INI settings where the script works. I have set these in my docker container and I always get that timeout error in the same amount of time:

mysql.allow_local_infile => On => On
mysql.allow_persistent => On => On
mysql.connect_timeout => 60 => 60
mysql.default_host => no value => no value
mysql.default_password => no value => no value
mysql.default_port => no value => no value
mysql.default_socket => /var/lib/mysql/mysql.sock => /var/lib/mysql/mysql.sock
mysql.default_user => no value => no value
mysql.max_links => Unlimited => Unlimited
mysql.max_persistent => Unlimited => Unlimited
mysql.trace_mode => Off => Off
max_execution_time => 0 => 0
max_input_time => -1 => -1
default_socket_timeout => 60 => 60

Also, I am only selecting data. No inserts or updates. Another crucial point to consider is that I am able to run the queries with any MySQL client and get results, leading me to believe that the issue is how PHP is setup in the php:apache container.

Additionally, I just ran the same code from PHP running directly on my Windows host and I got back results. And so now I know that it's the Docker container....

Spencer Williams
  • 821
  • 10
  • 20
  • "The script is using the old mysql functions. I do want to upgrade this, but first I want to know why it works on the other server and not mine." no switch to PDO, its a lot easier to debug. –  Mar 02 '18 at 22:46
  • Check the logfiles. Is PHP using too much RAM? Is PHP in safe mode and you can't change some INI settings? Has some activist DB administrator written a kill_runaways procedure? ... did I mention looking into some logfiles? :-) – Kevin_Kinsey Mar 02 '18 at 22:51
  • I am going to go ahead and rewrite this script using the PDO methods and I hope that helps reveal what the problem is. I am very sure it is respecting the INI settings I change because calling `ini_get` confirms the new values and if I set `default_socket_timeout` to 0, it immediately gives me a timeout error. The PHP error log appears to only have the same warnings as it shows when I run the script. – Spencer Williams Mar 02 '18 at 22:57
  • It may not be because of a php.ini setting at all. Have you seen this?https://stackoverflow.com/questions/10474922/error-2006-hy000-mysql-server-has-gone-away?rq=1 – Don't Panic Mar 02 '18 at 23:35
  • `max_allowed_packet` is set to `268435456` on the mysql server. I strongly doubt that is the issue because of how the other PHP server is able to run the script and wait a long time for mysql to finish, yet it always times out after about a minute in my docker container. – Spencer Williams Mar 02 '18 at 23:48
  • I'm using PDO in this script now, but I still get the "MySQL server has gone away" error. I was provisioned a dev server with a similar setup as our production. The query successfully runs on the dev server as well, but the people who set it up can only thing of max_execution_time and mysql.connect_timeout as the only settings that would effect this Despite setting the PHP settings in my Docker container to either match the dev/prod setting or to something higher, the connection always times out in the same amount of time. – Spencer Williams Mar 13 '18 at 16:18

2 Answers2

0

I spent the last two week looking into a similar issue. In our case we used Python instead of PHP. I looked into every possible MySQL setting (which were all just very fine as your settings), all Django setting, still couldn't reproduce the issue in my testbed.

As soon as the application ran inside a Docker container, larger queries causes "MySQL server has gone away" errors.

After wasting weeks, I figured out that this only occurs when the container is run with bridged networking. As soon as I switched to host networking, the issue went away.

I couldn't find any reference to this behaviour on the net.

Joachim de Groot
  • 101
  • 1
  • 6
  • 1
    I'm sad to report that running the container with `--network=host` does not appear to make a difference with the MySQL timeout. It still says that the server has gone away in the same amount of time. – Spencer Williams Mar 05 '18 at 16:36
0

To my great relief, the PHP in Docker no longer ends the MySQL connection early. I think an update to Docker may have helped address this? I recently updated to 18.03.0-ce-win59 (16762) and I've not been having the problem since...

Spencer Williams
  • 821
  • 10
  • 20