MySQL Internals Manual

Abstract

This is the MySQL Internals Manual.

For legal information, see the Legal Notice.

For help with using MySQL, please visit the MySQL Forums, where you can discuss your issues with other MySQL users.

Document generated on: 2020-08-10 (revision: 566)

Table of Contents

Preface and Legal Notice
1 A Guided Tour Of The MySQL Source Code
1.1 Getting the Source Tree
1.2 The Major Directories
1.2.1 Major Directories: BUILD
1.2.2 Major Directories: client
1.2.3 Major Directories: myisam
1.2.4 Major Directories: mysys
1.2.5 Major Directories: sql
1.2.6 Major Directories: vio
1.3 The Flow
1.4 The Open-Source Directories
1.5 The Internal and External Storage Engine Directories
1.6 The OS-Specific Directories
1.7 Odds and Ends
1.8 A Chunk of Code in /sql/sql_update.cc
1.9 The Skeleton of the Server Code
1.10 Recap
2 Coding Guidelines
3 Reusable Classes and Templates
3.1 Containers
3.1.1 Array
3.1.2 I_P_List
3.1.3 I_List
3.2 Memory Management
3.2.1 MEM_ROOT
3.3 How to Extend This Page
4 Building MySQL Server with CMake
4.1 CMake Prerequisites
4.2 How to Install CMake
4.3 Quick How-to-Build Instructions
4.3.1 Release Configuration
4.3.2 Debug Configuration
4.3.3 Building Using the Same Options as MySQL Official Releases
4.4 Detailed How-to-Build Instructions
4.4.1 Create the Build Directory
4.4.2 Configure the Build
4.4.3 Listing Configuration Options
4.4.4 Changing Configuration Options
4.4.5 Building Debug Configurations
4.4.6 Building With CMake
4.4.7 ./configure Emulation
4.4.8 For Developers: How to Write Platform Checks
4.4.9 Troubleshooting CMake Code
4.4.10 Developer CMake Tips
4.4.11 MySQL-Specific CMake Macros
4.4.12 Interface to Third-Party Tools
4.5 FAQ / Miscellany
4.5.1 Running mysql-test-run.pl in an Out-of-Source Build
4.5.2 Running mysql-test-run.pl with Visual Studio or Xcode Projects
4.5.3 make distclean
4.5.4 Compiling for Different Hardware Architectures
4.6 Autotools to CMake Transition Guide
4.6.1 Command Invocation Syntax
4.6.2 Installation Layout Options
4.6.3 Storage Engine Options
4.6.4 Library Options
4.6.5 Miscellaneous Options
4.6.6 Debugging the Configuration Process
4.6.7 Interface to Third-Party Tools
5 Plugins
5.1 CMake Variables to Control Plugin Building (MySQL 5.5 and Later)
5.2 CMake Macro to Define the Plugin
5.3 Autotools configure Support (MySQL 5.1)
5.4 Autotools Plugin Macros
5.5 Specifying mysqld Variables Within a Plugin
5.6 Additional Accessors for Plugins
5.7 MySQL Services for Plugins
5.7.1 Plugin Services Components
5.7.2 Writing Services for Use from Within Plugins
6 Transaction Handling in the Server
6.1 Historical Note
6.2 Current Situation
6.3 Data Layout
6.4 Transaction Life Cycle
6.5 Roles and Responsibilities
6.6 Additional Notes on DDL and the Normal Transaction
7 The Optimizer
7.1 Code and Concepts
7.1.1 Definitions
7.1.2 The Optimizer Code
7.2 Primary Optimizations
7.2.1 Optimizing Constant Relations
7.2.2 Optimizing Joins
7.2.3 Transpositions
7.2.4 ORDER BY Clauses
7.2.5 GROUP BY and Related Conditions
7.3 Other Optimizations
7.3.1 NULLs Filtering for ref and eq_ref Access
7.3.2 Partitioning-Related Optimizations
8 Tracing the Optimizer
8.1 Typical Usage
8.2 System Variables Controlling the Trace
8.3 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table
8.4 Traceable Queries
8.5 Automatic Trace Purging
8.6 Tuning Trace Purging
8.7 Tracing Memory Usage
8.8 Privilege Checking
8.9 Interaction with the --debug Option
8.10 The optimizer_trace System Variable
8.11 The end_markers_in_json System Variable
8.12 Selecting Optimizer Features to Trace
8.13 General Trace Structure
8.14 Example
8.15 Nicely Displaying a Trace
8.16 Preventing Use of Optimizer Trace
8.17 Testing
8.18 Implementation
9 Memory Allocation
9.1 Memory Allocation in the MySQL Server (sql Directory)
9.2 Memory Allocation in a Library or Storage Engine
10 Important Algorithms and Structures
10.1 The Item Class
10.2 How MySQL Does Sorting (filesort)
10.3 Bulk Insert
10.4 How MySQL Does Caching
10.5 How MySQL Uses the Join Buffer Cache
10.6 How MySQL Handles FLUSH TABLES
10.7 Full-Text Search
10.8 FLOAT and DOUBLE Data Type Representation
10.9 Date and Time Data Type Representation
10.10 Threads
10.11 Character Sets and Collations
10.12 Error Flags and Functions
10.13 Functions in the mysys Library
10.14 Bitmaps
11 File Formats
11.1 MySQL .frm File Format
12 How MySQL Performs Different Selects
12.1 Steps of Select Execution
12.2 select_resultClass
12.3 SIMPLE or PRIMARY SELECT
12.4 Structure Of Complex Select
12.5 Non-Subquery UNION Execution
12.6 Derived Table Execution
12.7 Subqueries
12.8 Single Select Engine
12.9 Union Engine
12.10 Special Engines
12.11 Explain Execution
13 How MySQL Transforms Subqueries
13.1 Item_in_subselect::select_transformer
13.1.1 Scalar IN Subquery
13.1.2 Row IN Subquery
13.2 Item_allany_subselect
13.3 Item_singlerow_subselect
14 MySQL Client/Server Protocol
14.1 Overview
14.1.1 Basic Data Types
14.1.2 MySQL Packets
14.1.3 Generic Response Packets
14.1.4 Character Set
14.1.5 Connection Lifecycle
14.1.6 Command Phase
14.2 Connection Phase
14.2.1 Initial Handshake
14.2.2 Auth Phase Fast Path
14.2.3 Authentication Method Mismatch
14.2.4 Authentication After COM_CHANGE_USER Command
14.2.5 Connection Phase Packets
14.2.6 Capability Flags
14.3 Authentication Method
14.3.1 Limitations
14.3.2 Old Password Authentication
14.3.3 Secure Password Authentication
14.3.4 Clear Text Authentication
14.3.5 Windows Native Authentication
14.3.6 SHA256
14.4 Compression
14.4.1 Compressed Packet
14.4.2 Compressed Packet Header
14.4.3 Compressed Payload
14.4.4 Uncompressed Payload
14.5 SSL
14.6 Text Protocol
14.6.1 COM_SLEEP
14.6.2 COM_QUIT
14.6.3 COM_INIT_DB
14.6.4 COM_QUERY
14.6.5 COM_FIELD_LIST
14.6.6 COM_CREATE_DB
14.6.7 COM_DROP_DB
14.6.8 COM_REFRESH
14.6.9 COM_SHUTDOWN
14.6.10 COM_STATISTICS
14.6.11 COM_PROCESS_INFO
14.6.12 COM_CONNECT
14.6.13 COM_PROCESS_KILL
14.6.14 COM_DEBUG
14.6.15 COM_PING
14.6.16 COM_TIME
14.6.17 COM_DELAYED_INSERT
14.6.18 COM_CHANGE_USER
14.6.19 COM_RESET_CONNECTION
14.6.20 COM_DAEMON
14.7 Prepared Statements
14.7.1 Binary Protocol Resultset
14.7.2 Binary Protocol Resultset Row
14.7.3 Binary Protocol Value
14.7.4 COM_STMT_PREPARE
14.7.5 COM_STMT_SEND_LONG_DATA
14.7.6 COM_STMT_EXECUTE
14.7.7 COM_STMT_CLOSE
14.7.8 COM_STMT_RESET
14.8 Stored Procedures
14.8.1 Multi-Resultset
14.8.2 Multi-Statement
14.8.3 COM_SET_OPTION
14.8.4 COM_STMT_FETCH
14.9 Replication Protocol
14.9.1 Binlog File
14.9.2 Binlog Network Stream
14.9.3 Binlog Version
14.9.4 Binlog Event
14.9.5 COM_BINLOG_DUMP
14.9.6 COM_BINLOG_DUMP_GTID
14.9.7 COM_TABLE_DUMP
14.9.8 COM_CONNECT_OUT
14.9.9 COM_REGISTER_SLAVE
14.10 Row-Based Replication
14.10.1 TABLE_MAP_EVENT
14.10.2 ROWS_EVENT
14.10.3 ROWS_QUERY_EVENT
14.11 Semi-Synchronous Replication
14.11.1 Semi-Synchronous Binlog Event
14.11.2 Semi-Synchronous ACK Packet
14.12 Protocol Examples
14.12.1 A mysql Client Logs In
14.12.2 ProtocolText::Resultset
14.12.3 Auth Method Switch
14.12.4 SHA256 Example
14.13 Source Code Locations
14.13.1 MySQL Server
14.13.2 Binary Protocol Type Implementation
15 X Protocol
15.1 Life Cycle
15.2 Authentication
15.3 Messages
15.4 Expectations
15.5 Notices
15.6 Implementation of the X Protocol by the X Plugin
15.7 Use Cases
15.8 Implementation Notes
15.9 Comparison to MySQL C/S Protocol
16 Stored Programs
17 Prepared Statement and Stored Routine Re-Execution
17.1 Statement Re-Execution Requirements
17.2 Preparation of a Prepared Statement
17.3 Execution of a Prepared Statement
17.4 Execution of a Stored Procedure Statement
18 Writing a Procedure
18.1 Extend Class Procedure
18.1.1 Constructor
18.1.2 change_columns()
18.1.3 send_row()
18.1.4 add()
18.1.5 end_group()
18.1.6 end_of_records()
18.2 Initialization Callback
18.3 Calling Sequence
18.4 Required Server Code Patches
19 Replication
19.1 Chapter Organization
19.2 Source Code Files
19.3 Principles
19.3.1 Binlog Formats
19.3.2 Differences Between Master and Slave
19.3.3 Crashes
19.3.4 Binlog Files and Indexes
19.4 Rules
19.4.1 Determining the Logging Format
19.4.2 Unsafe Statements
19.4.3 Logging Transactions
19.4.4 Logging Updates to auto_increment Columns
19.4.5 Logging Access to Variables and Functions
19.4.6 Other Unsafe Statements
19.4.7 binlog_row_image
19.4.8 Replication Locks
20 The Binary Log
20.1 Binary Log Overview
20.2 High-Level Binary Log Structure and Contents
20.3 Source Files Related to the Binary Log
20.3.1 Source File Archaeological Notes
20.4 Generating Browsable Binary Log Information
20.5 Event Classes and Types
20.5.1 Event Class Archaeological Notes
20.6 Event Meanings
20.7 Event Structure
20.7.1 Event Content-Writing Conventions
20.7.2 Event Header Fields
20.7.3 Event Data Fields (Event-Specific Information)
20.8 Binary Log Versions
20.8.1 Determining the Binary Log Version
20.8.2 Ensuring Compatibility of Future Binary Log Versions
20.9 Event Data for Specific Event Types
20.9.1 LOAD DATA INFILE Events
20.10 Row-Based Binary Logging
20.11 Additional Resources
21 MyISAM Storage Engine
21.1 MyISAM Record Structure
21.1.1 Introduction
21.1.2 Physical Attributes of Columns
21.1.3 Where to Look For More Information
21.2 The .MYI File
21.2.1 MyISAM Files
21.3 MyISAM Dynamic Data File Layout
21.3.1 Layout of the Record Storage Frame (Record Part, Record Block)
21.3.2 Record Contents
21.3.3 Packed Record Layout
21.3.4 In-memory Record Layout
21.4 MyISAM Compressed Data File Layout
21.4.1 Huffman Compression
21.4.2 The myisampack Program
21.4.3 Record and Blob Length Encoding
21.4.4 Code Tree Representation
21.4.5 Usage of the Index File
21.4.6 myisampackTricks
21.4.7 Detailed Specification of the Decoding
21.5 MyISAM Key Cache
21.5.1 MyISAM Concurrent Insert
22 InnoDB Storage Engine
22.1 InnoDB Record Structure
22.1.1 High-Altitude Picture
22.1.2 Where to Look for More Information
22.2 InnoDB Page Structure
22.2.1 High-Altitude View
22.2.2 Example
22.2.3 Where to Look For More Information
22.3 InnoDB Mutex and Read/Write Lock Implementation
23 Writing a Custom Storage Engine
23.1 Additional Resources
23.2 Overview
23.3 Creating Storage Engine Source Files
23.4 Adding Engine Specific Variables and Parameters
23.5 Creating the handlerton
23.6 Handling Handler Instantiation
23.7 Defining Filename Extensions
23.8 Creating Tables
23.9 Opening a Table
23.10 Implementing Basic Table Scanning
23.10.1 Implementing the store_lock() Method
23.10.2 Implementing the external_lock() Method
23.10.3 Implementing the rnd_init() Method
23.10.4 Implementing the info(uinf flag) Method
23.10.5 Implementing the extra() Method
23.10.6 Implementing the rnd_next() Method
23.11 Closing a Table
23.12 Adding Support for INSERT to a Storage Engine
23.13 Adding Support for UPDATE to a Storage Engine
23.14 Adding Support for DELETE to a Storage Engine
23.15 Supporting Non-Sequential Reads
23.15.1 Implementing the position() Method
23.15.2 Implementing the rnd_pos() Method
23.16 Supporting Indexing
23.16.1 Indexing Overview
23.16.2 Getting Index Information During CREATE TABLE Operations
23.16.3 Creating Index Keys
23.16.4 Parsing Key Information
23.16.5 Providing Index Information to the Optimizer
23.16.6 Preparing for Index Use with index_init()
23.16.7 Cleaning up with index_end()
23.16.8 Implementing the index_read() Method
23.16.9 Implementing the index_read_idx() Method
23.16.10 Implementing the index_read_last() Method
23.16.11 Implementing the index_next() Method
23.16.12 Implementing the index_prev() Method
23.16.13 Implementing the index_first() Method
23.16.14 Implementing the index_last() Method
23.17 Supporting Transactions
23.17.1 Transaction Overview
23.17.2 Starting a Transaction
23.17.3 Implementing ROLLBACK
23.17.4 Implementing COMMIT
23.17.5 Adding Support for Savepoints
23.18 API Reference
23.18.1 bas_ext
23.18.2 close
23.18.3 create
23.18.4 delete_row
23.18.5 delete_table
23.18.6 external_lock
23.18.7 extra
23.18.8 index_end
23.18.9 index_first
23.18.10 index_init
23.18.11 index_last
23.18.12 index_next
23.18.13 index_prev
23.18.14 index_read
23.18.15 index_read_idx
23.18.16 index_read_last
23.18.17 info
23.18.18 open
23.18.19 position
23.18.20 records_in_range
23.18.21 rnd_init
23.18.22 rnd_next
23.18.23 rnd_pos
23.18.24 start_stmt
23.18.25 store_lock
23.18.26 update_row
23.18.27 write_row
23.19 FAQ
24 Test Synchronization
24.1 Sleep
24.2 Wait Condition
24.3 Dbug Sleep
24.4 Error Injection
24.5 User-Level Locks
24.6 Debug Sync Facility
24.6.1 Formal Syntax for DEBUG_SYNC Values
24.6.2 Debug Sync Activation/Deactivation
24.6.3 Debug Sync Implementation
24.6.4 A Typical Synchronization Pattern
24.6.5 Co-work With the DBUG Facility
24.6.6 Debug Sync Further Reading
24.7 Debug Sync Point (Obsolete)
24.7.1 Backup Breakpoint
25 Injecting Test Faults
25.1 Test Fault Macros
25.2 Test Fault Macro Usage
26 How to Create Good Test Cases
26.1 Formal Stuff
26.1.1 Coding Style
26.1.2 SQL Statement Example
26.1.3 Please Avoid too Dense Code
26.1.4 Header of Scripts
26.1.5 Header Example
26.1.6 Comments Within Boxes
26.2 Comments Everywhere
26.2.1 Complicated test Architecture or Tricky Code
26.2.2 A Subtest Case is Able to Reveal a Bug
26.2.3 Subtests
26.2.4 Make Test Protocols More Understandable
26.2.5 Better Protocol Example
26.3 Some Rules Outside of the Formal Stuff
26.3.1 Error Masking
26.3.2 Use of the Option --disable_abort_on_error
26.3.3 Perfect Cleanup at the End of a Test
26.3.4 Use of OS-Specific Commands
26.4 Negative Tests
26.5 Tests with Several Variants
26.5.1 Variation of the Storage Engine
26.5.2 Variation of the Protocol Option
26.6 Miscellaneous Tips
26.6.1 Stability Checks for Random Timing Problems
26.6.2 Stability Checks for Hostname-Related Problems
26.6.3 Stability Checks for Unexpected Problems
26.6.4 Row Order Within Result Sets
26.6.5 Test Case Behavior Should Depend on the Result of a SHOW
26.6.6 Does a Test Execution Hang?
26.6.7 If Your Test is Complicated, Support Debugging
26.6.8 You Need to Know Some Syntax, an Option, Etc., but Hate Reading Manuals
26.7 Notes on MTR2
26.8 Examples of Suspicious Tests and Scripts
26.8.1 Probably Mismatch of Focus of Test and Code Sequence
26.8.2 Too Greedy Test
26.8.3 Risky Handling Around Additional Sessions
27 Error Messages
27.1 Adding New Error Messages to MySQL
27.1.1 Adding an Error Message to Multiple MySQL Versions
27.1.2 Adding Error Messages to MySQL 5.0.3 or Higher
27.1.3 Adding Error Messages to Old (< MySQL 5.0) Versions
27.2 Marking an Error Message Obsolete
27.3 Adding Storage Engine Error Messages
A MySQL Source Code Distribution
A.1 Directory Listing
A.1.1 The BUILD Directory
A.1.2 The client Directory
A.1.3 The config Directory
A.1.4 The cmd-line-utils Directory
A.1.5 The dbug Directory
A.1.6 The Docs Directory
A.1.7 The extra Directory
A.1.8 The heap Directory
A.1.9 The include Directory
A.1.10 The innobase Directory
A.1.11 The libmysql Directory
A.1.12 The libmysql_r Directory
A.1.13 The libmysqld Directory
A.1.14 The man Directory
A.1.15 The myisam Directory
A.1.16 The myisammrg Directory
A.1.17 The mysql-test Directory
A.1.18 The mysys Directory
A.1.19 The ndb Directory
A.1.20 The netware Directory
A.1.21 The NEW-RPMS Directory
A.1.22 The os2 Directory
A.1.23 The pstack Directory
A.1.24 The regex Directory
A.1.25 The SCCS Directory
A.1.26 The scripts Directory
A.1.27 The server-tools Directory
A.1.28 The sql Directory
A.1.29 The sql-bench Directory
A.1.30 The sql-common Directory
A.1.31 The SSL Directory
A.1.32 The strings Directory
A.1.33 The support-files Directory
A.1.34 The tests Directory
A.1.35 The tools Directory
A.1.36 The VC++Files Directory
A.1.37 The vio Directory
A.1.38 The zlib Directory
B InnoDB Source Code Distribution
Index