Oracle 5.0 User manual

MySQL 5.0 Reference Manual

MySQL 5.0 Reference Manual
Abstract
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 (http://www.oracle.com/us/support/lifetime-
support/index.html). 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 and
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 release notes detailing the changes in each release, see the MySQL 5.0 Release Notes.
For legal information, see the Legal Notices.
Document generated on: 2013-04-16 (revision: 34918)
General Administrators MySQL
Enterprise Developers & Functionality Connectors & APIs HA/Scalability
Tutorial Installation &
Upgrades MySQL Enterprise Edition MySQL Workbench Connectors and APIs » HA/Scalability Guide
Server
Administration » MySQL Installer MySQL Enterprise
Monitor
Globalization Connector/J MySQL and
DRBD
SQL Syntax » Security MySQL
Enterprise
Backup
Optimization Connector/ODBC memcached
Storage Engines » Startup /
Shutdown MySQL Enterprise
Security
Functions and
Operators Connector/Net MySQL and Virtualization
Server Option /
Variable
Reference
» Backup and
Recovery
Overview
MySQL
Enterprise Audit Views and Stored Programs Connector/ Python MySQL Proxy
» Release Notes » MySQL Utilities MySQL Thread
Pool Precision Math PHP Replication
» MySQL Version
Reference » Linux/Unix Platform Guide Spatial Extensions C API
FAQs » Windows
Platform Guide Restrictions and Limitations Connector/C
» Mac OS X
Platform Guide Connector/C++
» Solaris Platform
Guide » MySQL for Excel
» Building from
Source

iii
Table of Contents
Preface and Legal Notices .......................................................................................................... xix
1. General Information .................................................................................................................. 1
1.1. About This Manual ......................................................................................................... 2
1.2. Typographical and Syntax Conventions ........................................................................... 3
1.3. Overview of the MySQL Database Management System .................................................. 4
1.3.1. What is MySQL? ................................................................................................. 4
1.3.2. The Main Features of MySQL .............................................................................. 6
1.3.3. History of MySQL ................................................................................................ 8
1.4. What Is New in MySQL 5.0 ............................................................................................ 9
1.5. MySQL Development History ........................................................................................ 11
1.6. MySQL Information Sources ......................................................................................... 11
1.6.1. MySQL Mailing Lists .......................................................................................... 11
1.6.2. MySQL Community Support at the MySQL Forums ............................................. 14
1.6.3. MySQL Community Support on Internet Relay Chat (IRC) ................................... 14
1.6.4. MySQL Enterprise ............................................................................................. 14
1.7. How to Report Bugs or Problems .................................................................................. 14
1.8. MySQL Standards Compliance ..................................................................................... 19
1.8.1. What Standards MySQL Follows ........................................................................ 19
1.8.2. Selecting SQL Modes ........................................................................................ 19
1.8.3. Running MySQL in ANSI Mode .......................................................................... 20
1.8.4. MySQL Extensions to Standard SQL .................................................................. 20
1.8.5. MySQL Differences from Standard SQL ............................................................. 23
1.8.6. How MySQL Deals with Constraints ................................................................... 28
1.9. Credits ......................................................................................................................... 32
1.9.1. Contributors to MySQL ...................................................................................... 32
1.9.2. Documenters and translators ............................................................................. 36
1.9.3. Packages that support MySQL ........................................................................... 37
1.9.4. Tools that were used to create MySQL ............................................................... 38
1.9.5. Supporters of MySQL ........................................................................................ 38
2. Installing and Upgrading MySQL ............................................................................................. 41
2.1. MySQL Installation Overview ........................................................................................ 42
2.2. Determining your current MySQL version ...................................................................... 42
2.3. Notes for MySQL Enterprise Server .............................................................................. 43
2.3.1. Enterprise Server Distribution Types ................................................................... 44
2.3.2. Upgrading MySQL Enterprise Server .................................................................. 44
2.4. Notes for MySQL Community Server ............................................................................. 44
2.4.1. Overview of MySQL Community Server Installation ............................................. 44
2.4.2. Operating Systems Supported by MySQL Community Server ............................... 45
2.4.3. Choosing Which MySQL Distribution to Install ..................................................... 46
2.5. How to Get MySQL ...................................................................................................... 50
2.6. Verifying Package Integrity Using MD5 Checksums or GnuPG ......................................... 50
2.6.1. Verifying the MD5 Checksum ............................................................................. 50
2.6.2. Signature Checking Using GnuPG ....................................................................... 51
2.6.3. Signature Checking Using Gpg4win for Windows ............................................... 53
2.6.4. Signature Checking Using RPM ........................................................................... 57
2.7. Installation Layouts ....................................................................................................... 58
2.8. Compiler-Specific Build Characteristics .......................................................................... 59
2.9. Standard MySQL Installation from a Binary Distribution .................................................. 60
2.10. Installing MySQL on Microsoft Windows ...................................................................... 60
2.10.1. Choosing An Installation Package .................................................................... 61
2.10.2. Installing MySQL on Microsoft Windows Using an MSI Package ......................... 62
2.10.3. MySQL Server Instance Configuration Wizard ................................................... 67
2.10.4. Installing MySQL on Microsoft Windows Using a noinstall Zip Archive ........... 79
2.10.5. Troubleshooting a MySQL Installation Under Windows ...................................... 86
2.10.6. Upgrading MySQL on Windows ........................................................................ 87

MySQL 5.0 Reference Manual
iv
2.10.7. Windows Postinstallation Procedures ................................................................ 88
2.10.8. Installing MySQL from Source on Windows ....................................................... 90
2.11. Installing MySQL on Mac OS X .................................................................................. 95
2.12. Installing MySQL from RPM Packages on Linux .......................................................... 97
2.13. Installing MySQL on Solaris ...................................................................................... 100
2.14. Installing MySQL on i5/OS ........................................................................................ 101
2.15. Installing MySQL on NetWare ................................................................................... 105
2.16. Installing MySQL from Generic Binaries on Other Unix-Like Systems ........................... 107
2.17. Installing MySQL from Source ................................................................................... 109
2.17.1. Installing MySQL from a Standard Source Distribution ..................................... 110
2.17.2. Installing MySQL from a Development Source Tree ......................................... 113
2.17.3. MySQL Source-Configuration Options ............................................................. 115
2.17.4. Dealing with Problems Compiling MySQL ....................................................... 123
2.17.5. Compiling and Linking an Optimized mysqld Server ....................................... 126
2.18. Postinstallation Setup and Testing ............................................................................. 127
2.18.1. Unix Postinstallation Procedures ..................................................................... 127
2.18.2. Securing the Initial MySQL Accounts .............................................................. 138
2.19. Upgrading or Downgrading MySQL ........................................................................... 142
2.19.1. Upgrading MySQL ......................................................................................... 142
2.19.2. Downgrading MySQL ..................................................................................... 153
2.19.3. Checking Whether Tables or Indexes Must Be Rebuilt ..................................... 154
2.19.4. Rebuilding or Repairing Tables or Indexes ...................................................... 156
2.19.5. Copying MySQL Databases to Another Machine ............................................. 158
2.20. Operating System-Specific Notes .............................................................................. 159
2.20.1. Linux Notes ................................................................................................... 159
2.20.2. Mac OS X Notes ........................................................................................... 165
2.20.3. Solaris Notes ................................................................................................. 165
2.20.4. BSD Notes .................................................................................................... 169
2.20.5. Other Unix Notes ........................................................................................... 172
2.20.6. OS/2 Notes ................................................................................................... 187
2.21. Environment Variables .............................................................................................. 187
2.22. Perl Installation Notes ............................................................................................... 188
2.22.1. Installing Perl on Unix .................................................................................... 189
2.22.2. Installing ActiveState Perl on Windows ........................................................... 190
2.22.3. Problems Using the Perl DBI/DBD Interface ..................................................... 190
3. Tutorial ................................................................................................................................. 193
3.1. Connecting to and Disconnecting from the Server ........................................................ 193
3.2. Entering Queries ........................................................................................................ 194
3.3. Creating and Using a Database .................................................................................. 197
3.3.1. Creating and Selecting a Database .................................................................. 198
3.3.2. Creating a Table .............................................................................................. 199
3.3.3. Loading Data into a Table ................................................................................ 200
3.3.4. Retrieving Information from a Table .................................................................. 201
3.4. Getting Information About Databases and Tables ......................................................... 213
3.5. Using mysql in Batch Mode ....................................................................................... 214
3.6. Examples of Common Queries .................................................................................... 216
3.6.1. The Maximum Value for a Column ................................................................... 216
3.6.2. The Row Holding the Maximum of a Certain Column ......................................... 216
3.6.3. Maximum of Column per Group ....................................................................... 217
3.6.4. The Rows Holding the Group-wise Maximum of a Certain Column ...................... 217
3.6.5. Using User-Defined Variables ........................................................................... 218
3.6.6. Using Foreign Keys ......................................................................................... 218
3.6.7. Searching on Two Keys ................................................................................... 220
3.6.8. Calculating Visits Per Day ................................................................................ 220
3.6.9. Using AUTO_INCREMENT ................................................................................. 220
3.7. Using MySQL with Apache ......................................................................................... 222
4. MySQL Programs .................................................................................................................. 223
4.1. Overview of MySQL Programs .................................................................................... 224

