Skip navigation links
15.7 Prepared Statements »
Section Navigation      [Toggle]

15.6. Text Protocol

15.6.1. COM_SLEEP
15.6.2. COM_QUIT
15.6.3. COM_INIT_DB
15.6.4. COM_QUERY
15.6.5. COM_FIELD_LIST
15.6.6. COM_CREATE_DB
15.6.7. COM_DROP_DB
15.6.8. COM_REFRESH
15.6.9. COM_SHUTDOWN
15.6.10. COM_STATISTICS
15.6.11. COM_PROCESS_INFO
15.6.12. COM_CONNECT
15.6.13. COM_PROCESS_KILL
15.6.14. COM_DEBUG
15.6.15. COM_PING
15.6.16. COM_TIME
15.6.17. COM_DELAYED_INSERT
15.6.18. COM_CHANGE_USER
15.6.19. COM_DAEMON

The old commands are supported for all MySQL Server versions from 3.20 upwards (and perhaps older).

15.6.1. COM_SLEEP

COM_SLEEP:

internal server command

Payload 

1              [00] COM_SLEEP
Returns

ERR_Packet

New in 3.20

15.6.2. COM_QUIT

COM_QUIT:

tells the server that the client wants to close the connection

response: either a connection close or a OK_Packet

Payload 

1              [01] COM_QUIT
Fields

command (1) -- 0x01 COM_QUIT

Example 

01 00 00 00 01

New in 3.20

15.6.3. COM_INIT_DB

COM_INIT_DB:

change the default schema of the connection

Returns

OK_Packet or ERR_Packet

Payload 

1              [02] COM_INIT_DB
string[EOF]    schema name
Fields

  • command (1) -- 0x02 COM_INIT_DB

  • schema_name (string.EOF) -- name of the schema to change to

Example 

05 00 00 00 02 74 65 73    74                         .....test

New in 3.20

15.6.4. COM_QUERY

15.6.4.1. COM_QUERY Response
COM_QUERY:

A COM_QUERY is used to send the server a text-based query that is executed immediately.

The server replies to a COM_QUERY packet with a COM_QUERY Response.

The length of the query-string is a taken from the packet length - 1.

Payload 

1              [03] COM_QUERY
string[EOF]    the query the server shall execute
Fields

  • command_id (1) -- 0x03 COM_QUERY

  • query (string.EOF) -- query_text

Implemented by

mysql_query()

Returns

COM_QUERY_Response

Example 

21 00 00 00 03 73 65 6c    65 63 74 20 40 40 76 65    !....select @@ve
72 73 69 6f 6e 5f 63 6f    6d 6d 65 6e 74 20 6c 69    rsion_comment li
6d 69 74 20 31                                        mit 1

New in 3.20

15.6.4.1. COM_QUERY Response

15.6.4.1.1. Text Resultset
15.6.4.1.2. LOCAL INFILE request

The query-response packet is a meta packet which can be one of

COM_QUERY_Response:

[D]

The type of the packet is defined by the type-identifier: 

COM_QUERY response
  response to a COM_QUERY packet

  payload
    lenenc-int     number of columns in the resultset

If the number of columns in the resultset is 0, this is a OK_Packet.


[D]

If it is not a valid Protocol::LengthEncodedInteger it is either a ERR_Packet or a Protocol::LOCAL_INFILE_Request.


[D]
15.6.4.1.1. Text Resultset
15.6.4.1.1.1. Column Type
15.6.4.1.1.2. Column Definition
15.6.4.1.1.3. Text Resultset Row

A Text Resultset is a possible COM_QUERY Response.

It is made up of two parts:

  • the column definitions

  • the rows

which consist of a sequence of packets.

The column defintions part starts with a packet containing the column-count, followed by as many Column Definition packets as we have columns and terminated by an EOF packet.

Each row is a packet too. The rows are terminated by another EOF_Packet. In case the query could generate the column-definition, but generating the rows afterwards failed, a ERR_Packet may be sent instead of the last EOF_Packet.

ProtocolText::Resultset:

If the SERVER_MORE_RESULTS_EXISTS flag is set in the last EOF_Packet another ProtocolText::Resultset will follow (see Multi-resultset).


[D]
Example

see ProtocolText::Resultset

15.6.4.1.1.1. Column Type
Protocol::ColumnType:
Protocol::MYSQL_TYPE_DECIMAL:
Value

0x00

Implemented by

ProtocolBinary::MYSQL_TYPE_DECIMAL

Protocol::MYSQL_TYPE_TINY:
Value

0x01

Implemented by

ProtocolBinary::MYSQL_TYPE_TINY

Protocol::MYSQL_TYPE_SHORT:
Value

0x02

Implemented by

ProtocolBinary::MYSQL_TYPE_SHORT

Protocol::MYSQL_TYPE_LONG:
Value

