MySQL 3.23, 4.0, 4.1 Reference Manual
`
`Versata Exh. 2019
`Callidus v. Versata
`CBM2013-00052
`
`

`
`MySQL 3.23, 4.0, 4.1 Reference Manual
`
`Abstract
`
`This is the MySQL™ Reference Manual. It documents MySQL 3.23 through MySQL 4.1.25.
`
`MySQL 3.23, 4.0, and 4.1 features.
` This manual describes features that are not included in every edition of
`MySQL 3.23, MySQL 4.0, and MySQL 4.1; such features may not be included in the edition of MySQL 3.23, MySQL
`4.0, or MySQL 4.1; licensed to you. If you have any questions about the features included in your edition of MySQL
`3.23, MySQL 4.0, or MySQL 4.1, refer to your MySQL 3.23, MySQL 4.0, or MySQL 4.1 agreement or contact your
`Oracle sales representative.
`
`For release notes detailing the changes in each release of MySQL 3.23, 4.0, and 4.1, see Appendix C, MySQL
`Release Notes.
`
`Document generated on: 2014-02-17 (revision: 37744)
`
`For legal information, see the Legal Notice.
`
`End of Product Lifecycle. Active development and support for MySQL Database Server versions 3.23, 4.0, and
`4.1 has ended. For details, see http://www.mysql.com/about/legal/lifecycle/#calendar. Please consider upgrading to a
`recent version. Further updates to the content of this manual will be minimal. All formats of this manual will continue to
`be available until 31 Dec 2010.
`
`

`
`Table of Contents
`Preface and Legal Notice ................................................................................................................. xxi
`1 General Information ......................................................................................................................... 1
`1.1 About This Manual ............................................................................................................... 2
`1.2 Typographical and Syntax Conventions ................................................................................. 3
`1.3 Overview of the MySQL Database Management System ........................................................ 4
`1.3.1 What is MySQL? ....................................................................................................... 4
`1.3.2 The Main Features of MySQL .................................................................................... 6
`1.3.3 History of MySQL ...................................................................................................... 9
`1.4 MySQL Development History ................................................................................................ 9
`1.5 MySQL 4.0 in a Nutshell ..................................................................................................... 10
`1.6 MySQL 4.1 in a Nutshell ..................................................................................................... 11
`1.7 MySQL Information Sources ................................................................................................ 13
`1.7.1 MySQL Mailing Lists ................................................................................................ 13
`1.7.2 MySQL Community Support at the MySQL Forums ................................................... 16
`1.7.3 MySQL Community Support on Internet Relay Chat (IRC) .......................................... 16
`1.7.4 MySQL Enterprise .................................................................................................... 16
`1.8 How to Report Bugs or Problems ........................................................................................ 16
`1.9 MySQL Standards Compliance ............................................................................................ 21
`1.9.1 What Standards MySQL Follows .............................................................................. 22
`1.9.2 Selecting SQL Modes .............................................................................................. 22
`1.9.3 Running MySQL in ANSI Mode ................................................................................ 22
`1.9.4 MySQL Extensions to Standard SQL ........................................................................ 22
`1.9.5 MySQL Differences from Standard SQL .................................................................... 25
`1.9.6 How MySQL Deals with Constraints .......................................................................... 31
`1.10 Credits .............................................................................................................................. 33
`1.10.1 Contributors to MySQL ........................................................................................... 33
`1.10.2 Documenters and translators .................................................................................. 37
`1.10.3 Packages that support MySQL ............................................................................... 39
`1.10.4 Tools that were used to create MySQL ................................................................... 40
`1.10.5 Supporters of MySQL ............................................................................................. 40
`2 Installing and Upgrading MySQL .................................................................................................... 43
`2.1 General Installation Guidance .............................................................................................. 45
`2.1.1 Operating Systems On Which MySQL Is Known To Run ............................................ 45
`2.1.2 Choosing Which MySQL Distribution to Install ........................................................... 46
`2.1.3 How to Get MySQL .................................................................................................. 50
`2.1.4 Verifying Package Integrity Using MD5 Checksums or GnuPG .................................... 50
`2.1.5 Installation Layouts .................................................................................................. 53
`2.1.6 Compiler-Specific Build Characteristics ..................................................................... 55
`2.2 Standard MySQL Installation from a Binary Distribution ........................................................ 55
`2.3 Installing MySQL on Microsoft Windows .............................................................................. 55
`2.3.1 Choosing An Installation Package ............................................................................. 56
`2.3.2 Installing MySQL with the Automated Installer ........................................................... 57
`2.3.3 Using the MySQL Installation Wizard ........................................................................ 57
`2.3.4 Using the Configuration Wizard ................................................................................ 60
`2.3.5 Installing MySQL from a Noinstall Zip Archive ........................................................... 65
`2.3.6 Extracting the Install Archive .................................................................................... 66
`2.3.7 Creating an Option File ............................................................................................ 66
`2.3.8 Selecting a MySQL Server Type ............................................................................... 67
`2.3.9 Starting the Server for the First Time ........................................................................ 69
`2.3.10 Starting MySQL from the Windows Command Line .................................................. 70
`2.3.11 Starting MySQL as a Windows Service ................................................................... 71
`
`iii
`
`