MySQL 5.0 Reference Manual
v
4.2. Using MySQL Programs ............................................................................................. 229
4.2.1. Invoking MySQL Programs ............................................................................... 229
4.2.2. Connecting to the MySQL Server ..................................................................... 229
4.2.3. Specifying Program Options ............................................................................. 232
4.2.4. Setting Environment Variables .......................................................................... 243
4.3. MySQL Server and Server-Startup Programs ............................................................... 244
4.3.1. mysqld — The MySQL Server ........................................................................ 244
4.3.2. mysqld_safe — MySQL Server Startup Script ................................................ 245
4.3.3. mysql.server — MySQL Server Startup Script .............................................. 249
4.3.4. mysqld_multi — Manage Multiple MySQL Servers ........................................ 250
4.4. MySQL Installation-Related Programs ......................................................................... 254
4.4.1. comp_err — Compile MySQL Error Message File ............................................ 254
4.4.2. make_win_bin_dist — Package MySQL Distribution as Zip Archive ............... 255
4.4.3. make_win_src_distribution — Create Source Distribution for Windows ...... 256
4.4.4. mysqlbug — Generate Bug Report ................................................................. 257
4.4.5. mysql_fix_privilege_tables — Upgrade MySQL System Tables .............. 257
4.4.6. mysql_install_db — Initialize MySQL Data Directory ................................... 258
4.4.7. mysql_secure_installation — Improve MySQL Installation Security .......... 259
4.4.8. mysql_tzinfo_to_sql — Load the Time Zone Tables .................................. 260
4.4.9. mysql_upgrade — Check Tables for MySQL Upgrade .................................... 260
4.5. MySQL Client Programs ............................................................................................. 262
4.5.1. mysql — The MySQL Command-Line Tool ...................................................... 262
4.5.2. mysqladmin — Client for Administering a MySQL Server ................................. 282
4.5.3. mysqlcheck — A Table Maintenance Program ................................................ 288
4.5.4. mysqldump — A Database Backup Program .................................................... 293
4.5.5. mysqlimport — A Data Import Program ......................................................... 308
4.5.6. mysqlshow — Display Database, Table, and Column Information ...................... 312
4.6. MySQL Administrative and Utility Programs ................................................................. 315
4.6.1. innochecksum — Offline InnoDB File Checksum Utility .................................... 315
4.6.2. myisam_ftdump — Display Full-Text Index information .................................... 316
4.6.3. myisamchk — MyISAM Table-Maintenance Utility ............................................ 317
4.6.4. myisamlog — Display MyISAM Log File Contents ............................................ 333
4.6.5. myisampack — Generate Compressed, Read-Only MyISAM Tables .................. 334
4.6.6. mysqlaccess — Client for Checking Access Privileges .................................... 339
4.6.7. mysqlbinlog — Utility for Processing Binary Log Files .................................... 342
4.6.8. mysqldumpslow — Summarize Slow Query Log Files ...................................... 350
4.6.9. mysqlhotcopy — A Database Backup Program .............................................. 352
4.6.10. mysqlmanager — The MySQL Instance Manager .......................................... 354
4.6.11. mysql_convert_table_format — Convert Tables to Use a Given Storage
Engine ...................................................................................................................... 364
4.6.12. mysql_explain_log — Use EXPLAIN on Statements in Query Log .............. 365
4.6.13. mysql_find_rows — Extract SQL Statements from Files .............................. 366
4.6.14. mysql_fix_extensions — Normalize Table File Name Extensions .............. 366
4.6.15. mysql_setpermission — Interactively Set Permissions in Grant Tables ........ 367
4.6.16. mysql_tableinfo — Generate Database Metadata ...................................... 367
4.6.17. mysql_waitpid — Kill Process and Wait for Its Termination .......................... 369
4.6.18. mysql_zap — Kill Processes That Match a Pattern ........................................ 370
4.7. MySQL Program Development Utilities ........................................................................ 370
4.7.1. msql2mysql — Convert mSQL Programs for Use with MySQL ......................... 371
4.7.2. mysql_config — Display Options for Compiling Clients .................................. 371
4.7.3. my_print_defaults — Display Options from Option Files .............................. 372
4.7.4. resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols ....... 373
4.8. Miscellaneous Programs ............................................................................................. 373
4.8.1. perror — Explain Error Codes ....................................................................... 373
4.8.2. replace — A String-Replacement Utility ......................................................... 374
4.8.3. resolveip — Resolve Host name to IP Address or Vice Versa ........................ 375
5. MySQL Server Administration ................................................................................................ 377
5.1. The MySQL Server .................................................................................................... 377

MySQL 5.0 Reference Manual
vi
5.1.1. Server Option and Variable Reference .............................................................. 378
5.1.2. Server Configuration Defaults ........................................................................... 399
5.1.3. Server Command Options ................................................................................ 399
5.1.4. Server System Variables .................................................................................. 425
5.1.5. Using System Variables ................................................................................... 507
5.1.6. Server Status Variables ................................................................................... 516
5.1.7. Server SQL Modes .......................................................................................... 534
5.1.8. Server-Side Help ............................................................................................. 541
5.1.9. Server Response to Signals ............................................................................. 541
5.1.10. The Shutdown Process .................................................................................. 542
5.2. MySQL Server Logs ................................................................................................... 543
5.2.1. The Error Log .................................................................................................. 543
5.2.2. The General Query Log ................................................................................... 544
5.2.3. The Binary Log ................................................................................................ 545
5.2.4. The Slow Query Log ........................................................................................ 548
5.2.5. Server Log Maintenance .................................................................................. 549
5.3. Running Multiple MySQL Instances on One Machine ................................................... 550
5.3.1. Setting Up Multiple Data Directories ................................................................. 552
5.3.2. Running Multiple MySQL Instances on Windows ............................................... 553
5.3.3. Running Multiple MySQL Instances on Unix ...................................................... 555
5.3.4. Using Client Programs in a Multiple-Server Environment .................................... 556
6. Security ................................................................................................................................ 559
6.1. General Security Issues .............................................................................................. 559
6.1.1. Security Guidelines .......................................................................................... 560
6.1.2. Keeping Passwords Secure ............................................................................. 561
6.1.3. Making MySQL Secure Against Attackers ......................................................... 569
6.1.4. Security-Related mysqld Options and Variables ............................................... 570
6.1.5. How to Run MySQL as a Normal User ............................................................. 571
6.1.6. Security Issues with LOAD DATA LOCAL ......................................................... 572
6.1.7. Client Programming Security Guidelines ........................................................... 573
6.2. The MySQL Access Privilege System .......................................................................... 574
6.2.1. Privileges Provided by MySQL ......................................................................... 575
6.2.2. Privilege System Grant Tables ......................................................................... 579
6.2.3. Specifying Account Names ............................................................................... 583
6.2.4. Access Control, Stage 1: Connection Verification .............................................. 585
6.2.5. Access Control, Stage 2: Request Verification ................................................... 588
6.2.6. When Privilege Changes Take Effect ................................................................ 590
6.2.7. Causes of Access-Denied Errors ...................................................................... 590
6.3. MySQL User Account Management ............................................................................. 595
6.3.1. User Names and Passwords ............................................................................ 595
6.3.2. Adding User Accounts ..................................................................................... 597
6.3.3. Removing User Accounts ................................................................................. 600
6.3.4. Setting Account Resource Limits ...................................................................... 600
6.3.5. Assigning Account Passwords .......................................................................... 602
6.3.6. Using SSL for Secure Connections ................................................................... 603
6.3.7. Connecting to MySQL Remotely from Windows with SSH .................................. 614
6.3.8. SQL-Based MySQL Account Activity Auditing .................................................... 614
7. Backup and Recovery ........................................................................................................... 617
7.1. Backup and Recovery Types ...................................................................................... 618
7.2. Database Backup Methods ......................................................................................... 620
7.3. Example Backup and Recovery Strategy ..................................................................... 622
7.3.1. Establishing a Backup Policy ............................................................................ 623
7.3.2. Using Backups for Recovery ............................................................................ 625
7.3.3. Backup Strategy Summary ............................................................................... 625
7.4. Using mysqldump for Backups ................................................................................... 625
7.4.1. Dumping Data in SQL Format with mysqldump ................................................ 626
7.4.2. Reloading SQL-Format Backups ....................................................................... 627
7.4.3. Dumping Data in Delimited-Text Format with mysqldump .................................. 627

