Lecture
You may see the error “Lost connection to MySQL server” not only because of the too small connect_timeout, but also for a number of other reasons. In this chapter we will look at these reasons.
$php phpconf2009_4.php
string(44) "Lost connection to MySQL server during query" The most common error log will show what happened:
Version: '5.1.39' socket: '/tmp/mysql_sandbox5139.sock' port: 5139 MySQL Community Server (GPL)
091002 14:56:54 - mysqld got signal 11 ;
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.
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.
key_buffer_size=8384512
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338301 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd: 0x69e1b00
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 = 0x450890f0 thread_stack 0x40000
/users/ssmirnova/blade12/5.1.39/bin/mysqld(my_print_stacktrace+0x2e)[0x8ac81e]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(handle_segfault+0x322)[0x5df502]
/lib64/libpthread.so.0[0x3429e0dd40]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN6String4copyERKS_+0x16)[0x5d9876]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Item_cache_str5storeEP4Item+0xc9)[0x52ddd9]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN26select_singlerow_subselect9send_dataER4ListI4ItemE+0x45)[0x5ca145]
/users/ssmirnova/blade12/5.1.39/bin/mysqld[0x6386d1]
/users/ssmirnova/blade12/5.1.39/bin/mysqld[0x64236a]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN4JOIN4execEv+0x949)[0x658869]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN30subselect_single_select_engine4execEv+0x36c)[0x596f3c]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Item_subselect4execEv+0x26)[0x595d96]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN24Item_singlerow_subselect8val_realEv+0xd)[0x595fbd]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Arg_comparator18compare_real_fixedEv+0x39)[0x561b89]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN12Item_func_ne7val_intEv+0x23)[0x568fb3]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN4JOIN8optimizeEv+0x12ef)[0x65208f]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xa0)[0x654850]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z13handle_selectP3THDP6st_lexP13select_resultm+0x16c)[0x65a1cc]
/users/ssmirnova/blade12/5.1.39/bin/mysqld[0x5ecbda]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z21mysql_execute_commandP3THD+0x602)[0x5efdd2]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x357)[0x5f52f7]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xe93)[0x5f6193]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_Z10do_commandP3THD+0xe6)[0x5f6a56]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(handle_one_connection+0x246)[0x5e93f6]
/lib64/libpthread.so.0[0x3429e061b5]
/lib64/libc.so.6(clone+0x6d)[0x34292cd39d]`)
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x6a39e60 = select 1 from `t1` where `c0` <> (select geometrycollectionfromwkb(`c3`) from `t1`)
thd->thread_id=2
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what We see that the MySQL server crashed due to system signal 11 (mysqld got signal 11). That is, the MySQL server requested a resource from the operating system (for example, access to a file or RAM), but was refused with code 11. This signal most often indicates Segmentation fault - denial of access to RAM. You can see exactly in the documentation for your operating system: man signal in UNIX. In Windows, you usually get an entry like "mysqld got exception 0xc0000005". For Windows exception codes, look in the system documentation.
You may also get an error like this:
100828 21:52:31 InnoDB: Error: cannot allocate 8589950976 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 46483728 bytes. Operating system errno: 12 Operating system errno: 12 is an operating system error. Operating system error numbers are not universal and may vary. If you want to be sure that this code has a certain value in your operating system or you have encountered an uncommon error, use the perror utility, which is located in the bin directory of the directory where you installed MySQL. Here, for example, what perror shows for my MacOSX installation:
$perror 12
OS error code 12: Cannot allocate memory Next we see the backtrace (starting with “Attempting backtrace.”) We will return to the backtrace later.
Below we see the request that caused this problem:
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x6a39e60 = select 1 from `t1` where `c0` <> (select geometrycollectionfromwkb(`c3`) from `t1`) Problem in the request
select 1 from `t1` where `c0` <> (select geometrycollectionfromwkb(`c3`) from `t1`) Try to play in mysql cli
$./my sql
mysql [localhost] {msandbox} ((none)) > use test
Database changed
mysql [localhost] {msandbox} (test) > select 1 from `t1` where `c0` <> (select geometrycollectionfromwkb(`c3`) from `t1`);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql [localhost] {msandbox} (test) > \q
Bye That is, it is a regularly repeated bug. You need to change the application so that it does not encounter this bug until it is fixed.
To do this, look backtrace
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Item_subselect4execEv+0x26)[0x595d96]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN24Item_singlerow_subselect8val_realEv+0xd)[0x595fbd]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN14Arg_comparator18compare_real_fixedEv+0x39)[0x561b89]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN12Item_func_ne7val_intEv+0x23)[0x568fb3]
/users/ssmirnova/blade12/5.1.39/bin/mysqld(_ZN4JOIN8optimizeEv+0x12ef)[0x65208f] It contains the following calls: Item_subselect and Item_singlerow_subselect. From here - without even looking into the MySQL code - we can conclude that the subquery is “to blame”.
Let's try to rewrite the request
$./my sql
mysql [localhost] {msandbox} (test) > select 1 from `t1` where `c0` <> geometrycollectionfromwkb(`c3`);
Empty set (0.00 sec) MySQL server is working fine! We can use the rewritten request until the bug is fixed.
Reception number 18: always use the error log
But sometimes there is no necessary information in the error log.
Same query, but on Mac
091002 16:49:48 - mysqld got signal 10 ;
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.
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.
key_buffer_size=8384512
read_buffer_size=131072
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225784 K As you can see, neither the backtrace nor the query in the error log is present. What to do?
In this case, as before, general query log will help us. MySQL first writes a query to the general query log and only then executes it. Therefore, it is quite logical to use this method for repeated problem queries.
mysql> set global general_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set global log_output='table';
Query OK, 0 rows affected (0.00 sec) Run the test. After restarting the server, we check general query log:
mysql> select argument from mysql.general_log order by event_time desc limit 10;
+--------------------------------------------+
| argument |
+--------------------------------------------+
| Access denied for user 'MySQL_Instance_Manager'@'localhost' (using password: YES) |
| select 1 from `t1` where `c0` <> (select geometrycollectionfromwkb(`c3`) from `t1`) | The request that caused the crash is detected!
Reception number 19: use the general query log if the error log does not contain information about the causes of the server crash.
When using this technique, it is possible that the mysql.general_log table will be damaged during the MySQL server crash. In this case, try writing to a file.
It is also possible that the MySQL server will stop working while recording the query in the general query log. In this case, use either your application logs or proxy.
The example we have just reviewed originated from the MySQL server bug. But the MySQL server may be abnormally stopped due to the lack of resources in the system.
The first thing you should pay attention to is RAM.
Quote from the real log:
key_buffer_size=235929600
read_buffer_size=4190208
max_used_connections=17
max_connections=2048
threads_connected=13
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 21193712 K
-----
21193712K ~= 20G That is, MySQL can use up to 20G RAM! Now powerful machines, but it is worth checking whether you really have 20G RAM.
Admission number 20: always check whether you have enough RAM for the allocated buffers.
Also note the value of the max_connections variable.
In the previous example, max_connections = 2048. This is quite a lot. Check if you have enough resources for so many simultaneous connections.
I have seen cases where users set the max_connections value significantly more than their servers could serve. This led to unpredictable crashes of MySQL server with sharply increased loads on the serviced web resources.
Reception number 21: set the value of max_connections as you can serve.
Also, the MySQL server may experience a shortage of other resources. Typically, error information is contained in the error log. Analyze this information and fix the problem.
However, it is not always the MySQL server itself that is to blame for the lack of resources. It may happen that they were taken by another application. In this case, use system monitoring tools to identify the culprit.
Admission # 22: use the monitoring tools of your operating system to establish what consumes an excessive amount of resources, which leads to the MySQL server crash.
As we previously reviewed the message “Lost connection to MySQL server” may also denote timeout. If the error log contains no other errors or you suspect this is the case, add the log_warnings = 2 option to the configuration file and check the error log after receiving the message.
Reception # 23: Use the option log_warnings = 2 to track if you have rejected connections.
Sometimes the error is repeated only with competitive requests. Use additional software and general log to figure out which ones. I will not dwell here on this topic in more detail, since it is quite complex and requires an individual solution.
Comments
To leave a comment
Databases - Error detection methods in SQL application
Terms: Databases - Error detection methods in SQL application