«Сервер MySQL исчез» при повторении небуферизованного запроса с PHP 7.4 PDO в локальной базе данных MySQL 5.7 InnoDB

Question

Мой скрипт PHP 7.4 выбирает большую таблицу InnoDB из локальной базы данных MySQL 5.7 и выполняет итерацию каждой строки с использованием PDO. Я использую небуферизованные запросы по назначению, так как вся таблица не помещается в память сервера.

запрос простойSELECT * FROM table WHERE field_a = ... ORDER BY field_b, field_c.

Код, повторяющий результат:

      $rows = $db->prepare('redacted');
$rows->bindParam(':redacted', $redacted);
$rows->execute();

foreach($rows as $r) {
    if($r->fieldname != redacted)
        doWork1();
    
    doWork2();
}

doWork2()и он всегда выполняет следующую работу, используя отдельное буферизованное соединение PDO:

1. обновляет строку в БД;
2. в транзакции вставьте несколько новых строк в другую таблицу (один запрос INSERT на строку).

Каждая итерация занимает не более 1-2 секунд, но весь цикл может занять несколько часов.

Скрипт работает нормально, но ровно каждые 12:00 минут PDO выдает следующее исключение:

      [03-Feb-2023 08:34:29 Europe/Rome] PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in redacted:176
Stack trace:
redacted
[03-Feb-2023 08:46:50 Europe/Rome] PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in redacted:176
Stack trace:
redacted

Номер строки всегда является foreach или if.

mysqld.log говорит:

      2023-02-03T08:36:33.480661+01:00 781575 [Note] Aborted connection 781575 to db: 'redacted' user: 'redacted' host: 'localhost' (Got timeout writing communication packets)
2023-02-03T08:48:56.534865+01:00 781768 [Note] Aborted connection 781768 to db: 'redacted' user: 'redacted' host: 'localhost' (Got timeout writing communication packets)

После перезапуска сценария он отлично работает с теми же данными, поэтому проблема не в значениях или типах данных.mysqladmin versionпоказывает, что сервер никогда не падал.

Я предполагаю, что это из-за своего рода тайм-аута.

Я заметил, что отметка времени в журнале mysql примерно через 2 минуты после фатальной ошибки PHP, поэтому отключение из-за тайм-аута записи является следствием, а не причиной. По этой причине увеличениеnet_read_timeoutиnet_write_timeout(120 секунд),wait_timeout(8 часов),max_allowed_packet(256 МБ) не помогло. Я внимательно просмотрел все переменные сервера, и у меня не было значения около 12 минут или 720 секунд.

Запрос быстрый (менее 3 секунд) и оптимизированный; нет больших полей; он использует индексы; запрос EXPLAIN (соответствующая часть):594802 | 5.67 | Using index condition; Using where; Using filesort. Меня нет в случаях, упомянутых на https://dev.mysql.com/doc/refman/8.0/en/gone-away.html .

Машина представляет собой AWS Lightsail с Linux и cPanel.

      #  mysqladmin version
mysqladmin  Ver 8.42 Distrib 5.7.40, for Linux on x86_64
redacted
Uptime:                 10 days 10 hours 34 min 13 sec
redacted
Threads: 6  Questions: 15310531  Slow queries: 0  Opens: 9989  Flush tables: 1  Open tables: 1854  Queries per second avg: 16.972

Я включил журнал запросов и тщательно проверил время ошибки, и все запросы верны.

Answer 1

Я был удивлен, когда мне удалось так легко воспроизвести это. Я создал три отдельных соединения PDO с моим сервером БД -

1. для небуферизованного запроса
2. запускать регулярные вызовы для проверки состояния потока, обрабатывающего небуферизованный запрос
3. запускать обновления на основе строк, извлекаемых из небуферизованного запроса

Без внесения каких-либо изменений в конфигурацию поток, обрабатывающий небуферизованный запрос, закрывался сервером через 16 минут. Мне не удалось найти в переменных сеанса ничего, что указывало бы на тайм-аут в 16 минут.

Решение в моем случае состояло в том, чтобы увеличить время ожидания net_write_timeout, достаточное для получения всего небуферизованного результата, строка за строкой. Я увеличил его до 30 минут, 60 минут, а затем 2 часа. Каждый раз соединение для небуферизованного результата запроса оставалось открытым до истечения указанного тайм-аута (через 1–6 минут).

Затем я установил его на 36000 (10 часов), и теперь он работает чуть более четырех часов -

      SET SESSION net_write_timeout = 36000

Я подозреваю, что использование небольших буферизованных запросов (количество строк зависит от вашего среднего размера строки) для выбора данных из исходной таблицы было бы лучшим решением, чем держать это соединение открытым для небуферизованного запроса в течение нескольких часов подряд.

Единственная недавняя статья, в которой обсуждаются небуферизованные запросы и эффект net_write_timeout, написана Хейденом Джеймсом. Он лишь мимоходом затронул net_write_timeout.