MySQL 5.1 Reference Manual

Table of Contents

Preface

1. General Information

1.1. About This Manual

1.2. Conventions Used in This Manual

1.3. Overview of MySQL AB

1.4. Overview of the MySQL Database Management System

1.4.1. History of MySQL

1.4.2. The Main Features of MySQL

1.4.3. MySQL Stability

1.4.4. How Large MySQL Tables Can Be

1.4.5. Year 2000 Compliance

1.5. Overview of the MaxDB Database Management System

1.5.1. What is MaxDB?

1.5.2. History of MaxDB

1.5.3. Features of MaxDB

1.5.4. Licensing and Support

1.5.5. Feature Differences Between MaxDB and MySQL

1.5.6. Interoperability Features Between MaxDB and MySQL

1.5.7. MaxDB-Related Links

1.6. MySQL Development Roadmap

1.6.1. What's New in MySQL 5.1

1.7. MySQL Information Sources

1.7.1. MySQL Mailing Lists

1.7.2. MySQL Community Support at the MySQL Forums

1.7.3. MySQL Community Support on Internet Relay Chat (IRC)

1.8. How to Report Bugs or Problems

1.9. MySQL Standards Compliance

1.9.1. What Standards MySQL Follows

1.9.2. Selecting SQL Modes

1.9.3. Running MySQL in ANSI Mode

1.9.4. MySQL Extensions to Standard SQL

1.9.5. MySQL Differences from Standard SQL

1.9.6. How MySQL Deals with Constraints

2. Installing and Upgrading MySQL

2.1. General Installation Issues

2.1.1. Operating Systems Supported by MySQL

2.1.2. Choosing Which MySQL 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.2. Standard MySQL Installation Using a Binary Distribution

2.3. Installing MySQL on Windows

2.3.1. Choosing An Installation Package

2.3.2. Installing MySQL with the Automated Installer

2.3.3. Using the MySQL Installation Wizard

2.3.4. Using the Configuration Wizard

2.3.5. Installing MySQL from a Noinstall Zip Archive

2.3.6. Extracting the Install Archive

2.3.7. Creating an Option File

2.3.8. Selecting a MySQL Server type

2.3.9. Starting the Server for the First Time

2.3.10. Starting MySQL from the Windows Command Line

2.3.11. Starting MySQL as a Windows Service

2.3.12. Testing The MySQL Installation

2.3.13. Troubleshooting a MySQL Installation Under Windows

2.3.14. Upgrading MySQL on Windows

2.3.15. MySQL on Windows Compared to MySQL on Unix

2.4. Installing MySQL on Linux

2.5. Installing MySQL on Mac OS X

2.6. Installing MySQL on NetWare

2.7. Installing MySQL on Other Unix-Like Systems

2.8. MySQL Installation Using a Source Distribution

2.8.1. Source Installation Overview

2.8.2. Typical configure Options

2.8.3. Installing from the Development Source Tree

2.8.4. Dealing with Problems Compiling MySQL

2.8.5. MIT-pthreads Notes

2.8.6. Installing MySQL from Source on Windows

2.8.7. Compiling MySQL Clients on Windows

2.9. Post-Installation Setup and Testing

2.9.1. Windows Post-Installation Procedures

2.9.2. Unix Post-Installation Procedures

2.9.3. Securing the Initial MySQL Accounts

2.10. Upgrading MySQL

2.10.1. Upgrading from MySQL 5.0

2.10.2. Copying MySQL Databases to Another Machine

2.11. Downgrading MySQL

2.12. Operating System-Specific Notes

2.12.1. Linux Notes

2.12.2. Mac OS X Notes

2.12.3. Solaris Notes

2.12.4. BSD Notes

2.12.5. Other Unix Notes

2.12.6. OS/2 Notes

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 Field

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. Queries from the Twin Project

3.7.1. Find All Non-distributed Twins

3.7.2. Show a Table of Twin Pair Status

3.8. Using MySQL with Apache

4. Using MySQL Programs

4.1. Overview of MySQL Programs

4.2. Invoking MySQL Programs

4.3. Specifying Program Options

4.3.1. Using Options on the Command Line

4.3.2. Using Option Files

4.3.3. Using Environment Variables to Specify Options

4.3.4. Using Options to Set Program Variables

5. Database Administration

5.1. Overview of Server-Side Programs

5.2. mysqld — The MySQL Server

5.2.1. mysqld Command Options

5.2.2. Server System Variables

5.2.3. Using System Variables

5.2.4. Server Status Variables

5.2.5. The Server SQL Mode

5.2.6. The MySQL Server Shutdown Process

5.3. MySQL Server Startup Programs

5.3.1. mysqld_safe — MySQL Server Startup Script

5.3.2. mysql.server — MySQL Server Startup Script