MySQL 5.0 Reference Manual
vii
7.4.4. Reloading Delimited-Text Format Backups ........................................................ 628
7.4.5. mysqldump Tips ............................................................................................. 629
7.5. Point-in-Time (Incremental) Recovery Using the Binary Log .......................................... 631
7.5.1. Point-in-Time Recovery Using Event Times ....................................................... 632
7.5.2. Point-in-Time Recovery Using Event Positions .................................................. 632
7.6. MyISAM Table Maintenance and Crash Recovery ........................................................ 633
7.6.1. Using myisamchk for Crash Recovery ............................................................. 634
7.6.2. How to Check MyISAM Tables for Errors .......................................................... 634
7.6.3. How to Repair MyISAM Tables ......................................................................... 635
7.6.4. MyISAM Table Optimization .............................................................................. 637
7.6.5. Setting Up a MyISAM Table Maintenance Schedule ........................................... 638
8. Optimization .......................................................................................................................... 639
8.1. Optimization Overview ................................................................................................ 640
8.1.1. MySQL Design Limitations and Tradeoffs ......................................................... 640
8.1.2. Designing Applications for Portability ................................................................ 640
8.1.3. The MySQL Benchmark Suite .......................................................................... 641
8.1.4. Using Your Own Benchmarks .......................................................................... 642
8.2. Obtaining Query Execution Plan Information ................................................................ 643
8.2.1. Optimizing Queries with EXPLAIN .................................................................... 643
8.2.2. EXPLAIN Output Format .................................................................................. 643
8.2.3. EXPLAIN EXTENDED Output Format ................................................................ 652
8.2.4. Estimating Query Performance ......................................................................... 654
8.3. Optimizing SQL Statements ........................................................................................ 654
8.3.1. Optimizing SELECT Statements ........................................................................ 654
8.3.2. Optimizing Non-SELECT Statements ................................................................. 685
8.3.3. Other Optimization Tips ................................................................................... 689
8.4. Controlling the Query Optimizer .................................................................................. 691
8.5. Optimization and Indexes ........................................................................................... 692
8.5.1. Column Indexes .............................................................................................. 692
8.5.2. Multiple-Column Indexes .................................................................................. 692
8.5.3. How MySQL Uses Indexes .............................................................................. 694
8.5.4. MyISAM Index Statistics Collection ................................................................... 696
8.6. Buffering and Caching ................................................................................................ 698
8.6.1. The MyISAM Key Cache .................................................................................. 698
8.6.2. The InnoDB Buffer Pool .................................................................................. 702
8.6.3. The MySQL Query Cache ................................................................................ 703
8.7. Locking Issues ........................................................................................................... 709
8.7.1. Internal Locking Methods ................................................................................. 709
8.7.2. Table Locking Issues ....................................................................................... 711
8.7.3. Concurrent Inserts ........................................................................................... 712
8.7.4. External Locking .............................................................................................. 713
8.8. Optimizing Database Structure .................................................................................... 714
8.8.1. Make Your Data as Small as Possible .............................................................. 714
8.8.2. Using PROCEDURE ANALYSE ........................................................................... 715
8.8.3. How MySQL Opens and Closes Tables ............................................................ 716
8.8.4. Disadvantages of Creating Many Tables in the Same Database ......................... 717
8.8.5. How MySQL Uses Internal Temporary Tables ................................................... 717
8.9. Optimizing the MySQL Server ..................................................................................... 718
8.9.1. System Factors and Startup Parameter Tuning ................................................. 718
8.9.2. Tuning Server Parameters ............................................................................... 718
8.9.3. How MySQL Uses Threads for Client Connections ............................................ 723
8.9.4. How MySQL Uses Memory .............................................................................. 724
8.9.5. Disk Issues ..................................................................................................... 725
8.9.6. Using Symbolic Links ....................................................................................... 726
8.9.7. Enabling Large Page Support .......................................................................... 729
8.9.8. DNS Lookup Optimization and the Host Cache ................................................. 730
8.10. Examining Thread Information ................................................................................... 731
8.10.1. Thread Command Values ............................................................................... 732

MySQL 5.0 Reference Manual
viii
8.10.2. General Thread States ................................................................................... 734
8.10.3. Delayed-Insert Thread States ......................................................................... 739
8.10.4. Query Cache Thread States ........................................................................... 740
8.10.5. Replication Master Thread States ................................................................... 740
8.10.6. Replication Slave I/O Thread States ............................................................... 741
8.10.7. Replication Slave SQL Thread States ............................................................. 742
8.10.8. Replication Slave Connection Thread States ................................................... 742
8.10.9. MySQL Cluster Thread States ........................................................................ 743
9. Language Structure ............................................................................................................... 745
9.1. Literal Values ............................................................................................................. 745
9.1.1. String Literals .................................................................................................. 745
9.1.2. Number Literals ............................................................................................... 747
9.1.3. Date and Time Literals ..................................................................................... 748
9.1.4. Hexadecimal Literals ........................................................................................ 750
9.1.5. Boolean Literals ............................................................................................... 750
9.1.6. Bit-Field Literals ............................................................................................... 750
9.1.7. NULL Values ................................................................................................... 751
9.2. Schema Object Names ............................................................................................... 751
9.2.1. Identifier Qualifiers ........................................................................................... 753
9.2.2. Identifier Case Sensitivity ................................................................................. 754
9.2.3. Function Name Parsing and Resolution ............................................................ 755
9.3. Reserved Words ........................................................................................................ 758
9.4. User-Defined Variables ............................................................................................... 761
9.5. Expression Syntax ...................................................................................................... 764
9.6. Comment Syntax ........................................................................................................ 766
10. Globalization ....................................................................................................................... 767
10.1. Character Set Support .............................................................................................. 767
10.1.1. Character Sets and Collations in General ........................................................ 768
10.1.2. Character Sets and Collations in MySQL ........................................................ 769
10.1.3. Specifying Character Sets and Collations ........................................................ 770
10.1.4. Connection Character Sets and Collations ...................................................... 776
10.1.5. Configuring the Character Set and Collation for Applications ............................ 778
10.1.6. Character Set for Error Messages .................................................................. 780
10.1.7. Collation Issues ............................................................................................. 780
10.1.8. String Repertoire ............................................................................................ 787
10.1.9. Operations Affected by Character Set Support ................................................ 788
10.1.10. Unicode Support .......................................................................................... 791
10.1.11. UTF-8 for Metadata ...................................................................................... 792
10.1.12. Column Character Set Conversion ................................................................ 793
10.1.13. Character Sets and Collations That MySQL Supports .................................... 794
10.2. Setting the Error Message Language ........................................................................ 804
10.3. Adding a Character Set ............................................................................................ 805
10.3.1. Character Definition Arrays ............................................................................. 807
10.3.2. String Collating Support for Complex Character Sets ....................................... 808
10.3.3. Multi-Byte Character Support for Complex Character Sets ................................ 808
10.4. Adding a Collation to a Character Set ....................................................................... 808
10.4.1. Collation Implementation Types ...................................................................... 809
10.4.2. Choosing a Collation ID ................................................................................. 811
10.4.3. Adding a Simple Collation to an 8-Bit Character Set ........................................ 812
10.4.4. Adding a UCA Collation to a Unicode Character Set ........................................ 813
10.5. Character Set Configuration ...................................................................................... 816
10.6. MySQL Server Time Zone Support ............................................................................ 816
10.6.1. Staying Current with Time Zone Changes ....................................................... 819
10.6.2. Time Zone Leap Second Support ................................................................... 820
10.7. MySQL Server Locale Support .................................................................................. 821
11. Data Types ......................................................................................................................... 825
11.1. Data Type Overview ................................................................................................. 825
11.1.1. Numeric Type Overview ................................................................................. 825

