MySQL 5.7 Reference Manual

Including MySQL NDB Cluster 7.5

Abstract

This is the MySQL™ Reference Manual. It documents MySQL 5.7 through 5.7.18, as well as MySQL Cluster releases based on version 7.5 of NDB through 5.7.17-ndb-7.5.6, respectively.

MySQL 5.7 features.  This manual describes features that are not included in every edition of MySQL 5.7; such features may not be included in the edition of MySQL 5.7 licensed to you. If you have any questions about the features included in your edition of MySQL 5.7, refer to your MySQL 5.7 license agreement or contact your Oracle sales representative.

For notes detailing the changes in each release, see the MySQL 5.7 Release Notes.

For legal information, see the Legal Notices.

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

For additional documentation on MySQL products, including translations of the documentation into other languages, and downloadable versions in variety of formats, including HTML and PDF formats, see the MySQL Documentation Library.

Licensing information—MySQL 5.7.  This product may include third-party software, used under license. If you are using a Commercial release of MySQL 5.7, see this document for licensing information, including licensing information relating to third-party software that may be included in this Commercial release. If you are using a Community release of MySQL 5.7, see this document for licensing information, including licensing information relating to third-party software that may be included in this Community release.

Licensing information—MySQL Cluster.  This product may include third-party software, used under license. If you are using a Community release of MySQL NDB Cluster 7.5, see this document for licensing information, including licensing information relating to third-party software that may be included in this Community release.

Document generated on: 2017-02-24 (revision: 50920)


Table of Contents