5.3.3. mysqld_multi — Manage Multiple MySQL Servers

5.4. mysqlmanager — The MySQL Instance Manager

5.4.1. Starting the MySQL Server with MySQL Instance Manager

5.4.2. Connecting to the MySQL Instance Manager and Creating User Accounts

5.4.3. MySQL Instance Manager Command Options

5.4.4. MySQL Instance Manager Configuration Files

5.4.5. Commands Recognized by the MySQL Instance Manager

5.5. Installation-Related Programs

5.5.1. mysql_fix_privilege_tables — Upgrade MySQL System Tables

5.5.2. mysql_upgrade — Check Tables for MySQL Upgrade

5.6. General Security Issues

5.6.1. General Security Guidelines

5.6.2. Making MySQL Secure Against Attackers

5.6.3. Security-Related mysqld Options

5.6.4. Security Issues with LOAD DATA LOCAL

5.6.5. How to Run MySQL as a Normal User

5.7. The MySQL Access Privilege System

5.7.1. What the Privilege System Does

5.7.2. How the Privilege System Works

5.7.3. Privileges Provided by MySQL

5.7.4. Connecting to the MySQL Server

5.7.5. Access Control, Stage 1: Connection Verification

5.7.6. Access Control, Stage 2: Request Verification

5.7.7. When Privilege Changes Take Effect

5.7.8. Causes of Access denied Errors

5.7.9. Password Hashing as of MySQL 4.1

5.8. MySQL User Account Management

5.8.1. MySQL Usernames and Passwords

5.8.2. Adding New User Accounts to MySQL

5.8.3. Removing User Accounts from MySQL

5.8.4. Limiting Account Resources

5.8.5. Assigning Account Passwords

5.8.6. Keeping Your Password Secure

5.8.7. Using Secure Connections

5.9. Backup and Recovery

5.9.1. Database Backups

5.9.2. Example Backup and Recovery Strategy

5.9.3. Point-in-Time Recovery

5.9.4. Table Maintenance and Crash Recovery

5.10. MySQL Localization and International Usage

5.10.1. The Character Set Used for Data and Sorting

5.10.2. Setting the Error Message Language

5.10.3. Adding a New Character Set

5.10.4. The Character Definition Arrays

5.10.5. String Collating Support

5.10.6. Multi-Byte Character Support

5.10.7. Problems With Character Sets

5.10.8. MySQL Server Time Zone Support

5.11. MySQL Server Logs

5.11.1. Server Log Tables

5.11.2. The Error Log

5.11.3. The General Query Log

5.11.4. The Binary Log

5.11.5. The Slow Query Log

5.11.6. Server Log Maintenance

5.12. Running Multiple MySQL Servers on the Same Machine

5.12.1. Running Multiple Servers on Windows

5.12.2. Running Multiple Servers on Unix

5.12.3. Using Client Programs in a Multiple-Server Environment

5.13. The MySQL Query Cache

5.13.1. How the Query Cache Operates

5.13.2. Query Cache SELECT Options

5.13.3. Query Cache Configuration

5.13.4. Query Cache Status and Maintenance

6. Replication

6.1. Introduction to Replication

6.2. Replication Implementation Overview

6.3. Row-Based Replication

6.4. Replication Implementation Details

6.4.1. Replication Master Thread States

6.4.2. Replication Slave I/O Thread States

6.4.3. Replication Slave SQL Thread States

6.4.4. Replication Relay and Status Files

6.5. How to Set Up Replication

6.6. Replication Compatibility Between MySQL Versions

6.7. Upgrading a Replication Setup

6.7.1. Upgrading Replication to 5.0

6.8. Replication Features and Known Problems

6.9. Replication Startup Options

6.10. How Servers Evaluate Replication Rules

6.11. Replication FAQ

6.12. Comparison of Statement-Based Versus Row-Based Replication

6.13. Troubleshooting Replication

6.14. How to Report Replication Bugs or Problems

6.15. Auto-Increment in Multiple-Master Replication

7. Optimization

7.1. Optimization Overview

7.1.1. MySQL Design Limitations and Tradeoffs

7.1.2. Designing Applications for Portability

7.1.3. What We Have Used MySQL For

7.1.4. The MySQL Benchmark Suite

7.1.5. Using Your Own Benchmarks

7.2. Optimizing SELECT and Other Statements

7.2.1. Optimizing Queries with EXPLAIN

7.2.2. Estimating Query Performance

7.2.3. Speed of SELECT Queries

7.2.4. WHERE Clause Optimization

7.2.5. Range Optimization

7.2.6. Index Merge Optimization

7.2.7. IS NULL Optimization

7.2.8. DISTINCT Optimization

7.2.9. LEFT JOIN and RIGHT JOIN Optimization

7.2.10. Nested Join Optimization

7.2.11. Outer Join Simplification

