Ошибка при обработке запроса код ошибки 504 phpmyadmin

I have an immensely annoying problem with my phpmyadmin. It appeared out of the blue. No changes that I am aware of have been been to my dedicated server.

When i try to execute any query or show a view that takes more than 1 second to execute in my browser (firefox on windows 7, ie behaves the same), i get the following error:

Error in Processing Request 
Error code: 504 
Error text: Gateway Timeout

the queries can or don’t need to have count(*). they work on tables with 5m — 50m rows.

sometimes the same error shows up when i click any link in phpmyadmin, such as «Browse», «Server» or «Structure».

php scripts cronned to run much more intensive queries on the same dedicated server work flawlessly.

Here are some technical details:

Database server
•Server: Localhost via UNIX socket
•Server type: MySQL
•Server version: 5.5.38-0ubuntu0.14.04.1-log - (Ubuntu)
•Protocol version: 10
•User: XXX@localhost
• Server charset:  UTF-8 Unicode (utf8)  

Web server
•Apache/2.4.7 (Ubuntu)
•Database client version: libmysql - 5.5.38
•PHP extension: mysqli Documentation

phpMyAdmin
•Version information: 4.2.3deb1.trusty~ppa.1

I followed some clues online and I tried adding the last two line in my conf.inc.php file (see below), but no changes, also after restarting the server:

<?php
/**
 * Debian local configuration file
 *
 * This file overrides the settings made by phpMyAdmin interactive setup
 * utility.
 *
 * For example configuration see
 *   /usr/share/doc/phpmyadmin/examples/config.sample.inc.php
 * or
 *   /usr/share/doc/phpmyadmin/examples/config.manyhosts.inc.php
 *
 * NOTE: do not add security sensitive data to this file (like passwords)
 * unless you really know what you're doing. If you do, any user that can
 * run PHP or CGI on your webserver will be able to read them. If you still
 * want to do this, make sure to properly secure the access to this file
 * (also on the filesystem level).
 */

// Load secret generated on postinst
include('/var/lib/phpmyadmin/blowfish_secret.inc.php');

// Load autoconf local config
include('/var/lib/phpmyadmin/config.inc.php');

/**
 * Server(s) configuration
 */
$i = 0;
// The $cfg['Servers'] array starts with $cfg['Servers'][1].  Do not use $cfg['Servers'][0].
// You can disable a server config entry by setting host to ''.
$i++;

/**
 * Read configuration from dbconfig-common
 * You can regenerate it using: dpkg-reconfigure -plow phpmyadmin
 */
if (is_readable('/etc/phpmyadmin/config-db.php')) {
    require('/etc/phpmyadmin/config-db.php');
} else {
    error_log('phpmyadmin: Failed to load /etc/phpmyadmin/config-db.php.'
        . ' Check group www-data has read access.');
}

/* Configure according to dbconfig-common if enabled */
if (!empty($dbname)) {
    /* Authentication type */
    $cfg['Servers'][$i]['auth_type'] = 'cookie';
    /* Server parameters */
    if (empty($dbserver)) $dbserver = 'localhost';
    $cfg['Servers'][$i]['host'] = $dbserver;

    if (!empty($dbport) || $dbserver != 'localhost') {
        $cfg['Servers'][$i]['connect_type'] = 'tcp';
        $cfg['Servers'][$i]['port'] = $dbport;
    }
    //$cfg['Servers'][$i]['compress'] = false;
    /* Select mysqli if your server has it */
    $cfg['Servers'][$i]['extension'] = 'mysqli';
    /* Optional: User for advanced features */
    $cfg['Servers'][$i]['controluser'] = $dbuser;
    $cfg['Servers'][$i]['controlpass'] = $dbpass;
    /* Optional: Advanced phpMyAdmin features */
    $cfg['Servers'][$i]['pmadb'] = $dbname;
    $cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
    $cfg['Servers'][$i]['relation'] = 'pma_relation';
    $cfg['Servers'][$i]['table_info'] = 'pma_table_info';
    $cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
    $cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
    $cfg['Servers'][$i]['column_info'] = 'pma_column_info';
    $cfg['Servers'][$i]['history'] = 'pma_history';
    $cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';
    $cfg['Servers'][$i]['tracking'] = 'pma_tracking';
    $cfg['Servers'][$i]['userconfig'] = 'pma_userconfig';

    /* Uncomment the following to enable logging in to passwordless accounts,
     * after taking note of the associated security risks. */
    // $cfg['Servers'][$i]['AllowNoPassword'] = TRUE;

    /* Advance to next server for rest of config */
    $i++;
}