0x03

Implemented by

ProtocolBinary::MYSQL_TYPE_LONG

Protocol::MYSQL_TYPE_FLOAT:
Value

0x04

Implemented by

ProtocolBinary::MYSQL_TYPE_FLOAT

Protocol::MYSQL_TYPE_DOUBLE:
Value

0x05

Implemented by

ProtocolBinary::MYSQL_TYPE_DOUBLE

Protocol::MYSQL_TYPE_NULL:
Value

0x06

Implemented by

ProtocolBinary::MYSQL_TYPE_NULL

Protocol::MYSQL_TYPE_TIMESTAMP:
Value

0x07

Implemented by

ProtocolBinary::MYSQL_TYPE_TIMESTAMP

Protocol::MYSQL_TYPE_LONGLONG:
Value

0x08

Implemented by

ProtocolBinary::MYSQL_TYPE_LONGLONG

Protocol::MYSQL_TYPE_INT24:
Value

0x09

Implemented by

ProtocolBinary::MYSQL_TYPE_INT24

Protocol::MYSQL_TYPE_DATE:
Value

0x0a

Implemented by

ProtocolBinary::MYSQL_TYPE_DATE

Protocol::MYSQL_TYPE_TIME:
Value

0x0b

Implemented by

ProtocolBinary::MYSQL_TYPE_TIME

Protocol::MYSQL_TYPE_DATETIME:
Value

0x0c

Implemented by

ProtocolBinary::MYSQL_TYPE_DATETIME

Protocol::MYSQL_TYPE_YEAR:
Value

0x0d

Implemented by

ProtocolBinary::MYSQL_TYPE_YEAR

Protocol::MYSQL_TYPE_NEWDATE:
Value

0x0e

Implemented by

ProtocolBinary::MYSQL_TYPE_NEWDATE

Protocol::MYSQL_TYPE_VARCHAR:
Value

0x0f

Implemented by

ProtocolBinary::MYSQL_TYPE_VARCHAR

Protocol::MYSQL_TYPE_BIT:
Value

0x10

Implemented by

ProtocolBinary::MYSQL_TYPE_BIT

Protocol::MYSQL_TYPE_NEWDECIMAL:
Value

0xf6

Implemented by

ProtocolBinary::MYSQL_TYPE_NEWDECIMAL

Protocol::MYSQL_TYPE_ENUM:
Value

0xf7

Protocol::MYSQL_TYPE_SET:
Value

0xf8

Protocol::MYSQL_TYPE_TINY_BLOB:
Value

0xf9

Implemented by

ProtocolBinary::MYSQL_TYPE_TINY_BLOB

Protocol::MYSQL_TYPE_MEDIUM_BLOB:
Value

0xfa

Implemented by

ProtocolBinary::MYSQL_TYPE_MEDIUM_BLOB

Protocol::MYSQL_TYPE_LONG_BLOB:
Value

0xfb

Implemented by

ProtocolBinary::MYSQL_TYPE_LONG_BLOB

Protocol::MYSQL_TYPE_BLOB:
Value

0xfc

Implemented by

ProtocolBinary::MYSQL_TYPE_BLOB

Protocol::MYSQL_TYPE_VAR_STRING:
Value

0xfd

Implemented by

ProtocolBinary::MYSQL_TYPE_VAR_STRING

Protocol::MYSQL_TYPE_STRING:
Value

0xfe

Implemented by

ProtocolBinary::MYSQL_TYPE_STRING

Protocol::MYSQL_TYPE_GEOMETRY:
Value

0xff

15.6.4.1.1.2. Column Definition
Protocol::ColumnDefinition:

if CLIENT_PROTOCOL_41 is set Protocol::ColumnDefinition41 is used, Protocol::ColumnDefinition320 otherwise

Protocol::ColumnDefinition41:

Column Definition

Payload 

lenenc_str     catalog
lenenc_str     schema
lenenc_str     table
lenenc_str     org_table
lenenc_str     name
lenenc_str     org_name
lenenc_int     length of fixed-length fields [0c]
2              character set
4              column length
1              type
2              flags
1              decimals
2              filler [00] [00]
  if command was COM_FIELD_LIST {
lenenc_int     length of default-values
string[$len]   default values
  }
Implemented by

Protocol::send_result_set_metadata()

Fields

  • catalog (lenenc_str) -- catalog (always "def")

  • schema (lenenc_str) -- schema-name

  • table (lenenc_str) -- virtual table-name

  • org_table (lenenc_str) -- physical table-name

  • name (lenenc_str) -- virtual column name

  • org_name (lenenc_str) -- physical column name

  • next_length (lenenc_int) -- length of the following fields (always 0x0c)

  • character_set (2) -- is the column character set and is defined in Protocol::CharacterSet.

  • column_length (4) -- maximum length of the field

  • column_type (1) -- type of the column as defined in Column Type

  • flags (2) -- flags

  • decimals (1) -- max shown decimal digits * 0x00 for integers and static strings * 0x1f for dynamic strings, double, float * 0x00 - 0x51 for decimals

