Сбой канала связи с MySQL во время длинных LOCK TABLES

Question

Я сталкиваюсь с некоторым странным поведением. У меня есть Java-программа, которая постоянно записывает данные в таблицу MySQL, используя подготовленные операторы и пакетные вставки. Если какой-то другой процесс выдает LOCK TABLES t WRITE на той же таблице и вскоре после этого (через несколько минут) запускает программу на Java, как и ожидалось. Однако, когда блокировка поддерживается в течение более длительного периода (более 30 минут), программа Java теряет соединение и не продолжается. Через 2 часа происходит сбой при сбое канала связи. Оператор вставки, ожидающий выполнения во время блокировки таблицы, выполняется после снятия блокировки, но после этого соединение разрывается.

Вот подробности:

• это происходит на MySQL 5.0.51a и 5.1.66
• Я использую самый последний драйвер JDBC mysql-connector-5.1.25-bin.jar
• wait_timeout установлен на 28800 (8 часов)
• трассировка стека сбоя канала связи и Java-программы приведены ниже

Кто-нибудь знает, что здесь происходит? Есть ли тайм-ауты, которые мне нужно установить / увеличить?

---

Исключение выдается через два часа:

Exception in thread "main" java.sql.BatchUpdateException: Communications link failure

The last packet successfully received from the server was 7.260.436 milliseconds ago.  The last packet sent successfully to the server was 7.210.431 milliseconds ago.
    at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1836)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1456)
    at com.mysql.jdbc.CallableStatement.executeBatch(CallableStatement.java:2499)
    at main.LockTest.main(LockTest.java:26)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 7.260.436 milliseconds ago.  The last packet sent successfully to the server was 7.210.431 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3670)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3559)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4110)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
    at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1792)
    ... 3 more
Caused by: java.net.SocketTimeoutException: Read timed out
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.read(SocketInputStream.java:129)
    at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
    at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
    at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3116)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3570)
    ... 13 more

Полная Java-программа:

package main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class LockTest {

    private static final String CONNECTION_PATTERN = "jdbc:mysql://%s/?user=%s&password=%s"
            + "&autoReconnect=true&rewriteBatchedStatements=true&characterEncoding=utf8";
    private static final String QUERY = "INSERT INTO test.lock_test (random_text) VALUES (?)";

    public static void main(String[] args) throws SQLException, InterruptedException {
        Connection con = DriverManager.getConnection(String.format(CONNECTION_PATTERN, "host", "user", "pw"));
        PreparedStatement ps = con.prepareCall(QUERY);
        int i = 0;
        while (true) {
            i++;
            ps.setString(1, Long.toString(System.currentTimeMillis()));
            ps.addBatch();
            if (i % 10 == 0) {
                ps.executeBatch();
                System.out.println(new Date() + ": inserting 10 rows");
            }
            Thread.sleep(5000);
        }
    }
}

Answer 1

Наконец-то я узнал об истинной причине. MySQL, wait_timeout не имеет ничего общего с проблемой - пока выполняется оператор (независимо от того, как долго) сеанс базы данных не находится в состоянии SLEEP и, следовательно, сессия никогда не будет закрыта MySQL.

Причина, по-видимому, заключается в операционной системе Linux (Debian 6 или 7), которая закрывает tcp-соединение после простоя в течение 2 часов (конкретный механизм мне неизвестен, может, кто-нибудь может уточнить это?).

Чтобы избежать описанных таймаутов, нужно отправлять более частые tcp пакеты поддержки активности. Для этого нужно уменьшить /proc/sys/net/ipv4/tcp_keepalive_time (Я уменьшил его с 7200 до 60). cat 60 > /proc/sys/net/ipv4/tcp_keepalive_time делает это временно; чтобы сохранить его после перезагрузки системы, нужно установить net.ipv4.tcp_keepalive_time=60 в /etc/sysctl.conf,

Answer 2

Я просто ищу тот же вопрос. Я думаю, что мой ответ может помочь вам.

1. просмотреть ваше исключение

Последний пакет, успешно полученный от сервера, был 7.260.436 миллисекунд назад.

Это, очевидно, означает, что 7.260.436 миллисекунд

2.Если ваше приложение выдает исключение, потому что wait_timeout слишком мал. Исключение показывают так:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: последний пакет, успешно полученный от сервера, был 46 408 149 миллисекунд назад. Последний пакет, успешно отправленный на сервер, был 46,208,150 миллисекунд назад. больше, чем сконфигурированное сервером значение wait_timeout. Чтобы избежать этой проблемы, следует рассмотреть возможность истечения срока действия и / или проверки допустимости соединения перед использованием в приложении, увеличения значений, настроенных сервером для тайм-аутов клиента, или использования свойства соединения Connector/J "autoReconnect=true".; SQL []; Последний пакет, успешно полученный от сервера, был 46,208,149 миллисекунд назад.  
Последний пакет, успешно отправленный на сервер, был 46,208,150 миллисекунд назад.
 больше, чем сконфигурированное сервером значение wait_timeout. 
 Вам следует рассмотреть вопрос об истечении срока действия и / или проверке правильности подключения перед использованием в вашем приложении, 
 увеличение значений, настроенных сервером для тайм-аутов клиента,
 или используя свойство соединения Connector/J 'autoReconnect=true', чтобы избежать этой проблемы.;

это означает, что 46,208,150 миллисекунд назад> wait_timeout

3. Ответ!!!

Обратите внимание на следующее ключевое слово "Ошибка канала связи", в основном это переменные MySQl: net_write_timeout или net_read_timeout 。

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: сбой линии связи

значение по умолчанию net_write_timeout и net_read_timeout мало, вы можете выполнить

 mysql> показывать переменные типа '%timeout%'; 

найти результат.