MySQL 中止连接未知错误

Question

我使用多jdbc来执行全表查询以进行数据迁移。当我使用并行 75 jdbc 查询每个表有近 3000000 行的 diff 表时，我发现 MySQL 在所有数据返回到 jdbc 之前中止了一个连接。

慢日志:

Time: 160919  9:09:38
User@Host: test[test] @  [10.142.90.20]
Thread_id: 349  Schema: mmig_1005  QC_hit: No
Query_time: 161.997180  Lock_time: 0.000560  Rows_sent: 619246  Rows_examined: 619246
Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
Filesort: No  Filesort_on_disk: No  Merge_passes: 0
explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    Extra
explain: 1    SIMPLE  CAR_NEW_6       ALL     NULL    NULL    NULL    NULL    2923495 
use mmig_1005;
SET timestamp=1474247378;
SELECT @@version, name, id, sharding_id, model
FROM CAR_NEW_6;

表 mmig_1005.car_new_6 有近 3000000 行，但 MySQL 刚刚发送了 619246 行并中止了此连接。线程id是349，然后我在mysql-err日志中发现了这个中止的连接349。

160919  9:09:38 [Warning] Aborted connection 349 to db: 'mmig_1005' user: 'test' host: '10.142.90.20' (Unknown error)
160919  9:09:38 [Warning] Aborted connection 305 to db: 'mmig_1001' user: 'test' host: '10.142.90.20' (Unknown error)

MySQL参数:

connect_timeout 10
deadlock_timeout_long   50000000
deadlock_timeout_short  10000
delayed_insert_timeout  300
innodb_flush_log_at_timeout 1
innodb_lock_wait_timeout    45
innodb_rollback_on_timeout  OFF
interactive_timeout 1800
lock_wait_timeout   600
net_read_timeout    7200
net_write_timeout   7200
rpl_semi_sync_master_timeout    10000
slave_net_timeout   30
sqlasyntimeout  10
sqlasynwarntimeout  3
thread_pool_idle_timeout    60
wait_timeout    1800
innodb_log_buffer_size  134217728
max_allowed_packet  1073741824

为什么 MySQL 在数据全部发送之前中止此连接？

Answer 1

这可能有点偏离事实，但确实如此。来自 MySQL 手册页，标题为 Communication Errors and Aborted Connections :

If a client successfully connects but later disconnects improperly or is terminated, the server increments the Aborted_clients status variable, and logs an Aborted connection message to the error log. The cause can be any of the following:

• The client program did not call mysql_close() before exiting.

• The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server. See Section 6.1.5, “Server System Variables”.

• The client program ended abruptly in the middle of a data transfer.

Other reasons for problems with aborted connections or aborted clients:

• The max_allowed_packet variable value is too small or queries require more memory than you have allocated for mysqld. See Section B.5.2.10, “Packet Too Large”.

无论您显示或尝试增加多少 max_allowed_pa​​cket，我建议重新审视策略，尝试将其分成多个部分。