/* Authentication type */
//$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
//$cfg['Servers'][$i]['host'] = 'localhost';
//$cfg['Servers'][$i]['connect_type'] = 'tcp';
//$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
//$cfg['Servers'][$i]['extension'] = 'mysql';
/* Optional: User for advanced features */
 //$cfg['Servers'][$i]['controluser'] = 'pma';
 //$cfg['Servers'][$i]['controlpass'] = 'pmapass';
/* Optional: Advanced phpMyAdmin features */
// $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
// $cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
// $cfg['Servers'][$i]['relation'] = 'pma_relation';
// $cfg['Servers'][$i]['table_info'] = 'pma_table_info';
// $cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
// $cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
// $cfg['Servers'][$i]['column_info'] = 'pma_column_info';
// $cfg['Servers'][$i]['history'] = 'pma_history';
// $cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';
/* Uncomment the following to enable logging in to passwordless accounts,
 * after taking note of the associated security risks. */
// $cfg['Servers'][$i]['AllowNoPassword'] = TRUE;

/*
 * End of servers configuration
 */

/*
 * Directories for saving/loading files from server
 */
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';

//$cfg['AllowArbitraryServer'] = true;
$cfg['LoginCookieValidity'] = 86400;
$cfg['MaxExactCount'] = 0;
$cfg['MaxExactCountViews'] = 0;

Please help me.

Solution 1

In the end, I figured out this is not an issue on my server, but my then current internet connection provider simply was cancelling (sending this error message) all https requests that take more than 10 seconds to complete.

Solution 2

If you using php fcgi with Nginx,add this to

                fastcgi_intercept_errors on;
                fastcgi_buffers 8 16k;
                fastcgi_buffer_size 32k;
                fastcgi_connect_timeout 900;//in 900 timeout
                fastcgi_send_timeout 900; //in 900secend
                fastcgi_read_timeout 900; //in 900secend

Example file in /etc/nginx/sites-enabled/phpmyadmin.conf

    location ~ ^/phpmyadmin/(.+\.php)$ {
        alias /usr/share/phpmyadmin/$1;

            fastcgi_pass unix:/run/php/php7.3-fpm.sock;  //check what version php you have in /etc/php/

            fastcgi_index  index.php;
            fastcgi_param  SCRIPT_FILENAME  $request_filename;

            # From fastcgi_params
            fastcgi_param  QUERY_STRING       $query_string;
            fastcgi_param  REQUEST_METHOD     $request_method;
            fastcgi_param  CONTENT_TYPE       $content_type;
            fastcgi_param  CONTENT_LENGTH     $content_length;
                fastcgi_param  SCRIPT_NAME        $fastcgi_script_name;
                fastcgi_param  REQUEST_URI        $request_uri;
                fastcgi_param  DOCUMENT_URI       $document_uri;
                fastcgi_param  DOCUMENT_ROOT      /usr/share/phpmyadmin; # <-- Changed
                fastcgi_param  SERVER_PROTOCOL    $server_protocol;
                fastcgi_param  GATEWAY_INTERFACE  CGI/1.1;
                fastcgi_param  SERVER_SOFTWARE    nginx/$nginx_version;
                fastcgi_param  REMOTE_ADDR        $remote_addr;
                fastcgi_param  REMOTE_PORT        $remote_port;
                fastcgi_param  SERVER_ADDR        $server_addr;
                fastcgi_param  SERVER_PORT        $server_port;
                fastcgi_param  SERVER_NAME        $server_name;
                fastcgi_param  REDIRECT_STATUS    200;

                fastcgi_intercept_errors on;
                fastcgi_buffers 8 16k;
                fastcgi_buffer_size 32k;
                fastcgi_connect_timeout 900;
                fastcgi_send_timeout 900;
                fastcgi_read_timeout 900;
    }

Solution 3