MySQL 5.0 Reference Manual
ix
11.1.2. Date and Time Type Overview ....................................................................... 829
11.1.3. String Type Overview ..................................................................................... 830
11.1.4. Numeric Types .............................................................................................. 834
11.1.5. Date and Time Types .................................................................................... 838
11.1.6. String Types .................................................................................................. 848
11.1.7. Data Type Default Values .............................................................................. 856
11.2. Data Type Storage Requirements ............................................................................. 858
11.3. Choosing the Right Type for a Column ...................................................................... 861
11.4. Using Data Types from Other Database Engines ....................................................... 861
12. Functions and Operators ..................................................................................................... 863
12.1. Function and Operator Reference ............................................................................. 864
12.2. Type Conversion in Expression Evaluation ................................................................ 871
12.3. Operators ................................................................................................................. 872
12.3.1. Operator Precedence ..................................................................................... 873
12.3.2. Comparison Functions and Operators ............................................................. 874
12.3.3. Logical Operators .......................................................................................... 880
12.3.4. Assignment Operators .................................................................................... 881
12.4. Control Flow Functions ............................................................................................. 882
12.5. String Functions ....................................................................................................... 884
12.5.1. String Comparison Functions .......................................................................... 895
12.5.2. Regular Expressions ...................................................................................... 899
12.6. Numeric Functions and Operators ............................................................................. 904
12.6.1. Arithmetic Operators ...................................................................................... 905
12.6.2. Mathematical Functions .................................................................................. 907
12.7. Date and Time Functions .......................................................................................... 915
12.8. What Calendar Is Used By MySQL? .......................................................................... 934
12.9. Full-Text Search Functions ....................................................................................... 934
12.9.1. Natural Language Full-Text Searches ............................................................. 935
12.9.2. Boolean Full-Text Searches ........................................................................... 938
12.9.3. Full-Text Searches with Query Expansion ....................................................... 940
12.9.4. Full-Text Stopwords ....................................................................................... 941
12.9.5. Full-Text Restrictions ..................................................................................... 944
12.9.6. Fine-Tuning MySQL Full-Text Search ............................................................. 944
12.9.7. Adding a Collation for Full-Text Indexing ......................................................... 946
12.10. Cast Functions and Operators ................................................................................. 948
12.11. Bit Functions .......................................................................................................... 950
12.12. Encryption and Compression Functions ................................................................... 952
12.13. Information Functions .............................................................................................. 957
12.14. Miscellaneous Functions ......................................................................................... 964
12.15. Functions and Modifiers for Use with GROUP BY Clauses ......................................... 968
12.15.1. GROUP BY (Aggregate) Functions ................................................................ 968
12.15.2. GROUP BY Modifiers .................................................................................... 972
12.15.3. MySQL Extensions to GROUP BY ................................................................. 975
12.16. Spatial Extensions .................................................................................................. 976
12.16.1. Introduction to MySQL Spatial Support .......................................................... 976
12.16.2. The OpenGIS Geometry Model .................................................................... 977
12.16.3. Supported Spatial Data Formats ................................................................... 983
12.16.4. Creating a Spatially Enabled MySQL Database ............................................. 984
12.16.5. Spatial Analysis Functions ............................................................................ 989
12.16.6. Optimizing Spatial Analysis ........................................................................... 998
12.16.7. MySQL Conformance and Compatibility ...................................................... 1001
12.17. Precision Math ...................................................................................................... 1001
12.17.1. Types of Numeric Values ........................................................................... 1002
12.17.2. DECIMAL Data Type Changes .................................................................... 1002
12.17.3. Expression Handling ................................................................................... 1004
12.17.4. Rounding Behavior ..................................................................................... 1005
12.17.5. Precision Math Examples ........................................................................... 1006
13. SQL Statement Syntax ...................................................................................................... 1011

MySQL 5.0 Reference Manual
x
13.1. Data Definition Statements ...................................................................................... 1012
13.1.1. ALTER DATABASE Syntax ........................................................................... 1012
13.1.2. ALTER FUNCTION Syntax ........................................................................... 1012
13.1.3. ALTER PROCEDURE Syntax ......................................................................... 1013
13.1.4. ALTER TABLE Syntax ................................................................................. 1013
13.1.5. ALTER VIEW Syntax ................................................................................... 1020
13.1.6. CREATE DATABASE Syntax ......................................................................... 1020
13.1.7. CREATE FUNCTION Syntax ......................................................................... 1021
13.1.8. CREATE INDEX Syntax ............................................................................... 1021
13.1.9. CREATE PROCEDURE and CREATE FUNCTION Syntax .................................. 1023
13.1.10. CREATE TABLE Syntax ............................................................................. 1029
13.1.11. CREATE TRIGGER Syntax ......................................................................... 1047
13.1.12. CREATE VIEW Syntax ............................................................................... 1050
13.1.13. DROP DATABASE Syntax ........................................................................... 1054
13.1.14. DROP FUNCTION Syntax ........................................................................... 1055
13.1.15. DROP INDEX Syntax ................................................................................. 1055
13.1.16. DROP PROCEDURE and DROP FUNCTION Syntax ........................................ 1056
13.1.17. DROP TABLE Syntax ................................................................................. 1056
13.1.18. DROP TRIGGER Syntax ............................................................................. 1056
13.1.19. DROP VIEW Syntax ................................................................................... 1057
13.1.20. RENAME TABLE Syntax ............................................................................. 1057
13.1.21. TRUNCATE TABLE Syntax ......................................................................... 1058
13.2. Data Manipulation Statements ................................................................................. 1059
13.2.1. CALL Syntax ................................................................................................ 1059
13.2.2. DELETE Syntax ............................................................................................ 1060
13.2.3. DO Syntax .................................................................................................... 1064
13.2.4. HANDLER Syntax .......................................................................................... 1064
13.2.5. INSERT Syntax ............................................................................................ 1065
13.2.6. LOAD DATA INFILE Syntax ....................................................................... 1072
13.2.7. REPLACE Syntax .......................................................................................... 1081
13.2.8. SELECT Syntax ............................................................................................ 1082
13.2.9. Subquery Syntax .......................................................................................... 1098
13.2.10. UPDATE Syntax .......................................................................................... 1108
13.3. MySQL Transactional and Locking Statements ......................................................... 1110
13.3.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax ............................... 1110
13.3.2. Statements That Cannot Be Rolled Back ....................................................... 1113
13.3.3. Statements That Cause an Implicit Commit ................................................... 1113
13.3.4. SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT, and
Syntax .................................................................................................................... 1114
13.3.5. LOCK TABLES and UNLOCK TABLES Syntax ................................................ 1114
13.3.6. SET TRANSACTION Syntax ......................................................................... 1119
13.3.7. XA Transactions .......................................................................................... 1121
13.4. Replication Statements ........................................................................................... 1125
13.4.1. SQL Statements for Controlling Master Servers ............................................. 1125
13.4.2. SQL Statements for Controlling Slave Servers ............................................... 1127
13.5. SQL Syntax for Prepared Statements ...................................................................... 1133
13.5.1. PREPARE Syntax .......................................................................................... 1136
13.5.2. EXECUTE Syntax .......................................................................................... 1136
13.5.3. DEALLOCATE PREPARE Syntax ................................................................... 1136
13.6. MySQL Compound-Statement Syntax ...................................................................... 1137
13.6.1. BEGIN ... END Compound-Statement Syntax ............................................ 1137
13.6.2. Statement Label Syntax ............................................................................... 1137
13.6.3. DECLARE Syntax .......................................................................................... 1138
13.6.4. Variables in Stored Programs ....................................................................... 1138
13.6.5. Flow Control Statements .............................................................................. 1140
13.6.6. Cursors ....................................................................................................... 1144
13.6.7. Condition Handling ....................................................................................... 1145
13.7. Database Administration Statements ........................................................................ 1149

