MySQL 5.7 Reference Manual

Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6

Abstract

This is the MySQL Reference Manual. It documents MySQL 5.7 through 5.7.48, as well as NDB Cluster releases based on version 7.5 of NDB through 5.7.44-ndb-7.5.36, respectively. It may include documentation of features of MySQL versions that have not yet been released. For information about which versions have been released, see the MySQL 5.7 Release Notes.

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, including licensing information, see the Preface and Legal Notices.

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

Document generated on: 2024-12-19 (revision: 80567)

---

Table of Contents

Preface and Legal Notices

1 General Information

1.1 About This Manual

1.2 Overview of the MySQL Database Management System

1.2.1 What is MySQL?

1.2.2 The Main Features of MySQL

1.2.3 History of MySQL

1.3 What Is New in MySQL 5.7

1.4 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 5.7

1.5 How to Report Bugs or Problems

1.6 MySQL Standards Compliance

1.6.1 MySQL Extensions to Standard SQL

1.6.2 MySQL Differences from Standard SQL

1.6.3 How MySQL Deals with Constraints

2 Installing and Upgrading MySQL

2.1 General Installation Guidance

2.1.1 Supported Platforms

2.1.2 Which MySQL Version and Distribution to Install

2.1.3 How to Get MySQL

2.1.4 Verifying Package Integrity Using MD5 Checksums or GnuPG

2.1.5 Installation Layouts

2.1.6 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 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 Deploying MySQL on Linux with Docker

2.5.8 Installing MySQL on Linux from the Native Software Repositories

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

2.7.1 Installing MySQL on Solaris Using a Solaris PKG

2.8 Installing MySQL from Source

2.8.1 Source Installation Methods

2.8.2 Source Installation Prerequisites

2.8.3 MySQL Layout for Source Installation

2.8.4 Installing MySQL Using a Standard Source Distribution

2.8.5 Installing MySQL Using a Development Source Tree

2.8.6 Configuring SSL Library Support

2.8.7 MySQL Source-Configuration Options

2.8.8 Dealing with Problems Compiling MySQL

2.8.9 MySQL Configuration and Third-Party Tools

2.9 Postinstallation Setup and Testing

2.9.1 Initializing the Data Directory

2.9.2 Starting the Server

2.9.3 Testing the Server

2.9.4 Securing the Initial MySQL Account

2.9.5 Starting and Stopping MySQL Automatically

2.10 Upgrading MySQL

2.10.1 Before You Begin

2.10.2 Upgrade Paths

2.10.3 Changes in MySQL 5.7

2.10.4 Upgrading MySQL Binary or Package-based Installations on Unix/Linux

2.10.5 Upgrading MySQL with the MySQL Yum Repository

2.10.6 Upgrading MySQL with the MySQL APT Repository

2.10.7 Upgrading MySQL with the MySQL SLES Repository

2.10.8 Upgrading MySQL on Windows

2.10.9 Upgrading a Docker Installation of MySQL

2.10.10 Upgrading MySQL with Directly-Downloaded RPM Packages

2.10.11 Upgrade Troubleshooting

2.10.12 Rebuilding or Repairing Tables or Indexes

2.10.13 Copying MySQL Databases to Another Machine

2.11 Downgrading MySQL

2.11.1 Before You Begin

2.11.2 Downgrade Paths

2.11.3 Downgrade Notes

2.11.4 Downgrading Binary and Package-based Installations on Unix/Linux

2.11.5 Downgrade Troubleshooting

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 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 Connection Transport Protocols

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_install_db — Initialize MySQL Data Directory

4.4.3 mysql_plugin — Configure MySQL Server Plugins

4.4.4 mysql_secure_installation — Improve MySQL Installation Security

4.4.5 mysql_ssl_rsa_setup — Create SSL/RSA Files

4.4.6 mysql_tzinfo_to_sql — Load the Time Zone Tables

4.4.7 mysql_upgrade — Check and Upgrade MySQL Tables

4.5 Client Programs

4.5.1 mysql — The MySQL Command-Line Client

4.5.2 mysqladmin — A MySQL Server Administration Program

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 — A Load Emulation Client

4.6 Administrative and Utility Programs

4.6.1 innochecksum — Offline InnoDB File Checksum Utility

4.6.2 myisam_ftdump — Display Full-Text Index information

4.6.3 myisamchk — MyISAM Table-Maintenance Utility

4.6.4 myisamlog — Display MyISAM Log File Contents

4.6.5 myisampack — Generate Compressed, Read-Only MyISAM Tables

4.6.6 mysql_config_editor — MySQL Configuration Utility

4.6.7 mysqlbinlog — Utility for Processing Binary Log Files

4.6.8 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.7.3 resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols

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 replace — A String-Replacement Utility

4.8.4 resolveip — Resolve Host name to IP Address or Vice Versa

4.8.5 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 Option, System Variable, and Status Variable Reference

5.1.4 Server System Variable Reference

5.1.5 Server Status Variable Reference

5.1.6 Server Command Options

5.1.7 Server System Variables

5.1.8 Using System Variables

5.1.9 Server Status Variables

5.1.10 Server SQL Modes

5.1.11 Connection Management

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

5.1.16 The Server Shutdown Process

5.2 The MySQL Data Directory

5.3 The mysql System Database

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 Plugins