I added the following line to the file /etc/nginx/nginx.conf in the http{} block:

• fastcgi_read_timeout 360;

Restart nginx

• sudo service nginx restart

Good luck!!! Remember to like me. Thanks you so much!

Related videos on Youtube

The error message you are seeing could be caused by a handful of different things. One possibility might be that the tables involved are very large, and the query is just taking too long. The reason why large tables could be a performance killer is that you have several correlated subqueries in your select clause.

One workaround is to replace the correlated subqueries with appropriate joins to non correlated subqueries:

SELECT
    i.ITEM_NO AS SKU,
    i.DESCR AS Description,
    COALESCE(t1.qty_sold_ytd, 0) AS qty_sold_ytd,
    COALESCE(t1.qty_sold_last_month, 0) AS qty_sold_last_month,
    COALESCE(t2.qty_on_hand, 0) AS qty_on_hand
FROM ps_tkt_hist_lin i
LEFT JOIN
(
    SELECT
        ITEM_NO,
        SUM(CASE WHEN BUS_DAT >= MAKEDATE(YEAR(CURDATE()), 1)
                 THEN QTY_SOLD ELSE 0 END) AS qty_sold_ytd,
        SUM(CASE WHEN BUS_DAT >=
                      DATE_SUB(DATE_SUB(CURDATE(), INTERVAL (DAY(CURDATE())-1) DAY),
                               INTERVAL 1 MONTH)
                 THEN QTY_SOLD ELSE 0 END) AS qty_sold_last_month
    FROM ps_tkt_hist_lin
    GROUP BY ITEM_NO
) t1
    ON i.ITEM_NO = t1.ITEM_NO
LEFT JOIN
(
    SELECT ITEM_NO, SUM(QTY_ON_HND) AS qty_on_hand
    FROM IM_INV
    WHERE LOC_ID IN ('SL','S','LO','AF')
    GROUP BY ITEM_NO
) t2
    ON i.ITEM_NO = t2.ITEM_NO
WHERE
    i.ITEM_VEND_NO = 'MD'
ORDER BY
    i.ITEM_NO;

I tried to keep true to the logic of your original query, which did not make complete sense. For instance, it is not clear why you are aggregating in both the subqueries and outer query. My suggestion would give you every row from the ps_tkt_hist_lin table.

У меня очень раздражающая проблема с моим phpmyadmin. Это появилось на ровном месте. На моем выделенном сервере не было никаких изменений, о которых мне известно.

Когда я пытаюсь выполнить любой запрос или показать представление, выполнение которого в моем браузере занимает более 1 секунды (Firefox в Windows 7, т.е. ведет себя так же), я получаю следующую ошибку:

Error in Processing Request 
Error code: 504 
Error text: Gateway Timeout

запросы могут или не должны иметь количество (*). они работают на столах с 5 — 50 м строк.

иногда та же самая ошибка появляется, когда я нажимаю любую ссылку в phpmyadmin, такую ​​как «Обзор», «Сервер» или «Структура».

Сценарии php, созданные для выполнения гораздо более интенсивных запросов на одном и том же выделенном сервере, работают безупречно.

Вот некоторые технические детали:

Database server
•Server: Localhost via UNIX socket
•Server type: MySQL
•Server version: 5.5.38-0ubuntu0.14.04.1-log - (Ubuntu)
•Protocol version: 10
•User: XXX@localhost
• Server charset:  UTF-8 Unicode (utf8)  

Web server
•Apache/2.4.7 (Ubuntu)
•Database client version: libmysql - 5.5.38
•PHP extension: mysqli Documentation

phpMyAdmin
•Version information: 4.2.3deb1.trusty~ppa.1

Я следил за некоторыми подсказками онлайн и попытался добавить две последние строки в мой файл conf.inc.php (см. Ниже), но без изменений, даже после перезапуска сервера:

<?php
/**
 * Debian local configuration file
 *
 * This file overrides the settings made by phpMyAdmin interactive setup
 * utility.
 *
 * For example configuration see
 *   /usr/share/doc/phpmyadmin/examples/config.sample.inc.php
 * or
 *   /usr/share/doc/phpmyadmin/examples/config.manyhosts.inc.php
 *
 * NOTE: do not add security sensitive data to this file (like passwords)
 * unless you really know what you're doing. If you do, any user that can
 * run PHP or CGI on your webserver will be able to read them. If you still
 * want to do this, make sure to properly secure the access to this file
 * (also on the filesystem level).
 */