MySQL 5.0 Reference Manual
xi
13.7.1. Account Management Statements ................................................................. 1149
13.7.2. Table Maintenance Statements ..................................................................... 1162
13.7.3. User-Defined Function Statements ................................................................ 1169
13.7.4. SET Syntax .................................................................................................. 1170
13.7.5. SHOW Syntax ................................................................................................ 1173
13.7.6. Other Administrative Statements ................................................................... 1205
13.8. MySQL Utility Statements ....................................................................................... 1210
13.8.1. DESCRIBE Syntax ........................................................................................ 1210
13.8.2. EXPLAIN Syntax .......................................................................................... 1210
13.8.3. HELP Syntax ................................................................................................ 1211
13.8.4. USE Syntax .................................................................................................. 1213
14. Storage Engines ................................................................................................................ 1215
14.1. The MyISAM Storage Engine ................................................................................... 1218
14.1.1. MyISAM Startup Options ............................................................................... 1220
14.1.2. Space Needed for Keys ............................................................................... 1222
14.1.3. MyISAM Table Storage Formats .................................................................... 1222
14.1.4. MyISAM Table Problems .............................................................................. 1224
14.2. The InnoDB Storage Engine ................................................................................... 1226
14.2.1. InnoDB Configuration .................................................................................. 1226
14.2.2. InnoDB Startup Options and System Variables ............................................. 1234
14.2.3. Creating and Using InnoDB Tables .............................................................. 1254
14.2.4. Adding, Removing, or Resizing InnoDB Data and Log Files ........................... 1259
14.2.5. Backing Up and Recovering an InnoDB Database ......................................... 1261
14.2.6. Moving an InnoDB Database to Another Machine ......................................... 1264
14.2.7. The InnoDB Transaction Model and Locking ................................................. 1264
14.2.8. InnoDB Multi-Versioning .............................................................................. 1275
14.2.9. InnoDB Table and Index Structures .............................................................. 1276
14.2.10. InnoDB Disk I/O and File Space Management ............................................ 1279
14.2.11. InnoDB Error Handling .............................................................................. 1280
14.2.12. InnoDB Performance Tuning and Troubleshooting ....................................... 1284
14.2.13. Limits on InnoDB Tables ........................................................................... 1297
14.3. The MERGE Storage Engine ..................................................................................... 1300
14.3.1. MERGE Table Advantages and Disadvantages ............................................... 1302
14.3.2. MERGE Table Problems ................................................................................ 1303
14.4. The MEMORY (HEAP) Storage Engine ....................................................................... 1305
14.5. The BDB (BerkeleyDB) Storage Engine .................................................................. 1307
14.5.1. Operating Systems Supported by BDB .......................................................... 1308
14.5.2. Installing BDB ............................................................................................... 1308
14.5.3. BDB Startup Options ..................................................................................... 1309
14.5.4. Characteristics of BDB Tables ....................................................................... 1310
14.5.5. Restrictions on BDB Tables ........................................................................... 1312
14.5.6. Errors That May Occur When Using BDB Tables ............................................ 1312
14.6. The EXAMPLE Storage Engine ................................................................................. 1313
14.7. The FEDERATED Storage Engine ............................................................................. 1313
14.7.1. Description of the FEDERATED Storage Engine .............................................. 1313
14.7.2. How to Use FEDERATED Tables ................................................................... 1314
14.7.3. Limitations of the FEDERATED Storage Engine ............................................... 1315
14.8. The ARCHIVE Storage Engine ................................................................................. 1316
14.9. The CSV Storage Engine ......................................................................................... 1317
14.10. The BLACKHOLE Storage Engine ........................................................................... 1318
15. High Availability and Scalability .......................................................................................... 1321
15.1. Oracle VM Template for MySQL Enterprise Edition ................................................... 1324
15.2. Overview of MySQL with DRBD/Pacemaker/Corosync/Oracle Linux ........................... 1324
15.3. Overview of MySQL with Windows Failover Clustering .............................................. 1327
15.4. Using MySQL within an Amazon EC2 Instance ........................................................ 1329
15.4.1. Setting Up MySQL on an EC2 AMI ............................................................... 1330
15.4.2. EC2 Instance Limitations .............................................................................. 1331
15.4.3. Deploying a MySQL Database Using EC2 ..................................................... 1331

MySQL 5.0 Reference Manual
xii
15.5. Using ZFS Replication ............................................................................................ 1334
15.5.1. Using ZFS for File System Replication .......................................................... 1335
15.5.2. Configuring MySQL for ZFS Replication ........................................................ 1336
15.5.3. Handling MySQL Recovery with ZFS ............................................................ 1337
15.6. Using MySQL with memcached ............................................................................... 1337
15.6.1. Installing memcached ................................................................................... 1339
15.6.2. Using memcached ....................................................................................... 1340
15.6.3. Developing a memcached Application ........................................................... 1358
15.6.4. Getting memcached Statistics ....................................................................... 1381
15.6.5. memcached FAQ ......................................................................................... 1390
15.7. MySQL Proxy ......................................................................................................... 1392
15.7.1. MySQL Proxy Supported Platforms ............................................................... 1393
15.7.2. Installing MySQL Proxy ................................................................................ 1394
15.7.3. MySQL Proxy Command Options ................................................................. 1396
15.7.4. MySQL Proxy Scripting ................................................................................ 1405
15.7.5. Using MySQL Proxy ..................................................................................... 1420
15.7.6. MySQL Proxy FAQ ...................................................................................... 1425
16. Replication ........................................................................................................................ 1431
16.1. Replication Configuration ........................................................................................ 1432
16.1.1. How to Set Up Replication ........................................................................... 1433
16.1.2. Replication and Binary Logging Options and Variables ................................... 1441
16.1.3. Common Replication Administration Tasks .................................................... 1473
16.2. Replication Implementation ...................................................................................... 1475
16.2.1. Replication Implementation Details ................................................................ 1476
16.2.2. Replication Relay and Status Logs ............................................................... 1478
16.2.3. How Servers Evaluate Replication Filtering Rules .......................................... 1480
16.3. Replication Solutions ............................................................................................... 1486
16.3.1. Using Replication for Backups ...................................................................... 1487
16.3.2. Using Replication with Different Master and Slave Storage Engines ................ 1488
16.3.3. Using Replication for Scale-Out .................................................................... 1489
16.3.4. Replicating Different Databases to Different Slaves ........................................ 1490
16.3.5. Improving Replication Performance ............................................................... 1492
16.3.6. Switching Masters During Failover ................................................................ 1493
16.3.7. Setting Up Replication Using SSL ................................................................. 1495
16.4. Replication Notes and Tips ..................................................................................... 1497
16.4.1. Replication Features and Issues ................................................................... 1497
16.4.2. Replication Compatibility Between MySQL Versions ....................................... 1508
16.4.3. Upgrading a Replication Setup ..................................................................... 1509
16.4.4. Troubleshooting Replication .......................................................................... 1510
16.4.5. How to Report Replication Bugs or Problems ................................................ 1511
17. MySQL Cluster .................................................................................................................. 1513
17.1. MySQL Cluster Overview ........................................................................................ 1514
17.1.1. MySQL Cluster Core Concepts ..................................................................... 1516
17.1.2. MySQL Cluster Nodes, Node Groups, Replicas, and Partitions ....................... 1518
17.1.3. MySQL Cluster Hardware, Software, and Networking Requirements ................ 1520
17.1.4. MySQL Cluster Development History ............................................................ 1521
17.1.5. Known Limitations of MySQL Cluster ............................................................ 1522
17.2. MySQL Cluster Installation and Upgrades ................................................................ 1530
17.2.1. Installing MySQL Cluster on Linux ................................................................ 1533
17.2.2. Initial Configuration of MySQL Cluster ........................................................... 1537
17.2.3. Initial Startup of MySQL Cluster .................................................................... 1539
17.2.4. MySQL Cluster Example with Tables and Data .............................................. 1540
17.2.5. Safe Shutdown and Restart of MySQL Cluster .............................................. 1543
17.2.6. Upgrading and Downgrading MySQL Cluster ................................................. 1544
17.3. MySQL Cluster Configuration .................................................................................. 1546
17.3.1. Quick Test Setup of MySQL Cluster ............................................................. 1546
17.3.2. MySQL Cluster Configuration Files ............................................................... 1548
17.3.3. Overview of MySQL Cluster Configuration Parameters ................................... 1593

