Detailed introduction to the error package and analysis of the mysql protocol

git

https://github.com/sea-boat/mysql-protocol

Overview

During the interaction between the mysql client and the mysql server, if the server encounters an error and needs to inform the client, it will return an error packet.

mysql communication message structure

##string payloadMessage body, the length is the previously specified payload length

Type	Name	Description
int5bdf4c78156c7953567bb5a0aef2fc53	payload length	Stored according to the least significant byte first, 3-byte payload and 1-byte sequence number combination Into the message header
intf35d6e602fd7d0f0edfa6f7d103c1b57	sequence number

Error packet

Payload

TypeNameDescriptionintf35d6e602fd7d0f0edfa6f7d103c1b57header[ff] header of the ERR packetint2cc198a1d5eb0d3eb508d858c9f5cbdberror_codeerror-codestring[1]string[5] stringerror_messagehuman readable error messageMore details: http://dev .mysql.com/doc/internals/en/packet-ERR_Packet.html

##if capabilities & CLIENT_PROTOCOL_41 {
sql_state_marker	marker of the SQL State
sql_state	SQL State
##}

Error package operation

Error package class

1. /**
    * 
    * @author seaboat
    * @date 2016-09-25
    * @version 1.0
    * <pre class="brush:php;toolbar:false"><b>email: </b>849586227@qq.com

   *

   <b>blog: </b>http://www.php.cn/;/pre>
    * <p>mysql error packet.</p>
    */public class ErrorPacket extends MySQLPacket {
       public static final byte header = (byte) 0xff;    
       private static final byte SQLSTATE_MARKER = (byte) &#39;#&#39;;    
       private static final byte[] DEFAULT_SQLSTATE = "HY000".getBytes();    
       public int errno;    
       public byte mark = SQLSTATE_MARKER;    
       public byte[] sqlState = DEFAULT_SQLSTATE;    
       public byte[] message;    
       public void read(byte[] data) {
           MySQLMessage mm = new MySQLMessage(data);
           packetLength = mm.readUB3();
           packetId = mm.read();
           mm.read();
           errno = mm.readUB2();        
           if (mm.hasRemaining() && (mm.read(mm.position()) == SQLSTATE_MARKER)) {
               mm.read();
               sqlState = mm.readBytes(5);
           }
           message = mm.readBytes();
       }    
       public void write(ByteBuffer buffer) {        
       int size = calcPacketSize();
           BufferUtil.writeUB3(buffer, size);
           buffer.put(packetId);
           buffer.put(header);
           BufferUtil.writeUB2(buffer, errno);
           buffer.put(mark);
           buffer.put(sqlState);
           buffer.put(message);
       }    @Override
       public int calcPacketSize() {        
       int size = 9;// 1 + 2 + 1 + 5
           if (message != null) {
               size += message.length;
           }        
           return size;
       }    
       @Override
       protected String getPacketInfo() {        
       return "MySQL Error Packet";
       }
   
   }

Hexadecimal conversion tool

1. /**
    * 
    * @author seaboat
    * @date 2016-09-25
    * @version 1.0
    * <pre class="brush:php;toolbar:false"><b>email: </b>849586227@qq.com

   *

   <b>blog: </b>http://www.php.cn/;/pre>
    * <p>hex transform util.</p>
    */public class HexUtil {
       private final static byte[] hex = "0123456789ABCDEF".getBytes();    
       public static String Bytes2HexString(byte[] b) {        
       byte[] buff = new byte[2 * b.length];        
       for (int i = 0; i < b.length; i++) {
               buff[2 * i] = hex[(b[i] >> 4) & 0x0f];
               buff[2 * i + 1] = hex[b[i] & 0x0f];
           }        return new String(buff);
       }    public static String str2HexStr(String str) {        
       char[] chars = "0123456789ABCDEF".toCharArray();
           StringBuilder sb = new StringBuilder("");        
           byte[] bs = str.getBytes();        
           int bit;        
           for (int i = 0; i < bs.length; i++) {
               bit = (bs[i] & 0x0f0) >> 4;
               sb.append(chars[bit]);
               bit = bs[i] & 0x0f;
               sb.append(chars[bit]);
           }        return sb.toString();
       }
   }

Error package generation test

1. /**
    * 
    * @author seaboat
    * @date 2016-09-25
    * @version 1.0
    * <pre class="brush:php;toolbar:false"><b>email: </b>849586227@qq.com

   *

   <b>blog: </b>http://www.php.cn/;/pre>
    * <p>test auth packet.</p>
    */public class ErrorPacketTest {
       @Test
       public void produce() {
           ErrorPacket err = new ErrorPacket();
           err.packetId = 1;
           err.errno = 32322;
           err.message = "sorry".getBytes();
           ByteBuffer buffer = ByteBuffer.allocate(256);
           err.write(buffer);
           buffer.flip();        byte[] bytes = new byte[buffer.remaining()];
           buffer.get(bytes, 0, bytes.length);
           String result = HexUtil.Bytes2HexString(bytes);
           System.out.println(result);
           assertTrue(Integer.valueOf(result.substring(0, 2), 16) == result
                   .length() / 2 - 4);
   
           ErrorPacket err2 = new ErrorPacket();
           err2.read(bytes);
           assertTrue(err2.errno == 32322);
           assertTrue(err2.message.length == "sorry".getBytes().length);
       }
   
   }

   The above is a detailed introduction to the error packet and analysis content of the MySQL protocol. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!