This documentation is for an older version. If you're using the most current version, select the documentation for that version with the version switch in the upper right corner of the online documentation, or by downloading a newer PDF or EPUB file.

MySQL 5.0 Reference Manual


This is the MySQL™ Reference Manual. It documents MySQL 5.0 through 5.0.96.

End of Product Lifecycle. Active development for MySQL Database Server version 5.0 has ended. Oracle offers various support offerings which may be of interest. For details and more information, see the MySQL section of the Lifetime Support Policy for Oracle Technology Products ( Please consider upgrading to a recent version.

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

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

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.  This product may include third-party software, used under license. If you are using a Commercial release of MySQL 5.0, 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.0, 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: 2016-05-11 (revision: 47682)

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.0
1.5 MySQL Development History
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 MySQL Installation Overview
2.2 Determining Your Current MySQL Version
2.3 Notes for MySQL Enterprise Server
2.3.1 Enterprise Server Distribution Types
2.3.2 Upgrading MySQL Enterprise Server
2.4 Notes for MySQL Community Server
2.4.1 Overview of MySQL Community Server Installation
2.4.2 Choosing Which MySQL Distribution to Install
2.5 How to Get MySQL
2.6 Verifying Package Integrity Using MD5 Checksums or GnuPG
2.6.1 Verifying the MD5 Checksum
2.6.2 Signature Checking Using GnuPG
2.6.3 Signature Checking Using Gpg4win for Windows
2.6.4 Signature Checking Using RPM
2.7 Installation Layouts
2.8 Compiler-Specific Build Characteristics
2.9 Standard MySQL Installation from a Binary Distribution
2.10 Installing MySQL on Microsoft Windows
2.10.1 Choosing An Installation Package
2.10.2 Installing MySQL on Microsoft Windows Using an MSI Package
2.10.3 MySQL Server Instance Configuration Wizard
2.10.4 Installing MySQL on Microsoft Windows Using a noinstall Zip Archive
2.10.5 Troubleshooting a MySQL Installation Under Windows
2.10.6 Windows Postinstallation Procedures
2.10.7 Upgrading MySQL on Windows
2.10.8 Installing MySQL from Source on Windows
2.11 Installing MySQL on OS X
2.12 Installing MySQL on Linux Using RPM Packages
2.13 Installing MySQL on Solaris
2.14 Installing MySQL on i5/OS
2.15 Installing MySQL on NetWare
2.16 Installing MySQL on Unix/Linux Using Generic Binaries
2.17 Installing MySQL from Source
2.17.1 Installing MySQL Using a Standard Source Distribution
2.17.2 Installing MySQL Using a Development Source Tree
2.17.3 MySQL Source-Configuration Options
2.17.4 Dealing with Problems Compiling MySQL
2.17.5 Compiling and Linking an Optimized mysqld Server
2.18 Postinstallation Setup and Testing
2.18.1 Initializing the Data Directory
2.18.2 Starting the Server
2.18.3 Testing the Server
2.18.4 Securing the Initial MySQL Accounts
2.18.5 Starting and Stopping MySQL Automatically
2.19 Upgrading or Downgrading MySQL
2.19.1 Upgrading MySQL
2.19.2 Downgrading MySQL
2.19.3 Checking Whether Tables or Indexes Must Be Rebuilt
2.19.4 Rebuilding or Repairing Tables or Indexes
2.19.5 Copying MySQL Databases to Another Machine
2.20 Operating System-Specific Notes
2.20.1 Linux Notes
2.20.2 OS X Notes
2.20.3 Solaris Notes
2.20.4 BSD Notes
2.20.5 Other Unix Notes
2.20.6 OS/2 Notes
2.21 Environment Variables
2.22 Perl Installation Notes
2.22.1 Installing Perl on Unix
2.22.2 Installing ActiveState Perl on Windows
2.22.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 Connecting to the MySQL Server
4.2.3 Specifying Program Options
4.2.4 Using Options on the Command Line
4.2.5 Program Option Modifiers
4.2.6 Using Option Files
4.2.7 Command-Line Options that Affect Option-File Handling
4.2.8 Using Options to Set Program Variables
4.2.9 Option Defaults, Options Expecting Values, and the = Sign
4.2.10 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 make_win_bin_dist — Package MySQL Distribution as Zip Archive
4.4.3 make_win_src_distribution — Create Source Distribution for Windows
4.4.4 mysqlbug — Generate Bug Report
4.4.5 mysql_fix_privilege_tables — Upgrade MySQL System Tables
4.4.6 mysql_install_db — Initialize MySQL Data Directory
4.4.7 mysql_secure_installation — Improve MySQL Installation Security
4.4.8 mysql_tzinfo_to_sql — Load the Time Zone Tables
4.4.9 mysql_upgrade — Check Tables for MySQL Upgrade
4.5 MySQL Client Programs
4.5.1 mysql — The MySQL Command-Line Tool
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.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 mysqlmanager — The MySQL Instance Manager
4.6.11 mysql_convert_table_format — Convert Tables to Use a Given Storage Engine
4.6.12 mysql_explain_log — Use EXPLAIN on Statements in Query Log
4.6.13 mysql_find_rows — Extract SQL Statements from Files
4.6.14 mysql_fix_extensions — Normalize Table File Name Extensions
4.6.15 mysql_setpermission — Interactively Set Permissions in Grant Tables
4.6.16 mysql_tableinfo — Generate Database Metadata
4.6.17 mysql_waitpid — Kill Process and Wait for Its Termination
4.6.18 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 — Explain Error Codes
4.8.2 replace — A String-Replacement Utility
4.8.3 resolveip — Resolve Host name to IP Address or Vice Versa
5 MySQL Server Administration
5.1 The MySQL Server
5.1.1 Server Option and Variable Reference
5.1.2 Server Configuration Defaults
5.1.3 Server Command Options
5.1.4 Server System Variables
5.1.5 Using System Variables
5.1.6 Server Status Variables
5.1.7 Server SQL Modes
5.1.8 Server-Side Help
5.1.9 Server Response to Signals
5.1.10 The Server Shutdown Process
5.2 The MySQL Data Directory
5.3 The mysql System Database
5.4 MySQL Server Logs
5.4.1 The Error Log
5.4.2 The General Query Log
5.4.3 The Binary Log
5.4.4 The Slow Query Log
5.4.5 Server Log Maintenance
5.5 Running Multiple MySQL Instances on One Machine
5.5.1 Setting Up Multiple Data Directories
5.5.2 Running Multiple MySQL Instances on Windows
5.5.3 Running Multiple MySQL Instances on Unix
5.5.4 Using Client Programs in a Multiple-Server Environment
6 Security
6.1 General Security Issues
6.1.1 Security Guidelines
6.1.2 Keeping Passwords Secure
6.1.3 Making MySQL Secure Against Attackers
6.1.4 Security-Related mysqld Options and Variables
6.1.5 How to Run MySQL as a Normal User
6.1.6 Security Issues with LOAD DATA LOCAL
6.1.7 Client Programming Security Guidelines
6.2 The MySQL Access Privilege System
6.2.1 Privileges Provided by MySQL
6.2.2 Grant Tables
6.2.3 Specifying Account Names
6.2.4 Access Control, Stage 1: Connection Verification
6.2.5 Access Control, Stage 2: Request Verification
6.2.6 When Privilege Changes Take Effect
6.2.7 Troubleshooting Problems Connecting to MySQL
6.3 MySQL User Account Management
6.3.1 User Names and Passwords
6.3.2 Adding User Accounts
6.3.3 Removing User Accounts
6.3.4 Setting Account Resource Limits
6.3.5 Assigning Account Passwords
6.3.6 Using Secure Connections
6.3.7 Creating SSL Certificates and Keys Using openssl
6.3.8 Connecting to MySQL Remotely from Windows with SSH
6.3.9 SQL-Based MySQL Account Activity Auditing
7 Backup and Recovery
7.1 Backup and Recovery Types
7.2 Database Backup Methods
7.3 Example Backup and Recovery Strategy
7.3.1 Establishing a Backup Policy
7.3.2 Using Backups for Recovery
7.3.3 Backup Strategy Summary
7.4 Using mysqldump for Backups
7.4.1 Dumping Data in SQL Format with mysqldump
7.4.2 Reloading SQL-Format Backups
7.4.3 Dumping Data in Delimited-Text Format with mysqldump
7.4.4 Reloading Delimited-Text Format Backups
7.4.5 mysqldump Tips
7.5 Point-in-Time (Incremental) Recovery Using the Binary Log
7.5.1 Point-in-Time Recovery Using Event Times
7.5.2 Point-in-Time Recovery Using Event Positions
7.6 MyISAM Table Maintenance and Crash Recovery
7.6.1 Using myisamchk for Crash Recovery
7.6.2 How to Check MyISAM Tables for Errors
7.6.3 How to Repair MyISAM Tables
7.6.4 MyISAM Table Optimization
7.6.5 Setting Up a MyISAM Table Maintenance Schedule
8 Optimization
8.1 Optimization Overview
8.2 Optimizing SQL Statements
8.2.1 Optimizing SELECT Statements
8.2.2 Optimizing DML Statements
8.2.3 Optimizing Database Privileges
8.2.4 Other Optimization Tips
8.3 Optimization and Indexes
8.3.1 How MySQL Uses Indexes
8.3.2 Using Primary Keys
8.3.3 Using Foreign Keys
8.3.4 Column Indexes
8.3.5 Multiple-Column Indexes
8.3.6 Verifying Index Usage
8.3.7 MyISAM Index Statistics Collection
8.3.8 Comparison of B-Tree and Hash Indexes
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.5 Optimizing for MyISAM Tables
8.5.1 Optimizing MyISAM Queries
8.5.2 Bulk Data Loading for MyISAM Tables
8.5.3 Speed of REPAIR TABLE Statements
8.6 Optimizing for InnoDB Tables
8.6.1 Optimizing Storage Layout for InnoDB Tables
8.6.2 Optimizing InnoDB Transaction Management
8.6.3 Optimizing InnoDB Redo Logging
8.6.4 Bulk Data Loading for InnoDB Tables
8.6.5 Optimizing InnoDB Queries
8.6.6 Optimizing InnoDB DDL Operations
8.6.7 Optimizing InnoDB Disk I/O
8.6.8 Optimizing InnoDB for Systems with Many Tables
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 EXPLAIN EXTENDED Output Format
8.8.4 Estimating Query Performance
8.9 Controlling the Query Optimizer
8.9.1 Controlling Query Plan Evaluation
8.9.2 Index Hints
8.10 Buffering and Caching
8.10.1 The MyISAM Key Cache
8.10.2 The InnoDB Buffer Pool
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 External Locking
8.12 Optimizing the MySQL Server
8.12.1 System Factors and Startup Parameter Tuning
8.12.2 Tuning Server Parameters
8.12.3 Optimizing Disk I/O
8.12.4 Using Symbolic Links
8.12.5 Optimizing Memory Use
8.12.6 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.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 MySQL Cluster Thread States
9 Language Structure
9.1 Literal Values
9.1.1 String Literals
9.1.2 Number Literals
9.1.3 Date and Time Literals
9.1.4 Hexadecimal Literals
9.1.5 Boolean Literals
9.1.6 Bit-Field Literals
9.1.7 NULL Values
9.2 Schema Object Names
9.2.1 Identifier Qualifiers
9.2.2 Identifier Case Sensitivity
9.2.3 Function Name Parsing and Resolution
9.3 Keywords and Reserved Words
9.4 User-Defined Variables
9.5 Expression Syntax
9.6 Comment Syntax
10 Globalization
10.1 Character Set Support
10.1.1 Character Sets and Collations in General
10.1.2 Character Sets and Collations in MySQL
10.1.3 Specifying Character Sets and Collations
10.1.4 Connection Character Sets and Collations
10.1.5 Configuring the Character Set and Collation for Applications
10.1.6 Character Set for Error Messages
10.1.7 Collation Issues
10.1.8 String Repertoire
10.1.9 Operations Affected by Character Set Support
10.1.10 Unicode Support
10.1.11 UTF-8 for Metadata
10.1.12 Column Character Set Conversion
10.1.13 Character Sets and Collations That MySQL Supports
10.2 Setting the Error Message Language
10.3 Adding a Character Set
10.3.1 Character Definition Arrays
10.3.2 String Collating Support for Complex Character Sets
10.3.3 Multi-Byte Character Support for Complex Character Sets
10.4 Adding a Collation to a Character Set
10.4.1 Collation Implementation Types
10.4.2 Choosing a Collation ID
10.4.3 Adding a Simple Collation to an 8-Bit Character Set
10.4.4 Adding a UCA Collation to a Unicode Character Set
10.5 Character Set Configuration
10.6 MySQL Server Time Zone Support
10.6.1 Staying Current with Time Zone Changes
10.6.2 Time Zone Leap Second Support
10.7 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 YEAR(2) Limitations and Migrating to YEAR(4)
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 Two-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 Extensions for Spatial Data
11.5.1 Spatial Data Types
11.5.2 The OpenGIS Geometry Model
11.5.3 Using Spatial Data
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
12.5.1 String Comparison Functions
12.5.2 Regular Expressions
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 Bit Functions and Operators
12.12 Encryption and Compression Functions
12.13 Information Functions
12.14 Spatial Analysis Functions
12.14.1 Spatial Function Reference
12.14.2 Argument Handling by Spatial Functions
12.14.3 Functions That Create Geometry Values from WKT Values
12.14.4 Functions That Create Geometry Values from WKB Values
12.14.5 MySQL-Specific Functions That Create Geometry Values
12.14.6 Geometry Format Conversion Functions
12.14.7 Geometry Property Functions
12.14.8 Spatial Operator Functions
12.14.9 Functions That Test Spatial Relations Between Geometry Objects
12.15 Miscellaneous Functions
12.16 GROUP BY (Aggregate) Functions
12.16.1 GROUP BY (Aggregate) Function Descriptions
12.16.2 GROUP BY Modifiers
12.16.3 MySQL Handling of GROUP BY
12.17 Precision Math
12.17.1 Types of Numeric Values
12.17.2 DECIMAL Data Type Characteristics
12.17.3 Expression Handling
12.17.4 Rounding Behavior
12.17.5 Precision Math Examples
13 SQL Statement Syntax
13.1 Data Definition Statements
13.1.1 ALTER DATABASE Syntax
13.1.2 ALTER FUNCTION Syntax
13.1.4 ALTER TABLE Syntax
13.1.5 ALTER VIEW Syntax
13.1.8 CREATE INDEX Syntax
13.1.10 CREATE TABLE Syntax
13.1.11 CREATE TRIGGER Syntax
13.1.12 CREATE VIEW Syntax
13.1.13 DROP DATABASE Syntax
13.1.14 DROP FUNCTION Syntax
13.1.15 DROP INDEX Syntax
13.1.17 DROP TABLE Syntax
13.1.18 DROP TRIGGER Syntax
13.1.19 DROP VIEW Syntax
13.1.20 RENAME TABLE Syntax
13.1.21 TRUNCATE TABLE Syntax
13.2 Data Manipulation Statements
13.2.1 CALL Syntax
13.2.2 DELETE Syntax
13.2.3 DO Syntax
13.2.4 HANDLER Syntax
13.2.5 INSERT Syntax
13.2.6 LOAD DATA INFILE Syntax
13.2.7 REPLACE Syntax
13.2.8 SELECT Syntax
13.2.9 Subquery Syntax
13.2.10 UPDATE Syntax
13.3 MySQL Transactional and Locking Statements
13.3.2 Statements That Cannot Be Rolled Back
13.3.3 Statements That Cause an Implicit Commit
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 SQL Syntax for Prepared Statements
13.5.1 PREPARE Syntax
13.5.2 EXECUTE Syntax
13.6 MySQL Compound-Statement Syntax
13.6.1 BEGIN ... END Compound-Statement Syntax
13.6.2 Statement Label Syntax
13.6.3 DECLARE Syntax
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 User-Defined Function Statements
13.7.4 SET Syntax
13.7.5 SHOW Syntax
13.7.6 Other Administrative Statements
13.8 MySQL Utility Statements
13.8.1 DESCRIBE Syntax
13.8.2 EXPLAIN Syntax
13.8.3 HELP Syntax
13.8.4 USE Syntax
14 Storage Engines
14.1 The MyISAM Storage Engine
14.1.1 MyISAM Startup Options
14.1.2 Space Needed for Keys
14.1.3 MyISAM Table Storage Formats
14.1.4 MyISAM Table Problems
14.2 The InnoDB Storage Engine
14.2.1 Configuring InnoDB
14.2.2 InnoDB Startup Options and System Variables
14.2.3 Creating and Using InnoDB Tables
14.2.4 Changing the Number or Size of InnoDB Redo Log Files
14.2.5 Resizing the InnoDB System Tablespace
14.2.6 Backing Up and Recovering an InnoDB Database
14.2.7 Moving an InnoDB Database to Another Machine
14.2.8 InnoDB Transaction Model and Locking
14.2.9 InnoDB Multi-Versioning
14.2.10 InnoDB Table and Index Structures
14.2.11 InnoDB Disk I/O and File Space Management
14.2.12 InnoDB Error Handling
14.2.13 InnoDB Troubleshooting
14.2.14 Limits on InnoDB Tables
14.3 The MERGE Storage Engine
14.3.1 MERGE Table Advantages and Disadvantages
14.3.2 MERGE Table Problems
14.4 The MEMORY (HEAP) Storage Engine
14.5 The BDB (BerkeleyDB) Storage Engine
14.5.1 Operating Systems Supported by BDB
14.5.2 Installing BDB
14.5.3 BDB Startup Options
14.5.4 Characteristics of BDB Tables
14.5.5 Restrictions on BDB Tables
14.5.6 Errors That May Occur When Using BDB Tables
14.6 The EXAMPLE Storage Engine
14.7 The FEDERATED Storage Engine
14.7.1 Description of the FEDERATED Storage Engine
14.7.2 How to Use FEDERATED Tables
14.7.3 Limitations of the FEDERATED Storage Engine
14.8 The ARCHIVE Storage Engine
14.9 The CSV Storage Engine
14.10 The BLACKHOLE Storage Engine
15 High Availability and Scalability
15.1 Using MySQL within an Amazon EC2 Instance
15.1.1 Setting Up MySQL on an EC2 AMI
15.1.2 EC2 Instance Limitations
15.1.3 Deploying a MySQL Database Using EC2
15.2 Using ZFS Replication
15.2.1 Using ZFS for File System Replication
15.2.2 Configuring MySQL for ZFS Replication
15.2.3 Handling MySQL Recovery with ZFS
15.3 Using MySQL with memcached
15.3.1 Installing memcached
15.3.2 Using memcached
15.3.3 Developing a memcached Application
15.3.4 Getting memcached Statistics
15.3.5 memcached FAQ
16 Replication
16.1 Replication Configuration
16.1.1 How to Set Up Replication
16.1.2 Replication and Binary Logging Options and Variables
16.1.3 Common Replication Administration Tasks
16.2 Replication Implementation
16.2.1 Replication Implementation Details
16.2.2 Replication Relay and Status Logs
16.2.3 How Servers Evaluate Replication Filtering Rules
16.3 Replication Solutions
16.3.1 Using Replication for Backups
16.3.2 Using Replication with Different Master and Slave Storage Engines
16.3.3 Using Replication for Scale-Out
16.3.4 Replicating Different Databases to Different Slaves
16.3.5 Improving Replication Performance
16.3.6 Switching Masters During Failover
16.3.7 Setting Up Replication to Use Secure Connections
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 Setup
16.4.4 Troubleshooting Replication
16.4.5 How to Report Replication Bugs or Problems
17 MySQL Cluster
17.1 MySQL Cluster Overview
17.1.1 MySQL Cluster Core Concepts
17.1.2 MySQL Cluster Nodes, Node Groups, Replicas, and Partitions
17.1.3 MySQL Cluster Hardware, Software, and Networking Requirements
17.1.4 What is New in MySQL Cluster
17.1.5 Known Limitations of MySQL Cluster
17.2 MySQL Cluster Installation and Upgrades
17.2.1 Installing MySQL Cluster on Linux
17.2.2 Initial Configuration of MySQL Cluster
17.2.3 Initial Startup of MySQL Cluster
17.2.4 MySQL Cluster Example with Tables and Data
17.2.5 Safe Shutdown and Restart of MySQL Cluster
17.2.6 Upgrading and Downgrading MySQL Cluster
17.3 MySQL Cluster Configuration
17.3.1 Quick Test Setup of MySQL Cluster
17.3.2 Overview of MySQL Cluster Configuration Parameters, Options, and Variables
17.3.3 MySQL Cluster Configuration Files
17.3.4 Using High-Speed Interconnects with MySQL Cluster
17.4 MySQL Cluster Programs
17.4.1 ndbd — The MySQL Cluster Data Node Daemon
17.4.2 ndb_mgmd — The MySQL Cluster Management Server Daemon
17.4.3 ndb_mgm — The MySQL Cluster Management Client
17.4.4 ndb_config — Extract MySQL Cluster Configuration Information
17.4.5 ndb_cpcd — Automate Testing for NDB Development
17.4.6 ndb_delete_all — Delete All Rows from an NDB Table
17.4.7 ndb_desc — Describe NDB Tables
17.4.8 ndb_drop_index — Drop Index from an NDB Table
17.4.9 ndb_drop_table — Drop an NDB Table
17.4.10 ndb_error_reporter — NDB Error-Reporting Utility
17.4.11 ndb_print_backup_file — Print NDB Backup File Contents
17.4.12 ndb_print_schema_file — Print NDB Schema File Contents
17.4.13 ndb_print_sys_file — Print NDB System File Contents
17.4.14 ndb_restore — Restore a MySQL Cluster Backup
17.4.15 ndb_select_all — Print Rows from an NDB Table
17.4.16 ndb_select_count — Print Row Counts for NDB Tables
17.4.17 ndb_show_tables — Display List of NDB Tables
17.4.18 — NDBCLUSTER Size Requirement Estimator
17.4.19 ndb_waiter — Wait for MySQL Cluster to Reach a Given Status
17.4.20 Options Common to MySQL Cluster Programs — Options Common to MySQL Cluster Programs
17.5 Management of MySQL Cluster
17.5.1 Summary of MySQL Cluster Start Phases
17.5.2 Commands in the MySQL Cluster Management Client
17.5.3 Online Backup of MySQL Cluster
17.5.4 MySQL Server Usage for MySQL Cluster
17.5.5 Performing a Rolling Restart of a MySQL Cluster
17.5.6 Event Reports Generated in MySQL Cluster
17.5.7 MySQL Cluster Log Messages
17.5.8 MySQL Cluster Single User Mode
17.5.9 Quick Reference: MySQL Cluster SQL Statements
17.5.10 MySQL Cluster Security Issues
18 Stored Programs and Views
18.1 Defining Stored Programs
18.2 Using Stored Routines (Procedures and Functions)
18.2.1 Stored Routine Syntax
18.2.2 Stored Routines and MySQL Privileges
18.2.3 Stored Routine Metadata
18.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()
18.3 Using Triggers
18.3.1 Trigger Syntax and Examples
18.3.2 Trigger Metadata
18.4 Using Views
18.4.1 View Syntax
18.4.2 View Processing Algorithms
18.4.3 Updatable and Insertable Views
18.4.4 The View WITH CHECK OPTION Clause
18.4.5 View Metadata
18.5 Access Control for Stored Programs and Views
18.6 Binary Logging of Stored Programs
19.18 Extensions to SHOW Statements
20 Connectors and APIs
20.1 MySQL Connector/ODBC
20.2 MySQL Connector/Net
20.3 MySQL Connector/J
20.4 MySQL Connector/C
20.5 libmysqld, the Embedded MySQL Server Library
20.6 MySQL C API
20.6.1 MySQL C API Implementations
20.6.2 Simultaneous MySQL Server and Connector/C Installations
20.6.3 Example C API Client Programs
20.6.4 Building and Running C API Client Programs
20.6.5 C API Data Structures
20.6.6 C API Function Overview
20.6.7 C API Function Descriptions
20.6.8 C API Prepared Statements
20.6.9 C API Prepared Statement Data Structures
20.6.10 C API Prepared Statement Function Overview
20.6.11 C API Prepared Statement Function Descriptions
20.6.12 C API Threaded Function Descriptions
20.6.13 C API Embedded Server Function Descriptions
20.6.14 Common Questions and Problems When Using the C API
20.6.15 Controlling Automatic Reconnection Behavior
20.6.16 C API Support for Multiple Statement Execution
20.6.17 C API Prepared Statement Problems
20.6.18 C API Prepared Statement Handling of Date and Time Values
20.6.19 C API Support for Prepared CALL Statements
20.8 MySQL Perl API
20.9 MySQL Python API
20.10 MySQL Ruby APIs
20.10.1 The MySQL/Ruby API
20.10.2 The Ruby/MySQL API
20.11 MySQL Tcl API
20.12 MySQL Eiffel Wrapper
21 Extending MySQL
21.1 MySQL Internals
21.1.1 MySQL Threads
21.1.2 The MySQL Test Suite
21.2 Adding New Functions to MySQL
21.2.1 Features of the User-Defined Function Interface
21.2.2 Adding a New User-Defined Function
21.2.3 Adding a New Native Function
21.3 Debugging and Porting MySQL
21.3.1 Debugging a MySQL Server
21.3.2 Debugging a MySQL Client
21.3.3 The DBUG Package
22 MySQL Enterprise Edition
22.1 MySQL Enterprise Monitor Overview
22.2 MySQL Enterprise Backup Overview
22.3 MySQL Enterprise Security Overview
22.4 MySQL Enterprise Encryption Overview
22.5 MySQL Enterprise Audit Overview
22.6 MySQL Enterprise Firewall Overview
22.7 MySQL Enterprise Thread Pool Overview
A MySQL 5.0 Frequently Asked Questions
A.1 MySQL 5.0 FAQ: General
A.2 MySQL 5.0 FAQ: Storage Engines
A.3 MySQL 5.0 FAQ: Server SQL Mode
A.4 MySQL 5.0 FAQ: Stored Procedures and Functions
A.5 MySQL 5.0 FAQ: Triggers
A.6 MySQL 5.0 FAQ: Views
A.8 MySQL 5.0 FAQ: Migration
A.9 MySQL 5.0 FAQ: Security
A.10 MySQL 5.0 FAQ: MySQL Cluster
A.11 MySQL 5.0 FAQ: MySQL Chinese, Japanese, and Korean Character Sets
A.12 MySQL 5.0 FAQ: Connectors & APIs
A.13 MySQL 5.0 FAQ: Replication
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 Server-Side Cursors
C.3 Restrictions on Subqueries
C.4 Restrictions on Views
C.5 Restrictions on XA Transactions
C.6 Restrictions on Character Sets
C.7 Limits in MySQL
C.7.1 Limits on Joins
C.7.2 Limits on Number of Databases and Tables
C.7.3 Limits on Table Size
C.7.4 Limits on Table Column Count and Row Size
C.7.5 Limits Imposed by .frm File Structure
C.7.6 Windows Platform Limitations
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