`
`MySQL 3.23, 4.0, 4.1 Reference Manual
`
`2.3.12 Testing The MySQL Installation .............................................................................. 73
`2.3.13 Troubleshooting a MySQL Installation Under Windows ............................................. 74
`2.3.14 Upgrading MySQL on Windows .............................................................................. 75
`2.4 Installing MySQL from RPM Packages on Linux ................................................................... 76
`2.5 Installing MySQL on Mac OS X ........................................................................................... 80
`2.6 Installing MySQL on Solaris ................................................................................................ 82
`2.7 Installing MySQL on NetWare ............................................................................................. 83
`2.8 Installing MySQL from Generic Binaries on Other Unix-Like Systems ..................................... 85
`2.9 Installing MySQL from Source ............................................................................................. 87
`2.9.1 Installing MySQL from a Standard Source Distribution ............................................... 88
`2.9.2 Installing MySQL from a Development Source Tree ................................................... 92
`2.9.3 MySQL Source-Configuration Options ....................................................................... 95
`2.9.4 Dealing with Problems Compiling MySQL .................................................................. 98
`2.9.5 Compiling and Linking an Optimized mysqld Server ................................................ 101
`2.9.6 MIT-pthreads Notes ................................................................................................ 102
`2.9.7 Installing MySQL from Source on Windows ............................................................. 104
`2.10 Postinstallation Setup and Testing ................................................................................... 107
`2.10.1 Windows Postinstallation Procedures .................................................................... 107
`2.10.2 Unix Postinstallation Procedures ........................................................................... 109
`2.10.3 Securing the Initial MySQL Accounts ..................................................................... 121
`2.11 Upgrading or Downgrading MySQL .................................................................................. 125
`2.11.1 Upgrading MySQL ................................................................................................ 125
`2.11.2 Downgrading MySQL ............................................................................................ 138
`2.11.3 Checking Whether Tables or Indexes Must Be Rebuilt ........................................... 139
`2.11.4 Rebuilding or Repairing Tables or Indexes ............................................................ 141
`2.11.5 Copying MySQL Databases to Another Machine .................................................... 142
`2.12 Operating System-Specific Notes ..................................................................................... 143
`2.12.1 Linux Notes .......................................................................................................... 143
`2.12.2 Mac OS X Notes .................................................................................................. 150
`2.12.3 Solaris Notes ....................................................................................................... 151
`2.12.4 BSD Notes ........................................................................................................... 155
`2.12.5 Other Unix Notes ................................................................................................. 158
`2.12.6 OS/2 Notes .......................................................................................................... 174
`2.13 Environment Variables .................................................................................................... 175
`2.14 Perl Installation Notes ..................................................................................................... 176
`2.14.1 Installing Perl on Unix .......................................................................................... 177
`2.14.2 Installing ActiveState Perl on Windows .................................................................. 178
`2.14.3 Problems Using the Perl DBI/DBD Interface ........................................................... 178
`3 Tutorial ........................................................................................................................................ 181
`3.1 Connecting to and Disconnecting from the Server .............................................................. 181
`3.2 Entering Queries ............................................................................................................... 182
`3.3 Creating and Using a Database ......................................................................................... 185
`3.3.1 Creating and Selecting a Database ......................................................................... 187
`3.3.2 Creating a Table .................................................................................................... 187
`3.3.3 Loading Data into a Table ...................................................................................... 189
`3.3.4 Retrieving Information from a Table ........................................................................ 190
`3.4 Getting Information About Databases and Tables ............................................................... 204
`3.5 Using mysql in Batch Mode ............................................................................................. 205
`3.6 Examples of Common Queries .......................................................................................... 206
`3.6.1 The Maximum Value for a Column .......................................................................... 207
`3.6.2 The Row Holding the Maximum of a Certain Column ............................................... 207
`3.6.3 Maximum of Column per Group .............................................................................. 208
`3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column ............................ 208
`3.6.5 Using User-Defined Variables ................................................................................. 209
`
`iv
`
`

`
`MySQL 3.23, 4.0, 4.1 Reference Manual
`
`3.6.6 Using Foreign Keys ................................................................................................ 210
`3.6.7 Searching on Two Keys ......................................................................................... 211
`3.6.8 Calculating Visits Per Day ...................................................................................... 212
`3.6.9 Using AUTO_INCREMENT ........................................................................................ 212
`3.7 Using MySQL with Apache ................................................................................................ 214
`4 MySQL Programs ........................................................................................................................ 217
`4.1 Overview of MySQL Programs .......................................................................................... 218
`4.2 Using MySQL Programs .................................................................................................... 223
`4.2.1 Invoking MySQL Programs ..................................................................................... 223
`4.2.2 Connecting to the MySQL Server ............................................................................ 224
`4.2.3 Specifying Program Options ................................................................................... 227
`4.2.4 Setting Environment Variables ................................................................................ 240
`4.3 MySQL Server and Server-Startup Programs ..................................................................... 241
`4.3.1 mysqld — The MySQL Server ............................................................................... 241
`4.3.2 mysqld_safe — MySQL Server Startup Script ...................................................... 242
`4.3.3 mysql.server — MySQL Server Startup Script ..................................................... 246
`4.3.4 mysqld_multi — Manage Multiple MySQL Servers ............................................... 247
`4.4 MySQL Installation-Related Programs ................................................................................ 251
`4.4.1 comp_err — Compile MySQL Error Message File .................................................. 251
`4.4.2 make_win_src_distribution — Create Source Distribution for Windows ............ 251
`4.4.3 mysql_create_system_tables — Generate Statements to Initialize MySQL
`System Tables ................................................................................................................ 252
`4.4.4 mysqlbug — Generate Bug Report ........................................................................ 252
`4.4.5 mysql_fix_privilege_tables — Upgrade MySQL System Tables .................... 253
`4.4.6 mysql_install_db — Initialize MySQL Data Directory ......................................... 254
`4.4.7 mysql_secure_installation — Improve MySQL Installation Security ................ 255
`4.4.8 mysql_tzinfo_to_sql — Load the Time Zone Tables ......................................... 255
`4.5 MySQL Client Programs .................................................................................................... 256
`4.5.1 mysql — The MySQL Command-Line Tool ............................................................ 256
`4.5.2 mysqladmin — Client for Administering a MySQL Server ........................................ 275
`4.5.3 mysqlcheck — A Table Maintenance Program ...................................................... 282
`4.5.4 mysqldump — A Database Backup Program .......................................................... 287
`4.5.5 mysqlimport — A Data Import Program ............................................................... 301
`4.5.6 mysqlshow — Display Database, Table, and Column Information ............................ 306
`4.6 MySQL Administrative and Utility Programs ....................................................................... 309
`4.6.1 myisam_ftdump — Display Full-Text Index information .......................................... 309
`4.6.2 myisamchk — MyISAM Table-Maintenance Utility ................................................... 310
`4.6.3 myisamlog — Display MyISAM Log File Contents .................................................. 327
`4.6.4 myisampack — Generate Compressed, Read-Only MyISAM Tables ........................ 328
`4.6.5 mysqlaccess — Client for Checking Access Privileges .......................................... 334
`4.6.6 mysqlbinlog — Utility for Processing Binary Log Files .......................................... 337
`4.6.7 mysqldumpslow — Summarize Slow Query Log Files ............................................ 344
`4.6.8 mysqlhotcopy — A Database Backup Program .................................................... 346
`4.6.9 mysqlmanagerc — Internal Test-Suite Program ..................................................... 349
`4.6.10 mysqlmanager-pwgen — Internal Test-Suite Program ......................................... 349
`4.6.11 mysql_convert_table_format — Convert Tables to Use a Given Storage
`Engine ............................................................................................................................ 349
`4.6.12 mysql_explain_log — Use EXPLAIN on Statements in Query Log ..................... 350
`4.6.13 mysql_find_rows — Extract SQL Statements from Files ..................................... 351
`4.6.14 mysql_fix_extensions — Normalize Table File Name Extensions ..................... 352
`4.6.15 mysql_setpermission — Interactively Set Permissions in Grant Tables .............. 352
`4.6.16 mysql_tableinfo — Generate Database Metadata ............................................ 353
`4.6.17 mysql_waitpid — Kill Process and Wait for Its Termination ................................ 355
`4.6.18 mysql_zap — Kill Processes That Match a Pattern .............................................. 355
`
`v
`
`