MySQL 5.0 Reference Manual
xiii
17.3.4. MySQL Server Options and Variables for MySQL Cluster ............................... 1607
17.3.5. Using High-Speed Interconnects with MySQL Cluster .................................... 1616
17.4. MySQL Cluster Programs ....................................................................................... 1618
17.4.1. ndbd — The MySQL Cluster Data Node Daemon .......................................... 1618
17.4.2. ndb_mgmd — The MySQL Cluster Management Server Daemon .................... 1623
17.4.3. ndb_mgm — The MySQL Cluster Management Client .................................... 1626
17.4.4. ndb_config — Extract MySQL Cluster Configuration Information .................. 1627
17.4.5. ndb_cpcd — Automate Testing for NDB Development .................................. 1631
17.4.6. ndb_delete_all — Delete All Rows from an NDB Table ............................. 1631
17.4.7. ndb_desc — Describe NDB Tables ............................................................. 1632
17.4.8. ndb_drop_index — Drop Index from an NDB Table .................................... 1633
17.4.9. ndb_drop_table — Drop an NDB Table .................................................... 1634
17.4.10. ndb_error_reporter — NDB Error-Reporting Utility ................................ 1635
17.4.11. ndb_print_backup_file — Print NDB Backup File Contents ................... 1635
17.4.12. ndb_print_schema_file — Print NDB Schema File Contents .................. 1636
17.4.13. ndb_print_sys_file — Print NDB System File Contents ......................... 1636
17.4.14. ndb_restore — Restore a MySQL Cluster Backup .................................... 1637
17.4.15. ndb_select_all — Print Rows from an NDB Table .................................. 1642
17.4.16. ndb_select_count — Print Row Counts for NDB Tables .......................... 1645
17.4.17. ndb_show_tables — Display List of NDB Tables ...................................... 1645
17.4.18. ndb_size.pl — NDBCLUSTER Size Requirement Estimator ..................... 1647
17.4.19. ndb_waiter — Wait for MySQL Cluster to Reach a Given Status ................ 1648
17.4.20. Options Common to MySQL Cluster Programs — Options Common to MySQL
Cluster Programs .................................................................................................... 1650
17.5. Management of MySQL Cluster ............................................................................... 1653
17.5.1. Summary of MySQL Cluster Start Phases ..................................................... 1653
17.5.2. Commands in the MySQL Cluster Management Client ................................... 1655
17.5.3. Online Backup of MySQL Cluster ................................................................. 1656
17.5.4. MySQL Server Usage for MySQL Cluster ...................................................... 1660
17.5.5. Performing a Rolling Restart of a MySQL Cluster ........................................... 1661
17.5.6. Event Reports Generated in MySQL Cluster .................................................. 1663
17.5.7. MySQL Cluster Log Messages ..................................................................... 1671
17.5.8. MySQL Cluster Single User Mode ................................................................ 1682
17.5.9. Quick Reference: MySQL Cluster SQL Statements ........................................ 1683
17.5.10. MySQL Cluster Security Issues ................................................................... 1684
18. Stored Programs and Views .............................................................................................. 1693
18.1. Defining Stored Programs ....................................................................................... 1693
18.2. Using Stored Routines (Procedures and Functions) .................................................. 1694
18.2.1. Stored Routine Syntax ................................................................................. 1695
18.2.2. Stored Routines and MySQL Privileges ......................................................... 1696
18.2.3. Stored Routine Metadata .............................................................................. 1696
18.2.4. Stored Procedures, Functions, Triggers, and LAST_INSERT_ID() ................. 1697
18.3. Using Triggers ........................................................................................................ 1697
18.3.1. Trigger Syntax ............................................................................................. 1697
18.3.2. Trigger Metadata .......................................................................................... 1700
18.4. Using Views ........................................................................................................... 1700
18.4.1. View Syntax ................................................................................................. 1700
18.4.2. View Processing Algorithms ......................................................................... 1700
18.4.3. Updatable and Insertable Views .................................................................... 1702
18.4.4. View Metadata ............................................................................................. 1704
18.5. Access Control for Stored Programs and Views ....................................................... 1704
18.6. Binary Logging of Stored Programs ......................................................................... 1705
19. INFORMATION_SCHEMA Tables .......................................................................................... 1715
19.1. The INFORMATION_SCHEMA CHARACTER_SETS Table ............................................ 1717
19.2. The INFORMATION_SCHEMA COLLATIONS Table .................................................... 1717
19.3. The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY
Table .............................................................................................................................. 1718
19.4. The INFORMATION_SCHEMA COLUMNS Table ......................................................... 1718

MySQL 5.0 Reference Manual
xiv
19.5. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table ...................................... 1719
19.6. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table ........................................ 1720
19.7. The INFORMATION_SCHEMA PROFILING Table ..................................................... 1721
19.8. The INFORMATION_SCHEMA ROUTINES Table ....................................................... 1722
19.9. The INFORMATION_SCHEMA SCHEMATA Table ....................................................... 1723
19.10. The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table .................................... 1723
19.11. The INFORMATION_SCHEMA STATISTICS Table .................................................. 1723
19.12. The INFORMATION_SCHEMA TABLES Table .......................................................... 1724
19.13. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table .................................... 1725
19.14. The INFORMATION_SCHEMA TABLE_PRIVILEGES Table ...................................... 1726
19.15. The INFORMATION_SCHEMA TRIGGERS Table ...................................................... 1726
19.16. The INFORMATION_SCHEMA USER_PRIVILEGES Table ........................................ 1728
19.17. The INFORMATION_SCHEMA VIEWS Table ............................................................ 1728
19.18. Extensions to SHOW Statements ............................................................................. 1729
20. Connectors and APIs ......................................................................................................... 1733
20.1. MySQL Connector/ODBC ........................................................................................ 1737
20.1.1. Connector/ODBC Versions ........................................................................... 1738
20.1.2. Connector/ODBC Introduction ....................................................................... 1739
20.1.3. Connector/ODBC Installation ........................................................................ 1742
20.1.4. Configuring Connector/ODBC ....................................................................... 1752
20.1.5. Connector/ODBC Examples .......................................................................... 1772
20.1.6. Connector/ODBC Reference ......................................................................... 1798
20.1.7. Connector/ODBC Notes and Tips ................................................................. 1804
20.1.8. Connector/ODBC Support ............................................................................ 1814
20.2. MySQL Connector/Net ............................................................................................ 1815
20.2.1. Connector/Net Versions ................................................................................ 1816
20.2.2. Connector/Net Installation ............................................................................. 1818
20.2.3. Connector/Net Visual Studio Integration ........................................................ 1826
20.2.4. Connector/Net Tutorials ................................................................................ 1856
20.2.5. Connector/Net Programming ......................................................................... 1896
20.2.6. Connector/Net Connection String Options Reference ..................................... 1935
20.2.7. Connector/Net API Reference ....................................................................... 1942
20.2.8. Connector/Net Support ................................................................................. 2045
20.2.9. Connector/Net FAQ ...................................................................................... 2046
20.3. MySQL Connector/J ................................................................................................ 2047
20.3.1. Overview of MySQL Connector/J .................................................................. 2047
20.3.2. Connector/J Versions ................................................................................... 2047
20.3.3. Connector/J Installation ................................................................................ 2049
20.3.4. Connector/J Examples ................................................................................. 2053
20.3.5. Connector/J (JDBC) Reference ..................................................................... 2054
20.3.6. JDBC Concepts ........................................................................................... 2087
20.3.7. Connection Pooling with Connector/J ............................................................ 2096
20.3.8. Load Balancing with Connector/J .................................................................. 2099
20.3.9. Failover with Connector/J ............................................................................. 2101
20.3.10. Using the Connector/J Interceptor Classes .................................................. 2103
20.3.11. Using Connector/J with Tomcat .................................................................. 2103
20.3.12. Using Connector/J with JBoss .................................................................... 2105
20.3.13. Using Connector/J with Spring .................................................................... 2106
20.3.14. Using Connector/J with GlassFish ............................................................... 2110
20.3.15. Troubleshooting Connector/J Applications ................................................... 2116
20.3.16. Connector/J Support ................................................................................... 2124
20.4. MySQL Connector/C ............................................................................................... 2125
20.4.1. MySQL Connector/C Supported Platforms ..................................................... 2126
20.4.2. MySQL Connector/C Distribution Contents .................................................... 2126
20.4.3. Obtaining MySQL Connector/C ..................................................................... 2126
20.4.4. Installing MySQL Connector/C from a Binary Distribution ................................ 2127
20.4.5. Installing MySQL Connector/C from Source ................................................... 2127
20.4.6. Testing MySQL Connector/C ........................................................................ 2129

