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
  What Is MySQL?
    History of MySQL
    The Main Features of MySQL
    How Stable Is MySQL?
    How Big Can MySQL Tables Be?
    Year 2000 Compliance
  What Is MySQL AB?
    The Business Model and Services of MySQL AB
      Support
      Training and Certification
      Consulting
      Commercial Licenses
      Partnering
      Advertising
    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 do you need a 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 4.0 In A Nutshell
    Features Available From MySQL 4.0
    Embedded MySQL Server
  MySQL 4.1 In A Nutshell
    Features Available From MySQL 4.1
    Stepwise Rollout
    Ready for Immediate Development Use
    MySQL 5.0, The Next Development Release
  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)
  How Standards-compatible Is MySQL?
    What Standards Does MySQL Follow?
    Running MySQL in ANSI Mode
    MySQL Extensions to ANSI SQL92
    MySQL Differences Compared to ANSI SQL92
      Sub`SELECT's
      `SELECT INTO TABLE'
      Transactions and Atomic Operations
      Stored Procedures and Triggers
      Foreign Keys
      Views
      `--' as the Start of a Comment
    Known Errors and Design Deficiencies in MySQL
      Errors in 3.23 Fixed in a Later MySQL Version
      Open Bugs / Design Deficiencies in MySQL
  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 Do
  How MySQL Compares to Other Databases
    How MySQL Compares to `mSQL'
      How to Convert `mSQL' Tools for MySQL
      How `mSQL' and MySQL Client/Server Communications Protocols Differ
      How `mSQL' 2.0 SQL Syntax Differs from MySQL
    How MySQL Compares to `PostgreSQL'
      MySQL and PostgreSQL development strategies
      Featurewise Comparison of MySQL and PostgreSQL
      Benchmarking MySQL and PostgreSQL
 
MySQL Installation
  Quick Standard Installation of MySQL
    Installing MySQL on Linux
    Installing MySQL on Windows
      Installing the Binaries
      Preparing the Windows MySQL Environment
      Starting the Server for the First Time
    Installing MySQL on Mac OS X
    Installing MySQL on NetWare
      Installing the MySQL for NetWare Binaries
  General Installation Issues
    How to Get MySQL
    Verifying Package Integrity Using `MD5 Checksums' or `GnuPG'
    Operating Systems Supported by MySQL
    Which MySQL Version to Use
    Installation Layouts
    How and When Updates Are Released
    Release Philosophy - No Known Bugs in Releases
    MySQL Binaries Compiled by MySQL AB
    Installing a MySQL Binary Distribution
  Installing a MySQL Source Distribution
    Quick Installation Overview
    Applying Patches
    Typical `configure' Options
    Installing from the Development Source Tree
    Problems Compiling MySQL?
    MIT-pthreads Notes
    Windows Source Distribution
  Post-installation Setup and Testing
    Problems Running `mysql_install_db'
    Problems Starting the MySQL Server
    Starting and Stopping MySQL Automatically
  Upgrading/Downgrading MySQL
    Upgrading From Version 4.0 to 4.1
    Preparing to Upgrade 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 to Another Architecture
  Operating System Specific Notes
    Linux Notes (All Linux Versions)
      Linux Notes for Binary Distributions
      Linux x86 Notes
      Linux SPARC Notes
      Linux Alpha Notes
      Linux PowerPC Notes
      Linux MIPS Notes
      Linux IA64 Notes
    Windows Notes
      Starting MySQL on Windows 95, 98 or Me
      Starting MySQL on Windows NT, 2000 or XP
      Running MySQL on Windows
      Connecting to a Remote MySQL from Windows with SSH
      Splitting Data Across Different Disks on Windows
      Compiling MySQL Clients on Windows
      MySQL-Windows Compared to Unix MySQL
    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
    Mac OS X Notes
      Mac OS X 10.x
      Mac OS X Server 1.2 (Rhapsody)
    Other Unix Notes
      HP-UX Notes for Binary Distributions
      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
      Caldera (SCO) Notes
      Caldera (SCO) Unixware Version 7.0 Notes
    OS/2 Notes
    BeOS Notes
    Novell NetWare Notes
  Perl Installation Comments
    Installing Perl on Unix
    Installing ActiveState Perl on Windows
    Installing the MySQL Perl Distribution on Windows
    Problems Using the Perl `DBI'/`DBD' Interface
 