7.2.12. ORDER BY Optimization

7.2.13. GROUP BY Optimization

7.2.14. LIMIT Optimization

7.2.15. How to Avoid Table Scans

7.2.16. Speed of INSERT Statements

7.2.17. Speed of UPDATE Statements

7.2.18. Speed of DELETE Statements

7.2.19. Other Optimization Tips

7.3. Locking Issues

7.3.1. Locking Methods

7.3.2. Table Locking Issues

7.3.3. Concurrent Inserts

7.4. Optimizing Database Structure

7.4.1. Design Choices

7.4.2. Make Your Data as Small as Possible

7.4.3. Column Indexes

7.4.4. Multiple-Column Indexes

7.4.5. How MySQL Uses Indexes

7.4.6. The MyISAM Key Cache

7.4.7. MyISAM Index Statistics Collection

7.4.8. How MySQL Opens and Closes Tables

7.4.9. Drawbacks to Creating Many Tables in the Same Database

7.5. Optimizing the MySQL Server

7.5.1. System Factors and Startup Parameter Tuning

7.5.2. Tuning Server Parameters

7.5.3. Controlling Query Optimizer Performance

7.5.4. How Compiling and Linking Affects the Speed of MySQL

7.5.5. How MySQL Uses Memory

7.5.6. How MySQL Uses DNS

7.6. Disk Issues

7.6.1. Using Symbolic Links

8. Client and Utility Programs

8.1. Overview of Client and Utility Programs

8.2. myisamchk — MyISAM Table-Maintenance Utility

8.2.1. myisamchk General Options

8.2.2. myisamchk Check Options

8.2.3. myisamchk Repair Options

8.2.4. Other myisamchk Options

8.2.5. myisamchk Memory Usage

8.3. myisamlog — Display MyISAM Log File Contents

8.4. myisampack — Generate Compressed, Read-Only MyISAM Tables

8.5. mysql — The MySQL Command-Line Tool

8.5.1. mysql Options

8.5.2. mysql Commands

8.5.3. Executing SQL Statements from a Text File

8.5.4. mysql Tips

8.6. mysqlaccess — Client for Checking Access Privileges

8.7. mysqladmin — Client for Administering a MySQL Server

8.8. mysqlbinlog — Utility for Processing Binary Log Files

8.9. mysqlcheck — A Table Maintenance and Repair Program

8.10. mysqldump — A Database Backup Program

8.11. mysqlhotcopy — A Database Backup Program

8.12. mysqlimport — A Data Import Program

8.13. mysqlshow — Display Database, Table, and Column Information

8.14. mysqlslap — Load Emulation Client

8.15. mysql_zap — Kill Processes That Match a Pattern

8.16. perror — Explain Error Codes

8.17. replace — A String-Replacement Utility

9. Language Structure

9.1. Literal Values

9.1.1. Strings

9.1.2. Numbers

9.1.3. Hexadecimal Values

9.1.4. Boolean Values

9.1.5. Bit-Field Values

9.1.6. NULL Values

9.2. Database, Table, Index, Column, and Alias Names

9.2.1. Identifier Qualifiers

9.2.2. Identifier Case Sensitivity

9.3. User-Defined Variables

9.4. Comment Syntax

9.5. Treatment of Reserved Words in MySQL

10. Character Set Support

10.1. Character Sets and Collations in General

10.2. Character Sets and Collations in MySQL

10.3. Specifying Character Sets and Collations

10.3.1. Server Character Set and Collation

10.3.2. Database Character Set and Collation

10.3.3. Table Character Set and Collation

10.3.4. Column Character Set and Collation

10.3.5. Character String Literal Character Set and Collation

10.3.6. National Character Set

10.3.7. Examples of Character Set and Collation Assignment

10.3.8. Compatibility with Other DBMSs

10.4. Connection Character Sets and Collations

10.5. Collation Issues

10.5.1. Using COLLATE in SQL Statements

10.5.2. COLLATE Clause Precedence

10.5.3. BINARY Operator

10.5.4. Some Special Cases Where the Collation Determination Is Tricky

10.5.5. Collations Must Be for the Right Character Set

10.5.6. An Example of the Effect of Collation

10.6. Operations Affected by Character Set Support

10.6.1. Result Strings

10.6.2. CONVERT() and CAST()

10.6.3. SHOW Statements and INFORMATION_SCHEMA

10.7. Unicode Support

10.8. UTF-8 for Metadata

10.9. Character Sets and Collations That MySQL Supports

10.9.1. Unicode Character Sets

10.9.2. West European Character Sets

10.9.3. Central European Character Sets

10.9.4. South European and Middle East Character Sets

10.9.5. Baltic Character Sets

10.9.6. Cyrillic Character Sets

10.9.7. Asian Character Sets

11. Data Types

11.1. Data Type Overview