Preface and Legal Notices
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 5.7
1.5 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 5.7
1.6 MySQL Information Sources
1.6.1 MySQL Mailing Lists
1.6.2 MySQL Community Support at the MySQL Forums
1.6.3 MySQL Community Support on Internet Relay Chat (IRC)
1.6.4 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 Installing MySQL on Microsoft Windows Using MySQL Installer
2.3.4 MySQL Notifier
2.3.5 Installing MySQL on Microsoft Windows Using a noinstall Zip Archive
2.3.6 Troubleshooting a Microsoft Windows MySQL Server Installation
2.3.7 Windows Postinstallation Procedures
2.3.8 Upgrading MySQL on Windows
2.4 Installing MySQL on OS X
2.4.1 General Notes on Installing MySQL on OS X
2.4.2 Installing MySQL on OS X Using Native Packages
2.4.3 Installing a 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 Replacing a Third-Party Distribution of MySQL Using the MySQL Yum Repository
2.5.3 Installing MySQL on Linux Using the MySQL APT Repository
2.5.4 Installing MySQL on Linux Using the MySQL SLES Repository
2.5.5 Installing MySQL on Linux Using RPM Packages from Oracle
2.5.6 Installing MySQL on Linux Using Debian Packages from Oracle
2.5.7 Installing MySQL on Linux from the Native Software Repositories
2.5.8 Installing MySQL on Linux with docker
2.5.9 Installing MySQL on Linux with juju
2.5.10 Managing MySQL Server with systemd
2.6 Installing MySQL Using Unbreakable Linux Network (ULN)
2.7 Installing MySQL on Solaris and OpenSolaris
2.7.1 Installing MySQL on Solaris Using a Solaris PKG
2.7.2 Installing MySQL on OpenSolaris Using IPS
2.8 Installing MySQL on FreeBSD
2.9 Installing MySQL from Source
2.9.1 MySQL Layout for Source Installation
2.9.2 Installing MySQL Using a Standard Source Distribution
2.9.3 Installing MySQL Using a Development Source Tree
2.9.4 MySQL Source-Configuration Options
2.9.5 Dealing with Problems Compiling MySQL
2.9.6 MySQL Configuration and Third-Party Tools
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 Accounts
2.10.5 Starting and Stopping MySQL Automatically
2.11 Upgrading or Downgrading MySQL
2.11.1 Upgrading MySQL
2.11.2 Downgrading MySQL
2.11.3 Checking Whether Tables or Indexes Must Be Rebuilt
2.11.4 Rebuilding or Repairing Tables or Indexes
2.11.5 Copying MySQL Databases to Another Machine
2.12 Perl Installation Notes
2.12.1 Installing Perl on Unix
2.12.2 Installing ActiveState Perl on Windows
2.12.3 Problems Using the Perl DBI/DBD Interface
3 Using MySQL as a Document Store
3.1 Preproduction Status — Legal Notice
3.2 Key Concepts
3.3 Setting Up MySQL as a Document Store
3.3.1 Installing MySQL Shell
3.3.2 Starting MySQL Shell
3.4 Quick-Start Guide: MySQL Shell for JavaScript
3.4.1 Introduction
3.4.2 Import Database Sample
3.4.3 MySQL Shell
3.4.4 Documents and Collections
3.4.5 Relational Tables
3.4.6 Documents in Tables
3.5 Quick-Start Guide: MySQL Shell for Python
3.5.1 Introduction
3.5.2 Import Database Sample
3.5.3 MySQL Shell
3.5.4 Documents and Collections
3.5.5 Relational Tables
3.5.6 Documents in Tables
3.6 Quick-Start Guide: MySQL for Visual Studio
3.7 X Plugin
3.7.1 Using Secure Connections with X Plugin
3.7.2 X Plugin Options and Variables
3.7.3 Monitoring X Plugin
3.8 MySQL Shell User Guide
3.8.1 MySQL Shell Features
3.8.2 Getting Started with MySQL Shell
3.8.3 MySQL Shell Code Execution
3.8.4 Configuring MySQL Shell
3.8.5 Stored Sessions
3.8.6 MySQL Shell Application Log
3.8.7 Customizing MySQL Shell
4 Tutorial
4.1 Connecting to and Disconnecting from the Server
4.2 Entering Queries
4.3 Creating and Using a Database
4.3.1 Creating and Selecting a Database
4.3.2 Creating a Table
4.3.3 Loading Data into a Table
4.3.4 Retrieving Information from a Table
4.4 Getting Information About Databases and Tables
4.5 Using mysql in Batch Mode
4.6 Examples of Common Queries
4.6.1 The Maximum Value for a Column
4.6.2 The Row Holding the Maximum of a Certain Column
4.6.3 Maximum of Column per Group
4.6.4 The Rows Holding the Group-wise Maximum of a Certain Column
4.6.5 Using User-Defined Variables
4.6.6 Using Foreign Keys
4.6.7 Searching on Two Keys
4.6.8 Calculating Visits Per Day
4.6.9 Using AUTO_INCREMENT
4.7 Using MySQL with Apache
5 MySQL Programs
5.1 Overview of MySQL Programs
5.2 Using MySQL Programs
5.2.1 Invoking MySQL Programs
5.2.2 Connecting to the MySQL Server
5.2.3 Specifying Program Options
5.2.4 Using Options on the Command Line
5.2.5 Program Option Modifiers
5.2.6 Using Option Files
5.2.7 Command-Line Options that Affect Option-File Handling
5.2.8 Using Options to Set Program Variables
5.2.9 Option Defaults, Options Expecting Values, and the = Sign
5.2.10 Setting Environment Variables
5.3 MySQL Server and Server-Startup Programs
5.3.1 mysqld — The MySQL Server
5.3.2 mysqld_safe — MySQL Server Startup Script
5.3.3 mysql.server — MySQL Server Startup Script
5.3.4 mysqld_multi — Manage Multiple MySQL Servers
5.4 MySQL Installation-Related Programs
5.4.1 comp_err — Compile MySQL Error Message File
5.4.2 mysql_install_db — Initialize MySQL Data Directory
5.4.3 mysql_plugin — Configure MySQL Server Plugins
5.4.4 mysql_secure_installation — Improve MySQL Installation Security
5.4.5 mysql_ssl_rsa_setup — Create SSL/RSA Files
5.4.6 mysql_tzinfo_to_sql — Load the Time Zone Tables
5.4.7 mysql_upgrade — Check and Upgrade MySQL Tables
5.5 MySQL Client Programs
5.5.1 mysql — The MySQL Command-Line Tool
5.5.2 mysqladmin — Client for Administering a MySQL Server
5.5.3 mysqlcheck — A Table Maintenance Program
5.5.4 mysqldump — A Database Backup Program
5.5.5 mysqlimport — A Data Import Program
5.5.6 mysqlpump — A Database Backup Program
5.5.7 mysqlsh — The MySQL Shell
5.5.8 mysqlshow — Display Database, Table, and Column Information
5.5.9 mysqlslap — Load Emulation Client
5.6 MySQL Administrative and Utility Programs
5.6.1 innochecksum — Offline InnoDB File Checksum Utility
5.6.2 myisam_ftdump — Display Full-Text Index information
5.6.3 myisamchk — MyISAM Table-Maintenance Utility
5.6.4 myisamlog — Display MyISAM Log File Contents
5.6.5 myisampack — Generate Compressed, Read-Only MyISAM Tables
5.6.6 mysql_config_editor — MySQL Configuration Utility
5.6.7 mysqlbinlog — Utility for Processing Binary Log Files
5.6.8 mysqldumpslow — Summarize Slow Query Log Files
5.7 MySQL Program Development Utilities
5.7.1 mysql_config — Display Options for Compiling Clients
5.7.2 my_print_defaults — Display Options from Option Files
5.7.3 resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols
5.8 Miscellaneous Programs
5.8.1 lz4_decompress — Decompress mysqlpump LZ4-Compressed Output
5.8.2 perror — Explain Error Codes
5.8.3 replace — A String-Replacement Utility
5.8.4 resolveip — Resolve Host name to IP Address or Vice Versa
5.8.5 zlib_decompress — Decompress mysqlpump ZLIB-Compressed Output
5.9 MySQL Program Environment Variables
6 MySQL Server Administration
6.1 The MySQL Server
6.1.1 Configuring the Server
6.1.2 Server Configuration Defaults
6.1.3 Server Option and Variable Reference
6.1.4 Server Command Options
6.1.5 Server System Variables
6.1.6 Using System Variables
6.1.7 Server Status Variables
6.1.8 Server SQL Modes
6.1.9 IPv6 Support
6.1.10 Server-Side Help
6.1.11 Server Response to Signals
6.1.12 The Server Shutdown Process
6.2 The MySQL Data Directory
6.3 The mysql System Database
6.4 MySQL Server Logs
6.4.1 Selecting General Query and Slow Query Log Output Destinations
6.4.2 The Error Log
6.4.3 The General Query Log
6.4.4 The Binary Log
6.4.5 The Slow Query Log
6.4.6 The DDL Log
6.4.7 Server Log Maintenance
6.5 MySQL Server Plugins
6.5.1 Server Plugins Available
6.5.2 Installing and Uninstalling Plugins
6.5.3 Obtaining Server Plugin Information
6.5.4 MySQL Enterprise Thread Pool
6.5.5 The Rewriter Query Rewrite Plugin
6.5.6 Version Tokens
6.6 Running Multiple MySQL Instances on One Machine
6.6.1 Setting Up Multiple Data Directories
6.6.2 Running Multiple MySQL Instances on Windows
6.6.3 Running Multiple MySQL Instances on Unix
6.6.4 Using Client Programs in a Multiple-Server Environment
6.7 Tracing mysqld Using DTrace
6.7.1 mysqld DTrace Probe Reference
7 Security
7.1 General Security Issues
7.1.1 Security Guidelines
7.1.2 Keeping Passwords Secure
7.1.3 Making MySQL Secure Against Attackers
7.1.4 Security-Related mysqld Options and Variables
7.1.5 How to Run MySQL as a Normal User
7.1.6 Security Issues with LOAD DATA LOCAL
7.1.7 Client Programming Security Guidelines
7.2 The MySQL Access Privilege System
7.2.1 Privileges Provided by MySQL
7.2.2 Grant Tables
7.2.3 Specifying Account Names
7.2.4 Access Control, Stage 1: Connection Verification
7.2.5 Access Control, Stage 2: Request Verification
7.2.6 When Privilege Changes Take Effect
7.2.7 Troubleshooting Problems Connecting to MySQL
7.3 MySQL User Account Management
7.3.1 User Names and Passwords
7.3.2 Adding User Accounts
7.3.3 Removing User Accounts
7.3.4 Setting Account Resource Limits
7.3.5 Assigning Account Passwords
7.3.6 Password Expiration Policy
7.3.7 Password Expiration and Sandbox Mode
7.3.8 Pluggable Authentication
7.3.9 Proxy Users
7.3.10 User Account Locking
7.3.11 SQL-Based MySQL Account Activity Auditing
7.4 Using Secure Connections
7.4.1 OpenSSL Versus yaSSL
7.4.2 Building MySQL with Support for Secure Connections
7.4.3 Secure Connection Protocols and Ciphers
7.4.4 Configuring MySQL to Use Secure Connections
7.4.5 Command Options for Secure Connections
7.4.6 Creating SSL and RSA Certificates and Keys
7.4.7 Connecting to MySQL Remotely from Windows with SSH
7.5 Security Plugins
7.5.1 Authentication Plugins
7.5.2 The Connection-Control Plugin
7.5.3 The Password Validation Plugin
7.5.4 The MySQL Keyring
7.5.5 MySQL Enterprise Audit
7.5.6 MySQL Enterprise Firewall
8 Backup and Recovery
8.1 Backup and Recovery Types
8.2 Database Backup Methods
8.3 Example Backup and Recovery Strategy
8.3.1 Establishing a Backup Policy
8.3.2 Using Backups for Recovery
8.3.3 Backup Strategy Summary
8.4 Using mysqldump for Backups
8.4.1 Dumping Data in SQL Format with mysqldump
8.4.2 Reloading SQL-Format Backups
8.4.3 Dumping Data in Delimited-Text Format with mysqldump
8.4.4 Reloading Delimited-Text Format Backups
8.4.5 mysqldump Tips
8.5 Point-in-Time (Incremental) Recovery Using the Binary Log
8.5.1 Point-in-Time Recovery Using Event Times
8.5.2 Point-in-Time Recovery Using Event Positions
8.6 MyISAM Table Maintenance and Crash Recovery
8.6.1 Using myisamchk for Crash Recovery
8.6.2 How to Check MyISAM Tables for Errors
8.6.3 How to Repair MyISAM Tables
8.6.4 MyISAM Table Optimization
8.6.5 Setting Up a MyISAM Table Maintenance Schedule
9 Optimization
9.1 Optimization Overview
9.2 Optimizing SQL Statements
9.2.1 Optimizing SELECT Statements
9.2.2 Optimizing Subqueries, Derived Tables, and View References
9.2.3 Optimizing INFORMATION_SCHEMA Queries
9.2.4 Optimizing Data Change Statements
9.2.5 Optimizing Database Privileges
9.2.6 Other Optimization Tips
9.3 Optimization and Indexes
9.3.1 How MySQL Uses Indexes
9.3.2 Using Primary Keys
9.3.3 Using Foreign Keys
9.3.4 Column Indexes
9.3.5 Multiple-Column Indexes
9.3.6 Verifying Index Usage
9.3.7 InnoDB and MyISAM Index Statistics Collection
9.3.8 Comparison of B-Tree and Hash Indexes
9.3.9 Use of Index Extensions
9.3.10 Optimizer Use of Generated Column Indexes
9.4 Optimizing Database Structure
9.4.1 Optimizing Data Size
9.4.2 Optimizing MySQL Data Types
9.4.3 Optimizing for Many Tables
9.4.4 Internal Temporary Table Use in MySQL
9.5 Optimizing for InnoDB Tables
9.5.1 Optimizing Storage Layout for InnoDB Tables
9.5.2 Optimizing InnoDB Transaction Management
9.5.3 Optimizing InnoDB Read-Only Transactions
9.5.4 Optimizing InnoDB Redo Logging
9.5.5 Bulk Data Loading for InnoDB Tables
9.5.6 Optimizing InnoDB Queries
9.5.7 Optimizing InnoDB DDL Operations
9.5.8 Optimizing InnoDB Disk I/O
9.5.9 Optimizing InnoDB Configuration Variables
9.5.10 Optimizing InnoDB for Systems with Many Tables
9.6 Optimizing for MyISAM Tables
9.6.1 Optimizing MyISAM Queries
9.6.2 Bulk Data Loading for MyISAM Tables
9.6.3 Optimizing REPAIR TABLE Statements
9.7 Optimizing for MEMORY Tables
9.8 Understanding the Query Execution Plan
9.8.1 Optimizing Queries with EXPLAIN
9.8.2 EXPLAIN Output Format
9.8.3 Extended EXPLAIN Output Format
9.8.4 Obtaining Execution Plan Information for a Named Connection
9.8.5 Estimating Query Performance
9.9 Controlling the Query Optimizer
9.9.1 Controlling Query Plan Evaluation
9.9.2 Controlling Switchable Optimizations
9.9.3 Optimizer Hints
9.9.4 Index Hints
9.9.5 The Optimizer Cost Model
9.10 Buffering and Caching
9.10.1 InnoDB Buffer Pool Optimization
9.10.2 The MyISAM Key Cache
9.10.3 The MySQL Query Cache
9.10.4 Caching of Prepared Statements and Stored Programs
9.11 Optimizing Locking Operations
9.11.1 Internal Locking Methods
9.11.2 Table Locking Issues
9.11.3 Concurrent Inserts
9.11.4 Metadata Locking
9.11.5 External Locking
9.12 Optimizing the MySQL Server
9.12.1 System Factors
9.12.2 Optimizing Disk I/O
9.12.3 Using Symbolic Links
9.12.4 Optimizing Memory Use
9.12.5 Optimizing Network Use
9.13 Measuring Performance (Benchmarking)
9.13.1 Measuring the Speed of Expressions and Functions
9.13.2 Using Your Own Benchmarks
9.13.3 Measuring Performance with performance_schema
9.14 Examining Thread Information
9.14.1 Thread Command Values
9.14.2 General Thread States
9.14.3 Query Cache Thread States
9.14.4 Replication Master Thread States
9.14.5 Replication Slave I/O Thread States
9.14.6 Replication Slave SQL Thread States
9.14.7 Replication Slave Connection Thread States
9.14.8 MySQL Cluster Thread States
9.14.9 Event Scheduler Thread States
10 Language Structure
10.1 Literal Values
10.1.1 String Literals
10.1.2 Number Literals
10.1.3 Date and Time Literals
10.1.4 Hexadecimal Literals
10.1.5 Bit-Value Literals
10.1.6 Boolean Literals
10.1.7 NULL Values
10.2 Schema Object Names
10.2.1 Identifier Qualifiers
10.2.2 Identifier Case Sensitivity
10.2.3 Mapping of Identifiers to File Names
10.2.4 Function Name Parsing and Resolution
10.3 Keywords and Reserved Words
10.4 User-Defined Variables
10.5 Expression Syntax
10.6 Comment Syntax
11 Globalization
11.1 Character Set Support
11.1.1 Character Sets and Collations in General
11.1.2 Character Sets and Collations in MySQL
11.1.3 Specifying Character Sets and Collations
11.1.4 Connection Character Sets and Collations
11.1.5 Configuring Application Character Set and Collation
11.1.6 Error Message Character Set
11.1.7 Column Character Set Conversion
11.1.8 Collation Issues
11.1.9 Unicode Support
11.1.10 Supported Character Sets and Collations
11.2 Setting the Error Message Language
11.3 Adding a Character Set
11.3.1 Character Definition Arrays
11.3.2 String Collating Support for Complex Character Sets
11.3.3 Multi-Byte Character Support for Complex Character Sets
11.4 Adding a Collation to a Character Set
11.4.1 Collation Implementation Types
11.4.2 Choosing a Collation ID
11.4.3 Adding a Simple Collation to an 8-Bit Character Set
11.4.4 Adding a UCA Collation to a Unicode Character Set
11.5 Character Set Configuration
11.6 MySQL Server Time Zone Support
11.6.1 Staying Current with Time Zone Changes
11.6.2 Time Zone Leap Second Support
11.7 MySQL Server Locale Support
12 Data Types
12.1 Data Type Overview
12.1.1 Numeric Type Overview
12.1.2 Date and Time Type Overview
12.1.3 String Type Overview
12.2 Numeric Types
12.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
12.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC
12.2.3 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE
12.2.4 Bit-Value Type - BIT
12.2.5 Numeric Type Attributes
12.2.6 Out-of-Range and Overflow Handling
12.3 Date and Time Types
12.3.1 The DATE, DATETIME, and TIMESTAMP Types
12.3.2 The TIME Type
12.3.3 The YEAR Type
12.3.4 YEAR(2) Limitations and Migrating to YEAR(4)
12.3.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME
12.3.6 Fractional Seconds in Time Values
12.3.7 Conversion Between Date and Time Types
12.3.8 Two-Digit Years in Dates
12.4 String Types
12.4.1 The CHAR and VARCHAR Types
12.4.2 The BINARY and VARBINARY Types
12.4.3 The BLOB and TEXT Types
12.4.4 The ENUM Type
12.4.5 The SET Type
12.5 Extensions for Spatial Data
12.5.1 Spatial Data Types
12.5.2 The OpenGIS Geometry Model
12.5.3 Using Spatial Data
12.6 The JSON Data Type
12.7 Data Type Default Values
12.8 Data Type Storage Requirements
12.9 Choosing the Right Type for a Column
12.10 Using Data Types from Other Database Engines
13 Functions and Operators
13.1 Function and Operator Reference
13.2 Type Conversion in Expression Evaluation
13.3 Operators
13.3.1 Operator Precedence
13.3.2 Comparison Functions and Operators
13.3.3 Logical Operators
13.3.4 Assignment Operators
13.4 Control Flow Functions
13.5 String Functions
13.5.1 String Comparison Functions
13.5.2 Regular Expressions
13.5.3 Character Set and Collation of Function Results
13.6 Numeric Functions and Operators
13.6.1 Arithmetic Operators
13.6.2 Mathematical Functions
13.7 Date and Time Functions
13.8 What Calendar Is Used By MySQL?
13.9 Full-Text Search Functions
13.9.1 Natural Language Full-Text Searches
13.9.2 Boolean Full-Text Searches
13.9.3 Full-Text Searches with Query Expansion
13.9.4 Full-Text Stopwords
13.9.5 Full-Text Restrictions
13.9.6 Fine-Tuning MySQL Full-Text Search
13.9.7 Adding a Collation for Full-Text Indexing
13.9.8 ngram Full-Text Parser
13.9.9 MeCab Full-Text Parser Plugin
13.10 Cast Functions and Operators
13.11 XML Functions
13.12 Bit Functions and Operators
13.13 Encryption and Compression Functions
13.14 Information Functions
13.15 Spatial Analysis Functions
13.15.1 Spatial Function Reference
13.15.2 Argument Handling by Spatial Functions
13.15.3 Functions That Create Geometry Values from WKT Values
13.15.4 Functions That Create Geometry Values from WKB Values
13.15.5 MySQL-Specific Functions That Create Geometry Values
13.15.6 Geometry Format Conversion Functions
13.15.7 Geometry Property Functions
13.15.8 Spatial Operator Functions
13.15.9 Functions That Test Spatial Relations Between Geometry Objects
13.15.10 Spatial Geohash Functions
13.15.11 Spatial GeoJSON Functions
13.15.12 Spatial Convenience Functions
13.16 JSON Functions
13.16.1 JSON Function Reference
13.16.2 Functions That Create JSON Values
13.16.3 Functions That Search JSON Values
13.16.4 Functions That Modify JSON Values
13.16.5 Functions That Return JSON Value Attributes
13.16.6 JSON Path Syntax
13.17 Functions Used with Global Transaction IDs
13.18 MySQL Enterprise Encryption Functions
13.18.1 Enterprise Encryption Installation
13.18.2 Enterprise Encryption Usage and Examples
13.18.3 Enterprise Encryption Function Reference
13.18.4 Enterprise Encryption Function Descriptions
13.19 Aggregate (GROUP BY) Functions
13.19.1 Aggregate (GROUP BY) Function Descriptions
13.19.2 GROUP BY Modifiers
13.19.3 MySQL Handling of GROUP BY
13.19.4 Detection of Functional Dependence
13.20 Miscellaneous Functions
13.21 Precision Math
13.21.1 Types of Numeric Values
13.21.2 DECIMAL Data Type Characteristics
13.21.3 Expression Handling
13.21.4 Rounding Behavior
13.21.5 Precision Math Examples
14 SQL Statement Syntax
14.1 Data Definition Statements
14.1.1 ALTER DATABASE Syntax
14.1.2 ALTER EVENT Syntax
14.1.3 ALTER FUNCTION Syntax
14.1.4 ALTER INSTANCE Syntax
14.1.5 ALTER LOGFILE GROUP Syntax
14.1.6 ALTER PROCEDURE Syntax
14.1.7 ALTER SERVER Syntax
14.1.8 ALTER TABLE Syntax
14.1.9 ALTER TABLESPACE Syntax
14.1.10 ALTER VIEW Syntax
14.1.11 CREATE DATABASE Syntax
14.1.12 CREATE EVENT Syntax
14.1.13 CREATE FUNCTION Syntax
14.1.14 CREATE INDEX Syntax
14.1.15 CREATE LOGFILE GROUP Syntax
14.1.16 CREATE PROCEDURE and CREATE FUNCTION Syntax
14.1.17 CREATE SERVER Syntax
14.1.18 CREATE TABLE Syntax
14.1.19 CREATE TABLESPACE Syntax
14.1.20 CREATE TRIGGER Syntax
14.1.21 CREATE VIEW Syntax
14.1.22 DROP DATABASE Syntax
14.1.23 DROP EVENT Syntax
14.1.24 DROP FUNCTION Syntax
14.1.25 DROP INDEX Syntax
14.1.26 DROP LOGFILE GROUP Syntax
14.1.27 DROP PROCEDURE and DROP FUNCTION Syntax
14.1.28 DROP SERVER Syntax
14.1.29 DROP TABLE Syntax
14.1.30 DROP TABLESPACE Syntax
14.1.31 DROP TRIGGER Syntax
14.1.32 DROP VIEW Syntax
14.1.33 RENAME TABLE Syntax
14.1.34 TRUNCATE TABLE Syntax
14.2 Data Manipulation Statements
14.2.1 CALL Syntax
14.2.2 DELETE Syntax
14.2.3 DO Syntax
14.2.4 HANDLER Syntax
14.2.5 INSERT Syntax
14.2.6 LOAD DATA INFILE Syntax
14.2.7 LOAD XML Syntax
14.2.8 REPLACE Syntax
14.2.9 SELECT Syntax
14.2.10 Subquery Syntax
14.2.11 UPDATE Syntax
14.3 Transactional and Locking Statements
14.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax
14.3.2 Statements That Cannot Be Rolled Back
14.3.3 Statements That Cause an Implicit Commit
14.3.4 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Syntax
14.3.5 LOCK TABLES and UNLOCK TABLES Syntax
14.3.6 SET TRANSACTION Syntax
14.3.7 XA Transactions
14.4 Replication Statements
14.4.1 SQL Statements for Controlling Master Servers
14.4.2 SQL Statements for Controlling Slave Servers
14.4.3 SQL Statements for Controlling Group Replication
14.5 Prepared SQL Statement Syntax
14.5.1 PREPARE Syntax
14.5.2 EXECUTE Syntax
14.5.3 DEALLOCATE PREPARE Syntax
14.6 Compound-Statement Syntax
14.6.1 BEGIN ... END Compound-Statement Syntax
14.6.2 Statement Label Syntax
14.6.3 DECLARE Syntax
14.6.4 Variables in Stored Programs
14.6.5 Flow Control Statements
14.6.6 Cursors
14.6.7 Condition Handling
14.7 Database Administration Statements
14.7.1 Account Management Statements
14.7.2 Table Maintenance Statements
14.7.3 Plugin and User-Defined Function Statements
14.7.4 SET Syntax
14.7.5 SHOW Syntax
14.7.6 Other Administrative Statements
14.8 Utility Statements
14.8.1 DESCRIBE Syntax
14.8.2 EXPLAIN Syntax
14.8.3 HELP Syntax
14.8.4 USE Syntax
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 Checking InnoDB Availability
15.1.4 Testing and Benchmarking with InnoDB
15.1.5 Turning Off InnoDB
15.2 InnoDB and the ACID Model
15.3 InnoDB Multi-Versioning
15.4 InnoDB Architecture
15.4.1 Buffer Pool
15.4.2 Change Buffer
15.4.3 Adaptive Hash Index
15.4.4 Redo Log Buffer
15.4.5 System Tablespace
15.4.6 InnoDB Data Dictionary
15.4.7 Doublewrite Buffer
15.4.8 Undo Logs
15.4.9 File-Per-Table Tablespaces
15.4.10 General Tablespaces
15.4.11 Undo Tablespace
15.4.12 Temporary Tablespace
15.4.13 Redo Log
15.5 InnoDB Locking and Transaction Model
15.5.1 InnoDB Locking
15.5.2 InnoDB Transaction Model
15.5.3 Locks Set by Different SQL Statements in InnoDB
15.5.4 Phantom Rows
15.5.5 Deadlocks in InnoDB
15.6 InnoDB Configuration
15.6.1 InnoDB Startup Configuration
15.6.2 Configuring InnoDB for Read-Only Operation
15.6.3 InnoDB Buffer Pool Configuration
15.6.4 Configuring the Memory Allocator for InnoDB
15.6.5 Configuring InnoDB Change Buffering
15.6.6 Configuring Thread Concurrency for InnoDB
15.6.7 Configuring the Number of Background InnoDB I/O Threads
15.6.8 Configuring the InnoDB Master Thread I/O Rate
15.6.9 Configuring Spin Lock Polling
15.6.10 Configuring InnoDB Purge Scheduling
15.6.11 Configuring Optimizer Statistics for InnoDB
15.6.12 Configuring the Merge Threshold for Index Pages
15.7 InnoDB Tablespaces
15.7.1 Resizing the InnoDB System Tablespace
15.7.2 Changing the Number or Size of InnoDB Redo Log Files
15.7.3 Using Raw Disk Partitions for the System Tablespace
15.7.4 InnoDB File-Per-Table Tablespaces
15.7.5 Creating a File-Per-Table Tablespace Outside the Data Directory
15.7.6 Copying File-Per-Table Tablespaces to Another Server
15.7.7 Storing InnoDB Undo Logs in Separate Tablespaces
15.7.8 Truncating Undo Logs That Reside in Undo Tablespaces
15.7.9 InnoDB General Tablespaces
15.7.10 InnoDB Tablespace Encryption
15.8 InnoDB Tables and Indexes
15.8.1 Creating InnoDB Tables
15.8.2 Role of the .frm File for InnoDB Tables
15.8.3 Physical Row Structure of InnoDB Tables
15.8.4 Moving or Copying InnoDB Tables to Another Machine
15.8.5 Converting Tables from MyISAM to InnoDB
15.8.6 AUTO_INCREMENT Handling in InnoDB
15.8.7 InnoDB and FOREIGN KEY Constraints
15.8.8 Limits on InnoDB Tables
15.8.9 Clustered and Secondary Indexes
15.8.10 InnoDB FULLTEXT Indexes
15.8.11 Physical Structure of an InnoDB Index
15.8.12 Sorted Index Builds
15.9 InnoDB Table and Page Compression
15.9.1 InnoDB Table Compression
15.9.2 InnoDB Page Compression
15.10 InnoDB File-Format Management
15.10.1 Enabling File Formats
15.10.2 Verifying File Format Compatibility
15.10.3 Identifying the File Format in Use
15.10.4 Modifying the File Format
15.11 InnoDB Row Storage and Row Formats
15.11.1 Overview of InnoDB Row Storage
15.11.2 Specifying the Row Format for a Table
15.11.3 DYNAMIC and COMPRESSED Row Formats
15.11.4 COMPACT and REDUNDANT Row Formats
15.12 InnoDB Disk I/O and File Space Management
15.12.1 InnoDB Disk I/O
15.12.2 File Space Management
15.12.3 InnoDB Checkpoints
15.12.4 Defragmenting a Table
15.12.5 Reclaiming Disk Space with TRUNCATE TABLE
15.13 InnoDB and Online DDL
15.13.1 Overview of Online DDL
15.13.2 Performance and Concurrency Considerations for Online DDL
15.13.3 SQL Syntax for Online DDL
15.13.4 Combining or Separating DDL Statements
15.13.5 Examples of Online DDL
15.13.6 Implementation Details of Online DDL
15.13.7 How Crash Recovery Works with Online DDL
15.13.8 Online DDL for Partitioned InnoDB Tables
15.13.9 Limitations of Online DDL
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 System 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 Information 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 The InnoDB Recovery Process
15.18.2 Tablespace Discovery During Crash 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 Security Considerations for the InnoDB memcached Plugin
15.20.5 Writing Applications for the InnoDB memcached Plugin
15.20.6 The InnoDB memcached Plugin and Replication
15.20.7 InnoDB memcached Plugin Internals
15.20.8 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
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 High Availability and Scalability
17.1 Using ZFS Replication
17.1.1 Using ZFS for File System Replication
17.1.2 Configuring MySQL for ZFS Replication
17.1.3 Handling MySQL Recovery with ZFS
17.2 Using MySQL with memcached
17.2.1 Installing memcached
17.2.2 Using memcached
17.2.3 Developing a memcached Application
17.2.4 Getting memcached Statistics
17.2.5 memcached FAQ
18 Replication
18.1 Configuring Replication
18.1.1 Binary Log File Position Based Replication Configuration Overview
18.1.2 Setting Up Binary Log File Position Based Replication
18.1.3 Replication with Global Transaction Identifiers
18.1.4 MySQL Multi-Source Replication
18.1.5 Changing Replication Modes on Online Servers
18.1.6 Replication and Binary Logging Options and Variables
18.1.7 Common Replication Administration Tasks
18.2 Replication Implementation
18.2.1 Replication Formats
18.2.2 Replication Implementation Details
18.2.3 Replication Channels
18.2.4 Replication Relay and Status Logs
18.2.5 How Servers Evaluate Replication Filtering Rules
18.3 Replication Solutions
18.3.1 Using Replication for Backups
18.3.2 Handling an Unexpected Halt of a Replication Slave
18.3.3 Using Replication with Different Master and Slave Storage Engines
18.3.4 Using Replication for Scale-Out
18.3.5 Replicating Different Databases to Different Slaves
18.3.6 Improving Replication Performance
18.3.7 Switching Masters During Failover
18.3.8 Setting Up Replication to Use Secure Connections
18.3.9 Semisynchronous Replication
18.3.10 Delayed Replication
18.4 Replication Notes and Tips
18.4.1 Replication Features and Issues
18.4.2 Replication Compatibility Between MySQL Versions
18.4.3 Upgrading a Replication Setup
18.4.4 Troubleshooting Replication
18.4.5 How to Report Replication Bugs or Problems
19 Group Replication
19.1 Group Replication Background
19.1.1 Replication Technologies
19.1.2 Group Replication Use Cases
19.1.3 Group Replication Details
19.2 Getting Started
19.2.1 Deploying Group Replication in Single-Primary Mode
19.3 Monitoring Group Replication
19.3.1 Replication_group_member_stats
19.3.2 Replication_group_members
19.3.3 Replication_connection_status
19.3.4 Replication_applier_status
19.3.5 Group Replication Server States
19.4 Group Replication Operations
19.4.1 Deploying in Multi-Primary or Single-Primary Mode
19.4.2 Tuning Recovery
19.4.3 Network Partitioning
19.5 Group Replication Security
19.5.1 IP Address Whitelisting
19.5.2 Secure Socket Layer Support (SSL)
19.5.3 Virtual Private Networks (VPN)
19.6 Group Replication System Variables
19.7 Requirements and Limitations
19.7.1 Group Replication Requirements
19.7.2 Limitations
19.8 Frequently Asked Questions
19.9 Group Replication Technical Details
19.9.1 Group Replication Plugin Architecture
19.9.2 The Group
19.9.3 Data Manipulation Statements
19.9.4 Data Definition Statements
19.9.5 Distributed Recovery
19.9.6 Observability
19.9.7 Group Replication Performance
20 MySQL NDB Cluster 7.5
20.1 NDB Cluster Overview
20.1.1 NDB Cluster Core Concepts
20.1.2 NDB Cluster Nodes, Node Groups, Replicas, and Partitions
20.1.3 NDB Cluster Hardware, Software, and Networking Requirements
20.1.4 What is New in MySQL NDB Cluster 7.5
20.1.5 MySQL Server Using InnoDB Compared with NDB Cluster
20.1.6 Known Limitations of NDB Cluster
20.2 NDB Cluster Installation
20.2.1 The NDB Cluster Auto-Installer
20.2.2 Installation of NDB Cluster on Linux
20.2.3 Installing NDB Cluster on Windows
20.2.4 Initial Configuration of NDB Cluster
20.2.5 Initial Startup of NDB Cluster
20.2.6 NDB Cluster Example with Tables and Data
20.2.7 Safe Shutdown and Restart of NDB Cluster
20.2.8 Upgrading and Downgrading NDB Cluster
20.3 Configuration of NDB Cluster
20.3.1 Quick Test Setup of NDB Cluster
20.3.2 Overview of NDB Cluster Configuration Parameters, Options, and Variables
20.3.3 NDB Cluster Configuration Files
20.3.4 Using High-Speed Interconnects with NDB Cluster
20.4 NDB Cluster Programs
20.4.1 ndbd — The NDB Cluster Data Node Daemon
20.4.2 ndbinfo_select_all — Select From ndbinfo Tables
20.4.3 ndbmtd — The NDB Cluster Data Node Daemon (Multi-Threaded)
20.4.4 ndb_mgmd — The NDB Cluster Management Server Daemon
20.4.5 ndb_mgm — The NDB Cluster Management Client
20.4.6 ndb_blob_tool — Check and Repair BLOB and TEXT columns of NDB Cluster Tables
20.4.7 ndb_config — Extract NDB Cluster Configuration Information
20.4.8 ndb_cpcd — Automate Testing for NDB Development
20.4.9 ndb_delete_all — Delete All Rows from an NDB Table
20.4.10 ndb_desc — Describe NDB Tables
20.4.11 ndb_drop_index — Drop Index from an NDB Table
20.4.12 ndb_drop_table — Drop an NDB Table
20.4.13 ndb_error_reporter — NDB Error-Reporting Utility
20.4.14 ndb_index_stat — NDB Index Statistics Utility
20.4.15 ndb_print_backup_file — Print NDB Backup File Contents
20.4.16 ndb_print_file — Print NDB Disk Data File Contents
20.4.17 ndb_print_schema_file — Print NDB Schema File Contents
20.4.18 ndb_print_sys_file — Print NDB System File Contents
20.4.19 ndbd_redo_log_reader — Check and Print Content of Cluster Redo Log
20.4.20 ndb_restore — Restore an NDB Cluster Backup
20.4.21 ndb_select_all — Print Rows from an NDB Table
20.4.22 ndb_select_count — Print Row Counts for NDB Tables
20.4.23 ndb_setup.py — Start browser-based Auto-Installer for NDB Cluster
20.4.24 ndb_show_tables — Display List of NDB Tables
20.4.25 ndb_size.pl — NDBCLUSTER Size Requirement Estimator
20.4.26 ndb_waiter — Wait for NDB Cluster to Reach a Given Status
20.4.27 Options Common to NDB Cluster Programs — Options Common to NDB Cluster Programs
20.5 Management of NDB Cluster
20.5.1 Summary of NDB Cluster Start Phases
20.5.2 Commands in the NDB Cluster Management Client
20.5.3 Online Backup of NDB Cluster
20.5.4 MySQL Server Usage for NDB Cluster
20.5.5 Performing a Rolling Restart of an NDB Cluster
20.5.6 Event Reports Generated in NDB Cluster
20.5.7 NDB Cluster Log Messages
20.5.8 NDB Cluster Single User Mode
20.5.9 Quick Reference: NDB Cluster SQL Statements
20.5.10 The ndbinfo NDB Cluster Information Database
20.5.11 INFORMATION_SCHEMA Tables for NDB Cluster
20.5.12 NDB Cluster Security Issues
20.5.13 NDB Cluster Disk Data Tables
20.5.14 Adding NDB Cluster Data Nodes Online
20.5.15 Distributed MySQL Privileges for NDB Cluster
20.5.16 NDB API Statistics Counters and Variables
20.6 NDB Cluster Replication
20.6.1 NDB Cluster Replication: Abbreviations and Symbols
20.6.2 General Requirements for NDB Cluster Replication
20.6.3 Known Issues in NDB Cluster Replication
20.6.4 NDB Cluster Replication Schema and Tables
20.6.5 Preparing the NDB Cluster for Replication
20.6.6 Starting NDB Cluster Replication (Single Replication Channel)
20.6.7 Using Two Replication Channels for NDB Cluster Replication
20.6.8 Implementing Failover with NDB Cluster Replication
20.6.9 NDB Cluster Backups With NDB Cluster Replication
20.6.10 NDB Cluster Replication: Multi-Master and Circular Replication
20.6.11 NDB Cluster Replication Conflict Resolution
20.7 NDB Cluster Release Notes
21 Partitioning
21.1 Overview of Partitioning in MySQL
21.2 Partitioning Types
21.2.1 RANGE Partitioning
21.2.2 LIST Partitioning
21.2.3 COLUMNS Partitioning
21.2.4 HASH Partitioning
21.2.5 KEY Partitioning
21.2.6 Subpartitioning
21.2.7 How MySQL Partitioning Handles NULL
21.3 Partition Management
21.3.1 Management of RANGE and LIST Partitions
21.3.2 Management of HASH and KEY Partitions
21.3.3 Exchanging Partitions and Subpartitions with Tables
21.3.4 Maintenance of Partitions
21.3.5 Obtaining Information About Partitions
21.4 Partition Pruning
21.5 Partition Selection
21.6 Restrictions and Limitations on Partitioning
21.6.1 Partitioning Keys, Primary Keys, and Unique Keys
21.6.2 Partitioning Limitations Relating to Storage Engines
21.6.3 Partitioning Limitations Relating to Functions
21.6.4 Partitioning and Locking
22 Stored Programs and Views
22.1 Defining Stored Programs
22.2 Using Stored Routines (Procedures and Functions)
22.2.1 Stored Routine Syntax
22.2.2 Stored Routines and MySQL Privileges
22.2.3 Stored Routine Metadata
22.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()
22.3 Using Triggers
22.3.1 Trigger Syntax and Examples
22.3.2 Trigger Metadata
22.4 Using the Event Scheduler
22.4.1 Event Scheduler Overview
22.4.2 Event Scheduler Configuration
22.4.3 Event Syntax
22.4.4 Event Metadata
22.4.5 Event Scheduler Status
22.4.6 The Event Scheduler and MySQL Privileges
22.5 Using Views
22.5.1 View Syntax
22.5.2 View Processing Algorithms
22.5.3 Updatable and Insertable Views
22.5.4 The View WITH CHECK OPTION Clause
22.5.5 View Metadata
22.6 Access Control for Stored Programs and Views
22.7 Binary Logging of Stored Programs
23 INFORMATION_SCHEMA Tables
23.1 The INFORMATION_SCHEMA CHARACTER_SETS Table
23.2 The INFORMATION_SCHEMA COLLATIONS Table
23.3 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
23.4 The INFORMATION_SCHEMA COLUMNS Table
23.5 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
23.6 The INFORMATION_SCHEMA ENGINES Table
23.7 The INFORMATION_SCHEMA EVENTS Table
23.8 The INFORMATION_SCHEMA FILES Table
23.9 The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables
23.10 The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables
23.11 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
23.12 The INFORMATION_SCHEMA ndb_transid_mysql_connection_map Table
23.13 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table
23.14 The INFORMATION_SCHEMA PARAMETERS Table
23.15 The INFORMATION_SCHEMA PARTITIONS Table
23.16 The INFORMATION_SCHEMA PLUGINS Table
23.17 The INFORMATION_SCHEMA PROCESSLIST Table
23.18 The INFORMATION_SCHEMA PROFILING Table
23.19 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table
23.20 The INFORMATION_SCHEMA ROUTINES Table
23.21 The INFORMATION_SCHEMA SCHEMATA Table
23.22 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
23.23 The INFORMATION_SCHEMA STATISTICS Table
23.24 The INFORMATION_SCHEMA TABLES Table
23.25 The INFORMATION_SCHEMA TABLESPACES Table
23.26 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
23.27 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
23.28 The INFORMATION_SCHEMA TRIGGERS Table
23.29 The INFORMATION_SCHEMA USER_PRIVILEGES Table
23.30 The INFORMATION_SCHEMA VIEWS Table
23.31 InnoDB INFORMATION_SCHEMA Tables
23.31.1 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table
23.31.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table
23.31.3 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table
23.31.4 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables
23.31.5 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables
23.31.6 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables
23.31.7 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table
23.31.8 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table
23.31.9 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table
23.31.10 The INFORMATION_SCHEMA INNODB_FT_DELETED Table
23.31.11 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table
23.31.12 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table
23.31.13 The INFORMATION_SCHEMA INNODB_LOCKS Table
23.31.14 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table
23.31.15 The INFORMATION_SCHEMA INNODB_METRICS Table
23.31.16 The INFORMATION_SCHEMA INNODB_SYS_COLUMNS Table
23.31.17 The INFORMATION_SCHEMA INNODB_SYS_DATAFILES Table
23.31.18 The INFORMATION_SCHEMA INNODB_SYS_FIELDS Table
23.31.19 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN Table
23.31.20 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN_COLS Table
23.31.21 The INFORMATION_SCHEMA INNODB_SYS_INDEXES Table
23.31.22 The INFORMATION_SCHEMA INNODB_SYS_TABLES Table
23.31.23 The INFORMATION_SCHEMA INNODB_SYS_TABLESPACES Table
23.31.24 The INFORMATION_SCHEMA INNODB_SYS_TABLESTATS View
23.31.25 The INFORMATION_SCHEMA INNODB_SYS_VIRTUAL Table
23.31.26 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table
23.31.27 The INFORMATION_SCHEMA INNODB_TRX Table
23.32 Thread Pool INFORMATION_SCHEMA Tables
23.32.1 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATE Table
23.32.2 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATS Table
23.32.3 The INFORMATION_SCHEMA TP_THREAD_STATE Table
23.33 Connection-Control INFORMATION_SCHEMA Tables
23.33.1 The INFORMATION_SCHEMA CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS Table
23.34 Extensions to SHOW Statements
24 MySQL Performance Schema
24.1 Performance Schema Quick Start
24.2 Performance Schema Build and Startup Configuration
24.2.1 Performance Schema Build Configuration
24.2.2 Performance Schema Startup Configuration
24.3 Performance Schema Runtime Configuration
24.3.1 Performance Schema Event Timing
24.3.2 Performance Schema Event Filtering
24.3.3 Event Pre-Filtering
24.3.4 Pre-Filtering by Instrument
24.3.5 Pre-Filtering by Object
24.3.6 Pre-Filtering by Thread
24.3.7 Pre-Filtering by Consumer
24.3.8 Example Consumer Configurations
24.3.9 Naming Instruments or Consumers for Filtering Operations
24.3.10 Determining What Is Instrumented
24.4 Performance Schema Queries
24.5 Performance Schema Instrument Naming Conventions
24.6 Performance Schema Status Monitoring
24.7 Performance Schema Atom and Molecule Events
24.8 Performance Schema Statement Digests
24.9 Performance Schema General Table Characteristics
24.10 Performance Schema Table Descriptions
24.10.1 Performance Schema Table Index
24.10.2 Performance Schema Setup Tables
24.10.3 Performance Schema Instance Tables
24.10.4 Performance Schema Wait Event Tables
24.10.5 Performance Schema Stage Event Tables
24.10.6 Performance Schema Statement Event Tables
24.10.7 Performance Schema Transaction Tables
24.10.8 Performance Schema Connection Tables
24.10.9 Performance Schema Connection Attribute Tables
24.10.10 Performance Schema User Variable Tables
24.10.11 Performance Schema Replication Tables
24.10.12 Performance Schema Lock Tables
24.10.13 Performance Schema System Variable Tables
24.10.14 Performance Schema Status Variable Tables
24.10.15 Performance Schema Summary Tables
24.10.16 Performance Schema Miscellaneous Tables
24.11 Performance Schema Option and Variable Reference
24.12 Performance Schema Command Options
24.13 Performance Schema System Variables
24.14 Performance Schema Status Variables
24.15 The Performance Schema Memory-Allocation Model
24.16 Performance Schema and Plugins
24.17 Using the Performance Schema to Diagnose Problems
24.17.1 Query Profiling Using Performance Schema
24.18 Migrating to Performance Schema System and Status Variable Tables
25 MySQL sys Schema
25.1 Prerequisites for Using the sys Schema
25.2 Using the sys Schema
25.3 sys Schema Progress Reporting
25.4 sys Schema Object Reference
25.4.1 sys Schema Object Index
25.4.2 sys Schema Tables and Triggers
25.4.3 sys Schema Views
25.4.4 sys Schema Stored Procedures
25.4.5 sys Schema Stored Functions
26 Connectors and APIs
26.1 MySQL Connector/ODBC
26.2 MySQL Connector/Net
26.3 MySQL Connector/J
26.4 MySQL Connector/C++
26.5 MySQL Connector/C
26.6 MySQL Connector/Python
26.7 libmysqld, the Embedded MySQL Server Library
26.7.1 Compiling Programs with libmysqld
26.7.2 Restrictions When Using the Embedded MySQL Server
26.7.3 Options with the Embedded Server
26.7.4 Embedded Server Examples
26.8 MySQL C API
26.8.1 MySQL C API Implementations
26.8.2 Simultaneous MySQL Server and Connector/C Installations
26.8.3 Example C API Client Programs
26.8.4 Building and Running C API Client Programs
26.8.5 C API Data Structures
26.8.6 C API Function Overview
26.8.7 C API Function Descriptions
26.8.8 C API Prepared Statements
26.8.9 C API Prepared Statement Data Structures
26.8.10 C API Prepared Statement Function Overview
26.8.11 C API Prepared Statement Function Descriptions
26.8.12 C API Threaded Function Descriptions
26.8.13 C API Embedded Server Function Descriptions
26.8.14 C API Client Plugin Functions
26.8.15 Common Questions and Problems When Using the C API
26.8.16 Controlling Automatic Reconnection Behavior
26.8.17 C API Support for Multiple Statement Execution
26.8.18 C API Prepared Statement Problems
26.8.19 C API Prepared Statement Handling of Date and Time Values
26.8.20 C API Support for Prepared CALL Statements
26.9 MySQL PHP API
26.10 MySQL Perl API
26.11 MySQL Python API
26.12 MySQL Ruby APIs
26.12.1 The MySQL/Ruby API
26.12.2 The Ruby/MySQL API
26.13 MySQL Tcl API
26.14 MySQL Eiffel Wrapper
27 Extending MySQL
27.1 MySQL Internals
27.1.1 MySQL Threads
27.1.2 The MySQL Test Suite
27.2 The MySQL Plugin API
27.2.1 Types of Plugins
27.2.2 Plugin API Characteristics
27.2.3 Plugin API Components
27.2.4 Writing Plugins
27.3 MySQL Services for Plugins
27.3.1 The Locking Service
27.3.2 The Keyring Service
27.4 Adding New Functions to MySQL
27.4.1 Features of the User-Defined Function Interface
27.4.2 Adding a New User-Defined Function
27.4.3 Adding a New Native Function
27.5 Debugging and Porting MySQL
27.5.1 Debugging a MySQL Server
27.5.2 Debugging a MySQL Client
27.5.3 The DBUG Package
28 MySQL Enterprise Edition
28.1 MySQL Enterprise Monitor Overview
28.2 MySQL Enterprise Backup Overview
28.3 MySQL Enterprise Security Overview
28.4 MySQL Enterprise Encryption Overview
28.5 MySQL Enterprise Audit Overview
28.6 MySQL Enterprise Firewall Overview
28.7 MySQL Enterprise Thread Pool Overview
29 MySQL Workbench
A MySQL 5.7 Frequently Asked Questions
A.1 MySQL 5.7 FAQ: General
A.2 MySQL 5.7 FAQ: Storage Engines
A.3 MySQL 5.7 FAQ: Server SQL Mode
A.4 MySQL 5.7 FAQ: Stored Procedures and Functions
A.5 MySQL 5.7 FAQ: Triggers
A.6 MySQL 5.7 FAQ: Views
A.7 MySQL 5.7 FAQ: INFORMATION_SCHEMA
A.8 MySQL 5.7 FAQ: Migration
A.9 MySQL 5.7 FAQ: Security
A.10 MySQL 5.7 FAQ: MySQL Cluster
A.11 MySQL 5.7 FAQ: MySQL Chinese, Japanese, and Korean Character Sets
A.12 MySQL 5.7 FAQ: Connectors & APIs
A.13 MySQL 5.7 FAQ: Replication
A.14 MySQL 5.7 FAQ: MySQL Enterprise Thread Pool
A.15 MySQL 5.7 FAQ: InnoDB Change Buffer
A.16 MySQL 5.7 FAQ: InnoDB Tablespace Encryption
A.17 MySQL 5.7 FAQ: Virtualization Support
B Errors, Error Codes, and Common Problems
B.1 Sources of Error Information
B.2 Types of Error Values
B.3 Server Error Codes and Messages
B.4 Client Error Codes and Messages
B.5 Problems and Common Errors
B.5.1 How to Determine What Is Causing a Problem
B.5.2 Common Errors When Using MySQL Programs
B.5.3 Administration-Related Issues
B.5.4 Query-Related Issues
B.5.5 Optimizer-Related Issues
B.5.6 Table Definition-Related Issues
B.5.7 Known Issues in MySQL
C Restrictions and Limits
C.1 Restrictions on Stored Programs
C.2 Restrictions on Condition Handling
C.3 Restrictions on Server-Side Cursors
C.4 Restrictions on Subqueries
C.5 Restrictions on Views
C.6 Restrictions on XA Transactions
C.7 Restrictions on Character Sets
C.8 Restrictions on Performance Schema
C.9 Restrictions on Pluggable Authentication
C.10 Limits in MySQL
C.10.1 Limits on Joins
C.10.2 Limits on Number of Databases and Tables
C.10.3 Limits on Table Size
C.10.4 Limits on Table Column Count and Row Size
C.10.5 Limits Imposed by .frm File Structure
C.10.6 Windows Platform Limitations
D 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