Shulou(Shulou.com)06/01 Report--

Original: reprint, please state the source.

Due to the limited level and the complexity of the source code, errors are inevitable. Please study together to correct them.

This article refers to the source code:

Net_serv.cc (main reference)

Mysql.h.pp

Mysql_socket.h

Violite.h

Viosocket.c

Vio.c

Reference books:

In-depth understanding of MYSQL core technology

MYSQL Core Insider

Internals-en

MYSQL official manual

LINUX system programming manual

Note:

1. This paper will mainly analyze the uncompressed MYSQL NET package, but try not to consider the compressed MYSQL NET package to reduce the difficulty.

2. This paper mainly describes TCP- > IP- > Ethernet, regardless of other protocols such as (UDP).

3. This paper mainly uses the my_net_write of Net_serv.cc to describe the writing socket phase, but does not consider net_write_command.

In fact, the net_write_command function is the main function entry of the client transfer command package, and the lower-level functions called are consistent.

4. The write phase can reach net buffer full write or call net_flush () write, but it is impossible to study the case of net_flush () only full write.

I. basic concepts

Before that, we must understand the following basic concepts, otherwise it is not good for reading.

1. Socket: it is a way of inter-process communication, which can be used to communicate between polymorphic computers and two local processes. Similar pipes are bi-directional.

A way of communicating on a network, mainly by binding IP and ports and identifying a unique network server, by binding locally

Set a local file to communicate, which works in the LINUX kernel state.

2. Communication protocol: the protocol is a format agreed between the client and the server in advance. If the format is incorrect, the unpacking fails, such as TCP.

The protocol format is as follows, and MYSQL has its own communication protocol.

3. MYSQL protocol: as a large database system, MYSQL has its own protocol, which contains at least some of the following data packets.

1. Handshake stage

Server-to-client initialization handshake package

Client-to-server client authentication package

-- server-to-client OK package, ERROR package

2. Connection establishment phase

Client-to-server command (command) package

-- server-to-client OK package, ERROR package, result set package

The result set package contains:

1. Baotou Bao

2. FILED property package

3. EOF package

4. Line data packet

FILED property package: for column properties, each column will have one

Row packets: one packet per row to return data

If a SELECT returns 2 rows and 3 columns of data

Will contain 3 (column) + 2 (row) + 1 (header packet) + 2 (EOF packet) packets

Because of the complexity of MYSQL packets, this article does not intend to parse various packets of MYSQL protocol. Please refer to:

MYSQL Core Insider

Internals-en

The following figure shows how the MYSQL server and the client successfully shake hands and transfer data.

We agreed that it was called a MYSQL packet.

4. MYSQL NET packet: it is the size of the actual transmission packet, with a maximum size of 16M-1, which is defined dead in the source code, each MYSQL NET packet

Contains a header that contains at least 4 bytes (uncompressed package, if compressed will increase by 3 bytes), as follows:

3 bytes: (compressed) payload length

1 bytes: serial number

(compression) 3 bytes: payload length before compression

Where payload is the actual data

For example, a MYSQL NET package:

Why is there a serial number? Because in order to ensure that the packets sent by each command are orderly, such as a result

The collection package will contain multiple packets, and the row data packets in it (when SERVER- > CLIENT, each row of data is a MYSQL packet)

The packet is likely to be larger than 16M-1, so we need to divide the entire result set packet into multiple MYSQL NET packets for transmission.

Make sure he's in order when you client. Of course, not every MYSQL NET packet is very large. For example, some MYSQL packets, such as OK packets, are very large.

Small, we know that the maximum frame transmitted over Ethernet is MTU 1500 bytes, so it is possible that an Ethernet frame contains multiple MYSQL NET

A packet (such as an OK packet), or a MYSQL NET packet in multiple Ethernet frames, and a MYSQL packet in multiple MYSQL at the same time

NET package, but at least one MYSQL NET package contains at least one MYSQL packet (such as OK packet), of course TCP

Note that when a MYSQL packet is divided into multiple MYSQL NET packets, it will be followed by a flag with a length of 0 as the end, in the source code

/ * End of big multi-packet. , /

If (! pkt_len)

Goto end

We agreed that it was called the MYSQL NET package.

5. NET structure description

Let's take a look at some screenshots to illustrate:

You can see that a BUFFER is encapsulated in the NET structure, and the BUFFER is controlled by the parameter net-buffer-length

The buffer whose size cannot exceed the size of the parameter max-allowed-packet is called net buffer by convention in this article.

The default maximum of net-buffer-length is 1m at 16K.

Max-allowed-packet defaults to 4m and up to 1G

The structure also encapsulates two variables of unsigned int, write_timeout,read_timeout. They are exactly

The net-wirte-timeout,net-read-timeout parameter is specified to indicate that before an ETIMEDOUT error is returned

The maximum time of KEEPLIVE.

The underlying call that sets the timeout is most likely

Ret= mysql_socket_setsockopt (vio- > mysql_socket, SOL_SOCKET, optname,optval, sizeof (timeout))

And so on.

In addition, the structure also encapsulates retry_count, which is the number of retries when an EINTR error is encountered by the parameter net-retry-count.

Control, which will be described later

6 、 LINUX ETIMEDOUT 、 EINTR 、 EWOULDBLOCK 、 EAGAIN

# define ETIMEDOUT 110 / * Connection timed out * /

# define EINTR 4 / * Interrupted system call * /

# define EAGAIN 11 / * Try again * /

# define EWOULDBLOCK EAGAIN / * Operation would block *

7. MYSQL read and write scoket functions on LINUX platform

Located in Mysql_socket.h

Send (mysql_socket.fd, buf, IF_WIN ((int),) n, flags)

Recv (mysql_socket.fd, buf, IF_WIN ((int),) n, flags)

