极客观点
项目管理
HarmonyOS
使用PHP执行一条全库 group by 并且带子查询的语句, 脚本运行一两秒内就会报错 :
errno:2006 error:MySQL server has gone away
在脚本执行该 sql 且未报错时, 如果在数据库执行任何 sql 都会报错 :
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql3306.sock' (111 "Connection refused")
ERROR: Can't connect to the server
直到PHP脚本那边报错停止后, 再次执行sql就运行正常.
直接在mysql命令行执行该sql, 则立即返回 :
ERROR 2013 (HY000): Lost connection to MySQL server during query
这到底是什么原因呢?请各位指教.实在是百思不得其解, sql的执行怎么会立即被拒绝?
而且在不同的地方(php, mysql命令行)执行, 返回的错误居然不一样,而且,PHP执行该sql的时候,为什么此时mysql服务器会 Connection refused 呢?
以下是相关配置参数 :
| root@localhost : (none) > show variables like '%connections%'; | |
|---|---|
| Variable_name | Value |
| extra_max_connections | 1 |
| max_connections | 3000 |
| max_user_connections | 980 |
| root@localhost : (none) > show variables like 'max_allowed_packet'; | |
|---|---|
| Variable_name | Value |
| max_allowed_packet | 67108864 |
这条sql本身并不长, 属于普通长度, 只是会对整个表进行group by, 然后进行聚合操作.所以如果是 max_allowed_packet 的问题, 我觉得可能性不大.
查询的 sql 如下 :
SELECT
a.`name`,
a.user_id,
max(a.single_day_sum) AS single_day_highest,
sum(a.single_day_sum) AS pay_sum,
a.pay_status
FROM
(
SELECT
`name`,
user_id,
sum(money) AS single_day_sum
pay_status
FROM
dalan_vip_pay_log
WHERE
pay_status = 1
GROUP BY
`name`,
user_id,
date
) AS a
GROUP BY
`name`,
user_id
还有一个现象, 在连续插入数据/读取数据时, 会出现
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111 "Connection refused")
的错误, 实在不明白为什么会出现的情况.
2018年7月7日18:39:01
补充MySQL的错误日志 :
180707 17:59:35 [Warning] 'user' entry 'root@lan-test-ucloudbjc-php5' ignored in --skip-name-resolve mode.
180707 17:59:35 [Warning] 'user' entry '@lan-test-ucloudbjc-php5' ignored in --skip-name-resolve mode.
180707 17:59:35 [Warning] 'proxies_priv' entry '@% root@lan-test-ucloudbjc-php5' ignored in --skip-name-resolve mode.
180707 17:59:35 [Note] Event Scheduler: Loaded 0 events
180707 17:59:35 [Note] Reading of all Master_info entries succeded
180707 17:59:35 [Note] Added new Master_info '' to hash table
180707 17:59:35 [Note] /work/servers/mysql/bin/mysqld: ready for connections.
Version: '10.0.13-MariaDB-log' socket: '/tmp/mysql3306.sock' port: 3306 MariaDB Server
180707 17:59:35 [Note] Event Scheduler: scheduler thread started with id 2
180707 18:02:09 [Warning] Aborted connection 5 to db: 'unconnected' user: 'root' host: '127.0.0.1' (Unknown error)
[work@Lan-Web-UCloudBJC-Release1 script_kefu]$ tail -n 200 /work/logs/mysql/error3306.log
2018-07-07 17:56:13 7f5d563ff700 InnoDB: Rolling back trx with id 31861354, 1 rows to undo
180707 17:56:13 [Note] InnoDB: Rollback of trx with id 31861354 completed
2018-07-07 17:56:13 7f5d563ff700 InnoDB: Rollback of non-prepared transactions completed
180707 17:56:32 [Note] InnoDB: Waiting for purge to start
180707 17:56:32 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.19-67.0 started; log sequence number 20205946484
180707 17:56:32 [Note] Plugin 'FEEDBACK' is disabled.
180707 17:56:32 [Note] Recovering after a crash using /work/data/mysql_data3306/log/mysql-bin
180707 17:56:32 [Note] Starting crash recovery...
180707 17:56:32 [Note] Crash recovery finished.
180707 17:56:32 [Note] Server socket created on IP: '0.0.0.0'.
180707 17:56:32 [Warning] 'user' entry 'root@lan-test-ucloudbjc-php5' ignored in --skip-name-resolve mode.
180707 17:56:32 [Warning] 'user' entry '@lan-test-ucloudbjc-php5' ignored in --skip-name-resolve mode.
180707 17:56:32 [Warning] 'proxies_priv' entry '@% root@lan-test-ucloudbjc-php5' ignored in --skip-name-resolve mode.
180707 17:56:32 [Note] Event Scheduler: Loaded 0 events
180707 17:56:32 [Note] Event Scheduler: scheduler thread started with id 2
180707 17:56:32 [Note] Reading of all Master_info entries succeded
180707 17:56:32 [Note] Added new Master_info '' to hash table
180707 17:56:32 [Note] /work/servers/mysql/bin/mysqld: ready for connections.
Version: '10.0.13-MariaDB-log' socket: '/tmp/mysql3306.sock' port: 3306 MariaDB Server
2018-07-07 17:57:59 7f5d51dfc700 InnoDB: Assertion failure in thread 140038782306048 in file btr0cur.cc line 286
InnoDB: Failing assertion: page_is_comp(get_block->frame) == page_is_comp(page)
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refm...
InnoDB: about forcing recovery.
180707 17:57:59 [ERROR] mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
To report this bug, see http://kb.askmonty.org/en/rep...
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Server version: 10.0.13-MariaDB-log
key_buffer_size=33554432
read_buffer_size=1048576
max_used_connections=1
max_threads=3002
thread_count=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 27761306 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x0 thread_stack 0x48000
mysys/stacktrace.c:247(my_print_stacktrace)[0xbb1cee]
sql/signal_handler.cc:153(handle_fatal_signal)[0x71d46b]
/lib64/libpthread.so.0(+0xf7e0)[0x7f5dbe0e37e0]
/lib64/libc.so.6(gsignal+0x35)[0x7f5dbcfa4495]
/lib64/libc.so.6(abort+0x175)[0x7f5dbcfa5c75]
btr/btr0cur.cc:325(btr_cur_latch_leaves)[0x99112a]
btr/btr0cur.cc:790(btr_cur_search_to_nth_level(dict_index_t, unsigned long, dtuple_t const, unsigned long, unsigned long, btr_cur_t, unsigned long, char const, unsigned long, mtr_t*))[0x9929b3]
row/row0row.cc:817(row_search_index_entry(dict_index_t, dtuple_t const, unsigned long, btr_pcur_t, mtr_t))[0x9333aa]
row/row0purge.cc:306(row_purge_remove_sec_if_poss_tree)[0x931718]
row/row0purge.cc:492(row_purge_remove_sec_if_poss)[0x932c75]
que/que0que.cc:1115(que_thr_step)[0x900480]
trx/trx0purge.cc:1254(trx_purge(unsigned long, unsigned long, bool))[0x95b35b]
srv/srv0srv.cc:3224(srv_do_purge)[0x94ba76]
/lib64/libpthread.so.0(+0x7aa1)[0x7f5dbe0dbaa1]
/lib64/libc.so.6(clone+0x6d)[0x7f5dbd05abcd]
The manual page at http://dev.mysql.com/doc/mysq... contains
information that should help you find out what is causing the crash.
180707 17:57:59 mysqld_safe Number of processes running now: 0
180707 17:57:59 mysqld_safe mysqld restarted
180707 17:57:59 [Note] InnoDB: Using mutexes to ref count buffer pool pages
180707 17:57:59 [Note] InnoDB: The InnoDB memory heap is disabled
180707 17:57:59 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
180707 17:57:59 [Note] InnoDB: Compressed tables use zlib 1.2.3
180707 17:57:59 [Note] InnoDB: Using Linux native AIO
180707 17:57:59 [Note] InnoDB: Using CPU crc32 instructions
180707 17:57:59 [Note] InnoDB: Initializing buffer pool, size = 512.0M
180707 17:57:59 [Note] InnoDB: Completed initialization of buffer pool
180707 17:58:00 [Note] InnoDB: Highest supported file format is Barracuda.
180707 17:58:00 [Note] InnoDB: Log scan progressed past the checkpoint lsn 20206120606
180707 17:58:00 [Note] InnoDB: Database was not shutdown normally!
180707 17:58:00 [Note] InnoDB: Starting crash recovery.
180707 17:58:00 [Note] InnoDB: Reading tablespace information from the .ibd files...
180707 17:58:00 [Note] InnoDB: Restoring possible half-written data pages
180707 17:58:00 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 20211363328
InnoDB: Doing recovery: scanned up to log sequence number 20216606208
InnoDB: Doing recovery: scanned up to log sequence number 20221252520
180707 17:58:04 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 8736141, file name /work/data/mysql_data3306/log/mysql-bin.000116
180707 17:58:09 [Note] InnoDB: 128 rollback segment(s) are active.
180707 17:58:31 [Note] InnoDB: Waiting for purge to start
180707 17:58:31 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.19-67.0 started; log sequence number 20221252520
180707 17:58:31 [Note] Plugin 'FEEDBACK' is disabled.
180707 17:58:31 [Note] Recovering after a crash using /work/data/mysql_data3306/log/mysql-bin
180707 17:58:31 [Note] Starting crash recovery...
180707 17:58:31 [Note] Crash recovery finished.
180707 17:58:32 [Note] Server socket created on IP: '0.0.0.0'.
180707 17:58:32 [Warning] 'user' entry 'root@lan-test-ucloudbjc-php5' ignored in --skip-name-resolve mode.
180707 17:58:32 [Warning] 'user' entry '@lan-test-ucloudbjc-php5' ignored in --skip-name-resolve mode.
180707 17:58:32 [Warning] 'proxies_priv' entry '@% root@lan-test-ucloudbjc-php5' ignored in --skip-name-resolve mode.
180707 17:58:32 [Note] Event Scheduler: Loaded 0 events
180707 17:58:32 [Note] Event Scheduler: scheduler thread started with id 2
180707 17:58:32 [Note] Reading of all Master_info entries succeded
180707 17:58:32 [Note] Added new Master_info '' to hash table
180707 17:58:32 [Note] /work/servers/mysql/bin/mysqld: ready for connections.
Version: '10.0.13-MariaDB-log' socket: '/tmp/mysql3306.sock' port: 3306 MariaDB Server
2018-07-07 17:59:30 7f7065dfc700 InnoDB: Assertion failure in thread 140120722228992 in file btr0cur.cc line 286
InnoDB: Failing assertion: page_is_comp(get_block->frame) == page_is_comp(page)
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refm...
InnoDB: about forcing recovery.
180707 17:59:30 [ERROR] mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
To report this bug, see http://kb.askmonty.org/en/rep...
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Server version: 10.0.13-MariaDB-log
key_buffer_size=33554432
read_buffer_size=1048576
max_used_connections=0
max_threads=3002
thread_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 27761306 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x0 thread_stack 0x48000
mysys/stacktrace.c:247(my_print_stacktrace)[0xbb1cee]
sql/signal_handler.cc:153(handle_fatal_signal)[0x71d46b]
/lib64/libpthread.so.0(+0xf7e0)[0x7f70d1c137e0]
/lib64/libc.so.6(gsignal+0x35)[0x7f70d0ad4495]
/lib64/libc.so.6(abort+0x175)[0x7f70d0ad5c75]
btr/btr0cur.cc:325(btr_cur_latch_leaves)[0x99112a]
btr/btr0cur.cc:790(btr_cur_search_to_nth_level(dict_index_t, unsigned long, dtuple_t const, unsigned long, unsigned long, btr_cur_t, unsigned long, char const, unsigned long, mtr_t*))[0x9929b3]
row/row0row.cc:817(row_search_index_entry(dict_index_t, dtuple_t const, unsigned long, btr_pcur_t, mtr_t))[0x9333aa]
row/row0purge.cc:306(row_purge_remove_sec_if_poss_tree)[0x931718]
row/row0purge.cc:492(row_purge_remove_sec_if_poss)[0x932c75]
que/que0que.cc:1115(que_thr_step)[0x900480]
trx/trx0purge.cc:1254(trx_purge(unsigned long, unsigned long, bool))[0x95b35b]
srv/srv0srv.cc:3224(srv_do_purge)[0x94ba76]
/lib64/libpthread.so.0(+0x7aa1)[0x7f70d1c0baa1]
/lib64/libc.so.6(clone+0x6d)[0x7f70d0b8abcd]
The manual page at http://dev.mysql.com/doc/mysq... contains
information that should help you find out what is causing the crash.
180707 17:59:30 mysqld_safe Number of processes running now: 0
180707 17:59:30 mysqld_safe mysqld restarted
180707 17:59:31 [Note] InnoDB: Using mutexes to ref count buffer pool pages
180707 17:59:31 [Note] InnoDB: The InnoDB memory heap is disabled
180707 17:59:31 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
180707 17:59:31 [Note] InnoDB: Compressed tables use zlib 1.2.3
180707 17:59:31 [Note] InnoDB: Using Linux native AIO
180707 17:59:31 [Note] InnoDB: Using CPU crc32 instructions
180707 17:59:31 [Note] InnoDB: Initializing buffer pool, size = 512.0M
180707 17:59:31 [Note] InnoDB: Completed initialization of buffer pool
180707 17:59:31 [Note] InnoDB: Highest supported file format is Barracuda.
180707 17:59:31 [Note] InnoDB: The log sequence numbers 5469490558 and 5469490558 in ibdata files do not match the log sequence number 20221253552 in the ib_logfiles!
180707 17:59:31 [Note] InnoDB: Database was not shutdown normally!
180707 17:59:31 [Note] InnoDB: Starting crash recovery.
180707 17:59:31 [Note] InnoDB: Reading tablespace information from the .ibd files...
180707 17:59:31 [Note] InnoDB: Restoring possible half-written data pages
180707 17:59:31 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 8736141, file name /work/data/mysql_data3306/log/mysql-bin.000116
180707 17:59:34 [Note] InnoDB: 128 rollback segment(s) are active.
180707 17:59:34 [Note] InnoDB: Waiting for purge to start
180707 17:59:35 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.19-67.0 started; log sequence number 20221253552
180707 17:59:35 [Note] Plugin 'FEEDBACK' is disabled.
180707 17:59:35 [Note] Recovering after a crash using /work/data/mysql_data3306/log/mysql-bin
180707 17:59:35 [Note] Starting crash recovery...
180707 17:59:35 [Note] Crash recovery finished.
180707 17:59:35 [Note] Server socket created on IP: '0.0.0.0'.
180707 17:59:35 [Warning] 'user' entry 'root@lan-test-ucloudbjc-php5' ignored in --skip-name-resolve mode.
180707 17:59:35 [Warning] 'user' entry '@lan-test-ucloudbjc-php5' ignored in --skip-name-resolve mode.
180707 17:59:35 [Warning] 'proxies_priv' entry '@% root@lan-test-ucloudbjc-php5' ignored in --skip-name-resolve mode.
180707 17:59:35 [Note] Event Scheduler: Loaded 0 events
180707 17:59:35 [Note] Reading of all Master_info entries succeded
180707 17:59:35 [Note] Added new Master_info '' to hash table
180707 17:59:35 [Note] /work/servers/mysql/bin/mysqld: ready for connections.
Version: '10.0.13-MariaDB-log' socket: '/tmp/mysql3306.sock' port: 3306 MariaDB Server
180707 17:59:35 [Note] Event Scheduler: scheduler thread started with id 2
180707 18:02:09 [Warning] Aborted connection 5 to db: 'unconnected' user: 'root' host: '127.0.0.1' (Unknown error)
mysql
我觉得你这个sql好像有问题，一般提示gone_away是数据库挂掉了，另外一个是拒绝链接，很可能你这个group by子句生成了大量的视图查询，消耗了大量的内存并且阻塞了其他的语句的执行，导致数据库挂掉了。可以用show process查看当时执行的线程，可能是并发数达到上限了，也可能是其他问题