Note

decimals and column_length can be used for text-output formatting.

Protocol::ColumnDefinition320:

Column Definition

Payload 

lenenc-str     table
lenenc-str     name
lenenc_int     [03] length of the column_length field
3              column_length
lenenc_int     [01] length of type field
1              type
  if capabilities & CLIENT_LONG_FLAG {
lenenc_int     [03] length of flags+decimals fields
2              flags
1              decimals
  } else {
1              [02] length of flags+decimals fields
1              flags
1              decimals
  }
  if command was COM_FIELD_LIST {
lenenc_int     length of default-values
string[$len]   default values
  }
Implemented by

sql/protocol.cc Protocol::send_result_set_metadata()

15.6.4.1.1.3. Text Resultset Row
ProtocolText::ResultsetRow:

A row with the data for each column.

15.6.4.1.2. LOCAL INFILE request
15.6.4.1.2.1. LOCAL INFILE data

If the client wants to LOAD DATA from a LOCAL file into the server it sends: 

LOAD DATA LOCAL INFILE '<filename>' INTO TABLE <table>;

The LOCAL keyword triggers the server to send a LOCAL INFILE request packet which asks the client to send the file via a Protocol::LOCAL_INFILE_Data response.


[D]

The client has to set the CLIENT_LOCAL_FILES capability.

Protocol::LOCAL_INFILE_Request:
Returns

LOCAL INFILE data

Payload 

1              [fb] LOCAL INFILE
string[EOF]    filename the client shall send
Example 

0c 00 00 01 fb 2f 65 74    63 2f 70 61 73 73 77 64    ...../etc/passwd
15.6.4.1.2.1. LOCAL INFILE data

If the client has data to send, it sends in one or more non-empty packets AS IS followed by a empty packet.

If the file is empty or there is a error while reading the file only the empty packet is sent.

Protocol::LOCAL_INFILE_Data:
Payload 

string[EOF]             the filedata
Fields

data (string.EOF) -- the raw file data

15.6.5. COM_FIELD_LIST

15.6.5.1. COM_FIELD_LIST response
COM_FIELD_LIST:

get the column definitions of a table

Payload 

1              [04] COM_FIELD_LIST
string[NUL]    table
string[EOF]    field wildcard
Returns

COM_FIELD_LIST response

Implemented by

mysql_list_fields()

New in 3.20

15.6.5.1. COM_FIELD_LIST response

The response to a COM_FIELD_LIST can either be a

  • a ERR_Packet or

  • one or more Column Definition packets and a closing EOF_Packet 

    31 00 00 01 03 64 65 66    04 74 65 73 74 09 66 69    1....def.test.fi
65 6c 64 6c 69 73 74 09    66 69 65 6c 64 6c 69 73    eldlist.fieldlis
74 02 69 64 02 69 64 0c    3f 00 0b 00 00 00 03 00    t.id.id.?.......
00 00 00 00 fb 05 00 00    02 fe 00 00 02 00          ..............

15.6.6. COM_CREATE_DB

COM_CREATE_DB:

create a schema

Payload 

1              [05] COM_CREATE_DB
string[EOF]    schema name
Returns

OK_Packet or ERR_Packet

Example 

05 00 00 00 05 74 65 73    74                         .....test

Added in 3.20. No longer supported, as of 4.0.0.

15.6.7. COM_DROP_DB

COM_DROP_DB:

drop a schema

Payload 

1              [06] COM_DROP_DB
string[EOF]    schema name
Returns

OK_Packet or ERR_Packet

Example 

05 00 00 00 06 74 65 73    74                         .....test

Added in 3.20. No longer supported, as of 4.0.0.

15.6.8. COM_REFRESH

a low-level version of several FLUSH ... and RESET ... commands.

COM_REFRESH:

call REFRESH or FLUSH statements

Payload 

1              [07] COM_REFRESH
1              flags
Fields

  • command (1) -- 0x07 COM_REFRESH

  • sub_command (1) -- a bitmask of sub-systems to refresh

    sub_command

    constant name

    description

    0x01

    REFRESH_GRANT

    Refresh grant tables FLUSH PRIVILEGES

    0x02

    REFRESH_LOG

    Start on new log file FLUSH LOGS

    0x04

    REFRESH_TABLES

    close all tables FLUSH TABLES

    0x08

    REFRESH_HOSTS

    Flush host cache FLUSH HOSTS

    0x10

    REFRESH_STATUS

    Flush status variables FLUSH STATUS

    0x20

    REFRESH_THREADS

    Flush thread cache

    0x40

    REFRESH_SLAVE

    Reset master info and restart slave thread RESET SLAVE

    0x80

    REFRESH_MASTER

    Remove all bin logs in the index and truncate the index RESET MASTER