MySQL 5.0 Reference Manual
xv
20.4.7. Building MySQL Connector/C Applications .................................................... 2129
20.5. libmysqld, the Embedded MySQL Server Library ...................................................... 2129
20.6. MySQL C API ......................................................................................................... 2130
20.6.1. MySQL C API Implementations .................................................................... 2131
20.6.2. Example C API Client Programs ................................................................... 2132
20.6.3. Building and Running C API Client Programs ................................................ 2132
20.6.4. C API Data Structures .................................................................................. 2135
20.6.5. C API Function Overview ............................................................................. 2139
20.6.6. C API Function Descriptions ......................................................................... 2144
20.6.7. C API Prepared Statements ......................................................................... 2191
20.6.8. C API Prepared Statement Data Structures ................................................... 2191
20.6.9. C API Prepared Statement Function Overview ............................................... 2197
20.6.10. C API Prepared Statement Function Descriptions ........................................ 2200
20.6.11. C API Threaded Function Descriptions ........................................................ 2221
20.6.12. C API Embedded Server Function Descriptions ........................................... 2223
20.6.13. Common Questions and Problems When Using the C API ............................ 2223
20.6.14. Controlling Automatic Reconnection Behavior .............................................. 2225
20.6.15. C API Support for Multiple Statement Execution .......................................... 2226
20.6.16. C API Prepared Statement Problems .......................................................... 2228
20.6.17. C API Prepared Statement Handling of Date and Time Values ...................... 2228
20.6.18. C API Support for Prepared CALL Statements ............................................. 2229
20.7. MySQL PHP API .................................................................................................... 2230
20.7.1. Overview of the MySQL PHP drivers ............................................................ 2230
20.7.2. Original MySQL API (Mysql) ........................................................................ 2237
20.7.3. MySQL Improved Extension (Mysqli) .......................................................... 2306
20.7.4. MySQL Functions (PDO_MYSQL) (MySQL (PDO)) ....................................... 2520
20.7.5. MySQL Native Driver (Mysqlnd) .................................................................. 2524
20.7.6. Mysqlnd replication and load balancing plugin (mysqlnd_ms) ......................... 2556
20.7.7. Mysqlnd query result cache plugin (mysqlnd_qc) ......................................... 2657
20.7.8. Mysqlnd user handler plugin (mysqlnd_uh) .................................................. 2708
20.7.9. Mysqlnd connection multiplexing plugin (mysqlnd_mux) ................................ 2791
20.7.10. Mysqlnd Memcache plugin (mysqlnd_memcache) ....................................... 2795
20.7.11. Connector/PHP .......................................................................................... 2804
20.7.12. Common Problems with MySQL and PHP ................................................... 2804
20.7.13. Enabling Both mysql and mysqli in PHP .................................................. 2804
20.8. MySQL Perl API ..................................................................................................... 2805
20.9. MySQL Python API ................................................................................................. 2806
20.10. MySQL Ruby APIs ................................................................................................ 2806
20.10.1. The MySQL/Ruby API ................................................................................ 2806
20.10.2. The Ruby/MySQL API ................................................................................ 2806
20.11. MySQL Tcl API ..................................................................................................... 2806
20.12. MySQL Eiffel Wrapper .......................................................................................... 2806
21. Extending MySQL .............................................................................................................. 2807
21.1. MySQL Internals ..................................................................................................... 2807
21.1.1. MySQL Threads ........................................................................................... 2807
21.1.2. The MySQL Test Suite ................................................................................. 2808
21.2. Adding New Functions to MySQL ............................................................................ 2808
21.2.1. Features of the User-Defined Function Interface ............................................ 2809
21.2.2. Adding a New User-Defined Function ........................................................... 2809
21.2.3. Adding a New Native Function ..................................................................... 2819
21.3. Debugging and Porting MySQL ............................................................................... 2820
21.3.1. Debugging a MySQL Server ......................................................................... 2821
21.3.2. Debugging a MySQL Client .......................................................................... 2827
21.3.3. The DBUG Package .................................................................................... 2827
22. MySQL Enterprise Edition .................................................................................................. 2831
22.1. MySQL Enterprise Monitor ...................................................................................... 2831
22.2. MySQL Enterprise Backup ...................................................................................... 2832
22.3. MySQL Enterprise Security ..................................................................................... 2832