11.1.1. Overview of Numeric Types

11.1.2. Overview of Date and Time Types

11.1.3. Overview of String Types

11.1.4. Data Type Default Values

11.2. Numeric Types

11.3. Date and Time Types

11.3.1. The DATETIME, DATE, and TIMESTAMP Types

11.3.2. The TIME Type

11.3.3. The YEAR Type

11.3.4. Y2K Issues and Date Types

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. Data Type Storage Requirements

11.6. Choosing the Right Type for a Column

11.7. Using Data Types from Other Database Engines

12. Functions and Operators

12.1. Operators

12.1.1. Operator Precedence

12.1.2. Type Conversion in Expression Evaluation

12.1.3. Comparison Functions and Operators

12.1.4. Logical Operators

12.2. Control Flow Functions

12.3. String Functions

12.3.1. String Comparison Functions

12.4. Numeric Functions

12.4.1. Arithmetic Operators

12.4.2. Mathematical Functions

12.5. Date and Time Functions

12.6. What Calendar Is Used By MySQL?

12.7. Full-Text Search Functions

12.7.1. Boolean Full-Text Searches

12.7.2. Full-Text Searches with Query Expansion

12.7.3. Full-Text Stopwords

12.7.4. Full-Text Restrictions

12.7.5. Fine-Tuning MySQL Full-Text Search

12.8. Cast Functions and Operators

12.9. XML Functions

12.10. Other Functions

12.10.1. Bit Functions

12.10.2. Encryption and Compression Functions

12.10.3. Information Functions

12.10.4. Miscellaneous Functions

12.11. Functions and Modifiers for Use with GROUP BY Clauses

12.11.1. GROUP BY (Aggregate) Functions

12.11.2. GROUP BY Modifiers

12.11.3. GROUP BY with Hidden Fields

13. SQL Statement Syntax

13.1. Data Definition Statements

13.1.1. ALTER DATABASE Syntax

13.1.2. ALTER TABLE Syntax

13.1.3. CREATE DATABASE Syntax

13.1.4. CREATE INDEX Syntax

13.1.5. CREATE TABLE Syntax

13.1.6. DROP DATABASE Syntax

13.1.7. DROP INDEX Syntax

13.1.8. DROP TABLE Syntax

13.1.9. RENAME DATABASE Syntax

13.1.10. RENAME TABLE Syntax

13.2. Data Manipulation Statements

13.2.1. DELETE Syntax

13.2.2. DO Syntax

13.2.3. HANDLER Syntax

13.2.4. INSERT Syntax

13.2.5. LOAD DATA INFILE Syntax

13.2.6. REPLACE Syntax

13.2.7. SELECT Syntax

13.2.8. Subquery Syntax

13.2.9. TRUNCATE Syntax

13.2.10. UPDATE Syntax

13.3. MySQL Utility Statements

13.3.1. DESCRIBE Syntax

13.3.2. USE Syntax

13.4. MySQL Transactional and Locking Statements

13.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax

13.4.2. Statements That Cannot Be Rolled Back

13.4.3. Statements That Cause an Implicit Commit

13.4.4. SAVEPOINT and ROLLBACK TO SAVEPOINT Syntax

13.4.5. LOCK TABLES and UNLOCK TABLES Syntax

13.4.6. SET TRANSACTION Syntax

13.4.7. XA Transactions

13.5. Database Administration Statements

13.5.1. Account Management Statements

13.5.2. Table Maintenance Statements

13.5.3. SET Syntax

13.5.4. SHOW Syntax

13.5.5. Other Administrative Statements

13.6. Replication Statements

13.6.1. SQL Statements for Controlling Master Servers

13.6.2. SQL Statements for Controlling Slave Servers

13.7. SQL Syntax for Prepared Statements

14. Storage Engines and Table Types

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. InnoDB Overview

14.2.2. InnoDB Contact Information

14.2.3. InnoDB Configuration

14.2.4. InnoDB Startup Options and System Variables

14.2.5. Creating the InnoDB Tablespace

14.2.6. Creating and Using InnoDB Tables

14.2.7. Adding and Removing InnoDB Data and Log Files

14.2.8. Backing Up and Recovering an InnoDB Database

14.2.9. Moving an InnoDB Database to Another Machine

14.2.10. InnoDB Transaction Model and Locking

14.2.11. InnoDB Performance Tuning Tips

14.2.12. Implementation of Multi-Versioning

14.2.13. InnoDB Table and Index Structures

14.2.14. InnoDB File Space Management and Disk I/O

14.2.15. InnoDB Error Handling

14.2.16. Restrictions on InnoDB Tables

14.2.17. InnoDB Troubleshooting

14.3. The MERGE Storage Engine

14.3.1. 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. Things We Need to Fix for BDB

14.5.6. Restrictions on BDB Tables