5.5.1 Installing and Uninstalling Plugins

5.5.2 Obtaining Server Plugin Information

5.5.3 MySQL Enterprise Thread Pool

5.5.4 The Rewriter Query Rewrite Plugin

5.5.5 Version Tokens

5.5.6 MySQL Plugin Services

5.6 MySQL Server Loadable Functions

5.6.1 Installing and Uninstalling Loadable Functions

5.6.2 Obtaining Information About Loadable Functions

5.7 Running Multiple MySQL Instances on One Machine

5.7.1 Setting Up Multiple Data Directories

5.7.2 Running Multiple MySQL Instances on Windows

5.7.3 Running Multiple MySQL Instances on Unix

5.7.4 Using Client Programs in a Multiple-Server Environment

5.8 Debugging MySQL

5.8.1 Debugging a MySQL Server

5.8.2 Debugging a MySQL Client

5.8.3 The DBUG Package

5.8.4 Tracing mysqld Using DTrace

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 Considerations for 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 Access Control, Stage 1: Connection Verification

6.2.6 Access Control, Stage 2: Request Verification

6.2.7 Adding Accounts, Assigning Privileges, and Dropping Accounts

6.2.8 Reserved Accounts

6.2.9 When Privilege Changes Take Effect

6.2.10 Assigning Account Passwords

6.2.11 Password Management

6.2.12 Server Handling of Expired Passwords

6.2.13 Pluggable Authentication

6.2.14 Proxy Users

6.2.15 Account Locking

6.2.16 Setting Account Resource Limits

6.2.17 Troubleshooting Problems Connecting to MySQL

6.2.18 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 SSL Library-Dependent Capabilities

6.3.5 Connecting to MySQL Remotely from Windows with SSH

6.4 Security Plugins

6.4.1 Authentication Plugins

6.4.2 The Connection-Control Plugins

6.4.3 The Password Validation Plugin

6.4.4 The MySQL Keyring

6.4.5 MySQL Enterprise Audit

6.4.6 MySQL Enterprise Firewall

6.5 MySQL Enterprise Data Masking and De-Identification

6.5.1 MySQL Enterprise Data Masking and De-Identification Elements

6.5.2 Installing or Uninstalling MySQL Enterprise Data Masking and De-Identification

6.5.3 Using MySQL Enterprise Data Masking and De-Identification

6.5.4 MySQL Enterprise Data Masking and De-Identification Function Reference

6.5.5 MySQL Enterprise Data Masking and De-Identification Function Descriptions

6.6 MySQL Enterprise Encryption

6.6.1 MySQL Enterprise Encryption Installation

6.6.2 MySQL Enterprise Encryption Usage and Examples

6.6.3 MySQL Enterprise Encryption Function Reference

6.6.4 MySQL Enterprise Encryption Function Descriptions

6.7 SELinux

6.7.1 Check if SELinux is Enabled

6.7.2 Changing the SELinux Mode

6.7.3 MySQL Server SELinux Policies

6.7.4 SELinux File Context

6.7.5 SELinux TCP Port Context

6.7.6 Troubleshooting SELinux

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

7.5.1 Point-in-Time Recovery Using Binary Log

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, and View References

8.2.3 Optimizing INFORMATION_SCHEMA Queries

8.2.4 Optimizing Data Change Statements

8.2.5 Optimizing Database Privileges

8.2.6 Other Optimization Tips

8.3 Optimization and Indexes

8.3.1 How MySQL Uses Indexes

8.3.2 Primary Key Optimization

8.3.3 Foreign Key Optimization

8.3.4 Column Indexes

8.3.5 Multiple-Column Indexes

8.3.6 Verifying Index Usage

8.3.7 InnoDB and MyISAM Index Statistics Collection

8.3.8 Comparison of B-Tree and Hash Indexes

8.3.9 Use of Index Extensions

8.3.10 Optimizer Use of Generated Column Indexes

8.3.11 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.10 Buffering and Caching

8.10.1 InnoDB Buffer Pool Optimization

8.10.2 The MyISAM Key Cache

8.10.3 The MySQL Query Cache

8.10.4 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 System Factors

8.12.2 Optimizing Disk I/O

8.12.3 Using Symbolic Links

8.12.4 Optimizing Memory Use

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 Server Thread (Process) Information

8.14.1 Accessing the Process List

8.14.2 Thread Command Values

8.14.3 General Thread States

8.14.4 Query Cache Thread States

8.14.5 Replication Source Thread States

8.14.6 Replication Replica I/O Thread States

8.14.7 Replication Replica SQL Thread States

8.14.8 Replication Replica Connection Thread States

8.14.9 NDB Cluster Thread States

8.14.10 Event Scheduler Thread States

8.15 Tracing the Optimizer

8.15.1 Typical Usage

8.15.2 System Variables Controlling Tracing

8.15.3 Traceable Statements

8.15.4 Tuning Trace Purging

8.15.5 Tracing Memory Usage

8.15.6 Privilege Checking

8.15.7 Interaction with the --debug Option

8.15.8 The optimizer_trace System Variable

8.15.9 The end_markers_in_json System Variable

8.15.10 Selecting Optimizer Features to Trace

8.15.11 Trace General Structure

8.15.12 Example

8.15.13 Displaying Traces in Other Applications