Returns

OK_Packet or ERR_Packet

New in 3.20

15.6.9. COM_SHUTDOWN

COM_SHUTDOWN is used to shutdown the mysql-server.

SHUTDOWN privilege is required.

COM_SHUTDOWN:

shutdown the server

Payload 

1              [08] COM_SHUTDOWN
  if shutdown type != 0x00 {
1              shutdown type
  }
Fields

  • command (1) -- 0x08 COM_SHUTDOWN

  • sub_command (1) -- optional if sub_command is 0x00

    type

    constant name

    description

    0x00

    SHUTDOWN_DEFAULT

    defaults to SHUTDOWN_WAIT_ALL_BUFFERS

    0x01

    SHUTDOWN_WAIT_CONNECTIONS

    wait for existing connections to finish

    0x02

    SHUTDOWN_WAIT_TRANSACTIONS

    wait for existing trans to finish

    0x08

    SHUTDOWN_WAIT_UPDATES

    wait for existing updates to finish (=> no partial MyISAM update)

    0x10

    SHUTDOWN_WAIT_ALL_BUFFERS

    flush InnoDB buffers and other storage engines' buffers

    0x11

    SHUTDOWN_WAIT_CRITICAL_BUFFERS

    don't flush InnoDB buffers, flush other storage engines' buffers

    0xfe

    KILL_QUERY

    		 

    0xff

    KILL_CONNECTION

    		 
Returns

EOF_Packet or ERR_Packet

New in 3.20

Note

Even if several shutdown types are defined, right now only one is in use: SHUTDOWN_WAIT_ALL_BUFFERS

15.6.10. COM_STATISTICS

Get a human readable string of internal statistics.

COM_STATISTICS:

get a list of active threads

Todo.  check description

Returns

string.EOF

Payload 

1              [09] COM_STATISTICS

New in 3.20

15.6.11. COM_PROCESS_INFO

COM_PROCESS_INFO:

get a list of active threads

Returns

a ProtocolText::Resultset or ERR_Packet

Payload 

1              [0a] COM_PROCCESS_INFO

Deprecated in 4.1.0use SHOW PROCESSLIST instead.

New in 3.20

15.6.12. COM_CONNECT

COM_CONNECT:

an internal command in the server

Payload 

1              [0b] COM_CONNECT
Returns

ERR_Packet

New in 3.20

15.6.13. COM_PROCESS_KILL

Same as KILL <id>.

COM_PROCESS_KILL:

ask the server to terminate a connection

Returns

OK_Packet or ERR_Packet

Payload 

1              [0c] COM_PROCCESS_KILL
4              connection id

New in 3.20

15.6.14. COM_DEBUG

COM_DEBUG triggers a dump on internal debug info to stdout of the mysql-server.

The SUPER privilege is required for this operation.

COM_DEBUG:

dump debug info to stdout

Returns

EOF_Packet or ERR_Packet on error

Payload 

1              [0d] COM_DEBUG

New in 3.21

15.6.15. COM_PING

COM_PING:

check if the server is alive

Returns

OK_Packet

Payload 

1              [0e] COM_PING

New in 3.22

15.6.16. COM_TIME

COM_TIME:

an internal command in the server

Payload 

1              [0f] COM_TIME
Returns

ERR_Packet

New in 3.22

15.6.17. COM_DELAYED_INSERT

COM_DELAYED_INSERT:

an internal command in the server

Payload 

1              [10] COM_DELAYED_INSERT
Returns

ERR_Packet

New in 3.22

15.6.18. COM_CHANGE_USER

COM_CHANGE_USER changes the user of the current connection and reset the connection state.

  • user variables

  • temp tables

  • prepared statemants

  • ... and others

It is followed by the same states as the initial handshake.

COM_CHANGE_USER:

change the user of the current connection

Returns

Authentication Method Switch Request Packet or ERR_Packet

Payload 

1              [11] COM_CHANGE_USER
string[NUL]    user
  if capabilities & SECURE_CONNECTION {
1              auth-response-len
string[$len]   auth-response
  } else {
string[NUL]    auth-response
  }
string[NUL]    schema-name
  if more data {
2              character-set (since 5.1.23?)
  }
  if more data {
string[NUL]    auth plugin name (since WL1054 and if CLIENT_PLUGIN_AUTH is used)
  }
Implemented by

parse_com_change_user_packet()

New in 3.23

character set is the connection character set as defined in Protocol::CharacterSet and is also the encoding of user and schema-name.

15.6.19. COM_DAEMON

COM_DAEMON:

an internal command in the server

Payload 

1              [1d] COM_DAEMON
Returns

ERR_Packet

New in 5.0

Copyright © 1997, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices