1 General Information about MySQL 1.1 What is MySQL? 1.2 About this manual 1.2.1 Conventions used in this manual 1.3 History of MySQL 1.4 Books about MySQL 1.5 The main features of MySQL 1.6 How stable is MySQL? 1.7 Year 2000 compliance 1.8 General SQL information and tutorials 1.9 Useful MySQL-related links 2 MySQL mailing lists and how to ask questions or report errors (bugs) 2.1 The MySQL mailing lists 2.2 Asking questions or reporting bugs 2.3 How to report bugs or problems 2.4 Guidelines for answering questions on the mailing list 3 MySQL licensing and support 3.1 MySQL licensing policy 3.2 Copyrights used by MySQL 3.2.1 Possible future copyright changes 3.3 Distributing MySQL commercially 3.4 Example licensing situations 3.4.1 Selling products that use MySQL 3.4.2 Selling MySQL-related services 3.4.3 ISP MySQL services 3.4.4 Running a web server using MySQL 3.5 MySQL licensing and support costs 3.5.1 Payment information 3.5.2 Contact information 3.6 Types of commercial support 3.6.1 Basic email support 3.6.2 Extended email support 3.6.3 Login support 3.6.4 Extended login support 4 Installing MySQL 4.1 How to get MySQL 4.2 Operating systems supported by MySQL 4.3 Which MySQL version to use 4.4 How and when updates are released 4.5 Installation layouts 4.6 Installing a MySQL binary distribution 4.6.1 Linux RPM notes 4.6.2 Building client programs 4.6.3 System-specific issues 4.6.3.1 Linux notes 4.6.3.2 HP-UX notes 4.7 Installing a MySQL source distribution 4.7.1 Quick installation overview 4.7.2 Applying patches 4.7.3 Typical configure options 4.8 Problems compiling? 4.9 MIT-pthreads notes 4.10 Perl installation comments 4.10.1 Installing Perl on Unix 4.10.2 Installing ActiveState Perl on Win32 4.10.3 Installing the MySQL Perl distribution on Win32 4.10.4 Problems using the Perl DBI/DBD interface 4.11 System-specific issues 4.11.1 Solaris notes 4.11.2 Solaris 2.7 notes 4.11.3 Solaris x86 notes 4.11.4 SunOS 4 notes 4.11.5 Linux notes (all Linux versions) 4.11.5.1 Linux-x86 notes 4.11.5.2 RedHat 5.0 notes 4.11.5.3 RedHat 5.1 notes 4.11.5.4 Linux-SPARC notes 4.11.5.5 Linux-Alpha notes 4.11.5.6 MkLinux notes 4.11.5.7 Qube2 Linux notes 4.11.6 Alpha-DEC-Unix notes 4.11.7 Alpha-DEC-OSF1 notes 4.11.8 SGI-Irix notes 4.11.9 FreeBSD notes 4.11.10 NetBSD notes 4.11.11 OpenBSD 2.5 notes 4.11.12 BSD/OS notes 4.11.12.1 BSD/OS 2.x notes 4.11.12.2 BSD/OS 3.x notes 4.11.12.3 BSD/OS 4.x notes 4.11.13 SCO notes 4.11.14 SCO Unixware 7.0 notes 4.11.15 IBM-AIX notes 4.11.16 HP-UX 10.20 notes 4.11.17 HP-UX 11.x notes 4.11.18 MacOS X notes 4.12 Win32 notes 4.12.1 Installing MySQL on Win32 4.12.2 Starting MySQL on Win95 / Win98 4.12.3 Starting MySQL on NT 4.12.4 Running MySQL on Win32 4.12.5 Connecting to a remote MySQL from Win32 with SSH 4.12.6 MySQL-Win32 compared to Unix MySQL 4.13 OS/2 notes 4.14 TcX binaries 4.15 Post-installation setup and testing 4.15.1 Problems running mysql_install_db 4.15.2 Problems starting the MySQL server 4.15.3 Starting and stopping MySQL automatically 4.15.4 Option files 4.16 Is there anything special to do when upgrading/downgrading MySQL? 4.16.1 Upgrading from a 3.22 version to 3.23 4.16.2 Upgrading from a 3.21 version to 3.22 4.16.3 Upgrading from a 3.20 version to 3.21 4.16.4 Upgrading to another architecture 5 How standards-compatible is MySQL? 5.1 MySQL extensions to ANSI SQL92 5.2 Runnning MySQL in ANSI mode 5.3 MySQL differences compared to ANSI SQL92 5.4 Functionality missing from MySQL 5.4.1 Sub-selects 5.4.2 SELECT INTO TABLE 5.4.3 Transactions 5.4.4 Stored procedures and triggers 5.4.5 Foreign Keys 5.4.5.1 Reasons NOT to use foreign keys 5.4.6 Views 5.4.7 `--' as the start of a comment 5.5 What standards does MySQL follow? 5.6 How to cope without COMMIT/ROLLBACK 6 The MySQL access privilege system 6.1 General security 6.2 How to make MySQL secure against crackers 6.3 What the privilege system does 6.4 MySQL user names and passwords 6.5 Connecting to the MySQL server 6.6 Keeping your password secure 6.7 Privileges provided by MySQL 6.8 How the privilege system works 6.9 Access control, stage 1: Connection verification 6.10 Access control, stage 2: Request verification 6.11 When privilege changes take effect 6.12 Setting up the initial MySQL privileges 6.13 Adding new user privileges to MySQL 6.14 How to set up passwords 6.15 Causes of Access denied errors 7 MySQL language reference 7.1 Literals: how to write strings and numbers 7.1.1 Strings 7.1.2 Numbers 7.1.3 Hexadecimal values 7.1.4 NULL values 7.1.5 Database, table, index, column and alias names 7.1.5.1 Case sensitivity in names 7.2 User variables 7.3 Column types 7.3.1 Column type storage requirements 7.3.5 Numeric types 7.3.6 Date and time types 7.3.6.1 Y2K issues and date types 7.3.6.2 The DATETIME, DATE and TIMESTAMP types 7.3.6.3 The TIME type 7.3.6.4 The YEAR type 7.3.7 String types 7.3.7.1 The CHAR and VARCHAR types 7.3.7.2 The BLOB and TEXT types 7.3.7.3 The ENUM type 7.3.7.4 The SET type 7.3.8 Choosing the right type for a column 7.3.9 Column indexes 7.3.10 Multiple-column indexes 7.3.11 Using column types from other database engines 7.4 Functions for use in SELECT and WHERE clauses 7.4.1 Grouping functions 7.4.2 Normal arithmetic operations 7.4.3 Bit functions 7.4.4 Logical operations 7.4.5 Comparison operators 7.4.6 String comparison functions 7.4.7 Cast operators 7.4.8 Control flow functions 7.4.9 Mathematical functions 7.4.10 String functions 7.4.11 Date and time functions 7.4.12 Miscellaneous functions 7.4.13 Functions for use with GROUP BY clauses 7.5 CREATE DATABASE syntax 7.6 DROP DATABASE syntax 7.7 CREATE TABLE syntax 7.7.1 Silent column specification changes 7.8 ALTER TABLE syntax 7.9 OPTIMIZE TABLE syntax 7.10 DROP TABLE syntax 7.11 DELETE syntax 7.12 SELECT syntax 7.13 JOIN syntax 7.14 INSERT syntax 7.15 REPLACE syntax 7.16 LOAD DATA INFILE syntax 7.17 UPDATE syntax 7.18 USE syntax 7.19 FLUSH syntax (clearing caches) 7.20 KILL syntax 7.21 SHOW syntax (Get information about tables, columns,...) 7.22 EXPLAIN syntax (Get information about a SELECT) 7.23 DESCRIBE syntax (Get information about columns) 7.24 LOCK TABLES/UNLOCK TABLES syntax 7.25 SET OPTION syntax 7.26 GRANT and REVOKE syntax 7.27 CREATE INDEX syntax 7.28 DROP INDEX syntax 7.29 Comment syntax 7.30 CREATE FUNCTION/DROP FUNCTION syntax 7.31 Is MySQL picky about reserved words? 8 MySQL Tutorial 8.1 Connecting to and disconnecting from the server 8.2 Entering queries 8.3 Examples of common queries 8.3.1 The maximum value for column 8.3.2 The row holding the maximum of a certain column 8.3.3 Maximum of column: per group: only the values 8.3.4 The rows holding the group-wise maximum of a certain field 8.3.5 Using foreign keys 8.4 Creating and using a database 8.4.1 Creating and selecting a database 8.4.2 Creating a table 8.4.3 Loading data into a table 8.4.4 Retrieving information from a table 8.4.4.1 Selecting all data 8.4.4.2 Selecting particular rows 8.4.4.3 Selecting particular columns 8.4.4.4 Sorting rows 8.4.4.5 Date calculations 8.4.4.6 Working with NULL values 8.4.4.7 Pattern matching 8.4.4.8 Counting rows 8.4.5 Using more than one table 8.5 Getting information about databases and tables 8.6 Using mysql in batch mode 8.7 Queries from twin project 8.7.1 Find all non-distributed twins 8.7.2 Show a table on twin pair status 9 MySQL server functions 9.1 What languages are supported by MySQL? 9.1.1 The character set used for data and sorting 9.1.2 Adding a new character set 9.1.3 Multi-byte character support 9.2 The update log 9.3 How big MySQL tables can be 9.4 MySQL table types 10 Getting maximum performance from MySQL 10.1 Optimization overview 10.2 System/Compile time and startup parameter tuning 10.2.1 How compiling and linking affects the speed of MySQL 10.2.2 Disk issues 10.2.2.1 Using symbolic links for databases and tables 10.2.3 Tuning server parameters 10.2.4 How MySQL opens and closes tables 10.2.5 Drawbacks of creating large numbers of tables in the same database 10.2.6 Why so many open tables? 10.2.7 How MySQL uses memory 10.2.8 How MySQL locks tables 10.2.9 Table locking issues 10.3 Get your data as small as possible 10.4 MySQL index use 10.5 Speed of queries that access or update data 10.5.1 Estimating query performance 10.5.2 Speed of SELECT queries 10.5.3 How MySQL optimizes WHERE clauses 10.5.4 How MySQL optimizes LEFT JOIN 10.5.5 How MySQL optimizes LIMIT 10.5.6 Speed of INSERT queries 10.5.7 Speed of UPDATE queries 10.5.8 Speed of DELETE queries 10.6 Choosing a table type 10.6.1 Static (Fixed-length) table characteristics 10.6.2 Dynamic table characteristics 10.6.3 Compressed table characteristics 10.6.4 In memory table characteristics 10.7 Other optimization tips 10.8 Using your own benchmarks 10.9 Design choices 10.10 MySQL design limitations/tradeoffs 10.11 Portability 10.12 What have we used MySQL for? 11 The MySQL benchmark suite 12 MySQL Utilites 12.1 Overview of the different MySQL programs 12.2 Administering a MySQL server 12.3 Dumping the structure and data from MySQL databases and tables 12.4 Importing data from text files 12.5 The MySQL compressed read-only table generator 13 Maintaining a MySQL installation 13.1 Using myisamchk for table maintenance and crash recovery 13.1.1 myisamchk invocation syntax 13.1.2 myisamchk memory usage 13.2 Setting up a table maintenance regimen 13.3 Getting information about a table 13.4 Using myisamchk for crash recovery 13.4.1 How to check tables for errors 13.4.2 How to repair tables 13.4.3 Table optimization 13.5 Log file maintenance 14 Adding new functions to MySQL 14.1 Adding a new user-definable function 14.1.1 UDF calling sequences 14.1.2 Argument processing 14.1.3 Return values and error handling 14.1.4 Compiling and installing user-definable functions 14.2 Adding a new native function 15 Adding new procedures to MySQL 15.1 Procedure analyse 15.2 Writing a procedure. 16 MySQL ODBC Support 16.1 Operating systems supported by MyODBC 16.2 How to fill in the various fields in the ODBC administrator program 16.3 How to report problems with MyODBC 16.4 Programs known to work with MyODBC 16.5 How to get the value of an AUTO_INCREMENT column in ODBC 16.6 Reporting problems with MyODBC 17 Using MySQL with some common programs 17.1 Using MySQL with Apache 18 Problems and common errors 18.1 What to do if MySQL keeps crashing 18.2 Some common errors when using MySQL 18.2.1 MySQL server has gone away error 18.2.2 Can't connect to [local] MySQL server error 18.2.3 Host '...' is blocked error 18.2.4 Too many connections error 18.2.5 Out of memory error 18.2.6 Packet too large error 18.2.7 The table is full error 18.2.8 Commands out of sync error in client 18.2.9 Ignoring user error 18.2.10 Table 'xxx' doesn't exist error 18.3 How MySQL handles a full disk 18.4 How to run SQL commands from a text file 18.5 Where MySQL stores temporary files 18.6 How to protect `/tmp/mysql.sock' from being deleted 18.7 Access denied error 18.8 How to run MySQL as a normal user 18.9 How to reset a forgotten password. 18.10 Problems with file permissions 18.11 File not found 18.12 Problems using DATE columns 18.13 Timezone problems 18.14 Case sensitivity in searches 18.15 Problems with NULL values 18.16 Problems with alias 18.17 Deleting rows from related tables 18.18 Solving problems with no matching rows 18.19 Problems with ALTER TABLE. 18.20 How to change the order of columns in a table 19 Solving some common problems with MySQL 19.1 Database replication 19.2 Database backups 19.3 Running multiple MySQL servers on the same machine 20 MySQL client tools and APIs 20.1 MySQL C API 20.2 C API datatypes 20.3 C API function overview 20.4 C API function descriptions 20.4.1 mysql_affected_rows() 20.4.2 mysql_close() 20.4.3 mysql_connect() 20.4.4 mysql_change_user() 20.4.5 mysql_create_db() 20.4.6 mysql_data_seek() 20.4.7 mysql_debug() 20.4.8 mysql_drop_db() 20.4.9 mysql_dump_debug_info() 20.4.10 mysql_eof() 20.4.11 mysql_errno() 20.4.12 mysql_error() 20.4.13 mysql_escape_string() 20.4.14 mysql_fetch_field() 20.4.15 mysql_fetch_fields() 20.4.16 mysql_fetch_field_direct() 20.4.17 mysql_fetch_lengths() 20.4.18 mysql_fetch_row() 20.4.19 mysql_field_count() 20.4.20 mysql_field_seek() 20.4.21 mysql_field_tell() 20.4.22 mysql_free_result() 20.4.23 mysql_get_client_info() 20.4.24 mysql_get_host_info() 20.4.25 mysql_get_proto_info() 20.4.26 mysql_get_server_info() 20.4.27 mysql_info() 20.4.28 mysql_init() 20.4.29 mysql_insert_id() 20.4.30 mysql_kill() 20.4.31 mysql_list_dbs() 20.4.32 mysql_list_fields() 20.4.33 mysql_list_processes() 20.4.34 mysql_list_tables() 20.4.35 mysql_num_fields() 20.4.36 mysql_num_rows() 20.4.37 mysql_options() 20.4.38 mysql_ping() 20.4.39 mysql_query() 20.4.40 mysql_real_connect() 20.4.41 mysql_real_query() 20.4.42 mysql_reload() 20.4.43 mysql_row_seek() 20.4.44 mysql_row_tell() 20.4.45 mysql_select_db() 20.4.46 mysql_shutdown() 20.4.47 mysql_stat() 20.4.48 mysql_store_result() 20.4.49 mysql_thread_id() 20.4.50 mysql_use_result() 20.4.51 Why is it that after mysql_query() returns success, mysql_store_result() sometimes returns NULL? 20.4.52 What results can I get from a query? 20.4.53 How can I get the unique ID for the last inserted row? 20.4.54 Problems linking with the C API 20.4.55 How to make a thread-safe client 20.5 MySQL Perl API 20.5.1 DBI with DBD::mysql 20.5.2 The DBI interface 20.5.3 More DBI/DBD information 20.6 MySQL Eiffel wrapper 20.7 MySQL Java connectivity (JDBC) 20.8 MySQL PHP API 20.9 MySQL C++ APIs 20.10 MySQL Python APIs 20.11 MySQL TCL APIs 21 How MySQL compares to other databases 21.1 How MySQL compares to mSQL 21.1.1 How to convert mSQL tools for MySQL 21.1.2 How mSQL and MySQL client/server communications protocols differ 21.1.3 How mSQL 2.0 SQL syntax differs from MySQL 21.2 How MySQL compares to PostgreSQL A Some MySQL users A.1 General news sites A.2 Some Web search engines A.3 Some Information search engines concentrated on some area A.4 Online magazines A.5 Web sites the use MySQL as a backed A.6 Some Domain/Internet/Web and related services A.7 Web sites that use PHP and MySQL A.8 Some MySQL consultants A.9 Programming A.10 Uncategorized pages B Contributed programs B.1 API's B.2 Clients B.3 Web tools B.4 Authentication tools B.5 Converters B.6 Using MySQL with other products B.7 Useful tools B.8 RPMs for common tools (Most are for RedHat 6.1) B.9 Useful functions B.10 Uncategorized C Contributors to MySQL D MySQL change history D.1 Changes in release 3.23.x (Released as alpha) D.1.1 Changes in release 3.23.9 D.1.2 Changes in release 3.23.8 D.1.3 Changes in release 3.23.7 D.1.4 Changes in release 3.23.6 D.1.5 Changes in release 3.23.5 D.1.6 Changes in release 3.23.4 D.1.7 Changes in release 3.23.3 D.1.8 Changes in release 3.23.2 D.1.9 Changes in release 3.23.1 D.1.10 Changes in release 3.23.0 D.2 Changes in release 3.22.x D.2.1 Changes in release 3.22.30 D.2.2 Changes in release 3.22.29 D.2.3 Changes in release 3.22.28 D.2.4 Changes in release 3.22.27 D.2.5 Changes in release 3.22.26 D.2.6 Changes in release 3.22.25 D.2.7 Changes in release 3.22.24 D.2.8 Changes in release 3.22.23 D.2.9 Changes in release 3.22.22 D.2.10 Changes in release 3.22.21 D.2.11 Changes in release 3.22.20 D.2.12 Changes in release 3.22.19 D.2.13 Changes in release 3.22.18 D.2.14 Changes in release 3.22.17 D.2.15 Changes in release 3.22.16 D.2.16 Changes in release 3.22.15 D.2.17 Changes in release 3.22.14 D.2.18 Changes in release 3.22.13 D.2.19 Changes in release 3.22.12 D.2.20 Changes in release 3.22.11 D.2.21 Changes in release 3.22.10 D.2.22 Changes in release 3.22.9 D.2.23 Changes in release 3.22.8 D.2.24 Changes in release 3.22.7 D.2.25 Changes in release 3.22.6 D.2.26 Changes in release 3.22.5 D.2.27 Changes in release 3.22.4 D.2.28 Changes in release 3.22.3 D.2.29 Changes in release 3.22.2 D.2.30 Changes in release 3.22.1 D.2.31 Changes in release 3.22.0 D.3 Changes in release 3.21.x D.3.1 Changes in release 3.21.33 D.3.2 Changes in release 3.21.32 D.3.3 Changes in release 3.21.31 D.3.4 Changes in release 3.21.30 D.3.5 Changes in release 3.21.29 D.3.6 Changes in release 3.21.28 D.3.7 Changes in release 3.21.27 D.3.8 Changes in release 3.21.26 D.3.9 Changes in release 3.21.25 D.3.10 Changes in release 3.21.24 D.3.11 Changes in release 3.21.23 D.3.12 Changes in release 3.21.22 D.3.13 Changes in release 3.21.21a D.3.14 Changes in release 3.21.21 D.3.15 Changes in release 3.21.20 D.3.16 Changes in release 3.21.19 D.3.17 Changes in release 3.21.18 D.3.18 Changes in release 3.21.17 D.3.19 Changes in release 3.21.16 D.3.20 Changes in release 3.21.15 D.3.21 Changes in release 3.21.14b D.3.22 Changes in release 3.21.14a D.3.23 Changes in release 3.21.13 D.3.24 Changes in release 3.21.12 D.3.25 Changes in release 3.21.11 D.3.26 Changes in release 3.21.10 D.3.27 Changes in release 3.21.9 D.3.28 Changes in release 3.21.8 D.3.29 Changes in release 3.21.7 D.3.30 Changes in release 3.21.6 D.3.31 Changes in release 3.21.5 D.3.32 Changes in release 3.21.4 D.3.33 Changes in release 3.21.3 D.3.34 Changes in release 3.21.2 D.3.35 Changes in release 3.21.0 D.4 Changes in release 3.20.x D.4.1 Changes in release 3.20.18 D.4.2 Changes in release 3.20.17 D.4.3 Changes in release 3.20.16 D.4.4 Changes in release 3.20.15 D.4.5 Changes in release 3.20.14 D.4.6 Changes in release 3.20.13 D.4.7 Changes in release 3.20.11 D.4.8 Changes in release 3.20.10 D.4.9 Changes in release 3.20.9 D.4.10 Changes in release 3.20.8 D.4.11 Changes in release 3.20.7 D.4.12 Changes in release 3.20.6 D.4.13 Changes in release 3.20.3 D.4.14 Changes in release 3.20.0 D.5 Changes in release 3.19.x D.5.1 Changes in release 3.19.5 D.5.2 Changes in release 3.19.4 D.5.3 Changes in release 3.19.3 E Known errors and design deficiencies in MySQL F List of things we want to add to MySQL in the future (The TODO) F.1 Things that must done in the real near future F.2 Things that have to be done sometime F.3 Some things we don't have any plans to do G Comments on porting to other systems G.1 Debugging a MySQL server G.2 Debugging a MySQL client G.3 Comments about RTS threads G.4 Differences between different thread packages H Description of MySQL regular expression syntax I What is Unireg? J The MySQL server license for non Microsoft operating systems K The MySQL license for Microsoft operating systems SQL command, type and function index Concept Index

This document was generated on 1 Febuary 2000 using the texi2html translator version 1.52 (extended by davida@detron.se).