8.15.14 Preventing the Use of Optimizer Trace

8.15.15 Testing Optimizer Trace

8.15.16 Optimizer Trace Implementation

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 Comments

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 2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR

11.2.6 Automatic Initialization and Updating for TIMESTAMP and DATETIME

11.2.7 Fractional Seconds in Time Values

11.2.8 What Calendar Is Used By MySQL?

11.2.9 Conversion Between Date and Time Types

11.2.10 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 Creating Spatial Columns

11.4.6 Populating Spatial Columns

11.4.7 Fetching Spatial Data

11.4.8 Optimizing Spatial Analysis

11.4.9 Creating Spatial Indexes

11.4.10 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 Built-In Function and Operator Reference

12.2 Loadable Function Reference

12.3 Type Conversion in Expression Evaluation

12.4 Operators

12.4.1 Operator Precedence

12.4.2 Comparison Functions and Operators

12.4.3 Logical Operators

12.4.4 Assignment Operators

12.5 Flow Control Functions

12.6 Numeric Functions and Operators

12.6.1 Arithmetic Operators

12.6.2 Mathematical Functions

12.7 Date and Time Functions

12.8 String Functions and Operators

12.8.1 String Comparison Functions and Operators

12.8.2 Regular Expressions

12.8.3 Character Set and Collation of Function Results

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 User-Defined 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 Utility Functions

12.18 Functions Used with Global Transaction Identifiers (GTIDs)

12.19 Aggregate Functions

12.19.1 Aggregate Function Descriptions

12.19.2 GROUP BY Modifiers

12.19.3 MySQL Handling of GROUP BY

12.19.4 Detection of Functional Dependence

12.20 Miscellaneous Functions

12.21 Precision Math

12.21.1 Types of Numeric Values

12.21.2 DECIMAL Data Type Characteristics

12.21.3 Expression Handling

12.21.4 Rounding Behavior

12.21.5 Precision Math Examples

13 SQL Statements

13.1 Data Definition Statements

13.1.1 ALTER DATABASE Statement

13.1.2 ALTER EVENT Statement

13.1.3 ALTER FUNCTION Statement

13.1.4 ALTER INSTANCE Statement

13.1.5 ALTER LOGFILE GROUP Statement

13.1.6 ALTER PROCEDURE Statement

13.1.7 ALTER SERVER Statement

13.1.8 ALTER TABLE Statement

13.1.9 ALTER TABLESPACE Statement

13.1.10 ALTER VIEW Statement

13.1.11 CREATE DATABASE Statement

13.1.12 CREATE EVENT Statement

13.1.13 CREATE FUNCTION Statement

13.1.14 CREATE INDEX Statement

13.1.15 CREATE LOGFILE GROUP Statement

13.1.16 CREATE PROCEDURE and CREATE FUNCTION Statements

13.1.17 CREATE SERVER Statement

13.1.18 CREATE TABLE Statement

13.1.19 CREATE TABLESPACE Statement

13.1.20 CREATE TRIGGER Statement

13.1.21 CREATE VIEW Statement

13.1.22 DROP DATABASE Statement

13.1.23 DROP EVENT Statement

13.1.24 DROP FUNCTION Statement

13.1.25 DROP INDEX Statement

13.1.26 DROP LOGFILE GROUP Statement

13.1.27 DROP PROCEDURE and DROP FUNCTION Statements

13.1.28 DROP SERVER Statement

13.1.29 DROP TABLE Statement

13.1.30 DROP TABLESPACE Statement

13.1.31 DROP TRIGGER Statement

13.1.32 DROP VIEW Statement

13.1.33 RENAME TABLE Statement

13.1.34 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 INSERT Statement

13.2.6 LOAD DATA Statement

13.2.7 LOAD XML Statement

13.2.8 REPLACE Statement

13.2.9 SELECT Statement

13.2.10 Subqueries

13.2.11 UPDATE Statement

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 TABLES and UNLOCK TABLES Statements

13.3.6 SET TRANSACTION Statement

13.3.7 XA Transactions

13.4 Replication Statements

13.4.1 SQL Statements for Controlling Replication Source Servers

13.4.2 SQL Statements for Controlling Replica 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 Statements

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.7 Database Administration Statements

13.7.1 Account Management Statements

13.7.2 Table Maintenance Statements

13.7.3 Plugin and Loadable Function Statements

13.7.4 SET Statements

13.7.5 SHOW Statements

13.7.6 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 The InnoDB Storage Engine

14.1 Introduction to InnoDB

14.1.1 Benefits of Using InnoDB Tables

14.1.2 Best Practices for InnoDB Tables

14.1.3 Verifying that InnoDB is the Default Storage Engine

14.1.4 Testing and Benchmarking with InnoDB

14.1.5 Turning Off InnoDB

14.2 InnoDB and the ACID Model

14.3 InnoDB Multi-Versioning

14.4 InnoDB Architecture

14.5 InnoDB In-Memory Structures

14.5.1 Buffer Pool

14.5.2 Change Buffer

14.5.3 Adaptive Hash Index

14.5.4 Log Buffer

14.6 InnoDB On-Disk Structures

14.6.1 Tables

14.6.2 Indexes

14.6.3 Tablespaces

14.6.4 InnoDB Data Dictionary