14.5.7. 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. Writing a Custom Storage Engine

15.1. Introduction

15.2. Overview

15.3. Creating Storage Engine Source Files

15.4. Creating the handlerton

15.5. Handling Handler Instantiation

15.6. Defining Filename Extensions

15.7. Creating Tables

15.8. Opening a Table

15.9. Implementing Basic Table Scanning

15.9.1. Implementing the store_lock() Function

15.9.2. Implementing the external_lock() Function

15.9.3. Implementing the rnd_init() Function

15.9.4. Implementing the info() Function

15.9.5. Implementing the extra() Function

15.9.6. Implementing the rnd_next() Function

15.10. Closing a Table

15.11. Adding Support for INSERT to a Storage Engine

15.12. Adding Support for UPDATE to a Storage Engine

15.13. Adding Support for DELETE to a Storage Engine

15.14. Supporting Non-Sequential Reads

15.14.1. Implementing the position() Function

15.14.2. Implementing the rnd_pos() Function

15.15. Supporting Indexing

15.15.1. Indexing Overview

15.15.2. Getting Index Information During CREATE TABLE Operations

15.15.3. Creating Index Keys

15.15.4. Parsing Key Information

15.15.5. Providing Index Information to the Optimizer

15.15.6. Preparing for Index Use with index_init()

15.15.7. Cleaning up with index_end()

15.15.8. Implementing the index_read() Function

15.15.9. Implementing the index_read_idx() Function

15.15.10. Implementing the index_next() Function

15.15.11. Implementing the index_prev() Function

15.15.12. Implementing the index_first() Function

15.15.13. Implementing the index_last() Function

15.16. Supporting Transactions

15.16.1. Transaction Overview

15.16.2. Starting a Transaction

15.16.3. Implementing ROLLBACK

15.16.4. Implementing COMMIT

15.16.5. Adding Support for Savepoints

15.17. API Reference

15.17.1. bas_ext

15.17.2. close

15.17.3. create

15.17.4. delete_row

15.17.5. delete_table

15.17.6. external_lock

15.17.7. extra

15.17.8. index_end

15.17.9. index_first

15.17.10. index_init

15.17.11. index_last

15.17.12. index_next

15.17.13. index_prev

15.17.14. index_read_idx

15.17.15. index_read

15.17.16. info

15.17.17. open

15.17.18. position

15.17.19. records_in_range

15.17.20. rnd_init

15.17.21. rnd_next

15.17.22. rnd_pos

15.17.23. start_stmt

15.17.24. store_lock

15.17.25. update_row

15.17.26. write_row

16. MySQL Cluster

16.1. MySQL Cluster Overview

16.2. Basic MySQL Cluster Concepts

16.2.1. MySQL Cluster Nodes, Node Groups, Replicas, and Partitions

16.3. Simple Multi-Computer How-To

16.3.1. Hardware, Software, and Networking

16.3.2. Multi-Computer Installation

16.3.3. Multi-Computer Configuration

16.3.4. Initial Startup

16.3.5. Loading Sample Data and Performing Queries

16.3.6. Safe Shutdown and Restart

16.4. MySQL Cluster Configuration

16.4.1. Building MySQL Cluster from Source Code

16.4.2. Installing the Software

16.4.3. Quick Test Setup of MySQL Cluster

16.4.4. Configuration File

16.5. Process Management in MySQL Cluster

16.5.1. MySQL Server Process Usage for MySQL Cluster

16.5.2. ndbd, the Storage Engine Node Process

16.5.3. ndb_mgmd, the Management Server Process

16.5.4. ndb_mgm, the Management Client Process

16.5.5. Command Options for MySQL Cluster Processes

16.6. Management of MySQL Cluster

16.6.1. MySQL Cluster Startup Phases

16.6.2. Commands in the Management Client

16.6.3. Event Reports Generated in MySQL Cluster

16.6.4. Single-User Mode

16.6.5. On-line Backup of MySQL Cluster

16.7. MySQL Cluster Replication

16.7.1. Abbreviations and Symbols

16.7.2. Assumptions and General Requirements

16.7.3. Known Issues

16.7.4. Replication Schema and Tables

16.7.5. Preparing the Cluster for Replication

16.7.6. Starting Replication (Single Replication Channel)

16.7.7. Using Two Replication Channels

16.7.8. Implementing Failover with MySQL Cluster

16.7.9. MySQL Cluster Backups With Replication

16.8. Using High-Speed Interconnects with MySQL Cluster

16.8.1. Configuring MySQL Cluster to use SCI Sockets

16.8.2. Understanding the Impact of Cluster Interconnects

16.9. Known Limitations of MySQL Cluster

16.10. MySQL Cluster Development Roadmap

16.10.1. MySQL Cluster Changes in MySQL 5.0

