| ▼MySQL Libraries | |
| Global Transaction IDentifiers (GTIDs) Library | |
| ►MySQL Binlog Libraries | |
| Binlog Event library | |
| MySQL Serialization Library | |
| MySQL Utility Library | |
| ▼Getting Started | |
| ►Coding Guidelines | This section shows the guidelines that MySQL developers follow when writing new code |
| ►General Development Guidelines | We use Git for source management |
| Legacy C++ Coding Guidelines of MySQL Server | This section covers guidelines for C++ code for the MySQL server, old code only |
| Legacy Naming Conventions | |
| Legacy Commenting Style | |
| Header Files | |
| Example Setup for ctags | Put this configuration into your ~/.ctags file: |
| DBUG Tags | |
| Infrastructure | |
| ▼Client/Server Protocol | |
| ►Protocol Basics | This is a description of the basic building blocks used by the MySQL protocol: |
| ►Basic Data Types | The protocol has a few basic types that are used throughout the protocol: |
| Integer Types | The MySQL Protocol has a set of possible encodings for integers |
| String Types | Strings are sequences of bytes and appear in a few forms in the protocol |
| MySQL Packets | If a MySQL client or server wants to send data, it: |
| ►Generic Response Packets | For most commands the client sends to the server, the server returns one of these packets in response: |
| OK_Packet | An OK packet is sent from the server to the client to signal successful completion of a command |
| ERR_Packet | This packet signals that an error occurred |
| EOF_Packet | If CLIENT_PROTOCOL_41 is enabled, the EOF packet contains a warning count and status flags |
| Character Set | MySQL has a very flexible character set support as documented in Character Set Support |
| ►Compression | Compression is: |
| Compressed Packet | The compressed packet consists of a Compressed Packet Header and a payload which is either a Compressed Payload or Uncompressed Payload |
| TLS | The MySQL Protocol also supports encryption and authentication via TLS |
| Expired Password | Since MySQL 5.6.7, a MySQL account can be expired |
| ►Connection Lifecycle | The MySQL protocol is a stateful protocol |
| ►Connection Phase | The Connection Phase performs these tasks: |
| ►Connection Phase Packets | Protocol::Handshake Protocol::SSLRequest: Protocol::HandshakeResponse: Protocol::AuthSwitchRequest: Protocol::OldAuthSwitchRequest: Protocol::AuthSwitchResponse: Protocol::AuthMoreData: Protocol::AuthNextFactor: |
| ►Protocol::Handshake | Initial Handshake Packet |
| Protocol::HandshakeV9: | Initial handshake packet for protocol version 9 |
| Protocol::HandshakeV10 | Initial handshake packet for protocol version 10 |
| Protocol::SSLRequest: | SSL Connection Request Packet |
| Protocol::HandshakeResponse: | Depending on the servers support for the CLIENT_PROTOCOL_41 capability and the clients understanding of that flag the client has to send either a Protocol::HandshakeResponse320 or Protocol::HandshakeResponse41 |
| Protocol::AuthSwitchRequest: | Authentication method Switch Request Packet |
| Protocol::OldAuthSwitchRequest: | |
| Protocol::AuthSwitchResponse: | Authentication Method Switch Response Packet which contains response data generated by the authenticatication method requested in Protocol::OldAuthSwitchRequest: packet |
| Protocol::AuthMoreData: | We need to make sure that when sending plugin supplied data to the client they are not considered a special out-of-band command, like e.g |
| Protocol::AuthNextFactor: | Next Authentication method Packet in Multi Factor Authentication |
| ►Authentication Methods | To authenticate a user against the server the client server protocol employs one of several authentication methods |
| Native Authentication | Authentication::Native41: |
| Caching_sha2_password information | |
| Clear text client plugin | |
| Windows Native Authentication | Authentication::WindowsAuth: |
| authentication_webauthn information | Page_webauthn_authentication_exchanges |
| Multi Factor Authentication | Assume the client wants to log in via user account U and that user account is defined with multiple authentication methods namely X,Y,Z |
| ►Command Phase | In the command phase, the client sends a command packet with the sequence-id [00]: |
| ►Text Protocol | |
| ►COM_QUERY | Send a Text Protocol based SQL query |
| ►COM_QUERY Response | The query response packet is a meta packet which can be one of: |
| ►LOCAL INFILE Request | If the client wants to LOAD DATA from a LOCAL file into the server it sends: |
| 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 |
| ►Text Resultset | A Text Resultset is a possible COM_QUERY Response |
| Column Definition | If CLIENT_PROTOCOL_41 is set Protocol::ColumnDefinition41: is used, Protocol::ColumnDefinition320: |
| Text Resultset Row | ProtocolText::ResultsetRow: |
| ►Utility Commands | |
| COM_QUIT | Tells the server that the client wants it to close the connection |
| COM_INIT_DB | Change the default schema of the connection |
| COM_FIELD_LIST | |
| COM_STATISTICS | Get a human readable string of some internal status vars |
| COM_DEBUG | Dump debug info to server's stdout |
| COM_PING | Check if the server is alive |
| COM_CHANGE_USER | Changes the user of the current connection |
| COM_RESET_CONNECTION | Resets the session state |
| COM_SET_OPTION | Sets options for the current connection |
| ►Prepared Statements | The prepared statement protocol was introduced in MySQL 4.1 and adds a few new commands: |
| COM_STMT_PREPARE | Creates a prepared statement for the passed query string |
| ►COM_STMT_EXECUTE | COM_STMT_EXECUTE asks the server to execute a prepared statement as identified by statement_id |
| ►COM_STMT_EXECUTE Response | Similar to the COM_QUERY Response a COM_STMT_EXECUTE returns either: |
| Binary Protocol Resultset | Binary Protocol Resultset is similar to the Text Resultset |
| COM_STMT_FETCH | Fetches the requested amount of rows from a resultset produced by COM_STMT_EXECUTE |
| COM_STMT_CLOSE | COM_STMT_CLOSE deallocates a prepared statement |
| COM_STMT_RESET | COM_STMT_RESET resets the data of a prepared statement which was accumulated with COM_STMT_SEND_LONG_DATA commands and closes the cursor if it was opened with COM_STMT_EXECUTE |
| COM_STMT_SEND_LONG_DATA | Sends the data for a parameter |
| Stored Programs | In MySQL 5.0 the protocol was extended to handle: |
| ►Replication Protocol | Replication uses binlogs to ship changes done on the master to the slave and can be written to Binlog File and sent over the network as Binlog Network Stream |
| Binlog Event | The events contain the actual data that should be shipped from the master to the slave |
| COM_BINLOG_DUMP | Request a Binlog Network Stream from the server |
| ▼X Protocol | |
| Life Cycle | Topics in this section: |
| Authentication | Topics in this section: |
| Messages | Topics in this section: |
| Expectations | Topics in this section: |
| Notices | Topics in this section: |
| Implementation of the X Protocol by the X Plugin | Topics in this section: |
| Use Cases | Topics in this section: |
| Implementation Notes | Topics in this section: |
| Comparison to MySQL C/S Protocol | |
| How to build a Community Connector | MySQL provides a set of official MySQL Connectors for several popular development frameworks and languages like Node.js, .Net, Python, Java, C, C++ and more |
| ▼SQL Query Execution | |
| ►SQL Optimizer | The task of query optimizer is to determine the most efficient means for executing queries |
| The Optimizer Trace | |
| Stored Programs | |
| Transactions | See trans_begin, trans_commit, trans_rollback |
| ▼Data Storage | |
| Innodb data lock instrumentation | Innodb Performance Schema data lock instrumentation |
| ►Innodb redo log | |
| Redo log buffer | When mtr commits, data has to be moved from internal buffer of the mtr to the redo log buffer |
| Background redo log threads | Three background log threads are responsible for writes of new data to disk: |
| Format of redo log | |
| Innodb Lock-sys | |
| Innodb utils | Useful data structures: |
| ►TempTable storage engine | The TempTable storage engine is designed to be used by the Optimizer for creating temporary tables internally when dealing with complex queries |
| Glossary | Following is a list of the terms used in the TempTable storage engine source code |
| Row format | The handler interface uses two different formats |
| ▼Replication | How replication of field metadata works. |
| ▼Security | Authorization IDs, roles and users Keyring Component |
| Authorization IDs, roles and users | |
| ►Keyring Component | |
| ►Keyring Component Implementation | |
| component_keyring_file | This is keyring component services' implementation with file as backend to store data |
| component_keyring_mysql_server | MySQL server component provides a wrapper implementation of all component services related to keyring |
| component_daemon_keyring_proxy | This plugin implementation provides wrappers over keyring plugin implementation and exposes functionality provided by plugin through keyring component service APIs |
| "How to write a new keyring component" | Common keyring implementation infrastructure provides useful parts to minimize efforts involved in writing a new component |
| Common Keyring Implementation Infrastructure | Keyring_common library includes modules that can be used in various keyring implementation |
| ▼Monitoring | Performance Schema |
| ►Performance Schema | MySQL PERFORMANCE_SCHEMA implementation |
| Instrumentation interface | MySQL performance schema instrumentation interface |
| Aggregates | Performance schema aggregates |
| Performance schema data locks | |
| Implementing a new performance_schema table | To implement a new performance schema table, two independent problems need to be resolved: |
| Notification service | |
| Resource group service | |
| ►Plugin table service | Performance Schema plugin table service is a mechanism which provides plugins/components a way to expose their own tables in Performance Schema |
| Service Introduction | This service is named as pfs_plugin_table and it exposes two major methods which are
|
| Service Interface | This interface is provided to plugins/components, using which they can expose their tables in performance schema |
| ►Example plugin/component to use this service. | Any plugin/component, which exposes tables in performance schema, has to provide an implementation of interface PFS_engine_table_proxy |
| An example plugin | Plugin Name : pfs_example_plugin_employee
Source location : plugin/pfs_table_plugin |
| An example component | Component Name : pfs_example_component_population
Source location : components/pfs_component |
| Performance schema TLS channels instrumentation | |
| ▼Extending MySQL | |
| ►Component Subsystem | The component subsystem is designed to overcome some of the architectural issues of the plugin subsystem, namely: |
| ►Component Subsystem Concepts | These are the building blocks of the component subsystem: |
| A Service and a Service Implementation | The Service is basic concept of the Components subsystem |
| The Service Registry Service | The Service Registry is a central part of Components subsystem |
| The Dynamic Loader Service | |
| ►A Component | A component is a code container that contains one or more Service Implementations |
| MySQL Component - creating implementation | The Component for the Dynamic Loader needs to be a dynamic library (for example .so or .dll) with specified entry-point method |
| Component Infrastructure Layers | |
| Components and Plugins | An important question to answer is what is the relationship between components and plugins |
| Plugins | |
| ►Plugin Services | |
| Plugin Service Anathomy | A "service" is a struct of C function pointers |
| How to add a new service | A "plugin service" is in its core a C struct containing one or more function pointers |
| What defines a "good" plugin service ? | The following is an attempt to explain what is a good plugin service |
| User Defined Functions | |
| ▼Available services | Table Access service Server telemetry traces service Event Tracking Services Server metrics instrument service Server telemetry metrics service |
| Table Access service | The TABLE ACCESS service allows components to read and write to MySQL tables owned by the component |
| ►Server telemetry traces service | Performance Schema server telemetry traces service is a mechanism which provides plugins/components a way to get notifications related to SQL statements lifetime |
| Service Introduction | This service is named mysql_server_telemetry_traces_v1 and it exposes three major methods:
|
| Service Interface | This interface is provided to plugins/components, using which they can receive notifications related to statement lifetime events |
| Example component | Component/plugin that implements telemetry tracing, typically also uses other services within the callbacks to inspect and filter out the traced statements according to its needs |
| ►Event Tracking Services | |
| Details of existing event tracking services | |
| How to create a new consumer component | A consumer of events essentially need to implement a subset of event tracking services |
| How to create a new producer component | A new producer component would typically mean new set of events being introduced |
| ►Server metrics instrument service | Performance Schema server metrics instrument service is a mechanism which provides registration of metric sources within the server or plugin/component |
| Service Introduction | This service is named pfs_metrics_v1 and it exposes the following methods:
|
| Example component | As an example, see "components/test_server_telemetry_metrics" test component source code, used to test this service |
| ►Server telemetry metrics service | Performance Schema server telemetry metrics service provides a way for plugins/components to query telemetry meters (metric groups), metrics and metric measurements in order to periodically export these measurements using Open Telemetry protocol |
| Service Introduction | This service is named mysql_server_telemetry_metrics_v1 and it exposes the set of methods to:
|
| Service Interface | Service exposes the following methods to discover registered meters using an meter iterator: |
| Example component | As an example, see "components/test_server_telemetry_metrics" test component source code, used to test this service |
| ▼Server tools | |
| ►MySQL Router | MySQL Router |
| MySQL Server Mock | |
| Client tools | See mysqldump.cc mysql.cc |
| ▼Testing Tools | |
| ►The MySQL Test Framework | This manual describes the MySQL test framework, consisting of the test driver and the test script language |
| Preface | MySQL distributions include a set of test cases and programs for running them |
| Introduction to the MySQL Test Framework | MySQL distributions include a test suite: a set of test cases and programs for running them |
| ►MySQL Test Framework Components | |
| Test Framework System Requirements | The mysqltest and mysql_client_test programs are written in C++ and are available on any system where MySQL itself can be compiled, or for which a binary MySQL distribution is available |
| How to Report Bugs from Tests in the MySQL Test Suite | If test cases from the test suite fail, you should do the following: |
| Running Test Cases | Typically, you run the test suite either from within a source tree (after MySQL has been built), or on a host where the MySQL server distribution has been installed |
| ►Writing Test Cases | |
| Writing a Test Case: Quick Start | The basic principle of test case evaluation is that output resulting from running a test case is compared to the expected result |
| ►Test Case Coding Guidelines | |
| File Naming and Organization Guidelines | Test case file names may use alphanumeric characters (A-Z, a-z, 0-9), underscore ('_') or dash ('-'), but should not start with underscore or dash |
| Test Case Content-Formatting Guidelines | When you write a test case, please keep in mind the following general guidelines |
| Naming Conventions for Database Objects | It is possible to run test cases against a production server |
| Sample Test Case | Here is a small sample test case: |
| Cleaning Up from a Previous Test Run | For efficiency, the mysqltest test engine does not start with a clean new database for running each test case, so a test case generally starts with a “cleaning up section” |
| Generating a Test Case Result File | The test code we just wrote contains no checks of the result |
| Checking for Expected Errors | A good test suite checks not only that operations succeed as they ought, but also that they fail as they ought |
| Controlling the Information Produced by a Test Case | By default, the mysqltest test engine produces output only from select, show, and other SQL statements that you expect to produce output (that is, statements that create a result set) |
| Dealing with Output That Varies Per Test Run | It is best to write each test case so that the result it produces does not vary for each test run, or according to factors such as the time of day, differences in how program binaries are compiled, the operating system, and so forth |
| Passing Options from mysql-test-run.pl to mysqld or mysqltest | mysql-test-run.pl supports several options that enable you to pass options to other programs |
| Specifying Test Case-Specific Server Options | Within a test case, many system variables can be set by using statements such as these: |
| Specifying Test Case-Specific Bootstrap Options | If a test has to run with a particular value of a bootstrap variable such as --innodb-page-size or --innodb-data-file-path, the option can be passed on the command line while running mysql-test-run.pl |
| Specifying Test Case-Specific Client Options | If a test case needs a specific client option, you can specify such options in a file named testname-client.opt |
| Using Include Files to Simplify Test Cases | The include directory contains many files intended for inclusion into test case files |
| ►Controlling the Binary Log Format Used for Tests | |
| Controlling the Binary Log Format Used for an Entire Test Run | To specify the binary log format for a test run, you can use the --mysqld or --combination option to tell mysql-test-run.pl to pass a logging option to mysqld |
| Specifying the Required Binary Log Format for Individual Test Cases | To specify within a test case that a particular binary log format is required, include one of the following lines to indicate the format: |
| Writing Replication Tests | If you are writing a replication test case, the test case file should begin with this command: |
| Thread Synchronization in Test Cases | The Debug Sync facility allows placement of synchronization points in the code |
| Suppressing Errors and Warning | After a test is finished, and if it didn't fail for some other reason, mysql-test-run.pl will check the log written by the server(s) during the test for any warnings or errors |
| Stopping and Restarting a Server During a Test | If a server dies during execution of a test case, this will be interpreted as a failure |
| Other Tips for Writing Test Cases | |
| ►MySQL Test Programs | |
| mysqltest — Program to Run Test Cases | The mysqltest program runs a test case against a MySQL server and optionally compares the output with a result file |
| mysql_client_test — Test Client API | The mysql_client_test program is used for testing aspects of the MySQL client API that cannot be tested using mysqltest and its test language |
| mysql-test-run.pl — Run MySQL Test Suite | The mysql-test-run.pl Perl script is the main application used to run the MySQL test suite |
| mysql-stress-test.pl — Server Stress Test Program | The mysql-stress-test.pl Perl script performs stress-testing of the MySQL server |
| ►mysqltest Language Reference | |
| mysqltest Input Conventions | mysqltest reads input lines and processes them as follows: |
| mysqltest Commands | mysqltest supports the commands described in this section |
| mysqltest Variables | You can define variables and refer to their values |
| mysqltest Flow Control Constructs | The syntax for if block looks like this: |
| Error Handling | If an expected error is specified and that error occurs, mysqltest continues reading input |
| ►Creating and Executing Unit Tests | |
| Unit Testing Using TAP | The unit-testing facility based on the Test Anything Protocol (TAP) is mainly used when developing Perl and PHP modules |
| Unit Testing Using the Google Test Framework | The Google Test unit-testing framework is available in MySQL source trees and distributions |
| Unit Tests Added to Main Test Runs | mysql-test-run.pl will also run unit tests at the end of full test runs, when being run from within a build directory |
| Plugins for Testing Plugin Services | MySQL server plugins have access to server “services”, as described in MySQL Services for Plugins |
| Component unit testing using the minimal chasiss | This framework allows testing components without loading them into the server |
| ▼Development Tools | |
| Lock Order | MySQL LOCK ORDER |
| ▼Code paths | This section details how the server executes some statements, to illustrate how different parts work together |
| CREATE TABLE | |
| Deprecated List | |
1.9.2