14.6.5 Doublewrite Buffer

14.6.6 Redo Log

14.6.7 Undo Logs

14.7 InnoDB Locking and Transaction Model

14.7.1 InnoDB Locking

14.7.2 InnoDB Transaction Model

14.7.3 Locks Set by Different SQL Statements in InnoDB

14.7.4 Phantom Rows

14.7.5 Deadlocks in InnoDB

14.8 InnoDB Configuration

14.8.1 InnoDB Startup Configuration

14.8.2 Configuring InnoDB for Read-Only Operation

14.8.3 InnoDB Buffer Pool Configuration

14.8.4 Configuring the Memory Allocator for InnoDB

14.8.5 Configuring Thread Concurrency for InnoDB

14.8.6 Configuring the Number of Background InnoDB I/O Threads

14.8.7 Using Asynchronous I/O on Linux

14.8.8 Configuring InnoDB I/O Capacity

14.8.9 Configuring Spin Lock Polling

14.8.10 Purge Configuration

14.8.11 Configuring Optimizer Statistics for InnoDB

14.8.12 Configuring the Merge Threshold for Index Pages

14.9 InnoDB Table and Page Compression

14.9.1 InnoDB Table Compression

14.9.2 InnoDB Page Compression

14.10 InnoDB File-Format Management

14.10.1 Enabling File Formats

14.10.2 Verifying File Format Compatibility

14.10.3 Identifying the File Format in Use

14.10.4 Modifying the File Format

14.11 InnoDB Row Formats

14.12 InnoDB Disk I/O and File Space Management

14.12.1 InnoDB Disk I/O

14.12.2 File Space Management

14.12.3 InnoDB Checkpoints

14.12.4 Defragmenting a Table

14.12.5 Reclaiming Disk Space with TRUNCATE TABLE

14.13 InnoDB and Online DDL

14.13.1 Online DDL Operations

14.13.2 Online DDL Performance and Concurrency

14.13.3 Online DDL Space Requirements

14.13.4 Simplifying DDL Statements with Online DDL

14.13.5 Online DDL Failure Conditions

14.13.6 Online DDL Limitations

14.14 InnoDB Data-at-Rest Encryption

14.15 InnoDB Startup Options and System Variables

14.16 InnoDB INFORMATION_SCHEMA Tables

14.16.1 InnoDB INFORMATION_SCHEMA Tables about Compression

14.16.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Information

14.16.3 InnoDB INFORMATION_SCHEMA System Tables

14.16.4 InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables

14.16.5 InnoDB INFORMATION_SCHEMA Buffer Pool Tables

14.16.6 InnoDB INFORMATION_SCHEMA Metrics Table

14.16.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table

14.16.8 Retrieving InnoDB Tablespace Metadata from INFORMATION_SCHEMA.FILES

14.17 InnoDB Integration with MySQL Performance Schema

14.17.1 Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema

14.17.2 Monitoring InnoDB Mutex Waits Using Performance Schema

14.18 InnoDB Monitors

14.18.1 InnoDB Monitor Types

14.18.2 Enabling InnoDB Monitors

14.18.3 InnoDB Standard Monitor and Lock Monitor Output

14.19 InnoDB Backup and Recovery

14.19.1 InnoDB Backup

14.19.2 InnoDB Recovery

14.20 InnoDB and MySQL Replication

14.21 InnoDB memcached Plugin

14.21.1 Benefits of the InnoDB memcached Plugin

14.21.2 InnoDB memcached Architecture

14.21.3 Setting Up the InnoDB memcached Plugin

14.21.4 Security Considerations for the InnoDB memcached Plugin

14.21.5 Writing Applications for the InnoDB memcached Plugin

14.21.6 The InnoDB memcached Plugin and Replication

14.21.7 InnoDB memcached Plugin Internals

14.21.8 Troubleshooting the InnoDB memcached Plugin

14.22 InnoDB Troubleshooting

14.22.1 Troubleshooting InnoDB I/O Problems

14.22.2 Forcing InnoDB Recovery

14.22.3 Troubleshooting InnoDB Data Dictionary Operations

14.22.4 InnoDB Error Handling

14.23 InnoDB Limits

14.24 InnoDB Restrictions and Limitations

15 Alternative Storage Engines

15.1 Setting the Storage Engine

15.2 The MyISAM Storage Engine

15.2.1 MyISAM Startup Options

15.2.2 Space Needed for Keys

15.2.3 MyISAM Table Storage Formats

15.2.4 MyISAM Table Problems

15.3 The MEMORY Storage Engine

15.4 The CSV Storage Engine

15.4.1 Repairing and Checking CSV Tables

15.4.2 CSV Limitations

15.5 The ARCHIVE Storage Engine

15.6 The BLACKHOLE Storage Engine

15.7 The MERGE Storage Engine

15.7.1 MERGE Table Advantages and Disadvantages

15.7.2 MERGE Table Problems

15.8 The FEDERATED Storage Engine

15.8.1 FEDERATED Storage Engine Overview

15.8.2 How to Create FEDERATED Tables

15.8.3 FEDERATED Storage Engine Notes and Tips

15.8.4 FEDERATED Storage Engine Resources

15.9 The EXAMPLE Storage Engine

15.10 Other Storage Engines

15.11 Overview of MySQL Storage Engine Architecture