16.10.2. MySQL 5.1 Development Roadmap for MySQL Cluster

16.11. MySQL Cluster FAQ

16.12. MySQL Cluster Glossary

17. Partitioning

17.1. Overview of Partitioning in MySQL

17.2. Partition Types

17.2.1. RANGE Partitioning

17.2.2. LIST Partitioning

17.2.3. HASH Partitioning

17.2.4. KEY Partitioning

17.2.5. Subpartitioning

17.2.6. How MySQL Partitioning Handles NULL Values

17.3. Partition Management

17.3.1. Management of RANGE and LIST Partitions

17.3.2. Management of HASH and KEY Partitions

17.3.3. Maintenance of Partitions

17.3.4. Obtaining Information About Partitions

17.4. Restrictions and Limitations on Partitioning

18. Spatial Extensions

18.1. Introduction to MySQL Spatial Support

18.2. The OpenGIS Geometry Model

18.2.1. The Geometry Class Hierarchy

18.2.2. Class Geometry

18.2.3. Class Point

18.2.4. Class Curve

18.2.5. Class LineString

18.2.6. Class Surface

18.2.7. Class Polygon

18.2.8. Class GeometryCollection

18.2.9. Class MultiPoint

18.2.10. Class MultiCurve

18.2.11. Class MultiLineString

18.2.12. Class MultiSurface

18.2.13. Class MultiPolygon

18.3. Supported Spatial Data Formats

18.3.1. Well-Known Text (WKT) Format

18.3.2. Well-Known Binary (WKB) Format

18.4. Creating a Spatially Enabled MySQL Database

18.4.1. MySQL Spatial Data Types

18.4.2. Creating Spatial Values

18.4.3. Creating Spatial Columns

18.4.4. Populating Spatial Columns

18.4.5. Fetching Spatial Data

18.5. Analyzing Spatial Information

18.5.1. Geometry Format Conversion Functions

18.5.2. Geometry Functions

18.5.3. Functions That Create New Geometries from Existing Ones

18.5.4. Functions for Testing Spatial Relations Between Geometric Objects

18.5.5. Relations on Geometry Minimal Bounding Rectangles (MBRs)

18.5.6. Functions That Test Spatial Relationships Between Geometries

18.6. Optimizing Spatial Analysis

18.6.1. Creating Spatial Indexes

18.6.2. Using a Spatial Index

18.7. MySQL Conformance and Compatibility

19. Stored Procedures and Functions

19.1. Stored Routines and the Grant Tables

19.2. Stored Procedure Syntax

19.2.1. CREATE PROCEDURE and CREATE FUNCTION Syntax

19.2.2. ALTER PROCEDURE and ALTER FUNCTION Syntax

19.2.3. DROP PROCEDURE and DROP FUNCTION Syntax

19.2.4. CALL Statement Syntax

19.2.5. BEGIN ... END Compound Statement Syntax

19.2.6. DECLARE Statement Syntax

19.2.7. Variables in Stored Routines

19.2.8. Conditions and Handlers

19.2.9. Cursors

19.2.10. Flow Control Constructs

19.3. Stored Procedures, Functions, Triggers, and Replication: Frequently Asked Questions

19.4. Binary Logging of Stored Routines and Triggers

20. Triggers

20.1. CREATE TRIGGER Syntax

20.2. DROP TRIGGER Syntax

20.3. Using Triggers

21. Event Scheduler

21.1. Event Scheduler Overview

21.2. Event Scheduler Syntax

21.2.1. CREATE EVENT Syntax

21.2.2. ALTER EVENT Syntax

21.2.3. DROP EVENT Syntax

21.3. Event Metadata

21.4. The Event Scheduler and MySQL Privileges

21.5. Event Scheduler Limitations and Restrictions

22. Views

22.1. ALTER VIEW Syntax

22.2. CREATE VIEW Syntax

22.3. DROP VIEW Syntax

23. The INFORMATION_SCHEMA Database

23.1. The INFORMATION_SCHEMA SCHEMATA Table

23.2. The INFORMATION_SCHEMA TABLES Table

23.3. The INFORMATION_SCHEMA COLUMNS Table

23.4. The INFORMATION_SCHEMA STATISTICS Table

23.5. The INFORMATION_SCHEMA USER_PRIVILEGES Table

23.6. The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table

23.7. The INFORMATION_SCHEMA TABLE_PRIVILEGES Table

23.8. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table

23.9. The INFORMATION_SCHEMA CHARACTER_SETS Table

23.10. The INFORMATION_SCHEMA COLLATIONS Table

23.11. The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table

23.12. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table

23.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table

23.14. The INFORMATION_SCHEMA ROUTINES Table

23.15. The INFORMATION_SCHEMA VIEWS Table

23.16. The INFORMATION_SCHEMA TRIGGERS Table