// Load secret generated on postinst
include('/var/lib/phpmyadmin/blowfish_secret.inc.php');

// Load autoconf local config
include('/var/lib/phpmyadmin/config.inc.php');

/**
 * Server(s) configuration
 */
$i = 0;
// The $cfg['Servers'] array starts with $cfg['Servers'][1].  Do not use $cfg['Servers'][0].
// You can disable a server config entry by setting host to ''.
$i++;

/**
 * Read configuration from dbconfig-common
 * You can regenerate it using: dpkg-reconfigure -plow phpmyadmin
 */
if (is_readable('/etc/phpmyadmin/config-db.php')) {
    require('/etc/phpmyadmin/config-db.php');
} else {
    error_log('phpmyadmin: Failed to load /etc/phpmyadmin/config-db.php.'
        . ' Check group www-data has read access.');
}

/* Configure according to dbconfig-common if enabled */
if (!empty($dbname)) {
    /* Authentication type */
    $cfg['Servers'][$i]['auth_type'] = 'cookie';
    /* Server parameters */
    if (empty($dbserver)) $dbserver = 'localhost';
    $cfg['Servers'][$i]['host'] = $dbserver;

    if (!empty($dbport) || $dbserver != 'localhost') {
        $cfg['Servers'][$i]['connect_type'] = 'tcp';
        $cfg['Servers'][$i]['port'] = $dbport;
    }
    //$cfg['Servers'][$i]['compress'] = false;
    /* Select mysqli if your server has it */
    $cfg['Servers'][$i]['extension'] = 'mysqli';
    /* Optional: User for advanced features */
    $cfg['Servers'][$i]['controluser'] = $dbuser;
    $cfg['Servers'][$i]['controlpass'] = $dbpass;
    /* Optional: Advanced phpMyAdmin features */
    $cfg['Servers'][$i]['pmadb'] = $dbname;
    $cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
    $cfg['Servers'][$i]['relation'] = 'pma_relation';
    $cfg['Servers'][$i]['table_info'] = 'pma_table_info';
    $cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
    $cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
    $cfg['Servers'][$i]['column_info'] = 'pma_column_info';
    $cfg['Servers'][$i]['history'] = 'pma_history';
    $cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';
    $cfg['Servers'][$i]['tracking'] = 'pma_tracking';
    $cfg['Servers'][$i]['userconfig'] = 'pma_userconfig';

    /* Uncomment the following to enable logging in to passwordless accounts,
     * after taking note of the associated security risks. */
    // $cfg['Servers'][$i]['AllowNoPassword'] = TRUE;

    /* Advance to next server for rest of config */
    $i++;
}

/* Authentication type */
//$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
//$cfg['Servers'][$i]['host'] = 'localhost';
//$cfg['Servers'][$i]['connect_type'] = 'tcp';
//$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
//$cfg['Servers'][$i]['extension'] = 'mysql';
/* Optional: User for advanced features */
 //$cfg['Servers'][$i]['controluser'] = 'pma';
 //$cfg['Servers'][$i]['controlpass'] = 'pmapass';
/* Optional: Advanced phpMyAdmin features */
// $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
// $cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
// $cfg['Servers'][$i]['relation'] = 'pma_relation';
// $cfg['Servers'][$i]['table_info'] = 'pma_table_info';
// $cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
// $cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
// $cfg['Servers'][$i]['column_info'] = 'pma_column_info';
// $cfg['Servers'][$i]['history'] = 'pma_history';
// $cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';
/* Uncomment the following to enable logging in to passwordless accounts,
 * after taking note of the associated security risks. */
// $cfg['Servers'][$i]['AllowNoPassword'] = TRUE;

/*
 * End of servers configuration
 */

/*
 * Directories for saving/loading files from server
 */
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';

//$cfg['AllowArbitraryServer'] = true;
$cfg['LoginCookieValidity'] = 86400;
$cfg['MaxExactCount'] = 0;
$cfg['MaxExactCountViews'] = 0;

Пожалуйста, помогите мне.