15.11.1 Pluggable Storage Engine Architecture

15.11.2 The Common Database Server Layer

16 Replication

16.1 Configuring Replication

16.1.1 Binary Log File Position Based Replication Configuration Overview

16.1.2 Setting Up Binary Log File Position Based Replication

16.1.3 Replication with Global Transaction Identifiers

16.1.4 Changing Replication Modes on Online Servers

16.1.5 MySQL Multi-Source Replication

16.1.6 Replication and Binary Logging Options and Variables

16.1.7 Common Replication Administration Tasks

16.2 Replication Implementation

16.2.1 Replication Formats

16.2.2 Replication Channels

16.2.3 Replication Threads

16.2.4 Relay Log and Replication Metadata Repositories

16.2.5 How Servers Evaluate Replication Filtering Rules

16.3 Replication Solutions

16.3.1 Using Replication for Backups

16.3.2 Handling an Unexpected Halt of a Replica

16.3.3 Using Replication with Different Source and Replica Storage Engines

16.3.4 Using Replication for Scale-Out

16.3.5 Replicating Different Databases to Different Replicas

16.3.6 Improving Replication Performance

16.3.7 Switching Sources During Failover

16.3.8 Setting Up Replication to Use Encrypted Connections

16.3.9 Semisynchronous Replication

16.3.10 Delayed Replication

16.4 Replication Notes and Tips

16.4.1 Replication Features and Issues

16.4.2 Replication Compatibility Between MySQL Versions

16.4.3 Upgrading a Replication Topology

16.4.4 Troubleshooting Replication

16.4.5 How to Report Replication Bugs or Problems

17 Group Replication

17.1 Group Replication Background

17.1.1 Replication Technologies

17.1.2 Group Replication Use Cases

17.1.3 Group Replication Details

17.2 Getting Started

17.2.1 Deploying Group Replication in Single-Primary Mode

17.2.2 Deploying Group Replication Locally

17.3 Requirements and Limitations

17.3.1 Group Replication Requirements

17.3.2 Group Replication Limitations

17.4 Monitoring Group Replication

17.4.1 Group Replication Server States

17.4.2 The replication_group_members Table

17.4.3 The replication_group_member_stats Table

17.5 Group Replication Operations

17.5.1 Deploying in Multi-Primary or Single-Primary Mode

17.5.2 Tuning Recovery

17.5.3 Network Partitioning

17.5.4 Restarting a Group

17.5.5 Using MySQL Enterprise Backup with Group Replication

17.6 Group Replication Security

17.6.1 Group Replication IP Address Allowlisting

17.6.2 Group Replication Secure Socket Layer (SSL) Support

17.6.3 Group Replication and Virtual Private Networks (VPNs)

17.7 Group Replication Variables

17.7.1 Group Replication System Variables

17.7.2 Group Replication Status Variables

17.8 Frequently Asked Questions

17.9 Group Replication Technical Details

17.9.1 Group Replication Plugin Architecture

17.9.2 The Group

17.9.3 Data Manipulation Statements

17.9.4 Data Definition Statements

17.9.5 Distributed Recovery

17.9.6 Observability

17.9.7 Group Replication Performance

18 MySQL Shell

19 Using MySQL as a Document Store

19.1 Key Concepts

19.2 Setting Up MySQL as a Document Store

19.2.1 Installing MySQL Shell

19.2.2 Starting MySQL Shell

19.3 Quick-Start Guide: MySQL for Visual Studio

19.4 X Plugin

19.4.1 Using Encrypted Connections with X Plugin

19.4.2 X Plugin Options and Variables

19.4.3 Monitoring X Plugin

20 InnoDB Cluster

21 MySQL NDB Cluster 7.5 and NDB Cluster 7.6

21.1 General Information

21.2 NDB Cluster Overview

21.2.1 NDB Cluster Core Concepts

21.2.2 NDB Cluster Nodes, Node Groups, Fragment Replicas, and Partitions

21.2.3 NDB Cluster Hardware, Software, and Networking Requirements

21.2.4 What is New in MySQL NDB Cluster

21.2.5 NDB: Added, Deprecated, and Removed Options, Variables, and Parameters

21.2.6 MySQL Server Using InnoDB Compared with NDB Cluster

21.2.7 Known Limitations of NDB Cluster

21.3 NDB Cluster Installation

21.3.1 Installation of NDB Cluster on Linux

21.3.2 Installing NDB Cluster on Windows

21.3.3 Initial Configuration of NDB Cluster

21.3.4 Initial Startup of NDB Cluster

21.3.5 NDB Cluster Example with Tables and Data

21.3.6 Safe Shutdown and Restart of NDB Cluster

21.3.7 Upgrading and Downgrading NDB Cluster

21.3.8 The NDB Cluster Auto-Installer (NDB 7.5) (NO LONGER SUPPORTED)

21.3.9 The NDB Cluster Auto-Installer (NO LONGER SUPPORTED)

21.4 Configuration of NDB Cluster

21.4.1 Quick Test Setup of NDB Cluster

21.4.2 Overview of NDB Cluster Configuration Parameters, Options, and Variables

21.4.3 NDB Cluster Configuration Files

21.4.4 Using High-Speed Interconnects with NDB Cluster

21.5 NDB Cluster Programs