Of course, if it is the WIN_32 platform, the send and recv functions have the underlying encapsulation.

8. Package encapsulation process

As shown below:

This paper studies how the application layer MYSQL transmits the packet after it is encapsulated through its own protocol.

II. The writing scoket phase of MYSQL packets

1. Split potentially large MYSQL packets

Function prototype

My_bool my_net_write (NET * net, const uchar * packet, size_t len)

Net:NET structure pointer

Packet:MYSQL packet pointer, MYSQL packet is prepared by MYSQL protocol stack

Len:MYSQL packet length

This process splits large MYSQL packets into multiple MYSQL NET packets, and if they are small MYSQL packets (such as OK packets)

Package it as a MYSQL NET package and call net_write_buff. I will release the Chinese comments I wrote together with the source code as follows:

Click (here) to collapse or open

My_bool my_net_write (NET * net, const uchar * packet, size_t len) / / write data of length packet to net- > buffer

{

Uchar buffs [net _ HEADER_SIZE]; / / lenth 3 seq 1 4bytes

Int rc

If (unlikely (! net- > vio) / * nowhere to write * /

Return 0

MYSQL_NET_WRITE_START (len)

DBUG_EXECUTE_IF ("simulate_net_write_failure", {

My_error (ER_NET_ERROR_ON_WRITE, MYF (0))

Return 1

}

);

/ *

Big packets are handled by splitting them in packets of MAX_PACKET_LENGTH

Length. The last packet is always a packet that is = MAX_PACKET_LENGTH) / / if the length of the MYSQL protocol packet is greater than the maximum mysq NET packet, it is divided into multiple MYSQL NET packets

{

Const ulong z_size = MAX_PACKET_LENGTH; / / 16M-1 counts as the length of the packet

Int3store (buff, z_size); / / write the length to the stack buff

Buff [3] = (uchar) net- > pkt_nr++; / / change the seq+1 in buffer, of course, the pkt_nr sequence also + 1

If (net_write_buff (net, buff, NET_HEADER_SIZE) | | / / write MYSQL NET packet header

Net_write_buff (net, packet, z_size) / / writes a split part of the MYSQL protocol packet of length z_size to net buffer

{

MYSQL_NET_WRITE_DONE (1)

Return 1

}

Packet + = 16M-1; / / add the pointer of packet to the size of z_size, which is actually 16M-1

Len-= zambisize; / / of course, len also reduces z_size, which is actually 16M-1.

}

/ / if it is not a large MYSQL package or the last part of the MYSQL package, execute the following code

/ * Write last packet * /

Int3store (buff,len); / / count the last length in the first 3 bytes of buffer

Buff [3] = (uchar) net- > pkt_nr++; / / of course the serial number continues + 1

If (net_write_buff (net, buff, NET_HEADER_SIZE)) / / write MYSQL NET packet header

{

MYSQL_NET_WRITE_DONE (1)

Return 1

}

# ifndef DEBUG_DATA_PACKETS

DBUG_DUMP ("packet_header", buff, NET_HEADER_SIZE)

# endif

Rc= MY_TEST (net_write_buff (net,packet,len)); / / the last data written to the MYSQL protocol packet is written to net buffer

MYSQL_NET_WRITE_DONE (rc)

Return rc

} 2. Write cache phase

Function prototype

Static my_bool net_write_buff (NET * net, const uchar * packet, ulong len)

Net:NET structure pointer

Packet:MYSQL packet pointer, note that this pointer is different from the above, because the pointer after my_net_write subpacket

It will also be increased to the position after the last write each time.

Len: if it is a split large package, it is 16M-1. If it is a small packet (such as OK package), it is its corresponding length, and it may also be the length of MYSQL NET header.

The process is divided into the following situations:

-- if the MYSQL NET package is larger than the remaining space of net buffer

-- write a part of the MYSQL NET packet call memcpy to the remaining space, and then call net_write_packet for a transfer to clear the net buffer

-- if the rest of the MYSQL NET packet is still larger than net buffer (net-buffer-length), then net_write_packet is directly called for transmission

-- if the MYSQL NET package can be stored in net buffer

-- directly call memcpy to write to net buffer

Here are a few key points.

In this way, one and MYSQL actually distinguish between large MYSQL NET packets and small MYSQL NET, using net buffer to reduce the number of transmissions and improve

Performance

Two, which also reveals that the write phase will not exceed the net buffer size. Unlike read, net buffer is only an improvement in the write phase.

The performance of the cache, if it is larger than it can directly call net_write_packet write, while the read phase of different net buffer also hosts another important

The ability to combine multiple MYSQL NET packets into a single MYSQL packet, so the size of the net buffer is less than the size of a MYSQL packet

Directly lead to error reports such as: Got a packet bigger than 'max_allowed_packet' bytes

The setting of three and net buffer, that is, the setting of net-buffer-length parameters, will be directly affected here, and it will not be extended to max_allowed_packet.

The extension to max_allowed_packet occurs only in the read phase, which will be described later

Below, I will release the Chinese notes I wrote together with the source code as follows:

Click (here) to collapse or open

Static my_bool

Net_write_buff (NET * net, const uchar * packet, ulong len)

{

Ulong left_length

/ / calculate the remaining space of buffer- > max_packet below

If (net- > compress & & net- > max_packet > MAX_PACKET_LENGTH)

Left_length= (ulong) (MAX_PACKET_LENGTH-(net- > write_pos-net- > buff))

Else

Left_length= (ulong) (net- > buff_end-net- > write_pos)

# ifdef DEBUG_DATA_PACKETS

DBUG_DUMP ("data", packet, len)

# endif

If (len > left_length) / / if the length is greater than the remaining space

{

If (net- > write_pos! = net- > buff)

{

/ * Fill up already used packet and write it * /

Memcpy (net- > write_pos, packet, left_length); / / the left_lengeh length after the pointer packet is used here to fill the entire net buffer.

If (net_write_packet (net, net- > buff)

(size_t) (net- > write_pos-net- > buff) + left_length) / / when it is full, then call net_write_packet to write to scoket

/ / (size_t) (net- > write_pos-net- > buff) + left_length is the entire buffer length

Return 1

Net- > write_pos= net- > buff; / / here the wirte_pos pointer should also be moved to wirte_pos+left_lengeh

Packet+= left_length; / / packet pointer increased

Len-= left_length; / / length is correspondingly reduced

}

If (net- > compress) / / Compression attribute is not considered first, actually compression is enabled and compression using Zlib is located in Zlib/compress.

{

.}

If (len > net- > max_packet) / / if the remaining data after filling net- > max_packet is still larger than the entire net buffer size, skip the buffer and write scoket directly (important)

/ / in fact, the maximum len here is 16M-1. If the MYSQL NET package is 16M-1, it will always use the direct write method.

/ / unlike the read phase, there will be a process of merging mysql net packets into MYSQL protocol packages in the read phase, and net buffer has additional

/ the mission of

Return net_write_packet (net, packet, len); / / directly call net_write_packet to write

/ * Send out rest of the blocks as full sized blocks * /

}

Memcpy (net- > write_pos, packet, len); / / if the length is less than the remaining space in net buffer, just write net buffer

Net- > write_pos+= len; / / where the wirte_pos pointer also moves the corresponding length

Return 0

3. Carry on the compression phase

Function prototype

My_bool net_write_packet (NET * net, const uchar * packet, size_t length)

Return TRUE on error, FALSE on success.

Net:NET structure pointer

Packet: there are two possible sources of packet here

-- from net buffer

-- the offset location of the original MYSQL packet pointer, such as 16M-1 's large MYSQL NET packet

Lenth: write length

This step is actually a compression function, and there is no real transmission, so we won't discuss it too much.

Next, I will release the Chinese notes I wrote together with the source code as follows

Click (here) to collapse or open

My_bool

Net_write_packet (NET * net, const uchar * packet, size_t length) / / function doesn't really transmit, it just makes a layer of encapsulation to encapsulate the data compression.

/ / Note that the data here may come from net- > buffer may come from net_flush

{

My_bool res

DBUG_ENTER ("net_write_packet")

# if defined (MYSQL_SERVER) & & defined (USE_QUERY_CACHE)

Query_cache_insert ((char*) packet, length, net- > pkt_nr)

# endif

/ * Socket can't be used * /

If (net- > error = = 2)

DBUG_RETURN (TRUE)

Net- > reading_or_writing= 2; / / set the flag to start writing

# whether the ifdef HAVE_COMPRESS / / parameter is enabled

Const bool do_compress= net- > compress

If (do_compress) / / MYSQL decides whether to turn on compression or not

{

If ((packet= compress_packet (net, packet, & length)) = = NULL) / / compressed data reports an error if memory is insufficient

/ / {"ER_OUT_OF_RESOURCES", 1041, "Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use\ 'ulimit\' to allow mysqld to use more memory or you can add more swap space"}

/ / return a malloc memory space (the memory first address of the compressed data) to packet after compression is completed. At this time, packet is no longer the packet of formal parameters and needs to be released.

{

Net- > error= 2

Net- > last_errno= ER_OUT_OF_RESOURCES

/ * In the server, allocation failure raises an error. , /

Net- > reading_or_writing= 0

DBUG_RETURN (TRUE)

}

}

# endif / * HAVE_COMPRESS * /

# ifdef DEBUG_DATA_PACKETS

DBUG_DUMP ("data", packet, length)

# endif

Res= net_write_raw_loop (net, packet, length); / / do the real underlying transmission work

# whether the ifdef HAVE_COMPRESS// parameter is enabled

If (do_compress) / / mysql decides for itself

My_free ((void *) packet); / / as mentioned earlier, the memory of the compressed data needs to be freed to avoid leakage.

# endif

Net- > reading_or_writing= 0ram / off Mark

DBUG_RETURN (res)

} 4. Call the vio virtual Iram O interface for the write phase

Function prototype

Static my_bool net_write_raw_loop (NET * net, const uchar * buf, size_t count)

Net:NET structure pointer

Packet: there are three possible sources of buffer here

-- from net buffer

-- the offset location of the original MYSQL packet pointer, such as 16M-1 's large MYSQL NET packet

-- the above two packages after compression

Lenth: write length

This function calls the real underlying vio_write virtual IO interface function to write, and if an EINTR error is encountered, the following operations will be performed:

Thread safe client always retries if it is EINTR

If the non-thread safe client or server is EINTR and reaches net- > retry_count, it will jump out of the loop.

The server-side MYSQLD is definitely thread-safe, but it is impossible to retry indefinitely under the EINTR error for server-side performance.

Non-thread-safe clients may have confused global data, resulting in an Icano error.

In addition, if the data is not sent or the rest of the data will be thrown according to the error code.

-- ETIMEOUT error. If so, error Got timeout writing communication packets

-- otherwise Got an error writing communication packets

Note that the ETIMEOUT here is the SOCKET timeout set according to the parameter net-wirte-timeout.

Next, I will release the Chinese notes I wrote together with the source code as follows

Click (here) to collapse or open

Static my_bool

Net_write_raw_loop (NET * net, const uchar * buf, size_t count)

{

Unsigned int retry_count= 0

While (count)

{

Size_t sentcnt= vio_write (net- > vio, buf, count); / / successfully put back the number of bytes written back failed to return-1 here the data that really writes max_packet buffer package / mysql NET package > max_packet buffer to socket

/ * VIO_SOCKET_ERROR (- 1) indicates an error. , /

If (sentcnt = = VIO_SOCKET_ERROR) / / if the write operation encounters an error, the following is exception handling: generally speaking, if EINTR faints, try again, otherwise directly exit the sending data loop and enter the exception handling if statement.

{

/ * A recoverable Imax O error occurred? * /

If (net_should_retry (net, & retry_count))

/ / 1. Thread-safe client always retries if it is EINTR

/ 2. If the non-thread-safe client or server is EINTR and reaches net- > retry_count, it will jump out of the loop.

/ / the server-side MYSQLD must be thread-safe, but it is impossible to wirelessly retry under the EINTR error for the server's performance.

/ / the non-thread-safe client may have confused global data, resulting in an Icano error.

Continue

Else

Break

}

/ / the following is under normal circumstances

Count-= sentcnt; / / Total-sent

Of course, the buf+= sentcnt; / / pointer will increase accordingly.

Update_statistics (thd_increment_bytes_sent (sentcnt))

}

/ * On failure, propagate the error code. , /

If (count) / / if count > 0, that is, there is still unsent data

{

/ * Socket should be closed. , /

Net- > error= 2

/ * Interrupted by a timeout? * /

If (vio_was_timeout (net- > vio)) / / whether it is an ETIMEOUT error, and if so, an error Got timeout writing communication packets is reported

Net- > last_errno= ER_NET_WRITE_INTERRUPTED

Else / / otherwise report an error Got an error writing communication packets

Net- > last_errno= ER_NET_ERROR_ON_WRITE;#ifdef MYSQL_SERVER

My_error (net- > last_errno, MYF (0))

# endif

} at this point, the MYSQL level has completed the preparation for the conversion and transmission of MYSQL packets to MYSQL NET packets, and the next step is to use the

The underlying TCP/IP, Ethernet and other protocols are encapsulated and then transmitted through socket. The following picture shows the description above.

Make a summary, but some details in the figure are not shown, it is best to know through the source code comments.

III. The scoket stage of reading MYSQL packets

1. Merge multiple MYSQL NET packets into one MYSQL packet

Function prototype

Ulong my_net_read (NET * net)

Net:NET structure pointer, the memory to which a MYSQL packet is stored in the buffer of a NET structure

In space

The return value is the length of the actual MYSQL packet read, and the number of header bytes of the non-packet MYSQL NET packet.

This function calls net_read_packet to read a MYSQL packet. If you decompress a large MYSQL packet,

Merge operation source code comments divide large MYSQL packets into multiple MYSQL NET packets called packet of a multi-packet

Next, I will release the Chinese notes I wrote together with the source code as follows. Note that I ignore the decompression operation to reduce the difficulty of learning.

Click (here) to collapse or open

Ulong

My_net_read (NET * net) / /

{

Size_t len, complen

MYSQL_NET_READ_START ()

# ifdef HAVE_COMPRESS

If (! net- > compress) / / if there is no compression

{

# endif

Len= net_read_packet (net, & complen); / / read a MYSQL NET packet to return the actual length in the len variable, if there is compression

/ / the length before compression is saved in the complen variable. This function is also important.

/ / the function is to expand the length of max_packet buffer until max_packet_size

/ / limit. If you cannot expand, you will report an error. This also points out a reality of each MYSQL.

/ / the protocol package must be placed in a max_packet buffer, which is also a lot of packet

/ / the root cause of insufficient error reporting in buffer

Whether if (len = = MAX_PACKET_LENGTH) / / is a full packet and the size is 16M-1 size

{

/ * First packet of a multi-packet. Concatenate the packets * /

Ulong save_pos = net- > where_b

Size_t total_length= 0

Do / / here this loop completes the action of merging multiple mysql net packets into a single MYSQL protocol package

{

Net- > where_b + = len; / / read offset is increasing

Total_length + = len; / / the total length is increasing

Len= net_read_packet (net, & complen); / / read action

} while (len = = MAX_PACKET_LENGTH)

If (len! = packet_error) / / packet_err is defined as ~ (unsigned long) (0))

Len+= total_length; / / Note here that the length of the end packet after the MYSQL protocol packet is divided into multiple mysql net packets is 0, so the len will not be added.

Net- > where_b = save_pos

}

Net- > read_pos = net- > buff + net- > where_b

If (len! = packet_error)

Net- > read_ Poslen] = 0; / * Safeguard for mysql_use_result * /

MYSQL_NET_READ_DONE (0, len)

Return len; / / returns the total length of the read

# ifdef HAVE_COMPRESS

}

Else / / do not consider compression

{.

2. Obtain the MYSQL NET packet length stage

Function prototype

Static ulong net_read_packet (NET * net, size_t * complen)

Net:NET structure pointer, the memory to which a MYSQL packet is stored in the buffer of a NET structure

In space

Complen: as an output parameter, the output is the possible data length before compression

The return value is the length of the actual read MYSQL NET packet (Read the packet data (payload))

Packet_error is returned if failed.

This function is mainly encapsulated in order to obtain the length of the MYSQL NET packet, and net_read_packet_header is used to obtain MYSQL

NET packet length function, and this function calculates whether the memory space required after multiple MYSQL NET packets are one MYSQL packet is sufficient

If it is not enough, it is divided into the following operations

1. If the size of the expanded NET BUFFER does not exceed the size set by the parameter max_packet_size, the call to net_realloc () expands successfully.

2. If the size of the expanded NET BUFFER exceeds the size set by the parameter max_packet_size, the failure of calling net_realloc extension reports an error.

{"ER_NET_PACKET_TOO_LARGE", 1153, "Got a packet bigger than\ 'max_allowed_packet\' bytes"}

This is also a very common mistake.

Of course, running out of memory will cause the following errors

{"ER_OUT_OF_RESOURCES", 1041, "Out of memory; check if mysqld or some other process uses all

Available memory; if not, you may have to use\ 'ulimit\' to allow mysqld to use more memory

Or you can add more swap space "}

Net_realloc function and net_read_packet_header function will not be analyzed in detail here. If you are interested, you will study it yourself.

Next, I will release the Chinese notes I wrote together with the source code as follows

Click (here) to collapse or open

Static ulong net_read_packet (NET * net, size_t * complen)

{

Size_t pkt_len, pkt_data_len

* complen= 0

Net- > reading_or_writing= 1; / / set the read and write mark to 1 to indicate the start of the read

/ * Retrieve packet length and number. , /

If (net_read_packet_header (net)) / / read the length and MYSQL NET sequence of a MYSQL net packet

Goto error

Net- > compress_pkt_nr= net- > pkt_nr

# ifdef HAVE_COMPRESS

If (net- > compress) / / forget about compression

{

.}

# endif

/ * The length of the packet that follows. , /

Pkt_len= uint3korr (net- > buff+net- > where_b); / / obtain the length of this MYSQL NET package

/ * End of big multi-packet. , /

If (! pkt_len) / / determines whether it is the end packet of a mysql packet after subcontracting

Goto end

Pkt_data_len = max (pkt_len, * complen) + net- > where_b; / / get the memory space needed after reading this MYSQL NET package, that is, how much the whole NET BUFFER needs.

/ / the space required for compression is the length of the data before compression

/ * Expand packet buffer if necessary. , /

If ((pkt_data_len > = net- > max_packet) & & net_realloc (net, pkt_data_len)) / / the actual judgment here is whether net buffer is sufficient, and if not, call realloc for memory expansion

/ / determine whether to exceed the max_packet_size setting in realloc

Goto error

/ * Read the packet data (payload). , /

If (net_read_raw_loop (net, pkt_len)) / / starts the actual read operation

Goto error

End:

Net- > reading_or_writing= 0; / / set the read / write mark to 0 to indicate the end of the read

The return pkt_len; / / function returns this read

Error: / / error return value

Net- > reading_or_writing= 0

Return packet_error;}

3. Call the vio virtual Iram O interface for the read phase.

Function prototype

Static my_bool net_read_raw_loop (NET * net, size_t count)

Net:NET structure pointer, the memory to which a MYSQL packet is stored in the buffer of a NET structure

In space

Count: how big is the MYSQL NET packet read this time? if it is a compressed MYSQL NET packet, it is not the data before compression but the length of the compressed MYSQL NET packet.

@ return TRUE on error, FALSE on success.

Return FALSE for success and TURE for failure

Click (here) to collapse or open

Static my_bool net_read_raw_loop (NET * net, size_t count)

{

Bool eof= false

Unsigned int retry_count= 0

Uchar * buf= net- > buff + net- > where_b

While (count)

{

Size_t recvcnt= vio_read (net- > vio, buf, count); / / if the write operation encounters an error, the following is exception handling: generally speaking, if EINTR faints, try again, otherwise you will directly exit the sending data loop and enter the exception handling if statement.

/ * VIO_SOCKET_ERROR (- 1) indicates an error. , /

If (recvcnt = = VIO_SOCKET_ERROR) / /

{

/ * A recoverable Imax O error occurred? * /

If (net_should_retry (net, & retry_count))

/ / 1. Thread-safe client always retries if it is EINTR

/ 2. If the non-thread-safe client or server is EINTR and reaches net- > retry_count, it will jump out of the loop.

/ / the server-side MYSQLD must be thread-safe, but it is impossible to wirelessly retry under the EINTR error for the server's performance.

/ / the non-thread-safe client may have confused global data, resulting in an Icano error.

Continue

Else

Break

}

/ * Zero indicates end of file. , /

Else if (! recvcnt) / / recv semi-connected status? LINUX man recv:The return values will be 0 when the peer has performed an orderly shutdown

{

Eof= true

Break

}

Count-= recvcnt

Buf+= recvcnt

Update_statistics (thd_increment_bytes_received (recvcnt))

}

/ * On failure, propagate the error code. , /

If (count) / / if count > 0, that is, the expected data is not read

{

/ * Socket should be closed. , /

Net- > error= 2

/ * Interrupted by a timeout? * /

If (! eof & & vio_was_timeout (net- > vio)) / / whether it is an ETIMEOUT error, and if so, an error Got timeout reading communication packets is reported

Net- > last_errno= ER_NET_READ_INTERRUPTED

Else

Net- > last_errno= ER_NET_READ_ERROR;// otherwise error Got an error reading communication packets is reported.

# ifdef MYSQL_SERVER

My_error (net- > last_errno, MYF (0))

# endif

}

Return MY_TEST (count)

} this function is similar to the previous write phase, calling the underlying vio virtual IO interface to actually read

It will also occur that if the data is not sent or the remaining part will be thrown according to the error code.

-- ETIMEOUT error. If so, error Got timeout reading communication packets

-- otherwise Got an error reading communication packets

Note that the ETIMEOUT here is the SOCKET timeout set according to the parameter net-read-timeout.

At this point, the MYSQL level completes the conversion and merging process from reading to MYSQL NET packets to MYSQL packets, reading work

The next step is to encapsulate it through underlying TCP/IP, Ethernet and other protocols, and then read it through socket. The following picture shows the description above.

Make a summary, but some details in the figure are not shown, it is best to know through the source code comments.

4. Use TCPDUMP to grab MYSQL NET package parsing instance

Although this article does not parse the MYSQL protocol, it uses tcpdump to grab the package to carry out a simple interaction between the client and the server after the connection is established.

Use the command

Tcpdump tcp port 3307-X > log.log

1. Select model

Client: select * from test.test; (command package)

Server: return query results (result set package)

Id1 id2

1 1

2 2

3 3

4 4

5 5

6 6

7 7

1311 12 Flags 48 IP bogon.61796 > testmy.opsession-prxy: Flags [P.], seq 53:82, ack 19791, win 16142, length 29

1312 0x0000: 4500 0045 0dbe 4000 4006 2f45 c0a8 be01 E.

1313 0x0010: c0a8 be5d f164 0ceb 097a 4b52 7e10 8b88.] .d.zKR ~.

1314 0x0020: 5018 3f0e 9de8 0000 1900 0000 0373 656c P.?.sel

1315 0x0030: 6563 7420 2a20 6672 6f6d 2074 6573 742e ect.*.from.test.

1316 0x0040: 7465 7374 3b test

1317 12 Flags 48 IP testmy.opsession-prxy 29.632651 IP testmy.opsession-prxy > bogon.61796: Flags [P.], seq 19791 Freud 19956, ack 82, win 131,165

1318 0x0000: 4500 00cd f754 4000 4006 4526 c0a8 be5d.

1319 0x0010: c0a8 be01 0ceb f164 7e10 8b88 097a 4b6f .d ~.... zKo

1320 0x0020: 5018 0083 fe6f 0000 0100 0001 0226 0000 P.O.

1321 0x0030: 0203 6465 6604 7465 7374 0474 6573 7404.. def.test.test.

1322 0x0040: 7465 7374 0269 6402 6964 0c3f 000b 0000 test.id.id.?....

1323 0x0050: 0003 0350 0000 0028 0000 0303 6465 6604... (.... def.

1324 0x0060: 7465 7374 0474 6573 7404 7465 7374 0369 test.test.test.i

1325 0x0070: 6432 0369 6432 0c3f 000b 0000 0003 0000 d2.id2.?.

1326 0x0080: 0000 0005 0000 04fe 0000 2200 0400 0005.

1327 0x0090: 0131 0131 0400 0006 0132 0132 0400 0007 .1.1.2.2....

1328 0x00a0: 0133 0133 0400 0008 0134 0134 0400 0009 .3.3.4.4....

1329 0x00b0: 0135 0135 0400 000a 0136 0136 0400 000b .5.5.6.6....

1330 0x00c0: 0137 0137 0500 000c fe00 0022 00 .7.7.

Client:

IP bogon.61796 > testmy.opsession-prxy client port 61796 TCP packets sent to port 3307

1314 0x0020: 1900 0000 0373 656c .sel

1315 0x0030: 6563 7420 2a20 6672 6f6d 2074 6573 742e ect.*.from.test.

1316 0x0040: 7465 7374 3b test

Let's just watch this part.

1900 00 MYSQL NET packet length small end is displayed as 0X19=25, of course, a total of 25 bytes from 0373.

00 MYSQL NET serial number

03 the first 0X03 representative of the MYSQL protocol command package is the COM_QUERY instruction

There is nothing to say later. The carrying is the strings mode of select * from test.test;.

Server:

IP testmy.opsession-prxy > TCP packets sent by bogon.61796 server port 3307 to client port 61796

On the server side, we leave the analysis of the column property package to the reader, starting with the EOF package after the column property.

05 0000: the small end is displayed as 0X05=05

04: the SEQ of this MYSQL NET package in the entire result set package

Fe: always 0XFE

0000: number of warnin

2200: status indication

0400 00: the small end is displayed as 0X05=04MYSQL NET length

05: the SEQ of this MYSQL NET package in the entire result set package

The length 31 of the first column of the result set returned by 0131purl 01 is the actual data 1.

The length 31 of the first column data in the returned result set is the actual data 1.

And so on, we can see all the results, and we also show the fact that because a row is recorded as a result set row package, then

An error occurs when this line of data is so long that it exceeds the max_packet_size size of the client (such as MYSQL MYSQLDUMP).

We should also pay great attention to this point that has been analyzed in the source code analysis.

2. Insert model

Client: insert into test.test values (100100), (101102), (103103), (104104)

(105105), (106107), (108109), (111123); (command package)

Server: returns the number of affected rows, etc. (OK package)

[SQL] insert into test.test values (100100), (101102), (103103), (104104)

(105105), (106107), (108109), (111123)

Affected lines: 8

Time: 0.027s

13 Flags 07 IP bogon.61796 39.121552 IP bogon.61796 > testmy.opsession-prxy: Flags [P.], seq 220 Flags 335, ack 39809, win 16140, length 115

0x0000: 4500 009b 100f 4000 4006 2c9e c0a8 be01 E.

0x0010: c0a8 be5d f164 0ceb 097a 4de5 7e11 54c6.] .d.zM. ~ .T.

0x0020: 5018 3f0c 9890 0000 6f00 0000 0369 6e73 P.?.o....ins

0x0030: 6572 7420 696e 746f 2074 6573 742e 7465 ert.into.test.te

0x0040: 7374 2076 616c 7565 7328 3130 302c 3130 st.values

0x0050: 3029 2c28 3130 312c 3130 3229 2c28 3130 0), (101102), (10

0x0060: 332c 3130 3329 2c28 3130 342c 3130 3429 3103), (104104)

0x0070: 2c0d 0a28 3130 352c 3130 3529 2c28 3130,.. (105105), (10)

0x0080: 362c 3130 3729 2c28 3130 382c 3130 3929 6107), (108109)

0x0090: 2c28 3131 312c 3132 3329 3b, (111123)

13 Flags 07 IP testmy.opsession-prxy 39.147808 IP testmy.opsession-prxy > bogon.61796: Flags [P.], seq 39809 seq 39859, ack 335, win 140, length 50

0x0000: 4500 005a f77f 4000 4006 456e c0a8 be5d E.. Z.room.en.]