`
`MySQL 3.23, 4.0, 4.1 Reference Manual
`
`4.7 MySQL Program Development Utilities .............................................................................. 356
`4.7.1 msql2mysql — Convert mSQL Programs for Use with MySQL ................................ 356
`4.7.2 mysql_config — Display Options for Compiling Clients ......................................... 357
`4.7.3 my_print_defaults — Display Options from Option Files .................................... 358
`4.7.4 resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols ............. 359
`4.8 Miscellaneous Programs ................................................................................................... 359
`4.8.1 perror — Explain Error Codes ............................................................................. 359
`4.8.2 replace — A String-Replacement Utility ................................................................ 360
`4.8.3 resolveip — Resolve Host name to IP Address or Vice Versa ............................... 361
`5 MySQL Server Administration ...................................................................................................... 363
`5.1 The MySQL Server ........................................................................................................... 364
`5.1.1 Server Option and Variable Reference .................................................................... 364
`5.1.2 Server Command Options ...................................................................................... 383
`5.1.3 Server System Variables ........................................................................................ 396
`5.1.4 Using System Variables ......................................................................................... 434
`5.1.5 Server Status Variables .......................................................................................... 444
`5.1.6 Server SQL Modes ................................................................................................ 457
`5.1.7 Server-Side Help .................................................................................................... 461
`5.1.8 Server Response to Signals ................................................................................... 461
`5.1.9 The Shutdown Process .......................................................................................... 462
`5.2 The mysqld-max Extended MySQL Server ....................................................................... 463
`5.3 MySQL Server Logs ......................................................................................................... 466
`5.3.1 The Error Log ........................................................................................................ 467
`5.3.2 The General Query Log .......................................................................................... 468
`5.3.3 The Update Log ..................................................................................................... 468
`5.3.4 The Binary Log ...................................................................................................... 469
`5.3.5 The Slow Query Log .............................................................................................. 472
`5.3.6 Server Log Maintenance ......................................................................................... 473
`5.4 General Security Issues .................................................................................................... 474
`5.4.1 General Security Guidelines ................................................................................... 474
`5.4.2 Password Security in MySQL ................................................................................. 477
`5.4.3 Making MySQL Secure Against Attackers ................................................................ 483
`5.4.4 Security-Related mysqld Options ........................................................................... 485
`5.4.5 Security Issues with LOAD DATA LOCAL ................................................................ 487
`5.4.6 How to Run MySQL as a Normal User .................................................................... 488
`5.5 The MySQL Access Privilege System ................................................................................ 489
`5.5.1 Privileges Provided by MySQL ................................................................................ 490
`5.5.2 Privilege System Grant Tables ................................................................................ 493
`5.5.3 Specifying Account Names ..................................................................................... 497
`5.5.4 Access Control, Stage 1: Connection Verification ..................................................... 499
`5.5.5 Access Control, Stage 2: Request Verification ......................................................... 502
`5.5.6 When Privilege Changes Take Effect ...................................................................... 504
`5.5.7 Causes of Access-Denied Errors ............................................................................ 505
`5.6 MySQL User Account Management ................................................................................... 510
`5.6.1 User Names and Passwords .................................................................................. 510
`5.6.2 Adding User Accounts ............................................................................................ 512
`5.6.3 Removing User Accounts ....................................................................................... 515
`5.6.4 Setting Account Resource Limits ............................................................................. 515
`5.6.5 Assigning Account Passwords ................................................................................ 516
`5.6.6 Using SSL for Secure Connections ......................................................................... 518
`5.6.7 Connecting to MySQL Remotely from Windows with SSH ........................................ 527
`5.6.8 Auditing MySQL Account Activity ............................................................................ 528
`5.7 Running Multiple MySQL Servers on the Same Machine ..................................................... 529
`5.7.1 Running Multiple Servers on Windows .................................................................... 531
`
`vi
`
`

`
`MySQL 3.23, 4.0, 4.1 Reference Manual
`
`5.7.2 Running Multiple Servers on Unix ........................................................................... 534
`5.7.3 Using Client Programs in a Multiple-Server Environment .......................................... 535
`6 Backup and Recovery .................................................................................................................. 537
`6.1 Backup and Recovery Types ............................................................................................. 538
`6.2 Database Backup Methods ................................................................................................ 540
`6.3 Example Backup and Recovery Strategy ........................................................................... 542
`6.3.1 Establishing a Backup Policy .................................................................................. 543
`6.3.2 Using Backups for Recovery ................................................................................... 545
`6.3.3 Backup Strategy Summary ..................................................................................... 546
`6.4 Using mysqldump for Backups ......................................................................................... 546
`6.4.1 Dumping Data in SQL Format with mysqldump ....................................................... 546
`6.4.2 Reloading SQL-Format Backups ............................................................................. 547
`6.4.3 Dumping Data in Delimited-Text Format with mysqldump ........................................ 548
`6.4.4 Reloading Delimited-Text Format Backups .............................................................. 549
`6.4.5 mysqldump Tips .................................................................................................... 550
`6.5 Point-in-Time (Incremental) Recovery Using the Binary Log ................................................ 552
`6.5.1 Point-in-Time Recovery Using Event Times ............................................................. 553
`6.5.2 Point-in-Time Recovery Using Event Positions ......................................................... 554
`6.6 MyISAM Table Maintenance and Crash Recovery ............................................................... 554
`6.6.1 Using myisamchk for Crash Recovery .................................................................... 555
`6.6.2 How to Check MyISAM Tables for Errors ................................................................. 556
`6.6.3 How to Repair MyISAM Tables ............................................................................... 556
`6.6.4 MyISAM Table Optimization .................................................................................... 559
`6.6.5 Setting Up a MyISAM Table Maintenance Schedule ................................................. 559
`7 Optimization ................................................................................................................................ 561
`7.1 Optimization Overview ....................................................................................................... 562
`7.1.1 MySQL Design Limitations and Tradeoffs ................................................................ 562
`7.1.2 Designing Applications for Portability ....................................................................... 563
`7.1.3 The MySQL Benchmark Suite ................................................................................. 563
`7.1.4 Using Your Own Benchmarks ................................................................................. 564
`7.2 Obtaining Query Execution Plan Information ...................................................................... 565
`7.2.1 Optimizing Queries with EXPLAIN ........................................................................... 565
`7.2.2 EXPLAIN Output Format ........................................................................................ 565
`7.2.3 Estimating Query Performance ............................................................................... 574
`7.3 Optimizing SQL Statements .............................................................................................. 575
`7.3.1 Optimizing SELECT Statements .............................................................................. 575
`7.3.2 Optimizing Non-SELECT Statements ....................................................................... 590
`7.3.3 Other Optimization Tips .......................................................................................... 594
`7.4 Optimization and Indexes .................................................................................................. 597
`7.4.1 Column Indexes ..................................................................................................... 597
`7.4.2 Multiple-Column Indexes ........................................................................................ 597
`7.4.3 How MySQL Uses Indexes ..................................................................................... 598
`7.4.4 MyISAM Index Statistics Collection .......................................................................... 601
`7.5 Buffering and Caching ....................................................................................................... 602
`7.5.1 The MyISAM Key Cache ......................................................................................... 602
`7.5.2 The InnoDB Buffer Pool ........................................................................................ 607
`7.5.3 The MySQL Query Cache ...................................................................................... 608
`7.6 Locking Issues .................................................................................................................. 614
`7.6.1 Internal Locking Methods ........................................................................................ 615
`7.6.2 Table Locking Issues ....................................