23.17. The INFORMATION_SCHEMA PLUGINS Table

23.18. The INFORMATION_SCHEMA ENGINES Table

23.19. The INFORMATION_SCHEMA PARTITIONS Table

23.20. The INFORMATION_SCHEMA EVENTS Table

23.21. The INFORMATION_SCHEMA FILES Table

23.22. The INFORMATION_SCHEMA PROCESSLIST Table

23.23. Other INFORMATION_SCHEMA Tables

23.24. Extensions to SHOW Statements

24. Precision Math

24.1. Types of Numeric Values

24.2. DECIMAL Data Type Changes

24.3. Expression Handling

24.4. Rounding Behavior

24.5. Precision Math Examples

25. APIs and Libraries

25.1. libmysqld, the Embedded MySQL Server Library

25.1.1. Overview of the Embedded MySQL Server Library

25.1.2. Compiling Programs with libmysqld

25.1.3. Restrictions when using the Embedded MySQL Server

25.1.4. Options with the Embedded Server

25.1.5. Things left to do in Embedded Server (TODO)

25.1.6. Embedded Server Examples

25.1.7. Licensing the Embedded Server

25.2. MySQL C API

25.2.1. C API Data types

25.2.2. C API Function Overview

25.2.3. C API Function Descriptions

25.2.4. C API Prepared Statements

25.2.5. C API Prepared Statement Data types

25.2.6. C API Prepared Statement Function Overview

25.2.7. C API Prepared Statement Function Descriptions

25.2.8. C API Prepared statement problems

25.2.9. C API Handling of Multiple Query Execution

25.2.10. C API Handling of Date and Time Values

25.2.11. C API Threaded Function Descriptions

25.2.12. C API Embedded Server Function Descriptions

25.2.13. Common questions and problems when using the C API

25.2.14. Building Client Programs

25.2.15. How to Make a Threaded Client

25.3. MySQL PHP API

25.3.1. Common Problems with MySQL and PHP

25.4. MySQL Perl API

25.5. MySQL C++ API

25.5.1. Borland C++

25.6. MySQL Python API

25.7. MySQL Tcl API

25.8. MySQL Eiffel Wrapper

25.9. MySQL Program Development Utilities

25.9.1. msql2mysql — Convert mSQL Programs for Use with MySQL

25.9.2. mysql_config — Get Compile Options for Compiling Clients

26. Connectors

26.1. MySQL Connector/ODBC

26.1.1. Introduction to MyODBC

26.1.2. General Information About ODBC and MyODBC

26.1.3. How to Install MyODBC

26.1.4. Installing MyODBC from a Binary Distribution on Windows

26.1.5. Installing MyODBC from a Binary Distribution on Unix

26.1.6. Installing MyODBC from a Source Distribution on Windows

26.1.7. Installing MyODBC from a Source Distribution on Unix

26.1.8. Installing MyODBC from the BitKeeper Development Source Tree

26.1.9. MyODBC Configuration

26.1.10. MyODBC Connection-Related Issues

26.1.11. MyODBC and Microsoft Access

26.1.12. MyODBC and Microsoft VBA and ASP

26.1.13. MyODBC and Third-Party ODBC Tools

26.1.14. MyODBC General Functionality

26.1.15. Basic MyODBC Application Steps

26.1.16. MyODBC API Reference

26.1.17. MyODBC Data Types

26.1.18. MyODBC Error Codes

26.1.19. MyODBC With VB: ADO, DAO and RDO

26.1.20. MyODBC with Microsoft .NET

26.1.21. Credits

26.2. MySQL Connector/NET

26.2.1. Introduction

26.2.2. Downloading and Installing MySQL Connector/NET

26.2.3. Connector/NET Architecture

26.2.4. Using MySQL Connector/NET

26.2.5. MySQL Connector/NET Change History

26.3. MySQL Connector/J

26.3.1. Basic JDBC concepts

26.3.2. Installing Connector/J

26.3.3. JDBC Reference

26.3.4. Using Connector/J with J2EE and Other Java Frameworks

26.3.5. Diagnosing Connector/J Problems

26.3.6. MySQL Connector/J Change History

26.4. MySQL Connector/MXJ

26.4.1. Introduction

26.4.2. Support Platforms:

26.4.3. JUnit Test Requirements

26.4.4. Running the JUnit Tests

26.4.5. Running as part of the JDBC Driver

26.4.6. Running within a Java Object

26.4.7. The MysqldResource API

26.4.8. Running within a JMX Agent (custom)

26.4.9. Deployment in a standard JMX Agent environment (JBoss)

26.4.10. Installation

26.5. Connector/PHP

27. Extending MySQL

27.1. MySQL Internals

27.1.1. MySQL Threads

27.1.2. MySQL Test Suite

27.2. The MySQL Plugin Interface

27.2.1. Characteristics of the Plugin Interface

