MySQL 5.5 Reference Manual

Including MySQL NDB Cluster 7.2 Reference Guide


This is the MySQL™ Reference Manual. It documents MySQL 5.5 through 5.5.62, as well as NDB Cluster releases based on version 7.2 of NDBCLUSTER through 5.5.65-ndb-7.2.39. 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.5 Release Notes.

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

For notes detailing the changes in each release, see the MySQL 5.5 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: 2019-11-18 (revision: 64229)

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.5
1.5 MySQL Information Sources
1.5.1 MySQL Websites
1.5.2 MySQL Community Support at the MySQL Forums
1.5.3 MySQL Enterprise
1.6 How to Report Bugs or Problems
1.7 MySQL Standards Compliance
1.7.1 MySQL Extensions to Standard SQL
1.7.2 MySQL Differences from Standard SQL
1.7.3 How MySQL Deals with Constraints
1.8 Credits
1.8.1 Contributors to MySQL
1.8.2 Documenters and translators
1.8.3 Packages that support MySQL
1.8.4 Tools that were used to create MySQL
1.8.5 Supporters of MySQL
2 Installing and Upgrading MySQL
2.1 General Installation Guidance
2.1.1 Which MySQL Version and Distribution to Install
2.1.2 How to Get MySQL
2.1.3 Verifying Package Integrity Using MD5 Checksums or GnuPG
2.1.4 Installation Layouts
2.1.5 Compiler-Specific Build Characteristics
2.2 Installing MySQL on Unix/Linux Using Generic Binaries
2.3 Installing MySQL on Microsoft Windows
2.3.1 MySQL Installation Layout on Microsoft Windows
2.3.2 Choosing an Installation Package
2.3.3 MySQL Installer for Windows
2.3.4 Installing MySQL on Microsoft Windows Using an MSI Package
2.3.5 MySQL Server Instance Configuration Wizard
2.3.6 Installing MySQL on Microsoft Windows Using a noinstall ZIP Archive
2.3.7 Troubleshooting a Microsoft Windows MySQL Server Installation
2.3.8 Windows Postinstallation Procedures
2.3.9 Windows Platform Restrictions
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 RPM Packages
2.5.2 Installing MySQL on Linux Using Debian Packages
2.5.3 Deploying MySQL on Linux with Docker
2.5.4 Installing MySQL on Linux Using Native Package Managers
2.6 Installing MySQL Using Unbreakable Linux Network (ULN)
2.7 Installing MySQL on Solaris
2.7.1 Installing MySQL on Solaris Using a Solaris PKG
2.8 Installing MySQL on FreeBSD
2.9 Installing MySQL from Source
2.9.1 Source Installation Methods
2.9.2 Source Installation Prerequisites
2.9.3 MySQL Layout for Source Installation
2.9.4 Installing MySQL Using a Standard Source Distribution
2.9.5 Installing MySQL Using a Development Source Tree
2.9.6 Configuring SSL Library Support
2.9.7 MySQL Source-Configuration Options
2.9.8 Dealing with Problems Compiling MySQL
2.9.9 MySQL Configuration and Third-Party Tools
2.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 MySQL
2.11.1 Before You Begin
2.11.2 Upgrade Paths
2.11.3 Changes in MySQL 5.5
2.11.4 Upgrading MySQL Binary or Package-based Installations on Unix/Linux
2.11.5 Upgrading MySQL on Windows
2.11.6 Upgrade Troubleshooting
2.11.7 Rebuilding or Repairing Tables or Indexes
2.11.8 Copying MySQL Databases to Another Machine
2.12 Downgrading MySQL
2.12.1 Before You Begin
2.12.2 Downgrade Paths
2.12.3 Downgrade Notes
2.12.4 Downgrading MySQL Binary and Package Installations on Unix/Linux
2.12.5 Downgrading from MySQL Enterprise Edition to MySQL Community Server
2.12.6 Downgrade Troubleshooting
2.13 Perl Installation Notes
2.13.1 Installing Perl on Unix
2.13.2 Installing ActiveState Perl on Windows
2.13.3 Problems Using the Perl DBI/DBD Interface
3 Tutorial
3.1 Connecting to and Disconnecting from the Server
3.2 Entering Queries
3.3 Creating and Using a Database
3.3.1 Creating and Selecting a Database
3.3.2 Creating a Table
3.3.3 Loading Data into a Table
3.3.4 Retrieving Information from a Table
3.4 Getting Information About Databases and Tables
3.5 Using mysql in Batch Mode
3.6 Examples of Common Queries
3.6.1 The Maximum Value for a Column
3.6.2 The Row Holding the Maximum of a Certain Column
3.6.3 Maximum of Column per Group
3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column
3.6.5 Using User-Defined Variables
3.6.6 Using Foreign Keys
3.6.7 Searching on Two Keys
3.6.8 Calculating Visits Per Day
3.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 Compression Control
4.2.6 Setting Environment Variables
4.3 MySQL 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 MySQL Installation-Related Programs
4.4.1 comp_err — Compile MySQL Error Message File
4.4.2 mysqlbug — Generate Bug Report
4.4.3 mysql_install_db — Initialize MySQL Data Directory
4.4.4 mysql_plugin — Configure MySQL Server Plugins
4.4.5 mysql_secure_installation — Improve MySQL Installation Security
4.4.6 mysql_tzinfo_to_sql — Load the Time Zone Tables
4.4.7 mysql_upgrade — Check and Upgrade MySQL Tables
4.5 MySQL Client Programs
4.5.1 mysql — The MySQL Command-Line Client
4.5.2 mysqladmin — Client for Administering a MySQL Server
4.5.3 mysqlcheck — A Table Maintenance Program
4.5.4 mysqldump — A Database Backup Program
4.5.5 mysqlimport — A Data Import Program
4.5.6 mysqlshow — Display Database, Table, and Column Information
4.5.7 mysqlslap — Load Emulation Client
4.6 MySQL 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 mysqlaccess — Client for Checking Access Privileges
4.6.7 mysqlbinlog — Utility for Processing Binary Log Files
4.6.8 mysqldumpslow — Summarize Slow Query Log Files
4.6.9 mysqlhotcopy — A Database Backup Program
4.6.10 mysql_convert_table_format — Convert Tables to Use a Given Storage Engine
4.6.11 mysql_find_rows — Extract SQL Statements from Files
4.6.12 mysql_fix_extensions — Normalize Table File Name Extensions
4.6.13 mysql_setpermission — Interactively Set Permissions in Grant Tables
4.6.14 mysql_waitpid — Kill Process and Wait for Its Termination
4.6.15 mysql_zap — Kill Processes That Match a Pattern
4.7 MySQL Program Development Utilities
4.7.1 msql2mysql — Convert mSQL Programs for Use with MySQL
4.7.2 mysql_config — Display Options for Compiling Clients
4.7.3 my_print_defaults — Display Options from Option Files
4.7.4 resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols
4.8 Miscellaneous Programs
4.8.1 perror — Display MySQL Error Message Information
4.8.2 replace — A String-Replacement Utility
4.8.3 resolveip — Resolve Host name to IP Address or Vice Versa
4.9 MySQL Environment Variables
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 IPv6 Support
5.1.12 MySQL Server Time Zone Support
5.1.13 Server-Side Help Support
5.1.14 Server Response to Signals
5.1.15 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.6 MySQL Server User-Defined Functions
5.6.1 Installing and Uninstalling User-Defined Functions
5.6.2 Obtaining User-Defined Function Information
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 Tracing mysqld Using DTrace
5.8.1 mysqld DTrace Probe Reference
6 Security
6.1 General Security Issues
6.1.1 Security Guidelines
6.1.2 Keeping Passwords Secure
6.1.3 Making MySQL Secure Against Attackers
6.1.4 Security-Related mysqld Options and Variables
6.1.5 How to Run MySQL as a Normal User
6.1.6 Security Issues with LOAD DATA LOCAL
6.1.7 Client Programming Security Guidelines
6.2 Access Control and Account Management
6.2.1 Account User Names and Passwords
6.2.2 Privileges Provided by MySQL
6.2.3 Grant Tables
6.2.4 Specifying Account Names
6.2.5 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 When Privilege Changes Take Effect
6.2.9 Assigning Account Passwords
6.2.10 Pluggable Authentication
6.2.11 Proxy Users
6.2.12 Setting Account Resource Limits
6.2.13 Troubleshooting Problems Connecting to MySQL
6.2.14 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 Certificates and Keys Using openssl
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 MySQL Enterprise Audit
7 Backup and Recovery
7.1 Backup and Recovery Types
7.2 Database Backup Methods
7.3 Example Backup and Recovery Strategy
7.3.1 Establishing a Backup Policy
7.3.2 Using Backups for Recovery
7.3.3 Backup Strategy Summary
7.4 Using mysqldump for Backups
7.4.1 Dumping Data in SQL Format with mysqldump
7.4.2 Reloading SQL-Format Backups
7.4.3 Dumping Data in Delimited-Text Format with mysqldump
7.4.4 Reloading Delimited-Text Format Backups
7.4.5 mysqldump Tips
7.5 Point-in-Time (Incremental) Recovery Using the Binary Log
7.5.1 Point-in-Time Recovery Using Event Times
7.5.2 Point-in-Time Recovery Using Event Positions
7.6 MyISAM Table Maintenance and Crash Recovery
7.6.1 Using myisamchk for Crash Recovery
7.6.2 How to Check MyISAM Tables for Errors
7.6.3 How to Repair MyISAM Tables
7.6.4 MyISAM Table Optimization
7.6.5 Setting Up a MyISAM Table Maintenance Schedule
8 Optimization
8.1 Optimization Overview
8.2 Optimizing SQL Statements
8.2.1 Optimizing SELECT Statements
8.2.2 Subquery Optimization
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 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 Redo Logging
8.5.4 Bulk Data Loading for InnoDB Tables
8.5.5 Optimizing InnoDB Queries
8.5.6 Optimizing InnoDB DDL Operations
8.5.7 Optimizing InnoDB Disk I/O
8.5.8 Optimizing InnoDB Configuration Variables
8.5.9 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 Estimating Query Performance
8.9 Controlling the Query Optimizer
8.9.1 Controlling Query Plan Evaluation
8.9.2 Switchable Optimizations
8.9.3 Index Hints
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.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.12.5 Optimizing Network Use
8.13 Measuring Performance (Benchmarking)
8.13.1 Measuring the Speed of Expressions and Functions
8.13.2 The MySQL Benchmark Suite
8.13.3 Using Your Own Benchmarks
8.13.4 Measuring Performance with performance_schema
8.14 Examining Thread Information
8.14.1 Thread Command Values
8.14.2 General Thread States
8.14.3 Delayed-Insert Thread States
8.14.4 Query Cache Thread States
8.14.5 Replication Master Thread States
8.14.6 Replication Slave I/O Thread States
8.14.7 Replication Slave SQL Thread States
8.14.8 Replication Slave Connection Thread States
8.14.9 NDB Cluster Thread States
8.14.10 Event Scheduler Thread States
9 Language Structure
9.1 Literal Values
9.1.1 String Literals
9.1.2 Numeric Literals
9.1.3 Date and Time Literals
9.1.4 Hexadecimal Literals
9.1.5 Bit-Value Literals
9.1.6 Boolean Literals
9.1.7 NULL Values
9.2 Schema Object Names
9.2.1 Identifier Length Limits
9.2.2 Identifier Qualifiers
9.2.3 Identifier Case Sensitivity
9.2.4 Mapping of Identifiers to File Names
9.2.5 Function Name Parsing and Resolution
9.3 Keywords and Reserved Words
9.4 User-Defined Variables
9.5 Expressions
9.6 Comment Syntax
10 Character Sets, Collations, Unicode
10.1 Character Sets and Collations in General
10.2 Character Sets and Collations in MySQL
10.2.1 Character Set Repertoire
10.2.2 UTF-8 for Metadata
10.3 Specifying Character Sets and Collations
10.3.1 Collation Naming Conventions
10.3.2 Server Character Set and Collation
10.3.3 Database Character Set and Collation
10.3.4 Table Character Set and Collation
10.3.5 Column Character Set and Collation
10.3.6 Character String Literal Character Set and Collation
10.3.7 The National Character Set
10.3.8 Character Set Introducers
10.3.9 Examples of Character Set and Collation Assignment
10.3.10 Compatibility with Other DBMSs
10.4 Connection Character Sets and Collations
10.5 Configuring Application Character Set and Collation
10.6 Error Message Character Set
10.7 Column Character Set Conversion
10.8 Collation Issues
10.8.1 Using COLLATE in SQL Statements
10.8.2 COLLATE Clause Precedence
10.8.3 Character Set and Collation Compatibility
10.8.4 Collation Coercibility in Expressions
10.8.5 The binary Collation Compared to _bin Collations
10.8.6 Examples of the Effect of Collation
10.8.7 Using Collation in INFORMATION_SCHEMA Searches
10.9 Unicode Support
10.9.1 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)
10.9.2 The utf8mb3 Character Set (3-Byte UTF-8 Unicode Encoding)
10.9.3 The utf8 Character Set (Alias for utf8mb3)
10.9.4 The ucs2 Character Set (UCS-2 Unicode Encoding)
10.9.5 The utf16 Character Set (UTF-16 Unicode Encoding)
10.9.6 The utf32 Character Set (UTF-32 Unicode Encoding)
10.9.7 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 Data Type Overview
11.1.1 Numeric Type Overview
11.1.2 Date and Time Type Overview
11.1.3 String Type Overview
11.2 Numeric Types
11.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC
11.2.3 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE
11.2.4 Bit-Value Type - BIT
11.2.5 Numeric Type Attributes
11.2.6 Out-of-Range and Overflow Handling
11.3 Date and Time Types
11.3.1 The DATE, DATETIME, and TIMESTAMP Types
11.3.2 The TIME Type
11.3.3 The YEAR Type
11.3.4 2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR
11.3.5 Automatic Initialization and Updating for TIMESTAMP
11.3.6 Fractional Seconds in Time Values
11.3.7 Conversion Between Date and Time Types
11.3.8 2-Digit Years in Dates
11.4 String Types
11.4.1 The CHAR and VARCHAR Types
11.4.2 The BINARY and VARBINARY Types
11.4.3 The BLOB and TEXT Types
11.4.4 The ENUM Type
11.4.5 The SET Type
11.5 Spatial Data Types
11.5.1 Spatial Data Types
11.5.2 The OpenGIS Geometry Model
11.5.3 Supported Spatial Data Formats
11.5.4 Creating Spatial Columns
11.5.5 Populating Spatial Columns
11.5.6 Fetching Spatial Data
11.5.7 Optimizing Spatial Analysis
11.5.8 Creating Spatial Indexes
11.5.9 Using Spatial Indexes
11.6 Data Type Default Values
11.7 Data Type Storage Requirements
11.8 Choosing the Right Type for a Column
11.9 Using Data Types from Other Database Engines
12 Functions and Operators
12.1 Function and Operator Reference
12.2 Type Conversion in Expression Evaluation
12.3 Operators
12.3.1 Operator Precedence
12.3.2 Comparison Functions and Operators
12.3.3 Logical Operators
12.3.4 Assignment Operators
12.4 Control Flow Functions
12.5 String Functions and Operators
12.5.1 String Comparison Functions and Operators
12.5.2 Regular Expressions
12.5.3 Character Set and Collation of Function Results
12.6 Numeric Functions and Operators
12.6.1 Arithmetic Operators
12.6.2 Mathematical Functions
12.7 Date and Time Functions
12.8 What Calendar Is Used By MySQL?
12.9 Full-Text Search Functions
12.9.1 Natural Language Full-Text Searches
12.9.2 Boolean Full-Text Searches
12.9.3 Full-Text Searches with Query Expansion
12.9.4 Full-Text Stopwords
12.9.5 Full-Text Restrictions
12.9.6 Fine-Tuning MySQL Full-Text Search
12.9.7 Adding a Collation for Full-Text Indexing
12.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.17 Aggregate (GROUP BY) Functions
12.17.1 Aggregate (GROUP BY) Function Descriptions
12.17.2 GROUP BY Modifiers
12.17.3 MySQL Handling of GROUP BY
12.18 Miscellaneous Functions
12.19 Precision Math
12.19.1 Types of Numeric Values
12.19.2 DECIMAL Data Type Characteristics
12.19.3 Expression Handling
12.19.4 Rounding Behavior
12.19.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 LOGFILE GROUP Statement
13.1.5 ALTER PROCEDURE Statement
13.1.6 ALTER SERVER Statement
13.1.7 ALTER TABLE Statement
13.1.8 ALTER TABLESPACE Statement
13.1.9 ALTER VIEW Statement
13.1.10 CREATE DATABASE Statement
13.1.11 CREATE EVENT Statement
13.1.12 CREATE FUNCTION Statement
13.1.13 CREATE INDEX Statement
13.1.14 CREATE LOGFILE GROUP Statement
13.1.16 CREATE SERVER Statement
13.1.17 CREATE TABLE Statement
13.1.18 CREATE TABLESPACE Statement
13.1.19 CREATE TRIGGER Statement
13.1.20 CREATE VIEW Statement
13.1.21 DROP DATABASE Statement
13.1.22 DROP EVENT Statement
13.1.23 DROP FUNCTION Statement
13.1.24 DROP INDEX Statement
13.1.25 DROP LOGFILE GROUP Statement
13.1.27 DROP SERVER Statement
13.1.28 DROP TABLE Statement
13.1.29 DROP TABLESPACE Statement
13.1.30 DROP TRIGGER Statement
13.1.31 DROP VIEW Statement
13.1.32 RENAME TABLE Statement
13.1.33 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.2 Statements That Cannot Be Rolled Back
13.3.3 Statements That Cause an Implicit Commit
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 Master Servers
13.4.2 SQL Statements for Controlling Slave Servers
13.5 Prepared Statements
13.5.1 PREPARE Statement
13.5.2 EXECUTE Statement
13.5.4 Automatic Prepared Statement Repreparation
13.6 Compound-Statements
13.6.1 BEGIN ... END Compound Statement
13.6.2 Statement Labels
13.6.3 DECLARE Statements
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 User-Defined 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 Checking InnoDB Availability
14.1.4 Upward and Downward Compatibility
14.1.5 Testing and Benchmarking with InnoDB
14.1.6 Turning Off InnoDB
14.1.7 Third-Party Software Contributions
14.2 Installing the InnoDB Storage Engine
14.3 Upgrading the InnoDB Storage Engine
14.4 Downgrading the InnoDB Storage Engine
14.5 InnoDB and the ACID Model
14.6 InnoDB Multi-Versioning
14.7 InnoDB Architecture
14.8 InnoDB In-Memory Structures
14.8.1 Buffer Pool
14.8.2 Change Buffer
14.8.3 Adaptive Hash Index
14.8.4 Log Buffer
14.9 InnoDB On-Disk Structures
14.9.1 Tables
14.9.2 Indexes
14.9.3 Tablespaces
14.9.4 InnoDB Data Dictionary
14.9.5 Doublewrite Buffer
14.9.6 Redo Log
14.9.7 Undo Logs
14.10 InnoDB Locking and Transaction Model
14.10.1 InnoDB Locking
14.10.2 InnoDB Transaction Model
14.10.3 Locks Set by Different SQL Statements in InnoDB
14.10.4 Phantom Rows
14.10.5 Deadlocks in InnoDB
14.11 InnoDB Configuration
14.11.1 InnoDB Startup Configuration
14.11.2 InnoDB Buffer Pool Configuration
14.11.3 Configuring the Memory Allocator for InnoDB
14.11.4 Configuring Thread Concurrency for InnoDB
14.11.5 Configuring the Number of Background InnoDB I/O Threads
14.11.6 Using Asynchronous I/O on Linux
14.11.7 Configuring InnoDB I/O Capacity
14.11.8 Configuring Spin Lock Polling
14.11.9 Purge Configuration
14.11.10 Configuring Optimizer Statistics for InnoDB
14.12 InnoDB Table Compression
14.12.1 Overview of Table Compression
14.12.2 Enabling Compression for a Table
14.12.3 Tuning Compression for InnoDB Tables
14.12.4 Monitoring InnoDB Table Compression at Runtime
14.12.5 How Compression Works for InnoDB Tables
14.12.6 SQL Compression Syntax Warnings and Errors
14.13 InnoDB File-Format Management
14.13.1 Enabling File Formats
14.13.2 Verifying File Format Compatibility
14.13.3 Identifying the File Format in Use
14.13.4 Downgrading the File Format
14.14 InnoDB Row Formats
14.15 InnoDB Disk I/O and File Space Management
14.15.1 InnoDB Disk I/O
14.15.2 File Space Management
14.15.3 InnoDB Checkpoints
14.15.4 Defragmenting a Table
14.15.5 Reclaiming Disk Space with TRUNCATE TABLE
14.16 InnoDB Fast Index Creation
14.16.1 Overview of Fast Index Creation
14.16.2 Examples of Fast Index Creation
14.16.3 Implementation Details of Fast Index Creation
14.16.4 Concurrency Considerations for Fast Index Creation
14.16.5 How Crash Recovery Works with Fast Index Creation
14.16.6 Limitations of Fast Index Creation
14.17 InnoDB Startup Options and System Variables
14.18.1 InnoDB INFORMATION_SCHEMA Tables about Compression
14.18.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Information
14.18.3 InnoDB INFORMATION_SCHEMA Buffer Pool Tables
14.19 InnoDB Integration with MySQL Performance Schema
14.19.1 Monitoring InnoDB Mutex Waits Using Performance Schema
14.20 InnoDB Monitors
14.20.1 InnoDB Monitor Types
14.20.2 Enabling InnoDB Monitors
14.20.3 InnoDB Standard Monitor and Lock Monitor Output
14.20.4 InnoDB Tablespace Monitor Output
14.20.5 InnoDB Table Monitor Output
14.21 InnoDB Backup and Recovery
14.21.1 InnoDB Backup
14.21.2 InnoDB Recovery
14.22 InnoDB and MySQL Replication
14.23 InnoDB Troubleshooting
14.23.1 Troubleshooting InnoDB I/O Problems
14.23.2 Forcing InnoDB Recovery
14.23.3 Troubleshooting InnoDB Data Dictionary Operations
14.23.4 InnoDB Error Handling
14.24 InnoDB Limits
14.25 InnoDB Restrictions and Limitations
15 Alternative Storage Engines
15.1 Setting the Storage Engine
15.2 Overview of MySQL Storage Engine Architecture
15.2.1 Pluggable Storage Engine Architecture
15.2.2 The Common Database Server Layer
15.3 The MyISAM Storage Engine
15.3.1 MyISAM Startup Options
15.3.2 Space Needed for Keys
15.3.3 MyISAM Table Storage Formats
15.3.4 MyISAM Table Problems
15.4 The MEMORY Storage Engine
15.5 The CSV Storage Engine
15.5.1 Repairing and Checking CSV Tables
15.5.2 CSV Limitations
15.6 The ARCHIVE Storage Engine
15.7 The BLACKHOLE Storage Engine
15.8 The MERGE Storage Engine
15.8.1 MERGE Table Advantages and Disadvantages
15.8.2 MERGE Table Problems
15.9 The FEDERATED Storage Engine
15.9.1 FEDERATED Storage Engine Overview
15.9.2 How to Create FEDERATED Tables
15.9.3 FEDERATED Storage Engine Notes and Tips
15.9.4 FEDERATED Storage Engine Resources
15.10 The EXAMPLE Storage Engine
15.11 Other Storage Engines
16 High Availability and Scalability
16.1 Using ZFS Replication
16.1.1 Using ZFS for File System Replication
16.1.2 Configuring MySQL for ZFS Replication
16.1.3 Handling MySQL Recovery with ZFS
16.2 Using MySQL with memcached
16.2.1 Installing memcached
16.2.2 Using memcached
16.2.3 Developing a memcached Application
16.2.4 Getting memcached Statistics
16.2.5 memcached FAQ
17 Replication
17.1 Replication Configuration
17.1.1 How to Set Up Replication
17.1.2 Replication Formats
17.1.3 Replication and Binary Logging Options and Variables
17.1.4 Common Replication Administration Tasks
17.2 Replication Implementation
17.2.1 Replication Implementation Details
17.2.2 Replication Relay and Status Logs
17.2.3 How Servers Evaluate Replication Filtering Rules
17.3 Replication Solutions
17.3.1 Using Replication for Backups
17.3.2 Using Replication with Different Master and Slave Storage Engines
17.3.3 Using Replication for Scale-Out
17.3.4 Replicating Different Databases to Different Slaves
17.3.5 Improving Replication Performance
17.3.6 Switching Masters During Failover
17.3.7 Setting Up Replication to Use Encrypted Connections
17.3.8 Semisynchronous Replication
17.4 Replication Notes and Tips
17.4.1 Replication Features and Issues
17.4.2 Replication Compatibility Between MySQL Versions
17.4.3 Upgrading a Replication Setup
17.4.4 Troubleshooting Replication
17.4.5 How to Report Replication Bugs or Problems
18 MySQL NDB Cluster 7.2
18.1 NDB Cluster Overview
18.1.1 NDB Cluster Core Concepts
18.1.2 NDB Cluster Nodes, Node Groups, Replicas, and Partitions
18.1.3 NDB Cluster Hardware, Software, and Networking Requirements
18.1.4 What is New in MySQL NDB Cluster 7.2
18.1.5 Options, Variables, and Parameters Added, Deprecated or Removed in NDB 7.2
18.1.6 MySQL Server Using InnoDB Compared with NDB Cluster
18.1.7 Known Limitations of NDB Cluster
18.2 NDB Cluster Installation
18.2.1 Installing NDB Cluster on Linux
18.2.2 Installing NDB Cluster on Windows
18.2.3 Initial Configuration of NDB Cluster
18.2.4 Initial Startup of NDB Cluster
18.2.5 NDB Cluster Example with Tables and Data
18.2.6 Safe Shutdown and Restart of NDB Cluster
18.2.7 Upgrading and Downgrading NDB Cluster
18.3 Configuration of NDB Cluster
18.3.1 Quick Test Setup of NDB Cluster
18.3.2 Overview of NDB Cluster Configuration Parameters, Options, and Variables
18.3.3 NDB Cluster Configuration Files
18.3.4 Using High-Speed Interconnects with NDB Cluster
18.4 NDB Cluster Programs
18.4.1 ndbd — The NDB Cluster Data Node Daemon
18.4.2 ndbinfo_select_all — Select From ndbinfo Tables
18.4.3 ndbmtd — The NDB Cluster Data Node Daemon (Multi-Threaded)
18.4.4 ndb_mgmd — The NDB Cluster Management Server Daemon
18.4.5 ndb_mgm — The NDB Cluster Management Client
18.4.6 ndb_blob_tool — Check and Repair BLOB and TEXT columns of NDB Cluster Tables
18.4.7 ndb_config — Extract NDB Cluster Configuration Information
18.4.8 ndb_cpcd — Automate Testing for NDB Development
18.4.9 ndb_delete_all — Delete All Rows from an NDB Table
18.4.10 ndb_desc — Describe NDB Tables
18.4.11 ndb_drop_index — Drop Index from an NDB Table
18.4.12 ndb_drop_table — Drop an NDB Table
18.4.13 ndb_error_reporter — NDB Error-Reporting Utility
18.4.14 ndb_index_stat — NDB Index Statistics Utility
18.4.15 ndb_move_data — NDB Data Copy Utility
18.4.16 ndb_print_backup_file — Print NDB Backup File Contents
18.4.17 ndb_print_file — Print NDB Disk Data File Contents
18.4.18 ndb_print_schema_file — Print NDB Schema File Contents
18.4.19 ndb_print_sys_file — Print NDB System File Contents
18.4.20 ndb_redo_log_reader — Check and Print Content of Cluster Redo Log
18.4.21 ndb_restore — Restore an NDB Cluster Backup
18.4.22 ndb_select_all — Print Rows from an NDB Table
18.4.23 ndb_select_count — Print Row Counts for NDB Tables
18.4.24 ndb_show_tables — Display List of NDB Tables
18.4.25 — NDBCLUSTER Size Requirement Estimator
18.4.26 ndb_waiter — Wait for NDB Cluster to Reach a Given Status
18.4.27 Options Common to NDB Cluster Programs — Options Common to NDB Cluster Programs
18.5 Management of NDB Cluster
18.5.1 Summary of NDB Cluster Start Phases
18.5.2 Commands in the NDB Cluster Management Client
18.5.3 Online Backup of NDB Cluster
18.5.4 MySQL Server Usage for NDB Cluster
18.5.5 Performing a Rolling Restart of an NDB Cluster
18.5.6 Event Reports Generated in NDB Cluster
18.5.7 NDB Cluster Log Messages
18.5.8 NDB Cluster Single User Mode
18.5.9 Quick Reference: NDB Cluster SQL Statements
18.5.10 ndbinfo: The NDB Cluster Information Database
18.5.11 NDB Cluster Security Issues
18.5.12 NDB Cluster Disk Data Tables
18.5.13 Online Operations with ALTER TABLE in NDB Cluster
18.5.14 Adding NDB Cluster Data Nodes Online
18.5.15 Distributed Privileges Using Shared Grant Tables
18.5.16 NDB API Statistics Counters and Variables
18.6 NDB Cluster Replication
18.6.1 NDB Cluster Replication: Abbreviations and Symbols
18.6.2 General Requirements for NDB Cluster Replication
18.6.3 Known Issues in NDB Cluster Replication
18.6.4 NDB Cluster Replication Schema and Tables
18.6.5 Preparing the NDB Cluster for Replication
18.6.6 Starting NDB Cluster Replication (Single Replication Channel)
18.6.7 Using Two Replication Channels for NDB Cluster Replication
18.6.8 Implementing Failover with NDB Cluster Replication
18.6.9 NDB Cluster Backups With NDB Cluster Replication
18.6.10 NDB Cluster Replication: Multi-Master and Circular Replication
18.6.11 NDB Cluster Replication Conflict Resolution
18.7 NDB Cluster Release Notes
19 Partitioning
19.1 Overview of Partitioning in MySQL
19.2 Partitioning Types
19.2.1 RANGE Partitioning
19.2.2 LIST Partitioning
19.2.3 COLUMNS Partitioning
19.2.4 HASH Partitioning
19.2.5 KEY Partitioning
19.2.6 Subpartitioning
19.2.7 How MySQL Partitioning Handles NULL
19.3 Partition Management
19.3.1 Management of RANGE and LIST Partitions
19.3.2 Management of HASH and KEY Partitions
19.3.3 Maintenance of Partitions
19.3.4 Obtaining Information About Partitions
19.4 Partition Pruning
19.5 Restrictions and Limitations on Partitioning
19.5.1 Partitioning Keys, Primary Keys, and Unique Keys
19.5.2 Partitioning Limitations Relating to Storage Engines
19.5.3 Partitioning Limitations Relating to Functions
19.5.4 Partitioning and Table-Level Locking
20 Stored Objects
20.1 Defining Stored Programs
20.2 Using Stored Routines
20.2.1 Stored Routine Syntax
20.2.2 Stored Routines and MySQL Privileges
20.2.3 Stored Routine Metadata
20.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()
20.3 Using Triggers
20.3.1 Trigger Syntax and Examples
20.3.2 Trigger Metadata
20.4 Using the Event Scheduler
20.4.1 Event Scheduler Overview
20.4.2 Event Scheduler Configuration
20.4.3 Event Syntax
20.4.4 Event Metadata
20.4.5 Event Scheduler Status
20.4.6 The Event Scheduler and MySQL Privileges
20.5 Using Views
20.5.1 View Syntax
20.5.2 View Processing Algorithms
20.5.3 Updatable and Insertable Views
20.5.4 The View WITH CHECK OPTION Clause
20.5.5 View Metadata
20.6 Stored Object Access Control
20.7 Stored Program Binary Logging
20.8 Restrictions on Stored Programs
20.9 Restrictions on Views
21.1 Introduction
21.30.2 The INFORMATION_SCHEMA ndb_transid_mysql_connection_map Table
21.31 INFORMATION_SCHEMA Thread Pool Tables
21.32 Extensions to SHOW Statements
22 MySQL Performance Schema
22.1 Performance Schema Quick Start
22.2 Performance Schema Build Configuration
22.3 Performance Schema Startup Configuration
22.4 Performance Schema Runtime Configuration
22.4.1 Performance Schema Event Timing
22.4.2 Performance Schema Event Filtering
22.4.3 Event Pre-Filtering
22.4.4 Naming Instruments or Consumers for Filtering Operations
22.4.5 Determining What Is Instrumented
22.5 Performance Schema Queries
22.6 Performance Schema Instrument Naming Conventions
22.7 Performance Schema Status Monitoring
22.8 Performance Schema Tables for Current and Historical Events
22.9 Performance Schema General Table Characteristics
22.10 Performance Schema Table Descriptions
22.10.1 Performance Schema Table Index
22.10.2 Performance Schema Setup Tables
22.10.3 Performance Schema Instance Tables
22.10.4 Performance Schema Wait Event Tables
22.10.5 Performance Schema Summary Tables
22.10.6 Performance Schema Miscellaneous Tables
22.11 Performance Schema Option and Variable Reference
22.12 Performance Schema System Variables
22.13 Performance Schema Status Variables
22.14 Performance Schema and Plugins
22.15 Using the Performance Schema to Diagnose Problems
22.16 Restrictions on Performance Schema
23 Connectors and APIs
23.1 MySQL Connector/C++
23.2 MySQL Connector/J
23.3 MySQL Connector/NET
23.4 MySQL Connector/ODBC
23.5 MySQL Connector/Python
23.6 libmysqld, the Embedded MySQL Server Library
23.6.1 Compiling Programs with libmysqld
23.6.2 Restrictions When Using the Embedded MySQL Server
23.6.3 Options with the Embedded Server
23.6.4 Embedded Server Examples
23.7 MySQL C API
23.7.1 MySQL C API Implementations
23.7.2 Example C API Client Programs
23.7.3 Building and Running C API Client Programs
23.7.4 C API Data Structures
23.7.5 C API Function Overview
23.7.6 C API Function Descriptions
23.7.7 C API Prepared Statements
23.7.8 C API Prepared Statement Data Structures
23.7.9 C API Prepared Statement Function Overview
23.7.10 C API Prepared Statement Function Descriptions
23.7.11 C API Threaded Function Descriptions
23.7.12 C API Embedded Server Function Descriptions
23.7.13 C API Client Plugin Functions
23.7.14 C API Encrypted Connection Support
23.7.15 C API Multiple Statement Execution Support
23.7.16 C API Prepared Statement Handling of Date and Time Values
23.7.17 C API Prepared CALL Statement Support
23.7.18 C API Prepared Statement Problems
23.7.19 C API Automatic Reconnection Control
23.7.20 C API Common Issues
23.9 MySQL Perl API
23.10 MySQL Python API
23.11 MySQL Ruby APIs
23.11.1 The MySQL/Ruby API
23.11.2 The Ruby/MySQL API
23.12 MySQL Tcl API
23.13 MySQL Eiffel Wrapper
24 Extending MySQL
24.1 MySQL Internals
24.1.1 MySQL Threads
24.1.2 The MySQL Test Suite
24.2 The MySQL Plugin API
24.2.1 Types of Plugins
24.2.2 Plugin API Characteristics
24.2.3 Plugin API Components
24.2.4 Writing Plugins
24.3 MySQL Services for Plugins
24.4 Adding Functions to MySQL
24.4.1 Features of the User-Defined Function Interface
24.4.2 Adding a User-Defined Function
24.4.3 Adding a Native Function
24.5 Debugging and Porting MySQL
24.5.1 Debugging a MySQL Server
24.5.2 Debugging a MySQL Client
24.5.3 The DBUG Package
25 MySQL Enterprise Edition
25.1 MySQL Enterprise Monitor Overview
25.2 MySQL Enterprise Backup Overview
25.3 MySQL Enterprise Security Overview
25.4 MySQL Enterprise Encryption Overview
25.5 MySQL Enterprise Audit Overview
25.6 MySQL Enterprise Firewall Overview
25.7 MySQL Enterprise Thread Pool Overview
25.8 MySQL Enterprise Data Masking and De-Identification Overview
26 MySQL Workbench
A MySQL 5.5 Frequently Asked Questions
A.1 MySQL 5.5 FAQ: General
A.2 MySQL 5.5 FAQ: Storage Engines
A.3 MySQL 5.5 FAQ: Server SQL Mode
A.4 MySQL 5.5 FAQ: Stored Procedures and Functions
A.5 MySQL 5.5 FAQ: Triggers
A.6 MySQL 5.5 FAQ: Views
A.8 MySQL 5.5 FAQ: Migration
A.9 MySQL 5.5 FAQ: Security
A.10 MySQL FAQ: MySQL 5.5 and NDB Cluster
A.11 MySQL 5.5 FAQ: MySQL Chinese, Japanese, and Korean Character Sets
A.12 MySQL 5.5 FAQ: Connectors & APIs
A.13 MySQL 5.5 FAQ: C API, libmysql
A.14 MySQL 5.5 FAQ: Replication
A.15 MySQL 5.5 FAQ: MySQL Enterprise Thread Pool
A.16 MySQL 5.5 FAQ: InnoDB Change Buffer
A.17 MySQL 5.5 FAQ: Virtualization Support
B Errors, Error Codes, and Common Problems
B.1 Error Message Sources and Components
B.2 Error Information Interfaces
B.3 Error Message Reference
B.3.1 Server Error Message Reference
B.3.2 Client Error Message Reference
B.3.3 Global Error Message Reference
B.4 Problems and Common Errors
B.4.1 How to Determine What Is Causing a Problem
B.4.2 Common Errors When Using MySQL Programs
B.4.3 Administration-Related Issues
B.4.4 Query-Related Issues
B.4.5 Optimizer-Related Issues
B.4.6 Table Definition-Related Issues
B.4.7 Known Issues in MySQL
C Indexes
General Index
C Function Index
Command Index
Function Index
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