START-INFO-DIR-ENTRY * mysql: (mysql). MySQL documentation. END-INFO-DIR-ENTRY Table of Contents ***************** General Information About This Manual Conventions Used in This Manual Overview of the MySQL Database Management System History of MySQL The Main Features of MySQL MySQL Stability How Big MySQL Tables Can Be Year 2000 Compliance Overview of MySQL AB The Business Model and Services of MySQL AB Support Training and Certification Consulting Commercial Licenses Partnering Contact Information MySQL Support and Licensing Support Offered by MySQL AB Copyrights and Licenses Used by MySQL MySQL Licenses Using the MySQL Software Under a Commercial License Using the MySQL Software for Free Under GPL MySQL AB Logos and Trademarks The Original MySQL Logo MySQL Logos that may be Used Without Written Permission When You Need Written Permission to Use MySQL Logos MySQL AB Partnership Logos Using the Word `MySQL' in Printed Text or Presentations Using the Word `MySQL' in Company and Product Names MySQL Development Roadmap MySQL 4.0 in a Nutshell Features Available in MySQL 4.0 The Embedded MySQL Server MySQL 4.1 in a Nutshell Features Available in MySQL 4.1 Stepwise Rollout Ready for Immediate Development Use MySQL 5.0, The Next Development Release MySQL and the Future (The TODO) New Features Planned for 4.1 New Features Planned for 5.0 New Features Planned for 5.1 New Features Planned for the Near Future New Features Planned for the Mid-Term Future New Features We Don't Plan to Implement MySQL Information Sources MySQL Mailing Lists The MySQL Mailing Lists Asking Questions or Reporting Bugs How to Report Bugs or Problems Guidelines for Answering Questions on the Mailing List MySQL Community Support on IRC (Internet Relay Chat) MySQL Standards Compliance What Standards MySQL Follows Selecting SQL Modes Running MySQL in ANSI Mode MySQL Extensions to the SQL-92 Standard MySQL Differences Compared to SQL-92 Subqueries `SELECT INTO TABLE' Transactions and Atomic Operations Stored Procedures and Triggers Foreign Keys Views `--' as the Start of a Comment How MySQL Deals with Constraints Constraint PRIMARY KEY / UNIQUE Constraint `NOT NULL' and `DEFAULT' values Constraint `ENUM' and `SET' Known Errors and Design Deficiencies in MySQL Errors in 3.23 Fixed in a Later MySQL Version Errors in 4.0 Fixed in a Later MySQL Version Open Bugs / Design Deficiencies in MySQL Installing MySQL General Installation Issues Operating Systems Supported by MySQL Choosing Which MySQL Distribution to Install Choosing Which Version of MySQL to Install Choosing a Distribution Format How and When Updates Are Released Release Philosophy--No Known Bugs in Releases MySQL Binaries Compiled by MySQL AB How to Get MySQL Verifying Package Integrity Using MD5 Checksums or `GnuPG' Installation Layouts Standard MySQL Installation Using a Binary Distribution Installing MySQL on Windows Windows System Requirements Installing a Windows Binary Distribution Preparing the Windows MySQL Environment Selecting a Windows Server Starting the Server for the First Time Starting MySQL from the Windows Command Line Starting MySQL as a Windows Service Running MySQL Client Programs on Windows MySQL on Windows Compared to MySQL on Unix Installing MySQL on Linux Installing MySQL on Mac OS X Installing MySQL on NetWare Installing MySQL on HP-UX Installing MySQL on Other Unix-like Systems MySQL Installation Using a Source Distribution Quick Source Installation Overview Typical `configure' Options Installing from the Development Source Tree Dealing With Problems Compiling MySQL MIT-pthreads Notes Installing MySQL from Source on Windows Building MySQL Using VC++ Creating a Windows Source Package from the Latest Development Source Compiling MySQL Clients on Windows Post-installation Setup and Testing Windows Post-installation Procedures Unix Post-installation Procedures Problems Running `mysql_install_db' Starting and Stopping MySQL Automatically Starting and Troubleshooting the MySQL Server Upgrading/Downgrading MySQL Upgrading from Version 4.1 to 5.0 Upgrading from Version 4.0 to 4.1 Upgrading from Version 3.23 to 4.0 Upgrading from Version 3.22 to 3.23 Upgrading from Version 3.21 to 3.22 Upgrading from Version 3.20 to 3.21 Upgrading MySQL under Windows Upgrading the Grant Tables Copying MySQL Databases to Another Machine Operating System Specific Notes Linux Notes Linux Operating System Notes Linux Binary Distribution Notes Linux Source Distribution Notes Linux Post-installation Notes Linux x86 Notes Linux SPARC Notes Linux Alpha Notes Linux PowerPC Notes Linux MIPS Notes Linux IA-64 Notes Mac OS X Notes Mac OS X 10.x (Darwin) Mac OS X Server 1.2 (Rhapsody) Solaris Notes Solaris 2.7/2.8 Notes Solaris x86 Notes BSD Notes FreeBSD Notes NetBSD Notes OpenBSD 2.5 Notes OpenBSD 2.8 Notes BSD/OS Version 2.x Notes BSD/OS Version 3.x Notes BSD/OS Version 4.x Notes Other Unix Notes HP-UX Version 10.20 Notes HP-UX Version 11.x Notes IBM-AIX notes SunOS 4 Notes Alpha-DEC-UNIX Notes (Tru64) Alpha-DEC-OSF/1 Notes SGI Irix Notes SCO Notes SCO UnixWare Version 7.1.x Notes OS/2 Notes BeOS Notes Perl Installation Notes Installing Perl on Unix Installing ActiveState Perl on Windows Problems Using the Perl `DBI'/`DBD' Interface MySQL Tutorial Connecting to and Disconnecting from the Server Entering Queries Creating and Using a Database Creating and Selecting a Database Creating a Table Loading Data into a Table Retrieving Information from a Table Selecting All Data Selecting Particular Rows Selecting Particular Columns Sorting Rows Date Calculations Working with `NULL' Values Pattern Matching Counting Rows Using More Than one Table Getting Information About Databases and Tables Using `mysql' in Batch Mode Examples of Common Queries The Maximum Value for a Column The Row Holding the Maximum of a Certain Column Maximum of Column per Group The Rows Holding the Group-wise Maximum of a Certain Field Using User Variables Using Foreign Keys Searching on Two Keys Calculating Visits Per Day Using `AUTO_INCREMENT' Queries from the Twin Project Find All Non-distributed Twins Show a Table of Twin Pair Status Using MySQL with Apache Using MySQL Programs Overview of MySQL Programs Invoking MySQL Programs Specifying Program Options Using Options on the Command Line Using Option Files Using Environment Variables to Specify Options Using Options to Set Program Variables Database Administration The MySQL Server and Server Startup Scripts Overview of the Server-Side Scripts and Utilities `mysqld-max', An Extended `mysqld' Server `mysqld_safe', The Wrapper Around `mysqld' `mysql.server', A Server Startup Script for Run Directories `mysqld_multi', A Program for Managing Multiple MySQL Servers Configuring MySQL `mysqld' Command-line Options The Server SQL Mode General Security Issues General Security Guidelines Making MySQL Secure Against Attackers Startup Options for `mysqld' Concerning Security Security Issues with `LOAD DATA LOCAL' The MySQL Access Privilege System What the Privilege System Does How the Privilege System Works Privileges Provided by MySQL Connecting to the MySQL Server Access Control, Stage 1: Connection Verification Access Control, Stage 2: Request Verification Password Hashing in MySQL 4.1 Causes of `Access denied' Errors MySQL User Account Management MySQL Usernames and Passwords When Privilege Changes Take Effect Setting Up the Initial MySQL Privileges Adding New Users to MySQL Deleting Users from MySQL Limiting user resources Setting Up Passwords Keeping Your Password Secure Using Secure Connections Basics Requirements Setting Up SSL Certificates for MySQL SSL `GRANT' Options SSL Command-line Options Connecting to MySQL Remotely from Windows with SSH Disaster Prevention and Recovery Database Backups Using `myisamchk' for Table Maintenance and Crash Recovery `myisamchk' Invocation Syntax General Options for `myisamchk' Check Options for `myisamchk' Repair Options for myisamchk Other Options for `myisamchk' `myisamchk' Memory Usage Using `myisamchk' for Crash Recovery How to Check Tables for Errors How to Repair Tables Table Optimization Setting Up a Table Maintenance Regimen Getting Information About a Table MySQL Localization and International Usage The Character Set Used for Data and Sorting German character set Non-English Error Messages Adding a New Character Set The Character Definition Arrays String Collating Support Multi-byte Character Support Problems With Character Sets The MySQL Log Files The Error Log The General Query Log The Update Log The Binary Log The Slow Query Log Log File Maintenance Running Multiple MySQL Servers on the Same Machine Running Multiple Servers on Windows Starting Multiple Windows Servers at the Command Line Starting Multiple Windows Servers as Services Running Multiple Servers on Unix Using Client Programs in a Multiple-Server Environment Replication in MySQL Introduction to Replication Replication Implementation Overview Replication Implementation Details How to Set Up Replication Upgrading a Replication Setup - Mixing Different MySQL Versions Replication Features and Known Problems Replication Startup Options Replication FAQ Troubleshooting Replication Reporting Replication Bugs MySQL Optimization Optimization Overview MySQL Design Limitations/Tradeoffs Portability What We Have Used MySQL For The MySQL Benchmark Suite Using Your Own Benchmarks Optimizing `SELECT' Statements and Other Queries `EXPLAIN' Syntax (Get Information About a `SELECT') Estimating Query Performance Speed of `SELECT' Queries How MySQL Optimizes `WHERE' Clauses How MySQL Optimizes `OR' Clauses How MySQL Optimizes `IS NULL' How MySQL Optimizes `DISTINCT' How MySQL Optimizes `LEFT JOIN' and `RIGHT JOIN' How MySQL Optimizes `ORDER BY' How MySQL Optimizes `LIMIT' Speed of `INSERT' Queries Speed of `UPDATE' Queries Speed of `DELETE' Queries Other Optimization Tips Locking Issues How MySQL Locks Tables Table Locking Issues Optimizing Database Structure Design Choices Get Your Data as Small as Possible How MySQL Uses Indexes Column Indexes Multiple-Column Indexes The MyISAM Key Cache Shared Key Cache Access Multiple Key Caches Midpoint Insertion Strategy Index Preloading Key Cache Block Size Restructuring a Key Cache How MySQL Counts Open Tables How MySQL Opens and Closes Tables Drawbacks to Creating Large Numbers of Tables in the Same Database Optimizing the MySQL Server System/Compile Time and Startup Parameter Tuning Tuning Server Parameters How Compiling and Linking Affects the Speed of MySQL How MySQL Uses Memory How MySQL uses DNS `SET' Syntax Disk Issues Using Symbolic Links Using Symbolic Links for Databases on Unix Using Symbolic Links for Tables on Unix Using Symbolic Links for Databases on Windows MySQL Client and Utility Programs Overview of the Client-Side Scripts and Utilities `mysql', The Command-line Tool How to Run SQL Commands from a Text File `mysqlcc', The MySQL Control Center `mysqladmin', Administering a MySQL Server `mysqlbinlog', Executing the queries from a binary log Using `mysqlcheck' for Table Maintenance and Crash Recovery `mysqldump', Dumping Table Structure and Data `mysqlhotcopy', Copying MySQL Databases and Tables `mysqlimport', Importing Data from Text Files `mysqlshow', Showing Databases, Tables, and Columns `myisampack', The MySQL Compressed Read-only Table Generator `mysql_config', Get compile options for compiling clients `perror', Explaining Error Codes MySQL Language Reference Language Structure Literal Values Strings Numbers Hexadecimal Values Boolean Values `NULL' Values Database, Table, Index, Column, and Alias Names Identifier Qualifiers Identifier Case Sensitivity User Variables System Variables Dynamic System Variables Structured System Variables Comment Syntax Treatment of Reserved Words in MySQL Column Types Numeric Types Date and Time Types Y2K Issues and Date Types The `DATETIME', `DATE', and `TIMESTAMP' Types The `TIME' Type The `YEAR' Type String Types The `CHAR' and `VARCHAR' Types The `BLOB' and `TEXT' Types The `ENUM' Type The `SET' Type Choosing the Right Type for a Column Using Column Types from Other Database Engines Column Type Storage Requirements Functions and Operators Non-Type-Specific Operators and Functions Parentheses Comparison Operators Logical Operators Control Flow Functions String Functions String Comparison Functions Case-Sensitivity Numeric Functions Arithmetic Operations Mathematical Functions Date and Time Functions Cast Functions Other Functions Bit Functions Encryption Functions Information Functions Miscellaneous Functions Functions and Modifiers for Use with `GROUP BY' Clauses `GROUP BY' Functions `GROUP BY' Modifiers `GROUP BY' with Hidden Fields SQL Statement Syntax Data Manipulation Statements `DELETE' Syntax `DO' Syntax `HANDLER' Syntax `INSERT' Syntax `INSERT ... SELECT' Syntax `INSERT DELAYED' Syntax `LOAD DATA INFILE' Syntax `REPLACE' Syntax `SELECT' Syntax `JOIN' Syntax `UNION' Syntax Subquery Syntax The Subquery as Scalar Operand Comparisons Using Subqueries Subqueries with `ANY', `IN', and `SOME' Subqueries with `ALL' Correlated Subqueries `EXISTS' and `NOT EXISTS' Row Subqueries Subqueries in the `FROM' clause Subquery Errors Optimizing Subqueries Rewriting Subqueries for Earlier MySQL Versions `TRUNCATE' Syntax `UPDATE' Syntax Data Definition Statements `ALTER DATABASE' Syntax `ALTER TABLE' Syntax `CREATE DATABASE' Syntax `CREATE INDEX' Syntax `CREATE TABLE' Syntax Silent Column Specification Changes `DROP DATABASE' Syntax `DROP INDEX' Syntax `DROP TABLE' Syntax `RENAME TABLE' Syntax Basic MySQL User Utility Statements `DESCRIBE' Syntax (Get Information About Columns) `USE' Syntax MySQL Transactional and Locking Statements `START TRANSACTION', `COMMIT', and `ROLLBACK' Syntax Statements That Cannot Be Rolled Back Statements That Cause an Implicit Commit `SAVEPOINT' and `ROLLBACK TO SAVEPOINT' Syntax `LOCK TABLES' and `UNLOCK TABLES' Syntax `SET TRANSACTION' Syntax Database Administration Statements Account Management Statements `GRANT' and `REVOKE' Syntax Table Maintenance Statements `ANALYZE TABLE' Syntax `BACKUP TABLE' Syntax `CHECK TABLE' Syntax `CHECKSUM TABLE' Syntax `OPTIMIZE TABLE' Syntax `REPAIR TABLE' Syntax `RESTORE TABLE' Syntax `SHOW' Syntax Retrieving Information about Database, Tables, Columns, and Indexes `SHOW TABLE STATUS' `SHOW STATUS' `SHOW VARIABLES' `SHOW [BDB] LOGS' `SHOW PROCESSLIST' `SHOW GRANTS' `SHOW CREATE TABLE' `SHOW WARNINGS | ERRORS' `SHOW TABLE TYPES' `SHOW PRIVILEGES' Other Administrative Statements `CACHE INDEX' Syntax `FLUSH' Syntax `KILL' Syntax `LOAD INDEX INTO CACHE' Syntax `PURGE MASTER LOGS' Syntax `RESET' Syntax Replication Statements SQL Statements for Controlling Master Servers `PURGE MASTER LOGS' `RESET MASTER' `SET SQL_LOG_BIN' `SHOW BINLOG EVENTS' `SHOW MASTER STATUS' `SHOW MASTER LOGS' `SHOW SLAVE HOSTS' SQL Statements for Controlling Slave Servers `CHANGE MASTER TO' `LOAD DATA FROM MASTER' `LOAD TABLE tbl_name FROM MASTER' `MASTER_POS_WAIT()' `RESET SLAVE' `SET GLOBAL SQL_SLAVE_SKIP_COUNTER' `SHOW SLAVE STATUS' `START SLAVE' `STOP SLAVE' MySQL Full-text Search Full-text Restrictions Fine-tuning MySQL Full-text Search Full-text Search TODO MySQL Query Cache How the Query Cache Operates Query Cache Configuration Query Cache Options in `SELECT' Query Cache Status and Maintenance MySQL Table Types `MyISAM' Tables Space Needed for Keys `MyISAM' Table Formats Static (Fixed-length) Table Characteristics Dynamic Table Characteristics Compressed Table Characteristics `MyISAM' Table Problems Corrupted `MyISAM' Tables Clients is using or hasn't closed the table properly `MERGE' Tables `MERGE' Table Problems `HEAP' Tables `InnoDB' Tables InnoDB Tables Overview InnoDB in MySQL Version 3.23 InnoDB Startup Options Creating InnoDB Tablespace If Something Goes Wrong in Database Creation Creating InnoDB Tables Converting MyISAM Tables to InnoDB `FOREIGN KEY' Constraints Multiple tablespaces - putting each table into its own .ibd file Adding and Removing InnoDB Data and Log Files Backing up and Recovering an InnoDB Database Forcing recovery Checkpoints Moving an InnoDB Database to Another Machine InnoDB Transaction Model and Locking InnoDB and `SET ... TRANSACTION ISOLATION LEVEL ...' Consistent Non-Locking Read Locking Reads `SELECT ... FOR UPDATE' and `SELECT ... LOCK IN SHARE MODE' Next-key Locking: Avoiding the Phantom Problem Locks Set by Different SQL Statements in `InnoDB' Deadlock Detection and Rollback An Example of How the Consistent Read Works in `InnoDB' How to Cope With Deadlocks Performance Tuning Tips `SHOW INNODB STATUS' and the `InnoDB' Monitors Implementation of Multi-versioning Table and Index Structures Physical Structure of an Index Insert Buffering Adaptive Hash Indexes Physical Record Structure How an `AUTO_INCREMENT' Column Works in InnoDB File Space Management and Disk I/O Disk I/O File Space Management Defragmenting a Table Error Handling Restrictions on InnoDB Tables InnoDB Change History MySQL/InnoDB-5.0.0, December 24, 2003 MySQL/InnoDB-4.0.17, December 17, 2003 MySQL/InnoDB-4.1.1, December 4, 2003 MySQL/InnoDB-4.0.16, October 22, 2003 MySQL/InnoDB-3.23.58, September 15, 2003 MySQL/InnoDB-4.0.15, September 10, 2003 MySQL/InnoDB-4.0.14, July 22, 2003 MySQL/InnoDB-3.23.57, June 20, 2003 MySQL/InnoDB-4.0.13, May 20, 2003 MySQL/InnoDB-4.1.0, April 3, 2003 MySQL/InnoDB-3.23.56, March 17, 2003 MySQL/InnoDB-4.0.12, March 18, 2003 MySQL/InnoDB-4.0.11, February 25, 2003 MySQL/InnoDB-4.0.10, February 4, 2003 MySQL/InnoDB-3.23.55, January 24, 2003 MySQL/InnoDB-4.0.9, January 14, 2003 MySQL/InnoDB-4.0.8, January 7, 2003 MySQL/InnoDB-4.0.7, December 26, 2002 MySQL/InnoDB-4.0.6, December 19, 2002 MySQL/InnoDB-3.23.54, December 12, 2002 MySQL/InnoDB-4.0.5, November 18, 2002 MySQL/InnoDB-3.23.53, October 9, 2002 MySQL/InnoDB-4.0.4, October 2, 2002 MySQL/InnoDB-4.0.3, August 28, 2002 MySQL/InnoDB-3.23.52, August 16, 2002 MySQL/InnoDB-4.0.2, July 10, 2002 MySQL/InnoDB-3.23.51, June 12, 2002 MySQL/InnoDB-3.23.50, April 23, 2002 MySQL/InnoDB-3.23.49, February 17, 2002 MySQL/InnoDB-3.23.48, February 9, 2002 MySQL/InnoDB-3.23.47, December 28, 2001 MySQL/InnoDB-4.0.1, December 23, 2001 MySQL/InnoDB-3.23.46, November 30, 2001 MySQL/InnoDB-3.23.45, November 23, 2001 MySQL/InnoDB-3.23.44, November 2, 2001 MySQL/InnoDB-3.23.43, October 4, 2001 MySQL/InnoDB-3.23.42, September 9, 2001 MySQL/InnoDB-3.23.41, August 13, 2001 MySQL/InnoDB-3.23.40, July 16, 2001 MySQL/InnoDB-3.23.39, June 13, 2001 MySQL/InnoDB-3.23.38, May 12, 2001 `InnoDB' Contact Information `BDB' or `BerkeleyDB' Tables Overview of `BDB' Tables Installing `BDB' `BDB' Startup Options Characteristics of `BDB' Tables Things We Need to Fix for `BDB' in the Near Future Operating Systems Supported by `BDB' Restrictions on `BDB' Tables Errors That May Occur When Using `BDB' Tables `ISAM' Tables Introduction to MaxDB History of MaxDB Licensing and Support Basic Concepts of MaxDB Feature Differences between MaxDB and MySQL Interoperability Features between MaxDB and MySQL MaxDB-related Links Reserved Words in MaxDB Functions Column Types National Character Sets and Unicode Character Sets and Collations in General Character Sets and Collations in MySQL Determining the Default Character Set and Collation Server Character Set and Collation Database Character Set and Collation Table Character Set and Collation Column Character Set and Collation Examples of Character Set and Collation Assignment Connection Character Sets and Collations Character String Literal Character Set and Collation `COLLATE' Clause in Various Parts of an SQL Query `COLLATE' Clause Precedence `BINARY' Operator Some Special Cases Where the Collation Determination is Tricky Collations Must Be for the Right Character Set An example of the Effect of Collation Operations Affected by Character Set Support Result Strings `CONVERT()' `CAST()' `SHOW CHARACTER SET' `SHOW COLLATION' `SHOW CREATE DATABASE' `SHOW FULL COLUMNS' Unicode Support UTF8 for Metadata Compatibility with Other DBMSs New Character Set Configuration File format National Character Set Upgrading from MySQL 4.0 4.0 Character Sets and Corresponding 4.1 Character Set/Collation Pairs The Character Sets and Collations that MySQL Supports The Unicode Character Sets Platform Specific Character Sets Character Sets for South Europe and Middle East The Asian Character Sets The Baltic Character Sets The Cyrillic Character Sets The Central European Character Sets The West European Character Sets Spatial Extensions in MySQL Introduction The OpenGIS Geometry Model The Geometry Class Hierarchy Class `Geometry' Class `Point' Class `Curve' Class `LineString' Class `Surface' Class `Polygon' Class `GeometryCollection' Class `MultiPoint' Class `MultiCurve' Class `MultiLineString' Class `MultiSurface' Class `MultiPolygon' Supported Spatial Data Formats Well-Known Text (WKT) Format Well-Known Binary (WKB) Format Creating a Spatially Enabled MySQL Database MySQL Spatial Datatypes Creating Spatial Values Creating Geometry Values Using WKT Functions Creating Geometry Values Using WKB Functions Creating Geometry Values Using MySQL-Specific Functions Creating Spatial Columns Populating Spatial Columns Fetching Spatial Data Fetching Spatial Data in Internal Format Fetching Spatial Data in WKT Format Fetching Spatial Data in WKB Format Analyzing Spatial Information Geometry Format Conversion Functions `Geometry' Functions General Geometry Functions `Point' Functions `LineString' Functions `MultiLineString' Functions `Polygon' Functions `MultiPolygon' Functions `GeometryCollection' Functions Functions That Create New Geometries from Existing Ones Geometry Functions That Produce New Geometries Spatial Operators Functions for Testing Spatial Relations Between Geometric Objects Relations on Geometry Minimal Bounding Rectangles (MBRs) Functions That Test Spatial Relationships Between Geometries Optimizing Spatial Analysis Creating Spatial Indexes Using a Spatial Index MySQL Conformance and Compatibility GIS Features That Are Not Yet Implemented Stored Procedures and Functions Stored Procedure Syntax Maintaining Stored Procedures `CREATE PROCEDURE' and `CREATE FUNCTION' `ALTER PROCEDURE' and `ALTER FUNCTION' `DROP PROCEDURE' and `DROP FUNCTION' `SHOW CREATE PROCEDURE' and `SHOW CREATE FUNCTION' `SHOW PROCEDURE STATUS' and `SHOW FUNCTION STATUS' `CALL' `BEGIN ... END' Compound Statement `DECLARE' Statement Variables in Stored Procedures `DECLARE' Local Variables Variable `SET' Statement `SELECT ... INTO' Statement Conditions and Handlers `DECLARE' Conditions `DECLARE' Handlers Cursors Declaring Cursors Cursor `OPEN' Statement Cursor `FETCH' Statement Cursor `CLOSE' Statement Flow Control Constructs `IF' Statement `CASE' Statement `LOOP' Statement `LEAVE' Statement `ITERATE' Statement `REPEAT' Statement `WHILE' Statement MySQL APIs MySQL C API C API Datatypes C API Function Overview C API Function Descriptions `mysql_affected_rows()' `mysql_change_user()' `mysql_character_set_name()' `mysql_close()' `mysql_connect()' `mysql_create_db()' `mysql_data_seek()' `mysql_debug()' `mysql_drop_db()' `mysql_dump_debug_info()' `mysql_eof()' `mysql_errno()' `mysql_error()' `mysql_escape_string()' `mysql_fetch_field()' `mysql_fetch_fields()' `mysql_fetch_field_direct()' `mysql_fetch_lengths()' `mysql_fetch_row()' `mysql_field_count()' `mysql_field_seek()' `mysql_field_tell()' `mysql_free_result()' `mysql_get_client_info()' `mysql_get_client_version()' `mysql_get_host_info()' `mysql_get_proto_info()' `mysql_get_server_info()' `mysql_get_server_version()' `mysql_info()' `mysql_init()' `mysql_insert_id()' `mysql_kill()' `mysql_list_dbs()' `mysql_list_fields()' `mysql_list_processes()' `mysql_list_tables()' `mysql_num_fields()' `mysql_num_rows()' `mysql_options()' `mysql_ping()' `mysql_query()' `mysql_real_connect()' `mysql_real_escape_string()' `mysql_real_query()' `mysql_reload()' `mysql_row_seek()' `mysql_row_tell()' `mysql_select_db()' `mysql_set_server_option()' `mysql_shutdown()' `mysql_sqlstate()' `mysql_ssl_set()' `mysql_stat()' `mysql_store_result()' `mysql_thread_id()' `mysql_use_result()' `mysql_warning_count()' `mysql_commit()' `mysql_rollback()' `mysql_autocommit()' `mysql_more_results()' `mysql_next_result()' C API Prepared Statements C API Prepared Statement Datatypes C API Prepared Statement Function Overview C API Prepared Statement Function Descriptions `mysql_bind_param()' `mysql_bind_result()' `mysql_execute()' `mysql_fetch()' `mysql_fetch_column()' `mysql_get_metadata()' `mysql_param_count()' `mysql_param_result()' `mysql_prepare()' `mysql_send_long_data()' `mysql_stmt_affected_rows()' `mysql_stmt_close()' `mysql_stmt_data_seek()' `mysql_stmt_errno()' `mysql_stmt_error()' `mysql_stmt_free_result()' `mysql_stmt_num_rows()' `mysql_stmt_reset()' `mysql_stmt_row_seek()' `mysql_stmt_row_tell()' `mysql_stmt_sqlstate()' `mysql_stmt_store_result()' C API Handling of Multiple Query Execution C API Handling of Date and Time Values C API Threaded Function Descriptions `my_init()' `mysql_thread_init()' `mysql_thread_end()' `mysql_thread_safe()' C API Embedded Server Function Descriptions `mysql_server_init()' `mysql_server_end()' Common questions and problems when using the C API Why `mysql_store_result()' Sometimes Returns `NULL' After `mysql_query()' Returns Success What Results You Can Get from a Query How to Get the Unique ID for the Last Inserted Row Problems Linking with the C API Building Client Programs How to Make a Threaded Client libmysqld, the Embedded MySQL Server Library Overview of the Embedded MySQL Server Library Compiling Programs with `libmysqld' Restrictions when using the Embedded MySQL Server Using Option Files with the Embedded Server Things left to do in Embedded Server (TODO) A Simple Embedded Server Example Licensing the Embedded Server MySQL ODBC Support How to Install MyODBC How to Fill in the Various Fields in the ODBC Administrator Program Connect parameters for MyODBC How to Report Problems with MyODBC Programs Known to Work with MyODBC How to Get the Value of an `AUTO_INCREMENT' Column in ODBC Reporting Problems with MyODBC MySQL Java Connectivity (JDBC) MySQL PHP API Common Problems with MySQL and PHP MySQL Perl API MySQL C++ API Borland C++ MySQL Python API MySQL Tcl API MySQL Eiffel Wrapper Error Handling in MySQL Error Returns Extending MySQL MySQL Internals MySQL Threads MySQL Test Suite Running the MySQL Test Suite Extending the MySQL Test Suite Reporting Bugs in the MySQL Test Suite Adding New Functions to MySQL `CREATE FUNCTION/DROP FUNCTION' Syntax Adding a New User-defined Function UDF Calling Sequences for simple functions UDF Calling Sequences for aggregate functions Argument Processing Return Values and Error Handling Compiling and Installing User-defined Functions Adding a New Native Function Adding New Procedures to MySQL Procedure Analyse Writing a Procedure Problems and Common Errors How to Determine What Is Causing Problems Common Errors When Using MySQL `Access denied' Error `MySQL server has gone away' Error `Can't connect to [local] MySQL server' Error `Client does not support authentication protocol' error `Host '...' is blocked' Error `Too many connections' Error `Some non-transactional changed tables couldn't be rolled back' Error `Out of memory' Error `Packet too large' Error Communication Errors / Aborted Connection `The table is full' Error `Can't create/write to file' Error `Commands out of sync' Error in Client `Ignoring user' Error `Table 'xxx' doesn't exist' Error `Can't initialize character set xxx' error File Not Found Installation Related Issues Problems When Linking with the MySQL Client Library How to Run MySQL As a Normal User Problems with File Permissions Administration Related Issues What To Do If MySQL Keeps Crashing How to Reset a Forgotten Root Password How MySQL Handles a Full Disk Where MySQL Stores Temporary Files How to Protect or Change the MySQL Socket File `/tmp/mysql.sock' Time Zone Problems Query Related Issues Case-Sensitivity in Searches Problems Using `DATE' Columns Problems with `NULL' Values Problems with `alias' Deleting Rows from Related Tables Solving Problems with No Matching Rows Problems with Floating-Point Comparison Optimizer Related Issues How to avoid table scan,,, Table Definition Related Issues Problems with `ALTER TABLE'. How To Change the Order of Columns in a Table TEMPORARY TABLE problems Credits Developers at MySQL AB Contributors to MySQL Documenters and translators Libraries used by and included with MySQL Packages that support MySQL Tools that were used to create MySQL Supporters of MySQL MySQL Change History Changes in release 5.0.x (Development) Changes in release 5.0.1 (not released yet) Changes in release 5.0.0 (22 Dec 2003: Alpha) Changes in release 4.1.x (Alpha) Changes in release 4.1.2 (not released yet) Changes in release 4.1.1 (01 Dec 2003) Changes in release 4.1.0 (03 Apr 2003: Alpha) Changes in release 4.0.x (Production) Changes in release 4.0.19 (not released yet) Changes in release 4.0.18 (to be released soon) Changes in release 4.0.17 (14 Dec 2003) Changes in release 4.0.16 (17 Oct 2003) Changes in release 4.0.15 (03 Sep 2003) Changes in release 4.0.14 (18 Jul 2003) Changes in release 4.0.13 (16 May 2003) Changes in release 4.0.12 (15 Mar 2003: Production) Changes in release 4.0.11 (20 Feb 2003) Changes in release 4.0.10 (29 Jan 2003) Changes in release 4.0.9 (09 Jan 2003) Changes in release 4.0.8 (07 Jan 2003) Changes in release 4.0.7 (20 Dec 2002) Changes in release 4.0.6 (14 Dec 2002: Gamma) Changes in release 4.0.5 (13 Nov 2002) Changes in release 4.0.4 (29 Sep 2002) Changes in release 4.0.3 (26 Aug 2002: Beta) Changes in release 4.0.2 (01 Jul 2002) Changes in release 4.0.1 (23 Dec 2001) Changes in release 4.0.0 (Oct 2001: Alpha) Changes in release 3.23.x (Recent; still supported) Changes in release 3.23.59 (not released yet) Changes in release 3.23.58 (11 Sep 2003) Changes in release 3.23.57 (06 Jun 2003) Changes in release 3.23.56 (13 Mar 2003) Changes in release 3.23.55 (23 Jan 2003) Changes in release 3.23.54 (05 Dec 2002) Changes in release 3.23.53 (09 Oct 2002) Changes in release 3.23.52 (14 Aug 2002) Changes in release 3.23.51 (31 May 2002) Changes in release 3.23.50 (21 Apr 2002) Changes in release 3.23.49 Changes in release 3.23.48 (07 Feb 2002) Changes in release 3.23.47 (27 Dec 2001) Changes in release 3.23.46 (29 Nov 2001) Changes in release 3.23.45 (22 Nov 2001) Changes in release 3.23.44 (31 Oct 2001) Changes in release 3.23.43 (04 Oct 2001) Changes in release 3.23.42 (08 Sep 2001) Changes in release 3.23.41 (11 Aug 2001) Changes in release 3.23.40 Changes in release 3.23.39 (12 Jun 2001) Changes in release 3.23.38 (09 May 2001) Changes in release 3.23.37 (17 Apr 2001) Changes in release 3.23.36 (27 Mar 2001) Changes in release 3.23.35 (15 Mar 2001) Changes in release 3.23.34a Changes in release 3.23.34 (10 Mar 2001) Changes in release 3.23.33 (09 Feb 2001) Changes in release 3.23.32 (22 Jan 2001: Production) Changes in release 3.23.31 (17 Jan 2001) Changes in release 3.23.30 (04 Jan 2001) Changes in release 3.23.29 (16 Dec 2000) Changes in release 3.23.28 (22 Nov 2000: Gamma) Changes in release 3.23.27 (24 Oct 2000) Changes in release 3.23.26 (18 Oct 2000) Changes in release 3.23.25 (29 Sep 2000) Changes in release 3.23.24 (08 Sep 2000) Changes in release 3.23.23 (01 Sep 2000) Changes in release 3.23.22 (31 Jul 2000) Changes in release 3.23.21 Changes in release 3.23.20 Changes in release 3.23.19 Changes in release 3.23.18 Changes in release 3.23.17 Changes in release 3.23.16 Changes in release 3.23.15 (May 2000: Beta) Changes in release 3.23.14 Changes in release 3.23.13 Changes in release 3.23.12 (07 Mar 2000) Changes in release 3.23.11 Changes in release 3.23.10 Changes in release 3.23.9 Changes in release 3.23.8 (02 Jan 2000) Changes in release 3.23.7 (10 Dec 1999) Changes in release 3.23.6 Changes in release 3.23.5 (20 Oct 1999) Changes in release 3.23.4 (28 Sep 1999) Changes in release 3.23.3 Changes in release 3.23.2 (09 Aug 1999) Changes in release 3.23.1 Changes in release 3.23.0 (05 Aug 1999: Alpha) Changes in release 3.22.x (Old; discontinued) Changes in release 3.22.35 Changes in release 3.22.34 Changes in release 3.22.33 Changes in release 3.22.32 (14 Feb 2000) Changes in release 3.22.31 Changes in release 3.22.30 Changes in release 3.22.29 (02 Jan 2000) Changes in release 3.22.28 (20 Oct 1999) Changes in release 3.22.27 Changes in release 3.22.26 (16 Sep 1999) Changes in release 3.22.25 Changes in release 3.22.24 (05 Jul 1999) Changes in release 3.22.23 (08 Jun 1999) Changes in release 3.22.22 (30 Apr 1999) Changes in release 3.22.21 Changes in release 3.22.20 (18 Mar 1999) Changes in release 3.22.19 (Mar 1999: Production) Changes in release 3.22.18 Changes in release 3.22.17 Changes in release 3.22.16 (Feb 1999: Gamma) Changes in release 3.22.15 Changes in release 3.22.14 Changes in release 3.22.13 Changes in release 3.22.12 Changes in release 3.22.11 Changes in release 3.22.10 Changes in release 3.22.9 Changes in release 3.22.8 Changes in release 3.22.7 (Sep 1998: Beta) Changes in release 3.22.6 Changes in release 3.22.5 Changes in release 3.22.4 Changes in release 3.22.3 Changes in release 3.22.2 Changes in release 3.22.1 (Jun 1998: Alpha) Changes in release 3.22.0 Changes in release 3.21.x Changes in release 3.21.33 Changes in release 3.21.32 Changes in release 3.21.31 Changes in release 3.21.30 Changes in release 3.21.29 Changes in release 3.21.28 Changes in release 3.21.27 Changes in release 3.21.26 Changes in release 3.21.25 Changes in release 3.21.24 Changes in release 3.21.23 Changes in release 3.21.22 Changes in release 3.21.21a Changes in release 3.21.21 Changes in release 3.21.20 Changes in release 3.21.19 Changes in release 3.21.18 Changes in release 3.21.17 Changes in release 3.21.16 Changes in release 3.21.15 Changes in release 3.21.14b Changes in release 3.21.14a Changes in release 3.21.13 Changes in release 3.21.12 Changes in release 3.21.11 Changes in release 3.21.10 Changes in release 3.21.9 Changes in release 3.21.8 Changes in release 3.21.7 Changes in release 3.21.6 Changes in release 3.21.5 Changes in release 3.21.4 Changes in release 3.21.3 Changes in release 3.21.2 Changes in release 3.21.0 Changes in release 3.20.x Changes in release 3.20.18 Changes in release 3.20.17 Changes in release 3.20.16 Changes in release 3.20.15 Changes in release 3.20.14 Changes in release 3.20.13 Changes in release 3.20.11 Changes in release 3.20.10 Changes in release 3.20.9 Changes in release 3.20.8 Changes in release 3.20.7 Changes in release 3.20.6 Changes in release 3.20.3 Changes in release 3.20.0 Changes in release 3.19.x Changes in release 3.19.5 Changes in release 3.19.4 Changes in release 3.19.3 Porting to Other Systems Debugging a MySQL server Compiling MYSQL for Debugging Creating Trace Files Debugging mysqld under gdb Using a Stack Trace Using Log Files to Find Cause of Errors in mysqld Making a Test Case If You Experience Table Corruption Debugging a MySQL client The DBUG Package Locking methods Comments about RTS threads Differences between different thread packages Environment Variables MySQL Regular Expressions GNU General Public License SQL command, type and function index Concept Index This is the Reference Manual for the `MySQL Database System'. This version refers to the 4.0.18 version of `MySQL Server' but it is also applicable for any older version (such as 3.23 and 4.0-production) as changes are always indicated. There are also references for version 5.0 (development). General Information ******************* The `MySQL' (R) software delivers a very fast, multi-threaded, 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. `MySQL' is a trademark of `MySQL AB'. The `MySQL' software is `Dual Licensed'. Users can choose to use the `MySQL' software as an `Open Source'/`Free Software' product under the terms of the `GNU General Public License' (`http://www.fsf.org/licenses/') or can purchase a standard commercial license from `MySQL AB'. *Note Licensing and Support::. The `MySQL' web site (`http://www.mysql.com/') provides the latest information about the `MySQL' software. The following list describes some sections of particular interest in this manual: * For information about the company behind the `MySQL Database Server', see *Note What is MySQL AB::. * For a discussion about the capabilities of the `MySQL Database Server', see *Note Features::. * For installation instructions, see *Note Installing::. * For tips on porting the `MySQL Database Software' to new architectures or operating systems, see *Note Porting::. * For information about upgrading from a Version 4.0 release, see *Note Upgrading-from-4.0::. * For information about upgrading from a Version 3.23 release, see *Note Upgrading-from-3.23::. * For information about upgrading from a Version 3.22 release, see *Note Upgrading-from-3.22::. * For a tutorial introduction to the `MySQL Database Server', see *Note Tutorial::. * For examples of `SQL' and benchmarking information, see the benchmarking directory (`sql-bench' in the distribution). * For a history of new features and bug fixes, see *Note News::. * For a list of currently known bugs and misfeatures, see *Note Bugs::. * For future plans, see *Note TODO::. * For a list of all the contributors to this project, see *Note Credits::. *Important*: Reports of errors (often called bugs), as well as questions and comments, should be sent to the general MySQL mailing list. *Note Mailing-list::. *Note Bug reports::. The `mysqlbug' script should be used to generate bug reports on Unix. (Windows distributions contain a file `mysqlbug.txt' in the base directory that can be used as a template for a bug report.) For source distributions, the `mysqlbug' script can be found in the `scripts' directory. For binary distributions, `mysqlbug' can be found in the `bin' directory (`/usr/bin' for the `MySQL-server' RPM package). If you have found a sensitive security bug in `MySQL Server', please let us know immediately by sending an email message to . About This Manual ================= This is the `MySQL' reference manual; it documents `MySQL' up to Version 4.0.18. Functional changes are always indicated with reference to the version, so this manual is also suitable if you are using an older version of the `MySQL' software (such as 3.23 or 4.0-production). There are also references for version 5.0 (development). Being a reference manual, it does not provide general instruction on `SQL' or relational database concepts. It also will not teach you how to use your operating system or command line interpreter. As the `MySQL Database Software' is under constant development, the manual is also updated frequently. The most recent version of this manual is available at `http://www.mysql.com/documentation/' in many different formats, including HTML, PDF, and Windows HLP versions. The primary document is the Texinfo file. The HTML version is produced automatically using a modified version of `texi2html'. The plain text and Info versions are produced with `makeinfo'. The PostScript version is produced using `texi2dvi' and `dvips'. The PDF version is produced with `pdftex'. The index can assist you in finding information in the manual. For online use, you can try the searchable version of the manual available at `http://www.mysql.com/doc/'. If you have any suggestions concerning additions or corrections to this manual, please send them to the documentation team at . This manual was initially written by David Axmark and Michael (Monty) Widenius. It is now maintained by the MySQL Documentation Team, consisting of Arjen Lentz, Paul DuBois and Stefan Hinz. For the many other contributors, see *Note Credits::. The copyright (2004) to this manual is owned by the Swedish company `MySQL AB'. *Note Copyright::. Conventions Used in This Manual ------------------------------- This manual uses certain typographical conventions: `constant' Constant-width font is used for command names and options; SQL statements; database, table, and column names; C and Perl code; and environment variables. Example: "To see how `mysqladmin' works, invoke it with the `--help' option." `filename' Constant-width font with surrounding quotes is used for filenames and pathnames. Example: "The distribution is installed under the `/usr/local/' directory." `c' Constant-width font with surrounding quotes is also used to indicate character sequences. Example: "To specify a wildcard, use the `%' character." _italic_ Italic font is used for emphasis, _like this_. *boldface* Boldface font is used in table headings and to convey *especially strong emphasis*. When commands are shown that are meant to be executed by a particular program, the program is indicated by a prompt shown before the command. For example, `shell>' indicates a command that you execute from your login shell, and `mysql>' indicates a statement that you execute from the `mysql' client program: shell> type a shell command here mysql> type a mysql statement here The "shell" is your command interpreter. On Unix, this is typically a program such as `sh' or `csh'. On Windows, the equivalent is `command.com' or `cmd.exe', typically run in a Windows console. Note that to enter a command or statement from an example, you do not type the prompt shown in the example. Commands to set shell variables are shown using Bourne shell syntax. If you are using `csh' or `tcsh', you will need to issue commands somewhat differently. For example, the sequence to set an environment variable and run a command looks like this in Bourne shell syntax: shell> VARNAME=value some_command For `csh' or `tcsh', you would execute the sequence like this: shell> setenv VARNAME value shell> some_command Database, table, and column names must often be substituted into commands. 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 uppercase or lowercase. This manual uses uppercase. In syntax descriptions, square brackets (`[' and `]') are used to indicate optional words or clauses. For example, in the following statement, `IF EXISTS' is optional: 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, `INSERT ... SELECT' is shorthand for the form of `INSERT' statement that is followed by a `SELECT' 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] ... Overview of the MySQL Database Management System ================================================ `MySQL', the most popular `Open Source' SQL database management system, is developed, distributed, and supported by `MySQL AB'. `MySQL AB' is a commercial company, founded by the MySQL developers, that builds its business by providing services around the `MySQL' database management system. *Note What is MySQL AB::. The `MySQL' web site (`http://www.mysql.com/') provides the latest information about `MySQL' software and `MySQL AB'. `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 stand-alone utilities or as parts of other applications. MySQL is a relational database management system. A relational database stores data in separate tables rather than putting all the data in one big storeroom. This adds speed and flexibility. The `SQL' part of "`MySQL'" stands for "`Structured Query Language'". SQL is the most common standardized language used to access databases and 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-99'" refers to the standard released in 1999, and "`SQL:2003'" refers to the next version of the standard. We use the term "`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. *Note MySQL licenses::. Why use the MySQL Database Server? The `MySQL Database Server' is very fast, reliable, and easy to use. If that is what you are looking for, you should give it a try. `MySQL Server' also has a practical set of features developed in close cooperation with our users. You can find a performance comparison of `MySQL Server' with other database managers on our benchmark page. *Note MySQL Benchmarks::. `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. Though 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. The technical features of MySQL Server The `MySQL Database Software' is a client/server system that consists of a multi-threaded `SQL' server that supports different backends, several different client programs and libraries, administrative tools, and a wide range of application programming interfaces (APIs). We also provide `MySQL Server' as a multi-threaded library which you can link into your application to get a smaller, faster, easier-to-manage product. There is a large amount of contributed MySQL software available. It is very likely that you will find that your favorite application or language already supports the `MySQL Database Server'. The official way to pronounce `MySQL' is "My Ess Que Ell" (not "my sequel"), but we don't mind if you pronounce it as "my sequel" or in some other localized way. History of MySQL ---------------- We started out with the intention of using `mSQL' 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 allow third-party code that was written for use with `mSQL' to be ported easily for use with `MySQL'. The derivation of the name `MySQL' is not clear. Our base directory and a large number of our libraries and tools have had the prefix "my" for well over 10 years. However, co-founder Monty Widenius's daughter is also named My. Which of the two gave its name to `MySQL' is still a mystery, even for us. The name of the MySQL Dolphin (our logo) is `Sakila'. `Sakila' was chosen by the founders of MySQL AB from a huge list of names suggested by users in our "Name the Dolphin" contest. The winning name was submitted by Ambrose Twebaze, an open source software developer from Swaziland, Africa. According to Ambrose, the 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. The Main Features of MySQL -------------------------- The following list describes some of the important characteristics of the `MySQL Database Software'. *Note MySQL 4.0 Nutshell::. Internals and Portability * Written in C and C++. * Tested with a broad range of different compilers. * Works on many different platforms. *Note Which OS::. * Uses GNU Automake, Autoconf, and Libtool for portability. * APIs for C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl are available. *Note Clients::. * Fully multi-threaded using kernel threads. This means it can easily use multiple CPUs if they are available. * Provides transactional and non-transactional storage engines. * Uses very fast B-tree disk tables (`MyISAM') with index compression. * Relatively easy to add another storage engine. This is useful if you want to add an SQL interface to an in-house database. * A very fast thread-based memory allocation system. * Very fast joins using an optimized one-sweep multi-join. * In-memory hash tables which are used as temporary tables. * SQL functions are implemented using a highly optimized class library and should be as fast as possible. Usually there is no memory allocation at all after query initialization. * The `MySQL' code is tested with Purify (a commercial memory leakage detector) as well as with Valgrind, a `GPL' tool (`http://developer.kde.org/~sewardj/'). * The server is available as a separate program for use in a client/server networked environment. It is also available 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. Column Types * Many column types: signed/unsigned integers 1, 2, 3, 4, and 8 bytes long, `FLOAT', `DOUBLE', `CHAR', `VARCHAR', `TEXT', `BLOB', `DATE', `TIME', `DATETIME', `TIMESTAMP', `YEAR', `SET', `ENUM', and OpenGIS geometry types. *Note Column types::. * Fixed-length and variable-length records. Commands and Functions * Full operator and function support in the `SELECT' and `WHERE' clauses of queries. For example: mysql> SELECT CONCAT(first_name, ' ', last_name) -> FROM tbl_name -> WHERE income/dependents > 10000 AND age > 30; * Full support for SQL `GROUP BY' and `ORDER BY' clauses. Support for group functions (`COUNT()', `COUNT(DISTINCT ...)', `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 SQL-92. * `DELETE', `INSERT', `REPLACE', and `UPDATE' return the number of rows that were changed (affected). It is possible to return the number of rows matched instead by setting a flag when connecting to the server. * The `MySQL'-specific `SHOW' command can be used to retrieve information about databases, tables, and indexes. The `EXPLAIN' command can be used to determine how the optimizer resolves a query. * Function names do not clash with table or column names. For example, `ABS' is a valid column name. The only restriction is that for a function call, no spaces are allowed between the function name and the `(' that follows it. *Note Reserved words::. * You can mix tables from different databases in the same query (as of Version 3.22). Security * A privilege and password system that is very flexible and secure, and allows host-based verification. Passwords are secure because all password traffic is encrypted when you connect to a server. Scalability and Limits * Handles large databases. We use `MySQL Server' with databases that contain 50 million records. We also know of users that use `MySQL Server' with 60,000 tables and about 5,000,000,000 rows. * Up to 32 indexes per table are allowed. Each index may consist of 1 to 16 columns or parts of columns. The maximum index width is 500 bytes (this may be changed when compiling `MySQL Server'). An index may use a prefix of a `CHAR' or `VARCHAR' column. Connectivity * Clients may connect to the `MySQL' server using TCP/IP sockets on any platform. On Windows systems in the NT family (NT, 2000, or XP), clients may connect using named pipes. On Unix systems, clients may connect using Unix domain socket files. * The Connector/ODBC 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 may be run on Windows or Unix. Connector/ODBC source is available. All ODBC 2.5 functions are supported, as are many others. *Note ODBC::. * The Connector/JDBC interface provides `MySQL' support for Java client programs that use JDBC connections. Clients may be run on Windows or Unix. Connector/JDBC source is available. *Note Java::. Localization * The server can provide error messages to clients in many languages. *Note Languages::. * Full support for several different character sets, including ISO-8859-1 (Latin1), german, big5, ujis, and more. For example, the Scandinavian characters `a^', `a"' and `o"' are allowed in table and column names. Unicode support is available as of `MySQL' 4.1. * All data is saved in the chosen character set. All comparisons for normal string columns are case-insensitive. * Sorting is done according to the chosen character set (the Swedish way by default). It is possible to change this when the `MySQL' server is started. 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 and runtime. Clients and Tools * The 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. *Note MySQL Database Administration::. * All `MySQL' programs can be invoked with the `--help' or `-?' options to obtain online assistance. MySQL Stability --------------- This section addresses the questions "_How stable is MySQL Server?_" and "_Can I depend on MySQL Server in this project?_" We will try to clarify these issues and answer some important questions that concern many potential users. The information in this section is based on data gathered from the mailing list, which is very active in identifying problems as well as reporting types of use. The original code stems back to the early 1980s. It provides a stable code base, and the ISAM table format used by the original storage engine remains backward-compatible. At TcX, the predecessor of `MySQL AB', `MySQL' code has worked in projects since mid-1996, without any problems. When the `MySQL Database Software' initially was released to a wider public, our new users quickly found some pieces of "untested code". Each new release since then has had fewer portability problems (even though each new release has also had many new features). Each release of the `MySQL Server' has been usable. Problems have occurred only when users try code from the "gray zones." Naturally, new users don't know what the gray zones are; this section therefore attempts to document those areas that are currently known. The descriptions mostly deal with Version 3.23 and 4.0 of `MySQL Server'. All known and reported bugs are fixed in the latest version, with the exception of those listed in the bugs section, which are design-related. *Note Bugs::. The `MySQL Server' design is multi-layered with independent modules. Some of the newer modules are listed here with an indication of how well-tested each of them is: *Replication -- Gamma* Large groups of servers using replication are in production use, with good results. Work on enhanced replication features is continuing in `MySQL' 5.x. *`InnoDB' tables -- Stable (in 3.23 from 3.23.49)* The `InnoDB' transactional storage engine has been declared stable in the `MySQL' 3.23 tree, starting from version 3.23.49. `InnoDB' is being used in large, heavy-load production systems. *`BDB' tables -- Gamma* The `Berkeley DB' code is very stable, but we are still improving the `BDB' transactional storage engine interface in `MySQL Server', so it will take some time before this is as well tested as the other table types. *Full-text searches -- Beta* Full-text searching works but is not yet widely used. Important enhancements have been implemented in `MySQL' 4.0. *`Connector/ODBC 3.51' (uses ODBC SDK 3.51) -- Stable* In wide production use. Some issues brought up appear to be application-related and independent of the ODBC driver or underlying database server. *Automatic recovery of `MyISAM' tables -- Gamma* This status applies only to the new code in the `MyISAM' storage engine that checks if the table was closed properly on open and executes an automatic check/repair of the table if it wasn't. *Bulk-insert -- Alpha* New feature in `MyISAM' tables in `MySQL' 4.0 for faster insert of many rows. *Locking -- Gamma* This is very system-dependent. On some systems there are big problems using standard operating system locking (`fcntl()'). In these cases, you should run `mysqld' with the `--skip-external-locking' flag. Problems are known to occur on some Linux systems, and on SunOS when using NFS-mounted filesystems. Paying customers receive high-quality support directly from MySQL AB. MySQL AB also provides the MySQL mailing list as a community resource where anyone may ask questions. Bugs are usually fixed right away with a patch. For serious bugs, there is almost always a new release. How Big MySQL Tables Can Be --------------------------- `MySQL' Version 3.22 had a 4 GB (4 gigabyte) limit on table size. With the `MyISAM' storage engine in `MySQL' Version 3.23, the maximum table size was increased to 8 million terabytes (2 ^ 63 bytes). With this larger allowed table size, the maximum effective table size for `MySQL' databases now normally is determined by operating system constraints on file sizes, not by MySQL internal limits. The `InnoDB' storage engine maintains `InnoDB' tables within a tablespace that can be created from several files. This allows a table to exceed the maximum individual file size. The tablespace can include raw disk partitions, which allows extremely large tables. The maximum tablespace size is 64 TB. The following table lists some examples of operating system file-size limits: *Operating System* *File-Size Limit* Linux-Intel 32-bit 2 GB, much more when using LFS Linux-Alpha 8 TB (?) Solaris 2.5.1 2 GB (4GB possible with patch) Solaris 2.6 4 GB (can be changed with flag) Solaris 2.7 Intel 4 GB Solaris 2.7 512 GB UltraSPARC On Linux 2.2, you can get `MyISAM' tables larger than 2 GB in size by using the LFS patch for the ext2 filesystem. On Linux 2.4, patches also exist for ReiserFS to get support for big files. Most current Linux distributions are based on kernel 2.4 and already include all the required Large File Support (LFS) patches. However, the maximum available file size still depends on several factors, one of them being the file system used to store MySQL tables. For a very detailed overview about LFS in Linux, have a look at Andreas Jaeger's "Large File Support in Linux" page at . By default, `MySQL' creates `MyISAM' tables with an internal structure that allows a maximum size of about 4 GB. You can check the maximum table size for a table with the `SHOW TABLE STATUS' command or with the `myisamchk -dv table_name'. *Note `SHOW': SHOW. If you need a `MyISQM' table that will be larger than 4 GB in size (and your operating system supports large files), the `CREATE TABLE' statement allows `AVG_ROW_LENGTH' and `MAX_ROWS' options. *Note `CREATE TABLE': CREATE TABLE. You can also change these options with `ALTER TABLE' after the table has been created, to increase the table's maximum allowable size. *Note `ALTER TABLE': ALTER TABLE. Other ways to work around file-size limits for `MyISAM' tables are as follows: * If your large table is read-only, you can use `myisampack' to compress it. `myisampack' usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. `myisampack' also can merge multiple tables into a single table. *Note `myisampack': myisampack. * Another way to get around the operating system file limit for `MyISAM' datafiles is by using the `RAID' options. *Note `CREATE TABLE': CREATE TABLE. * `MySQL' includes a `MERGE' library that allows you to handle a collection of `MyISAM' tables that have identical structure as a single `MERGE' table. *Note `MERGE' tables: MERGE. Year 2000 Compliance -------------------- The `MySQL Server' itself has no problems with Year 2000 (Y2K) compliance: * `MySQL Server' uses Unix time functions that handle dates into the year `2037' for `TIMESTAMP' values. For `DATE' and `DATETIME' values, dates through the year `9999' are accepted. * All `MySQL' date functions are implemented in one source file, `sql/time.cc', and are coded very carefully to be year 2000-safe. * In `MySQL' Version 3.22 and later, the `YEAR' column type can store years `0' and `1901' to `2155' in one byte and display them using two or four digits. All 2-digit years are considered to be in the range `1970' to `2069', which means that if you store `01' in a `YEAR' column, `MySQL Server' treats it as `2001'. The following simple demonstration illustrates that `MySQL Server' doesn't have any problems with dates until after the year 2030: mysql> DROP TABLE IF EXISTS y2k; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE y2k (date DATE, -> date_time DATETIME, -> time_stamp TIMESTAMP); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO y2k VALUES -> ('1998-12-31','1998-12-31 23:59:59',19981231235959), -> ('1999-01-01','1999-01-01 00:00:00',19990101000000), -> ('1999-09-09','1999-09-09 23:59:59',19990909235959), -> ('2000-01-01','2000-01-01 00:00:00',20000101000000), -> ('2000-02-28','2000-02-28 00:00:00',20000228000000), -> ('2000-02-29','2000-02-29 00:00:00',20000229000000), -> ('2000-03-01','2000-03-01 00:00:00',20000301000000), -> ('2000-12-31','2000-12-31 23:59:59',20001231235959), -> ('2001-01-01','2001-01-01 00:00:00',20010101000000), -> ('2004-12-31','2004-12-31 23:59:59',20041231235959), -> ('2005-01-01','2005-01-01 00:00:00',20050101000000), -> ('2030-01-01','2030-01-01 00:00:00',20300101000000), -> ('2050-01-01','2050-01-01 00:00:00',20500101000000); Query OK, 13 rows affected (0.01 sec) Records: 13 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM y2k; +------------+---------------------+----------------+ | date | date_time | time_stamp | +------------+---------------------+----------------+ | 1998-12-31 | 1998-12-31 23:59:59 | 19981231235959 | | 1999-01-01 | 1999-01-01 00:00:00 | 19990101000000 | | 1999-09-09 | 1999-09-09 23:59:59 | 19990909235959 | | 2000-01-01 | 2000-01-01 00:00:00 | 20000101000000 | | 2000-02-28 | 2000-02-28 00:00:00 | 20000228000000 | | 2000-02-29 | 2000-02-29 00:00:00 | 20000229000000 | | 2000-03-01 | 2000-03-01 00:00:00 | 20000301000000 | | 2000-12-31 | 2000-12-31 23:59:59 | 20001231235959 | | 2001-01-01 | 2001-01-01 00:00:00 | 20010101000000 | | 2004-12-31 | 2004-12-31 23:59:59 | 20041231235959 | | 2005-01-01 | 2005-01-01 00:00:00 | 20050101000000 | | 2030-01-01 | 2030-01-01 00:00:00 | 20300101000000 | | 2050-01-01 | 2050-01-01 00:00:00 | 00000000000000 | +------------+---------------------+----------------+ 13 rows in set (0.00 sec) The final `TIMESTAMP' column value is zero because the final year (`2050') exceeds the `TIMESTAMP' maximum. The `TIMESTAMP' datatype, which is used to store the current time, supports values that range from `19700101000000' to `20300101000000' on 32-bit machines (signed value). On 64-bit machines, `TIMESTAMP' handles values up to `2106' (unsigned value). The example also shows that the `DATE' and `DATETIME' datatypes have no problems with the dates used. They handle dates through the year `9999'. Although `MySQL Server' itself is Y2K-safe, you may run into problems if you use it with applications that are not Y2K-safe. For example, many old applications store or manipulate years using 2-digit values (which are ambiguous) rather than 4-digit values. This problem may be compounded by applications that use values such as `00' or `99' as "missing" value indicators. Unfortunately, these problems may be difficult to fix because different applications may be written by different programmers, each of whom may use a different set of conventions and date-handling functions. Thus, even though `MySQL Server' has no Y2K problems, it is the application's responsibility to provide unambiguous input. See *Note Y2K issues:: for `MySQL Server''s rules for dealing with ambiguous date input data that contains 2-digit year values. Overview of MySQL AB ==================== `MySQL AB' is the company of the `MySQL' founders and main developers. `MySQL AB' was originally established in Sweden by David Axmark, Allan Larsson, and Michael "Monty" Widenius. The developers of the `MySQL' server are all employed by the company. We are a virtual organization with people in a dozen countries around the world. We communicate extensively over the Internet every day with one another and with our users, supporters, and partners. We are dedicated to developing the `MySQL' database software and promoting it to new users. `MySQL AB' owns the copyright to the `MySQL' source code, the `MySQL' logo and trademark, and this manual. *Note What-is::. The `MySQL' core values show our dedication to `MySQL' and `Open Source'. We want the `MySQL Database Software' to be: * The best and the most widely used database in the world * Available to, and affordable by all * Easy to use * Continuously improving while remaining fast and safe * Fun to use and improve * Free from bugs `MySQL AB' and the people at `MySQL AB': * Promote `Open Source' philosophy and support the `Open Source' community * Aim to be good citizens * Prefer partners that share our values and mind-set * Answer email and provide support * Are a virtual company, networking with others * Work against software patents The `MySQL' web site (`http://www.mysql.com/') provides the latest information about `MySQL' and `MySQL AB'. By the way, the "AB" part of the company name is the acronym for the Swedish "aktiebolag", or "stock company." It translates to "MySQL, Inc." In fact, MySQL Inc. and MySQL GmbH are examples of MySQL AB subsidiaries. They are located in the US and Germany, respectively. The Business Model and Services of MySQL AB ------------------------------------------- One of the most common questions we encounter is: "_How can you make a living from something you give away for free?_" This is how: * `MySQL AB' makes money on support, services, commercial licenses, and royalties. * We use these revenues to fund product development and to expand the `MySQL' business. The company has been profitable since its inception. In October 2001, we accepted venture financing from leading Scandinavian investors and a handful of business angels. This investment is used to solidify our business model and build a basis for sustainable growth. Support ....... `MySQL AB' is run and owned by the founders and main developers of the `MySQL' database. The developers are committed to providing support to customers and other users in order to stay in touch with their needs and problems. All our support is provided by qualified developers. Really tricky questions are answered by Michael `Monty' Widenius, principal author of the `MySQL Server'. *Note Support::. For more information and ordering support at various levels, see `http://www.mysql.com/support/' or contact our sales staff at . Training and Certification .......................... `MySQL AB' delivers `MySQL' and related training worldwide. We offer both open courses and in-house courses tailored to the specific needs of your company. `MySQL Training' is also available through our partners, the `Authorized MySQL Training Centers'. Our training material uses the same example databases used in our documentation and our sample applications, and is always updated to reflect the latest `MySQL' version. Our trainers are backed by the development team to guarantee the quality of the training and the continuous development of the course material. This also ensures that no questions raised during the courses remain unanswered. Attending our training courses will enable you to achieve your `MySQL' application goals. You will also: * Save time. * Improve the performance of your applications. * Reduce or eliminate the need for additional hardware, decreasing cost. * Enhance security. * Increase customers' and co-workers' satisfaction. * Prepare yourself for `MySQL Certification'. If you are interested in our training as a potential participant or as a training partner, please visit the training section at `http://www.mysql.com/training/' or contact us at: . For details about the `MySQL Certification Program', please see `http://www.mysql.com/certification/'. Consulting .......... `MySQL AB' and its `Authorized Partners' offer consulting services to users of `MySQL Server' and to those who embed `MySQL Server' in their own software, all over the world. Our consultants can help you design and tune your databases, construct efficient queries, tune your platform for optimal performance, resolve migration issues, set up replication, build robust transactional applications, and more. We also help customers embed `MySQL Server' in their products and applications for large-scale deployment. Our consultants work in close collaboration with our development team, which ensures the technical quality of our professional services. Consulting assignments range from 2-day power-start sessions to projects that span weeks and months. Our expertise not only covers `MySQL Server'--it also extends into programming and scripting languages such as PHP, Perl, and more. If you are interested in our consulting services or want to become a consulting partner, please visit the consulting section of our web site at `http://www.mysql.com/consulting/' or contact our consulting staff at . Commercial Licenses ................... The `MySQL' database is released under the `GNU General Public License' (`GPL'). This means that the `MySQL' software can be used free of charge under the `GPL'. If you do not want to be bound by the `GPL' terms (such as the requirement that your application must also be `GPL'), you may purchase a commercial license for the same product from `MySQL AB'; see `http://www.mysql.com/products/pricing.html'. Since `MySQL AB' owns the copyright to the `MySQL' source code, we are able to employ `Dual Licensing', which means that the same product is available under `GPL' and under a commercial license. This does not in any way affect the `Open Source' commitment of `MySQL AB'. For details about when a commercial license is required, please see *Note MySQL licenses::. We also sell commercial licenses of third-party `Open Source GPL' software that adds value to `MySQL Server'. A good example is the `InnoDB' transactional storage engine that offers `ACID' support, row-level locking, crash recovery, multi-versioning, foreign key support, and more. *Note InnoDB::. Partnering .......... `MySQL AB' has a worldwide partner program that covers training courses, consulting and support, publications, plus reselling and distributing `MySQL' and related products. `MySQL AB Partners' get visibility on the `http://www.mysql.com/' web site and the right to use special versions of the `MySQL' trademarks to identify their products and promote their business. If you are interested in becoming a `MySQL AB Partner', please email . The word `MySQL' and the `MySQL' dolphin logo are trademarks of `MySQL AB'. *Note MySQL AB Logos and Trademarks::. These trademarks represent a significant value that the `MySQL' founders have built over the years. The `MySQL' web site (`http://www.mysql.com/') is popular among developers and users. In December 2003, we served 16 million page views. Our visitors represent a group that makes purchase decisions and recommendations for both software and hardware. Twelve percent of our visitors authorize purchase decisions, and only nine percent are not involved in purchase decisions at all. More than 65% have made one or more online business purchases within the last half-year, and 70% plan to make one in the next few months. Contact Information ------------------- The `MySQL' web site (`http://www.mysql.com/') provides the latest information about `MySQL' and `MySQL AB'. For press services and inquiries not covered in our News releases (`http://www.mysql.com/news/'), please send email to . If you have a valid support contract with `MySQL AB', you will get timely, precise answers to your technical questions about the `MySQL' software. For more information, see *Note Support::. On our web site, see `http://www.mysql.com/support/', or send an email message to . For information about `MySQL' training, please visit the training section at `http://www.mysql.com/training/'. If you have restricted access to the Internet, please contact the `MySQL AB' training staff via email at . *Note Business Services Training::. For information on the `MySQL Certification Program', please see `http://www.mysql.com/certification/'. *Note Business Services Training::. If you're interested in consulting, please visit the consulting section of our web site at `http://www.mysql.com/consulting/'. If you have restricted access to the Internet, please contact the `MySQL AB' consulting staff via email at . *Note Business Services Consulting::. Commercial licenses may be purchased online at `https://order.mysql.com/'. There you will also find information on how to fax your purchase order to `MySQL AB'. More information about licensing can be found at `http://www.mysql.com/products/pricing.html'. If you have questions regarding licensing or you want a quote for a high-volume license deal, please fill in the contact form on our web site (`http://www.mysql.com/') or send email to (for licensing questions) or to (for sales inquiries). *Note MySQL licenses::. If you represent a business that is interested in partnering with `MySQL AB', please send email to . *Note Business Services Partnering::. For more information on the `MySQL' trademark policy, refer to `http://www.mysql.com/company/trademark.html' or send email to . *Note MySQL AB Logos and Trademarks::. If you are interested in any of the `MySQL AB' jobs listed in our jobs section (`http://www.mysql.com/company/jobs/'), please send email to . Please do not send your CV as an attachment, but rather as plain text at the end of your email message. For general discussion among our many users, please direct your attention to the appropriate mailing list. *Note Questions::. Reports of errors (often called bugs), as well as questions and comments, should be sent to the general MySQL mailing list. *Note Mailing-list::. If you have found a sensitive security bug in `MySQL Server', please let us know immediately by sending an email message to . *Note Bug reports::. If you have benchmark results that we can publish, please contact us via email at . If you have suggestions concerning additions or corrections to this manual, please send them to the manual team via email at . For questions or comments about the workings or content of the `MySQL' web site (`http://www.mysql.com/'), please send email to . `MySQL AB' has a privacy policy, which can be read at `http://www.mysql.com/company/privacy.html'. For any queries regarding this policy, please send email to . For all other inquires, please send an email to . MySQL Support and Licensing =========================== This section describes `MySQL' support and licensing arrangements. Support Offered by MySQL AB --------------------------- Technical support from `MySQL AB' means individualized answers to your unique problems direct from the software engineers who code the `MySQL' database engine. We try to take a broad and inclusive view of technical support. Almost any problem involving `MySQL' software is important to us if it's important to you. Typically customers seek help on how to get different commands and utilities to work, remove performance bottlenecks, restore crashed systems, understand the impact of operating system or networking issues on `MySQL', set up best practices for backup and recovery, utilize APIs, and so on. Our support covers only the `MySQL' server and our own utilities, not third-party products that access the `MySQL' server, though we try to help with these where we can. Detailed information about our various support options is given at `http://www.mysql.com/support/', where support contracts can also be ordered online. If you have restricted access to the Internet, please contact our sales staff via email at . Technical support is like life insurance. You can live happily without it for years. However, when your hour arrives, it becomes critically important, but it's too late to buy it. If you use `MySQL Server' for important applications and encounter sudden difficulties, it may be too time consuming to figure out all the answers yourself. You may need immediate access to the most experienced `MySQL' troubleshooters available, those employed by `MySQL AB'. Copyrights and Licenses Used by MySQL ------------------------------------- `MySQL AB' owns the copyright to the `MySQL' source code, the `MySQL' logos and trademarks and this manual. *Note What is MySQL AB::. Several different licenses are relevant to the `MySQL' distribution: 1. All the `MySQL'-specific source in the server, the `mysqlclient' library and the client, as well as the `GNU' `readline' library is covered by the `GNU General Public License'. *Note GPL license::. The text of this license can be found as the file `COPYING' in the distribution. 2. The `GNU' `getopt' library is covered by the `GNU Lesser General Public License'. See . 3. Some parts of the source (the `regexp' library) are covered by a Berkeley-style copyright. 4. Older versions of `MySQL' (3.22 and earlier) are subject to a stricter license (`http://www.mysql.com/products/mypl.html'). See the documentation of the specific version for information. 5. The `MySQL' reference manual is currently *not* distributed under a `GPL'-style license. Use of the manual is subject to the following terms: * Conversion to other formats is allowed, but the actual content may not be altered or edited in any way. * You may create a printed copy for your own personal use. * For all other uses, such as selling printed copies or using (parts of) the manual in another publication, prior written agreement from `MySQL AB' is required. Please send an email message to for more information or if you are interested in doing a translation. For information about how the `MySQL' licenses work in practice, please refer to *Note MySQL licenses::. Also see *Note MySQL AB Logos and Trademarks::. MySQL Licenses -------------- The `MySQL' software is released under the `GNU General Public License' (`GPL'), which is probably the best known `Open Source' license. The formal terms of the `GPL' license can be found at `http://www.fsf.org/licenses/'. See also `http://www.fsf.org/licenses/gpl-faq.html' and `http://www.gnu.org/philosophy/enforcing-gpl.html'. Since the `MySQL' software is released under the `GPL', it may often be used for free, but for certain uses you may want or need to buy commercial licenses from `MySQL AB' at `https://order.mysql.com/'. See `http://www.mysql.com/products/licensing.html' for more information. Older versions of `MySQL' (3.22 and earlier) are subject to a stricter license (`http://www.mysql.com/products/mypl.html'). See the documentation of the specific version for information. Please note that the use of the `MySQL' software under commercial license, `GPL', or the old `MySQL' license does not automatically give you the right to use `MySQL AB' trademarks. *Note MySQL AB Logos and Trademarks::. Using the MySQL Software Under a Commercial License ................................................... The `GPL' license is contagious in the sense that when a program is linked to a `GPL' program all the source code for all the parts of the resulting product must also be released under the `GPL'. If you do not follow this `GPL' requirement, you break the license terms and forfeit your right to use the `GPL' program altogether. You also risk damages. You need a commercial license: * When you link a program with any `GPL' code from the `MySQL' software and don't want the resulting product to be licensed under `GPL', perhaps because you want to build a commercial product or keep the added non-`GPL' code closed source for other reasons. When purchasing commercial licenses, you are not using the `MySQL' software under `GPL' even though it's the same code. * When you distribute a non-`GPL' application that *only* works with the `MySQL' software and ship it with the `MySQL' software. This type of solution is considered to be linking even if it's done over a network. * When you distribute copies of the `MySQL' software without providing the source code as required under the `GPL' license. * When you want to support the further development of the `MySQL' database even if you don't formally need a commercial license. Purchasing support directly from `MySQL AB' is another good way of contributing to the development of the `MySQL' software, with immediate advantages for you. *Note Support::. If you require a license, you will need one for each installation of the `MySQL' software. This covers any number of CPUs on a machine, and there is no artificial limit on the number of clients that connect to the server in any way. For commercial licenses, please visit our website at `http://www.mysql.com/products/licensing.html'. For support contracts, see `http://www.mysql.com/support/'. If you have special needs or you have restricted access to the Internet, please contact our sales staff via email at . Using the MySQL Software for Free Under GPL ........................................... You can use the `MySQL' software for free under the `GPL' if you adhere to the conditions of the `GPL'. For additional details, including answers to common questions about the `GPL', see the generic FAQ from the Free Software Foundation at `http://www.fsf.org/licenses/gpl-faq.html'. Common uses of the `GPL' include: * When you distribute both your own application and the `MySQL' source code under the `GPL' with your product. * When you distribute the `MySQL' source code bundled with other programs that are not linked to or dependent on the `MySQL' system for their functionality even if you sell the distribution commercially. This is called mere aggregation in the `GPL' license. * When you are not distributing *any* part of the `MySQL' system, you can use it for free. * When you are an Internet Service Provider (ISP), offering web hosting with `MySQL' servers for your customers. We encourage people to use ISPs that have MySQL support, as this will give them the confidence that their ISP will, in fact, have the resources to solve any problems they may experience with the `MySQL' installation. Even if an ISP does not have a commercial license for `MySQL Server', their customers should at least be given read access to the source of the `MySQL' installation so that the customers can verify that it is correctly patched. * When you use the `MySQL' database software in conjunction with a web server, you do not need a commercial license (so long as it is not a product you distribute). This is true even if you run a commercial web server that uses `MySQL Server', because you are not distributing any part of the `MySQL' system. However, in this case we would like you to purchase `MySQL' support because the `MySQL' software is helping your enterprise. If your use of `MySQL' database software does not require a commercial license, we encourage you to purchase support from `MySQL AB' anyway. This way you contribute toward `MySQL' development and also gain immediate advantages for yourself. *Note Support::. If you use the `MySQL' database software in a commercial context such that you profit by its use, we ask that you further the development of the `MySQL' software by purchasing some level of support. We feel that if the `MySQL' database helps your business, it is reasonable to ask that you help `MySQL AB'. (Otherwise, if you ask us support questions, you are not only using for free something into which we've put a lot a work, you're asking us to provide free support, too.) MySQL AB Logos and Trademarks ----------------------------- Many users of the `MySQL' database want to display the `MySQL AB' dolphin logo on their web sites, books, or boxed products. We welcome and encourage this, although it should be noted that the word `MySQL' and the `MySQL' dolphin logo are trademarks of `MySQL AB' and may only be used as stated in our trademark policy at `http://www.mysql.com/company/trademark.html'. The Original MySQL Logo ....................... The `MySQL' dolphin logo was designed by the Finnish advertising agency Priority in 2001. The dolphin was chosen as a suitable symbol for the `MySQL' database management system, which is like a smart, fast, and lean animal, effortlessly navigating oceans of data. We also happen to like dolphins. The original `MySQL' logo may only be used by representatives of `MySQL AB' and by those having a written agreement allowing them to do so. MySQL Logos that may be Used Without Written Permission ....................................................... We have designed a set of special _Conditional Use_ logos that may be downloaded from our web site at `http://www.mysql.com/press/logos.html' and used on third-party web sites without written permission from `MySQL AB'. The use of these logos is not entirely unrestricted but, as the name implies, subject to our trademark policy that is also available on our web site. You should read through the trademark policy if you plan to use them. The requirements are basically as follows: * Use the logo you need as displayed on the `http://www.mysql.com/' site. You may scale it to fit your needs, but may not change colors or design, or alter the graphics in any way. * Make it evident that you, and not `MySQL AB', are the creator and owner of the site that displays the `MySQL' trademark. * Don't use the trademark in a way that is detrimental to `MySQL AB' or to the value of `MySQL AB' trademarks. We reserve the right to revoke the right to use the `MySQL AB' trademark. * If you use the trademark on a web site, make it clickable, leading directly to `http://www.mysql.com/'. * If you use the `MySQL' database under `GPL' in an application, your application must be `Open Source' and must be able to connect to a `MySQL' server. Contact us via email at to inquire about special arrangements to fit your needs. When You Need Written Permission to Use MySQL Logos ................................................... You need written permission from `MySQL AB' before using `MySQL' logos in the following cases: * When displaying any `MySQL AB' logo anywhere except on your web site. * When displaying any `MySQL AB' logo except the _Conditional Use_ logos mentioned previously on web sites or elsewhere. Due to legal and commercial reasons we monitor the use of MySQL trademarks on products, books, and other items. We usually require a fee for displaying `MySQL AB' logos on commercial products, since we think it is reasonable that some of the revenue is returned to fund further development of the `MySQL' database. MySQL AB Partnership Logos .......................... `MySQL' partnership logos may be used only by companies and persons having a written partnership agreement with `MySQL AB'. Partnerships include certification as a `MySQL' trainer or consultant. For more information, please see *Note Partnering: Business Services Partnering. Using the Word `MySQL' in Printed Text or Presentations ....................................................... `MySQL AB' welcomes references to the `MySQL' database, but it should be noted that the word `MySQL' is a trademark of `MySQL AB'. Because of this, you must append the trademark symbol (`TM') to the first or most prominent use of the word `MySQL' in a text and, where appropriate, state that `MySQL' is a trademark of `MySQL AB'. For more information, please refer to our trademark policy at `http://www.mysql.com/company/trademark.html'. Using the Word `MySQL' in Company and Product Names ................................................... Use of the word `MySQL' in product or company names or in Internet domain names is not allowed without written permission from `MySQL AB'. MySQL Development Roadmap ========================= This section provides a snapshot of the MySQL development roadmap, including major features implemented or planned for MySQL 4.0, 4.1, 5.0, and 5.1. The following sections provide information for each release series. The production release series is MySQL 4.0, which was declared stable for production use as of Version 4.0.12, released in March 2003. This means that future 4.0 development will be limited only to making bug fixes. For the older MySQL 3.23 series, only critical bug fixes will be made. Active MySQL development currently is taking place in the MySQL 4.1 and 5.0 release series. This means that new features are being added to MySQL 4.1 and MySQL 5.0. Both 4.1 and 5.0 are available now in alpha status. Before upgrading from one release series to the next, please see the notes at *Note Upgrade::. Plans for some of the most requested features are summarized in the following table. *Feature* *MySQL version* Unions 4.0 Subqueries 4.1 R-trees 4.1 (for `MyISAM' tables) Stored procedures 5.0 Views 5.0 or 5.1 Cursors 5.0 Foreign keys 5.1 (already implemented in 3.23 for `InnoDB') Triggers 5.1 Full outer join 5.1 Constraints 5.1 MySQL 4.0 in a Nutshell ----------------------- Long awaited by our users, MySQL Server 4.0 is now available in production status. MySQL 4.0 is available for download from `http://www.mysql.com/' and from our mirrors. MySQL 4.0 has been tested by a large number of users and is in production use at many large sites. The major new features of MySQL Server 4.0 are geared toward our existing business and community users, enhancing the MySQL database software as the solution for mission-critical, heavy-load database systems. Other new features target the users of embedded databases. Features Available in MySQL 4.0 ............................... Speed enhancements * MySQL 4.0 has a query cache that can give a huge speed boost to applications with repetitive queries. *Note Query Cache::. * Version 4.0 further increases the speed of MySQL Server in a number of areas, such as bulk `INSERT' statements, searching on packed indexes, full-text searching (using `FULLTEXT' indexes), and `COUNT(DISTINCT)'. Embedded MySQL Server introduced * The new Embedded Server library can easily be used to create standalone and embedded applications. The embedded server provides an alternative to using MySQL in a client/server environment. *Note Nutshell Embedded MySQL::. InnoDB storage engine as standard * The `InnoDB' storage engine is now offered as a standard feature of the `MySQL' server. This means full support for ACID transactions, foreign keys with cascading `UPDATE' and `DELETE', and row-level locking are now standard features. *Note `InnoDB': InnoDB. New functionality * The enhanced `FULLTEXT' search properties of MySQL Server 4.0 enables `FULLTEXT' indexing of large text masses with both binary and natural-language searching logic. You can customize minimal word length and define your own stop word lists in any human language, enabling a new set of applications to be built with MySQL Server. *Note Fulltext Search::. Standards compliance, portability, and migration * Many users will also be happy to learn that MySQL Server now supports the `UNION' statement, a long-awaited standard SQL feature. * MySQL now runs natively on the Novell NetWare 6.0 platform. *Note NetWare installation::. * Features to simplify migration from other database systems to MySQL Server include `TRUNCATE TABLE' (as in Oracle). Internationalization * Our German, Austrian, and Swiss users will note that `MySQL' now supports a new character set, `latin1_de', which ensures that the _German sorting order_ sorts words with umlauts in the same order as do German telephone books. Usability enhancements In the process of implementing features for new users, we have not forgotten requests from our loyal community of existing users. * Most `mysqld' parameters (startup options) can now be set without taking down the server. This is a convenient feature for database administrators (DBAs). *Note `SET OPTION': SET OPTION. * Multiple-table `DELETE' and `UPDATE' statements have been added.. * On Windows, symbolic link handling at the database level is enabled by default. On Unix, the `MyISAM' storage engine now supports symbolic linking at the table level (and not just the database level as before). * `SQL_CALC_FOUND_ROWS' and `FOUND_ROWS()' are new functions that make it possible to find out the number of rows a `SELECT' query that includes a `LIMIT' clause would have returned without that clause. The news section of this manual includes a more in-depth list of features. *Note News-4.0.x::. The Embedded MySQL Server ......................... The `libmysqld' embedded server library makes MySQL Server suitable for a vastly expanded realm of applications. By using this library, developers can embed MySQL Server into various applications and electronics devices, where the end user has no knowledge of there actually being an underlying database. Embedded MySQL Server is ideal for use behind the scenes in Internet appliances, public kiosks, turnkey hardware/software combination units, high performance Internet servers, self-contained databases distributed on CD-ROM, and so on. Many users of `libmysqld' will benefit from the MySQL _Dual Licensing_. For those not wishing to be bound by the `GPL', the software is also made available under a commercial license. The embedded MySQL library uses the same interface as the normal client library, so it is convenient and easy to use. *Note `libmysqld': libmysqld. MySQL 4.1 in a Nutshell ----------------------- MySQL Server 4.0 laid the foundation for new features implemented in MySQL 4.1, such as subqueries and Unicode support, and for the work on stored procedures being done in version 5.0. These features come at the top of the wish list of many of our customers. With these additions, critics of the MySQL Database Server have to be more imaginative than ever in pointing out deficiencies in the MySQL database management system. Already well-known for its stability, speed, and ease of use, MySQL Server will be able to fulfill the requirement checklists of very demanding buyers. Features Available in MySQL 4.1 ............................... The features listed in this section are implemented in MySQL 4.1. A few other features are still planned for MySQL 4.1. *Note TODO MySQL 4.1::. Most new features being coded are or will be available in MySQL 5.0. *Note TODO MySQL 5.0::. Support for subqueries and derived tables * A subquery is a `SELECT' statement nested within another statement. A derived table (an unnamed view) is a subquery in the `FROM' clause of another statement. *Note Subqueries::. Speed enhancements * Faster binary client/server protocol with support for prepared statements and parameter binding. *Note C API Prepared statements::. * `BTREE' indexing is now supported for `HEAP' tables, significantly improving response time for non-exact searches. New functionality * `CREATE TABLE table_name2 LIKE table_name1' allows you to create, with a single statement, a new table with a structure exactly like that of an existing table. * The MyISAM storage engine now supports OpenGIS spatial types for storing geographical data. *Note Spatial extensions in MySQL::. * Replication can be done over SSL connections. Standards compliance, portability, and migration * The new client/server protocol adds the ability to pass multiple warnings to the client, rather than only a single result. This makes operations such as bulk data loading much easier to track. * `SHOW WARNINGS' shows warnings for the last command. *Note `SHOW WARNINGS': SHOW WARNINGS. Internationalization * To support applications that require the use of local languages, the MySQL software now offers extensive Unicode support through the `utf8' and `ucs2' character sets. * Character sets can now be defined per column, table, and database. This allows for a high degree of flexibility in application design, particularly for multi-language web sites. * For documentation for this improved character set support, see *Note Charset::. Usability enhancements * In response to popular demand, we have added a server-based `HELP' command that can be used to get help information for SQL statements. The advantage of having this information on the server side is that the information is always applicable to the particular server version that you actually are using. Because this information is available by issuing a SQL statement, any client can be written to access it. For example, the `help' command of the `mysql' command-line client has been modified to have this capability. * In the new client/server protocol, multiple statements can be issued with a single call. *Note C API multiple queries::. * The new client/server protocol also supports returning multiple result sets. This might occur as a result of sending multiple statements, for example. * A new `INSERT ... ON DUPLICATE KEY UPDATE ...' syntax has been implemented. This allows you to `UPDATE' an existing row if the `INSERT' would have caused a duplicate in a `PRIMARY' or `UNIQUE' key (index). *Note `INSERT': INSERT. * A new aggregate function, `GROUP_CONCAT()' adds the extremely useful capability of concatenating column values from grouped rows into a single result string. *Note Group by functions and modifiers::. The news section of this manual includes a more in-depth list of features. *Note News-4.1.x::. Stepwise Rollout ................ New features are being added to MySQL 4.1. The alpha version is already available for download. *Note Nutshell Ready for Immediate Use::. The set of features that are being added to version 4.1 is mostly fixed. Additional development is already ongoing for version 5.0. MySQL 4.1 will go through the steps of _Alpha_ (during which time new features might still be added/changed), _Beta_ (when we have feature freeze and only bug corrections will be done), and _Gamma_ (indicating that a production release is just weeks ahead). At the end of this process, MySQL 4.1 will become the new production release. Ready for Immediate Development Use ................................... MySQL 4.1 is currently in the alpha stage, and binaries are available for download at `http://www.mysql.com/downloads/mysql-4.1.html'. All binary releases pass our extensive test suite without any errors on the platforms on which we test. *Note News-4.1.x::. For those wishing to use the most recent development source for MySQL 4.1, we make our 4.1 BitKeeper repository publicly available. *Note Installing source tree::. MySQL 5.0, The Next Development Release --------------------------------------- New development for MySQL is focused on the 5.0 release, featuring Stored Procedures and other new features. *Note TODO MySQL 5.0::. For those wishing to take a look at the bleeding edge of MySQL development, we make our BitKeeper repository for MySQL version 5.0 publicly available. *Note Installing source tree::. As of December 2003, binary builds of version 5.0 are also available. MySQL and the Future (The TODO) =============================== This section summarizes the features that we plan to implement in `MySQL Server'. The items are ordered by release series. Within a list, items are shown in approximately the order they will be done. *Note:* If you are an enterprise level user with an urgent need for a particular feature, please contact to discuss sponsoring options. Targeted financing by sponsor companies allows us to allocate additional resources for specific purposes. One example of a feature sponsored in the past is replication. New Features Planned for 4.1 ---------------------------- The features below are not yet implemented in MySQL 4.1, but are planned for implementation before MySQL 4.1 moves into its beta phase. For a list what is already done in MySQL 4.1, see *Note Nutshell 4.1 features::. * Stable OpenSSL support (MySQL 4.0 supports rudimentary, not 100% tested, support for OpenSSL). * More testing of prepared statements. * More testing of multiple character sets for one table. New Features Planned for 5.0 ---------------------------- The following features are planned for inclusion into MySQL 5.0. Some of the features such as stored procedures are complete and are included in MySQL 5.0 alpha, which is available now. Others such as cursors are only partially available. Expect these and other features to mature and be fully supported in upcoming releases. Note that because we have many developers that are working on different projects, there will also be many additional features. There is also a small chance that some of these features will be added to MySQL 4.1. For a list what is already done in MySQL 4.1, see *Note Nutshell 4.1 features::. For those wishing to take a look at the bleeding edge of MySQL development, we make our BitKeeper repository for MySQL version 5.0 publicly available. *Note Installing source tree::. As of December 2003, binary builds of version 5.0 are also available. Stored Procedures * Stored procedures are currently implemented, based on the SQL:2003 standard. *Note Stored Procedures::. We will also implement a framework to hook in external languages, and (where possible) compatibility with, for example, PL/SQL and T-SQL. New functionality * Elementary cursor support. *Note Cursors::. * The ability to specify explicitly for `MyISAM' tables that an index should be created as an `RTREE' index. (In MySQL 4.1, `RTREE' indexes are used internally for geometrical data that use GIS datatypes, but cannot be created on request.) * Dynamic length rows for `HEAP' tables. Standards compliance, portability and migration * Add true `VARCHAR' support (column lengths longer than 255, and no stripping of trailing whitespace). (There is already support for this in the `MyISAM' storage engine, but it is not yet available at the user level.) Speed enhancements * `SHOW COLUMNS FROM table_name' (used by `mysql' client to allow expansions of column names) should not open the table, only the definition file. This will require less memory and be much faster. * Allow `DELETE' on `MyISAM' tables to use the record cache. To do this, we need to update the threads record cache when we update the `.MYD' file. * Better support for `MEMORY' (`HEAP') tables: * Dynamic length rows. * Faster row handling (less copying). Usability enhancements * Resolving the issue of `RENAME TABLE' on a table used in an active `MERGE' table possibly corrupting the table. The news section of this manual includes a more in-depth list of features. *Note News-5.0.x::. New Features Planned for 5.1 ---------------------------- New functionality * `FOREIGN KEY' support for all table types, not just `InnoDB'. * Column-level constraints. * Fail-safe replication. * Online backup with very low performance penalty. The online backup will make it easy to add a new replication slave without taking down the master. Speed enhancements * New text based table definition file format (`.frm' files) and a table cache for table definitions. This will enable us to do faster queries of table structures and do more efficient foreign key support. * Optimize the `BIT' type to take 1 bit. (`BIT' now takes 1 byte; it is treated as a synonym for `TINYINT'.) Usability enhancements * Add options to the client/server protocol to get progress notes for long running commands. * Implement `RENAME DATABASE'. To make this safe for all storage engines, it should work as follows: * Create the new database. * For every table do a rename of the table to another database, as we do with the `RENAME' command. * Drop the old database. * New internal file interface change. This will make all file handling much more general and make it easier to add extensions like RAID. New Features Planned for the Near Future ---------------------------------------- New functionality * Oracle-like `CONNECT BY PRIOR ...' to search tree-like (hierarchical) structures. * Add all missing SQL-92 and ODBC 3.0 types. * Add `SUM(DISTINCT)'. * `INSERT SQL_CONCURRENT' and `mysqld --concurrent-insert' to do a concurrent insert at the end of a table if the table is read-locked. * Allow variables to be updated in `UPDATE' statements. For example: `UPDATE TABLE foo SET @a=a+b,a=@a, b=@a+c'. * Change when user variables are updated so that one can use them with `GROUP BY', as in the following example: `SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY id'. * Add an `IMAGE' option to `LOAD DATA INFILE' to not update `TIMESTAMP' and `AUTO_INCREMENT' fields. * Add `LOAD DATA INFILE ... UPDATE' syntax that works like this: * For tables with primary keys, if an input record contains a primary key value, existing rows matching that primary key value are updated from the remainder of the input columns. However, columns corresponding to columns that are *missing* from the input record are not touched. * For tables with primary keys, if an input record does not contain the primary key value or is missing some part of the key, the record is treated as `LOAD DATA INFILE ... REPLACE INTO'. * Make `LOAD DATA INFILE' understand syntax like: LOAD DATA INFILE 'file_name.txt' INTO TABLE tbl_name TEXT_FIELDS (text_field1, text_field2, text_field3) SET table_field1=CONCAT(text_field1, text_field2), table_field3=23 IGNORE text_field3 This can be used to skip over extra columns in the text file, or update columns based on expressions of the read data. * New functions for working with `SET' type columns: * `ADD_TO_SET(value,set)' * `REMOVE_FROM_SET(value,set)' * If you abort `mysql' in the middle of a query, you should open another connection and kill the old running query. Alternatively, an attempt should be made to detect this in the server. * Add a storage engine interface for table information so that you can use it as a system table. This would be a bit slow if you requested information about all tables, but very flexible. `SHOW INFO FROM tbl_name' for basic table information should be implemented. * Allow `SELECT a FROM table_name1 LEFT JOIN table_name2 USING (a)'; in this case `a' is assumed to come from the `table_name1' table. * `DELETE' and `REPLACE' options to the `UPDATE' statement (this will delete rows when one gets a duplicate key error while updating). * Change the format of `DATETIME' to store fractions of seconds. * Make it possible to use the new GNU `regexp' library instead of the current one (the new library should be much faster than the current one). Standards compliance, portability and migration * Don't add automatic `DEFAULT' values to columns. Produce an error for any `INSERT' statement that is missing a value for a column that has no `DEFAULT'. * Add `ANY()', `EVERY()', and `SOME()' group functions. In standard SQL, these work only on boolean columns, but we can extend these to work on any columns or expressions by treating 0 values as FALSE and non-zero values as TRUE. * Fix the type of `MAX(column)' to be the same as the column type: mysql> CREATE TABLE t1 (a DATE); mysql> INSERT INTO t1 VALUES (NOW()); mysql> CREATE TABLE t2 SELECT MAX(a) FROM t1; mysql> SHOW COLUMNS FROM t2; Speed enhancements * Don't allow more than a defined number of threads to run `MyISAM' recovery at the same time. * Change `INSERT ... SELECT' to optionally use concurrent inserts. * Add an option to periodically flush key pages for tables with delayed keys if they haven't been used in a while. * Allow join on key parts (optimization issue). * Add a log file analyzer that can parse out information about which tables are hit most often, how often multiple-table joins are executed, etc. This should help users identify areas of table design that could be optimized to execute much more efficient queries. Internationalization Usability enhancements * Return the original column types when doing `SELECT MIN(column) ... GROUP BY'. * Make it possible to specify `long_query_time' with a granularity in microseconds. * Link the `myisampack' code into the server so that it can perform `PACK' or `COMPRESS' operations. * Add a temporary key buffer cache during `INSERT/DELETE/UPDATE' so that we can gracefully recover if the index file gets full. * If you perform an `ALTER TABLE' on a table that is symlinked to another disk, create temporary tables on that disk. * Implement a `DATE/DATETIME' type that handles time zone information properly, to make dealing with dates in different time zones easier. * Fix `configure' so that one can compile all libraries (like `MyISAM') without threads. * Allow SQL variables as `LIMIT' arguments, for example, `LIMIT @a,@b'. * Automatic output from `mysql' to a web browser. * `LOCK DATABASES' (with various options). * Many more variables for `SHOW STATUS'. Records reads and updates. Selects on a single table and selects with joins. Mean number of tables in select. Number of `ORDER BY' and `GROUP BY' queries. * `mysqladmin copy database new-database'; this requires a `COPY' operation to be added to `mysqld'. * Processlist output should indicate the number of queries/threads. * `SHOW HOSTS' for printing information about the hostname cache. * Change table names from empty strings to `NULL' for calculated columns. * Don't use `Item_copy_string' on numerical values to avoid number->string->number conversion in case of: `SELECT COUNT(*)*(id+0) FROM table_name GROUP BY id' * Change so that `ALTER TABLE' doesn't abort clients that execute `INSERT DELAYED'. * Fix so that when columns are referenced in an `UPDATE' clause, they contain the old values from before the update started. New operating systems * Port the MySQL clients to LynxOS. New Features Planned for the Mid-Term Future -------------------------------------------- * Implement function: `get_changed_tables(timeout,table1,table2,...)'. * Change reading through tables to use memmap when possible. Now only compressed tables use memmap. * Make the automatic timestamp code nicer. Add timestamps to the update log with `SET TIMESTAMP=#;'. * Use read/write mutex in some places to get more speed. * Simple views (implemented in stepwise fashion up to full functionality). *Note ANSI diff Views::. * Automatically close some tables if a table, temporary table, or temporary file gets error 23 (too many open files). * Better constant propagation. When an occurrence of `col_name=n' is found in an expression, for some constant `n', replace other occurrences of `col_name' within the expression with `n'. Currently, this is done only for some simple cases. * Change all const expressions with calculated expressions if possible. * Optimize key = expression comparisons. At the moment only key = field or key = constant comparisons are optimized. * Join some of the copy functions for nicer code. * Change `sql_yacc.yy' to an inline parser to reduce its size and get better error messages. * Change the parser to use only one rule per different number of arguments in function. * Use of full calculation names in the order part (for ACCESS97). * `MINUS', `INTERSECT', and `FULL OUTER JOIN'. (Currently `UNION' [in 4.0] and `LEFT|RIGHT OUTER JOIN' are supported.) * Allow `SQL_OPTION MAX_SELECT_TIME=#', for placing a time limit on a query. * Allow updates to be logged to a database. * Enhance `LIMIT' to allow retrieval of data from the end of a result set. * Alarm around client connect/read/write functions. * Please note the changes to `mysqld_safe': according to FSSTND (which Debian tries to follow) PID files should go into `/var/run/.pid' and log files into `/var/log'. It would be nice if you could put the "DATADIR" in the first declaration of "pidfile" and "log", so the placement of these files can be changed with a single statement. * Allow a client to request logging. * Allow the `LOAD DATA INFILE' statement to read files that have been compressed with `gzip'. * Fix sorting and grouping of `BLOB' columns (partly solved now). * Change to use semaphores when counting threads. One should first implement a semaphore library for MIT-pthreads. * Add full support for `JOIN' with parentheses. * As an alternative to the one-thread-per-connection model, manage a pool of threads to handle queries. * Allow `GET_LOCK()' to obtain more than one lock. When doing this, one must also handle the possible deadlocks this change will introduce. New Features We Don't Plan to Implement --------------------------------------- We aim toward full compliance with SQL-92/SQL-99, so there are no features we plan not to implement. MySQL Information Sources ========================= MySQL Mailing Lists ------------------- This section introduces you to the MySQL mailing lists and provides some guidelines as to how the lists should be used. When you subscribe to a mailing list, you will receive all postings to the list as email messages. You can also to send your own questions and answers to the list. The MySQL Mailing Lists ....................... To subscribe to or unsubscribe from any of the mailing lists described in this section, visit `http://lists.mysql.com/'. 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. If you wish 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'' This list is for announcements of new versions of MySQL and related programs. This is a low-volume list to which all MySQL users should subscribe. ``mysql'' This is 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. ``mysql-digest'' This is the `mysql' list in digest form. Subscribing to this list means you will get all list messages, sent as one large mail message once a day. ``bugs'' This list will be of interest to you if you want to stay informed about issues reported since the last release of `MySQL' or if you want to be actively involved in the process of bug hunting and fixing. *Note Bug reports::. ``bugs-digest'' This is the `bugs' list in digest form. ``internals'' This list is for people who work on the MySQL code. This is also the forum for discussions on MySQL development and post patches. ``internals-digest'' This is the `internals' list in digest form. ``mysqldoc'' This list is for people who work on the MySQL documentation: people from MySQL AB, translators, and other community members. ``mysqldoc-digest'' This is the `mysqldoc' list in digest form. ``benchmarks'' This list is 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. ``benchmarks-digest'' This is the `benchmarks' list in digest form. ``packagers'' This list is 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. ``packagers-digest'' This is the `packagers' list in digest form. ``java'' This list is for discussions about the MySQL server and Java.It is mostly used to discuss JDBC drivers, including MySQL Connector/J. ``java-digest'' This is the `java' list in digest form. ``win32'' This list is for all topics concerning the MySQL software on Microsoft operating systems, such as Windows 9x/Me/NT/2000/XP. ``win32-digest'' This is the `win32' list in digest form. ``myodbc'' This list is for all topics concerning connecting to the MySQL server with ODBC. ``myodbc-digest'' This is the `myodbc' list in digest form. ``mysqlcc'' This list is for all topics concerning the `MySQL Control Center' graphical client. ``mysqlcc-digest'' This is the `mysqlcc' list in digest form. ``pluspl