27.2.2. Full-Text Parser Plugins

27.2.3. INSTALL PLUGIN Syntax

27.2.4. UNINSTALL PLUGIN Syntax

27.2.5. Writing Plugins

27.3. Adding New Functions to MySQL

27.3.1. Features of the User-Defined Function Interface

27.3.2. CREATE FUNCTION Syntax

27.3.3. DROP FUNCTION Syntax

27.3.4. Adding a New User-Defined Function

27.3.5. Adding a New Native Function

27.4. Adding New Procedures to MySQL

27.4.1. Procedure Analyse

27.4.2. Writing a Procedure

A. Problems and Common Errors

A.1. How to Determine What Is Causing a Problem

A.2. Common Errors When Using MySQL Programs

A.2.1. Access denied

A.2.2. Can't connect to [local] MySQL server

A.2.3. Client does not support authentication protocol

A.2.4. Password Fails When Entered Interactively

A.2.5. Host 'host_name' is blocked

A.2.6. Too many connections

A.2.7. Out of memory

A.2.8. MySQL server has gone away

A.2.9. Packet too large

A.2.10. Communication Errors and Aborted Connections

A.2.11. The table is full

A.2.12. Can't create/write to file

A.2.13. Commands out of sync

A.2.14. Ignoring user

A.2.15. Table 'tbl_name' doesn't exist

A.2.16. Can't initialize character set

A.2.17. File Not Found

A.3. Installation-Related Issues

A.3.1. Problems Linking to the MySQL Client Library

A.3.2. Problems with File Permissions

A.4. Administration-Related Issues

A.4.1. How to Reset the Root Password

A.4.2. What to Do If MySQL Keeps Crashing

A.4.3. How MySQL Handles a Full Disk

A.4.4. Where MySQL Stores Temporary Files

A.4.5. How to Protect or Change the MySQL Unix Socket File

A.4.6. Time Zone Problems

A.5. Query-Related Issues

A.5.1. Case Sensitivity in Searches

A.5.2. Problems Using DATE Columns

A.5.3. Problems with NULL Values

A.5.4. Problems with Column Aliases

A.5.5. Rollback Failure for Non-Transactional Tables

A.5.6. Deleting Rows from Related Tables

A.5.7. Solving Problems with No Matching Rows

A.5.8. Problems with Floating-Point Comparisons

A.6. Optimizer-Related Issues

A.7. Table Definition-Related Issues

A.7.1. Problems with ALTER TABLE

A.7.2. How to Change the Order of Columns in a Table

A.7.3. TEMPORARY TABLE Problems

A.8. Known Issues in MySQL

A.8.1. Open Issues in MySQL

B. Error Codes and Messages

B.1. Server Error Codes and Messages

B.2. Client Error Codes and Messages

C. Credits

C.1. Developers at MySQL AB

C.2. Contributors to MySQL

C.3. Documenters and translators

C.4. Libraries used by and included with MySQL

C.5. Packages that support MySQL

C.6. Tools that were used to create MySQL

C.7. Supporters of MySQL

D. MySQL Change History

D.1. Changes in release 5.1.x (Development)

D.1.1. Changes in release 5.1.8 (Not yet released)

D.1.2. Changes in release 5.1.7 (Not yet released)

D.1.3. Changes in release 5.1.6 (01 February 2006)

D.1.4. Changes in release 5.1.5 (10 January 2006)

D.1.5. Changes in release 5.1.4 (21 December 2005)

D.1.6. Changes in release 5.1.3 (29 November 2005)

D.1.7. Changes in release 5.1.2 (Not released)

D.1.8. Changes in release 5.1.1 (Not released)

D.2. Changes in MyODBC

D.2.1. Changes in MyODBC 3.51.13

D.2.2. Changes in MyODBC 3.51.12

D.2.3. Changes in MyODBC 3.51.11

E. Porting to Other Systems

E.1. Debugging a MySQL Server

E.1.1. Compiling MySQL for Debugging

E.1.2. Creating Trace Files

E.1.3. Debugging mysqld under gdb

E.1.4. Using a Stack Trace

E.1.5. Using Server Logs to Find Causes of Errors in mysqld

E.1.6. Making a Test Case If You Experience Table Corruption

E.2. Debugging a MySQL Client

E.3. The DBUG Package

E.4. Comments about RTS Threads

E.5. Differences Between Thread Packages

F. Environment Variables

G. Regular Expressions

H. Limits in MySQL

H.1. Limits of Joins

I. Feature Restrictions

I.1. Restrictions on Stored Routines and Triggers

I.2. Restrictions on Server-Side Cursors

I.3. Restrictions on Subqueries

I.4. Restrictions on Views

I.5. Restrictions on XA Transactions

J. GNU General Public License

K. MySQL FLOSS License Exception

Index