0x0010: c0a8 be01 0ceb f164 7e11 54c6 097a 4e58 .d ~ .T..zNX

0x0020: 5018 008c fdfc 0000 2e00 0001 0008 0002 P.

0x0030: 0000 0026 5265 636f 7264 733a 2038 2020... & Records:.8..

0x0040: 4475 706c 6963 6174 6573 3a20 3020 2057 Duplicates:.0..W

0x0050: 6172 6e69 6e67 733a 2030 arnings:.0

Client:

Bogon.61796 > testmy.opsession-prxy client port 61796 TCP packets sent to port 3307

6f00 0000 0369 6e73 starts here

The small end of the 6f00 00:MYSQL NET packet length is shown as 0X6f=111, of course, a total of 111bytes starting from 0369.

00:MYSQL NET serial number

The first 0X03 representative of the 03:MYSQL protocol command package is the COM_QUERY instruction, where query not only represents SELECT.

Represents INSERT\ UPDATE\ DELETE\ SELECT

There is nothing to say about insert into test.test values (100100), (101102), (103103), (104104).

(105105), (106107), (108109), (111123)

Strings acsii coding of

It also shows the fact that if the client source imports statements, then each INSERT statement is an instruction package.

If this instruction package is larger than the size of the server max_packet_size, it will report an error, or other errors, which requires great attention.

