MySQL帮助文档.chm

Table of Contents Preface 1. General Information 1.1. About This Manual 1.2. Conventions Used in This Manual 1.3. Overview of MySQL AB 1.4. Overview of the MySQL Database Management System 1.4.1. History of MySQL 1.4.2. The Main Features of MySQL 1.4.3. MySQL Stability 1.4.4. How Large MySQL Tables Can Be 1.4.5. Year 2000 Compliance 1.5. Overview of the MaxDB Database Management System 1.5.1. What is MaxDB? 1.5.2. History of MaxDB 1.5.3. Features of MaxDB 1.5.4. Licensing and Support 1.5.5. Feature Differences Between MaxDB and MySQL 1.5.6. Interoperability Features Between MaxDB and MySQL 1.5.7. MaxDB-Related Links 1.6. MySQL Development Roadmap 1.6.1. What's New in MySQL 5.1 1.6.2. What's Planned for MySQL 5.2 1.7. MySQL Information Sources 1.7.1. MySQL Mailing Lists 1.7.2. MySQL Community Support at the MySQL Forums 1.7.3. MySQL Community Support on Internet Relay Chat (IRC) 1.8. How to Report Bugs or Problems 1.9. MySQL Standards Compliance 1.9.1. What Standards MySQL Follows 1.9.2. Selecting SQL Modes 1.9.3. Running MySQL in ANSI Mode 1.9.4. MySQL Extensions to Standard SQL 1.9.5. MySQL Differences from Standard SQL 1.9.6. How MySQL Deals with Constraints 2. Installing and Upgrading MySQL 2.1. General Installation Issues 2.1.1. Operating Systems Supported by MySQL 2.1.2. Choosing Which MySQL Distribution to Install 2.1.3. How to Get MySQL 2.1.4. Verifying Package Integrity Using MD5 Checksums or GnuPG 2.1.5. Installation Layouts 2.2. Standard MySQL Installation Using a Binary Distribution 2.3. Installing MySQL on Windows 2.3.1. Choosing An Installation Package 2.3.2. Installing MySQL with the Automated Installer 2.3.3. Using the MySQL Installation Wizard 2.3.4. Using the Configuration Wizard 2.3.5. Installing MySQL from a Noinstall Zip Archive 2.3.6. Extracting the Install Archive 2.3.7. Creating an Option File 2.3.8. Selecting a MySQL Server type 2.3.9. Starting the Server for the First Time 2.3.10. Starting MySQL from the Windows Command Line 2.3.11. Starting MySQL as a Windows Service 2.3.12. Testing The MySQL Installation 2.3.13. Troubleshooting a MySQL Installation Under Windows 2.3.14. Upgrading MySQL on Windows 2.3.15. MySQL on Windows Compared to MySQL on Unix 2.4. Installing MySQL on Linux 2.5. Installing MySQL on Mac OS X 2.6. Installing MySQL on NetWare 2.7. Installing MySQL on Other Unix-Like Systems 2.8. MySQL Installation Using a Source Distribution 2.8.1. Source Installation Overview 2.8.2. Typical configure Options 2.8.3. Installing from the Development Source Tree 2.8.4. Dealing with Problems Compiling MySQL 2.8.5. MIT-pthreads Notes 2.8.6. Installing MySQL from Source on Windows 2.8.7. Compiling MySQL Clients on Windows 2.9. Post-Installation Setup and Testing 2.9.1. Windows Post-Installation Procedures 2.9.2. Unix Post-Installation Procedures 2.9.3. Securing the Initial MySQL Accounts 2.10. Upgrading MySQL 2.10.1. Upgrading from MySQL 5.0 to 5.1 2.10.2. Copying MySQL Databases to Another Machine 2.11. Downgrading MySQL 2.12. Operating System-Specific Notes 2.12.1. Linux Notes 2.12.2. Mac OS X Notes 2.12.3. Solaris Notes 2.12.4. BSD Notes 2.12.5. Other Unix Notes 2.12.6. OS/2 Notes 2.13. Perl Installation Notes 2.13.1. Installing Perl on Unix 2.13.2. Installing ActiveState Perl on Windows 2.13.3. Problems Using the Perl DBI/DBD Interface 3. Tutorial 3.1. Connecting to and Disconnecting from the Server 3.2. Entering Queries 3.3. Creating and Using a Database 3.3.1. Creating and Selecting a Database 3.3.2. Creating a Table 3.3.3. Loading Data into a Table 3.3.4. Retrieving Information from a Table 3.4. Getting Information About Databases and Tables 3.5. Using mysql in Batch Mode 3.6. Examples of Common Queries 3.6.1. The Maximum Value for a Column 3.6.2. The Row Holding the Maximum of a Certain Column 3.6.3. Maximum of Column per Group 3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field 3.6.5. Using User-Defined Variables 3.6.6. Using Foreign Keys 3.6.7. Searching on Two Keys 3.6.8. Calculating Visits Per Day 3.6.9. Using AUTO_INCREMENT 3.7. Queries from the Twin Project 3.7.1. Find All Non-distributed Twins 3.7.2. Show a Table of Twin Pair Status 3.8. Using MySQL with Apache 4. Using MySQL Programs 4.1. Overview of MySQL Programs 4.2. Invoking MySQL Programs 4.3. Specifying Program Options 4.3.1. Using Options on the Command Line 4.3.2. Using Option Files 4.3.3. Using Environment Variables to Specify Options 4.3.4. Using Options to Set Program Variables 5. Database Administration 5.1. Overview of Server-Side Programs 5.2. mysqld — The MySQL Server 5.2.1. mysqld Command Options 5.2.2. Server System Variables 5.2.3. Using System Variables 5.2.4. Server Status Variables 5.2.5. The Server SQL Mode 5.2.6. The MySQL Server Shutdown Process 5.3. MySQL Server Startup Programs 5.3.1. mysqld_safe — MySQL Server Startup Script 5.3.2. mysql.server — MySQL Server Startup Script 5.3.3. mysqld_multi — Manage Multiple MySQL Servers 5.4. mysqlmanager — The MySQL Instance Manager 5.4.1. Starting the MySQL Server with MySQL Instance Manager 5.4.2. Connecting to the MySQL Instance Manager and Creating User Accounts 5.4.3. MySQL Instance Manager Command Options 5.4.4. MySQL Instance Manager Configuration Files 5.4.5. Commands Recognized by the MySQL Instance Manager 5.5. Installation-Related Programs 5.5.1. mysql_fix_privilege_tables — Upgrade MySQL System Tables 5.5.2. mysql_upgrade — Check Tables for MySQL Upgrade 5.6. General Security Issues 5.6.1. General Security Guidelines 5.6.2. Making MySQL Secure Against Attackers 5.6.3. Security-Related mysqld Options 5.6.4. Security Issues with LOAD DATA LOCAL 5.6.5. How to Run MySQL as a Normal User 5.7. The MySQL Access Privilege System 5.7.1. What the Privilege System Does 5.7.2. How the Privilege System Works 5.7.3. Privileges Provided by MySQL 5.7.4. Connecting to the MySQL Server 5.7.5. Access Control, Stage 1: Connection Verification 5.7.6. Access Control, Stage 2: Request Verification 5.7.7. When Privilege Changes Take Effect 5.7.8. Causes of Access denied Errors 5.7.9. Password Hashing as of MySQL 4.1 5.8. MySQL User Account Management 5.8.1. MySQL Usernames and Passwords 5.8.2. Adding New User Accounts to MySQL 5.8.3. Removing User Accounts from MySQL 5.8.4. Limiting Account Resources 5.8.5. Assigning Account Passwords 5.8.6. Keeping Your Password Secure 5.8.7. Using Secure Connections 5.9. Backup and Recovery 5.9.1. Database Backups 5.9.2. Example Backup and Recovery Strategy 5.9.3. Point-in-Time Recovery 5.9.4. Table Maintenance and Crash Recovery 5.10. MySQL Localization and International Usage 5.10.1. The Character Set Used for Data and Sorting 5.10.2. Setting the Error Message Language 5.10.3. Adding a New Character Set 5.10.4. The Character Definition Arrays 5.10.5. String Collating Support 5.10.6. Multi-Byte Character Support 5.10.7. Problems With Character Sets 5.10.8. MySQL Server Time Zone Support 5.11. MySQL Server Logs 5.11.1. Server Log Tables 5.11.2. The Error Log 5.11.3. The General Query Log 5.11.4. The Binary Log 5.11.5. The Slow Query Log 5.11.6. Server Log Maintenance 5.12. Running Multiple MySQL Servers on the Same Machine 5.12.1. Running Multiple Servers on Windows 5.12.2. Running Multiple Servers on Unix 5.12.3. Using Client Programs in a Multiple-Server Environment 5.13. The MySQL Query Cache 5.13.1. How the Query Cache Operates 5.13.2. Query Cache SELECT Options 5.13.3. Query Cache Configuration 5.13.4. Query Cache Status and Maintenance 6. Replication 6.1. Introduction to Replication 6.2. Replication Implementation Overview 6.3. Row-Based Replication 6.4. Replication Implementation Details 6.4.1. Replication Master Thread States 6.4.2. Replication Slave I/O Thread States 6.4.3. Replication Slave SQL Thread States 6.4.4. Replication Relay and Status Files 6.5. How to Set Up Replication 6.6. Replication Compatibility Between MySQL Versions 6.7. Upgrading a Replication Setup 6.7.1. Upgrading Replication to 5.0 6.8. Replication Features and Known Problems 6.9. Replication Startup Options 6.10. How Servers Evaluate Replication Rules 6.11. Replication FAQ 6.12. Comparison of Statement-Based Versus Row-Based Replication 6.13. Troubleshooting Replication 6.14. How to Report Replication Bugs or Problems 6.15. Auto-Increment in Multiple-Master Replication 7. Optimization 7.1. Optimization Overview 7.1.1. MySQL Design Limitations and Tradeoffs 7.1.2. Designing Applications for Portability 7.1.3. What We Have Used MySQL For 7.1.4. The MySQL Benchmark Suite 7.1.5. Using Your Own Benchmarks 7.2. Optimizing SELECT and Other Statements 7.2.1. Optimizing Queries with EXPLAIN 7.2.2. Estimating Query Performance 7.2.3. Speed of SELECT Queries 7.2.4. WHERE Clause Optimization 7.2.5. Range Optimization 7.2.6. Index Merge Optimization 7.2.7. IS NULL Optimization 7.2.8. DISTINCT Optimization 7.2.9. LEFT JOIN and RIGHT JOIN Optimization 7.2.10. Nested Join Optimization 7.2.11. Outer Join Simplification 7.2.12. ORDER BY Optimization 7.2.13. GROUP BY Optimization 7.2.14. LIMIT Optimization 7.2.15. How to Avoid Table Scans 7.2.16. Speed of INSERT Statements 7.2.17. Speed of UPDATE Statements 7.2.18. Speed of DELETE Statements 7.2.19. Other Optimization Tips 7.3. Locking Issues 7.3.1. Locking Methods 7.3.2. Table Locking Issues 7.3.3. Concurrent Inserts 7.4. Optimizing Database Structure 7.4.1. Design Choices 7.4.2. Make Your Data as Small as Possible 7.4.3. Column Indexes 7.4.4. Multiple-Column Indexes 7.4.5. How MySQL Uses Indexes 7.4.6. The MyISAM Key Cache 7.4.7. MyISAM Index Statistics Collection 7.4.8. How MySQL Opens and Closes Tables 7.4.9. Drawbacks to Creating Many Tables in the Same Database 7.5. Optimizing the MySQL Server 7.5.1. System Factors and Startup Parameter Tuning 7.5.2. Tuning Server Parameters 7.5.3. Controlling Query Optimizer Performance 7.5.4. How Compiling and Linking Affects the Speed of MySQL 7.5.5. How MySQL Uses Memory 7.5.6. How MySQL Uses DNS 7.6. Disk Issues 7.6.1. Using Symbolic Links 8. Client and Utility Programs 8.1. Overview of Client and Utility Programs 8.2. myisamchk — MyISAM Table-Maintenance Utility 8.2.1. myisamchk General Options 8.2.2. myisamchk Check Options 8.2.3. myisamchk Repair Options 8.2.4. Other myisamchk Options 8.2.5. myisamchk Memory Usage 8.3. myisamlog — Display MyISAM Log File Contents 8.4. myisampack — Generate Compressed, Read-Only MyISAM Tables 8.5. mysql — The MySQL Command-Line Tool 8.5.1. mysql Options 8.5.2. mysql Commands 8.5.3. Executing SQL Statements from a Text File 8.5.4. mysql Tips 8.6. mysqlaccess — Client for Checking Access Privileges 8.7. mysqladmin — Client for Administering a MySQL Server 8.8. mysqlbinlog — Utility for Processing Binary Log Files 8.9. mysqlcheck — A Table Maintenance and Repair Program 8.10. mysqldump — A Database Backup Program 8.11. mysqlhotcopy — A Database Backup Program 8.12. mysqlimport — A Data Import Program 8.13. mysqlshow — Display Database, Table, and Column Information 8.14. mysqlslap — Load Emulation Client 8.15. mysql_zap — Kill Processes That Match a Pattern 8.16. perror — Explain Error Codes 8.17. replace — A String-Replacement Utility 9. Language Structure 9.1. Literal Values 9.1.1. Strings 9.1.2. Numbers 9.1.3. Hexadecimal Values 9.1.4. Boolean Values 9.1.5. Bit-Field Values 9.1.6. NULL Values 9.2. Database, Table, Index, Column, and Alias Names 9.2.1. Identifier Qualifiers 9.2.2. Identifier Case Sensitivity 9.2.3. Mapping of Identifiers to Filenames 9.3. User-Defined Variables 9.4. Comment Syntax 9.5. Treatment of Reserved Words in MySQL 10. Character Set Support 10.1. Character Sets and Collations in General 10.2. Character Sets and Collations in MySQL 10.3. Specifying Character Sets and Collations 10.3.1. Server Character Set and Collation 10.3.2. Database Character Set and Collation 10.3.3. Table Character Set and Collation 10.3.4. Column Character Set and Collation 10.3.5. Character String Literal Character Set and Collation 10.3.6. National Character Set 10.3.7. Examples of Character Set and Collation Assignment 10.3.8. Compatibility with Other DBMSs 10.4. Connection Character Sets and Collations 10.5. Collation Issues 10.5.1. Using COLLATE in SQL Statements 10.5.2. COLLATE Clause Precedence 10.5.3. BINARY Operator 10.5.4. Some Special Cases Where the Collation Determination Is Tricky 10.5.5. Collations Must Be for the Right Character Set 10.5.6. An Example of the Effect of Collation 10.6. Operations Affected by Character Set Support 10.6.1. Result Strings 10.6.2. CONVERT() and CAST() 10.6.3. SHOW Statements and INFORMATION_SCHEMA 10.7. Unicode Support 10.8. UTF-8 for Metadata 10.9. Character Sets and Collations That MySQL Supports 10.9.1. Unicode Character Sets 10.9.2. West European Character Sets 10.9.3. Central European Character Sets 10.9.4. South European and Middle East Character Sets 10.9.5. Baltic Character Sets 10.9.6. Cyrillic Character Sets 10.9.7. Asian Character Sets 11. Data Types 11.1. Data Type Overview 11.1.1. Overview of Numeric Types 11.1.2. Overview of Date and Time Types 11.1.3. Overview of String Types 11.1.4. Data Type Default Values 11.2. Numeric Types 11.3. Date and Time Types 11.3.1. The DATETIME, DATE, and TIMESTAMP Types 11.3.2. The TIME Type 11.3.3. The YEAR Type 11.3.4. Y2K Issues and Date Types 11.4. String Types 11.4.1. The CHAR and VARCHAR Types 11.4.2. The BINARY and VARBINARY Types 11.4.3. The BLOB and TEXT Types 11.4.4. The ENUM Type 11.4.5. The SET Type 11.5. Data Type Storage Requirements 11.6. Choosing the Right Type for a Column 11.7. Using Data Types from Other Database Engines 12. Functions and Operators 12.1. Operators 12.1.1. Operator Precedence 12.1.2. Type Conversion in Expression Evaluation 12.1.3. Comparison Functions and Operators 12.1.4. Logical Operators 12.2. Control Flow Functions 12.3. String Functions 12.3.1. String Comparison Functions 12.4. Numeric Functions 12.4.1. Arithmetic Operators 12.4.2. Mathematical Functions 12.5. Date and Time Functions 12.6. What Calendar Is Used By MySQL? 12.7. Full-Text Search Functions 12.7.1. Boolean Full-Text Searches 12.7.2. Full-Text Searches with Query Expansion 12.7.3. Full-Text Stopwords 12.7.4. Full-Text Restrictions 12.7.5. Fine-Tuning MySQL Full-Text Search 12.8. Cast Functions and Operators 12.9. XML Functions 12.10. Other Functions 12.10.1. Bit Functions 12.10.2. Encryption and Compression Functions 12.10.3. Information Functions 12.10.4. Miscellaneous Functions 12.11. Functions and Modifiers for Use with GROUP BY Clauses 12.11.1. GROUP BY (Aggregate) Functions 12.11.2. GROUP BY Modifiers 12.11.3. GROUP BY with Hidden Fields 13. SQL Statement Syntax 13.1. Data Definition Statements 13.1.1. ALTER DATABASE Syntax 13.1.2. ALTER TABLE Syntax 13.1.3. ALTER LOGFILE GROUP Syntax 13.1.4. ALTER TABLESPACE Syntax 13.1.5. CREATE DATABASE Syntax 13.1.6. CREATE INDEX Syntax 13.1.7. CREATE TABLE Syntax 13.1.8. CREATE LOGFILE GROUP Syntax 13.1.9. CREATE TABLESPACE Syntax 13.1.10. DROP DATABASE Syntax 13.1.11. DROP INDEX Syntax 13.1.12. DROP TABLE Syntax 13.1.13. DROP LOGFILE GROUP Syntax 13.1.14. DROP TABLESPACE Syntax 13.1.15. RENAME DATABASE Syntax 13.1.16. RENAME TABLE Syntax 13.2. Data Manipulation Statements 13.2.1. DELETE Syntax 13.2.2. DO Syntax 13.2.3. HANDLER Syntax 13.2.4. INSERT Syntax 13.2.5. LOAD DATA INFILE Syntax 13.2.6. REPLACE Syntax 13.2.7. SELECT Syntax 13.2.8. Subquery Syntax 13.2.9. TRUNCATE Syntax 13.2.10. UPDATE Syntax 13.3. MySQL Utility Statements 13.3.1. DESCRIBE Syntax 13.3.2. USE Syntax 13.4. MySQL Transactional and Locking Statements 13.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax 13.4.2. Statements That Cannot Be Rolled Back 13.4.3. Statements That Cause an Implicit Commit 13.4.4. SAVEPOINT and ROLLBACK TO SAVEPOINT Syntax 13.4.5. LOCK TABLES and UNLOCK TABLES Syntax 13.4.6. SET TRANSACTION Syntax 13.4.7. XA Transactions 13.5. Database Administration Statements 13.5.1. Account Management Statements 13.5.2. Table Maintenance Statements 13.5.3. SET Syntax 13.5.4. SHOW Syntax 13.5.5. Other Administrative Statements 13.6. Replication Statements 13.6.1. SQL Statements for Controlling Master Servers 13.6.2. SQL Statements for Controlling Slave Servers 13.7. SQL Syntax for Prepared Statements 14. Storage Engines and Table Types 14.1. The MyISAM Storage Engine 14.1.1. MyISAM Startup Options 14.1.2. Space Needed for Keys 14.1.3. MyISAM Table Storage Formats 14.1.4. MyISAM Table Problems 14.2. The InnoDB Storage Engine 14.2.1. InnoDB Overview 14.2.2. InnoDB Contact Information 14.2.3. InnoDB Configuration 14.2.4. InnoDB Startup Options and System Variables 14.2.5. Creating the InnoDB Tablespace 14.2.6. Creating and Using InnoDB Tables 14.2.7. Adding and Removing InnoDB Data and Log Files 14.2.8. Backing Up and Recovering an InnoDB Database 14.2.9. Moving an InnoDB Database to Another Machine 14.2.10. InnoDB Transaction Model and Locking 14.2.11. InnoDB Performance Tuning Tips 14.2.12. Implementation of Multi-Versioning 14.2.13. InnoDB Table and Index Structures 14.2.14. InnoDB File Space Management and Disk I/O 14.2.15. InnoDB Error Handling 14.2.16. Restrictions on InnoDB Tables 14.2.17. InnoDB Troubleshooting 14.3. The MERGE Storage Engine 14.3.1. MERGE Table Problems 14.4. The MEMORY (HEAP) Storage Engine 14.5. The BDB (BerkeleyDB) Storage Engine 14.5.1. Operating Systems Supported by BDB 14.5.2. Installing BDB 14.5.3. BDB Startup Options 14.5.4. Characteristics of BDB Tables 14.5.5. Things We Need to Fix for BDB 14.5.6. Restrictions on BDB Tables 14.5.7. Errors That May Occur When Using BDB Tables 14.6. The EXAMPLE Storage Engine 14.7. The FEDERATED Storage Engine 14.7.1. Description of the FEDERATED Storage Engine 14.7.2. How to use FEDERATED Tables 14.7.3. Limitations of the FEDERATED Storage Engine 14.8. The ARCHIVE Storage Engine 14.9. The CSV Storage Engine 14.10. The BLACKHOLE Storage Engine 15. Writing a Custom Storage Engine 15.1. Introduction 15.2. Overview 15.3. Creating Storage Engine Source Files 15.4. Creating the handlerton 15.5. Handling Handler Instantiation 15.6. Defining Filename Extensions 15.7. Creating Tables 15.8. Opening a Table 15.9. Implementing Basic Table Scanning 15.9.1. Implementing the store_lock() Function 15.9.2. Implementing the external_lock() Function 15.9.3. Implementing the rnd_init() Function 15.9.4. Implementing the info() Function 15.9.5. Implementing the extra() Function 15.9.6. Implementing the rnd_next() Function 15.10. Closing a Table 15.11. Adding Support for INSERT to a Storage Engine 15.12. Adding Support for UPDATE to a Storage Engine 15.13. Adding Support for DELETE to a Storage Engine 15.14. Supporting Non-Sequential Reads 15.14.1. Implementing the position() Function 15.14.2. Implementing the rnd_pos() Function 15.15. Supporting Indexing 15.15.1. Indexing Overview 15.15.2. Getting Index Information During CREATE TABLE Operations 15.15.3. Creating Index Keys 15.15.4. Parsing Key Information 15.15.5. Providing Index Information to the Optimizer 15.15.6. Preparing for Index Use with index_init() 15.15.7. Cleaning up with index_end() 15.15.8. Implementing the index_read() Function 15.15.9. Implementing the index_read_idx() Function 15.15.10. Implementing the index_next() Function 15.15.11. Implementing the index_prev() Function 15.15.12. Implementing the index_first() Function 15.15.13. Implementing the index_last() Function 15.16. Supporting Transactions 15.16.1. Transaction Overview 15.16.2. Starting a Transaction 15.16.3. Implementing ROLLBACK 15.16.4. Implementing COMMIT 15.16.5. Adding Support for Savepoints 15.17. API Reference 15.17.1. bas_ext 15.17.2. close 15.17.3. create 15.17.4. delete_row 15.17.5. delete_table 15.17.6. external_lock 15.17.7. extra 15.17.8. index_end 15.17.9. index_first 15.17.10. index_init 15.17.11. index_last 15.17.12. index_next 15.17.13. index_prev 15.17.14. index_read_idx 15.17.15. index_read 15.17.16. info 15.17.17. open 15.17.18. position 15.17.19. records_in_range 15.17.20. rnd_init 15.17.21. rnd_next 15.17.22. rnd_pos 15.17.23. start_stmt 15.17.24. store_lock 15.17.25. update_row 15.17.26. write_row 16. MySQL Cluster 16.1. MySQL Cluster Overview 16.2. Basic MySQL Cluster Concepts 16.2.1. MySQL Cluster Nodes, Node Groups, Replicas, and Partitions 16.3. Simple Multi-Computer How-To 16.3.1. Hardware, Software, and Networking 16.3.2. Multi-Computer Installation 16.3.3. Multi-Computer Configuration 16.3.4. Initial Startup 16.3.5. Loading Sample Data and Performing Queries 16.3.6. Safe Shutdown and Restart 16.4. MySQL Cluster Configuration 16.4.1. Building MySQL Cluster from Source Code 16.4.2. Installing the Software 16.4.3. Quick Test Setup of MySQL Cluster 16.4.4. Configuration File 16.5. Process Management in MySQL Cluster 16.5.1. MySQL Server Process Usage for MySQL Cluster 16.5.2. ndbd, the Storage Engine Node Process 16.5.3. ndb_mgmd, the Management Server Process 16.5.4. ndb_mgm, the Management Client Process 16.5.5. Command Options for MySQL Cluster Processes 16.6. Management of MySQL Cluster 16.6.1. MySQL Cluster Startup Phases 16.6.2. Commands in the Management Client 16.6.3. Event Reports Generated in MySQL Cluster 16.6.4. Single-User Mode 16.6.5. On-line Backup of MySQL Cluster 16.7. MySQL Cluster Replication 16.7.1. Abbreviations and Symbols 16.7.2. Assumptions and General Requirements 16.7.3. Known Issues 16.7.4. Replication Schema and Tables 16.7.5. Preparing the Cluster for Replication 16.7.6. Starting Replication (Single Replication Channel) 16.7.7. Using Two Replication Channels 16.7.8. Implementing Failover with MySQL Cluster 16.7.9. MySQL Cluster Backups With Replication 16.8. MySQL Cluster Disk Data Storage 16.9. Using High-Speed Interconnects with MySQL Cluster 16.9.1. Configuring MySQL Cluster to use SCI Sockets 16.9.2. Understanding the Impact of Cluster Interconnects 16.10. Known Limitations of MySQL Cluster 16.11. MySQL Cluster Development Roadmap 16.11.1. MySQL Cluster Changes in MySQL 5.1 16.12. MySQL Cluster FAQ 16.13. MySQL Cluster Glossary 17. Partitioning 17.1. Overview of Partitioning in MySQL 17.2. Partition Types 17.2.1. RANGE Partitioning 17.2.2. LIST Partitioning 17.2.3. HASH Partitioning 17.2.4. KEY Partitioning 17.2.5. Subpartitioning 17.2.6. How MySQL Partitioning Handles NULL Values 17.3. Partition Management 17.3.1. Management of RANGE and LIST Partitions 17.3.2. Management of HASH and KEY Partitions 17.3.3. Maintenance of Partitions 17.3.4. Obtaining Information About Partitions 17.4. Partition Pruning 17.5. Restrictions and Limitations on Partitioning 18. Spatial Extensions 18.1. Introduction to MySQL Spatial Support 18.2. The OpenGIS Geometry Model 18.2.1. The Geometry Class Hierarchy 18.2.2. Class Geometry 18.2.3. Class Point 18.2.4. Class Curve 18.2.5. Class LineString 18.2.6. Class Surface 18.2.7. Class Polygon 18.2.8. Class GeometryCollection 18.2.9. Class MultiPoint 18.2.10. Class MultiCurve 18.2.11. Class MultiLineString 18.2.12. Class MultiSurface 18.2.13. Class MultiPolygon 18.3. Supported Spatial Data Formats 18.3.1. Well-Known Text (WKT) Format 18.3.2. Well-Known Binary (WKB) Format 18.4. Creating a Spatially Enabled MySQL Database 18.4.1. MySQL Spatial Data Types 18.4.2. Creating Spatial Values 18.4.3. Creating Spatial Columns 18.4.4. Populating Spatial Columns 18.4.5. Fetching Spatial Data 18.5. Analyzing Spatial Information 18.5.1. Geometry Format Conversion Functions 18.5.2. Geometry Functions 18.5.3. Functions That Create New Geometries from Existing Ones 18.5.4. Functions for Testing Spatial Relations Between Geometric Objects 18.5.5. Relations on Geometry Minimal Bounding Rectangles (MBRs) 18.5.6. Functions That Test Spatial Relationships Between Geometries 18.6. Optimizing Spatial Analysis 18.6.1. Creating Spatial Indexes 18.6.2. Using a Spatial Index 18.7. MySQL Conformance and Compatibility 19. Stored Procedures and Functions 19.1. Stored Routines and the Grant Tables 19.2. Stored Routine Syntax 19.2.1. CREATE PROCEDURE and CREATE FUNCTION Syntax 19.2.2. ALTER PROCEDURE and ALTER FUNCTION Syntax 19.2.3. DROP PROCEDURE and DROP FUNCTION Syntax 19.2.4. CALL Statement Syntax 19.2.5. BEGIN ... END Compound Statement Syntax 19.2.6. DECLARE Statement Syntax 19.2.7. Variables in Stored Routines 19.2.8. Conditions and Handlers 19.2.9. Cursors 19.2.10. Flow Control Constructs 19.3. Stored Procedures, Functions, Triggers, and Replication: Frequently Asked Questions 19.4. Binary Logging of Stored Routines and Triggers 20. Triggers 20.1. CREATE TRIGGER Syntax 20.2. DROP TRIGGER Syntax 20.3. Using Triggers 21. Event Scheduler 21.1. Event Scheduler Overview 21.2. Event Scheduler Syntax 21.2.1. CREATE EVENT Syntax 21.2.2. ALTER EVENT Syntax 21.2.3. DROP EVENT Syntax 21.3. Event Metadata 21.4. The Event Scheduler and MySQL Privileges 21.5. Event Scheduler Limitations and Restrictions 22. Views 22.1. ALTER VIEW Syntax 22.2. CREATE VIEW Syntax 22.3. DROP VIEW Syntax 23. The INFORMATION_SCHEMA Database 23.1. The INFORMATION_SCHEMA SCHEMATA Table 23.2. The INFORMATION_SCHEMA TABLES Table 23.3. The INFORMATION_SCHEMA COLUMNS Table 23.4. The INFORMATION_SCHEMA STATISTICS Table 23.5. The INFORMATION_SCHEMA USER_PRIVILEGES Table 23.6. The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table 23.7. The INFORMATION_SCHEMA TABLE_PRIVILEGES Table 23.8. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table 23.9. The INFORMATION_SCHEMA CHARACTER_SETS Table 23.10. The INFORMATION_SCHEMA COLLATIONS Table 23.11. The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table 23.12. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table 23.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table 23.14. The INFORMATION_SCHEMA ROUTINES Table 23.15. The INFORMATION_SCHEMA VIEWS Table 23.16. The INFORMATION_SCHEMA TRIGGERS Table 23.17. The INFORMATION_SCHEMA PLUGINS Table 23.18. The INFORMATION_SCHEMA ENGINES Table 23.19. The INFORMATION_SCHEMA PARTITIONS Table 23.20. The INFORMATION_SCHEMA EVENTS Table 23.21. The INFORMATION_SCHEMA FILES Table 23.22. The INFORMATION_SCHEMA PROCESSLIST Table 23.23. Other INFORMATION_SCHEMA Tables 23.24. Extensions to SHOW Statements 24. Precision Math 24.1. Types of Numeric Values 24.2. DECIMAL Data Type Changes 24.3. Expression Handling 24.4. Rounding Behavior 24.5. Precision Math Examples 25. APIs and Libraries 25.1. libmysqld, the Embedded MySQL Server Library 25.1.1. Overview of the Embedded MySQL Server Library 25.1.2. Compiling Programs with libmysqld 25.1.3. Restrictions when using the Embedded MySQL Server 25.1.4. Options with the Embedded Server 25.1.5. Things left to do in Embedded Server (TODO) 25.1.6. Embedded Server Examples 25.1.7. Licensing the Embedded Server 25.2. MySQL C API 25.2.1. C API Data types 25.2.2. C API Function Overview 25.2.3. C API Function Descriptions 25.2.4. C API Prepared Statements 25.2.5. C API Prepared Statement Data types 25.2.6. C API Prepared Statement Function Overview 25.2.7. C API Prepared Statement Function Descriptions 25.2.8. C API Prepared statement problems 25.2.9. C API Handling of Multiple Query Execution 25.2.10. C API Handling of Date and Time Values 25.2.11. C API Threaded Function Descriptions 25.2.12. C API Embedded Server Function Descriptions 25.2.13. Common questions and problems when using the C API 25.2.14. Building Client Programs 25.2.15. How to Make a Threaded Client 25.3. MySQL PHP API 25.3.1. Common Problems with MySQL and PHP 25.3.2. Enabling Both mysql and mysqli in PHP 25.4. MySQL Perl API 25.5. MySQL C++ API 25.5.1. Borland C++ 25.6. MySQL Python API 25.7. MySQL Tcl API 25.8. MySQL Eiffel Wrapper 25.9. MySQL Program Development Utilities 25.9.1. msql2mysql — Convert mSQL Programs for Use with MySQL 25.9.2. mysql_config — Get Compile Options for Compiling Clients 26. Connectors 26.1. MySQL Connector/ODBC 26.1.1. Introduction to MyODBC 26.1.2. General Information About ODBC and MyODBC 26.1.3. How to Install MyODBC 26.1.4. Installing MyODBC from a Binary Distribution on Windows 26.1.5. Installing MyODBC from a Binary Distribution on Unix 26.1.6. Installing MyODBC from a Source Distribution on Windows 26.1.7. Installing MyODBC from a Source Distribution on Unix 26.1.8. Installing MyODBC from the BitKeeper Development Source Tree 26.1.9. MyODBC Configuration 26.1.10. MyODBC Connection-Related Issues 26.1.11. MyODBC and Microsoft Access 26.1.12. MyODBC and Microsoft VBA and ASP 26.1.13. MyODBC and Third-Party ODBC Tools 26.1.14. MyODBC General Functionality 26.1.15. Basic MyODBC Application Steps 26.1.16. MyODBC API Reference 26.1.17. MyODBC Data Types 26.1.18. MyODBC Error Codes 26.1.19. MyODBC With VB: ADO, DAO and RDO 26.1.20. MyODBC with Microsoft .NET 26.1.21. Credits 26.2. MySQL Connector/NET 26.2.1. Introduction 26.2.2. Downloading and Installing MySQL Connector/NET 26.2.3. Connector/NET Architecture 26.2.4. Using MySQL Connector/NET 26.2.5. MySQL Connector/NET Change History 26.3. MySQL Connector/J 26.3.1. Basic JDBC concepts 26.3.2. Installing Connector/J 26.3.3. JDBC Reference 26.3.4. Using Connector/J with J2EE and Other Java Frameworks 26.3.5. Diagnosing Connector/J Problems 26.3.6. MySQL Connector/J Change History 26.4. MySQL Connector/MXJ 26.4.1. Introduction 26.4.2. Support Platforms: 26.4.3. JUnit Test Requirements 26.4.4. Running the JUnit Tests 26.4.5. Running as part of the JDBC Driver 26.4.6. Running within a Java Object 26.4.7. The MysqldResource API 26.4.8. Running within a JMX Agent (custom) 26.4.9. Deployment in a standard JMX Agent environment (JBoss) 26.4.10. Installation 26.5. Connector/PHP 27. Extending MySQL 27.1. MySQL Internals 27.1.1. MySQL Threads 27.1.2. MySQL Test Suite 27.2. The MySQL Plugin Interface 27.2.1. Characteristics of the Plugin Interface 27.2.2. Full-Text Parser Plugins 27.2.3. INSTALL PLUGIN Syntax 27.2.4. UNINSTALL PLUGIN Syntax 27.2.5. Writing Plugins 27.3. Adding New Functions to MySQL 27.3.1. Features of the User-Defined Function Interface 27.3.2. CREATE FUNCTION Syntax 27.3.3. DROP FUNCTION Syntax 27.3.4. Adding a New User-Defined Function 27.3.5. Adding a New Native Function 27.4. Adding New Procedures to MySQL 27.4.1. Procedure Analyse 27.4.2. Writing a Procedure A. Problems and Common Errors A.1. How to Determine What Is Causing a Problem A.2. Common Errors When Using MySQL Programs A.2.1. Access denied A.2.2. Can't connect to [local] MySQL server A.2.3. Client does not support authentication protocol A.2.4. Password Fails When Entered Interactively A.2.5. Host 'host_name' is blocked A.2.6. Too many connections A.2.7. Out of memory A.2.8. MySQL server has gone away A.2.9. Packet too large A.2.10. Communication Errors and Aborted Connections A.2.11. The table is full A.2.12. Can't create/write to file A.2.13. Commands out of sync A.2.14. Ignoring user A.2.15. Table 'tbl_name' doesn't exist A.2.16. Can't initialize character set A.2.17. File Not Found A.3. Installation-Related Issues A.3.1. Problems Linking to the MySQL Client Library A.3.2. Problems with File Permissions A.4. Administration-Related Issues A.4.1. How to Reset the Root Password A.4.2. What to Do If MySQL Keeps Crashing A.4.3. How MySQL Handles a Full Disk A.4.4. Where MySQL Stores Temporary Files A.4.5. How to Protect or Change the MySQL Unix Socket File A.4.6. Time Zone Problems A.5. Query-Related Issues A.5.1. Case Sensitivity in Searches A.5.2. Problems Using DATE Columns A.5.3. Problems with NULL Values A.5.4. Problems with Column Aliases A.5.5. Rollback Failure for Non-Transactional Tables A.5.6. Deleting Rows from Related Tables A.5.7. Solving Problems with No Matching Rows A.5.8. Problems with Floating-Point Comparisons A.6. Optimizer-Related Issues A.7. Table Definition-Related Issues A.7.1. Problems with ALTER TABLE A.7.2. How to Change the Order of Columns in a Table A.7.3. TEMPORARY TABLE Problems A.8. Known Issues in MySQL A.8.1. Open Issues in MySQL B. Error Codes and Messages B.1. Server Error Codes and Messages B.2. Client Error Codes and Messages C. Credits C.1. Developers at MySQL AB C.2. Contributors to MySQL C.3. Documenters and translators C.4. Libraries used by and included with MySQL C.5. Packages that support MySQL C.6. Tools that were used to create MySQL C.7. Supporters of MySQL D. MySQL Change History D.1. Changes in release 5.1.x (Development) D.1.1. Changes in release 5.1.10 (Not yet released) D.1.2. Changes in release 5.1.9 (Not yet released) D.1.3. Changes in release 5.1.8 (Not released) D.1.4. Changes in release 5.1.7 (27 February 2006) D.1.5. Changes in release 5.1.6 (01 February 2006) D.1.6. Changes in release 5.1.5 (10 January 2006) D.1.7. Changes in release 5.1.4 (21 December 2005) D.1.8. Changes in release 5.1.3 (29 November 2005) D.1.9. Changes in release 5.1.2 (Not released) D.1.10. Changes in release 5.1.1 (Not released) D.2. Changes in MyODBC D.2.1. Changes in MyODBC 3.51.13 D.2.2. Changes in MyODBC 3.51.12 D.2.3. Changes in MyODBC 3.51.11 E. Porting to Other Systems E.1. Debugging a MySQL Server E.1.1. Compiling MySQL for Debugging E.1.2. Creating Trace Files E.1.3. Debugging mysqld under gdb E.1.4. Using a Stack Trace E.1.5. Using Server Logs to Find Causes of Errors in mysqld E.1.6. Making a Test Case If You Experience Table Corruption E.2. Debugging a MySQL Client E.3. The DBUG Package E.4. Comments about RTS Threads E.5. Differences Between Thread Packages F. Environment Variables G. Regular Expressions H. Limits in MySQL H.1. Limits of Joins I. Feature Restrictions I.1. Restrictions on Stored Routines and Triggers I.2. Restrictions on Server-Side Cursors I.3. Restrictions on Subqueries I.4. Restrictions on Views I.5. Restrictions on XA Transactions J. GNU General Public License K. MySQL FLOSS License Exception List of Figures 15.1. MySQL architecture List of Tables 26.1. Connection Properties 26.2. Conversion Table 26.3. MySQL Types to Java Types for ResultSet.getObject() 26.4. MySQL to Java Encoding Name Translations List of Examples 26.1. Obtaining a Connection From the DriverManager 26.2. Using java.sql.Statement to Execute a SELECT Query 26.3. Stored Procedure Example 26.4. Using Connection.prepareCall() 26.5. Registering Output Parameters 26.6. Setting CallableStatement Input Parameters 26.7. Retrieving Results and Output Parameter Values 26.8. Retrieving AUTO_INCREMENT Column Values using Statement.getGeneratedKeys() 26.9. Retrieving AUTO_INCREMENT Column Values using SELECT LAST_INSERT_ID() 26.10. Retrieving AUTO_INCREMENT Column Values in Updatable ResultSets 26.11. Setting the CLASSPATH Under UNIX 26.12. Using a Connection Pool with a J2EE Application Server 26.13. Example of transaction with retry logic