What to do when 504 Gateway Time-out occurs in phpMyAdmin

When trying to import SQL from phpMyAdmin, you may receive a 504 timeout error.
This time, we will introduce a workaround for those who want to somehow be able to import from phpMyAdmin in such a case.

Solution 1. File size problem

Edit the following 3 items in /etc/php.ini

1. Maximum file size that can be uploaded
   upload_max_filesize = 512M
2. Maximum value of data that can be POST
   post_max_size = 512M
3. Maximum memory that the script can allocate
   memory_limit = 512M

At this time, please note that the above values ​​have the following relationship.
upload_max_filesize ≦ post_max_size ≦ memory_limit

Please note that the way to reflect the settings in php.ini varies depending on the web middleware!

• Apache: Reflects after restarting
• nginx: Reloading php-fpm settings will take effect

$ /etc/init.d/php-fpm reload

How to check if it is reflected

If the following is written in the file selection for phpMyAdmin import, it is OK!

(Maximum: 512MiB)

Solution 2. Time is a problem.

This is a workaround to extend the time until timeout.

There is an item in php.ini called max_execution_time. Let's change this value.

max_execution_time sets the time until the script is forcibly terminated by the parser (*).
This setting is to prevent long scripts from putting a load on the server.
The unit is seconds, and setting it to 0 means there is no time limit.
(*Parser: A program that analyzes the syntax of a script and converts it into a collection of data structures that can be handled by the program)

Reference:
http://php.net/manual/ja/info.configuration.php#ini.max-execution-time
http://e-words.jp/w/%E3%83%91%E3%83% BC%E3%82%B5.html

Setting example

*I think it would be better to set not only max_execution_time but also other settings.

• php.ini
  max_execution_time = 300
• php-fpm.conf
  request_terminate_timeout 300
• nginx.conf
  fastcgi_read_timeout 300

request_terminate_timeout is the timeout setting for processing a single request. After this time, the worker process will be killed. This option is used if the 'max_execution_time' ini option fails to stop the script from running for some reason. A value of '0' means 'Off'. Available units: s (seconds) (default), m (minutes), h (hours) or d (days), default value: 0

Reference:
http://php.net/manual/ja/install.fpm.configuration.php#request-terminate-timeout

fastcgi_read_timeout sets the FastCGI response read timeout. It determines how long to wait to get a response for a request to nginx. 60 (seconds) is the default value.

Reference:
http://server-setting.info/centos/nginx-fastcgi-cache.html

Reflection of settings

For Apache, restart to make the settings take effect.

$ service httpd restart

In the case of nginx, settings are reflected using the reload settings command.

$ service php-fpm reload $ service nginx reload

By the way

If you encounter the following error after making these settings, please review your phpMyAdmin settings.

Maximum execution time of 300 seconds exceeded in ...

Change the following values ​​in the phpMyAdmin configuration file (/path/to/phpmyadmin/libraries/config.default.php).

/** * maximum execution time in seconds (0 for no limit) * * @global integer $cfg['ExecTimeLimit'] */ $cfg['ExecTimeLimit'] = 300;

The unit is seconds, and 0 means unlimited.

Also, max_execution_time is only the processing time of the php script, and does not include the waiting time until a response is returned when accessing the DB.
If the timeout is due to line speed, increase mysql.connect_timeout.

mysql.connect_timeout
Connection validity time (unit: seconds). On Linux, this validity time is also used as the time to wait for the first response from the server.

Extra

This is possible by changing the way the data is changed without changing the settings.

The process of deleting data seems to be heavy, so
if you want to delete a large amount of data, it seems to be faster to delete it all with truncate and then re-insert only the necessary data later.