What to do when you get 504 Gateway Timeout in phpMyAdmin

You may encounter a 504 timeout error when trying to import SQL from phpMyAdmin.
This article provides solutions for those who want to be able to import from phpMyAdmin in such situations.

Solution 1. File Size Issue

/etc/php.iniEdit the following three items in

1. Maximum uploadable file size:
   upload_max_filesize = 512M
2.  The maximum amount of data that can be POSTed is
   post_max_size = 512M
3. The maximum amount of memory that a script can allocate
   is memory_limit = 512M

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 limit before a script is forcibly terminated by the parser (*).
This setting prevents long scripts from overloading the server.
The unit is seconds; 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 set of data structures that the program can handle.)

References:
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 the connection speed, extend mysql.connect_timeout.

`mysql.connect_timeout` specifies
the connection's validity period (in seconds). On Linux, this validity period 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 resource-intensive, so
if you're deleting a large amount of data, it's apparently faster to use `truncate` to delete everything and then later add only the necessary data back in.