MySQL 5.0 Reference Manual
xvi
22.4. MySQL Enterprise Audit .......................................................................................... 2833
22.5. MySQL Thread Pool ............................................................................................... 2833
A. Licenses for Third-Party Components .................................................................................. 2835
A.1. Ant-Contrib License .................................................................................................. 2837
A.2. ANTLR 3.3 License .................................................................................................. 2838
A.3. Boost Library License ............................................................................................... 2838
A.4. c3p0 JDBC Library License ...................................................................................... 2839
A.5. dtoa.c License ....................................................................................................... 2839
A.6. Editline Library (libedit) License ........................................................................... 2839
A.7. Fred Fish's Dbug Library License .............................................................................. 2841
A.8. GLib License (for MySQL Proxy) ............................................................................... 2842
A.9. GNU General Public License Version 2.0, June 1991 ................................................. 2843
A.10. GNU Lesser General Public License Version 2.1, February 1999 .............................. 2848
A.11. GNU Libtool License .............................................................................................. 2855
A.12. GNU Readline License ........................................................................................... 2856
A.13. jboss-common-jdbc-wrapper.jar License .................................................................. 2856
A.14. lib_sql.cc License ............................................................................................. 2856
A.15. libevent License ................................................................................................. 2857
A.16. Libiconv License ..................................................................................................... 2858
A.17. libintl License ................................................................................................... 2859
A.18. LPeg Library License ............................................................................................. 2859
A.19. Lua (liblua) License ................................................................................................ 2860
A.20. LuaFileSystem Library License ............................................................................ 2860
A.21. md5 (Message-Digest Algorithm 5) License ............................................................. 2861
A.22. OpenSSL v1.0 License ........................................................................................... 2861
A.23. PCRE License ........................................................................................................ 2862
A.24. RegEX-Spencer Library License .............................................................................. 2863
A.25. RFC 3174 - US Secure Hash Algorithm 1 (SHA1) License ........................................ 2864
A.26. Richard A. O'Keefe String Library License ............................................................... 2864
A.27. SHA-1 in C License ................................................................................................ 2865
A.28. Simple Logging Facade for Java (SLF4J) License .................................................... 2865
A.29. zlib License ......................................................................................................... 2865
A.30. ZLIB.NET License .................................................................................................. 2866
B. MySQL 5.0 Frequently Asked Questions .............................................................................. 2867
B.1. MySQL 5.0 FAQ: General ........................................................................................ 2867
B.2. MySQL 5.0 FAQ: Storage Engines ............................................................................ 2869
B.3. MySQL 5.0 FAQ: Server SQL Mode ......................................................................... 2869
B.4. MySQL 5.0 FAQ: Stored Procedures and Functions ................................................... 2870
B.5. MySQL 5.0 FAQ: Triggers ........................................................................................ 2874
B.6. MySQL 5.0 FAQ: Views ........................................................................................... 2876
B.7. MySQL 5.0 FAQ: INFORMATION_SCHEMA ................................................................. 2877
B.8. MySQL 5.0 FAQ: Migration ....................................................................................... 2878
B.9. MySQL 5.0 FAQ: Security ........................................................................................ 2878
B.10. MySQL 5.0 FAQ: MySQL Cluster ............................................................................ 2879
B.11. MySQL 5.0 FAQ: MySQL Chinese, Japanese, and Korean Character Sets ................ 2891
B.12. MySQL 5.0 FAQ: Connectors & APIs ...................................................................... 2903
B.13. MySQL 5.0 FAQ: Replication .................................................................................. 2903
C. Errors, Error Codes, and Common Problems ....................................................................... 2909
C.1. Sources of Error Information ..................................................................................... 2909
C.2. Types of Error Values .............................................................................................. 2909
C.3. Server Error Codes and Messages ........................................................................... 2910
C.4. Client Error Codes and Messages ............................................................................ 2941
C.5. Problems and Common Errors .................................................................................. 2945
C.5.1. How to Determine What Is Causing a Problem ............................................... 2945
C.5.2. Common Errors When Using MySQL Programs .............................................. 2946
C.5.3. Installation-Related Issues ............................................................................. 2959
C.5.4. Administration-Related Issues ........................................................................ 2959
C.5.5. Query-Related Issues .................................................................................... 2966

MySQL 5.0 Reference Manual
xvii
C.5.6. Optimizer-Related Issues ............................................................................... 2973
C.5.7. Table Definition-Related Issues ...................................................................... 2974
C.5.8. Known Issues in MySQL ............................................................................... 2975
D. MySQL Release Notes ........................................................................................................ 2979
E. Restrictions and Limits ........................................................................................................ 2981
E.1. Restrictions on Stored Programs ............................................................................... 2981
E.2. Restrictions on Server-Side Cursors .......................................................................... 2983
E.3. Restrictions on Subqueries ....................................................................................... 2984
E.4. Restrictions on Views ............................................................................................... 2985
E.5. Restrictions on XA Transactions ............................................................................... 2987
E.6. Restrictions on Character Sets .................................................................................. 2988
E.7. Limits in MySQL ...................................................................................................... 2988
E.7.1. Limits of Joins ............................................................................................... 2988
E.7.2. Limits on Number of Databases and Tables .................................................... 2988
E.7.3. Limits on Table Size ...................................................................................... 2989
E.7.4. Limits on Table Column Count and Row Size ................................................. 2990
E.7.5. Limits Imposed by .frm File Structure ........................................................... 2991
E.7.6. Windows Platform Limitations ......................................................................... 2992
General Index ......................................................................................................................... 2995
C Function Index ..................................................................................................................... 3055
Command Index ...................................................................................................................... 3063
Function Index ........................................................................................................................ 3087
INFORMATION_SCHEMA Index ................................................................................................. 3103
Join Types Index ..................................................................................................................... 3105
Operator Index ........................................................................................................................ 3107
Option Index ........................................................................................................................... 3111
Privileges Index ....................................................................................................................... 3155
SQL Modes Index ................................................................................................................... 3159
Statement/Syntax Index ........................................................................................................... 3161
System Variable Index ............................................................................................................. 3191
Status Variable Index .............................................................................................................. 3205
Transaction Isolation Level Index ............................................................................................. 3211

xviii

xix
Preface and Legal Notices
This is the Reference Manual for the MySQL Database System, version 5.0, through release 5.0.96.
Differences between minor versions of MySQL 5.0 are noted in the present text with reference to
release numbers (5.0.x). For license information, see the Legal Notices. This product may contain
third-party code. For license information on third-party code, see Appendix A, Licenses for Third-Party
Components.
This manual is not intended for use with older versions of the MySQL software due to the many
functional and other differences between MySQL 5.0 and previous versions. If you are using an earlier
release of the MySQL software, please refer to the appropriate manual. For example, MySQL 3.23, 4.0,
4.1 Reference Manual covers the 4.1 series of MySQL software releases.
If you are using MySQL 5.1, please refer to the MySQL 5.1 Reference Manual.
Legal Notices
Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved.
This software and related documentation are provided under a license agreement containing
restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly
permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate,
broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any
form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless
required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-
free. If you find any errors, please report them to us in writing.
If this software or related documentation is delivered to the U.S. Government or anyone licensing it on
behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and
technical data delivered to U.S. Government customers are "commercial computer software" or
"commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-
specific supplemental regulations. As such, the use, duplication, disclosure, modification, and
adaptation shall be subject to the restrictions and license terms set forth in the applicable Government
contract, and, to the extent applicable by the terms of the Government contract, the additional rights set
forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc.,
500 Oracle Parkway, Redwood City, CA 94065.
This software is developed for general use in a variety of information management applications. It is not
developed or intended for use in any inherently dangerous applications, including applications which
may create a risk of personal injury. If you use this software in dangerous applications, then you shall
be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the
safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages
caused by use of this software in dangerous applications.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. MySQL is a trademark
of Oracle Corporation and/or its affiliates, and shall not be used without Oracle's express written
authorization. Other names may be trademarks of their respective owners.
This software and documentation may provide access to or information on content, products, and
services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly
disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle
Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to
your access to or use of third-party content, products, or services.
This document in any form, software or printed matter, contains proprietary information that is the
exclusive property of Oracle. Your access to and use of this material is subject to the terms and

Legal Notices
xx
conditions of your Oracle Software License and Service Agreement, which has been executed and with
which you agree to comply. This document and information contained herein may not be disclosed,
copied, reproduced, or distributed to anyone outside Oracle without prior written consent of Oracle
or as specifically provided below. This document is not part of your license agreement nor can it be
incorporated into any contractual agreement with Oracle or its subsidiaries or affiliates.
This documentation is NOT distributed under a GPL license. Use of this documentation is subject to the
following terms:
You may create a printed copy of this documentation solely for your own personal use. Conversion
to other formats is allowed as long as the actual content is not altered or edited in any way. You shall
not publish or distribute this documentation in any form or on any media, except if you distribute the
documentation in a manner similar to how Oracle disseminates it (that is, electronically for download
on a Web site with the software) or on a CD-ROM or similar medium, provided however that the
documentation is disseminated together with the software on the same medium. Any other use, such
as any dissemination of printed copies or use of this documentation, in whole or in part, in another
publication, requires the prior written consent from an authorized representative of Oracle. Oracle and/
or its affiliates reserve any and all rights to this documentation not expressly granted above.
For more information on the terms of this license, or for details on how the MySQL documentation is
built and produced, please visit MySQL Contact & Questions.
For additional licensing information, including licenses for third-party libraries used by MySQL products,
see Preface and Legal Notices.
For help with using MySQL, please visit either the MySQL Forums or MySQL Mailing Lists where you
can discuss your issues with other MySQL users.
For additional documentation on MySQL products, including translations of the documentation into
other languages, and downloadable versions in variety of formats, including HTML and PDF formats,
see the MySQL Documentation Library.