Mysql Server Hasgoneway Appears When Mysql Runs SQL?

  question

Use PHP to execute a statement with full library group by and query, the script will report an error within one or two seconds:

errno:2006   error:MySQL server has gone away

When the script executes the sql without reporting an error, any sql executed in the database will report an error:

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

Until the PHP script stops reporting errors, executing sql again will run normally.
Execute the sql directly on the mysql command line and immediately return:

ERROR 2013 (HY000): Lost connection to MySQL server during query

What exactly is the reason? Please give your comments. It is really beyond my comprehension. How could the execution of sql be rejected immediately?
And in different places (php, mysql command line), the error returned is actually different, moreover, when PHP executes the sql, why is mysql server Connection refused at this time?
The following are the relevant configuration parameters:

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

This sql itself is not long and belongs to a normal length. It only performs group by on the entire table and then performs aggregation operation. Therefore, if it is the problem of max_allowed_packet, I think it is unlikely.
The sql for the query is as follows:

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

Another phenomenon occurs when data is continuously inserted/read.
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111 “Connection refused”)
I really don’t understand why this happened.

July 7, 2018 18:39:01
To supplement MySQL’s error log:
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]’ proxis _ priv ‘entry’ @ percent root @ lan-test-ucoudbjc-php5′ ignored in-skip-name-resolvemode.
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]’ proxis _ priv ‘entry’ @ percent root @ lan-test-ucoudbjc-php5′ ignored in-skip-name-resolvemode.
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 tohttp://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, seehttp://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 plus (read_buffer_size plus sort _ buffer _ size) * max _ threads = 27761306kbytes 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 (plus 0xf7e0)[0x7f5dbe0e37e0]
/lib64/libc.so.6(gsignal plus 0x35)[0x7f5dbcfa4495]
/lib64/libc.so.6(abort plus 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 (plus 0x7aa1)[0x7f5dbe0dbaa1]
/lib64/libc.so.6(clone plus 0x6d)[0x7f5dbd05abcd]
The manual page athttp://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]’ proxis _ priv ‘entry’ @ percent root @ lan-test-ucoudbjc-php5′ ignored in-skip-name-resolvemode.
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 tohttp://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, seehttp://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 plus (read_buffer_size plus sort _ buffer _ size) * max _ threads = 27761306kbytes 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 (plus 0xf7e0)[0x7f70d1c137e0]
/lib64/libc.so.6(gsignal plus 0x35)[0x7f70d0ad4495]
/lib64/libc.so.6(abort plus 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 (plus 0x7aa1)[0x7f70d1c0baa1]
/lib64/libc.so.6(clone plus 0x6d)[0x7f70d0b8abcd]
The manual page athttp://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]’ proxis _ priv ‘entry’ @ percent root @ lan-test-ucoudbjc-php5′ ignored in-skip-name-resolvemode.
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)

I think there seems to be something wrong with your sql. Generally speaking, gone_away indicates that the database is hung up and the other is to reject the link. It is likely that your group by clause generates a large number of view queries, consumes a large amount of memory and blocks the execution of other statements, resulting in the database being hung up. You can use show process to view the threads executing at that time, which may be the maximum number of concurrent threads or other problems.