21.5.1 ndbd — The NDB Cluster Data Node Daemon

21.5.2 ndbinfo_select_all — Select From ndbinfo Tables

21.5.3 ndbmtd — The NDB Cluster Data Node Daemon (Multi-Threaded)

21.5.4 ndb_mgmd — The NDB Cluster Management Server Daemon

21.5.5 ndb_mgm — The NDB Cluster Management Client

21.5.6 ndb_blob_tool — Check and Repair BLOB and TEXT columns of NDB Cluster Tables

21.5.7 ndb_config — Extract NDB Cluster Configuration Information

21.5.8 ndb_cpcd — Automate Testing for NDB Development

21.5.9 ndb_delete_all — Delete All Rows from an NDB Table

21.5.10 ndb_desc — Describe NDB Tables

21.5.11 ndb_drop_index — Drop Index from an NDB Table

21.5.12 ndb_drop_table — Drop an NDB Table

21.5.13 ndb_error_reporter — NDB Error-Reporting Utility

21.5.14 ndb_import — Import CSV Data Into NDB

21.5.15 ndb_index_stat — NDB Index Statistics Utility

21.5.16 ndb_move_data — NDB Data Copy Utility

21.5.17 ndb_perror — Obtain NDB Error Message Information

21.5.18 ndb_print_backup_file — Print NDB Backup File Contents

21.5.19 ndb_print_file — Print NDB Disk Data File Contents

21.5.20 ndb_print_frag_file — Print NDB Fragment List File Contents

21.5.21 ndb_print_schema_file — Print NDB Schema File Contents

21.5.22 ndb_print_sys_file — Print NDB System File Contents

21.5.23 ndb_redo_log_reader — Check and Print Content of Cluster Redo Log

21.5.24 ndb_restore — Restore an NDB Cluster Backup

21.5.25 ndb_select_all — Print Rows from an NDB Table

21.5.26 ndb_select_count — Print Row Counts for NDB Tables

21.5.27 ndb_show_tables — Display List of NDB Tables

21.5.28 ndb_size.pl — NDBCLUSTER Size Requirement Estimator

21.5.29 ndb_top — View CPU usage information for NDB threads

21.5.30 ndb_waiter — Wait for NDB Cluster to Reach a Given Status

21.6 Management of NDB Cluster

21.6.1 Commands in the NDB Cluster Management Client

21.6.2 NDB Cluster Log Messages

21.6.3 Event Reports Generated in NDB Cluster

21.6.4 Summary of NDB Cluster Start Phases

21.6.5 Performing a Rolling Restart of an NDB Cluster

21.6.6 NDB Cluster Single User Mode

21.6.7 Adding NDB Cluster Data Nodes Online

21.6.8 Online Backup of NDB Cluster

21.6.9 Importing Data Into MySQL Cluster

21.6.10 MySQL Server Usage for NDB Cluster

21.6.11 NDB Cluster Disk Data Tables

21.6.12 Online Operations with ALTER TABLE in NDB Cluster

21.6.13 Distributed Privileges Using Shared Grant Tables

21.6.14 NDB API Statistics Counters and Variables

21.6.15 ndbinfo: The NDB Cluster Information Database

21.6.16 INFORMATION_SCHEMA Tables for NDB Cluster

21.6.17 Quick Reference: NDB Cluster SQL Statements

21.6.18 NDB Cluster Security Issues

21.7 NDB Cluster Replication

21.7.1 NDB Cluster Replication: Abbreviations and Symbols

21.7.2 General Requirements for NDB Cluster Replication

21.7.3 Known Issues in NDB Cluster Replication

21.7.4 NDB Cluster Replication Schema and Tables

21.7.5 Preparing the NDB Cluster for Replication

21.7.6 Starting NDB Cluster Replication (Single Replication Channel)

21.7.7 Using Two Replication Channels for NDB Cluster Replication

21.7.8 Implementing Failover with NDB Cluster Replication

21.7.9 NDB Cluster Backups With NDB Cluster Replication

21.7.10 NDB Cluster Replication: Bidirectional and Circular Replication

21.7.11 NDB Cluster Replication Conflict Resolution

21.8 NDB Cluster Release Notes

22 Partitioning

22.1 Overview of Partitioning in MySQL

22.2 Partitioning Types

22.2.1 RANGE Partitioning

22.2.2 LIST Partitioning

22.2.3 COLUMNS Partitioning

22.2.4 HASH Partitioning

22.2.5 KEY Partitioning

22.2.6 Subpartitioning

22.2.7 How MySQL Partitioning Handles NULL

22.3 Partition Management

22.3.1 Management of RANGE and LIST Partitions

22.3.2 Management of HASH and KEY Partitions

22.3.3 Exchanging Partitions and Subpartitions with Tables

22.3.4 Maintenance of Partitions

22.3.5 Obtaining Information About Partitions

22.4 Partition Pruning

22.5 Partition Selection

22.6 Restrictions and Limitations on Partitioning

22.6.1 Partitioning Keys, Primary Keys, and Unique Keys

22.6.2 Partitioning Limitations Relating to Storage Engines

22.6.3 Partitioning Limitations Relating to Functions

22.6.4 Partitioning and Locking

23 Stored Objects

23.1 Defining Stored Programs

23.2 Using Stored Routines

