What to do when you get 504 Gateway Timeout in phpMyAdmin

When trying to import SQL from phpMyAdmin, you may encounter a 504 timeout error.
This article explains how to fix this problem so that you can import from phpMyAdmin.

Solution 1. File Size Issue

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

1. Maximum upload file size:
   upload_max_filesize = 512M
2. The maximum size of data that can be POSTed is
   post_max_size = 512M
3. The maximum memory that a 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 php.ini settings are reflected varies depending on the web middleware!

• Apache: Restart to reflect
• nginx: Reload the php-fpm settings to reflect the changes

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

How to check if it is reflected

If the following is displayed when selecting the file to import in phpMyAdmin, then it's OK!

(Maximum: 512MiB)

Solution 2: Time

This is a workaround to extend the timeout period

There is an item called max_execution_time in php.ini. Change this value

max_execution_time sets the time until the parser (*) terminates the script.
This setting is intended to prevent lengthy scripts from placing a burden 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 a 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

Configuration Example

*It is recommended to set other settings in addition to max_execution_time

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

request_terminate_timeout sets the timeout for processing a single request, after which the worker process will be killed. This option is used if the 'max_execution_time' ini option fails to stop the script execution for some reason. Value '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 to a request to nginx. 60 (seconds) is the default value

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

Reflecting settings

If you are using Apache, restart it to reflect the changes

$ service httpd restart

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

$ service php-fpm reload $ service nginx reload

By the way

If you get the following error after making this setting, please review your phpMyAdmin settings

Maximum execution time of 300 seconds exceeded in ...

Change the following value 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, with 0 being unlimited

Also, max_execution_time only includes the processing time of the PHP script, and does not include the waiting time for a response when accessing the database.
If the timeout is due to line speed, extend mysql.connect_timeout.

mysql.connect_timeoutConnection
expiration time (unit: seconds). On Linux, this expiration time is also used as the waiting time for the first response from the server.

Extra

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

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