MySQL 8.0 Reference Manual

Table of Contents

1 General Information
1.1 About This Manual
1.2 Typographical and Syntax Conventions
1.3 Overview of the MySQL Database Management System
1.3.1 What is MySQL?
1.3.2 The Main Features of MySQL
1.3.3 History of MySQL
1.4 What Is New in MySQL 8.0
1.5 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 8.0
1.6 MySQL Information Sources
1.6.1 MySQL Websites
1.6.2 MySQL Community Support at the MySQL Forums
1.6.3 MySQL Enterprise
1.7 How to Report Bugs or Problems
1.8 MySQL Standards Compliance
1.8.1 MySQL Extensions to Standard SQL
1.8.2 MySQL Differences from Standard SQL
1.8.3 How MySQL Deals with Constraints
1.9 Credits
1.9.1 Contributors to MySQL
1.9.2 Documenters and translators
1.9.3 Packages that support MySQL
1.9.4 Tools that were used to create MySQL
1.9.5 Supporters of MySQL
2 Installing and Upgrading MySQL
2.1 General Installation Guidance
2.1.1 Which MySQL Version and Distribution to Install
2.1.2 How to Get MySQL
2.1.3 Verifying Package Integrity Using MD5 Checksums or GnuPG
2.1.4 Installation Layouts
2.1.5 Compiler-Specific Build Characteristics
2.2 Installing MySQL on Unix/Linux Using Generic Binaries
2.3 Installing MySQL on Microsoft Windows
2.3.1 MySQL Installation Layout on Microsoft Windows
2.3.2 Choosing an Installation Package
2.3.3 MySQL Installer for Windows
2.3.4 Installing MySQL on Microsoft Windows Using a noinstall ZIP Archive
2.3.5 Troubleshooting a Microsoft Windows MySQL Server Installation
2.3.6 Windows Postinstallation Procedures
2.3.7 Windows Platform Restrictions
2.4 Installing MySQL on macOS
2.4.1 General Notes on Installing MySQL on macOS
2.4.2 Installing MySQL on macOS Using Native Packages
2.4.3 Installing and Using the MySQL Launch Daemon
2.4.4 Installing and Using the MySQL Preference Pane
2.5 Installing MySQL on Linux
2.5.1 Installing MySQL on Linux Using the MySQL Yum Repository
2.5.2 Installing MySQL on Linux Using the MySQL APT Repository
2.5.3 Installing MySQL on Linux Using the MySQL SLES Repository
2.5.4 Installing MySQL on Linux Using RPM Packages from Oracle
2.5.5 Installing MySQL on Linux Using Debian Packages from Oracle
2.5.6 Deploying MySQL on Linux with Docker
2.5.7 Installing MySQL on Linux from the Native Software Repositories
2.5.8 Installing MySQL on Linux with Juju
2.5.9 Managing MySQL Server with systemd
2.6 Installing MySQL Using Unbreakable Linux Network (ULN)
2.7 Installing MySQL on Solaris
2.7.1 Installing MySQL on Solaris Using a Solaris PKG
2.8 Installing MySQL on FreeBSD
2.9 Installing MySQL from Source
2.9.1 Source Installation Methods
2.9.2 Source Installation Prerequisites
2.9.3 MySQL Layout for Source Installation
2.9.4 Installing MySQL Using a Standard Source Distribution
2.9.5 Installing MySQL Using a Development Source Tree
2.9.6 Configuring SSL Library Support
2.9.7 MySQL Source-Configuration Options
2.9.8 Dealing with Problems Compiling MySQL
2.9.9 MySQL Configuration and Third-Party Tools
2.9.10 Generating MySQL Doxygen Documentation Content
2.10 Postinstallation Setup and Testing
2.10.1 Initializing the Data Directory
2.10.2 Starting the Server
2.10.3 Testing the Server
2.10.4 Securing the Initial MySQL Account
2.10.5 Starting and Stopping MySQL Automatically
2.11 Upgrading MySQL
2.11.1 Before You Begin
2.11.2 Upgrade Paths
2.11.3 What the MySQL Upgrade Process Upgrades
2.11.4 Changes in MySQL 8.0
2.11.5 Preparing Your Installation for Upgrade
2.11.6 Upgrading MySQL Binary or Package-based Installations on Unix/Linux
2.11.7 Upgrading MySQL with the MySQL Yum Repository
2.11.8 Upgrading MySQL with the MySQL APT Repository
2.11.9 Upgrading MySQL with the MySQL SLES Repository
2.11.10 Upgrading MySQL on Windows
2.11.11 Upgrading a Docker Installation of MySQL
2.11.12 Upgrade Troubleshooting
2.11.13 Rebuilding or Repairing Tables or Indexes
2.11.14 Copying MySQL Databases to Another Machine
2.12 Downgrading MySQL
2.13 Perl Installation Notes
2.13.1 Installing Perl on Unix
2.13.2 Installing ActiveState Perl on Windows
2.13.3 Problems Using the Perl DBI/DBD Interface
3 Tutorial
3.1 Connecting to and Disconnecting from the Server
3.2 Entering Queries
3.3 Creating and Using a Database
3.3.1 Creating and Selecting a Database
3.3.2 Creating a Table
3.3.3 Loading Data into a Table
3.3.4 Retrieving Information from a Table
3.4 Getting Information About Databases and Tables
3.5 Using mysql in Batch Mode
3.6 Examples of Common Queries
3.6.1 The Maximum Value for a Column
3.6.2 The Row Holding the Maximum of a Certain Column
3.6.3 Maximum of Column per Group
3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column
3.6.5 Using User-Defined Variables
3.6.6 Using Foreign Keys
3.6.7 Searching on Two Keys
3.6.8 Calculating Visits Per Day
3.6.9 Using AUTO_INCREMENT
3.7 Using MySQL with Apache
4 MySQL Programs
4.1 Overview of MySQL Programs
4.2 Using MySQL Programs
4.2.1 Invoking MySQL Programs
4.2.2 Specifying Program Options
4.2.3 Command Options for Connecting to the Server
4.2.4 Connecting to the MySQL Server Using Command Options
4.2.5 Connecting to the Server Using URI-Like Strings or Key-Value Pairs
4.2.6 Connection Compression Control
4.2.7 Setting Environment Variables
4.3 Server and Server-Startup Programs
4.3.1 mysqld — The MySQL Server
4.3.2 mysqld_safe — MySQL Server Startup Script
4.3.3 mysql.server — MySQL Server Startup Script
4.3.4 mysqld_multi — Manage Multiple MySQL Servers
4.4 Installation-Related Programs
4.4.1 comp_err — Compile MySQL Error Message File
4.4.2 mysql_secure_installation — Improve MySQL Installation Security
4.4.3 mysql_ssl_rsa_setup — Create SSL/RSA Files
4.4.4 mysql_tzinfo_to_sql — Load the Time Zone Tables
4.4.5 mysql_upgrade — Check and Upgrade MySQL Tables
4.5 Client Programs
4.5.1 mysql — The MySQL Command-Line Client
4.5.2 mysqladmin — Client for Administering a MySQL Server
4.5.3 mysqlcheck — A Table Maintenance Program
4.5.4 mysqldump — A Database Backup Program
4.5.5 mysqlimport — A Data Import Program
4.5.6 mysqlpump — A Database Backup Program
4.5.7 mysqlshow — Display Database, Table, and Column Information
4.5.8 mysqlslap — Load Emulation Client
4.6 Administrative and Utility Programs
4.6.1 ibd2sdi — InnoDB Tablespace SDI Extraction Utility
4.6.2 innochecksum — Offline InnoDB File Checksum Utility
4.6.3 myisam_ftdump — Display Full-Text Index information
4.6.4 myisamchk — MyISAM Table-Maintenance Utility
4.6.5 myisamlog — Display MyISAM Log File Contents
4.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables
4.6.7 mysql_config_editor — MySQL Configuration Utility
4.6.8 mysqlbinlog — Utility for Processing Binary Log Files
4.6.9 mysqldumpslow — Summarize Slow Query Log Files
4.7 Program Development Utilities
4.7.1 mysql_config — Display Options for Compiling Clients
4.7.2 my_print_defaults — Display Options from Option Files
4.8 Miscellaneous Programs
4.8.1 lz4_decompress — Decompress mysqlpump LZ4-Compressed Output
4.8.2 perror — Display MySQL Error Message Information
4.8.3 zlib_decompress — Decompress mysqlpump ZLIB-Compressed Output
4.9 Environment Variables
4.10 Unix Signal Handling in MySQL
5 MySQL Server Administration
5.1 The MySQL Server
5.1.1 Configuring the Server
5.1.2 Server Configuration Defaults
5.1.3 Server Configuration Validation
5.1.4 Server Option, System Variable, and Status Variable Reference
5.1.5 Server System Variable Reference
5.1.6 Server Status Variable Reference
5.1.7 Server Command Options
5.1.8 Server System Variables
5.1.9 Using System Variables
5.1.10 Server Status Variables
5.1.11 Server SQL Modes
5.1.12 IPv6 Support
5.1.13 MySQL Server Time Zone Support
5.1.14 Server-Side Help Support
5.1.15 Server Tracking of Client Session State Changes
5.1.16 The Server Shutdown Process
5.2 The MySQL Data Directory
5.3 The mysql System Schema
5.4 MySQL Server Logs
5.4.1 Selecting General Query Log and Slow Query Log Output Destinations
5.4.2 The Error Log
5.4.3 The General Query Log
5.4.4 The Binary Log
5.4.5 The Slow Query Log
5.4.6 The DDL Log
5.4.7 Server Log Maintenance
5.5 MySQL Server Components
5.5.1 Installing and Uninstalling Components
5.5.2 Obtaining Server Component Information
5.5.3 Error Log Components
5.6 MySQL Server Plugins
5.6.1 Installing and Uninstalling Plugins
5.6.2 Obtaining Server Plugin Information
5.6.3 MySQL Enterprise Thread Pool
5.6.4 The Rewriter Query Rewrite Plugin
5.6.5 The ddl_rewriter Plugin
5.6.6 Version Tokens
5.6.7 The Clone Plugin
5.7 MySQL Server User-Defined Functions
5.7.1 Installing and Uninstalling User-Defined Functions
5.7.2 Obtaining User-Defined Function Information
5.8 Running Multiple MySQL Instances on One Machine
5.8.1 Setting Up Multiple Data Directories
5.8.2 Running Multiple MySQL Instances on Windows
5.8.3 Running Multiple MySQL Instances on Unix
5.8.4 Using Client Programs in a Multiple-Server Environment
6 Security
6.1 General Security Issues
6.1.1 Security Guidelines
6.1.2 Keeping Passwords Secure
6.1.3 Making MySQL Secure Against Attackers
6.1.4 Security-Related mysqld Options and Variables
6.1.5 How to Run MySQL as a Normal User
6.1.6 Security Issues with LOAD DATA LOCAL
6.1.7 Client Programming Security Guidelines
6.2 Access Control and Account Management
6.2.1 Account User Names and Passwords
6.2.2 Privileges Provided by MySQL
6.2.3 Grant Tables
6.2.4 Specifying Account Names
6.2.5 Specifying Role Names
6.2.6 Access Control, Stage 1: Connection Verification
6.2.7 Access Control, Stage 2: Request Verification
6.2.8 Adding Accounts, Assigning Privileges, and Dropping Accounts
6.2.9 Reserved Accounts
6.2.10 Using Roles
6.2.11 Account Categories
6.2.12 Privilege Restriction Using Partial Revokes
6.2.13 When Privilege Changes Take Effect
6.2.14 Assigning Account Passwords
6.2.15 Password Management
6.2.16 Server Handling of Expired Passwords
6.2.17 Pluggable Authentication
6.2.18 Proxy Users
6.2.19 Account Locking
6.2.20 Setting Account Resource Limits
6.2.21 Troubleshooting Problems Connecting to MySQL
6.2.22 SQL-Based Account Activity Auditing
6.3 Using Encrypted Connections
6.3.1 Configuring MySQL to Use Encrypted Connections
6.3.2 Encrypted Connection TLS Protocols and Ciphers
6.3.3 Creating SSL and RSA Certificates and Keys
6.3.4 Connecting to MySQL Remotely from Windows with SSH
6.4 Security Components and Plugins
6.4.1 Authentication Plugins
6.4.2 The Connection-Control Plugins
6.4.3 The Password Validation Component
6.4.4 The MySQL Keyring
6.4.5 MySQL Enterprise Audit
6.4.6 The Audit Message Component
6.4.7 MySQL Enterprise Firewall
6.4.8 MySQL Enterprise Data Masking and De-Identification
6.5 FIPS Support
7 Backup and Recovery
7.1 Backup and Recovery Types
7.2 Database Backup Methods
7.3 Example Backup and Recovery Strategy
7.3.1 Establishing a Backup Policy
7.3.2 Using Backups for Recovery
7.3.3 Backup Strategy Summary
7.4 Using mysqldump for Backups
7.4.1 Dumping Data in SQL Format with mysqldump
7.4.2 Reloading SQL-Format Backups
7.4.3 Dumping Data in Delimited-Text Format with mysqldump
7.4.4 Reloading Delimited-Text Format Backups
7.4.5 mysqldump Tips
7.5 Point-in-Time (Incremental) Recovery Using the Binary Log
7.5.1 Point-in-Time Recovery Using Event Times
7.5.2 Point-in-Time Recovery Using Event Positions
7.6 MyISAM Table Maintenance and Crash Recovery
7.6.1 Using myisamchk for Crash Recovery
7.6.2 How to Check MyISAM Tables for Errors
7.6.3 How to Repair MyISAM Tables
7.6.4 MyISAM Table Optimization
7.6.5 Setting Up a MyISAM Table Maintenance Schedule
8 Optimization
8.1 Optimization Overview
8.2 Optimizing SQL Statements
8.2.1 Optimizing SELECT Statements
8.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions
8.2.3 Optimizing INFORMATION_SCHEMA Queries
8.2.4 Optimizing Performance Schema Queries
8.2.5 Optimizing Data Change Statements
8.2.6 Optimizing Database Privileges
8.2.7 Other Optimization Tips
8.3 Optimization and Indexes
8.3.1 How MySQL Uses Indexes
8.3.2 Primary Key Optimization
8.3.3 SPATIAL Index Optimization
8.3.4 Foreign Key Optimization
8.3.5 Column Indexes
8.3.6 Multiple-Column Indexes
8.3.7 Verifying Index Usage
8.3.8 InnoDB and MyISAM Index Statistics Collection
8.3.9 Comparison of B-Tree and Hash Indexes
8.3.10 Use of Index Extensions
8.3.11 Optimizer Use of Generated Column Indexes
8.3.12 Invisible Indexes
8.3.13 Descending Indexes
8.3.14 Indexed Lookups from TIMESTAMP Columns
8.4 Optimizing Database Structure
8.4.1 Optimizing Data Size
8.4.2 Optimizing MySQL Data Types
8.4.3 Optimizing for Many Tables
8.4.4 Internal Temporary Table Use in MySQL
8.4.5 Limits on Number of Databases and Tables
8.4.6 Limits on Table Size
8.4.7 Limits on Table Column Count and Row Size
8.5 Optimizing for InnoDB Tables
8.5.1 Optimizing Storage Layout for InnoDB Tables
8.5.2 Optimizing InnoDB Transaction Management
8.5.3 Optimizing InnoDB Read-Only Transactions
8.5.4 Optimizing InnoDB Redo Logging
8.5.5 Bulk Data Loading for InnoDB Tables
8.5.6 Optimizing InnoDB Queries
8.5.7 Optimizing InnoDB DDL Operations
8.5.8 Optimizing InnoDB Disk I/O
8.5.9 Optimizing InnoDB Configuration Variables
8.5.10 Optimizing InnoDB for Systems with Many Tables
8.6 Optimizing for MyISAM Tables
8.6.1 Optimizing MyISAM Queries
8.6.2 Bulk Data Loading for MyISAM Tables
8.6.3 Optimizing REPAIR TABLE Statements
8.7 Optimizing for MEMORY Tables
8.8 Understanding the Query Execution Plan
8.8.1 Optimizing Queries with EXPLAIN
8.8.2 EXPLAIN Output Format
8.8.3 Extended EXPLAIN Output Format
8.8.4 Obtaining Execution Plan Information for a Named Connection
8.8.5 Estimating Query Performance
8.9 Controlling the Query Optimizer
8.9.1 Controlling Query Plan Evaluation
8.9.2 Switchable Optimizations
8.9.3 Optimizer Hints
8.9.4 Index Hints
8.9.5 The Optimizer Cost Model
8.9.6 Optimizer Statistics
8.10 Buffering and Caching
8.10.1 InnoDB Buffer Pool Optimization
8.10.2 The MyISAM Key Cache
8.10.3 Caching of Prepared Statements and Stored Programs
8.11 Optimizing Locking Operations
8.11.1 Internal Locking Methods
8.11.2 Table Locking Issues
8.11.3 Concurrent Inserts
8.11.4 Metadata Locking
8.11.5 External Locking
8.12 Optimizing the MySQL Server
8.12.1 Optimizing Disk I/O
8.12.2 Using Symbolic Links
8.12.3 Optimizing Memory Use
8.12.4 Optimizing Network Use
8.12.5 Resource Groups
8.13 Measuring Performance (Benchmarking)
8.13.1 Measuring the Speed of Expressions and Functions
8.13.2 Using Your Own Benchmarks
8.13.3 Measuring Performance with performance_schema
8.14 Examining Thread Information
8.14.1 Thread Command Values
8.14.2 General Thread States
8.14.3 Replication Master Thread States
8.14.4 Replication Slave I/O Thread States
8.14.5 Replication Slave SQL Thread States
8.14.6 Replication Slave Connection Thread States
8.14.7 NDB Cluster Thread States
8.14.8 Event Scheduler Thread States
9 Language Structure
9.1 Literal Values
9.1.1 String Literals
9.1.2 Numeric Literals
9.1.3 Date and Time Literals
9.1.4 Hexadecimal Literals
9.1.5 Bit-Value Literals
9.1.6 Boolean Literals
9.1.7 NULL Values
9.2 Schema Object Names
9.2.1 Identifier Length Limits
9.2.2 Identifier Qualifiers
9.2.3 Identifier Case Sensitivity
9.2.4 Mapping of Identifiers to File Names
9.2.5 Function Name Parsing and Resolution
9.3 Keywords and Reserved Words
9.4 User-Defined Variables
9.5 Expressions
9.6 Comment Syntax
10 Character Sets, Collations, Unicode
10.1 Character Sets and Collations in General
10.2 Character Sets and Collations in MySQL
10.2.1 Character Set Repertoire
10.2.2 UTF-8 for Metadata
10.3 Specifying Character Sets and Collations
10.3.1 Collation Naming Conventions
10.3.2 Server Character Set and Collation
10.3.3 Database Character Set and Collation
10.3.4 Table Character Set and Collation
10.3.5 Column Character Set and Collation
10.3.6 Character String Literal Character Set and Collation
10.3.7 The National Character Set
10.3.8 Character Set Introducers
10.3.9 Examples of Character Set and Collation Assignment
10.3.10 Compatibility with Other DBMSs
10.4 Connection Character Sets and Collations
10.5 Configuring Application Character Set and Collation
10.6 Error Message Character Set
10.7 Column Character Set Conversion
10.8 Collation Issues
10.8.1 Using COLLATE in SQL Statements
10.8.2 COLLATE Clause Precedence
10.8.3 Character Set and Collation Compatibility
10.8.4 Collation Coercibility in Expressions
10.8.5 The binary Collation Compared to _bin Collations
10.8.6 Examples of the Effect of Collation
10.8.7 Using Collation in INFORMATION_SCHEMA Searches
10.9 Unicode Support
10.9.1 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)
10.9.2 The utf8mb3 Character Set (3-Byte UTF-8 Unicode Encoding)
10.9.3 The utf8 Character Set (Alias for utf8mb3)
10.9.4 The ucs2 Character Set (UCS-2 Unicode Encoding)
10.9.5 The utf16 Character Set (UTF-16 Unicode Encoding)
10.9.6 The utf16le Character Set (UTF-16LE Unicode Encoding)
10.9.7 The utf32 Character Set (UTF-32 Unicode Encoding)
10.9.8 Converting Between 3-Byte and 4-Byte Unicode Character Sets
10.10 Supported Character Sets and Collations
10.10.1 Unicode Character Sets
10.10.2 West European Character Sets
10.10.3 Central European Character Sets
10.10.4 South European and Middle East Character Sets
10.10.5 Baltic Character Sets
10.10.6 Cyrillic Character Sets
10.10.7 Asian Character Sets
10.10.8 The Binary Character Set
10.11 Restrictions on Character Sets
10.12 Setting the Error Message Language
10.13 Adding a Character Set
10.13.1 Character Definition Arrays
10.13.2 String Collating Support for Complex Character Sets
10.13.3 Multi-Byte Character Support for Complex Character Sets
10.14 Adding a Collation to a Character Set
10.14.1 Collation Implementation Types
10.14.2 Choosing a Collation ID
10.14.3 Adding a Simple Collation to an 8-Bit Character Set
10.14.4 Adding a UCA Collation to a Unicode Character Set
10.15 Character Set Configuration
10.16 MySQL Server Locale Support
11 Data Types
11.1 Numeric Data Types
11.1.1 Numeric Data Type Syntax
11.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
11.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC
11.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE
11.1.5 Bit-Value Type - BIT
11.1.6 Numeric Type Attributes
11.1.7 Out-of-Range and Overflow Handling
11.2 Date and Time Data Types
11.2.1 Date and Time Data Type Syntax
11.2.2 The DATE, DATETIME, and TIMESTAMP Types
11.2.3 The TIME Type
11.2.4 The YEAR Type
11.2.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME
11.2.6 Fractional Seconds in Time Values
11.2.7 Conversion Between Date and Time Types
11.2.8 2-Digit Years in Dates
11.3 String Data Types
11.3.1 String Data Type Syntax
11.3.2 The CHAR and VARCHAR Types
11.3.3 The BINARY and VARBINARY Types
11.3.4 The BLOB and TEXT Types
11.3.5 The ENUM Type
11.3.6 The SET Type
11.4 Spatial Data Types
11.4.1 Spatial Data Types
11.4.2 The OpenGIS Geometry Model
11.4.3 Supported Spatial Data Formats
11.4.4 Geometry Well-Formedness and Validity
11.4.5 Spatial Reference System Support
11.4.6 Creating Spatial Columns
11.4.7 Populating Spatial Columns
11.4.8 Fetching Spatial Data
11.4.9 Optimizing Spatial Analysis
11.4.10 Creating Spatial Indexes
11.4.11 Using Spatial Indexes
11.5 The JSON Data Type
11.6 Data Type Default Values
11.7 Data Type Storage Requirements
11.8 Choosing the Right Type for a Column
11.9 Using Data Types from Other Database Engines
12 Functions and Operators
12.1 Function and Operator Reference
12.2 Type Conversion in Expression Evaluation
12.3 Operators
12.3.1 Operator Precedence
12.3.2 Comparison Functions and Operators
12.3.3 Logical Operators
12.3.4 Assignment Operators
12.4 Control Flow Functions
12.5 Numeric Functions and Operators
12.5.1 Arithmetic Operators
12.5.2 Mathematical Functions
12.6 Date and Time Functions
12.7 String Functions and Operators
12.7.1 String Comparison Functions and Operators
12.7.2 Regular Expressions
12.7.3 Character Set and Collation of Function Results
12.8 What Calendar Is Used By MySQL?
12.9 Full-Text Search Functions
12.9.1 Natural Language Full-Text Searches
12.9.2 Boolean Full-Text Searches
12.9.3 Full-Text Searches with Query Expansion
12.9.4 Full-Text Stopwords
12.9.5 Full-Text Restrictions
12.9.6 Fine-Tuning MySQL Full-Text Search
12.9.7 Adding a Collation for Full-Text Indexing
12.9.8 ngram Full-Text Parser
12.9.9 MeCab Full-Text Parser Plugin
12.10 Cast Functions and Operators
12.11 XML Functions
12.12 Bit Functions and Operators
12.13 Encryption and Compression Functions
12.14 Locking Functions
12.15 Information Functions
12.16 Spatial Analysis Functions
12.16.1 Spatial Function Reference
12.16.2 Argument Handling by Spatial Functions
12.16.3 Functions That Create Geometry Values from WKT Values
12.16.4 Functions That Create Geometry Values from WKB Values
12.16.5 MySQL-Specific Functions That Create Geometry Values
12.16.6 Geometry Format Conversion Functions
12.16.7 Geometry Property Functions
12.16.8 Spatial Operator Functions
12.16.9 Functions That Test Spatial Relations Between Geometry Objects
12.16.10 Spatial Geohash Functions
12.16.11 Spatial GeoJSON Functions
12.16.12 Spatial Convenience Functions
12.17 JSON Functions
12.17.1 JSON Function Reference
12.17.2 Functions That Create JSON Values
12.17.3 Functions That Search JSON Values
12.17.4 Functions That Modify JSON Values
12.17.5 Functions That Return JSON Value Attributes
12.17.6 JSON Table Functions
12.17.7 JSON Schema Validation Functions
12.17.8 JSON Utility Functions
12.18 Functions Used with Global Transaction Identifiers (GTIDs)
12.19 MySQL Enterprise Encryption Functions
12.19.1 MySQL Enterprise Encryption Installation
12.19.2 MySQL Enterprise Encryption Usage and Examples
12.19.3 MySQL Enterprise Encryption Function Reference
12.19.4 MySQL Enterprise Encryption Function Descriptions
12.20 Aggregate (GROUP BY) Functions
12.20.1 Aggregate (GROUP BY) Function Descriptions
12.20.2 GROUP BY Modifiers
12.20.3 MySQL Handling of GROUP BY
12.20.4 Detection of Functional Dependence
12.21 Window Functions
12.21.1 Window Function Descriptions
12.21.2 Window Function Concepts and Syntax
12.21.3 Window Function Frame Specification
12.21.4 Named Windows
12.21.5 Window Function Restrictions
12.22 Performance Schema Functions
12.23 Internal Functions
12.24 Miscellaneous Functions
12.25 Precision Math
12.25.1 Types of Numeric Values
12.25.2 DECIMAL Data Type Characteristics
12.25.3 Expression Handling
12.25.4 Rounding Behavior
12.25.5 Precision Math Examples
13 SQL Statements
13.1 Data Definition Statements
13.1.1 Atomic Data Definition Statement Support
13.1.2 ALTER DATABASE Statement
13.1.3 ALTER EVENT Statement
13.1.4 ALTER FUNCTION Statement
13.1.5 ALTER INSTANCE Statement
13.1.6 ALTER LOGFILE GROUP Statement
13.1.7 ALTER PROCEDURE Statement
13.1.8 ALTER SERVER Statement
13.1.9 ALTER TABLE Statement
13.1.10 ALTER TABLESPACE Statement
13.1.11 ALTER VIEW Statement
13.1.12 CREATE DATABASE Statement
13.1.13 CREATE EVENT Statement
13.1.14 CREATE FUNCTION Statement
13.1.15 CREATE INDEX Statement
13.1.16 CREATE LOGFILE GROUP Statement
13.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements
13.1.18 CREATE SERVER Statement
13.1.19 CREATE SPATIAL REFERENCE SYSTEM Statement
13.1.20 CREATE TABLE Statement
13.1.21 CREATE TABLESPACE Statement
13.1.22 CREATE TRIGGER Statement
13.1.23 CREATE VIEW Statement
13.1.24 DROP DATABASE Statement
13.1.25 DROP EVENT Statement
13.1.26 DROP FUNCTION Statement
13.1.27 DROP INDEX Statement
13.1.28 DROP LOGFILE GROUP Statement
13.1.29 DROP PROCEDURE and DROP FUNCTION Statements
13.1.30 DROP SERVER Statement
13.1.31 DROP SPATIAL REFERENCE SYSTEM Statement
13.1.32 DROP TABLE Statement
13.1.33 DROP TABLESPACE Statement
13.1.34 DROP TRIGGER Statement
13.1.35 DROP VIEW Statement
13.1.36 RENAME TABLE Statement
13.1.37 TRUNCATE TABLE Statement
13.2 Data Manipulation Statements
13.2.1 CALL Statement
13.2.2 DELETE Statement
13.2.3 DO Statement
13.2.4 HANDLER Statement
13.2.5 IMPORT TABLE Statement
13.2.6 INSERT Statement
13.2.7 LOAD DATA Statement
13.2.8 LOAD XML Statement
13.2.9 REPLACE Statement
13.2.10 SELECT Statement
13.2.11 Subqueries
13.2.12 TABLE Statement
13.2.13 UPDATE Statement
13.2.14 VALUES Statement
13.2.15 WITH (Common Table Expressions)
13.3 Transactional and Locking Statements
13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements
13.3.2 Statements That Cannot Be Rolled Back
13.3.3 Statements That Cause an Implicit Commit
13.3.4 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements
13.3.5 LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Statements
13.3.6 LOCK TABLES and UNLOCK TABLES Statements
13.3.7 SET TRANSACTION Statement
13.3.8 XA Transactions
13.4 Replication Statements
13.4.1 SQL Statements for Controlling Master Servers
13.4.2 SQL Statements for Controlling Slave Servers
13.4.3 SQL Statements for Controlling Group Replication
13.5 Prepared Statements
13.5.1 PREPARE Statement
13.5.2 EXECUTE Statement
13.5.3 DEALLOCATE PREPARE Statement
13.6 Compound Statement Syntax
13.6.1 BEGIN ... END Compound Statement
13.6.2 Statement Labels
13.6.3 DECLARE Statement
13.6.4 Variables in Stored Programs
13.6.5 Flow Control Statements
13.6.6 Cursors
13.6.7 Condition Handling
13.6.8 Restrictions on Condition Handling
13.7 Database Administration Statements
13.7.1 Account Management Statements
13.7.2 Resource Group Management Statements
13.7.3 Table Maintenance Statements
13.7.4 Component, Plugin, and User-Defined Function Statements
13.7.5 CLONE Statement
13.7.6 SET Statements
13.7.7 SHOW Statements
13.7.8 Other Administrative Statements
13.8 Utility Statements
13.8.1 DESCRIBE Statement
13.8.2 EXPLAIN Statement
13.8.3 HELP Statement
13.8.4 USE Statement
14 MySQL Data Dictionary
14.1 Data Dictionary Schema
14.2 Removal of File-based Metadata Storage
14.3 Transactional Storage of Dictionary Data
14.4 Dictionary Object Cache
14.5 INFORMATION_SCHEMA and Data Dictionary Integration
14.6 Serialized Dictionary Information (SDI)
14.7 Data Dictionary Usage Differences
14.8 Data Dictionary Limitations
15 The InnoDB Storage Engine
15.1 Introduction to InnoDB
15.1.1 Benefits of Using InnoDB Tables
15.1.2 Best Practices for InnoDB Tables
15.1.3 Verifying that InnoDB is the Default Storage Engine
15.1.4 Testing and Benchmarking with InnoDB
15.2 InnoDB and the ACID Model
15.3 InnoDB Multi-Versioning
15.4 InnoDB Architecture
15.5 InnoDB In-Memory Structures
15.5.1 Buffer Pool
15.5.2 Change Buffer
15.5.3 Adaptive Hash Index
15.5.4 Log Buffer
15.6 InnoDB On-Disk Structures
15.6.1 Tables
15.6.2 Indexes
15.6.3 Tablespaces
15.6.4 Doublewrite Buffer
15.6.5 Redo Log
15.6.6 Undo Logs
15.7 InnoDB Locking and Transaction Model
15.7.1 InnoDB Locking
15.7.2 InnoDB Transaction Model
15.7.3 Locks Set by Different SQL Statements in InnoDB
15.7.4 Phantom Rows
15.7.5 Deadlocks in InnoDB
15.8 InnoDB Configuration
15.8.1 InnoDB Startup Configuration
15.8.2 Configuring InnoDB for Read-Only Operation
15.8.3 InnoDB Buffer Pool Configuration
15.8.4 Configuring Thread Concurrency for InnoDB
15.8.5 Configuring the Number of Background InnoDB I/O Threads
15.8.6 Using Asynchronous I/O on Linux
15.8.7 Configuring InnoDB I/O Capacity
15.8.8 Configuring Spin Lock Polling
15.8.9 Purge Configuration
15.8.10 Configuring Optimizer Statistics for InnoDB
15.8.11 Configuring the Merge Threshold for Index Pages
15.8.12 Enabling Automatic Configuration for a Dedicated MySQL Server
15.9 InnoDB Table and Page Compression
15.9.1 InnoDB Table Compression
15.9.2 InnoDB Page Compression
15.10 InnoDB Row Formats
15.11 InnoDB Disk I/O and File Space Management
15.11.1 InnoDB Disk I/O
15.11.2 File Space Management
15.11.3 InnoDB Checkpoints
15.11.4 Defragmenting a Table
15.11.5 Reclaiming Disk Space with TRUNCATE TABLE
15.12 InnoDB and Online DDL
15.12.1 Online DDL Operations
15.12.2 Online DDL Performance and Concurrency
15.12.3 Online DDL Space Requirements
15.12.4 Simplifying DDL Statements with Online DDL
15.12.5 Online DDL Failure Conditions
15.12.6 Online DDL Limitations
15.13 InnoDB Data-at-Rest Encryption
15.14 InnoDB Startup Options and System Variables
15.15 InnoDB INFORMATION_SCHEMA Tables
15.15.1 InnoDB INFORMATION_SCHEMA Tables about Compression
15.15.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Information
15.15.3 InnoDB INFORMATION_SCHEMA Schema Object Tables
15.15.4 InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables
15.15.5 InnoDB INFORMATION_SCHEMA Buffer Pool Tables
15.15.6 InnoDB INFORMATION_SCHEMA Metrics Table
15.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table
15.15.8 Retrieving InnoDB Tablespace Metadata from INFORMATION_SCHEMA.FILES
15.16 InnoDB Integration with MySQL Performance Schema
15.16.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema
15.16.2 Monitoring InnoDB Mutex Waits Using Performance Schema
15.17 InnoDB Monitors
15.17.1 InnoDB Monitor Types
15.17.2 Enabling InnoDB Monitors
15.17.3 InnoDB Standard Monitor and Lock Monitor Output
15.18 InnoDB Backup and Recovery
15.18.1 InnoDB Backup
15.18.2 InnoDB Recovery
15.19 InnoDB and MySQL Replication
15.20 InnoDB memcached Plugin
15.20.1 Benefits of the InnoDB memcached Plugin
15.20.2 InnoDB memcached Architecture
15.20.3 Setting Up the InnoDB memcached Plugin
15.20.4 InnoDB memcached Multiple get and Range Query Support
15.20.5 Security Considerations for the InnoDB memcached Plugin
15.20.6 Writing Applications for the InnoDB memcached Plugin
15.20.7 The InnoDB memcached Plugin and Replication
15.20.8 InnoDB memcached Plugin Internals
15.20.9 Troubleshooting the InnoDB memcached Plugin
15.21 InnoDB Troubleshooting
15.21.1 Troubleshooting InnoDB I/O Problems
15.21.2 Forcing InnoDB Recovery
15.21.3 Troubleshooting InnoDB Data Dictionary Operations
15.21.4 InnoDB Error Handling
15.22 InnoDB Limits
15.23 InnoDB Restrictions and Limitations
16 Alternative Storage Engines
16.1 Setting the Storage Engine
16.2 The MyISAM Storage Engine
16.2.1 MyISAM Startup Options
16.2.2 Space Needed for Keys
16.2.3 MyISAM Table Storage Formats
16.2.4 MyISAM Table Problems
16.3 The MEMORY Storage Engine
16.4 The CSV Storage Engine
16.4.1 Repairing and Checking CSV Tables
16.4.2 CSV Limitations
16.5 The ARCHIVE Storage Engine
16.6 The BLACKHOLE Storage Engine
16.7 The MERGE Storage Engine
16.7.1 MERGE Table Advantages and Disadvantages
16.7.2 MERGE Table Problems
16.8 The FEDERATED Storage Engine
16.8.1 FEDERATED Storage Engine Overview
16.8.2 How to Create FEDERATED Tables
16.8.3 FEDERATED Storage Engine Notes and Tips
16.8.4 FEDERATED Storage Engine Resources
16.9 The EXAMPLE Storage Engine
16.10 Other Storage Engines
16.11 Overview of MySQL Storage Engine Architecture
16.11.1 Pluggable Storage Engine Architecture
16.11.2 The Common Database Server Layer
17 Replication
17.1 Configuring Replication
17.1.1 Binary Log File Position Based Replication Configuration Overview
17.1.2 Setting Up Binary Log File Position Based Replication
17.1.3 Replication with Global Transaction Identifiers
17.1.4 MySQL Multi-Source Replication
17.1.5 Changing Replication Modes on Online Servers
17.1.6 Replication and Binary Logging Options and Variables
17.1.7 Common Replication Administration Tasks
17.2 Replication Implementation
17.2.1 Replication Formats
17.2.2 Replication Implementation Details
17.2.3 Replication Channels
17.2.4 Replication Relay and Status Logs
17.2.5 How Servers Evaluate Replication Filtering Rules
17.3 Replication Security
17.3.1 Setting Up Replication to Use Encrypted Connections
17.3.2 Encrypting Binary Log Files and Relay Log Files
17.3.3 Replication Privilege Checks
17.4 Replication Solutions
17.4.1 Using Replication for Backups
17.4.2 Handling an Unexpected Halt of a Replication Slave
17.4.3 Monitoring Row-based Replication
17.4.4 Using Replication with Different Master and Slave Storage Engines
17.4.5 Using Replication for Scale-Out
17.4.6 Replicating Different Databases to Different Slaves
17.4.7 Improving Replication Performance
17.4.8 Switching Masters During Failover
17.4.9 Semisynchronous Replication
17.4.10 Delayed Replication
17.5 Replication Notes and Tips
17.5.1 Replication Features and Issues
17.5.2 Replication Compatibility Between MySQL Versions
17.5.3 Upgrading a Replication Setup
17.5.4 Troubleshooting Replication
17.5.5 How to Report Replication Bugs or Problems
18 Group Replication
18.1 Group Replication Background
18.1.1 Replication Technologies
18.1.2 Group Replication Use Cases
18.1.3 Multi-Primary and Single-Primary Modes
18.1.4 Group Replication Services
18.1.5 Group Replication Plugin Architecture
18.2 Getting Started
18.2.1 Deploying Group Replication in Single-Primary Mode
18.2.2 Deploying Group Replication Locally
18.3 Monitoring Group Replication
18.3.1 Group Replication Server States
18.3.2 The replication_group_members Table
18.3.3 The replication_group_member_stats Table
18.4 Group Replication Operations
18.4.1 Configuring an Online Group
18.4.2 Transaction Consistency Guarantees
18.4.3 Distributed Recovery
18.4.4 Network Partitioning
18.4.5 Support For IPv6 And For Mixed IPv6 And IPv4 Groups
18.4.6 Using MySQL Enterprise Backup with Group Replication
18.5 Group Replication Security
18.5.1 Group Replication IP Address Whitelisting
18.5.2 Group Replication Secure Socket Layer (SSL) Support
18.6 Group Replication Performance
18.6.1 Fine Tuning the Group Communication Thread
18.6.2 Flow Control
18.6.3 Message Compression
18.6.4 Message Fragmentation
18.6.5 XCom Cache Management
18.6.6 Responses to Failure Detection and Network Partitioning
18.7 Upgrading Group Replication
18.7.1 Combining Different Member Versions in a Group
18.7.2 Group Replication Offline Upgrade
18.7.3 Group Replication Online Upgrade
18.8 Group Replication System Variables
18.9 Requirements and Limitations
18.9.1 Group Replication Requirements
18.9.2 Group Replication Limitations
18.10 Frequently Asked Questions
19 MySQL Shell
20 Using MySQL as a Document Store
20.1 Interfaces to a MySQL Document Store
20.2 Document Store Concepts
20.3 JavaScript Quick-Start Guide: MySQL Shell for Document Store
20.3.1 MySQL Shell
20.3.2 Download and Import world_x Database
20.3.3 Documents and Collections
20.3.4 Relational Tables
20.3.5 Documents in Tables
20.4 Python Quick-Start Guide: MySQL Shell for Document Store
20.4.1 MySQL Shell
20.4.2 Download and Import world_x Database
20.4.3 Documents and Collections
20.4.4 Relational Tables
20.4.5 Documents in Tables
20.5 X Plugin
20.5.1 Checking X Plugin Installation
20.5.2 Disabling X Plugin
20.5.3 Using Secure Connections with X Plugin
20.5.4 Using X Plugin with the Caching SHA-2 Authentication Plugin
20.5.5 Connection Compression with X Plugin
20.5.6 X Plugin Options and Variables
20.5.7 Monitoring X Plugin
21 InnoDB Cluster
21.1 Introducing InnoDB Cluster
21.2 Creating an InnoDB Cluster
21.2.1 Deployment Scenarios
21.2.2 InnoDB Cluster Requirements
21.2.3 Methods of Installing
21.2.4 Production Deployment of InnoDB Cluster
21.2.5 Using MySQL Clone with InnoDB cluster
21.2.6 Sandbox Deployment of InnoDB Cluster
21.2.7 Adopting a Group Replication Deployment
21.3 Upgrading an InnoDB cluster
21.3.1 Rolling Upgrades
21.3.2 Upgrading InnoDB cluster Metadata
21.3.3 Troubleshooting InnoDB cluster Upgrades
21.4 Using MySQL Router with InnoDB Cluster
21.5 Working with InnoDB Cluster
21.6 InnoDB ReplicaSet
21.6.1 InnoDB ReplicaSet Introduction
21.6.2 Deploying InnoDB ReplicaSet
21.6.3 Adding Instances to a Replica Set
21.6.4 Adopting an Existing Replication Set Up
21.6.5 Working with InnoDB ReplicaSet
21.6.6 Using Replica Sets with MySQL Router
21.7 Known Limitations
22 MySQL NDB Cluster 8.0
22.1 NDB Cluster Overview
22.1.1 NDB Cluster Core Concepts
22.1.2 NDB Cluster Nodes, Node Groups, Replicas, and Partitions
22.1.3 NDB Cluster Hardware, Software, and Networking Requirements
22.1.4 What is New in NDB Cluster
22.1.5 Options, Variables, and Parameters Added, Deprecated or Removed in NDB 8.0
22.1.6 MySQL Server Using InnoDB Compared with NDB Cluster
22.1.7 Known Limitations of NDB Cluster
22.2 NDB Cluster Installation
22.2.1 The NDB Cluster Auto-Installer
22.2.2 Installation of NDB Cluster on Linux
22.2.3 Installing NDB Cluster on Windows
22.2.4 Initial Configuration of NDB Cluster
22.2.5 Initial Startup of NDB Cluster
22.2.6 NDB Cluster Example with Tables and Data
22.2.7 Safe Shutdown and Restart of NDB Cluster
22.2.8 Upgrading and Downgrading NDB Cluster
22.3 Configuration of NDB Cluster
22.3.1 Quick Test Setup of NDB Cluster
22.3.2 Overview of NDB Cluster Configuration Parameters, Options, and Variables
22.3.3 NDB Cluster Configuration Files
22.3.4 Using High-Speed Interconnects with NDB Cluster
22.4 NDB Cluster Programs
22.4.1 ndbd — The NDB Cluster Data Node Daemon
22.4.2 ndbinfo_select_all — Select From ndbinfo Tables
22.4.3 ndbmtd — The NDB Cluster Data Node Daemon (Multi-Threaded)
22.4.4 ndb_mgmd — The NDB Cluster Management Server Daemon
22.4.5 ndb_mgm — The NDB Cluster Management Client
22.4.6 ndb_blob_tool — Check and Repair BLOB and TEXT columns of NDB Cluster Tables
22.4.7 ndb_config — Extract NDB Cluster Configuration Information
22.4.8 ndb_delete_all — Delete All Rows from an NDB Table
22.4.9 ndb_desc — Describe NDB Tables
22.4.10 ndb_drop_index — Drop Index from an NDB Table
22.4.11 ndb_drop_table — Drop an NDB Table
22.4.12 ndb_error_reporter — NDB Error-Reporting Utility
22.4.13 ndb_import — Import CSV Data Into NDB
22.4.14 ndb_index_stat — NDB Index Statistics Utility
22.4.15 ndb_move_data — NDB Data Copy Utility
22.4.16 ndb_perror — Obtain NDB Error Message Information
22.4.17 ndb_print_backup_file — Print NDB Backup File Contents
22.4.18 ndb_print_file — Print NDB Disk Data File Contents
22.4.19 ndb_print_frag_file — Print NDB Fragment List File Contents
22.4.20 ndb_print_schema_file — Print NDB Schema File Contents
22.4.21 ndb_print_sys_file — Print NDB System File Contents
22.4.22 ndb_redo_log_reader — Check and Print Content of Cluster Redo Log
22.4.23 ndb_restore — Restore an NDB Cluster Backup
22.4.24 ndb_select_all — Print Rows from an NDB Table
22.4.25 ndb_select_count — Print Row Counts for NDB Tables
22.4.26 ndb_setup.py — Start browser-based Auto-Installer for NDB Cluster
22.4.27 ndb_show_tables — Display List of NDB Tables
22.4.28 ndb_size.pl — NDBCLUSTER Size Requirement Estimator
22.4.29 ndb_top — View CPU usage information for NDB threads
22.4.30 ndb_waiter — Wait for NDB Cluster to Reach a Given Status
22.4.31 Options Common to NDB Cluster Programs — Options Common to NDB Cluster Programs
22.5 Management of NDB Cluster
22.5.1 Summary of NDB Cluster Start Phases
22.5.2 Commands in the NDB Cluster Management Client
22.5.3 Online Backup of NDB Cluster
22.5.4 MySQL Server Usage for NDB Cluster
22.5.5 Performing a Rolling Restart of an NDB Cluster
22.5.6 Event Reports Generated in NDB Cluster
22.5.7 NDB Cluster Log Messages
22.5.8 NDB Cluster Single User Mode
22.5.9 Quick Reference: NDB Cluster SQL Statements
22.5.10 ndbinfo: The NDB Cluster Information Database
22.5.11 INFORMATION_SCHEMA Tables for NDB Cluster
22.5.12 NDB Cluster Security Issues
22.5.13 NDB Cluster Disk Data Tables
22.5.14 Online Operations with ALTER TABLE in NDB Cluster
22.5.15 Adding NDB Cluster Data Nodes Online
22.5.16 Distributed MySQL Privileges with NDB_STORED_USER
22.5.17 NDB API Statistics Counters and Variables
22.6 NDB Cluster Replication
22.6.1 NDB Cluster Replication: Abbreviations and Symbols
22.6.2 General Requirements for NDB Cluster Replication
22.6.3 Known Issues in NDB Cluster Replication
22.6.4 NDB Cluster Replication Schema and Tables
22.6.5 Preparing the NDB Cluster for Replication
22.6.6 Starting NDB Cluster Replication (Single Replication Channel)
22.6.7 Using Two Replication Channels for NDB Cluster Replication
22.6.8 Implementing Failover with NDB Cluster Replication
22.6.9 NDB Cluster Backups With NDB Cluster Replication
22.6.10 NDB Cluster Replication: Multi-Master and Circular Replication
22.6.11 NDB Cluster Replication Conflict Resolution
22.7 NDB Cluster Release Notes
23 Partitioning
23.1 Overview of Partitioning in MySQL
23.2 Partitioning Types
23.2.1 RANGE Partitioning
23.2.2 LIST Partitioning
23.2.3 COLUMNS Partitioning
23.2.4 HASH Partitioning
23.2.5 KEY Partitioning
23.2.6 Subpartitioning
23.2.7 How MySQL Partitioning Handles NULL
23.3 Partition Management
23.3.1 Management of RANGE and LIST Partitions
23.3.2 Management of HASH and KEY Partitions
23.3.3 Exchanging Partitions and Subpartitions with Tables
23.3.4 Maintenance of Partitions
23.3.5 Obtaining Information About Partitions
23.4 Partition Pruning
23.5 Partition Selection
23.6 Restrictions and Limitations on Partitioning
23.6.1 Partitioning Keys, Primary Keys, and Unique Keys
23.6.2 Partitioning Limitations Relating to Storage Engines
23.6.3 Partitioning Limitations Relating to Functions
24 Stored Objects
24.1 Defining Stored Programs
24.2 Using Stored Routines
24.2.1 Stored Routine Syntax
24.2.2 Stored Routines and MySQL Privileges
24.2.3 Stored Routine Metadata
24.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()
24.3 Using Triggers
24.3.1 Trigger Syntax and Examples
24.3.2 Trigger Metadata
24.4 Using the Event Scheduler
24.4.1 Event Scheduler Overview
24.4.2 Event Scheduler Configuration
24.4.3 Event Syntax
24.4.4 Event Metadata
24.4.5 Event Scheduler Status
24.4.6 The Event Scheduler and MySQL Privileges
24.5 Using Views
24.5.1 View Syntax
24.5.2 View Processing Algorithms
24.5.3 Updatable and Insertable Views
24.5.4 The View WITH CHECK OPTION Clause
24.5.5 View Metadata
24.6 Stored Object Access Control
24.7 Stored Program Binary Logging
24.8 Restrictions on Stored Programs
24.9 Restrictions on Views
25 INFORMATION_SCHEMA Tables
25.1 Introduction
25.2 The INFORMATION_SCHEMA ADMINISTRABLE_ROLE_AUTHORIZATIONS Table
25.3 The INFORMATION_SCHEMA APPLICABLE_ROLES Table
25.4 The INFORMATION_SCHEMA CHARACTER_SETS Table
25.5 The INFORMATION_SCHEMA CHECK_CONSTRAINTS Table
25.6 The INFORMATION_SCHEMA COLLATIONS Table
25.7 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
25.8 The INFORMATION_SCHEMA COLUMNS Table
25.9 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
25.10 The INFORMATION_SCHEMA COLUMN_STATISTICS Table
25.11 The INFORMATION_SCHEMA ENABLED_ROLES Table
25.12 The INFORMATION_SCHEMA ENGINES Table
25.13 The INFORMATION_SCHEMA EVENTS Table
25.14 The INFORMATION_SCHEMA FILES Table
25.15 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
25.16 The INFORMATION_SCHEMA ndb_transid_mysql_connection_map Table
25.17 The INFORMATION_SCHEMA KEYWORDS Table
25.18 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table
25.19 The INFORMATION_SCHEMA PARAMETERS Table
25.20 The INFORMATION_SCHEMA PARTITIONS Table
25.21 The INFORMATION_SCHEMA PLUGINS Table
25.22 The INFORMATION_SCHEMA PROCESSLIST Table
25.23 The INFORMATION_SCHEMA PROFILING Table
25.24 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table
25.25 The INFORMATION_SCHEMA RESOURCE_GROUPS Table
25.26 The INFORMATION_SCHEMA ROLE_COLUMN_GRANTS Table
25.27 The INFORMATION_SCHEMA ROLE_ROUTINE_GRANTS Table
25.28 The INFORMATION_SCHEMA ROLE_TABLE_GRANTS Table
25.29 The INFORMATION_SCHEMA ROUTINES Table
25.30 The INFORMATION_SCHEMA SCHEMATA Table
25.31 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
25.32 The INFORMATION_SCHEMA STATISTICS Table
25.33 The INFORMATION_SCHEMA ST_GEOMETRY_COLUMNS Table
25.34 The INFORMATION_SCHEMA ST_SPATIAL_REFERENCE_SYSTEMS Table
25.35 The INFORMATION_SCHEMA ST_UNITS_OF_MEASURE Table
25.36 The INFORMATION_SCHEMA TABLES Table
25.37 The INFORMATION_SCHEMA TABLESPACES Table
25.38 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
25.39 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
25.40 The INFORMATION_SCHEMA TRIGGERS Table
25.41 The INFORMATION_SCHEMA USER_PRIVILEGES Table
25.42 The INFORMATION_SCHEMA VIEWS Table
25.43 The INFORMATION_SCHEMA VIEW_ROUTINE_USAGE Table
25.44 The INFORMATION_SCHEMA VIEW_TABLE_USAGE Table
25.45 INFORMATION_SCHEMA InnoDB Tables
25.45.1 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table
25.45.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table
25.45.3 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table
25.45.4 The INFORMATION_SCHEMA INNODB_CACHED_INDEXES Table
25.45.5 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables
25.45.6 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables
25.45.7 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables
25.45.8 The INFORMATION_SCHEMA INNODB_COLUMNS Table
25.45.9 The INFORMATION_SCHEMA INNODB_DATAFILES Table
25.45.10 The INFORMATION_SCHEMA INNODB_FIELDS Table
25.45.11 The INFORMATION_SCHEMA INNODB_FOREIGN Table
25.45.12 The INFORMATION_SCHEMA INNODB_FOREIGN_COLS Table
25.45.13 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table
25.45.14 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table
25.45.15 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table
25.45.16 The INFORMATION_SCHEMA INNODB_FT_DELETED Table
25.45.17 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table
25.45.18 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table
25.45.19 The INFORMATION_SCHEMA INNODB_INDEXES Table
25.45.20 The INFORMATION_SCHEMA INNODB_LOCKS Table
25.45.21 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table
25.45.22 The INFORMATION_SCHEMA INNODB_METRICS Table
25.45.23 The INFORMATION_SCHEMA INNODB_SESSION_TEMP_TABLESPACES Table
25.45.24 The INFORMATION_SCHEMA INNODB_TABLES Table
25.45.25 The INFORMATION_SCHEMA INNODB_TABLESPACES Table
25.45.26 The INFORMATION_SCHEMA INNODB_TABLESPACES_BRIEF Table
25.45.27 The INFORMATION_SCHEMA INNODB_TABLESTATS View
25.45.28 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table
25.45.29 The INFORMATION_SCHEMA INNODB_TRX Table
25.45.30 The INFORMATION_SCHEMA INNODB_VIRTUAL Table
25.46 INFORMATION_SCHEMA Thread Pool Tables
25.46.1 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATE Table
25.46.2 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATS Table
25.46.3 The INFORMATION_SCHEMA TP_THREAD_STATE Table
25.47 INFORMATION_SCHEMA Connection-Control Tables
25.47.1 The INFORMATION_SCHEMA CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS Table
25.48 Extensions to SHOW Statements
26 MySQL Performance Schema
26.1 Performance Schema Quick Start
26.2 Performance Schema Build Configuration
26.3 Performance Schema Startup Configuration
26.4 Performance Schema Runtime Configuration
26.4.1 Performance Schema Event Timing
26.4.2 Performance Schema Event Filtering
26.4.3 Event Pre-Filtering
26.4.4 Pre-Filtering by Instrument
26.4.5 Pre-Filtering by Object
26.4.6 Pre-Filtering by Thread
26.4.7 Pre-Filtering by Consumer
26.4.8 Example Consumer Configurations
26.4.9 Naming Instruments or Consumers for Filtering Operations
26.4.10 Determining What Is Instrumented
26.5 Performance Schema Queries
26.6 Performance Schema Instrument Naming Conventions
26.7 Performance Schema Status Monitoring
26.8 Performance Schema Atom and Molecule Events
26.9 Performance Schema Tables for Current and Historical Events
26.10 Performance Schema Statement Digests and Sampling
26.11 Performance Schema General Table Characteristics
26.12 Performance Schema Table Descriptions
26.12.1 Performance Schema Table Index
26.12.2 Performance Schema Setup Tables
26.12.3 Performance Schema Instance Tables
26.12.4 Performance Schema Wait Event Tables
26.12.5 Performance Schema Stage Event Tables
26.12.6 Performance Schema Statement Event Tables
26.12.7 Performance Schema Transaction Tables
26.12.8 Performance Schema Connection Tables
26.12.9 Performance Schema Connection Attribute Tables
26.12.10 Performance Schema User-Defined Variable Tables
26.12.11 Performance Schema Replication Tables
26.12.12 Performance Schema Lock Tables
26.12.13 Performance Schema System Variable Tables
26.12.14 Performance Schema Status Variable Tables
26.12.15 Performance Schema Thread Pool Tables
26.12.16 Performance Schema Clone Tables
26.12.17 Performance Schema Summary Tables
26.12.18 Performance Schema Miscellaneous Tables
26.13 Performance Schema Option and Variable Reference
26.14 Performance Schema Command Options
26.15 Performance Schema System Variables
26.16 Performance Schema Status Variables
26.17 The Performance Schema Memory-Allocation Model
26.18 Performance Schema and Plugins
26.19 Using the Performance Schema to Diagnose Problems
26.19.1 Query Profiling Using Performance Schema
26.19.2 Obtaining Parent Event Information
26.20 Restrictions on Performance Schema
27 MySQL sys Schema
27.1 Prerequisites for Using the sys Schema
27.2 Using the sys Schema
27.3 sys Schema Progress Reporting
27.4 sys Schema Object Reference
27.4.1 sys Schema Object Index
27.4.2 sys Schema Tables and Triggers
27.4.3 sys Schema Views
27.4.4 sys Schema Stored Procedures
27.4.5 sys Schema Stored Functions
28 Connectors and APIs
28.1 MySQL Connector/C++
28.2 MySQL Connector/J
28.3 MySQL Connector/NET
28.4 MySQL Connector/ODBC
28.5 MySQL Connector/Python
28.6 MySQL C API
28.6.1 MySQL C API Implementations
28.6.2 Example C API Client Programs
28.6.3 Building and Running C API Client Programs
28.6.4 C API Data Structures
28.6.5 C API Function Overview
28.6.6 C API Function Descriptions
28.6.7 C API Prepared Statements
28.6.8 C API Prepared Statement Data Structures
28.6.9 C API Prepared Statement Function Overview
28.6.10 C API Prepared Statement Function Descriptions
28.6.11 C API Asynchronous Interface
28.6.12 C API Asynchronous Interface Data Structures
28.6.13 C API Asynchronous Function Overview
28.6.14 C API Asynchronous Function Descriptions
28.6.15 C API Threaded Function Descriptions
28.6.16 C API Client Plugin Functions
28.6.17 C API Binary Log Interface
28.6.18 C API Binary Log Data Structures
28.6.19 C API Binary Log Function Overview
28.6.20 C API Binary Log Function Descriptions
28.6.21 C API Encrypted Connection Support
28.6.22 C API Multiple Statement Execution Support
28.6.23 C API Prepared Statement Handling of Date and Time Values
28.6.24 C API Prepared CALL Statement Support
28.6.25 C API Prepared Statement Problems
28.6.26 C API Optional Result Set Metadata
28.6.27 C API Automatic Reconnection Control
28.6.28 C API Common Issues
28.7 MySQL PHP API
28.8 MySQL Perl API
28.9 MySQL Python API
28.10 MySQL Ruby APIs
28.10.1 The MySQL/Ruby API
28.10.2 The Ruby/MySQL API
28.11 MySQL Tcl API
28.12 MySQL Eiffel Wrapper
29 Extending MySQL
29.1 MySQL Internals
29.1.1 MySQL Threads
29.1.2 The MySQL Test Suite
29.2 The MySQL Plugin API
29.2.1 Types of Plugins
29.2.2 Plugin API Characteristics
29.2.3 Plugin API Components
29.2.4 Writing Plugins
29.3 MySQL Services for Plugins
29.3.1 The Locking Service
29.3.2 The Keyring Service
29.4 Adding Functions to MySQL
29.4.1 Features of the User-Defined Function Interface
29.4.2 Adding a User-Defined Function
29.4.3 Adding a Native Function
29.5 Debugging and Porting MySQL
29.5.1 Debugging a MySQL Server
29.5.2 Debugging a MySQL Client
29.5.3 The LOCK_ORDER Tool
29.5.4 The DBUG Package
30 MySQL Enterprise Edition
30.1 MySQL Enterprise Monitor Overview
30.2 MySQL Enterprise Backup Overview
30.3 MySQL Enterprise Security Overview
30.4 MySQL Enterprise Encryption Overview
30.5 MySQL Enterprise Audit Overview
30.6 MySQL Enterprise Firewall Overview
30.7 MySQL Enterprise Thread Pool Overview
30.8 MySQL Enterprise Data Masking and De-Identification Overview
31 MySQL Workbench
32 MySQL on the OCI Marketplace
32.1 Prerequisites to Deploying MySQL EE on Oracle Cloud Infrastructure
32.2 Deploying MySQL EE on Oracle Cloud Infrastructure
32.3 Configuring Network Access
32.4 Connecting
32.5 Maintenance
A MySQL 8.0 Frequently Asked Questions
A.1 MySQL 8.0 FAQ: General
A.2 MySQL 8.0 FAQ: Storage Engines
A.3 MySQL 8.0 FAQ: Server SQL Mode
A.4 MySQL 8.0 FAQ: Stored Procedures and Functions
A.5 MySQL 8.0 FAQ: Triggers
A.6 MySQL 8.0 FAQ: Views
A.7 MySQL 8.0 FAQ: INFORMATION_SCHEMA
A.8 MySQL 8.0 FAQ: Migration
A.9 MySQL 8.0 FAQ: Security
A.10 MySQL 8.0 FAQ: NDB Cluster
A.11 MySQL 8.0 FAQ: MySQL Chinese, Japanese, and Korean Character Sets
A.12 MySQL 8.0 FAQ: Connectors & APIs
A.13 MySQL 8.0 FAQ: C API, libmysql
A.14 MySQL 8.0 FAQ: Replication
A.15 MySQL 8.0 FAQ: MySQL Enterprise Thread Pool
A.16 MySQL 8.0 FAQ: InnoDB Change Buffer
A.17 MySQL 8.0 FAQ: InnoDB Data-at-Rest Encryption
A.18 MySQL 8.0 FAQ: Virtualization Support
B Errors, Error Codes, and Common Problems
B.1 Error Message Sources and Components
B.2 Error Information Interfaces
B.3 Error Message Reference
B.3.1 Server Error Message Reference
B.3.2 Client Error Message Reference
B.3.3 Global Error Message Reference
B.4 Problems and Common Errors
B.4.1 How to Determine What Is Causing a Problem
B.4.2 Common Errors When Using MySQL Programs
B.4.3 Administration-Related Issues
B.4.4 Query-Related Issues
B.4.5 Optimizer-Related Issues
B.4.6 Table Definition-Related Issues
B.4.7 Known Issues in MySQL
C Indexes
General Index
C Function Index
Command Index
Function Index
INFORMATION_SCHEMA Index
Join Types Index
Operator Index
Option Index
Privileges Index
SQL Modes Index
Statement/Syntax Index
Status Variable Index
System Variable Index
Transaction Isolation Level Index
MySQL Glossary
Up  Next
Home Preface and Legal Notices