23.2.1 Stored Routine Syntax

23.2.2 Stored Routines and MySQL Privileges

23.2.3 Stored Routine Metadata

23.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()

23.3 Using Triggers

23.3.1 Trigger Syntax and Examples

23.3.2 Trigger Metadata

23.4 Using the Event Scheduler

23.4.1 Event Scheduler Overview

23.4.2 Event Scheduler Configuration

23.4.3 Event Syntax

23.4.4 Event Metadata

23.4.5 Event Scheduler Status

23.4.6 The Event Scheduler and MySQL Privileges

23.5 Using Views

23.5.1 View Syntax

23.5.2 View Processing Algorithms

23.5.3 Updatable and Insertable Views

23.5.4 The View WITH CHECK OPTION Clause

23.5.5 View Metadata

23.6 Stored Object Access Control

23.7 Stored Program Binary Logging

23.8 Restrictions on Stored Programs

23.9 Restrictions on Views

24 INFORMATION_SCHEMA Tables

24.1 Introduction

24.2 INFORMATION_SCHEMA Table Reference

24.3 INFORMATION_SCHEMA General Tables

24.3.1 INFORMATION_SCHEMA General Table Reference

24.3.2 The INFORMATION_SCHEMA CHARACTER_SETS Table

24.3.3 The INFORMATION_SCHEMA COLLATIONS Table

24.3.4 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table

24.3.5 The INFORMATION_SCHEMA COLUMNS Table

24.3.6 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table

24.3.7 The INFORMATION_SCHEMA ENGINES Table

24.3.8 The INFORMATION_SCHEMA EVENTS Table

24.3.9 The INFORMATION_SCHEMA FILES Table

24.3.10 The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables

24.3.11 The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables

24.3.12 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table

24.3.13 The INFORMATION_SCHEMA ndb_transid_mysql_connection_map Table

24.3.14 The INFORMATION_SCHEMA OPTIMIZER_TRACE Table

24.3.15 The INFORMATION_SCHEMA PARAMETERS Table

24.3.16 The INFORMATION_SCHEMA PARTITIONS Table

24.3.17 The INFORMATION_SCHEMA PLUGINS Table

24.3.18 The INFORMATION_SCHEMA PROCESSLIST Table

24.3.19 The INFORMATION_SCHEMA PROFILING Table

24.3.20 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table

24.3.21 The INFORMATION_SCHEMA ROUTINES Table

24.3.22 The INFORMATION_SCHEMA SCHEMATA Table

24.3.23 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table

24.3.24 The INFORMATION_SCHEMA STATISTICS Table

24.3.25 The INFORMATION_SCHEMA TABLES Table

24.3.26 The INFORMATION_SCHEMA TABLESPACES Table

24.3.27 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table

24.3.28 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table

24.3.29 The INFORMATION_SCHEMA TRIGGERS Table

24.3.30 The INFORMATION_SCHEMA USER_PRIVILEGES Table

24.3.31 The INFORMATION_SCHEMA VIEWS Table

24.4 INFORMATION_SCHEMA InnoDB Tables

24.4.1 INFORMATION_SCHEMA InnoDB Table Reference

24.4.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table

24.4.3 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table

24.4.4 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table

24.4.5 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables

24.4.6 The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables

24.4.7 The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables

24.4.8 The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table

24.4.9 The INFORMATION_SCHEMA INNODB_FT_CONFIG Table

24.4.10 The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table

24.4.11 The INFORMATION_SCHEMA INNODB_FT_DELETED Table

24.4.12 The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table

24.4.13 The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table

24.4.14 The INFORMATION_SCHEMA INNODB_LOCKS Table

24.4.15 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table

24.4.16 The INFORMATION_SCHEMA INNODB_METRICS Table

24.4.17 The INFORMATION_SCHEMA INNODB_SYS_COLUMNS Table

24.4.18 The INFORMATION_SCHEMA INNODB_SYS_DATAFILES Table

24.4.19 The INFORMATION_SCHEMA INNODB_SYS_FIELDS Table

24.4.20 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN Table

24.4.21 The INFORMATION_SCHEMA INNODB_SYS_FOREIGN_COLS Table

24.4.22 The INFORMATION_SCHEMA INNODB_SYS_INDEXES Table

24.4.23 The INFORMATION_SCHEMA INNODB_SYS_TABLES Table

24.4.24 The INFORMATION_SCHEMA INNODB_SYS_TABLESPACES Table

24.4.25 The INFORMATION_SCHEMA INNODB_SYS_TABLESTATS View

24.4.26 The INFORMATION_SCHEMA INNODB_SYS_VIRTUAL Table

24.4.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table

24.4.28 The INFORMATION_SCHEMA INNODB_TRX Table

24.5 INFORMATION_SCHEMA Thread Pool Tables

24.5.1 INFORMATION_SCHEMA Thread Pool Table Reference

24.5.2 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATE Table

24.5.3 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATS Table

24.5.4 The INFORMATION_SCHEMA TP_THREAD_STATE Table

24.6 INFORMATION_SCHEMA Connection-Control Tables

24.6.1 INFORMATION_SCHEMA Connection-Control Table Reference

24.6.2 The INFORMATION_SCHEMA CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS Table

24.7 INFORMATION_SCHEMA MySQL Enterprise Firewall Tables

