Database Articles
  Home arrow Database Articles arrow Page 3 - Client/Server Communication in MySQL
Codewalker Forums 
  Tutorials  
Database Articles  
Miscellaneous  
Navigation Usability  
PEAR Articles  
Programming Basics  
Server Administration  
XML Tutorials  
  Reviews  
Database Book Reviews  
Linux Book Reviews  
Miscellaneous Reviews  
PHP Book Reviews  
PHP Software Reviews  
Server Admin Reviews  
SQL Tool Reviews  
  Code Gallery  
Content Management Code  
Contest Code  
Counters Code  
Database Code  
Date Time Code  
Discussion Board Code  
Email Code  
File Manipulation Code  
GUI Code  
Link Farm Code  
Miscellaneous Code  
Search Code  
Site Navigation Code  
User Management Code  
Mobile Linux 
App Generation ROI 
IBM® developerWorks 
Download TestComplete 
Forums Sitemap 
Weekly Newsletter 
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
DATABASE ARTICLES

Client/Server Communication in MySQL
By: O'Reilly Media
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 6
    2007-08-10

    Table of Contents:
  • Client/Server Communication in MySQL
  • The Common 4-Byte Header
  • Relationship Between MySQL Protocol and OS Layer
  • The Credentials Packet

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    Client/Server Communication in MySQL - Relationship Between MySQL Protocol and OS Layer


    (Page 3 of 4 )

    If you try to run a network sniffer on the MySQL port, you will notice that sometimes several MySQL protocol packets are contained in one TCP/IP packet, and sometimes a MySQL packet spans several TCP/IP layer packets, while some fit into exactly one TCP/IP packet. If you somehow manage to intercept the local socket traffic, you will observe a similar effect. Some buffer writes will have exactly one packet, while others may contain several. If the lower-level socket-buffer write operation has a limit on the maximum number of bytes it can handle in one chunk, you may also see one MySQL packet being transferred in several buffer writes.

    To understand the mechanics of this phenomenon, let’s examine the API the server or the client uses to send packets. Packets are put in the network buffer with a call to my_net_write() , defined in sql/net_serv.cc. When the buffer has reached capacity, its contents will be flushed, which results in an operating system write() call on the socket—or possibly a sequence of them if the contents of the buffer cannot be written into the socket in one operation. On the operating system level this may result in sending one or more packets, depending on how much it takes to accommodate the data volume under the operating system protocol constraints.

    In some cases, the data in the network buffer needs to be sent to the client immediately. In that case, net_flush() , defined in sql/net_serv.cc, is called.

    Authenticating Handshake

    The session between a client and a server begins with an authenticating handshake. Before it can begin, the server checks whether the host that the client is connecting from is even allowed to connect to this server. If it is not, an error message packet is sent to the client notifying it that the host is not allowed to connect.

    In the case of successful host verification, the server sends a greeting packet with the standard 4-byte header, the packet sequence number set to 0, and the body in the format shown in Table4-2.

    Table 4-2. Fields of the server’s greeting packet

    Offset in the body Length Description
    0 1 Protocol version number. Decimal 10 (0x0A) in recent versions.
    Although some changes were made in the protocol in versions 4.0 and 4.1, the protocol version number remained the same because the changes were fully backward-compatible.
    1 ver_len = strlen(server_version) + 1  Zero-terminated server version string. The length is variable, and is calculated according to the formula in the Length column. The subsequent offsets are a function of the length of this field.
    ver_len + 1 4 Internal MySQL ID of the thread that is handling this connection. Low byte first.
    ver_len + 5 9 In version 4.0 and earlier, the random seed string in its entirety. In 4.1 and later, the first 8 bytes of the 20-byte random seed string. At the end is a terminating zero. Starting in version 4.1, the length of this field is controlled by the value of SCRAMBLE_LENGTH_323 , defined in include/mysql_com.h. In the earlier versions, the macro is SCRAMBLE_LENGTH , defined in sql/sql_parse.cc. With the terminating zero byte, the length of the string is one greater than the value of the macro.
    ver_len + 14 2 Server capabilities bit mask with the low byte first. See Table 4-5 later for the meaning of different bits.
    ver_len + 16 1

    Default character set code, or more precisely, the code of the default collation. A character set collation is a set of rules that defines a sequential order among characters. A list of available collations and their codes can be obtained by executing SHOW COLLATION LIKE '%' in version 4.1.

    ver_len + 17 2

    The server status bit mask with the low byte first. Reports whether the server is in transaction or autocommit mode, if there are additional results from a multistatement query, or if a good index (or some index) was used for query optimization. For details, see the SERVER_ * values in include/mysql_com.h.

    ver_len + 19 13

    Reserved for future use. Currently zeroed out.

    ver_len + 32 13

    Present only in version 4.1 and later. The rest of the random seed string terminated with a zero byte. The length is equal to the value of SCRAMBLE_LENGTH – SCRAMBLE_LENGTH_323 + 1 .

    More Database Articles Articles
    More By O'Reilly Media


     

    Buy this book now. This article is excerpted from chapter 4 of the book Understanding MySQL Internals, written by Sasha Pachev (O'Reilly, 2007; ISBN: 0596009577). Check it out today at your favorite bookstore. Buy this book now.

    DATABASE ARTICLES ARTICLES

    - More on Query Optimization for Oracle Databa...
    - Query Optimization in Oracle
    - Clusters and Other Data Structures for Oracle
    - Using Indexes with an Oracle Database
    - The Basics of Data Structures in Oracle
    - Oracle Data Structures
    - Best Practices for PL/SQL Variables
    - What`s Code Without Variables?
    - Clauses, Sorting, and SQL Queries
    - The From Clause and SQL Queries
    - Query Primer
    - Full Text Searches and Strings
    - Searching with Strings
    - Pattern Matching with Strings
    - Working with Cases of Strings





    © 2003-2009 by Developer Shed. All rights reserved. DS Cluster 4 Hosted by Hostway
    Stay green...Green IT