MySQL 5.5 Reference Manual Including NDB Cluster 7.2 Guide Ref En.a4
User Manual:
Open the PDF directly: View PDF .
Page Count: 3648
MySQL 5.5 Reference Manual
Including MySQL NDB Cluster 7.2 Reference Guide
Abstract
This is the MySQL™ Reference Manual. It documents MySQL 5.5 through 5.5.63, as well as NDB Cluster
releases based on version 7.2 of NDBCLUSTER through 5.5.62-ndb-7.2.36. It may include documentation of
features of MySQL versions that have not yet been released. For information about which versions have been
released, see the MySQL 5.5 Release Notes.
MySQL 5.5 features.
This manual describes features that are not included in every edition of MySQL 5.5; such
features may not be included in the edition of MySQL 5.5 licensed to you. If you have any questions about the
features included in your edition of MySQL 5.5, refer to your MySQL 5.5 license agreement or contact your Oracle
sales representative.
For notes detailing the changes in each release, see the MySQL 5.5 Release Notes.
For legal information, including licensing information, see the Preface and Legal Notices.
For help with using MySQL, please visit either the MySQL Forums or MySQL Mailing Lists, where you can discuss
your issues with other MySQL users.
Document generated on: 2018-11-03 (revision: 59816)
Table of Contents
Preface and Legal Notices ........................................................................................................ xxiii
1 General Information ................................................................................................................... 1
1.1 About This Manual .......................................................................................................... 2
1.2 Typographical and Syntax Conventions ........................................................................... 2
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 ............................................................................... 5
1.3.3 History of MySQL ................................................................................................. 8
1.4 What Is New in MySQL 5.5 ............................................................................................. 9
1.5 MySQL Information Sources .......................................................................................... 17
1.5.1 MySQL Websites ............................................................................................... 17
1.5.2 MySQL Mailing Lists ........................................................................................... 17
1.5.3 MySQL Community Support at the MySQL Forums .............................................. 20
1.5.4 MySQL Community Support on Internet Relay Chat (IRC) .................................... 20
1.5.5 MySQL Enterprise .............................................................................................. 20
1.6 How to Report Bugs or Problems .................................................................................. 21
1.7 MySQL Standards Compliance ...................................................................................... 25
1.7.1 MySQL Extensions to Standard SQL ................................................................... 26
1.7.2 MySQL Differences from Standard SQL .............................................................. 29
1.7.3 How MySQL Deals with Constraints .................................................................... 31
1.8 Credits .......................................................................................................................... 34
1.8.1 Contributors to MySQL ....................................................................................... 34
1.8.2 Documenters and translators .............................................................................. 38
1.8.3 Packages that support MySQL ............................................................................ 39
1.8.4 Tools that were used to create MySQL ............................................................... 40
1.8.5 Supporters of MySQL ......................................................................................... 40
2 Installing and Upgrading MySQL .............................................................................................. 43
2.1 General Installation Guidance ........................................................................................ 45
2.1.1 Which MySQL Version and Distribution to Install .................................................. 45
2.1.2 How to Get MySQL ............................................................................................ 47
2.1.3 Verifying Package Integrity Using MD5 Checksums or GnuPG .............................. 47
2.1.4 Installation Layouts ............................................................................................. 60
2.1.5 Compiler-Specific Build Characteristics ................................................................ 60
2.2 Installing MySQL on Unix/Linux Using Generic Binaries .................................................. 60
2.3 Installing MySQL on Microsoft Windows ......................................................................... 63
2.3.1 MySQL Installation Layout on Microsoft Windows ................................................ 65
2.3.2 Choosing an Installation Package ....................................................................... 66
2.3.3 MySQL Installer for Windows .............................................................................. 67
2.3.4 MySQL Notifier ................................................................................................... 88
2.3.5 Installing MySQL on Microsoft Windows Using an MSI Package ........................... 99
2.3.6 MySQL Server Instance Configuration Wizard .................................................... 105
2.3.7 Installing MySQL on Microsoft Windows Using a noinstall ZIP Archive ............ 121
2.3.8 Troubleshooting a Microsoft Windows MySQL Server Installation ........................ 129
2.3.9 Windows Postinstallation Procedures ................................................................ 131
2.3.10 Upgrading MySQL on Windows ....................................................................... 133
2.4 Installing MySQL on OS X ........................................................................................... 135
2.4.1 General Notes on Installing MySQL on OS X ..................................................... 135
2.4.2 Installing MySQL on OS X Using Native Packages ............................................. 136
2.4.3 Installing a MySQL Launch Daemon ................................................................. 140
2.4.4 Installing and Using the MySQL Preference Pane .............................................. 143
2.5 Installing MySQL on Linux ........................................................................................... 147
2.5.1 Installing MySQL on Linux Using RPM Packages ............................................... 148
2.5.2 Installing MySQL on Linux Using Debian Packages ............................................ 153
2.5.3 Deploying MySQL on Linux with Docker ............................................................ 153
2.5.4 Installing MySQL on Linux Using Native Package Managers ............................... 160
iii
MySQL 5.5 Reference Manual
2.6 Installing MySQL Using Unbreakable Linux Network (ULN) ...........................................
2.7 Installing MySQL on Solaris .........................................................................................
2.7.1 Installing MySQL on Solaris Using a Solaris PKG ..............................................
2.8 Installing MySQL on FreeBSD .....................................................................................
2.9 Installing MySQL from Source .....................................................................................
2.9.1 MySQL Layout for Source Installation ................................................................
2.9.2 Installing MySQL Using a Standard Source Distribution ......................................
2.9.3 Installing MySQL Using a Development Source Tree ..........................................
2.9.4 MySQL Source-Configuration Options ...............................................................
2.9.5 Dealing with Problems Compiling MySQL ..........................................................
2.9.6 MySQL Configuration and Third-Party Tools ......................................................
2.10 Postinstallation Setup and Testing .............................................................................
2.10.1 Initializing the Data Directory ...........................................................................
2.10.2 Starting the Server .........................................................................................
2.10.3 Testing the Server ..........................................................................................
2.10.4 Securing the Initial MySQL Accounts ...............................................................
2.10.5 Starting and Stopping MySQL Automatically ....................................................
2.11 Upgrading or Downgrading MySQL ............................................................................
2.11.1 Upgrading MySQL ..........................................................................................
2.11.2 Downgrading MySQL ......................................................................................
2.11.3 Rebuilding or Repairing Tables or Indexes .......................................................
2.11.4 Copying MySQL Databases to Another Machine ..............................................
2.12 Perl Installation Notes ................................................................................................
2.12.1 Installing Perl on Unix .....................................................................................
2.12.2 Installing ActiveState Perl on Windows ............................................................
2.12.3 Problems Using the Perl DBI/DBD Interface .....................................................
3 Tutorial ..................................................................................................................................
3.1 Connecting to and Disconnecting from the Server .........................................................
3.2 Entering Queries .........................................................................................................
3.3 Creating and Using a Database ...................................................................................
3.3.1 Creating and Selecting a Database ...................................................................
3.3.2 Creating a Table ..............................................................................................
3.3.3 Loading Data into a Table ................................................................................
3.3.4 Retrieving Information from a Table ...................................................................
3.4 Getting Information About Databases and Tables .........................................................
3.5 Using mysql in Batch Mode .........................................................................................
3.6 Examples of Common Queries ....................................................................................
3.6.1 The Maximum Value for a Column ....................................................................
3.6.2 The Row Holding the Maximum of a Certain Column ..........................................
3.6.3 Maximum of Column per Group ........................................................................
3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column .......................
3.6.5 Using User-Defined Variables ...........................................................................
3.6.6 Using Foreign Keys ..........................................................................................
3.6.7 Searching on Two Keys ....................................................................................
3.6.8 Calculating Visits Per Day .................................................................................
3.6.9 Using AUTO_INCREMENT ...............................................................................
3.7 Using MySQL with Apache ..........................................................................................
4 MySQL Programs ..................................................................................................................
4.1 Overview of MySQL Programs .....................................................................................
4.2 Using MySQL Programs ..............................................................................................
4.2.1 Invoking MySQL Programs ...............................................................................
4.2.2 Connecting to the MySQL Server ......................................................................
4.2.3 Specifying Program Options ..............................................................................
4.2.4 Using Options on the Command Line ................................................................
4.2.5 Program Option Modifiers .................................................................................
4.2.6 Using Option Files ............................................................................................
4.2.7 Command-Line Options that Affect Option-File Handling .....................................
4.2.8 Using Options to Set Program Variables ............................................................
iv
163
164
165
166
166
168
168
173
175
187
188
188
189
193
196
198
202
203
203
213
217
219
220
220
221
222
223
223
224
227
228
229
230
231
245
246
247
248
248
248
249
249
250
251
252
252
255
257
258
262
262
263
267
267
269
270
274
275
MySQL 5.5 Reference Manual
4.2.9 Option Defaults, Options Expecting Values, and the = Sign .................................
4.2.10 Setting Environment Variables .........................................................................
4.3 MySQL Server and Server-Startup Programs ...............................................................
4.3.1 mysqld — The MySQL Server .........................................................................
4.3.2 mysqld_safe — MySQL Server Startup Script .................................................
4.3.3 mysql.server — MySQL Server Startup Script ...............................................
4.3.4 mysqld_multi — Manage Multiple MySQL Servers .........................................
4.4 MySQL Installation-Related Programs ..........................................................................
4.4.1 comp_err — Compile MySQL Error Message File ............................................
4.4.2 mysqlbug — Generate Bug Report ..................................................................
4.4.3 mysql_install_db — Initialize MySQL Data Directory ....................................
4.4.4 mysql_plugin — Configure MySQL Server Plugins .........................................
4.4.5 mysql_secure_installation — Improve MySQL Installation Security ...........
4.4.6 mysql_tzinfo_to_sql — Load the Time Zone Tables ...................................
4.4.7 mysql_upgrade — Check and Upgrade MySQL Tables ...................................
4.5 MySQL Client Programs ..............................................................................................
4.5.1 mysql — The MySQL Command-Line Tool .......................................................
4.5.2 mysqladmin — Client for Administering a MySQL Server ..................................
4.5.3 mysqlcheck — A Table Maintenance Program .................................................
4.5.4 mysqldump — A Database Backup Program .....................................................
4.5.5 mysqlimport — A Data Import Program .........................................................
4.5.6 mysqlshow — Display Database, Table, and Column Information ......................
4.5.7 mysqlslap — Load Emulation Client ...............................................................
4.6 MySQL Administrative and Utility Programs ..................................................................
4.6.1 innochecksum — Offline InnoDB File Checksum Utility ....................................
4.6.2 myisam_ftdump — Display Full-Text Index information .....................................
4.6.3 myisamchk — MyISAM Table-Maintenance Utility .............................................
4.6.4 myisamlog — Display MyISAM Log File Contents ............................................
4.6.5 myisampack — Generate Compressed, Read-Only MyISAM Tables ..................
4.6.6 mysqlaccess — Client for Checking Access Privileges .....................................
4.6.7 mysqlbinlog — Utility for Processing Binary Log Files .....................................
4.6.8 mysqldumpslow — Summarize Slow Query Log Files ......................................
4.6.9 mysqlhotcopy — A Database Backup Program ...............................................
4.6.10 mysql_convert_table_format — Convert Tables to Use a Given Storage
Engine ......................................................................................................................
4.6.11 mysql_find_rows — Extract SQL Statements from Files ...............................
4.6.12 mysql_fix_extensions — Normalize Table File Name Extensions ...............
4.6.13 mysql_setpermission — Interactively Set Permissions in Grant Tables ........
4.6.14 mysql_waitpid — Kill Process and Wait for Its Termination ...........................
4.6.15 mysql_zap — Kill Processes That Match a Pattern .........................................
4.7 MySQL Program Development Utilities .........................................................................
4.7.1 msql2mysql — Convert mSQL Programs for Use with MySQL ..........................
4.7.2 mysql_config — Display Options for Compiling Clients ...................................
4.7.3 my_print_defaults — Display Options from Option Files ..............................
4.7.4 resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols .......
4.8 Miscellaneous Programs ..............................................................................................
4.8.1 perror — Explain Error Codes ........................................................................
4.8.2 replace — A String-Replacement Utility ..........................................................
4.8.3 resolveip — Resolve Host name to IP Address or Vice Versa .........................
4.9 MySQL Program Environment Variables .......................................................................
5 MySQL Server Administration .................................................................................................
5.1 The MySQL Server .....................................................................................................
5.1.1 Configuring the Server ......................................................................................
5.1.2 Server Configuration Defaults ...........................................................................
5.1.3 Server Option, System Variable, and Status Variable Reference .........................
5.1.4 Server System Variable Reference ....................................................................
5.1.5 Server Status Variable Reference .....................................................................
5.1.6 Server Command Options .................................................................................
276
279
280
280
281
286
288
292
292
293
293
295
297
297
298
303
303
325
333
340
358
364
368
375
375
376
377
394
395
400
403
416
418
421
422
422
423
424
424
425
425
425
427
427
428
428
429
430
430
433
434
434
436
436
463
477
487
v
MySQL 5.5 Reference Manual
5.1.7 Server System Variables ..................................................................................
5.1.8 Using System Variables ....................................................................................
5.1.9 Server Status Variables ....................................................................................
5.1.10 Server SQL Modes .........................................................................................
5.1.11 IPv6 Support ..................................................................................................
5.1.12 MySQL Server Time Zone Support ..................................................................
5.1.13 Server-Side Help ............................................................................................
5.1.14 Server Response to Signals ............................................................................
5.1.15 The Server Shutdown Process ........................................................................
5.2 The MySQL Data Directory ..........................................................................................
5.3 The mysql System Database .......................................................................................
5.4 MySQL Server Logs ....................................................................................................
5.4.1 Selecting General Query Log and Slow Query Log Output Destinations ...............
5.4.2 The Error Log ..................................................................................................
5.4.3 The General Query Log ....................................................................................
5.4.4 The Binary Log ................................................................................................
5.4.5 The Slow Query Log ........................................................................................
5.4.6 The DDL Log ...................................................................................................
5.4.7 Server Log Maintenance ...................................................................................
5.5 MySQL Server Plugins ................................................................................................
5.5.1 Installing and Uninstalling Plugins .....................................................................
5.5.2 Obtaining Server Plugin Information ..................................................................
5.5.3 MySQL Enterprise Thread Pool .........................................................................
5.6 MySQL Server User-Defined Functions ........................................................................
5.6.1 Installing and Uninstalling User-Defined Functions .............................................
5.6.2 Obtaining User-Defined Function Information .....................................................
5.7 Running Multiple MySQL Instances on One Machine ....................................................
5.7.1 Setting Up Multiple Data Directories ..................................................................
5.7.2 Running Multiple MySQL Instances on Windows ................................................
5.7.3 Running Multiple MySQL Instances on Unix .......................................................
5.7.4 Using Client Programs in a Multiple-Server Environment ....................................
5.8 Tracing mysqld Using DTrace ......................................................................................
5.8.1 mysqld DTrace Probe Reference ......................................................................
6 Security .................................................................................................................................
6.1 General Security Issues ..............................................................................................
6.1.1 Security Guidelines ...........................................................................................
6.1.2 Keeping Passwords Secure ..............................................................................
6.1.3 Making MySQL Secure Against Attackers ..........................................................
6.1.4 Security-Related mysqld Options and Variables .................................................
6.1.5 How to Run MySQL as a Normal User ..............................................................
6.1.6 Security Issues with LOAD DATA LOCAL ..........................................................
6.1.7 Client Programming Security Guidelines ............................................................
6.2 The MySQL Access Privilege System ..........................................................................
6.2.1 Privileges Provided by MySQL ..........................................................................
6.2.2 Grant Tables ....................................................................................................
6.2.3 Specifying Account Names ...............................................................................
6.2.4 Access Control, Stage 1: Connection Verification ...............................................
6.2.5 Access Control, Stage 2: Request Verification ...................................................
6.2.6 When Privilege Changes Take Effect ................................................................
6.2.7 Troubleshooting Problems Connecting to MySQL ...............................................
6.3 MySQL User Account Management .............................................................................
6.3.1 User Names and Passwords .............................................................................
6.3.2 Adding User Accounts ......................................................................................
6.3.3 Removing User Accounts ..................................................................................
6.3.4 Setting Account Resource Limits .......................................................................
6.3.5 Assigning Account Passwords ...........................................................................
6.3.6 Pluggable Authentication ...................................................................................
6.3.7 Proxy Users .....................................................................................................
vi
520
607
619
634
642
646
651
651
652
653
654
656
656
659
661
662
673
675
675
677
677
680
681
687
688
688
689
690
691
694
695
696
697
715
716
716
717
725
727
727
728
729
731
732
738
743
745
747
749
750
755
755
757
758
759
761
762
764
MySQL 5.5 Reference Manual
6.3.8 SQL-Based MySQL Account Activity Auditing ....................................................
6.4 Using Encrypted Connections ......................................................................................
6.4.1 Configuring MySQL to Use Encrypted Connections ............................................
6.4.2 Command Options for Encrypted Connections ...................................................
6.4.3 Creating SSL Certificates and Keys Using openssl .............................................
6.4.4 OpenSSL Versus yaSSL ...................................................................................
6.4.5 Building MySQL with Support for Encrypted Connections ....................................
6.4.6 Encrypted Connection Protocols and Ciphers .....................................................
6.4.7 Connecting to MySQL Remotely from Windows with SSH ...................................
6.5 Security Plugins ..........................................................................................................
6.5.1 Authentication Plugins ......................................................................................
6.5.2 MySQL Enterprise Audit ...................................................................................
7 Backup and Recovery ............................................................................................................
7.1 Backup and Recovery Types .......................................................................................
7.2 Database Backup Methods ..........................................................................................
7.3 Example Backup and Recovery Strategy ......................................................................
7.3.1 Establishing a Backup Policy ............................................................................
7.3.2 Using Backups for Recovery .............................................................................
7.3.3 Backup Strategy Summary ................................................................................
7.4 Using mysqldump for Backups .....................................................................................
7.4.1 Dumping Data in SQL Format with mysqldump ..................................................
7.4.2 Reloading SQL-Format Backups .......................................................................
7.4.3 Dumping Data in Delimited-Text Format with mysqldump ....................................
7.4.4 Reloading Delimited-Text Format Backups .........................................................
7.4.5 mysqldump Tips ...............................................................................................
7.5 Point-in-Time (Incremental) Recovery Using the Binary Log ...........................................
7.5.1 Point-in-Time Recovery Using Event Times .......................................................
7.5.2 Point-in-Time Recovery Using Event Positions ...................................................
7.6 MyISAM Table Maintenance and Crash Recovery ........................................................
7.6.1 Using myisamchk for Crash Recovery ...............................................................
7.6.2 How to Check MyISAM Tables for Errors ...........................................................
7.6.3 How to Repair MyISAM Tables .........................................................................
7.6.4 MyISAM Table Optimization ..............................................................................
7.6.5 Setting Up a MyISAM Table Maintenance Schedule ...........................................
8 Optimization ...........................................................................................................................
8.1 Optimization Overview .................................................................................................
8.2 Optimizing SQL Statements .........................................................................................
8.2.1 Optimizing SELECT Statements ........................................................................
8.2.2 Subquery Optimization ......................................................................................
8.2.3 Optimizing INFORMATION_SCHEMA Queries ...................................................
8.2.4 Optimizing Data Change Statements .................................................................
8.2.5 Optimizing Database Privileges .........................................................................
8.2.6 Other Optimization Tips ....................................................................................
8.3 Optimization and Indexes ............................................................................................
8.3.1 How MySQL Uses Indexes ...............................................................................
8.3.2 Primary Key Optimization ..................................................................................
8.3.3 Foreign Key Optimization ..................................................................................
8.3.4 Column Indexes ...............................................................................................
8.3.5 Multiple-Column Indexes ...................................................................................
8.3.6 Verifying Index Usage ......................................................................................
8.3.7 InnoDB and MyISAM Index Statistics Collection .................................................
8.3.8 Comparison of B-Tree and Hash Indexes ..........................................................
8.4 Optimizing Database Structure .....................................................................................
8.4.1 Optimizing Data Size ........................................................................................
8.4.2 Optimizing MySQL Data Types .........................................................................
8.4.3 Optimizing for Many Tables ..............................................................................
8.4.4 Internal Temporary Table Use in MySQL ...........................................................
8.5 Optimizing for InnoDB Tables ......................................................................................
769
771
771
774
776
782
782
783
784
784
785
804
827
828
831
833
833
835
836
836
836
837
838
839
840
842
843
844
844
845
846
846
849
849
851
852
854
854
887
892
897
898
898
899
899
900
901
901
902
903
904
905
907
907
908
910
912
913
vii
MySQL 5.5 Reference Manual
8.5.1 Optimizing Storage Layout for InnoDB Tables ....................................................
8.5.2 Optimizing InnoDB Transaction Management .....................................................
8.5.3 Optimizing InnoDB Redo Logging ......................................................................
8.5.4 Bulk Data Loading for InnoDB Tables ................................................................
8.5.5 Optimizing InnoDB Queries ...............................................................................
8.5.6 Optimizing InnoDB DDL Operations ..................................................................
8.5.7 Optimizing InnoDB Disk I/O ..............................................................................
8.5.8 Optimizing InnoDB Configuration Variables ........................................................
8.5.9 Optimizing InnoDB for Systems with Many Tables ..............................................
8.6 Optimizing for MyISAM Tables .....................................................................................
8.6.1 Optimizing MyISAM Queries .............................................................................
8.6.2 Bulk Data Loading for MyISAM Tables ..............................................................
8.6.3 Optimizing REPAIR TABLE Statements .............................................................
8.7 Optimizing for MEMORY Tables ..................................................................................
8.8 Understanding the Query Execution Plan .....................................................................
8.8.1 Optimizing Queries with EXPLAIN .....................................................................
8.8.2 EXPLAIN Output Format ...................................................................................
8.8.3 Extended EXPLAIN Output Format ....................................................................
8.8.4 Estimating Query Performance ..........................................................................
8.9 Controlling the Query Optimizer ...................................................................................
8.9.1 Controlling Query Plan Evaluation .....................................................................
8.9.2 Switchable Optimizations ..................................................................................
8.9.3 Index Hints .......................................................................................................
8.10 Buffering and Caching ...............................................................................................
8.10.1 InnoDB Buffer Pool Optimization .....................................................................
8.10.2 The MyISAM Key Cache .................................................................................
8.10.3 The MySQL Query Cache ...............................................................................
8.11 Optimizing Locking Operations ...................................................................................
8.11.1 Internal Locking Methods ................................................................................
8.11.2 Table Locking Issues ......................................................................................
8.11.3 Concurrent Inserts ..........................................................................................
8.11.4 Metadata Locking ...........................................................................................
8.11.5 External Locking .............................................................................................
8.12 Optimizing the MySQL Server ....................................................................................
8.12.1 System Factors ..............................................................................................
8.12.2 Optimizing Disk I/O .........................................................................................
8.12.3 Using Symbolic Links ......................................................................................
8.12.4 Optimizing Memory Use ..................................................................................
8.12.5 Optimizing Network Use ..................................................................................
8.13 Measuring Performance (Benchmarking) ....................................................................
8.13.1 Measuring the Speed of Expressions and Functions .........................................
8.13.2 The MySQL Benchmark Suite .........................................................................
8.13.3 Using Your Own Benchmarks .........................................................................
8.13.4 Measuring Performance with performance_schema ..........................................
8.14 Examining Thread Information ...................................................................................
8.14.1 Thread Command Values ...............................................................................
8.14.2 General Thread States ....................................................................................
8.14.3 Delayed-Insert Thread States ..........................................................................
8.14.4 Query Cache Thread States ............................................................................
8.14.5 Replication Master Thread States ....................................................................
8.14.6 Replication Slave I/O Thread States ................................................................
8.14.7 Replication Slave SQL Thread States ..............................................................
8.14.8 Replication Slave Connection Thread States ....................................................
8.14.9 NDB Cluster Thread States .............................................................................
8.14.10 Event Scheduler Thread States .....................................................................
9 Language Structure ................................................................................................................
9.1 Literal Values ..............................................................................................................
9.1.1 String Literals ...................................................................................................
viii
913
914
915
915
916
916
917
919
920
920
920
922
923
924
925
925
925
936
938
938
938
939
940
943
943
943
947
954
955
957
958
959
960
961
961
961
963
966
969
972
973
973
974
974
974
975
977
983
984
985
985
986
987
987
988
989
989
989
MySQL 5.5 Reference Manual
9.1.2 Numeric Literals ............................................................................................... 992
9.1.3 Date and Time Literals ..................................................................................... 992
9.1.4 Hexadecimal Literals ........................................................................................ 994
9.1.5 Bit-Value Literals .............................................................................................. 996
9.1.6 Boolean Literals ............................................................................................... 997
9.1.7 NULL Values .................................................................................................... 998
9.2 Schema Object Names ................................................................................................ 998
9.2.1 Identifier Qualifiers .......................................................................................... 1000
9.2.2 Identifier Case Sensitivity ................................................................................ 1002
9.2.3 Mapping of Identifiers to File Names ............................................................... 1004
9.2.4 Function Name Parsing and Resolution ........................................................... 1006
9.3 Keywords and Reserved Words ................................................................................. 1010
9.4 User-Defined Variables .............................................................................................. 1030
9.5 Expression Syntax ..................................................................................................... 1033
9.6 Comment Syntax ....................................................................................................... 1035
10 Character Sets, Collations, Unicode .................................................................................... 1037
10.1 Character Sets and Collations in General ................................................................. 1038
10.2 Character Sets and Collations in MySQL .................................................................. 1039
10.2.1 Character Set Repertoire .............................................................................. 1040
10.2.2 UTF-8 for Metadata ...................................................................................... 1042
10.3 Specifying Character Sets and Collations ................................................................. 1043
10.3.1 Collation Naming Conventions ....................................................................... 1044
10.3.2 Server Character Set and Collation ............................................................... 1044
10.3.3 Database Character Set and Collation ........................................................... 1045
10.3.4 Table Character Set and Collation ................................................................. 1046
10.3.5 Column Character Set and Collation .............................................................. 1047
10.3.6 Character String Literal Character Set and Collation ....................................... 1048
10.3.7 The National Character Set ........................................................................... 1050
10.3.8 Character Set Introducers ............................................................................. 1050
10.3.9 Examples of Character Set and Collation Assignment ..................................... 1052
10.3.10 Compatibility with Other DBMSs .................................................................. 1053
10.4 Connection Character Sets and Collations ................................................................ 1053
10.5 Configuring Application Character Set and Collation .................................................. 1060
10.6 Error Message Character Set ................................................................................... 1061
10.7 Column Character Set Conversion ........................................................................... 1062
10.8 Collation Issues ....................................................................................................... 1063
10.8.1 Using COLLATE in SQL Statements .............................................................. 1063
10.8.2 COLLATE Clause Precedence ...................................................................... 1064
10.8.3 Character Set and Collation Compatibility ...................................................... 1064
10.8.4 Collation Coercibility in Expressions ............................................................... 1064
10.8.5 The binary Collation Compared to _bin Collations ........................................... 1066
10.8.6 Examples of the Effect of Collation ................................................................ 1068
10.8.7 Using Collation in INFORMATION_SCHEMA Searches .................................. 1069
10.9 Unicode Support ...................................................................................................... 1071
10.9.1 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding) ........................ 1072
10.9.2 The utf8mb3 Character Set (3-Byte UTF-8 Unicode Encoding) ........................ 1073
10.9.3 The utf8 Character Set (Alias for utf8mb3) ..................................................... 1074
10.9.4 The ucs2 Character Set (UCS-2 Unicode Encoding) ....................................... 1074
10.9.5 The utf16 Character Set (UTF-16 Unicode Encoding) ..................................... 1074
10.9.6 The utf32 Character Set (UTF-32 Unicode Encoding) ..................................... 1075
10.9.7 Converting Between 3-Byte and 4-Byte Unicode Character Sets ...................... 1075
10.10 Supported Character Sets and Collations ................................................................ 1077
10.10.1 Unicode Character Sets .............................................................................. 1078
10.10.2 West European Character Sets ................................................................... 1082
10.10.3 Central European Character Sets ................................................................ 1083
10.10.4 South European and Middle East Character Sets ......................................... 1084
10.10.5 Baltic Character Sets .................................................................................. 1084
10.10.6 Cyrillic Character Sets ................................................................................. 1085
ix
MySQL 5.5 Reference Manual
10.10.7 Asian Character Sets ..................................................................................
10.10.8 The Binary Character Set ............................................................................
10.11 Setting the Error Message Language ......................................................................
10.12 Adding a Character Set .........................................................................................
10.12.1 Character Definition Arrays ..........................................................................
10.12.2 String Collating Support for Complex Character Sets ....................................
10.12.3 Multi-Byte Character Support for Complex Character Sets .............................
10.13 Adding a Collation to a Character Set .....................................................................
10.13.1 Collation Implementation Types ...................................................................
10.13.2 Choosing a Collation ID ..............................................................................
10.13.3 Adding a Simple Collation to an 8-Bit Character Set .....................................
10.13.4 Adding a UCA Collation to a Unicode Character Set .....................................
10.14 Character Set Configuration ...................................................................................
10.15 MySQL Server Locale Support ...............................................................................
11 Data Types ........................................................................................................................
11.1 Data Type Overview ................................................................................................
11.1.1 Numeric Type Overview ................................................................................
11.1.2 Date and Time Type Overview ......................................................................
11.1.3 String Type Overview ....................................................................................
11.2 Numeric Types ........................................................................................................
11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT,
MEDIUMINT, BIGINT ..............................................................................................
11.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC .................................
11.2.3 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE .........................
11.2.4 Bit-Value Type - BIT .....................................................................................
11.2.5 Numeric Type Attributes ................................................................................
11.2.6 Out-of-Range and Overflow Handling .............................................................
11.3 Date and Time Types ..............................................................................................
11.3.1 The DATE, DATETIME, and TIMESTAMP Types ...........................................
11.3.2 The TIME Type ............................................................................................
11.3.3 The YEAR Type ...........................................................................................
11.3.4 YEAR(2) Limitations and Migrating to YEAR(4) ..............................................
11.3.5 Automatic Initialization and Updating for TIMESTAMP ....................................
11.3.6 Fractional Seconds in Time Values ................................................................
11.3.7 Conversion Between Date and Time Types ....................................................
11.3.8 Two-Digit Years in Dates ..............................................................................
11.4 String Types ............................................................................................................
11.4.1 The CHAR and VARCHAR Types .................................................................
11.4.2 The BINARY and VARBINARY Types ...........................................................
11.4.3 The BLOB and TEXT Types .........................................................................
11.4.4 The ENUM Type ...........................................................................................
11.4.5 The SET Type ..............................................................................................
11.5 Spatial Data Types ..................................................................................................
11.5.1 Spatial Data Types .......................................................................................
11.5.2 The OpenGIS Geometry Model .....................................................................
11.5.3 Supported Spatial Data Formats ....................................................................
11.5.4 Creating Spatial Columns ..............................................................................
11.5.5 Populating Spatial Columns ...........................................................................
11.5.6 Fetching Spatial Data ....................................................................................
11.5.7 Optimizing Spatial Analysis ...........................................................................
11.5.8 Creating Spatial Indexes ...............................................................................
11.5.9 Using Spatial Indexes ...................................................................................
11.6 Data Type Default Values ........................................................................................
11.7 Data Type Storage Requirements ............................................................................
11.8 Choosing the Right Type for a Column .....................................................................
11.9 Using Data Types from Other Database Engines ......................................................
12 Functions and Operators ....................................................................................................
12.1 Function and Operator Reference ............................................................................
x
1085
1089
1089
1090
1092
1093
1093
1093
1094
1096
1097
1098
1101
1102
1105
1106
1106
1109
1110
1114
1114
1114
1115
1115
1116
1116
1118
1119
1121
1121
1122
1123
1126
1126
1127
1128
1128
1130
1131
1132
1135
1137
1139
1140
1145
1148
1148
1149
1149
1150
1151
1153
1154
1158
1158
1161
1162
MySQL 5.5 Reference Manual
12.2 Type Conversion in Expression Evaluation ...............................................................
12.3 Operators ................................................................................................................
12.3.1 Operator Precedence ....................................................................................
12.3.2 Comparison Functions and Operators ............................................................
12.3.3 Logical Operators .........................................................................................
12.3.4 Assignment Operators ...................................................................................
12.4 Control Flow Functions ............................................................................................
12.5 String Functions ......................................................................................................
12.5.1 String Comparison Functions .........................................................................
12.5.2 Regular Expressions .....................................................................................
12.5.3 Character Set and Collation of Function Results .............................................
12.6 Numeric Functions and Operators ............................................................................
12.6.1 Arithmetic Operators .....................................................................................
12.6.2 Mathematical Functions .................................................................................
12.7 Date and Time Functions .........................................................................................
12.8 What Calendar Is Used By MySQL? ........................................................................
12.9 Full-Text Search Functions ......................................................................................
12.9.1 Natural Language Full-Text Searches ............................................................
12.9.2 Boolean Full-Text Searches ..........................................................................
12.9.3 Full-Text Searches with Query Expansion ......................................................
12.9.4 Full-Text Stopwords ......................................................................................
12.9.5 Full-Text Restrictions ....................................................................................
12.9.6 Fine-Tuning MySQL Full-Text Search ............................................................
12.9.7 Adding a Collation for Full-Text Indexing ........................................................
12.10 Cast Functions and Operators ................................................................................
12.11 XML Functions ......................................................................................................
12.12 Bit Functions and Operators ...................................................................................
12.13 Encryption and Compression Functions ..................................................................
12.14 Information Functions .............................................................................................
12.15 Spatial Analysis Functions .....................................................................................
12.15.1 Spatial Function Reference .........................................................................
12.15.2 Argument Handling by Spatial Functions ......................................................
12.15.3 Functions That Create Geometry Values from WKT Values ...........................
12.15.4 Functions That Create Geometry Values from WKB Values ...........................
12.15.5 MySQL-Specific Functions That Create Geometry Values .............................
12.15.6 Geometry Format Conversion Functions ......................................................
12.15.7 Geometry Property Functions ......................................................................
12.15.8 Spatial Operator Functions ..........................................................................
12.15.9 Functions That Test Spatial Relations Between Geometry Objects .................
12.16 Aggregate (GROUP BY) Functions .........................................................................
12.16.1 Aggregate (GROUP BY) Function Descriptions .............................................
12.16.2 GROUP BY Modifiers ..................................................................................
12.16.3 MySQL Handling of GROUP BY ..................................................................
12.17 Miscellaneous Functions ........................................................................................
12.18 Precision Math ......................................................................................................
12.18.1 Types of Numeric Values ............................................................................
12.18.2 DECIMAL Data Type Characteristics ............................................................
12.18.3 Expression Handling ...................................................................................
12.18.4 Rounding Behavior .....................................................................................
12.18.5 Precision Math Examples ............................................................................
13 SQL Statement Syntax .......................................................................................................
13.1 Data Definition Statements .......................................................................................
13.1.1 ALTER DATABASE Syntax ...........................................................................
13.1.2 ALTER EVENT Syntax ..................................................................................
13.1.3 ALTER FUNCTION Syntax ...........................................................................
13.1.4 ALTER LOGFILE GROUP Syntax .................................................................
13.1.5 ALTER PROCEDURE Syntax .......................................................................
13.1.6 ALTER SERVER Syntax ...............................................................................
1171
1173
1174
1175
1181
1183
1184
1186
1197
1201
1207
1208
1209
1211
1219
1240
1241
1242
1245
1248
1248
1250
1251
1253
1254
1260
1271
1273
1279
1288
1289
1290
1291
1291
1292
1293
1293
1299
1299
1301
1301
1306
1309
1310
1315
1316
1316
1317
1319
1320
1325
1326
1326
1327
1329
1329
1330
1331
xi
MySQL 5.5 Reference Manual
13.2
13.3
13.4
13.5
13.6
xii
13.1.7 ALTER TABLE Syntax ..................................................................................
13.1.8 ALTER TABLESPACE Syntax .......................................................................
13.1.9 ALTER VIEW Syntax ....................................................................................
13.1.10 CREATE DATABASE Syntax ......................................................................
13.1.11 CREATE EVENT Syntax .............................................................................
13.1.12 CREATE FUNCTION Syntax .......................................................................
13.1.13 CREATE INDEX Syntax ..............................................................................
13.1.14 CREATE LOGFILE GROUP Syntax .............................................................
13.1.15 CREATE PROCEDURE and CREATE FUNCTION Syntax ............................
13.1.16 CREATE SERVER Syntax ..........................................................................
13.1.17 CREATE TABLE Syntax .............................................................................
13.1.18 CREATE TABLESPACE Syntax ..................................................................
13.1.19 CREATE TRIGGER Syntax .........................................................................
13.1.20 CREATE VIEW Syntax ................................................................................
13.1.21 DROP DATABASE Syntax ..........................................................................
13.1.22 DROP EVENT Syntax .................................................................................
13.1.23 DROP FUNCTION Syntax ...........................................................................
13.1.24 DROP INDEX Syntax ..................................................................................
13.1.25 DROP LOGFILE GROUP Syntax .................................................................
13.1.26 DROP PROCEDURE and DROP FUNCTION Syntax ....................................
13.1.27 DROP SERVER Syntax ..............................................................................
13.1.28 DROP TABLE Syntax .................................................................................
13.1.29 DROP TABLESPACE Syntax ......................................................................
13.1.30 DROP TRIGGER Syntax .............................................................................
13.1.31 DROP VIEW Syntax ...................................................................................
13.1.32 RENAME TABLE Syntax .............................................................................
13.1.33 TRUNCATE TABLE Syntax .........................................................................
Data Manipulation Statements ..................................................................................
13.2.1 CALL Syntax ................................................................................................
13.2.2 DELETE Syntax ............................................................................................
13.2.3 DO Syntax ...................................................................................................
13.2.4 HANDLER Syntax .........................................................................................
13.2.5 INSERT Syntax ............................................................................................
13.2.6 LOAD DATA INFILE Syntax ..........................................................................
13.2.7 LOAD XML Syntax .......................................................................................
13.2.8 REPLACE Syntax .........................................................................................
13.2.9 SELECT Syntax ............................................................................................
13.2.10 Subquery Syntax ........................................................................................
13.2.11 UPDATE Syntax .........................................................................................
Transactional and Locking Statements .....................................................................
13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax ...........................
13.3.2 Statements That Cannot Be Rolled Back .......................................................
13.3.3 Statements That Cause an Implicit Commit ....................................................
13.3.4 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT
Syntax ....................................................................................................................
13.3.5 LOCK TABLES and UNLOCK TABLES Syntax ..............................................
13.3.6 SET TRANSACTION Syntax .........................................................................
13.3.7 XA Transactions ...........................................................................................
Replication Statements ............................................................................................
13.4.1 SQL Statements for Controlling Master Servers ..............................................
13.4.2 SQL Statements for Controlling Slave Servers ...............................................
Prepared SQL Statement Syntax .............................................................................
13.5.1 PREPARE Syntax .........................................................................................
13.5.2 EXECUTE Syntax .........................................................................................
13.5.3 DEALLOCATE PREPARE Syntax ..................................................................
13.5.4 Automatic Prepared Statement Repreparation ................................................
Compound-Statement Syntax ...................................................................................
13.6.1 BEGIN ... END Compound-Statement Syntax .................................................
1331
1352
1353
1353
1354
1358
1358
1363
1365
1370
1371
1402
1403
1405
1409
1410
1411
1411
1411
1412
1412
1412
1413
1413
1414
1414
1415
1416
1416
1418
1421
1422
1423
1432
1441
1449
1451
1466
1478
1480
1480
1483
1483
1484
1485
1490
1492
1495
1495
1498
1504
1507
1507
1508
1508
1508
1508
MySQL 5.5 Reference Manual
13.6.2 Statement Label Syntax ................................................................................
13.6.3 DECLARE Syntax .........................................................................................
13.6.4 Variables in Stored Programs ........................................................................
13.6.5 Flow Control Statements ...............................................................................
13.6.6 Cursors ........................................................................................................
13.6.7 Condition Handling ........................................................................................
13.7 Database Administration Statements ........................................................................
13.7.1 Account Management Statements .................................................................
13.7.2 Table Maintenance Statements .....................................................................
13.7.3 Plugin and User-Defined Function Statements ................................................
13.7.4 SET Syntax ..................................................................................................
13.7.5 SHOW Syntax ..............................................................................................
13.7.6 Other Administrative Statements ....................................................................
13.8 Utility Statements ....................................................................................................
13.8.1 DESCRIBE Syntax ........................................................................................
13.8.2 EXPLAIN Syntax ..........................................................................................
13.8.3 HELP Syntax ................................................................................................
13.8.4 USE Syntax ..................................................................................................
14 The InnoDB Storage Engine ...............................................................................................
14.1 Introduction to InnoDB .............................................................................................
14.1.1 Benefits of Using InnoDB Tables ...................................................................
14.1.2 Best Practices for InnoDB Tables ..................................................................
14.1.3 Checking InnoDB Availability .........................................................................
14.1.4 Upward and Downward Compatibility .............................................................
14.1.5 Testing and Benchmarking with InnoDB .........................................................
14.1.6 Turning Off InnoDB .......................................................................................
14.1.7 Third-Party Software Contributions ................................................................
14.2 Installing the InnoDB Storage Engine .......................................................................
14.3 Upgrading the InnoDB Storage Engine .....................................................................
14.4 Downgrading the InnoDB Storage Engine .................................................................
14.5 InnoDB and the ACID Model ....................................................................................
14.6 InnoDB Multi-Versioning ...........................................................................................
14.7 InnoDB Architecture .................................................................................................
14.8 InnoDB In-Memory Structures ..................................................................................
14.8.1 Buffer Pool ...................................................................................................
14.8.2 Change Buffer ..............................................................................................
14.8.3 Adaptive Hash Index .....................................................................................
14.8.4 Redo Log Buffer ...........................................................................................
14.9 InnoDB On-Disk Structures ......................................................................................
14.9.1 Tables ..........................................................................................................
14.9.2 Indexes ........................................................................................................
14.9.3 Tablespaces .................................................................................................
14.9.4 InnoDB Data Dictionary .................................................................................
14.9.5 Doublewrite Buffer ........................................................................................
14.9.6 Redo Log .....................................................................................................
14.9.7 Undo Logs ...................................................................................................
14.10 InnoDB Locking and Transaction Model ..................................................................
14.10.1 InnoDB Locking ..........................................................................................
14.10.2 InnoDB Transaction Model ..........................................................................
14.10.3 Locks Set by Different SQL Statements in InnoDB ........................................
14.10.4 Phantom Rows ...........................................................................................
14.10.5 Deadlocks in InnoDB ..................................................................................
14.11 InnoDB Configuration .............................................................................................
14.11.1 InnoDB Startup Configuration ......................................................................
14.11.2 InnoDB Buffer Pool Configuration ................................................................
14.11.3 Configuring the Memory Allocator for InnoDB ...............................................
14.11.4 Configuring Thread Concurrency for InnoDB ................................................
14.11.5 Configuring the Number of Background InnoDB I/O Threads .........................
1509
1510
1510
1512
1516
1517
1533
1533
1550
1559
1562
1567
1613
1621
1621
1621
1622
1624
1627
1628
1630
1631
1631
1632
1632
1633
1633
1635
1636
1636
1636
1638
1639
1639
1640
1643
1646
1646
1647
1647
1666
1667
1673
1673
1673
1674
1675
1675
1679
1687
1690
1690
1693
1693
1698
1701
1702
1703
xiii
MySQL 5.5 Reference Manual
14.11.6 Using Asynchronous I/O on Linux ................................................................
14.11.7 Configuring the InnoDB Master Thread I/O Rate ...........................................
14.11.8 Configuring Spin Lock Polling ......................................................................
14.11.9 Configuring InnoDB Purge Scheduling .........................................................
14.11.10 Configuring Optimizer Statistics for InnoDB ................................................
14.12 InnoDB Table Compression ...................................................................................
14.12.1 Overview of Table Compression ..................................................................
14.12.2 Enabling Compression for a Table ...............................................................
14.12.3 Tuning Compression for InnoDB Tables .......................................................
14.12.4 Monitoring InnoDB Table Compression at Runtime .......................................
14.12.5 How Compression Works for InnoDB Tables ................................................
14.12.6 SQL Compression Syntax Warnings and Errors ............................................
14.13 InnoDB File-Format Management ...........................................................................
14.13.1 Enabling File Formats .................................................................................
14.13.2 Verifying File Format Compatibility ...............................................................
14.13.3 Identifying the File Format in Use ................................................................
14.13.4 Downgrading the File Format ......................................................................
14.14 InnoDB Row Storage and Row Formats .................................................................
14.14.1 Overview of InnoDB Row Storage ...............................................................
14.14.2 Specifying the Row Format for a Table ........................................................
14.14.3 DYNAMIC and COMPRESSED Row Formats ..............................................
14.14.4 COMPACT and REDUNDANT Row Formats ................................................
14.15 InnoDB Disk I/O and File Space Management ........................................................
14.15.1 InnoDB Disk I/O ..........................................................................................
14.15.2 File Space Management .............................................................................
14.15.3 InnoDB Checkpoints ...................................................................................
14.15.4 Defragmenting a Table ................................................................................
14.15.5 Reclaiming Disk Space with TRUNCATE TABLE ..........................................
14.16 InnoDB Fast Index Creation ...................................................................................
14.16.1 Overview of Fast Index Creation ..................................................................
14.16.2 Examples of Fast Index Creation .................................................................
14.16.3 Implementation Details of Fast Index Creation ..............................................
14.16.4 Concurrency Considerations for Fast Index Creation .....................................
14.16.5 How Crash Recovery Works with Fast Index Creation ...................................
14.16.6 Limitations of Fast Index Creation ................................................................
14.17 InnoDB Startup Options and System Variables ........................................................
14.18 InnoDB INFORMATION_SCHEMA Tables ..............................................................
14.18.1 InnoDB INFORMATION_SCHEMA Tables about Compression ......................
14.18.2 InnoDB INFORMATION_SCHEMA Transaction and Locking Information ........
14.18.3 InnoDB INFORMATION_SCHEMA Buffer Pool Tables ..................................
14.19 InnoDB Integration with MySQL Performance Schema ............................................
14.19.1 Monitoring InnoDB Mutex Waits Using Performance Schema ........................
14.20 InnoDB Monitors ....................................................................................................
14.20.1 InnoDB Monitor Types ................................................................................
14.20.2 Enabling InnoDB Monitors ...........................................................................
14.20.3 InnoDB Standard Monitor and Lock Monitor Output ......................................
14.20.4 InnoDB Tablespace Monitor Output .............................................................
14.20.5 InnoDB Table Monitor Output ......................................................................
14.21 InnoDB Backup and Recovery ................................................................................
14.21.1 InnoDB Backup ...........................................................................................
14.21.2 InnoDB Recovery ........................................................................................
14.22 InnoDB and MySQL Replication .............................................................................
14.23 InnoDB Troubleshooting .........................................................................................
14.23.1 Troubleshooting InnoDB I/O Problems .........................................................
14.23.2 Forcing InnoDB Recovery ...........................................................................
14.23.3 Troubleshooting InnoDB Data Dictionary Operations .....................................
14.23.4 InnoDB Error Handling ................................................................................
15 Alternative Storage Engines ................................................................................................
xiv
1704
1704
1705
1705
1706
1707
1707
1708
1709
1712
1713
1716
1718
1718
1719
1722
1722
1722
1722
1723
1723
1724
1724
1725
1725
1726
1727
1727
1728
1728
1728
1729
1729
1730
1730
1731
1770
1770
1772
1777
1781
1783
1786
1786
1786
1788
1793
1795
1798
1799
1799
1801
1803
1804
1804
1806
1807
1809
MySQL 5.5 Reference Manual
15.1 Setting the Storage Engine ......................................................................................
15.2 Overview of MySQL Storage Engine Architecture ......................................................
15.2.1 Pluggable Storage Engine Architecture ..........................................................
15.2.2 The Common Database Server Layer ............................................................
15.3 The MyISAM Storage Engine ...................................................................................
15.3.1 MyISAM Startup Options ...............................................................................
15.3.2 Space Needed for Keys ................................................................................
15.3.3 MyISAM Table Storage Formats ....................................................................
15.3.4 MyISAM Table Problems ...............................................................................
15.4 The MEMORY Storage Engine ................................................................................
15.5 The CSV Storage Engine ........................................................................................
15.5.1 Repairing and Checking CSV Tables .............................................................
15.5.2 CSV Limitations ............................................................................................
15.6 The ARCHIVE Storage Engine .................................................................................
15.7 The BLACKHOLE Storage Engine ...........................................................................
15.8 The MERGE Storage Engine ...................................................................................
15.8.1 MERGE Table Advantages and Disadvantages ..............................................
15.8.2 MERGE Table Problems ...............................................................................
15.9 The FEDERATED Storage Engine ...........................................................................
15.9.1 FEDERATED Storage Engine Overview .........................................................
15.9.2 How to Create FEDERATED Tables ..............................................................
15.9.3 FEDERATED Storage Engine Notes and Tips ................................................
15.9.4 FEDERATED Storage Engine Resources .......................................................
15.10 The EXAMPLE Storage Engine ..............................................................................
15.11 Other Storage Engines ..........................................................................................
16 High Availability and Scalability ...........................................................................................
16.1 Using ZFS Replication .............................................................................................
16.1.1 Using ZFS for File System Replication ...........................................................
16.1.2 Configuring MySQL for ZFS Replication .........................................................
16.1.3 Handling MySQL Recovery with ZFS .............................................................
16.2 Using MySQL with memcached ................................................................................
16.2.1 Installing memcached ....................................................................................
16.2.2 Using memcached ........................................................................................
16.2.3 Developing a memcached Application ............................................................
16.2.4 Getting memcached Statistics ........................................................................
16.2.5 memcached FAQ ..........................................................................................
17 Replication .........................................................................................................................
17.1 Replication Configuration .........................................................................................
17.1.1 How to Set Up Replication ............................................................................
17.1.2 Replication Formats ......................................................................................
17.1.3 Replication and Binary Logging Options and Variables ....................................
17.1.4 Common Replication Administration Tasks .....................................................
17.2 Replication Implementation ......................................................................................
17.2.1 Replication Implementation Details ................................................................
17.2.2 Replication Relay and Status Logs ................................................................
17.2.3 How Servers Evaluate Replication Filtering Rules ...........................................
17.3 Replication Solutions ...............................................................................................
17.3.1 Using Replication for Backups .......................................................................
17.3.2 Using Replication with Different Master and Slave Storage Engines .................
17.3.3 Using Replication for Scale-Out .....................................................................
17.3.4 Replicating Different Databases to Different Slaves ........................................
17.3.5 Improving Replication Performance ................................................................
17.3.6 Switching Masters During Failover .................................................................
17.3.7 Setting Up Replication to Use Encrypted Connections ....................................
17.3.8 Semisynchronous Replication ........................................................................
17.4 Replication Notes and Tips ......................................................................................
17.4.1 Replication Features and Issues ....................................................................
17.4.2 Replication Compatibility Between MySQL Versions .......................................
1812
1813
1814
1814
1815
1818
1819
1820
1822
1824
1828
1829
1830
1830
1831
1834
1836
1837
1839
1839
1840
1842
1844
1844
1844
1845
1847
1848
1849
1850
1850
1851
1853
1871
1896
1904
1909
1910
1911
1920
1927
1969
1972
1973
1974
1977
1982
1982
1986
1987
1988
1989
1990
1992
1994
1999
1999
2024
xv
MySQL 5.5 Reference Manual
17.4.3 Upgrading a Replication Setup ......................................................................
17.4.4 Troubleshooting Replication ..........................................................................
17.4.5 How to Report Replication Bugs or Problems .................................................
18 MySQL NDB Cluster 7.2 ....................................................................................................
18.1 NDB Cluster Overview .............................................................................................
18.1.1 NDB Cluster Core Concepts ..........................................................................
18.1.2 NDB Cluster Nodes, Node Groups, Replicas, and Partitions ............................
18.1.3 NDB Cluster Hardware, Software, and Networking Requirements ....................
18.1.4 What is New in MySQL NDB Cluster 7.2 .......................................................
18.1.5 MySQL Server Using InnoDB Compared with NDB Cluster .............................
18.1.6 Known Limitations of NDB Cluster .................................................................
18.2 NDB Cluster Installation ...........................................................................................
18.2.1 Installing NDB Cluster on Linux .....................................................................
18.2.2 Installing NDB Cluster on Windows ...............................................................
18.2.3 Initial Configuration of NDB Cluster ...............................................................
18.2.4 Initial Startup of NDB Cluster ........................................................................
18.2.5 NDB Cluster Example with Tables and Data ..................................................
18.2.6 Safe Shutdown and Restart of NDB Cluster ...................................................
18.2.7 Upgrading and Downgrading NDB Cluster .....................................................
18.3 Configuration of NDB Cluster ...................................................................................
18.3.1 Quick Test Setup of NDB Cluster ..................................................................
18.3.2 Overview of NDB Cluster Configuration Parameters, Options, and Variables .....
18.3.3 NDB Cluster Configuration Files ....................................................................
18.3.4 Using High-Speed Interconnects with NDB Cluster .........................................
18.4 NDB Cluster Programs ............................................................................................
18.4.1 ndbd — The NDB Cluster Data Node Daemon ..............................................
18.4.2 ndbinfo_select_all — Select From ndbinfo Tables ..................................
18.4.3 ndbmtd — The NDB Cluster Data Node Daemon (Multi-Threaded) .................
18.4.4 ndb_mgmd — The NDB Cluster Management Server Daemon .........................
18.4.5 ndb_mgm — The NDB Cluster Management Client .........................................
18.4.6 ndb_blob_tool — Check and Repair BLOB and TEXT columns of NDB
Cluster Tables .........................................................................................................
18.4.7 ndb_config — Extract NDB Cluster Configuration Information ......................
18.4.8 ndb_cpcd — Automate Testing for NDB Development ...................................
18.4.9 ndb_delete_all — Delete All Rows from an NDB Table .............................
18.4.10 ndb_desc — Describe NDB Tables ............................................................
18.4.11 ndb_drop_index — Drop Index from an NDB Table ...................................
18.4.12 ndb_drop_table — Drop an NDB Table ...................................................
18.4.13 ndb_error_reporter — NDB Error-Reporting Utility .................................
18.4.14 ndb_index_stat — NDB Index Statistics Utility .........................................
18.4.15 ndb_move_data — NDB Data Copy Utility .................................................
18.4.16 ndb_print_backup_file — Print NDB Backup File Contents ...................
18.4.17 ndb_print_file — Print NDB Disk Data File Contents ..............................
18.4.18 ndb_print_schema_file — Print NDB Schema File Contents ..................
18.4.19 ndb_print_sys_file — Print NDB System File Contents .........................
18.4.20 ndb_redo_log_reader — Check and Print Content of Cluster Redo Log ....
18.4.21 ndb_restore — Restore an NDB Cluster Backup .......................................
18.4.22 ndb_select_all — Print Rows from an NDB Table ...................................
18.4.23 ndb_select_count — Print Row Counts for NDB Tables ...........................
18.4.24 ndb_show_tables — Display List of NDB Tables .......................................
18.4.25 ndb_size.pl — NDBCLUSTER Size Requirement Estimator ......................
18.4.26 ndb_waiter — Wait for NDB Cluster to Reach a Given Status .....................
18.4.27 Options Common to NDB Cluster Programs — Options Common to NDB
Cluster Programs ....................................................................................................
18.5 Management of NDB Cluster ...................................................................................
18.5.1 Summary of NDB Cluster Start Phases ..........................................................
18.5.2 Commands in the NDB Cluster Management Client ........................................
18.5.3 Online Backup of NDB Cluster ......................................................................
xvi
2025
2025
2027
2029
2032
2034
2036
2039
2040
2043
2046
2058
2060
2066
2075
2077
2078
2081
2082
2084
2084
2087
2103
2258
2259
2259
2266
2268
2269
2277
2278
2281
2289
2289
2290
2294
2295
2296
2297
2303
2305
2306
2306
2306
2307
2310
2330
2333
2334
2335
2338
2340
2344
2345
2347
2351
MySQL 5.5 Reference Manual
18.5.4 MySQL Server Usage for NDB Cluster ..........................................................
18.5.5 Performing a Rolling Restart of an NDB Cluster .............................................
18.5.6 Event Reports Generated in NDB Cluster ......................................................
18.5.7 NDB Cluster Log Messages ..........................................................................
18.5.8 NDB Cluster Single User Mode .....................................................................
18.5.9 Quick Reference: NDB Cluster SQL Statements .............................................
18.5.10 ndbinfo: The NDB Cluster Information Database ...........................................
18.5.11 NDB Cluster Security Issues .......................................................................
18.5.12 NDB Cluster Disk Data Tables ....................................................................
18.5.13 Adding NDB Cluster Data Nodes Online ......................................................
18.5.14 Distributed MySQL Privileges for NDB Cluster ..............................................
18.5.15 NDB API Statistics Counters and Variables ..................................................
18.6 NDB Cluster Replication ..........................................................................................
18.6.1 NDB Cluster Replication: Abbreviations and Symbols .....................................
18.6.2 General Requirements for NDB Cluster Replication ........................................
18.6.3 Known Issues in NDB Cluster Replication ......................................................
18.6.4 NDB Cluster Replication Schema and Tables .................................................
18.6.5 Preparing the NDB Cluster for Replication .....................................................
18.6.6 Starting NDB Cluster Replication (Single Replication Channel) ........................
18.6.7 Using Two Replication Channels for NDB Cluster Replication ..........................
18.6.8 Implementing Failover with NDB Cluster Replication .......................................
18.6.9 NDB Cluster Backups With NDB Cluster Replication .......................................
18.6.10 NDB Cluster Replication: Multi-Master and Circular Replication ......................
18.6.11 NDB Cluster Replication Conflict Resolution .................................................
18.7 NDB Cluster Release Notes .....................................................................................
19 Partitioning .........................................................................................................................
19.1 Overview of Partitioning in MySQL ...........................................................................
19.2 Partitioning Types ....................................................................................................
19.2.1 RANGE Partitioning ......................................................................................
19.2.2 LIST Partitioning ...........................................................................................
19.2.3 COLUMNS Partitioning .................................................................................
19.2.4 HASH Partitioning .........................................................................................
19.2.5 KEY Partitioning ...........................................................................................
19.2.6 Subpartitioning ..............................................................................................
19.2.7 How MySQL Partitioning Handles NULL ........................................................
19.3 Partition Management ..............................................................................................
19.3.1 Management of RANGE and LIST Partitions ..................................................
19.3.2 Management of HASH and KEY Partitions .....................................................
19.3.3 Maintenance of Partitions ..............................................................................
19.3.4 Obtaining Information About Partitions ...........................................................
19.4 Partition Pruning ......................................................................................................
19.5 Restrictions and Limitations on Partitioning ...............................................................
19.5.1 Partitioning Keys, Primary Keys, and Unique Keys .........................................
19.5.2 Partitioning Limitations Relating to Storage Engines ........................................
19.5.3 Partitioning Limitations Relating to Functions ..................................................
19.5.4 Partitioning and Table-Level Locking ..............................................................
20 Stored Programs and Views ...............................................................................................
20.1 Defining Stored Programs ........................................................................................
20.2 Using Stored Routines (Procedures and Functions) ...................................................
20.2.1 Stored Routine Syntax ..................................................................................
20.2.2 Stored Routines and MySQL Privileges .........................................................
20.2.3 Stored Routine Metadata ..............................................................................
20.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID() ....................
20.3 Using Triggers .........................................................................................................
20.3.1 Trigger Syntax and Examples ........................................................................
20.3.2 Trigger Metadata ..........................................................................................
20.4 Using the Event Scheduler ......................................................................................
20.4.1 Event Scheduler Overview ............................................................................
2355
2356
2358
2368
2383
2384
2386
2408
2415
2422
2433
2436
2447
2448
2448
2449
2456
2459
2460
2462
2463
2465
2471
2475
2484
2485
2487
2489
2491
2495
2498
2505
2508
2510
2513
2517
2518
2524
2525
2526
2528
2532
2538
2541
2542
2544
2545
2546
2547
2547
2548
2548
2549
2549
2550
2553
2553
2554
xvii
MySQL 5.5 Reference Manual
20.4.2 Event Scheduler Configuration ......................................................................
20.4.3 Event Syntax ................................................................................................
20.4.4 Event Metadata ............................................................................................
20.4.5 Event Scheduler Status .................................................................................
20.4.6 The Event Scheduler and MySQL Privileges ..................................................
20.5 Using Views ............................................................................................................
20.5.1 View Syntax .................................................................................................
20.5.2 View Processing Algorithms ..........................................................................
20.5.3 Updatable and Insertable Views ....................................................................
20.5.4 The View WITH CHECK OPTION Clause ......................................................
20.5.5 View Metadata ..............................................................................................
20.6 Access Control for Stored Programs and Views ........................................................
20.7 Binary Logging of Stored Programs ..........................................................................
21 INFORMATION_SCHEMA Tables .......................................................................................
21.1 Introduction .............................................................................................................
21.2 The INFORMATION_SCHEMA CHARACTER_SETS Table .......................................
21.3 The INFORMATION_SCHEMA COLLATIONS Table .................................................
21.4 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY
Table ..............................................................................................................................
21.5 The INFORMATION_SCHEMA COLUMNS Table .....................................................
21.6 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table ..................................
21.7 The INFORMATION_SCHEMA ENGINES Table .......................................................
21.8 The INFORMATION_SCHEMA EVENTS Table .........................................................
21.9 The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables ....
21.10 The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES
Tables .............................................................................................................................
21.11 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table ................................
21.12 The INFORMATION_SCHEMA PARAMETERS Table .............................................
21.13 The INFORMATION_SCHEMA PARTITIONS Table ................................................
21.14 The INFORMATION_SCHEMA PLUGINS Table ......................................................
21.15 The INFORMATION_SCHEMA PROCESSLIST Table .............................................
21.16 The INFORMATION_SCHEMA PROFILING Table ..................................................
21.17 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table ....................
21.18 The INFORMATION_SCHEMA ROUTINES Table ...................................................
21.19 The INFORMATION_SCHEMA SCHEMATA Table ..................................................
21.20 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table .................................
21.21 The INFORMATION_SCHEMA STATISTICS Table .................................................
21.22 The INFORMATION_SCHEMA TABLES Table .......................................................
21.23 The INFORMATION_SCHEMA TABLESPACES Table ............................................
21.24 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table ................................
21.25 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table ....................................
21.26 The INFORMATION_SCHEMA TRIGGERS Table ...................................................
21.27 The INFORMATION_SCHEMA USER_PRIVILEGES Table .....................................
21.28 The INFORMATION_SCHEMA VIEWS Table .........................................................
21.29 INFORMATION_SCHEMA InnoDB Tables ..............................................................
21.29.1 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table ...................
21.29.2 The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table ..........
21.29.3 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table ......
21.29.4 The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET
Tables .....................................................................................................................
21.29.5 The INFORMATION_SCHEMA INNODB_CMPMEM and
INNODB_CMPMEM_RESET Tables ........................................................................
21.29.6 The INFORMATION_SCHEMA INNODB_LOCKS Table ................................
21.29.7 The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table ......................
21.29.8 The INFORMATION_SCHEMA INNODB_TRX Table ....................................
21.30 INFORMATION_SCHEMA NDB Cluster Tables .......................................................
21.30.1 The INFORMATION_SCHEMA FILES Table ................................................
21.30.2 The INFORMATION_SCHEMA ndb_transid_mysql_connection_map Table ....
xviii
2555
2557
2557
2558
2558
2561
2561
2562
2563
2565
2565
2565
2567
2575
2576
2578
2579
2580
2580
2582
2583
2584
2587
2588
2588
2589
2590
2594
2595
2596
2597
2598
2600
2601
2602
2603
2607
2607
2608
2609
2611
2611
2613
2613
2616
2619
2622
2623
2624
2626
2627
2629
2630
2635
MySQL 5.5 Reference Manual
21.31 INFORMATION_SCHEMA Thread Pool Tables .......................................................
21.31.1 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATE Table ............
21.31.2 The INFORMATION_SCHEMA TP_THREAD_GROUP_STATS Table ............
21.31.3 The INFORMATION_SCHEMA TP_THREAD_STATE Table .........................
21.32 Extensions to SHOW Statements ...........................................................................
22 MySQL Performance Schema .............................................................................................
22.1 Performance Schema Quick Start ............................................................................
22.2 Performance Schema Build Configuration .................................................................
22.3 Performance Schema Startup Configuration ..............................................................
22.4 Performance Schema Runtime Configuration ............................................................
22.4.1 Performance Schema Event Timing ...............................................................
22.4.2 Performance Schema Event Filtering .............................................................
22.4.3 Event Pre-Filtering ........................................................................................
22.4.4 Naming Instruments or Consumers for Filtering Operations .............................
22.4.5 Determining What Is Instrumented .................................................................
22.5 Performance Schema Queries .................................................................................
22.6 Performance Schema Instrument Naming Conventions ..............................................
22.7 Performance Schema Status Monitoring ...................................................................
22.8 Performance Schema Tables for Current and Historical Events ..................................
22.9 Performance Schema General Table Characteristics .................................................
22.10 Performance Schema Table Descriptions ................................................................
22.10.1 Performance Schema Table Index ...............................................................
22.10.2 Performance Schema Setup Tables .............................................................
22.10.3 Performance Schema Instance Tables .........................................................
22.10.4 Performance Schema Wait Event Tables .....................................................
22.10.5 Performance Schema Summary Tables .......................................................
22.10.6 Performance Schema Miscellaneous Tables .................................................
22.11 Performance Schema Option and Variable Reference .............................................
22.12 Performance Schema System Variables .................................................................
22.13 Performance Schema Status Variables ...................................................................
22.14 Performance Schema and Plugins ..........................................................................
22.15 Using the Performance Schema to Diagnose Problems ...........................................
23 Connectors and APIs .........................................................................................................
23.1 MySQL Connector/C ................................................................................................
23.2 MySQL Connector/C++ ............................................................................................
23.3 MySQL Connector/J ................................................................................................
23.4 MySQL Connector/NET ...........................................................................................
23.5 MySQL Connector/ODBC .........................................................................................
23.6 MySQL Connector/Python ........................................................................................
23.7 libmysqld, the Embedded MySQL Server Library .......................................................
23.7.1 Compiling Programs with libmysqld ................................................................
23.7.2 Restrictions When Using the Embedded MySQL Server ..................................
23.7.3 Options with the Embedded Server ...............................................................
23.7.4 Embedded Server Examples .........................................................................
23.8 MySQL C API .........................................................................................................
23.8.1 MySQL C API Implementations .....................................................................
23.8.2 Simultaneous MySQL Server and Connector/C Installations ............................
23.8.3 Example C API Client Programs ....................................................................
23.8.4 Building and Running C API Client Programs .................................................
23.8.5 C API Data Structures ..................................................................................
23.8.6 C API Function Overview ..............................................................................
23.8.7 C API Function Descriptions ..........................................................................
23.8.8 C API Prepared Statements ..........................................................................
23.8.9 C API Prepared Statement Data Structures ....................................................
23.8.10 C API Prepared Statement Function Overview .............................................
23.8.11 C API Prepared Statement Function Descriptions .........................................
23.8.12 C API Threaded Function Descriptions .........................................................
23.8.13 C API Embedded Server Function Descriptions ............................................
2636
2637
2638
2640
2641
2643
2644
2649
2650
2651
2652
2655
2656
2657
2658
2659
2659
2660
2663
2664
2665
2666
2666
2668
2671
2675
2678
2679
2680
2686
2688
2688
2691
2694
2694
2694
2694
2694
2695
2695
2695
2696
2696
2697
2700
2701
2702
2703
2703
2707
2712
2717
2767
2767
2773
2776
2799
2800
xix
MySQL 5.5 Reference Manual
23.8.14 C API Client Plugin Functions .....................................................................
23.8.15 C API Encrypted Connection Support ..........................................................
23.8.16 C API Multiple Statement Execution Support ................................................
23.8.17 C API Prepared Statement Handling of Date and Time Values .......................
23.8.18 C API Prepared CALL Statement Support ....................................................
23.8.19 C API Prepared Statement Problems ...........................................................
23.8.20 C API Automatic Reconnection Control ........................................................
23.8.21 C API Common Issues ................................................................................
23.9 MySQL PHP API .....................................................................................................
23.10 MySQL Perl API ....................................................................................................
23.11 MySQL Python API ................................................................................................
23.12 MySQL Ruby APIs ................................................................................................
23.12.1 The MySQL/Ruby API .................................................................................
23.12.2 The Ruby/MySQL API .................................................................................
23.13 MySQL Tcl API .....................................................................................................
23.14 MySQL Eiffel Wrapper ...........................................................................................
24 Extending MySQL ..............................................................................................................
24.1 MySQL Internals ......................................................................................................
24.1.1 MySQL Threads ...........................................................................................
24.1.2 The MySQL Test Suite .................................................................................
24.2 The MySQL Plugin API ............................................................................................
24.2.1 Types of Plugins ...........................................................................................
24.2.2 Plugin API Characteristics .............................................................................
24.2.3 Plugin API Components ................................................................................
24.2.4 Writing Plugins .............................................................................................
24.3 MySQL Services for Plugins ....................................................................................
24.4 Adding New Functions to MySQL .............................................................................
24.4.1 Features of the User-Defined Function Interface .............................................
24.4.2 Adding a New User-Defined Function ............................................................
24.4.3 Adding a New Native Function ......................................................................
24.5 Debugging and Porting MySQL ................................................................................
24.5.1 Debugging a MySQL Server ..........................................................................
24.5.2 Debugging a MySQL Client ...........................................................................
24.5.3 The DBUG Package .....................................................................................
25 MySQL Enterprise Edition ...................................................................................................
25.1 MySQL Enterprise Monitor Overview ........................................................................
25.2 MySQL Enterprise Backup Overview ........................................................................
25.3 MySQL Enterprise Security Overview .......................................................................
25.4 MySQL Enterprise Encryption Overview ...................................................................
25.5 MySQL Enterprise Audit Overview ...........................................................................
25.6 MySQL Enterprise Firewall Overview ........................................................................
25.7 MySQL Enterprise Thread Pool Overview .................................................................
25.8 MySQL Enterprise Data Masking and De-Identification Overview ................................
26 MySQL Workbench ............................................................................................................
A MySQL 5.5 Frequently Asked Questions ...............................................................................
A.1 MySQL 5.5 FAQ: General .........................................................................................
A.2 MySQL 5.5 FAQ: Storage Engines ............................................................................
A.3 MySQL 5.5 FAQ: Server SQL Mode ..........................................................................
A.4 MySQL 5.5 FAQ: Stored Procedures and Functions ...................................................
A.5 MySQL 5.5 FAQ: Triggers .........................................................................................
A.6 MySQL 5.5 FAQ: Views ............................................................................................
A.7 MySQL 5.5 FAQ: INFORMATION_SCHEMA ..............................................................
A.8 MySQL 5.5 FAQ: Migration .......................................................................................
A.9 MySQL 5.5 FAQ: Security .........................................................................................
A.10 MySQL FAQ: MySQL 5.5 and NDB Cluster ..............................................................
A.11 MySQL 5.5 FAQ: MySQL Chinese, Japanese, and Korean Character Sets .................
A.12 MySQL 5.5 FAQ: Connectors & APIs .......................................................................
A.13 MySQL 5.5 FAQ: Replication ...................................................................................
xx
2801
2804
2806
2808
2809
2813
2813
2814
2816
2816
2817
2817
2817
2817
2817
2817
2819
2819
2819
2820
2821
2821
2824
2825
2826
2866
2867
2868
2868
2878
2879
2879
2886
2887
2891
2891
2892
2893
2893
2893
2893
2894
2894
2895
2897
2897
2898
2899
2900
2903
2906
2906
2907
2907
2908
2921
2933
2933
MySQL 5.5 Reference Manual
A.14 MySQL 5.5 FAQ: MySQL Enterprise Thread Pool .....................................................
A.15 MySQL 5.5 FAQ: InnoDB Change Buffer ..................................................................
A.16 MySQL 5.5 FAQ: Virtualization Support ....................................................................
B Errors, Error Codes, and Common Problems ........................................................................
B.1 Error Message Components ......................................................................................
B.2 Error Information Interfaces .......................................................................................
B.3 Server Error Message Reference ...............................................................................
B.4 Client Error Message Reference ................................................................................
B.5 Problems and Common Errors ..................................................................................
B.5.1 How to Determine What Is Causing a Problem ................................................
B.5.2 Common Errors When Using MySQL Programs ...............................................
B.5.3 Administration-Related Issues .........................................................................
B.5.4 Query-Related Issues .....................................................................................
B.5.5 Optimizer-Related Issues ................................................................................
B.5.6 Table Definition-Related Issues .......................................................................
B.5.7 Known Issues in MySQL ................................................................................
C Restrictions and Limits .........................................................................................................
C.1 Restrictions on Stored Programs ...............................................................................
C.2 Restrictions on Condition Handling ............................................................................
C.3 Restrictions on Server-Side Cursors ..........................................................................
C.4 Restrictions on Subqueries ........................................................................................
C.5 Restrictions on Views ................................................................................................
C.6 Restrictions on XA Transactions ................................................................................
C.7 Restrictions on Character Sets ..................................................................................
C.8 Restrictions on Performance Schema ........................................................................
C.9 Restrictions on Pluggable Authentication ....................................................................
C.10 Limits in MySQL .....................................................................................................
C.10.1 Limits on Joins .............................................................................................
C.10.2 Limits on Number of Databases and Tables ..................................................
C.10.3 Limits on Table Size .....................................................................................
C.10.4 Limits on Table Column Count and Row Size ................................................
C.10.5 Limits Imposed by .frm File Structure ............................................................
C.10.6 Windows Platform Limitations .......................................................................
D Indexes ...............................................................................................................................
MySQL Glossary .....................................................................................................................
2937
2938
2940
2941
2941
2942
2943
2998
3002
3002
3003
3016
3023
3030
3031
3032
3035
3035
3039
3039
3039
3041
3042
3043
3043
3044
3045
3045
3045
3046
3047
3050
3050
3053
3569
xxi
xxii
Preface and Legal Notices
This is the Reference Manual for the MySQL Database System, version 5.5, through release 5.5.63.
Differences between minor versions of MySQL 5.5 are noted in the present text with reference to
release numbers (5.5.x). For license information, see the Legal Notices.
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.5 and previous versions. If you are using an earlier
release of the MySQL software, please refer to the appropriate manual. For example, MySQL 5.1
Reference Manual covers the 5.1 series of MySQL software releases.
If you are using MySQL 5.6, please refer to the MySQL 5.6 Reference Manual.
Licensing information—MySQL 5.5.
This product may include third-party software, used under
license. If you are using a Commercial release of MySQL 5.5, see the MySQL 5.5 Commercial Release
License Information User Manual for licensing information, including licensing information relating to
third-party software that may be included in this Commercial release. If you are using a Community
release of MySQL 5.5, see the MySQL 5.5 Community Release License Information User Manual
for licensing information, including licensing information relating to third-party software that may be
included in this Community release.
Licensing information—MySQL NDB Cluster 7.2.
This product may include third-party software,
used under license. If you are using a Commercial release of NDB Cluster 7.2, see the MySQL NDB
Cluster 7.2 Commercial Release License Information User Manual for licensing information relating
to third-party software that may be included in this Commercial release. If you are using a Community
release of NDB Cluster 7.2, see the MySQL NDB Cluster 7.2 Community Release License Information
User Manual for licensing information relating to third-party software that may be included in this
Community release.
Legal Notices
Copyright © 1997, 2018, 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 errorfree. If you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone
licensing it on behalf of the U.S. Government, then the following notice is applicable:
U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated
software, any programs installed on the hardware, and/or documentation, delivered to U.S.
Government end users are "commercial computer software" pursuant to the applicable Federal
Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication,
disclosure, modification, and adaptation of the programs, including any operating system, integrated
software, any programs installed on the hardware, and/or documentation, shall be subject to license
terms and license restrictions applicable to the programs. No other rights are granted to the U.S.
Government.
This software or hardware 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 that may create a risk of personal injury. If you use this software or hardware in dangerous
applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and
xxiii
Documentation Accessibility
other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any
damages caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be
trademarks of their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC
trademarks are used under license and are trademarks or registered trademarks of SPARC
International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or
registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information about 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 unless otherwise set forth in an applicable agreement between you and Oracle. 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, except as set forth in an applicable
agreement between you and Oracle.
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.
Documentation Accessibility
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program
website at
http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Access to Oracle Support
Oracle customers that have purchased support have access to electronic support through My Oracle
Support. For information, visit
http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?
ctx=acc&id=trs if you are hearing impaired.
xxiv
Chapter 1 General Information
Table of Contents
1.1 About This Manual .................................................................................................................. 2
1.2 Typographical and Syntax Conventions ................................................................................... 2
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 ....................................................................................... 5
1.3.3 History of MySQL ......................................................................................................... 8
1.4 What Is New in MySQL 5.5 ..................................................................................................... 9
1.5 MySQL Information Sources .................................................................................................. 17
1.5.1 MySQL Websites ....................................................................................................... 17
1.5.2 MySQL Mailing Lists .................................................................................................. 17
1.5.3 MySQL Community Support at the MySQL Forums ...................................................... 20
1.5.4 MySQL Community Support on Internet Relay Chat (IRC) ............................................ 20
1.5.5 MySQL Enterprise ...................................................................................................... 20
1.6 How to Report Bugs or Problems .......................................................................................... 21
1.7 MySQL Standards Compliance .............................................................................................. 25
1.7.1 MySQL Extensions to Standard SQL ........................................................................... 26
1.7.2 MySQL Differences from Standard SQL ...................................................................... 29
1.7.3 How MySQL Deals with Constraints ............................................................................ 31
1.8 Credits .................................................................................................................................. 34
1.8.1 Contributors to MySQL ............................................................................................... 34
1.8.2 Documenters and translators ...................................................................................... 38
1.8.3 Packages that support MySQL .................................................................................... 39
1.8.4 Tools that were used to create MySQL ....................................................................... 40
1.8.5 Supporters of MySQL ................................................................................................. 40
The MySQL™ software delivers a very fast, multithreaded, multi-user, and robust SQL (Structured
Query Language) database server. MySQL Server is intended for mission-critical, heavy-load
production systems as well as for embedding into mass-deployed software. 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 by Customer without Oracle's express written authorization. Other
names may be trademarks of their respective owners.
The MySQL software is Dual Licensed. Users can choose to use the MySQL software as an Open
Source product under the terms of the GNU General Public License (http://www.fsf.org/licenses/) or
can purchase a standard commercial license from Oracle. See http://www.mysql.com/company/legal/
licensing/ for more information on our licensing policies.
The following list describes some sections of particular interest in this manual:
• For a discussion of MySQL Database Server capabilities, see Section 1.3.2, “The Main Features of
MySQL”.
• For an overview of new MySQL features, see Section 1.4, “What Is New in MySQL 5.5”. For
information about the changes in each version, see the Release Notes.
• For installation instructions, see Chapter 2, Installing and Upgrading MySQL. For information about
upgrading MySQL, see Section 2.11.1, “Upgrading MySQL”.
• For a tutorial introduction to the MySQL Database Server, see Chapter 3, Tutorial.
• For information about configuring and administering MySQL Server, see Chapter 5, MySQL Server
Administration.
• For information about security in MySQL, see Chapter 6, Security.
1
About This Manual
• For information about setting up replication servers, see Chapter 17, Replication.
• For information about MySQL Enterprise, the commercial MySQL release with advanced features
and management tools, see Chapter 25, MySQL Enterprise Edition.
• For answers to a number of questions that are often asked concerning the MySQL Database Server
and its capabilities, see Appendix A, MySQL 5.5 Frequently Asked Questions.
• For a history of new features and bug fixes, see the Release Notes.
Important
To report problems or bugs, please use the instructions at Section 1.6, “How
to Report Bugs or Problems”. If you find a sensitive security bug in MySQL
Server, please let us know immediately by sending an email message to
. Exception: Support customers should report
all problems, including security bugs, to Oracle Support.
1.1 About This Manual
This is the Reference Manual for the MySQL Database System, version 5.5, through release 5.5.63.
Differences between minor versions of MySQL 5.5 are noted in the present text with reference to
release numbers (5.5.x). For license information, see the Legal Notices.
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.5 and previous versions. If you are using an earlier
release of the MySQL software, please refer to the appropriate manual. For example, MySQL 5.1
Reference Manual covers the 5.1 series of MySQL software releases.
If you are using MySQL 5.6, please refer to the MySQL 5.6 Reference Manual.
Because this manual serves as a reference, it does not provide general instruction on SQL or relational
database concepts. It also does not teach you how to use your operating system or command-line
interpreter.
The MySQL Database Software is under constant development, and the Reference Manual is updated
frequently as well. The most recent version of the manual is available online in searchable form at
https://dev.mysql.com/doc/. Other formats also are available there, including HTML, PDF, and EPUB
versions.
The Reference Manual source files are written in DocBook XML format. The HTML version and other
formats are produced automatically, primarily using the DocBook XSL stylesheets. For information
about DocBook, see http://docbook.org/
If you have questions about using MySQL, you can ask them using our mailing lists or forums. See
Section 1.5.2, “MySQL Mailing Lists”, and Section 1.5.3, “MySQL Community Support at the MySQL
Forums”. If you have suggestions concerning additions or corrections to the manual itself, please send
them to the http://www.mysql.com/company/contact/.
This manual was originally written by David Axmark and Michael “Monty” Widenius. It is maintained
by the MySQL Documentation Team, consisting of Chris Cole, Paul DuBois, Margaret Fisher, Edward
Gilmore, Stefan Hinz, David Moss, Philip Olson, Daniel Price, Daniel So, and Jon Stephens.
1.2 Typographical and Syntax Conventions
This manual uses certain typographical conventions:
• Text in this style is used for SQL statements; database, table, and column names; program
listings and source code; and environment variables. Example: “To reload the grant tables, use the
FLUSH PRIVILEGES statement.”
2
Typographical and Syntax Conventions
• Text in this style indicates input that you type in examples.
• Text in this style indicates the names of executable programs and scripts, examples being
mysql (the MySQL command-line client program) and mysqld (the MySQL server executable).
• Text in this style is used for variable input for which you should substitute a value of your
own choosing.
• Text in this style is used for emphasis.
• Text in this style is used in table headings and to convey especially strong emphasis.
• Text in this style is used to indicate a program option that affects how the program is
executed, or that supplies information that is needed for the program to function in a certain way.
Example: “The --host option (short form -h) tells the mysql client program the hostname or IP
address of the MySQL server that it should connect to”.
• File names and directory names are written like this: “The global my.cnf file is located in the /etc
directory.”
• Character sequences are written like this: “To specify a wildcard, use the ‘%’ character.”
When commands are shown that are meant to be executed from within a particular program, the
prompt shown preceding the command indicates which command to use. For example, shell>
indicates a command that you execute from your login shell, root-shell> is similar but should be
executed as root, and mysql> indicates a statement that you execute from the mysql client program:
shell> type a shell command here
root-shell> type a shell command as root here
mysql> type a mysql statement here
In some areas different systems may be distinguished from each other to show that commands should
be executed in two different environments. For example, while working with replication the commands
might be prefixed with master and slave:
master> type a mysql command on the replication master here
slave> type a mysql command on the replication slave here
The “shell” is your command interpreter. On Unix, this is typically a program such as sh, csh, or bash.
On Windows, the equivalent program is command.com or cmd.exe, typically run in a console window.
When you enter a command or statement shown in an example, do not type the prompt shown in the
example.
Database, table, and column names must often be substituted into statements. To indicate that such
substitution is necessary, this manual uses db_name, tbl_name, and col_name. For example, you
might see a statement like this:
mysql> SELECT col_name FROM db_name.tbl_name;
This means that if you were to enter a similar statement, you would supply your own database, table,
and column names, perhaps like this:
mysql> SELECT author_name FROM biblio_db.author_list;
SQL keywords are not case-sensitive and may be written in any lettercase. This manual uses
uppercase.
In syntax descriptions, square brackets (“[” and “]”) indicate optional words or clauses. For example, in
the following statement, IF EXISTS is optional:
3
Overview of the MySQL Database Management System
DROP TABLE [IF EXISTS] tbl_name
When a syntax element consists of a number of alternatives, the alternatives are separated by vertical
bars (“|”). When one member from a set of choices may be chosen, the alternatives are listed within
square brackets (“[” and “]”):
TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
When one member from a set of choices must be chosen, the alternatives are listed within braces (“{”
and “}”):
{DESCRIBE | DESC} tbl_name [col_name | wild]
An ellipsis (...) indicates the omission of a section of a statement, typically to provide a shorter
version of more complex syntax. For example, SELECT ... INTO OUTFILE is shorthand for the form
of SELECT statement that has an INTO OUTFILE clause following other parts of the statement.
An ellipsis can also indicate that the preceding syntax element of a statement may be repeated. In
the following example, multiple reset_option values may be given, with each of those after the first
preceded by commas:
RESET reset_option [,reset_option] ...
Commands for setting shell variables are shown using Bourne shell syntax. For example, the sequence
to set the CC environment variable and run the configure command looks like this in Bourne shell
syntax:
shell> CC=gcc ./configure
If you are using csh or tcsh, you must issue commands somewhat differently:
shell> setenv CC gcc
shell> ./configure
1.3 Overview of the MySQL Database Management System
1.3.1 What is MySQL?
MySQL, the most popular Open Source SQL database management system, is developed, distributed,
and supported by Oracle Corporation.
The MySQL website (http://www.mysql.com/) provides the latest information about MySQL software.
• MySQL is a database management system.
A database is a structured collection of data. It may be anything from a simple shopping list to
a picture gallery or the vast amounts of information in a corporate network. To add, access, and
process data stored in a computer database, you need a database management system such
as MySQL Server. Since computers are very good at handling large amounts of data, database
management systems play a central role in computing, as standalone utilities, or as parts of other
applications.
• MySQL databases are relational.
A relational database stores data in separate tables rather than putting all the data in one big
storeroom. The database structures are organized into physical files optimized for speed. The
logical model, with objects such as databases, tables, views, rows, and columns, offers a flexible
4
The Main Features of MySQL
programming environment. You set up rules governing the relationships between different data
fields, such as one-to-one, one-to-many, unique, required or optional, and “pointers” between
different tables. The database enforces these rules, so that with a well-designed database, your
application never sees inconsistent, duplicate, orphan, out-of-date, or missing data.
The SQL part of “MySQL” stands for “Structured Query Language”. SQL is the most common
standardized language used to access databases. Depending on your programming environment,
you might enter SQL directly (for example, to generate reports), embed SQL statements into code
written in another language, or use a language-specific API that hides the SQL syntax.
SQL is defined by the ANSI/ISO SQL Standard. The SQL standard has been evolving since 1986
and several versions exist. In this manual, “SQL-92” refers to the standard released in 1992,
“SQL:1999” refers to the standard released in 1999, and “SQL:2003” refers to the current version
of the standard. We use the phrase “the SQL standard” to mean the current version of the SQL
Standard at any time.
• MySQL software is Open Source.
Open Source means that it is possible for anyone to use and modify the software. Anybody can
download the MySQL software from the Internet and use it without paying anything. If you wish, you
may study the source code and change it to suit your needs. The MySQL software uses the GPL
(GNU General Public License), http://www.fsf.org/licenses/, to define what you may and may not do
with the software in different situations. If you feel uncomfortable with the GPL or need to embed
MySQL code into a commercial application, you can buy a commercially licensed version from us.
See the MySQL Licensing Overview for more information (http://www.mysql.com/company/legal/
licensing/).
• The MySQL Database Server is very fast, reliable, scalable, and easy to use.
If that is what you are looking for, you should give it a try. MySQL Server can run comfortably on a
desktop or laptop, alongside your other applications, web servers, and so on, requiring little or no
attention. If you dedicate an entire machine to MySQL, you can adjust the settings to take advantage
of all the memory, CPU power, and I/O capacity available. MySQL can also scale up to clusters of
machines, networked together.
MySQL Server was originally developed to handle large databases much faster than existing
solutions and has been successfully used in highly demanding production environments for several
years. Although under constant development, MySQL Server today offers a rich and useful set of
functions. Its connectivity, speed, and security make MySQL Server highly suited for accessing
databases on the Internet.
• MySQL Server works in client/server or embedded systems.
The MySQL Database Software is a client/server system that consists of a multithreaded SQL server
that supports different back ends, several different client programs and libraries, administrative tools,
and a wide range of application programming interfaces (APIs).
We also provide MySQL Server as an embedded multithreaded library that you can link into your
application to get a smaller, faster, easier-to-manage standalone product.
• A large amount of contributed MySQL software is available.
MySQL Server has a practical set of features developed in close cooperation with our users. It is
very likely that your favorite application or language supports the MySQL Database Server.
The official way to pronounce “MySQL” is “My Ess Que Ell” (not “my sequel”), but we do not mind if you
pronounce it as “my sequel” or in some other localized way.
1.3.2 The Main Features of MySQL
5
The Main Features of MySQL
This section describes some of the important characteristics of the MySQL Database Software. In most
respects, the roadmap applies to all versions of MySQL. For information about features as they are
introduced into MySQL on a series-specific basis, see the “In a Nutshell” section of the appropriate
Manual:
• MySQL 8.0: What Is New in MySQL 8.0
• MySQL 5.7: What Is New in MySQL 5.7
• MySQL 5.6: What Is New in MySQL 5.6
• MySQL 5.5: Section 1.4, “What Is New in MySQL 5.5”
Internals and Portability
• Written in C and C++.
• Tested with a broad range of different compilers.
• Works on many different platforms. See https://www.mysql.com/support/supportedplatforms/
database.html.
• For portability, uses CMake in MySQL 5.5 and up. Previous series use GNU Automake, Autoconf,
and Libtool.
• Tested with Purify (a commercial memory leakage detector) as well as with Valgrind, a GPL tool
(http://developer.kde.org/~sewardj/).
• Uses multi-layered server design with independent modules.
• Designed to be fully multithreaded using kernel threads, to easily use multiple CPUs if they are
available.
• Provides transactional and nontransactional storage engines.
• Uses very fast B-tree disk tables (MyISAM) with index compression.
• Designed to make it relatively easy to add other storage engines. This is useful if you want to provide
an SQL interface for an in-house database.
• Uses a very fast thread-based memory allocation system.
• Executes very fast joins using an optimized nested-loop join.
• Implements in-memory hash tables, which are used as temporary tables.
• Implements SQL functions using a highly optimized class library that should be as fast as possible.
Usually there is no memory allocation at all after query initialization.
• Provides the server as a separate program for use in a client/server networked environment, and as
a library that can be embedded (linked) into standalone applications. Such applications can be used
in isolation or in environments where no network is available.
Data Types
• Many data types: signed/unsigned integers 1, 2, 3, 4, and 8 bytes long, FLOAT, DOUBLE, CHAR,
VARCHAR, BINARY, VARBINARY, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET,
ENUM, and OpenGIS spatial types. See Chapter 11, Data Types.
• Fixed-length and variable-length string types.
Statements and Functions
• Full operator and function support in the SELECT list and WHERE clause of queries. For example:
6
The Main Features of MySQL
mysql> SELECT CONCAT(first_name, ' ', last_name)
-> FROM citizen
-> WHERE income/dependents > 10000 AND age > 30;
• Full support for SQL GROUP BY and ORDER BY clauses. Support for group functions (COUNT(),
AVG(), STD(), SUM(), MAX(), MIN(), and GROUP_CONCAT()).
• Support for LEFT OUTER JOIN and RIGHT OUTER JOIN with both standard SQL and ODBC
syntax.
• Support for aliases on tables and columns as required by standard SQL.
• Support for DELETE, INSERT, REPLACE, and UPDATE to return the number of rows that were
changed (affected), or to return the number of rows matched instead by setting a flag when
connecting to the server.
• Support for MySQL-specific SHOW statements that retrieve information about databases, storage
engines, tables, and indexes. Support for the INFORMATION_SCHEMA database, implemented
according to standard SQL.
• An EXPLAIN statement to show how the optimizer resolves a query.
• Independence of function names from table or column names. For example, ABS is a valid column
name. The only restriction is that for a function call, no spaces are permitted between the function
name and the “(” that follows it. See Section 9.3, “Keywords and Reserved Words”.
• You can refer to tables from different databases in the same statement.
Security
• A privilege and password system that is very flexible and secure, and that enables host-based
verification.
• Password security by encryption of all password traffic when you connect to a server.
Scalability and Limits
• Support for large databases. We use MySQL Server with databases that contain 50 million records.
We also know of users who use MySQL Server with 200,000 tables and about 5,000,000,000 rows.
• Support for up to 64 indexes per table. Each index may consist of 1 to 16 columns or parts of
columns. The maximum index width for InnoDB tables is either 767 bytes or 3072 bytes. See
Section 14.9.1.7, “Limits on InnoDB Tables”. The maximum index width for MyISAM tables is 1000
bytes. See Section 15.3, “The MyISAM Storage Engine”. An index may use a prefix of a column for
CHAR, VARCHAR, BLOB, or TEXT column types.
Connectivity
• Clients can connect to MySQL Server using several protocols:
• Clients can connect using TCP/IP sockets on any platform.
• On Windows systems, clients can connect using named pipes if the server is started with the
--enable-named-pipe option. Windows servers also support shared-memory connections if
started with the --shared-memory option. Clients can connect through shared memory by using
the --protocol=memory option.
• On Unix systems, clients can connect using Unix domain socket files.
• MySQL client programs can be written in many languages. A client library written in C is available for
clients written in C or C++, or for any language that provides C bindings.
7
History of MySQL
• APIs for C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl are available, enabling MySQL
clients to be written in many languages. See Chapter 23, Connectors and APIs.
• The Connector/ODBC (MyODBC) interface provides MySQL support for client programs that use
ODBC (Open Database Connectivity) connections. For example, you can use MS Access to connect
to your MySQL server. Clients can be run on Windows or Unix. Connector/ODBC source is available.
All ODBC 2.5 functions are supported, as are many others. See MySQL Connector/ODBC Developer
Guide.
• The Connector/J interface provides MySQL support for Java client programs that use JDBC
connections. Clients can be run on Windows or Unix. Connector/J source is available. See MySQL
Connector/J 5.1 Developer Guide.
• MySQL Connector/NET enables developers to easily create .NET applications that require secure,
high-performance data connectivity with MySQL. It implements the required ADO.NET interfaces and
integrates into ADO.NET aware tools. Developers can build applications using their choice of .NET
languages. MySQL Connector/NET is a fully managed ADO.NET driver written in 100% pure C#.
See MySQL Connector/NET Developer Guide.
Localization
• The server can provide error messages to clients in many languages. See Section 10.11, “Setting
the Error Message Language”.
• Full support for several different character sets, including latin1 (cp1252), german, big5, ujis,
several Unicode character sets, and more. For example, the Scandinavian characters “å”, “ä” and “ö”
are permitted in table and column names.
• All data is saved in the chosen character set.
• Sorting and comparisons are done according to the default character set and collation. is possible
to change this when the MySQL server is started (see Section 10.3.2, “Server Character Set and
Collation”). To see an example of very advanced sorting, look at the Czech sorting code. MySQL
Server supports many different character sets that can be specified at compile time and runtime.
• The server time zone can be changed dynamically, and individual clients can specify their own time
zone. See Section 5.1.12, “MySQL Server Time Zone Support”.
Clients and Tools
• MySQL includes several client and utility programs. These include both command-line programs
such as mysqldump and mysqladmin, and graphical programs such as MySQL Workbench.
• MySQL Server has built-in support for SQL statements to check, optimize, and repair tables. These
statements are available from the command line through the mysqlcheck client. MySQL also
includes myisamchk, a very fast command-line utility for performing these operations on MyISAM
tables. See Chapter 4, MySQL Programs.
• MySQL programs can be invoked with the --help or -? option to obtain online assistance.
1.3.3 History of MySQL
We started out with the intention of using the mSQL database system to connect to our tables using
our own fast low-level (ISAM) routines. However, after some testing, we came to the conclusion that
mSQL was not fast enough or flexible enough for our needs. This resulted in a new SQL interface to our
database but with almost the same API interface as mSQL. This API was designed to enable third-party
code that was written for use with mSQL to be ported easily for use with MySQL.
MySQL is named after co-founder Monty Widenius's daughter, My.
The name of the MySQL Dolphin (our logo) is “Sakila,” which was chosen from a huge list of names
suggested by users in our “Name the Dolphin” contest. The winning name was submitted by Ambrose
8
What Is New in MySQL 5.5
Twebaze, an Open Source software developer from Swaziland, Africa. According to Ambrose, the
feminine name Sakila has its roots in SiSwati, the local language of Swaziland. Sakila is also the name
of a town in Arusha, Tanzania, near Ambrose's country of origin, Uganda.
1.4 What Is New in MySQL 5.5
This section summarizes what has been added to, deprecated in, and removed from MySQL 5.5.
• Features Added in MySQL 5.5
• Features Deprecated in MySQL 5.5
• Features Removed in MySQL 5.5
Features Added in MySQL 5.5
The following features have been added to MySQL 5.5:
• MySQL Enterprise Thread Pool.
The default thread-handling model in MySQL Server executes
statements using one thread per client connection. As more clients connect to the server and
execute statements, overall performance degrades. As of MySQL 5.5.16, MySQL Enterprise Edition
distributions include a thread pool plugin that provides an alternative thread-handling model designed
to reduce overhead and improve performance. The plugin implements a thread pool that increases
server performance by efficiently managing statement execution threads for large numbers of client
connections. For more information, see Section 5.5.3, “MySQL Enterprise Thread Pool”.
• MySQL Enterprise Audit.
MySQL Enterprise Edition now includes MySQL Enterprise Audit,
implemented using a server plugin named audit_log. MySQL Enterprise Audit uses the open
MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query
activity executed on specific MySQL servers. Designed to meet the Oracle audit specification,
MySQL Enterprise Audit provides an out of box, easy to use auditing and compliance solution for
applications that are governed by both internal and external regulatory guidelines. When installed,
the audit plugin enables MySQL Server to produce a log file containing an audit record of server
activity. The log contents include when clients connect and disconnect, and what actions they
perform while connected, such as which databases and tables they access. For more information,
see Section 6.5.2, “MySQL Enterprise Audit”.
• Pluggable authentication.
MySQL authentication supports two new capabilities, pluggable
authentication and proxy users. With pluggable authentication, the server can use plugins to
authenticate incoming client connections, and clients can load an authentication plugin that interacts
properly with the corresponding server plugin. This capability enables clients to connect to the
MySQL server with credentials that are appropriate for authentication methods other than the builtin MySQL authentication based on native MySQL passwords stored in the mysql.user table. For
example, plugins can be created to use external authentication methods such as LDAP, Kerberos,
PAM, or Windows login IDs. Proxy user capability enables a client who connects and authenticates
as one user to be treated, for purposes of access control while connected, as having the privileges
of a different user. In effect, one user impersonates another. Proxy capability depends on pluggable
authentication because it is based on having an authentication plugin return to the server the user
name that the connecting user impersonates. See Section 6.3.6, “Pluggable Authentication”, and
Section 6.3.7, “Proxy Users”.
As of MySQL 5.5.16, MySQL Enterprise Edition includes two plugins that enable MySQL Server to
use external authentication methods to authenticate MySQL users:
• PAM (Pluggable Authentication Modules) enables a system to access various kinds of
authentication methods through a standard interface. A PAM authentication plugin enables MySQL
Server to use PAM to authenticate MySQL users.
• Distributions of MySQL for Windows include an authentication plugin that enables MySQL Server
to use native Windows services to authenticate client connections. Users who have logged in to
9
Features Added in MySQL 5.5
Windows can connect from MySQL client programs to the server based on the information in their
environment without specifying an additional password.
These authentication plugins enable MySQL Server to accept connections from users defined
outside the MySQL grant tables. They also support the MySQL proxy-user capability. Each plugin
can return to MySQL a user name different from the login user, which means that the plugin can
return the MySQL user that defines the privileges the externally authenticated user should have.
For more information, see Section 6.5.1.4, “PAM Pluggable Authentication”, and Section 6.5.1.5,
“Windows Pluggable Authentication”.
• Multi-core scalability.
Scalability on multi-core CPUs is improved. The trend in hardware
development now is toward more cores rather than continued increases in CPU clock speeds, which
renders “wait until CPUs get faster” a nonviable means of improving database performance. Instead,
it is necessary to make better use of multiple cores to maximally exploit the processing cycles they
make available. MySQL 5.5 takes advantage of features of SMP systems and tries to eliminate
bottlenecks in MySQL architecture that hinder full use of multiple cores. The focus has been on
InnoDB, especially locking and memory management. See Scalability Improvements.
• Default storage engine.
The default storage engine for new tables is InnoDB rather than
MyISAM. See Section 14.1, “Introduction to InnoDB”.
• InnoDB I/O subsystem.
InnoDB I/O subsystem changes enable more effective use of available
I/O capacity. See InnoDB I/O Subsystem Changes.
• InnoDB storage engine.
MySQL 5.5 includes several InnoDB storage engine enhancements:
• Indexes can be added or dropped without copying the table. See Section 14.16, “InnoDB Fast
Index Creation”.
• Tables can be compressed to significantly reduce storage requirements and I/O. See
Section 14.12, “InnoDB Table Compression”.
• BLOB, TEXT, and VARCHAR columns can be stored fully off page. See Section 14.14, “InnoDB Row
Storage and Row Formats”.
• File format management enhancements protect upward and downward compatibility. See
Section 14.13, “InnoDB File-Format Management”.
• INFORMATION_SCHEMA tables provide information about InnoDB compression and locking. See
Section 14.18, “InnoDB INFORMATION_SCHEMA Tables”.
• InnoDB performance and scalability enhancements:
• The InnoDB mutex and read/write lock implementation was improved. Use of Pthreads mutexes
was replaced with calls to GCC - atomic builtins.
• The memory allocator used by InnoDB is configurable. See Section 14.11.3, “Configuring the
Memory Allocator for InnoDB”.
• The extent to which InnoDB performs change buffering is configurable. See Configuring
Change Buffering.
• The adaptive hash index (AHI) feature makes InnoDB perform more like an in-memory
database on systems with appropriate combinations of workload and ample memory for the
buffer pool, without sacrificing transactional features or reliability. See Section 14.8.3, “Adaptive
Hash Index”.
• Different techniques can be used to limit the number of concurrently executing operating system
threads to minimize context switching. See Section 14.11.4, “Configuring Thread Concurrency
for InnoDB”.
10
Features Added in MySQL 5.5
• How InnoDB performs buffer pool read-ahead is configurable. See Section 14.11.2.3,
“Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”.
• The number of background threads that service read and write I/O operations on data pages
is configurable. See Section 14.11.5, “Configuring the Number of Background InnoDB I/O
Threads”.
• Asynchronous I/O is supported on Linux systems. See innodb_use_native_aio.
• The overall I/O capacity available to InnoDB is configurable. See Section 14.11.7, “Configuring
the InnoDB Master Thread I/O Rate”.
• How InnoDB performs buffer pool flushing is configurable. Section 14.11.2.4, “Configuring
InnoDB Buffer Pool Flushing”.
• The maximum delay between checking the availability of a mutex or rw-lock is configurable. See
Section 14.11.8, “Configuring Spin Lock Polling”.
• InnoDB can be configured to minimize the amount of data brought into the buffer pool and
never accessed again. See Section 14.11.2.2, “Making the Buffer Pool Scan Resistant”.
• Crash recovery performance was improved. See Section 8.5.8, “Optimizing InnoDB
Configuration Variables”.
• Certain internal InnoDB operations can be profiled using the Performance Schema feature. See
Section 14.19, “InnoDB Integration with MySQL Performance Schema”.
• The buffer pool can be divided into separate instances to reduce contention between threads
that read and write to cached pages. See Section 14.11.2.1, “Configuring Multiple Buffer Pool
Instances”.
• The limit on concurrent data modifying transactions was increased. See Section 14.9.7, “Undo
Logs”.
• InnoDB can be configured to have purge operations performed by a separate thread, rather
than by the master thread. See Section 14.11.9, “Configuring InnoDB Purge Scheduling”.
• As of MySQL 5.5.62, the zlib library version bundled with MySQL was raised from version 1.2.3
to version 1.2.11. MySQL implements compression with the help of the zlib library.
If you use InnoDB compressed tables, see Section 2.11.1.3, “Changes in MySQL 5.5” for
related upgrade implications.
• InnoDB flexibility, ease of use, and reliability enhancements:
• The innodb_file_per_table, innodb_stats_on_metadata,
innodb_lock_wait_timeout, and innodb_adaptive_hash_index options can be set at
runtime using a SET statement.
• Operating system disk space can be reclaimed when truncating an InnoDB table. See
Section 14.15.5, “Reclaiming Disk Space with TRUNCATE TABLE”.
• InnoDB can be run in strict mode. See the innodb_strict_mode parameter documentation.
• InnoDB provides greater control over the quality of optimizer statistics estimates. See
Section 14.11.10, “Configuring Optimizer Statistics for InnoDB”.
• SHOW ENGINE INNODB MUTEX output is more compact. See Section 13.7.5.16, “SHOW
ENGINE Syntax”.
11
Features Added in MySQL 5.5
• SHOW ENGINE INNODB STATUS output displays counter information for the
Innodb_buffer_pool_read_ahead and Innodb_buffer_pool_read_ahead_evicted
global status variables, which you can use to fine-tune the innodb_random_read_ahead
setting and evaluate the effectiveness of the read-ahead algorithm.
• Diagnostic improvements.
There is better access to execution and performance information.
Diagnostic improvements include Performance Schema (a feature for monitoring MySQL Server
execution at a low level), DTrace probes, expanded SHOW ENGINE INNODB STATUS output, Debug
Sync, and a new status variable. See Diagnostic and Monitoring Capabilities.
• Solaris.
Several modifications improve operation of MySQL Server on Solaris. See Enhanced
Solaris Support.
• MySQL NDB Cluster.
MySQL NDB Cluster is released as a separate product, with version 7.2 of
the NDB storage engine being based on MySQL 5.5. Clustering support is not available in mainline
MySQL Server 5.5 releases. For more information about MySQL NDB Cluster 7.2, see Chapter 18,
MySQL NDB Cluster 7.2.
NDB Cluster releases are identified by a 3-part NDB version number. NDB Cluster 7.5, now available
as a General Availability (GA) release beginning with version 7.5.4, incorporates version 7.5 of the
NDB storage engine. Previous GA releases still available for production, NDB Cluster 7.3 and NDB
Cluster 7.4, incorporate NDB versions 7.3 and 7.4, respectively. For information about NDB Cluster
7.5, see MySQL NDB Cluster 7.5 and NDB Cluster 7.6. For more information about NDB Cluster 7.4
and NDB Cluster 7.3, see MySQL NDB Cluster 7.3 and NDB Cluster 7.4.
• Semisynchronous replication.
A commit performed on the master side blocks before returning
to the session that performed the transaction until at least one slave acknowledges that it has
received and logged the events for the transaction. Semisynchronous replication is implemented
through an optional plugin component. See Section 17.3.8, “Semisynchronous Replication”
• Unicode.
Support for supplementary Unicode characters; that is, characters outside the Basic
Multilingual Plane (BMP). These new Unicode character sets include supplementary characters:
utf16, utf32, and utf8mb4. See Section 10.9, “Unicode Support”.
• Partitioning.
Enhancements to table partitioning:
• Two new types of user-defined partitioning are supported: RANGE COLUMNS partitioning is an
extension to RANGE partitioning; LIST COLUMNS partitioning is an extension to LIST partitioning.
Each of these extensions provides two enhancements to MySQL partitioning capabilities:
• It is possible to define partitioning ranges or lists based on DATE, DATETIME, or string values
(such as CHAR or VARCHAR).
You can also define ranges or lists based on multiple column values when partitioning tables by
RANGE COLUMNS or LIST COLUMNS, respectively. Such a range or list may refer to up to 16
columns.
• For tables defined using these partitioning types, partition pruning can now optimize queries
with WHERE conditions that use multiple comparisons between (different) column values and
constants, such as a = 10 AND b > 5 or a < "2005-11-25" AND b = 10 AND c = 50.
See Section 19.2.1, “RANGE Partitioning”, and Section 19.2.2, “LIST Partitioning”.
• It is now possible to delete all rows from one or more partitions of a partitioned table using the
ALTER TABLE ... TRUNCATE PARTITION statement. Executing the statement deletes rows
without affecting the structure of the table. The partitions named in the TRUNCATE PARTITION
clause do not have to be contiguous.
• Key caches are now supported for indexes on partitioned MyISAM tables, using the CACHE INDEX
and LOAD INDEX INTO CACHE statements. In addition, a key cache can be defined for and
12
Features Deprecated in MySQL 5.5
loaded with indexes from an entire partitioned table, or for one or more partitions. In the latter
case, the partitions are not required to be contiguous.
• The new TO_SECONDS() function converts a date or datetime expression to a number of seconds
since the year 0. This is a general-purpose function, but is useful for partitioning. You may use
it in partitioning expressions, and partition pruning is supported for tables defined using such
expressions.
• SIGNAL and RESIGNAL.
Support for the SQL standard SIGNAL and RESIGNAL statements. See
Section 13.6.7, “Condition Handling”.
• Metadata locking.
The server now prevents DDL statements from compromising transaction
serializibility by using a new class of locks called metadata locks. See Section 8.11.4, “Metadata
Locking”.
• IPv6 support.
MySQL Server can accept TCP/IP connections from clients connecting over IPv6.
See Section 5.1.11, “IPv6 Support”.
• XML.
Enhancements to XML functionality, including a new LOAD XML INFILE statement. See
Section 13.2.7, “LOAD XML Syntax”.
• Build configuration.
MySQL releases are now built using CMake rather than the GNU autotools.
Accordingly, the instructions for installing MySQL from source have been updated to discuss how to
build MySQL using CMake. See Section 2.9, “Installing MySQL from Source”.
The build process is now similar enough on all platforms, including Windows, that there are no longer
sections dedicated to notes for specific platforms.
Features Deprecated in MySQL 5.5
The following features are deprecated in MySQL 5.5 and may be or will be removed in a future series.
Where alternatives are shown, applications should be updated to use them.
For applications that use features deprecated in MySQL 5.5 that have been removed in a higher
MySQL series, statements may fail when replicated from a MySQL 5.5 master to a higher-series slave,
or may have different effects on master and slave. To avoid such problems, applications that use
features deprecated in 5.5 should be revised to avoid them and use alternatives when possible.
• Relying on implicit GROUP BY sorting in MySQL 5.5 is deprecated. To achieve a specific sort order of
grouped results, it is preferable to use an explicit ORDER BY clause. GROUP BY sorting is a MySQL
extension that may change in a future release; for example, to make it possible for the optimizer to
order groupings in whatever manner it deems most efficient and to avoid the sorting overhead.
• The YEAR(2) data type. YEAR(2) columns in existing tables are treated as before, but YEAR(2)
in new or altered tables are converted to YEAR(4). For more information, see Section 11.3.4,
“YEAR(2) Limitations and Migrating to YEAR(4)”.
The SHOW AUTHORS and SHOW CONTRIBUTORS statements.
• The --ignore-builtin-innodb server option and ignore_builtin_innodb system variable.
They do nothing and have no effect.
• The --language server option. Use the lc_messages_dir and lc_messages sytem variables
instead.
• The ALWAYS value for the --base64-output option for mysqlbinlog.
• The --config-file option for mysqld_multi. Use --defaults-extra-file instead.
• Use of unambigious option prefixes. If an unambiguous prefix is given, a warning occurs to provide
feedback. Option prefixes are no longer supported in MySQL 5.7; only full options are accepted.
13
Features Removed in MySQL 5.5
• The engine_condition_pushdown system variable. Use the engine_condition_pushdown
flag of the optimizer_switch variable instead.
• The timed_mutexes system variable. It does nothing and has no effect.
• The storage_engine system variable. Use default_storage_engine instead.
• Use of the data directory as the location for my.cnf.
Features Removed in MySQL 5.5
The following constructs are obsolete and have been removed in MySQL 5.5. Where alternatives are
shown, applications should be updated to use them.
For MySQL 5.1 applications that use features removed in MySQL 5.5, statements may fail when
replicated from a MySQL 5.1 master to a MySQL 5.5 slave, or may have different effects on master
and slave. To avoid such problems, applications that use features removed in MySQL 5.5 should be
revised to avoid them and use alternatives when possible.
• The language system variable (use lc_messages_dir and lc_messages).
• The log_bin_trust_routine_creators system variable (use
log_bin_trust_function_creators).
• The myisam_max_extra_sort_file_size system variable.
• The record_buffer system variable (use read_buffer_size).
• The sql_log_update system variable.
• The Innodb_buffer_pool_read_ahead_rnd and Innodb_buffer_pool_read_ahead_seq
status variables (use Innodb_buffer_pool_read_ahead and
Innodb_buffer_pool_read_ahead_evicted).
• The table_lock_wait_timeout system variable.
• The table_type system variable (use default_storage_engine).
• The FRAC_SECOND modifier for the TIMESTAMPADD() function (use MICROSECOND).
• The TYPE table option to specify the storage engine for CREATE TABLE or ALTER TABLE (use
ENGINE).
• The SHOW TABLE TYPES SQL statement (use SHOW ENGINES).
• The SHOW INNODB STATUS and SHOW MUTEX STATUS SQL statements (use SHOW ENGINE
INNODB STATUS and SHOW ENGINE INNODB MUTEX).
• The SHOW PLUGIN SQL statement (use SHOW PLUGINS).
• The LOAD TABLE ... FROM MASTER and LOAD DATA FROM MASTER SQL statements (use
mysqldump or mysqlhotcopy to dump tables and mysql to reload dump files).
• The BACKUP TABLE and RESTORE TABLE SQL statements (use mysqldump or mysqlhotcopy to
dump tables and mysql to reload dump files).
• TIMESTAMP(N) data type: The ability to specify a display width of N (use without N).
• The --default-character-set and --default-collation server options (use -character-set-server and --collation-server).
• The --default-table-type server option (use --default-storage-engine).
14
Scalability Improvements
• The --delay-key-write-for-all-tables server option (use --delay-key-write=ALL).
• The --enable-locking and --skip-locking server options (use --external-locking and
--skip-external-locking).
• The --log-bin-trust-routine-creators server option (use --log-bin-trustfunction-creators).
• The --log-long-format server option.
• The --log-update server option.
• The --master-xxx server options to set replication parameters (use the CHANGE MASTER TO
statement instead): --master-host, --master-user, --master-password, --master-port,
--master-connect-retry, --master-ssl, --master-ssl-ca, --master-ssl-capath, -master-ssl-cert, --master-ssl-cipher, --master-ssl-key.
• The --safe-show-database server option.
• The --skip-symlink and --use-symbolic-links server options (use --skip-symboliclinks and --symbolic-links).
• The --sql-bin-update-same server option.
• The --warnings server option (use --log-warnings).
• The --no-named-commands option for mysql (use --skip-named-commands).
• The --no-pager option for mysql (use --skip-pager).
• The --no-tee option for mysql (use --skip-tee).
• The --position option for mysqlbinlog (use --start-position).
• The --all option for mysqldump (use --create-options).
• The --first-slave option for mysqldump (use --lock-all-tables).
• The --config-file option for mysqld_multi (use --defaults-extra-file).
• The --set-variable=var_name=value and -O var_name=value general-purpose options for
setting program variables (use --var_name=value).
• The --with-pstack option for configure and the --enable-pstack option for mysqld.
Scalability Improvements
MySQL 5.5 modifications improve performance on SMP systems to increase scalability on multi-core
systems. The changes affect InnoDB locking and memory management.
MySQL 5.5 incorporates changes in InnoDB that improve the performance of RW-locks by using
atomic CPU instructions (on platforms where they are available), rather than less scalable mutexes.
It is also possible for InnoDB memory allocation to be disabled and replaced by the normal malloc
library, or by a different library that implements malloc such as tcmalloc on Linux or mtalloc on
Solaris.
The reimplementation of RW-locks requires atomic instructions. A status variable,
Innodb_have_atomic_builtins, shows whether the server was built with atomic instructions.
InnoDB I/O Subsystem Changes
MySQL 5.5 changes to the InnoDB I/O subsystem enable more effective use of available I/O capacity.
The changes also provide more control over configuration of the I/O subsystem.
15
Diagnostic and Monitoring Capabilities
Background I/O Threads
InnoDB uses background threads to perform I/O for several kinds of activities, two of which are
prefetching disk blocks and flushing dirty pages. Previously, InnoDB used only one thread each to
perform these activities, but that can underutilize server capacity. MySQL 5.5 enables use of multiple
background read and write threads, making it possible to read and write pages faster.
The patch makes the number of background I/O threads configurable using system variables:
innodb_read_io_threads controls the number of threads to use for read prefetch requests.
innodb_write_io_threads controls the number of threads to use for writing dirty pages from the
buffer cache to disk. The default for both variables is 4.
The ability to increase the number of I/O threads can benefit systems that use multiple disks for
InnoDB. However, the type of I/O being done should be considered. On systems that use buffered
writes rather than direct writes, increasing the write thread count higher than 1 might yield little benefit
because writes will be quick already.
Adjustable I/O Rate
Previously, the number of input/output operations per second (IOPS) that InnoDB will perform was
a compile-time parameter. The rate was chosen to prevent background I/O from exhausting server
capacity and the compiled-in value of 100 reflected an assumption that the server can perform 100
IOPS. However, many modern systems can exceed this, so the value is low and unnecessarily restricts
I/O utilization.
MySQL 5.5 exposes this I/O rate parameter as a system variable, innodb_io_capacity. This
variable can be set at server startup, which enables higher values to be selected for systems capable
of higher I/O rates. Having a higher I/O rate can help the server handle a higher rate of row changes
because it may be able to increase dirty-page flushing, deleted-row removal, and application of
changes in the change buffer. The default value of innodb_io_capacity is 200. In general, you can
increase the value as a function of the number of drives used for InnoDB I/O.
The ability to raise the I/O limit should be especially beneficial on platforms that support many IOPS.
For example, systems that use multiple disks or solid-state disks for InnoDB are likely to benefit from
the ability to control this parameter.
Diagnostic and Monitoring Capabilities
MySQL 5.5 provides improved access to execution and performance information. Diagnostic
improvements include Performance Schema, Dtrace probes, expanded SHOW ENGINE INNODB
STATUS output, Debug Sync, and a new status variable.
Performance Schema
Performance Schema is a feature for monitoring MySQL Server execution at a low level. See
Chapter 22, MySQL Performance Schema.
DTrace Support
The DTrace probes work on Solaris, OS X, and FreeBSD. For information on using DTrace in MySQL,
see Section 5.8, “Tracing mysqld Using DTrace”.
Enhanced SHOW ENGINE INNODB STATUS Output
The output from SHOW ENGINE INNODB STATUS includes more information due to changes made for
InnoDB Plugin. A description of revisions to statement output follows.
A new BACKGROUND THREAD section has srv_master_thread lines that show work done by the
main background thread.
----------
16
Enhanced Solaris Support
BACKGROUND THREAD
---------srv_master_thread loops: 53 1_second, 44 sleeps, 5 10_second, 7 background,
7 flush
srv_master_thread log flush and writes: 48
The SEMAPHORES section includes a line to show the number of spinlock rounds per OS wait for a
mutex.
---------SEMAPHORES
---------...
Spin rounds per wait: 0.00 mutex, 20.00 RW-shared, 0.00 RW-excl
Debug Sync
The Debug Sync facility provides synchronization points for debugging, see MySQL Internals: Test
Synchronization.
New Status Variable
The Innodb_have_atomic_builtins status variable provides information about availability of
atomic instructions; see Scalability Improvements.
Enhanced Solaris Support
MySQL 5.5 incorporates several modifications for improved operation of MySQL Server on Solaris:
• DTrace support for execution monitoring. See Diagnostic and Monitoring Capabilities.
• Atomic instructions, which are needed for the improvements to RW-locking described in Scalability
Improvements. Atomic instructions now are supported for Sun Studio on SPARC and x86 platforms.
This extends their previous availability (supported for gcc 4.1 and up on all platforms).
• The SMP improvements described in Scalability Improvements, were originally intended for x86
platforms. In MySQL 5.5, these also work on SPARC platforms. Also, Solaris optimizations have
been implemented.
• Large page support is enhanced for recent SPARC platforms. Standard use of large pages in
MySQL attempts to use the largest size supported, up to 4MB. Under Solaris, a “super large pages”
feature enables uses of pages up to 256MB. This feature can be enabled or disabled by using the -super-large-pages or --skip-super-large-pages option.
• Inline handling for InnoDB and processor instruction prefetching support, previously not enabled for
builds created using Sun Studio, now are supported for that build environment.
1.5 MySQL Information Sources
This section lists sources of additional information that you may find helpful, such as MySQL websites,
mailing lists, user forums, and Internet Relay Chat.
1.5.1 MySQL Websites
The primary website for MySQL documentation is https://dev.mysql.com/doc/. Online and
downloadable documentation formats are available for the MySQL Reference Manual, MySQL
Connectors, and more.
The MySQL developers provide information about new and upcoming features as the MySQL Server
Blog.
1.5.2 MySQL Mailing Lists
17
MySQL Mailing Lists
This section introduces the MySQL mailing lists and provides guidelines as to how the lists should be
used. When you subscribe to a mailing list, you receive all postings to the list as email messages. You
can also send your own questions and answers to the list.
To subscribe to or unsubscribe from any of the mailing lists described in this section, visit http://
lists.mysql.com/. For most of them, you can select the regular version of the list where you get
individual messages, or a digest version where you get one large message per day.
Please do not send messages about subscribing or unsubscribing to any of the mailing lists, because
such messages are distributed automatically to thousands of other users.
Your local site may have many subscribers to a MySQL mailing list. If so, the site may have a local
mailing list, so that messages sent from lists.mysql.com to your site are propagated to the local
list. In such cases, please contact your system administrator to be added to or dropped from the local
MySQL list.
To have traffic for a mailing list go to a separate mailbox in your mail program, set up a filter based on
the message headers. You can use either the List-ID: or Delivered-To: headers to identify list
messages.
The MySQL mailing lists are as follows:
• announce
The list for announcements of new versions of MySQL and related programs. This is a low-volume
list to which all MySQL users should subscribe.
• mysql
The main list for general MySQL discussion. Please note that some topics are better discussed on
the more-specialized lists. If you post to the wrong list, you may not get an answer.
• bugs
The list for people who want to stay informed about issues reported since the last release of MySQL
or who want to be actively involved in the process of bug hunting and fixing. See Section 1.6, “How
to Report Bugs or Problems”.
• internals
The list for people who work on the MySQL code. This is also the forum for discussions on MySQL
development and for posting patches.
• mysqldoc
The list for people who work on the MySQL documentation.
• benchmarks
The list for anyone interested in performance issues. Discussions concentrate on database
performance (not limited to MySQL), but also include broader categories such as performance of the
kernel, file system, disk system, and so on.
• packagers
The list for discussions on packaging and distributing MySQL. This is the forum used by distribution
maintainers to exchange ideas on packaging MySQL and on ensuring that MySQL looks and feels as
similar as possible on all supported platforms and operating systems.
• java
The list for discussions about the MySQL server and Java. It is mostly used to discuss JDBC drivers
such as MySQL Connector/J.
18
MySQL Mailing Lists
• win32
The list for all topics concerning the MySQL software on Microsoft operating systems, such as
Windows 9x, Me, NT, 2000, XP, and 2003.
• myodbc
The list for all topics concerning connecting to the MySQL server with ODBC.
• gui-tools
The list for all topics concerning MySQL graphical user interface tools such as MySQL Workbench.
• cluster
The list for discussion of MySQL Cluster.
• dotnet
The list for discussion of the MySQL server and the .NET platform. It is mostly related to MySQL
Connector/NET.
• plusplus
The list for all topics concerning programming with the C++ API for MySQL.
• perl
The list for all topics concerning Perl support for MySQL with DBD::mysql.
If you're unable to get an answer to your questions from a MySQL mailing list or forum, one option is to
purchase support from Oracle. This puts you in direct contact with MySQL developers.
The following MySQL mailing lists are in languages other than English. These lists are not operated by
Oracle.
•
A French mailing list.
•
A Korean mailing list. To subscribe, email subscribe mysql your@email.address to this list.
•
A German mailing list. To subscribe, email subscribe mysql-de your@email.address to this
list. You can find information about this mailing list at http://www.4t2.com/mysql/.
•
A Portuguese mailing list. To subscribe, email subscribe mysql-br your@email.address to
this list.
•
A Spanish mailing list. To subscribe, email subscribe mysql your@email.address to this list.
1.5.2.1 Guidelines for Using the Mailing Lists
Please do not post mail messages from your browser with HTML mode turned on. Many users do not
read mail with a browser.
19
MySQL Community Support at the MySQL Forums
When you answer a question sent to a mailing list, if you consider your answer to have broad interest,
you may want to post it to the list instead of replying directly to the individual who asked. Try to make
your answer general enough that people other than the original poster may benefit from it. When you
post to the list, please make sure that your answer is not a duplication of a previous answer.
Try to summarize the essential part of the question in your reply. Do not feel obliged to quote the entire
original message.
When answers are sent to you individually and not to the mailing list, it is considered good etiquette to
summarize the answers and send the summary to the mailing list so that others may have the benefit
of responses you received that helped you solve your problem.
1.5.3 MySQL Community Support at the MySQL Forums
The forums at http://forums.mysql.com are an important community resource. Many forums are
available, grouped into these general categories:
• Migration
• MySQL Usage
• MySQL Connectors
• Programming Languages
• Tools
• 3rd-Party Applications
• Storage Engines
• MySQL Technology
• SQL Standards
• Business
1.5.4 MySQL Community Support on Internet Relay Chat (IRC)
In addition to the various MySQL mailing lists and forums, you can find experienced community people
on Internet Relay Chat (IRC). These are the best networks/channels currently known to us:
freenode (see http://www.freenode.net/ for servers)
• #mysql is primarily for MySQL questions, but other database and general SQL questions are
welcome. Questions about PHP, Perl, or C in combination with MySQL are also common.
• #workbench is primarily for MySQL Workbench related questions and thoughts, and it is also a
good place to meet the MySQL Workbench developers.
1.5.5 MySQL Enterprise
Oracle offers technical support in the form of MySQL Enterprise. For organizations that rely on the
MySQL DBMS for business-critical production applications, MySQL Enterprise is a commercial
subscription offering which includes:
• MySQL Enterprise Server
• MySQL Enterprise Monitor
• Monthly Rapid Updates and Quarterly Service Packs
• MySQL Knowledge Base
20
How to Report Bugs or Problems
• 24x7 Technical and Consultative Support
MySQL Enterprise is available in multiple tiers, giving you the flexibility to choose the level of service
that best matches your needs. For more information, see MySQL Enterprise.
1.6 How to Report Bugs or Problems
Before posting a bug report about a problem, please try to verify that it is a bug and that it has not been
reported already:
• Start by searching the MySQL online manual at https://dev.mysql.com/doc/. We try to keep the
manual up to date by updating it frequently with solutions to newly found problems. In addition, the
release notes accompanying the manual can be particularly useful since it is quite possible that a
newer version contains a solution to your problem. The release notes are available at the location
just given for the manual.
• If you get a parse error for an SQL statement, please check your syntax closely. If you cannot find
something wrong with it, it is extremely likely that your current version of MySQL Server doesn't
support the syntax you are using. If you are using the current version and the manual doesn't cover
the syntax that you are using, MySQL Server doesn't support your statement.
If the manual covers the syntax you are using, but you have an older version of MySQL Server, you
should check the MySQL change history to see when the syntax was implemented. In this case, you
have the option of upgrading to a newer version of MySQL Server.
• For solutions to some common problems, see Section B.5, “Problems and Common Errors”.
• Search the bugs database at http://bugs.mysql.com/ to see whether the bug has been reported and
fixed.
• Search the MySQL mailing list archives at http://lists.mysql.com/. See Section 1.5.2, “MySQL Mailing
Lists”.
• You can also use http://www.mysql.com/search/ to search all the Web pages (including the manual)
that are located at the MySQL website.
If you cannot find an answer in the manual, the bugs database, or the mailing list archives, check with
your local MySQL expert. If you still cannot find an answer to your question, please use the following
guidelines for reporting the bug.
The normal way to report bugs is to visit http://bugs.mysql.com/, which is the address for our bugs
database. This database is public and can be browsed and searched by anyone. If you log in to the
system, you can enter new reports.
Bugs posted in the bugs database at http://bugs.mysql.com/ that are corrected for a given release are
noted in the release notes.
If you find a sensitive security bug in MySQL Server, please let us know immediately by sending an
email message to . Exception: Support customers should report all
problems, including security bugs, to Oracle Support at http://support.oracle.com/.
To discuss problems with other users, you can use one of the MySQL mailing lists. Section 1.5.2,
“MySQL Mailing Lists”.
Writing a good bug report takes patience, but doing it right the first time saves time both for us and for
yourself. A good bug report, containing a full test case for the bug, makes it very likely that we will fix
the bug in the next release. This section helps you write your report correctly so that you do not waste
your time doing things that may not help us much or at all. Please read this section carefully and make
sure that all the information described here is included in your report.
Preferably, you should test the problem using the latest production or development version of MySQL
Server before posting. Anyone should be able to repeat the bug by just using mysql test <
21
How to Report Bugs or Problems
script_file on your test case or by running the shell or Perl script that you include in the bug report.
Any bug that we are able to repeat has a high chance of being fixed in the next MySQL release.
It is most helpful when a good description of the problem is included in the bug report. That is, give a
good example of everything you did that led to the problem and describe, in exact detail, the problem
itself. The best reports are those that include a full example showing how to reproduce the bug or
problem. See Section 24.5, “Debugging and Porting MySQL”.
Remember that it is possible for us to respond to a report containing too much information, but not to
one containing too little. People often omit facts because they think they know the cause of a problem
and assume that some details do not matter. A good principle to follow is that if you are in doubt about
stating something, state it. It is faster and less troublesome to write a couple more lines in your report
than to wait longer for the answer if we must ask you to provide information that was missing from the
initial report.
The most common errors made in bug reports are (a) not including the version number of the MySQL
distribution that you use, and (b) not fully describing the platform on which the MySQL server is
installed (including the platform type and version number). These are highly relevant pieces of
information, and in 99 cases out of 100, the bug report is useless without them. Very often we get
questions like, “Why doesn't this work for me?” Then we find that the feature requested wasn't
implemented in that MySQL version, or that a bug described in a report has been fixed in newer
MySQL versions. Errors often are platform-dependent. In such cases, it is next to impossible for us to
fix anything without knowing the operating system and the version number of the platform.
If you compiled MySQL from source, remember also to provide information about your compiler if
it is related to the problem. Often people find bugs in compilers and think the problem is MySQLrelated. Most compilers are under development all the time and become better version by version. To
determine whether your problem depends on your compiler, we need to know what compiler you used.
Note that every compiling problem should be regarded as a bug and reported accordingly.
If a program produces an error message, it is very important to include the message in your report. If
we try to search for something from the archives, it is better that the error message reported exactly
matches the one that the program produces. (Even the lettercase should be observed.) It is best
to copy and paste the entire error message into your report. You should never try to reproduce the
message from memory.
If you have a problem with Connector/ODBC (MyODBC), please try to generate a trace file and send it
with your report. See How to Report Connector/ODBC Problems or Bugs.
If your report includes long query output lines from test cases that you run with the mysql commandline tool, you can make the output more readable by using the --vertical option or the \G statement
terminator. The EXPLAIN SELECT example later in this section demonstrates the use of \G.
Please include the following information in your report:
• The version number of the MySQL distribution you are using (for example, MySQL 5.7.10). You can
find out which version you are running by executing mysqladmin version. The mysqladmin
program can be found in the bin directory under your MySQL installation directory.
• The manufacturer and model of the machine on which you experience the problem.
• The operating system name and version. If you work with Windows, you can usually get the name
and version number by double-clicking your My Computer icon and pulling down the “Help/About
Windows” menu. For most Unix-like operating systems, you can get this information by executing the
command uname -a.
• Sometimes the amount of memory (real and virtual) is relevant. If in doubt, include these values.
• The contents of the docs/INFO_BIN file from your MySQL installation. This file contains information
about how MySQL was configured and compiled.
22
How to Report Bugs or Problems
• If you are using a source distribution of the MySQL software, include the name and version number
of the compiler that you used. If you have a binary distribution, include the distribution name.
• If the problem occurs during compilation, include the exact error messages and also a few lines of
context around the offending code in the file where the error occurs.
• If mysqld died, you should also report the statement that crashed mysqld. You can usually get this
information by running mysqld with query logging enabled, and then looking in the log after mysqld
crashes. See Section 24.5, “Debugging and Porting MySQL”.
• If a database table is related to the problem, include the output from the SHOW CREATE TABLE
db_name.tbl_name statement in the bug report. This is a very easy way to get the definition of
any table in a database. The information helps us create a situation matching the one that you have
experienced.
• The SQL mode in effect when the problem occurred can be significant, so please report the value
of the sql_mode system variable. For stored procedure, stored function, and trigger objects, the
relevant sql_mode value is the one in effect when the object was created. For a stored procedure
or function, the SHOW CREATE PROCEDURE or SHOW CREATE FUNCTION statement shows the
relevant SQL mode, or you can query INFORMATION_SCHEMA for the information:
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, SQL_MODE
FROM INFORMATION_SCHEMA.ROUTINES;
For triggers, you can use this statement:
SELECT EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE, TRIGGER_NAME, SQL_MODE
FROM INFORMATION_SCHEMA.TRIGGERS;
• For performance-related bugs or problems with SELECT statements, you should always include
the output of EXPLAIN SELECT ..., and at least the number of rows that the SELECT statement
produces. You should also include the output from SHOW CREATE TABLE tbl_name for each
table that is involved. The more information you provide about your situation, the more likely it is that
someone can help you.
The following is an example of a very good bug report. The statements are run using the mysql
command-line tool. Note the use of the \G statement terminator for statements that would otherwise
provide very long output lines that are difficult to read.
mysql> SHOW VARIABLES;
mysql> SHOW COLUMNS FROM ...\G