24.7.1 INFORMATION_SCHEMA Firewall Table Reference

24.7.2 The INFORMATION_SCHEMA MYSQL_FIREWALL_USERS Table

24.7.3 The INFORMATION_SCHEMA MYSQL_FIREWALL_WHITELIST Table

24.8 Extensions to SHOW Statements

25 MySQL Performance Schema

25.1 Performance Schema Quick Start

25.2 Performance Schema Build Configuration

25.3 Performance Schema Startup Configuration

25.4 Performance Schema Runtime Configuration

25.4.1 Performance Schema Event Timing

25.4.2 Performance Schema Event Filtering

25.4.3 Event Pre-Filtering

25.4.4 Pre-Filtering by Instrument

25.4.5 Pre-Filtering by Object

25.4.6 Pre-Filtering by Thread

25.4.7 Pre-Filtering by Consumer

25.4.8 Example Consumer Configurations

25.4.9 Naming Instruments or Consumers for Filtering Operations

25.4.10 Determining What Is Instrumented

25.5 Performance Schema Queries

25.6 Performance Schema Instrument Naming Conventions

25.7 Performance Schema Status Monitoring

25.8 Performance Schema Atom and Molecule Events

25.9 Performance Schema Tables for Current and Historical Events

25.10 Performance Schema Statement Digests

25.11 Performance Schema General Table Characteristics

25.12 Performance Schema Table Descriptions

25.12.1 Performance Schema Table Reference

25.12.2 Performance Schema Setup Tables

25.12.3 Performance Schema Instance Tables

25.12.4 Performance Schema Wait Event Tables

25.12.5 Performance Schema Stage Event Tables

25.12.6 Performance Schema Statement Event Tables

25.12.7 Performance Schema Transaction Tables

25.12.8 Performance Schema Connection Tables

25.12.9 Performance Schema Connection Attribute Tables

25.12.10 Performance Schema User-Defined Variable Tables

25.12.11 Performance Schema Replication Tables

25.12.12 Performance Schema Lock Tables

25.12.13 Performance Schema System Variable Tables

25.12.14 Performance Schema Status Variable Tables

25.12.15 Performance Schema Summary Tables

25.12.16 Performance Schema Miscellaneous Tables

25.13 Performance Schema Option and Variable Reference

25.14 Performance Schema Command Options

25.15 Performance Schema System Variables

25.16 Performance Schema Status Variables

25.17 The Performance Schema Memory-Allocation Model

25.18 Performance Schema and Plugins

25.19 Using the Performance Schema to Diagnose Problems

25.19.1 Query Profiling Using Performance Schema

25.20 Migrating to Performance Schema System and Status Variable Tables

25.21 Restrictions on Performance Schema

26 MySQL sys Schema

26.1 Prerequisites for Using the sys Schema

26.2 Using the sys Schema

26.3 sys Schema Progress Reporting

26.4 sys Schema Object Reference

26.4.1 sys Schema Object Index

26.4.2 sys Schema Tables and Triggers

26.4.3 sys Schema Views

26.4.4 sys Schema Stored Procedures

26.4.5 sys Schema Stored Functions

27 Connectors and APIs

27.1 MySQL Connector/C++

27.2 MySQL Connector/J

27.3 MySQL Connector/NET

27.4 MySQL Connector/ODBC

27.5 MySQL Connector/Python

27.6 libmysqld, the Embedded MySQL Server Library

27.6.1 Compiling Programs with libmysqld

27.6.2 Restrictions When Using the Embedded MySQL Server

27.6.3 Options with the Embedded Server

27.6.4 Embedded Server Examples

27.7 MySQL C API

27.8 MySQL PHP API

27.9 MySQL Perl API

27.10 MySQL Python API

27.11 MySQL Ruby APIs

27.11.1 The MySQL/Ruby API

27.11.2 The Ruby/MySQL API

27.12 MySQL Tcl API

27.13 MySQL Eiffel Wrapper

28 MySQL Enterprise Edition

28.1 MySQL Enterprise Backup Overview

28.2 MySQL Enterprise Security Overview

28.3 MySQL Enterprise Encryption Overview

28.4 MySQL Enterprise Audit Overview

28.5 MySQL Enterprise Firewall Overview

28.6 MySQL Enterprise Thread Pool Overview

28.7 MySQL Enterprise Data Masking and De-Identification Overview

28.8 MySQL Enterprise Monitor Overview

28.9 MySQL Telemetry

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: NDB 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: C API, libmysql

A.14 MySQL 5.7 FAQ: Replication

A.15 MySQL 5.7 FAQ: MySQL Enterprise Thread Pool

A.16 MySQL 5.7 FAQ: InnoDB Change Buffer

A.17 MySQL 5.7 FAQ: InnoDB Data-at-Rest Encryption

A.18 MySQL 5.7 FAQ: Virtualization Support

B Error Messages and Common Problems

B.1 Error Message Sources and Elements

B.2 Error Information Interfaces

B.3 Problems and Common Errors

B.3.1 How to Determine What Is Causing a Problem

B.3.2 Common Errors When Using MySQL Programs

B.3.3 Administration-Related Issues

B.3.4 Query-Related Issues

B.3.5 Optimizer-Related Issues

B.3.6 Table Definition-Related Issues

B.3.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