Server:

IP testmy.opsession-prxy > TCP packets sent by bogon.61796 server port 3307 to client port 61796

Let's start with 2e00 0001 0008 0002

2e00 00 MYSQL NET packet length small end is displayed as 0X2e=46

01 MYSQL NET serial number

00 is always 0

08 affects the number of rows

00 insert ID

02 00 server status

00 00 number of warnings

Finally, there is nothing more to say about the ASCII coding of the message.

5. Some errors mentioned in this article

1. {"ER_OUT_OF_RESOURCES", 1041, "Out of memory; check if mysqld or some

Other process uses all available memory; if not, you may have to use

\ 'ulimit\' to allow mysqld to use more memory or you can add more swap space "}

Insufficient memory failed to allocate memory

2. {"ER_NET_PACKET_TOO_LARGE", 1153, "Got a packet bigger than\ 'max_allowed_packet\' bytes"}

In the read phase, due to the limitation of max_allowed_packet size, net buffer cannot be expanded, so an MYSQL packet must be stored in a

In net buffer.

3. {"ER_NET_WRITE_INTERRUPTED", 1161, "Got timeout writing communication packets"}

{"ER_NET_READ_INTERRUPTED", 1159, "Got timeout reading communication packets"}

Keep the connection active until an ETIMEDOUT error is returned due to the net-wirte-timeout,net-read-timeout parameter specification

Number of seconds

VI. Wrong demonstration

MYSQLD server

The official documentation of the MYSQL client program

? Max_allowed_packet

The maximum size of the buffer for client/server communication. The default is 16MB, the maximum is

1GB.

? Net_buffer_length

The buffer size for TCP/IP and socket communication. (Default value is 16KB.)

Mysql.cc is defined in the source code

{"max_allowed_packet", OPT_MAX_ALLOWED_PACKET

"The maximum packet length to send to or receive from server."

& opt_max_allowed_packet, & opt_max_allowed_packet, 0

GET_ULONG, REQUIRED_ARG, 16 * 1024L*1024L, 4096

(longlong) 2*1024L*1024L*1024L, MALLOC_OVERHEAD, 1024, 0}

{"net_buffer_length", OPT_NET_BUFFER_LENGTH

"The buffer size for TCP/IP and socket communication."

& opt_net_buffer_length, & opt_net_buffer_length, 0, GET_ULONG

REQUIRED_ARG, 16384, 1024, 512 "1024" 1024L, MALLOC_OVERHEAD, 1024, 0}

The official documentation of the MYSQLDUMP client program

? Max_allowed_packet

The maximum size of the buffer for client/server communication. The default is 24MB, the maximum is

1GB.

? Net_buffer_length

The initial size of the buffer for client/server communication. When creating multiple-row INSERT

Statements (as with the-extended-insert or-opt option), mysqldump creates rows up

To net_buffer_length bytes long. If you increase this variable, ensure that the MySQL server

Net_buffer_length system variable has a value at least this large.

Mysqldump.c is defined in the source code

{"max_allowed_packet", OPT_MAX_ALLOWED_PACKET

"The maximum packet length to send to or receive from server."

& opt_max_allowed_packet, & opt_max_allowed_packet, 0

GET_ULONG, REQUIRED_ARG, 24,1024,1024, 4096

(longlong) 2L*1024L*1024L*1024L, MALLOC_OVERHEAD, 1024, 0}

{"net_buffer_length", OPT_NET_BUFFER_LENGTH

"The buffer size for TCP/IP and socket communication."

& opt_net_buffer_length, & opt_net_buffer_length, 0

GET_ULONG, REQUIRED_ARG, 1024*1024L-1025, 4096, 16*1024L*1024L

MALLOC_OVERHEAD-1024, 1024, 0}

We can not see that neither MYSQLDUMP nor MYSQL client programs have these two command line arguments, and their functions are already in the above source code parsing.

It is explained that net_buffer_length also has an extra attention in MYSQLDUMP, that is, when multiple result set rows and packages enter NET BUFFER

Later, it needs to be output, and here it seems that its output is also stated in the official documentation in NET BUFFER, in other words, when using multiple-row

In INSERT mode, the length of a statement is controlled by him. Note that the max_allowed_packet of the server must be greater than this value when importing, because

When importing, an INSERT statement is a command package from the client to the server. The MYSQL server must save the command package to read the data in a

In NET BUFFER

Let's verify MYSQLDUMP's claim.

/ mysqldata/mysql5.7/bin/mysqldump-socket=/mysqldata/mysql5.7/mysqld3307.sock-uroot-pgelc123-net-buffer-length=4k test testpack2 > log10.log

/ mysqldata/mysql5.7/bin/mysqldump-socket=/mysqldata/mysql5.7/mysqld3307.sock-uroot-pgelc123-net-buffer-length=16k test testpack2 > log11.log

Cat log10.log | grep INSERT | head-N1 > test10.log

Cat log11.log | grep INSERT | head-N1 > test11.log

[root@testmy ~] # du-hs test1*

4.0K test10.log

16K test11.log

Indeed, as we expected, a multiple-row has changed its size due to changes in NET BUFFER.

Next, let's simulate the situation in which a mysql packet on the server and the client is larger than the max_allowed_packet error report.

1. The server reports an error

Use

/ mysqldata/mysql5.7/bin/mysqldump-socket=/mysqldata/mysql5.7/mysqld3307.sock-uroot-pgelc123-net-buffer-length=5m test testpack2 > log11.log

This will generate a command package of about 5m

Then source is performed on the server side

The default server max_allowed_packet is 4m, and the net buffer length is 16k.

Mysql > show variables like'% max_allowed_packet%'

+-+ +

| | Variable_name | Value |

+-+ +

| | max_allowed_packet | 4194304 | |

| | slave_max_allowed_packet | 1073741824 | |

+-+ +

Mysql > source / root/test11.log

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 63

Current database: test

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 64

Current database: test

ERROR 2006 (HY000): MySQL server has gone away

You can see that the server reported an error.

2017-05-07T07:14:15.957486Z 58 [Note] Aborted connection 58 to db: 'test' user:' root' host: 'localhost' (Got a packet bigger than' max_allowed_packet' bytes)

2017-05-07T07:14:16.020153Z 63 [Note] Aborted connection 63 to db: 'test' user:' root' host: 'localhost' (Got a packet bigger than' max_allowed_packet' bytes)

2017-05-07T07:14:16.080146Z 64 [Note] Aborted connection 64 to db: 'test' user:' root' host: 'localhost' (Got a packet bigger than' max_allowed_packet' bytes)

The reason has been described in detail earlier. Let's modify the max_allowed_packet to 6m.

Source again

Mysql > show variables like'% max_allowed_packet%'

+-+ +

| | Variable_name | Value |

+-+ +

| | max_allowed_packet | 5999616 | |

| | slave_max_allowed_packet | 1073741824 | |

+-+ +

Mysql > use test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > source / root/log11.log

Query OK, 349522 rows affected (3.49 sec)

Records: 349522 Duplicates: 0 Warnings: 0

Query OK, 174766 rows affected (1.77 sec)

Records: 174766 Duplicates: 0 Warnings: 0

2. Client MYSQL

To facilitate testing, I constructed a row of about 2m data, and we knew that a row of data was a MYSQL packet

Another error is simulated here.

Mysql > insert into testpack2 values (100dint repeat ('axiaojin7000000))

ERROR 1301 (HY000): Result of repeat () was larger than max_allowed_packet (5999616)-truncated

The error report is obviously larger than the max_allowed_packet (5999616) set by our server, and this command line package uses repeat but repeat

The number exceeds the max_allowed_packet setting of the server, resulting in an error.

Let's change it to about 2m.

Mysql > insert into testpack2 values (100 dint repeat ('aura dome 2000000))

Query OK, 1 row affected (0.06 sec)

No problem

[root@testmy data] # / mysqldata/mysql5.7/bin/mysql-- socket=/mysqldata/mysql5.7/mysqld3307.sock-uroot-pgelc123-- max-allowed-packet=1m-e'select * from test.testpack2 where id=100' > log.log

Mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2020 (HY000) at line 1: Got packet bigger than 'max_allowed_packet' bytes

We saw the expected error report.

Modified-- max-allowed-packet is 2m

[root@testmy data] # / mysqldata/mysql5.7/bin/mysql-- socket=/mysqldata/mysql5.7/mysqld3307.sock-uroot-pgelc123-- max-allowed-packet=2m-e'select * from test.testpack2 where id=100' > log.log

Mysql: [Warning] Using a password on the command line interface can be insecure.

[root@testmy data] #

The error disappeared.

3. Client MYSQLDUMP

Using the above data, the same result does not need to be described too much.

[root@testmy data] # / mysqldata/mysql5.7/bin/mysqldump-socket=/mysqldata/mysql5.7/mysqld3307.sock-uroot-pgelc123-max-allowed-packet=1m test testpack2 > log2.log

Mysqldump: [Warning] Using a password on the command line interface can be insecure.

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass-- set-gtid-purged=OFF. To make a complete dump, pass-all-databases-triggers-routines-events.

Mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `testpack 2` at row: 524288

[root@testmy data] # ^ C

[root@testmy data] # / mysqldata/mysql5.7/bin/mysqldump-socket=/mysqldata/mysql5.7/mysqld3307.sock-uroot-pgelc123-max-allowed-packet=2m test testpack2 > log2.log

Mysqldump: [Warning] Using a password on the command line interface can be insecure.

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass-- set-gtid-purged=OFF. To make a complete dump, pass-all-databases-triggers-routines-events.

[root@testmy data] #

These three tests are used to prove that both the client and the server need to merge a MYSQL packet in NET BUFFER during the read phase. if a MYSQL packet is larger than-- the max-allowed-packet setting will be thrown wrong, of course, there is no need for the write phase, and the source code has been parsed in detail.

So far, the whole article has been described in detail from preliminary knowledge to source code parsing to packet parsing to error proving. It took me about two and a half days, basically a little more than the whole weekend, because I was afraid that I would have less time to study during office hours. So hurry up to finish it.

There must be some inaccuracies or inaccuracies, especially source code parsing, because there is not too much data and it is particularly difficult to understand the designer's thinking, and there is also something related to the underlying SOCKET communication, because there is not too much to analyze, so

In some places, it will be given in the form of an article if a detailed analysis is carried out in the future.

Author Wechat:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.