Tutorial Introduction
  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
  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'
  Using `mysql' in Batch Mode
  Queries from Twin Project
    Find all Non-distributed Twins
    Show a Table on Twin Pair Status
  Using MySQL with Apache
 
Database Administration
  Configuring MySQL
    `mysqld' Command-line Options
    `my.cnf' Option Files
    Installing Many Servers on the Same Machine
    Running Multiple MySQL Servers on the Same Machine
  General Security Issues and the MySQL Access Privilege System
    General Security Guidelines
    How to Make MySQL Secure Against Crackers
    Startup Options for `mysqld' Concerning Security
    Security issues with LOAD DATA LOCAL
    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
    Causes of `Access denied' Errors
  MySQL User Account Management
    `GRANT' and `REVOKE' Syntax
    MySQL User Names and Passwords
    When Privilege Changes Take Effect
    Setting Up the Initial MySQL Privileges
    Adding New Users to MySQL
    Limiting user resources
    Setting Up Passwords
    Keeping Your Password Secure
    Using Secure Connections
      Basics
      Requirements
      Setting Up SSL Certificates for MySQL
      `GRANT' Options
  Disaster Prevention and Recovery
    Database Backups
    `BACKUP TABLE' Syntax
    `RESTORE TABLE' Syntax
    `CHECK TABLE' Syntax
    `REPAIR TABLE' Syntax
    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 Optimisation
    Setting Up a Table Maintenance Regimen
    Getting Information About a Table
  Database Administration Language Reference
    `OPTIMIZE TABLE' Syntax
    `ANALYZE TABLE' Syntax
    `FLUSH' Syntax
    `RESET' Syntax
    `KILL' Syntax
    `SHOW' Syntax
      Retrieving information about Database, Tables, Columns, and Indexes
      `SHOW TABLE STATUS'
      `SHOW STATUS'
      `SHOW VARIABLES'
      `SHOW LOGS'
      `SHOW PROCESSLIST'
      `SHOW GRANTS'
      `SHOW CREATE TABLE'
      `SHOW WARNINGS | ERRORS'
      `SHOW TABLE TYPES'
      `SHOW PRIVILEGES'
  MySQL Localisation 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
  MySQL Server-Side Scripts and Utilities
    Overview of the Server-Side Scripts and Utilities
    `safe_mysqld', The Wrapper Around `mysqld'
    `mysqld_multi', A Program for Managing Multiple MySQL Servers
    `myisampack', The MySQL Compressed Read-only Table Generator
    `mysqld-max', An Extended `mysqld' Server
  MySQL Client-Side Scripts and Utilities
    Overview of the Client-Side Scripts and Utilities
    `mysql', The Command-line Tool
    `mysqladmin', Administrating a MySQL Server
    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
    `mysql_config', Get compile options for compiling clients
    `perror', Explaining Error Codes
    How to Run SQL Commands from a Text File
  The MySQL Log Files
    The Error Log
    The General Query Log
    The Update Log
    The Binary Log
    The Slow Query Log
    Log File Maintenance
  Replication in MySQL
    Introduction
    Replication Implementation Overview
    How To Set Up Replication
    Replication Features and Known Problems
    Replication Options in `my.cnf'
    SQL Commands Related to Replication
    Replication FAQ
    Troubleshooting Replication
 
MySQL Optimisation
  Optimisation Overview
    MySQL Design Limitations/Tradeoffs
    Portability
    What Have We Used MySQL For?
    The MySQL Benchmark Suite
    Using Your Own Benchmarks
  Optimising `SELECT's and Other Queries
    `EXPLAIN' Syntax (Get Information About a `SELECT')
    Estimating Query Performance
    Speed of `SELECT' Queries
    How MySQL Optimises `WHERE' Clauses
    How MySQL Optimises `DISTINCT'
    How MySQL Optimises `LEFT JOIN' and `RIGHT JOIN'
    How MySQL Optimises `ORDER BY'
    How MySQL Optimises `LIMIT'
    Speed of `INSERT' Queries
    Speed of `UPDATE' Queries
    Speed of `DELETE' Queries
    Other Optimisation Tips
  Locking Issues
    How MySQL Locks Tables
    Table Locking Issues
  Optimising Database Structure
    Design Choices
    Get Your Data as Small as Possible
    How MySQL Uses Indexes
    Column Indexes
    Multiple-Column Indexes
    Why So Many Open tables?
    How MySQL Opens and Closes Tables
    Drawbacks to Creating Large Numbers of Tables in the Same Database
  Optimising 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
      Using Symbolic Links for Tables
 
MySQL Language Reference
  Language Structure
    Literals: How to Write Strings and Numbers
      Strings
      Numbers
      Hexadecimal Values
      `NULL' Values
    Database, Table, Index, Column, and Alias Names
    Case Sensitivity in Names
    User Variables
    System Variables
    Comment Syntax
    Is MySQL Picky About Reserved Words?
  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 for Use in `SELECT' and `WHERE' Clauses
    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
      Miscellaneous Functions
    Functions for Use with `GROUP BY' Clauses
  Data Manipulation: `SELECT', `INSERT', `UPDATE', `DELETE'
    `SELECT' Syntax
      `JOIN' Syntax
      `UNION' Syntax
    `HANDLER' Syntax
    `INSERT' Syntax
      `INSERT ... SELECT' Syntax
    `INSERT DELAYED' Syntax
    `UPDATE' Syntax
    `DELETE' Syntax
    `TRUNCATE' Syntax
    `REPLACE' Syntax
    `LOAD DATA INFILE' Syntax
    `DO' Syntax
  Data Definition: `CREATE', `DROP', `ALTER'
    `CREATE DATABASE' Syntax
    `DROP DATABASE' Syntax
    `CREATE TABLE' Syntax
      Silent Column Specification Changes
    `ALTER TABLE' Syntax
    `RENAME TABLE' Syntax
    `DROP TABLE' Syntax
    `CREATE INDEX' Syntax
    `DROP INDEX' Syntax
  Basic MySQL User Utility Commands
    `USE' Syntax
    `DESCRIBE' Syntax (Get Information About Columns)
  MySQL Transactional and Locking Commands
    `BEGIN/COMMIT/ROLLBACK' Syntax
    `LOCK TABLES/UNLOCK TABLES' Syntax
    `SET TRANSACTION' Syntax
  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
  `ISAM' Tables
  `HEAP' Tables
  `InnoDB' Tables
    InnoDB Tables Overview
    InnoDB Startup Options
    Creating InnoDB Tablespace
      If Something Goes Wrong in Database Creation
    Creating InnoDB Tables
      Converting MyISAM Tables to InnoDB
      Foreign Key Constraints
    Adding and Removing InnoDB Data and Log Files
    Backing up and Recovering an InnoDB Database
      Checkpoints
    Moving an InnoDB Database to Another Machine
    InnoDB Transaction Model
      Consistent Read
      Locking Reads
      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
      The InnoDB Monitor
    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-4.1.0, April x, 2003
      MySQL/InnoDB-3.23.56, March xx, 2003
      MySQL/InnoDB-4.0.12, March xx, 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
 
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_server_version()'
      `mysql_get_host_info()'
      `mysql_get_proto_info()'
      `mysql_get_server_info()'
      `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_shutdown()'
      `mysql_stat()'
      `mysql_store_result()'
      `mysql_thread_id()'
      `mysql_use_result()'
      `mysql_commit()'
      `mysql_rollback()'
      `mysql_autocommit()'
      `mysql_more_results()'
      `mysql_next_result()'
    C API Prepared Statements
    C API Prepared Statements DataTypes
    C API Prepared Statements Function Overview
    C API Prepared Statement Function Descriptions
      `mysql_prepare()'
      `mysql_param_count()'
      `mysql_prepare_result()'
      `mysql_bind_param()'
      `mysql_execute()'
      `mysql_stmt_affected_rows()'
      `mysql_bind_result()'
      `mysql_stmt_store_result()'
      `mysql_fetch()'
      `mysql_send_long_data()'
      `mysql_stmt_close()'
      `mysql_stmt_errno()'
      `mysql_stmt_error()'
    C API Handling multiple query executions
    C API Handling DATE, TIME and TIMESTAMP
    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()'