MySQL Reference Manual for version 3.22.27.

1 General Information about MySQL

This is the MySQL reference manual; it documents MySQL version 3.22.27.

MySQL is a very fast, multi-threaded, multi-user and robust SQL (Structured Query Language) database server.

For Unix and OS/2 platforms, MySQL is basically free; for Microsoft platforms you must get a MySQL license after a trial time of 30 days. See section 3 MySQL licensing and support.

The MySQL home page provides the latest information about MySQL.

For a discussion of MySQL's capabilities, see section 1.4 The main features of MySQL.

For installation instructions, see section 4 Installing MySQL. For tips on porting MySQL to new machines or operating systems, see section G Comments on porting to other systems.

For information about upgrading from a 3.21 release, see section 4.16.2 Upgrading from a 3.21 version to 3.22.

For a tutorial introduction to MySQL, see section 8 MySQL Tutorial.

For examples of SQL and benchmarking information, see the benchmarking directory. For source distributions, this is the `bench' directory. For binary distributions, this is the `sql-bench' directory.

For a history of new features and bug fixes, see section D MySQL change history.

For a list of currently known bugs and misfeatures, see section E Known errors and design deficiencies in MySQL.

For future plans, see section F List of things we want to add to MySQL in the future (The TODO).

For a list of all the contributors to this product, see section C Contributors to MySQL.

IMPORTANT:

Send bug (error) reports, questions and comments to the mailing list at

For source distributions, the mysqlbug script can be found in the `scripts' directory. For binary distributions, mysqlbug can be found in the `bin' directory.

If you have any suggestions concerning additions or corrections to this manual, please send them to the MySQL mailing list (mysql@lists.mysql.com) with the following subject line: documentation suggestion: [Insert Topic Here]. See section 2.1 The MySQL mailing lists.

1.1 What is MySQL?

MySQL is a true multi-user, multi-threaded SQL database server. SQL is the most popular database language in the world. MySQL is a client/server implementation that consists of a server daemon mysqld and many different client programs and libraries.

SQL is a standardized language that makes it easy to store, update and access information. For example, you can use SQL to retrieve product information and store customer information for a web site. MySQL is also fast and flexible enough to allow you to store logs and pictures in it.

The main goals of MySQL are speed, robustness and ease of use. MySQL was originally developed because we at TcX needed a SQL server that could handle very large databases an order of magnitude faster than what any database vendor could offer to us. We have now been using MySQL since 1996 in an environment with more than 40 databases containing 10,000 tables, of which more than 500 have more than 7 million rows. This is about 100 gigabytes of mission-critical data.

The base upon which MySQL is built is a set of routines that have been used in a highly demanding production environment for many years. Although MySQL is still under development, it already offers a rich and highly useful function set.

The official way to pronounce MySQL is "My Ess Que Ell" (Not MY-SEQUEL).

1.2 About this manual

This manual is currently available in Texinfo, plain text, Info, HTML, PostScript and PDF versions. Because of their size, PostScript and PDF versions are not included with the main MySQL distribution, but are available for separate download at http://www.mysql.com.

The primary document is the Texinfo file. The HTML version is produced automatically with a modified version of texi2html. The plain text and Info versions are produced with makeinfo. The Postscript version is produced using texi2dvi and dvips. The PDF version is produced with the Ghostscript utility ps2pdf.

This manual is written and maintained by David Axmark, Michael (Monty) Widenius, Paul DuBois and Kim Aldale. For other contributors, see section C Contributors to MySQL.

1.2.1 Conventions used in this manual

This manual uses certain typographical conventions:

constant

Constant-width font is used for command names and options; SQL statements; database, table and column names; C and Perl code; and environment variables. Example: "To see how mysqladmin works, invoke it with the --help option."

`filename'

Constant-width font with surrounding quotes is used for filenames and pathnames. Example: "The distribution is installed under the `/usr/local/' directory."

`c'

Constant-width font with surrounding quotes is also used to indicate character sequences. Example: "To specify a wildcard, use the `%' character."

italic

Italic font is used for emphasis, like this.

boldface

Boldface font is used for access privilege names (e.g., "do not grant the process privilege lightly") and to convey especially strong emphasis.

When commands are shown that are meant to be executed by a particular program, the program is indicated by the prompt shown with the command. For example, shell> indicates a command that you execute from your login shell, and mysql> indicates a command that you execute from the mysql client:

shell> type a shell command here
mysql> type a mysql command here

Shell commands are shown using Bourne shell syntax. If you are using a csh-style shell, you may need to issue commands slightly differently. For example, the sequence to set an environment variable and run a command looks like this in Bourne shell syntax:

shell> VARNAME=value some_command

For csh, you would execute the sequence like this:

shell> setenv VARNAME value
shell> some_command

Database, table and column names often must be substituted into commands. To indicate that such substitution is necessary, this manual uses db_name, tbl_name and col_name. For example, you might see a statement like this:

mysql> SELECT col_name FROM db_name.tbl_name;

This means that if you were to enter a similar statement, you would supply your own database, table and column names, perhaps like this:

mysql> SELECT author_name FROM biblio_db.author_list;

SQL statements may be written in uppercase or lowercase. When this manual shows a SQL statement, uppercase is used for particular keywords if those keywords are under discussion (to emphasize them) and lowercase is used for the rest of the statement. So you might see the following in a discussion of the SELECT statement:

mysql> SELECT count(*) FROM tbl_name;

On the other hand, in a discussion of the COUNT() function, the statement would be written like this:

mysql> select COUNT(*) from tbl_name;

If no particular emphasis is intended, all keywords are written uniformly in uppercase.

In syntax descriptions, square brackets (`[' and `]') are used to indicate optional words or clauses:

DROP TABLE [IF EXISTS] tbl_name

When a syntax element consists of a number of alternatives, the alternatives are separated by vertical bars (`|'). When one member from a set of choices may be chosen, the alternatives are listed within square brackets. When one member from a set of choices must be chosen, the alternatives are listed within braces (`{' and `}'):

TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
{DESCRIBE | DESC} tbl_name {col_name | wild}

1.3 History of MySQL

We once started off with the intention of using mSQL to connect to our tables using our own fast low-level (ISAM) routines. However, after some testing we came to the conclusion that mSQL was not fast enough or flexible enough for our needs. This resulted in a new SQL interface to our database but with almost the same API interface as mSQL. This API was chosen to ease porting of third-party code.

The derivation of the name MySQL is not perfectly clear. Our base directory and a large number of our libraries and tools have had the prefix "my" for well over 10 years. However, Monty's daughter (some years younger) is also named My. So which of the two gave its name to MySQL is still a mystery, even for us.

1.4 The main features of MySQL

The following list describes some of the important characteristics of MySQL:

• Fully multi-threaded using kernel threads. That means it easily can use multiple CPUs if available.
• C, C++, Eiffel, Java, Perl, PHP, Python and TCL APIs. See section 20 MySQL client tools and APIs.
• Works on many different platforms. See section 4.2 Operating systems supported by MySQL.
• Many column types: signed/unsigned integers 1, 2, 3, 4 and 8 bytes long, FLOAT, DOUBLE, CHAR, VARCHAR, TEXT, BLOB, DATE, TIME, DATETIME, TIMESTAMP, YEAR, SET and ENUM types. See section 7.2 Column types.
• Very fast joins using an optimized one-sweep multi-join.
• Full operator and function support in the SELECT and WHERE parts of queries. Example:

  mysql> SELECT CONCAT(first_name, " ", last_name) FROM tbl_name
             WHERE income/dependents > 10000 AND age > 30;
  

• SQL functions are implemented through a highly-optimized class library and should be as fast as they can get! Usually there shouldn't be any memory allocation at all after query initialization.
• Full support for SQL GROUP BY and ORDER BY clauses. Support for group functions (COUNT(), COUNT(DISTINCT), AVG(), STD(), SUM(), MAX() and MIN()).
• Support for LEFT OUTER JOIN with ANSI SQL and ODBC syntax.
• You can mix tables from different databases in the same query (as of version 3.22).
• A privilege and password system which is very flexible and secure, and which allows host-based verification. Passwords are secure since all password traffic when connecting to a server is encrypted.
• ODBC (Open-DataBase-Connectivity) for Windows95 (with source). All ODBC 2.5 functions and many others. You can, for example, use Access to connect to your MySQL server. See section 16 MySQL ODBC Support.
• Very fast B-tree disk tables with index compression.
• 16 indexes per table are allowed. Each index may consist of 1 to 16 columns or parts of columns. The maximum index length is 256 bytes (this may be changed when compiling MySQL). An index may use a prefix of a CHAR or VARCHAR field.
• Fixed-length and variable-length records.
• In-memory hash tables which are used as temporary tables.
• Handles large databases. We are using MySQL with some databases that contain 50,000,000 records.
• All columns have default values. You can use INSERT to insert a subset of a table's columns; those columns that are not explicitly given values are set to their default values.
• Uses GNU Automake, Autoconf, and libtool for portability.
• Written in C and C++. Tested with a broad range of different compilers.
• A very fast thread-based memory allocation system.
• No memory leaks. Tested with a commercial memory leakage detector (purify).
• Includes isamchk, a very fast utility for table checking, optimization and repair. See section 13 Maintaining a MySQL installation.
• Full support for the ISO-8859-1 Latin1 character set. For example, the Scandinavian characters @ringaccent{a}, @"a and @"o are allowed in table and column names.
• All data are saved in ISO-8859-1 Latin1 format. All comparisons for normal string columns are case insensitive.
• Sorting is done according to the ISO-8859-1 Latin1 character set (the Swedish way at the moment). It is possible to change this in the source by adding new sort order arrays. To see an example of very advanced sorting, look at the Czech sorting code. MySQL supports many different character sets that can be specified at compile time.
• Aliases on tables and columns as in the SQL92 standard.
• DELETE, INSERT, REPLACE, and UPDATE return how many rows were changed (affected).
• Function names do not clash with table or column names. For example, ABS is a valid column name. The only restriction is that for a function call, no spaces are allowed between the function name and the `(' that follows it. See section 7.30 Is MySQL picky about reserved words?.
• All MySQL programs can be invoked with the --help or -? options to obtain online assistance.
• The server can provide error messages to clients in many languages. See section 9.1 What languages are supported by MySQL?.
• Clients connect to the MySQL server using TCP/IP connections or Unix sockets, or named pipes under NT.
• The MySQL-specific SHOW command can be used to retrieve information about databases, tables and indexes. The EXPLAIN command can be used to determine how the optimizer resolves a query.

1.5 How stable is MySQL?

This section addresses the questions, "how stable is MySQL?" and, "can I depend on MySQL in this project?" Here we will try to clarify some issues and to answer some of the more important questions that seem to concern many people. This section has been put together from information gathered from the mailing list (which is very active in reporting bugs).

At TcX, MySQL has worked without any problems in our projects since mid-1996. When MySQL was released to a wider public, we noticed that there were some pieces of "untested code" that were quickly found by the new users who made queries in a manner different than our own. Each new release has had fewer portability problems than the previous one (even though each has had many new features), and we hope that it will be possible to label one of the next releases "stable".

Each release of MySQL has been usable and there have been problems only when users start to use code from "the gray zones". Naturally, outside users can't know what the gray zones are; this section attempts to indicate those that are currently known. The descriptions deal with the 3.22.x version of MySQL. All known and reported bugs are fixed in the latest version, with the exception of the bugs listed in the bugs section, which are things that are "design"-related. See section E Known errors and design deficiencies in MySQL.

MySQL is written in multiple layers and different independent modules. These modules are listed below with an indication of how well-tested each of them is:

The ISAM table handler -- Stable

This manages storage and retrieval of all data in MySQL 3.22 and earlier versions. In all MySQL releases there hasn't been a single (reported) bug in this code. The only known way to get a corrupted table is to kill the server in the middle of an update. Even that is unlikely to destroy any data beyond rescue, because all data are flushed to disk between each query. There hasn't been a single bug report about lost data because of bugs in MySQL, either.

The MyISAM table handler -- Beta

This is new in MySQL 3.23. It's largely based on the ISAM table code but has a lot of new very useful features.

The parser and lexical analyser -- Stable

There hasn't been a single reported bug in this system for a long time.

The C client code -- Stable

No known problems. In early 3.20 releases, there were some limitations in the send/receive buffer size. As of 3.21.x, the buffer size is now dynamic up to a default of 24M.

Standard client programs -- Stable

These include mysql, mysqladmin and mysqlshow, mysqldump, and mysqlimport.

Basic SQL -- Stable

The basic SQL function system and string classes and dynamic memory handling. Not a single reported bug in this system.

Query optimizer -- Stable

Range optimizer -- Gamma

Join optimizer -- Stable

Locking -- Gamma

This is very system-dependent. On some systems there are big problems using standard OS locking (fcntl()). In these cases, you should run the MySQL daemon with the --skip-locking flag. Problems are known to occur on some Linux systems and on SunOS when using NFS-mounted file systems.

Linux threads -- Gamma

The only problem found has been with the fcntl() call, which is fixed by using the --skip-locking option to mysqld. Some people have reported lockup problems with the 0.5 release.

Solaris 2.5+ pthreads -- Stable

We use this for all our production work.

MIT-pthreads (Other systems) -- Gamma

There have been no reported bugs since 3.20.15 and no known bugs since 3.20.16. On some systems, there is a "misfeature" where some operations are quite slow (a 1/20 second sleep is done between each query). Of course, MIT-pthreads may slow down everything a bit, but index-based SELECT statements are usually done in one time frame so there shouldn't be a mutex locking/thread juggling.

Other thread implementions -- Alpha - Beta

The ports to other systems are still very new and may have bugs, possibly in MySQL, but most often in the thread implementation itself.

LOAD DATA ..., INSERT ... SELECT -- Stable

Some people have thought they have found bugs here, but these usually have turned out to be misunderstandings. Please check the manual before reporting problems!

ALTER TABLE -- Stable

Small changes in 3.22.12.

DBD -- Stable

Now maintained by Jochen Wiedmann

mysqlaccess -- Stable

Written and maintained by Yves Carlier

GRANT -- Gamma

Big changes made in MySQL 3.22.12.

MyODBC (uses ODBC SDK 2.5) -- Beta

It seems to work well with some programs.

TcX provides email support for paying customers, but the MySQL mailing list usually provides answers to common questions. Bugs are usually fixed right away with a patch; for serious bugs, there is almost always a new release.

1.6 Year 2000 compliance

MySQL itself has no problems with Year 2000 (Y2K) compliance:

• MySQL uses Unix time functions and has no problems with dates until 2069; all 2-digit years are regarded to be in the range 1970 to 2069, which means that if you store 01 in a year column, MySQL treats it as 2001.
• All MySQL date functions are stored in one file `sql/time.cc' and coded very carefully to be year 2000-safe.
• In MySQL 3.22 and later versions, the new YEAR column type can store years 0 and 1901 to 2155 in 1 byte and display them using 2 or 4 digits.

You may run into problems with applications that use MySQL in a way that is not Y2K-safe. For example, many old applications store or manipulate years using 2-digit values (which are ambiguous) rather than 4-digit values. This problem may be compounded by applications that use values such as 00 or 99 as "missing" value indicators.

Unfortunately, these problems may be difficult to fix, since different applications may be written by different programmers, each of whom may use a different set of conventions and date-handling functions.

Here is a simple demonstration illustrating that MySQL doesn't have any problems with dates until the year 2030!

mysql> DROP TABLE IF EXISTS y2k;
mysql> CREATE TABLE y2k (date date, date_time datetime, time_stamp timestamp);
mysql> INSERT INTO y2k VALUES ("1998-12-31","1998-12-31 23:59:59",19981231235959);
mysql> INSERT INTO y2k VALUES ("1999-01-01","1999-01-01 00:00:00",19990101000000);
mysql> INSERT INTO y2k VALUES ("1999-09-09","1999-09-09 23:59:59",19990909235959);
mysql> INSERT INTO y2k VALUES ("2000-01-01","2000-01-01 00:00:00",20000101000000);
mysql> INSERT INTO y2k VALUES ("2000-02-28","2000-02-28 00:00:00",20000228000000);
mysql> INSERT INTO y2k VALUES ("2000-02-29","2000-02-29 00:00:00",20000229000000);
mysql> INSERT INTO y2k VALUES ("2000-03-01","2000-03-01 00:00:00",20000301000000);
mysql> INSERT INTO y2k VALUES ("2000-12-31","2000-12-31 23:59:59",20001231235959);
mysql> INSERT INTO y2k VALUES ("2001-01-01","2001-01-01 00:00:00",20010101000000);
mysql> INSERT INTO y2k VALUES ("2004-12-31","2004-12-31 23:59:59",20041231235959);
mysql> INSERT INTO y2k VALUES ("2005-01-01","2005-01-01 00:00:00",20050101000000);
mysql> INSERT INTO y2k VALUES ("2030-01-01","2030-01-01 00:00:00",20300101000000);
mysql> INSERT INTO y2k VALUES ("2050-01-01","2050-01-01 00:00:00",20500101000000);
mysql> SELECT * FROM y2k;
+------------+---------------------+----------------+
| date       | date_time           | time_stamp     |
+------------+---------------------+----------------+
| 1998-12-31 | 1998-12-31 23:59:59 | 19981231235959 |
| 1999-01-01 | 1999-01-01 00:00:00 | 19990101000000 |
| 1999-09-09 | 1999-09-09 23:59:59 | 19990909235959 |
| 2000-01-01 | 2000-01-01 00:00:00 | 20000101000000 |
| 2000-02-28 | 2000-02-28 00:00:00 | 20000228000000 |
| 2000-02-29 | 2000-02-29 00:00:00 | 20000229000000 |
| 2000-03-01 | 2000-03-01 00:00:00 | 20000301000000 |
| 2000-12-31 | 2000-12-31 23:59:59 | 20001231235959 |
| 2001-01-01 | 2001-01-01 00:00:00 | 20010101000000 |
| 2004-12-31 | 2004-12-31 23:59:59 | 20041231235959 |
| 2005-01-01 | 2005-01-01 00:00:00 | 20050101000000 |
| 2030-01-01 | 2030-01-01 00:00:00 | 20300101000000 |
| 2050-01-01 | 2050-01-01 00:00:00 | 00000000000000 |
+------------+---------------------+----------------+

13 rows in set (0.00 sec)

This shows that the DATE and DATETIME types are will not give any problems with future dates (they handle dates until the year 9999).

The TIMESTAMP type, that is used to store the current time, has a range up to only 2030-01-01. TIMESTAMP has a range of 1970 to 2030 on 32-bit machines (signed value). On 64-bit machines it handles times up to 2106 (unsigned value).

Even though MySQL is Y2K-compliant, it is your responsibility to provide unambiguous input. See section 7.2.6.1 Y2K issues and date types for MySQL's rules for dealing with ambiguous date input data (data containing 2-digit year values).

1.7 General SQL information and tutorials

This book has been recommended by a several people on the MySQL mailing list:

Judith S. Bowman, Sandra L. Emerson and Marcy Darnovsky
The Practical SQL Handbook: Using Structured Query Language
Second Edition
Addison-Wesley
ISBN 0-201-62623-3
http://www.awl.com

This book has also received some recommendations on the mailing list:

Martin Gruber
Understanding SQL
ISBN 0-89588-644-8
Publisher Sybex 510 523 8233
Alameda, CA USA

A SQL tutorial is available on the net at http://www.geocities.com/SiliconValley/Vista/2207/sql1.html

SQL in 21 Tagen (online book in German language): http://www.mut.de/leseecke/buecher/sql/inhalt.htm

1.8 Useful MySQL-related links

1.8.1 Commercial applications that support MySQL

• SupportWizard; Interactive helpdesk on the web (This product includes a licensed copy of MySQL)
• Right Now Web; Web automation for customer service
• Bazaar; Interactive Discussion Forums with web interface
• PhoneSweepT is the world's first commercial Telephone Scanner. Many break-ins in recent years have come not through the Internet, but through unauthorized dial-up modems. PhoneSweep lets you find these modems by repeatedly placing phone calls to every phone number that your organization controls. PhoneSweep has a built-in expert system that can recognize more than 250 different kinds of remote-access programs, including Carbon CopyT, pcANYWHERET, and Windows NT RAS. All information is stored in the SQL database. It then generates a comprehensive report detailing which services were discovered on which dial-up numbers in your organization.

1.8.2 SQL Clients

• MySQL Editor/Utility for MS Windows Platforms.
• KDE MySQL client
• Kiosk; a MySQL client for database management. Written in Perl. Will be a part of Bazaar.

1.8.3 Web development tools that support MySQL

• PHP: A server-side HTML-embedded scripting language
• The Midgard Application Server; a powerful Web development environment based on MySQL and PHP
• SmartWorker is a platform for web application development
• XSP: e(X)tendible (s)erver (p)ages and is a HTML embedded tag language written in Java (previously known as XTAGS)
• dbServ is an extension to a web server to integrate databases output into your HTML code. You may use any HTML function in your output. Only the client will stop you. It works as standalone server or as JAVA servlet.
• Platform independent ASP from Chili!Soft
• MySQL + PHP demos
• ForwardSQL: HTML interface to manipulate MySQL databases
• WWW-SQL: Display database information
• Minivend: A Web shopping cart
• HeiTML: A server-side extension of HTML and a 4GL language at the same time
• Metahtml: A Dynamic Programming Language for WWW Applications
• VelocityGen for Perl and TCL
• Hawkeye Internet Server Suite
• Network Database Connection For Linux
• WDBI: Web browser as a universal front end to databases which supports MySQL well.
• WebGroove Script: HTML compiler and server-side scripting language
• A server-side web site scripting language
• How to use MySQL with Coldfusion on Solaris
• Calistra's ODBC MySQL Administrator
• Webmerger This CGI tool interprets files and generates dynamic output based on a set of simple tags. Ready-to-run drivers for MySQL and PostgreSQL through ODBC.
• PHPclub. Tips and tricks for PHP
• MySQL and Perl Scripts
• The Widgetchuck; Web Site Tools and Gadgets
• AdCycle advertising management software

1.8.4 Databse design tools with MySQL support

• "Dezign for databases" is a database development tool using an rick> entity relationship diagram (ERD).

1.8.5 Web servers with MySQL tools

• An Apache authentication module
• The Roxen Challenger Web server

1.8.6 Extensions for other programs

A Delphi interface to MySQL. With source code. By Matthias Fichtner.
• TmySQL; A library to use MySQL with Delphi
• Delphi TDataset-component
• Support for BIND (The Internet Domain Name Server)
• Sendmail extensions using MySQL

1.8.7 ODBC related links

• Popular iODBC Driver Manager (libiodbc) now available in Open Source format
• The FreeODBC Pages

1.8.8 API related links

• www.jppp.com Partially implemented TDataset-compatible components for MySQL.
• qpopmysql A patch to allow POP3 authentication from a MySQL database. There's also a link to Paul Khavkine's patch for Procmail to allow any MTA to deliver to users in a MySQL database.
• Visual Basic class generator for Active X
• Client libraries for the Macintosh
• MySQL binding to Free Pascal

1.8.9 Other MySQL-related links

• Registry of Web providers who support MySQL Links about using MySQL in Japan/Asia
• Commercial Web defect tracking system
• PTS: Project Tracking System
• Job and software tracking system
• ExportSQL: A script to export data from Access95+
• SAL (Scientific Applications on Linux) MySQL entry
• A consulting company which mentions MySQL in the right company
• PMP Computer Solutions. Database developers using MySQL and mSQL
• Airborne Early Warning Association
• MySQL UDF Registry
• Y2K tester

1.8.10 SQL and database interfaces

• KMySQL KMySQL is a database client for KDE that primarily supports MySQL.
• The JDBC database access API
• Patch for mSQL TCL
• EasySQL: An ODBC-like driver manager
• A REXX interface to SQL databases
• TCL interface

1.8.11 Examples of MySQL use

• Little6 Inc An online contract and job finding site that is powered by MySQL, PHP3 and Linux.
• DELECis A tool which makes it very easy to create an automatically generated table documentation. They have used MySQL as an example.
• Steve Fambro Uses MySQL and webmerger. There is an employee database, and a license plate database with all of the registered Utah vehicles (over 1.2 million). The License plate field is indexed.....so the *searches* are instantaneous.
• World Records A search engine for information about music that uses MySQL and PHP.
• Examples using MySQL; (check Top 10)
• A Contact Database using MySQL and PHP
• Web based interface and Community Calender with PHP
• Perl package to generate html from a SQL table structure and for generating SQL statements from an html form.
• Basic telephone database using DBI/DBD.
• Installing new Perl modules that require locally installed modules
• JDBC examples by Daniel K. Schneider
• SQL BNF
• Object Oriented Concepts Inc; CORBA applications with examples in source
• DBWiz; Includes an example of how to manage own cursors in VB
• Pluribus Pluribus, is a free search engine that learns to improve the quality of its results over time. Pluribus works by recording which pages a user prefers among those returned for a query. A user votes for a page by selecting it; Pluribus then uses that knowledge to improve the quality of the results when someone else submits the same (or similar) query. Uses PHP and MySQL.
• Stopbit A technology news site using MySQL and PHP
• Example scripts at Jokes2000
• MySQL-perl tutorial
• PHP/MySQL Tutorial
• FutureForum Web Discussion Software
• http://www.linuxsupportline.com/~kalendar/ KDE based calendar manager The calendar manager has both single user (file based) and multi user (MySQL database) support.
• Example of storing/retrieving images with MySQL and CGI
• Online shopping cart system
• Old Photo Album The album is a collaborative popular history of photography project that generates all pages from data stored in a MySQL database. Pages are dynamically generated through a php3 interface to the database content. Users contribute images and descriptions. Contributed images are stored on the web server to avoid storing them in the database as BLOBs. All other information is stored in on the shared MySQL server.

1.8.12 General database links

• Database Jump Site
• Homepage of the webdb-l (Web Databases) mailing list.
• Perl DBI/DBD modules homepage
• Cygwin tools (MySQL +Apache + PHP under Win32
• dbasecentral.com; Development and distribution of powerful and easy-to-use database applications and systems.
• Tek-Tips Forums Tek-Tips Forums are 800+ independent peer-to-peer non-commercial support forums for Computer Professionals. Features include automatic e-mail notification of responses, a links library, and member confidentiality guaranteed.

There are also many web pages that use MySQL. See section A Some MySQL users. Send any additions to this list to MySQL logo somewhere (It is okay to have it on a "used tools" page or something similar) to be added.

2 MySQL mailing lists and how to ask questions or report errors (bugs)

2.1 The MySQL mailing lists

To subscribe to the main MySQL mailing list, send a message to the electronic mail address mysql-subscribe@lists.mysql.com.

To unsubscribe from the main MySQL mailing list, send a message to the electronic mail address mysql-unsubscribe@lists.mysql.com.

Only the address to which you send your messages is significant. The subject line and the body of the message are ignored.

If your reply address is not valid, you can specify your address explicitly. Adding a hyphen to the subscribe or unsubscribe command word, followed by your address with the `@' character in your address replaced by a `='. For example, to subscribe john@host.domain, send a message to mysql-subscribe-john=host.domain@lists.mysql.com.

Mail to mysql-subscribe@lists.mysql.com or ezmlm mailing list processor. Information about ezmlm is available at the ezmlm Website.

To post a message to the list itself, send your message to mysql@lists.mysql.com. However, please do not send mail about subscribing or unsubscribing to mysql@lists.mysql.com, since any mail sent to that address is distributed automatically to thousands of other users.

Your local site may have many subscribers to mysql@lists.mysql.com. If so, it may have a local mailing list, so that messages sent from lists.mysql.com to your site are propagated to the local list. In such cases, please contact your system administrator to be added to or dropped from the local MySQL list.

The following MySQL mailing lists exist:

announce

This is for announcement of new versions of MySQL and related programs. This is a low volume list that we think all MySQL users should be on.

mysql

The main list for general MySQL discussion. Please note that some topics are better discussed on the more-specialized lists. If you post to the wrong list, you may not get an answer!

mysql-digest

The mysql list in digest form. That means you get all individual messages, sent as one large mail message once a day.

java

Discussion about MySQL and Java. Mostly about the JDBC drivers.

java-digest

A digest version of the java list.

win32

All things concerning MySQL on Microsoft operating systems such as Windows NT.

win32-digest

A digest version of the win32 list.

myodbc

All things concerning connecting to MySQL with ODBC.

myodbc-digest

A digest version of the myodbc list.

msql-mysql-modules

A list about the Perl support in MySQL.

msql-mysql-modules-digest

A digest version of the msql-mysql-modules list.

developer

A list for people who work on the MySQL code.

developer-digest

A digest version of the developer list.

You subscribe or unsubscribe to all lists in the same way as described above. In your subscribe or unsubscribe message, just put the appropriate mailing list name rather than mysql. For example, to subscribe to or unsubscribe from the myodbc list, send a message to

2.2 Asking questions or reporting bugs

Before posting a bug report or question, please do the following:

• Start by searching the MySQL online manual at:

  http://www.mysql.com/Manual_chapter/manual_toc.html
  

  We try to keep the manual up to date by updating it frequently with solutions to newly found problems!
• Search the MySQL mailing list archives:

  http://www.mysql.com/doc.html
  

• You can also use http://www.mysql.com/search.html to search all the web pages (including the manual) that are located at http://www.mysql.com/.

If you can't find an answer in the manual or the archives, check with your local MySQL expert. If you still can't find an answer to your question, go ahead and read the next section about how to send mail to

2.3 How to report bugs or problems

Writing a good bug report takes patience, but doing it right the first time saves time for us and for you. This section will help you write your report correctly so that you don't waste your time doing things that may not help us much or at all.

We encourage everyone to use the mysqlbug script to generate a bug report (or a report about any problem), if possible. mysqlbug can be found in the `scripts' directory in the source distribution, or, for a binary distribution, in the `bin' directory under your MySQL installation directory. If you are unable to use mysqlbug, you should still include all the necessary information listed in this section.

The mysqlbug script helps you generate a report by determining much of the following information automatically, but if something important is missing, please include it with your message! Please read this section carefully and make sure that all the information described here is included in your report.

Remember that it is possible to respond to a message containing too much information, but not to one containing too little. Often people omit facts because they think they know the cause of a problem and assume that some details don't matter. A good principle is: if you are in doubt about stating something, state it! It is a thousand times faster and less troublesome to write a couple of lines more in your report than to be forced to ask again and wait for the answer because you didn't include enough information the first time.

The most common errors are that people don't indicate the version number of the MySQL distribution they are using, or don't indicate what platform they have MySQL installed on (including the platform version number). This is highly relevant information and in 99 cases out of 100 the bug report is useless without it! Very often we get questions like "Why doesn't this work for me?" and then we find that the feature requested wasn't implemented in that MySQL version, or that a bug described in a report has been fixed already in newer MySQL versions. Sometimes the error is platform dependent; in such cases, it is next to impossible to fix anything without knowing the operating system and the version number of the platform.

Remember also to provide information about your compiler, if it is related to the problem. Often people find bugs in compilers and think the problem is MySQL related. Most compilers are under development all the time and become better version by version, too. To determine whether or not your problem depends on your compiler, we need to know what compiler is used. Note that every compiling problem should be regarded as a bug report and reported accordingly.

It is most helpful when a good description of the problem is included in the bug report. That is, a good example of all the things you did that led to the problem and the problem itself exactly described. The best reports are those that include a full example showing how to reproduce the bug or problem.

If a program produces an error message, it is very important to include the message in your report! If we try to search for something from the archives using programs, it is better that the error message reported exactly matches the one that the program produces. (Even the case sensitivity should be observed!) You should never try to remember what the error message was; instead, copy and paste the entire message into your report!

Please include the following information in your report:

• The version number of the MySQL distribution you are using (for example, MySQL 3.22.22). You can find out which version you are running by executing mysqladmin version. mysqladmin can be found in the `bin' directory under your MySQL installation directory.
• The manufacturer and model of the machine you are working on.
• The operating system name and version. For most operating systems, you can get this information by executing the Unix command uname -a.
• Sometimes the amount of memory (real and virtual) is relevant. If in doubt, include these values.
• If you are using a source distribution of MySQL, the name and version number of the compiler used is needed. If you have a binary distribution, the distribution name is needed.
• If the problem occurs during compilation, include the exact error message(s) and also a few lines of context around the offending code in the file where the error occurred.
• If any database table is related to the problem, include the output from mysqldump --no-data db_name tbl_name1 tbl_name2 ... This is very easy to do and is a powerful way to get information about any table in a database that will help us create a situation matching the one you have.
• For speed-related bugs or problems with SELECT statements, you should always include the output of EXPLAIN SELECT ..., and at least the number of rows that the SELECT statement produces. The more information you give about your situation, the more likely it is that someone can help you! For example, the following is an example of a very good bug report (it should of course be posted with the mysqlbug script): Example run under the mysql command line tool:

  mysql> SHOW VARIABLES;
  mysql> EXPLAIN SELECT ...
         <output-from-EXPLAIN>
  mysql> FLUSH STATUS;
  mysql> SELECT ...
         <A short version of the output from SELECT,
         including the time taken to run the query>
  mysql> SHOW STATUS;
         <output from SHOW STATUS>
  

• If a bug or problem occurs while running MySQL, try to provide an input script that will reproduce the anomaly. This script should include any necessary source files. The more closely the script can reproduce your situation, the better. If you can't provide a script, you should at least include the output from mysqladmin variables extended-status processlist in your mail to provide some information of how your system is performing!
• If you think that MySQL produces a strange result from a query, include not only the result, but also your opinion of what the result should be and an account describing the basis for your opinion.
• When giving an example of the problem, it's better to use the variable names, table names, etc., that exist in your actual situation than to come up with new names. The problem could be related to the name of a variable, table, etc.! These cases are rare, perhaps, but it is better to be safe than sorry. After all, it should be easier for you to provide an example that uses your actual situation and it is by all means better for us. In case you have data you don't want to show to others, you can use ftp to transfer it to ftp://www.mysql.com/pub/mysql/secret/. If the data are really top secret and you don't want to show them even to us, then go ahead and provide an example using other names, but please regard this as the last choice.
• Include all the options given to the relevant programs, if possible. For example, indicate the options that you use when you start the mysqld daemon and that you use to run any MySQL client programs. The options to programs like mysqld and mysql, and to the configure script are often keys to answers and very relevant! It is never a bad idea to include them anyway! If you use any modules, such as Perl or PHP, please include the version number(s) of those as well.
• If you can't produce a test case in a few rows, or if the test table is too big to be mailed to the mailing list (more than 10 rows), you should dump your tables using mysqldump and create a `README' file that describes your problem. Create a compressed archive of your files using tar and gzip or zip, and use ftp to transfer the archive to ftp://www.mysql.com/pub/mysql/secret/. Then send a short description of the problem to
• If your question is related to the privilege system, please include the output of mysqlaccess, the output of mysqladmin reload and all the error messages you get when trying to connect! When you test your privileges, you should first run mysqlaccess. After this, execute mysqladmin reload version, and last you should try to connect with the program that gives you trouble. mysqlaccess can be found in the `bin' directory under your MySQL installation directory.
• If you have a patch for a bug, that is good. But don't assume the patch is all we need or that we will use it even if you don't provide some necessary information such as test cases showing the bug that your patch fixes. We might find problems with your patch or we might not understand it at all; if so, we can't use it. If we can't verify exactly what the patch is meant for, we won't use it. Test cases will help us here. Show that the patch will handle all the situations that may occur. If we find a borderline case (even a rare one) where the patch won't work, the patch may be useless.
• Guesses about what the bug is, why it occurs, or what it depends on, are usually wrong. Even we can't guess such things without first using a debugger to determine the real cause of a bug.
• Indicate in your mail message that you have checked the reference manual and mail archive so others know that you have tried to solve your problem yourself.
• If you get a parse error, please check your syntax closely! If you can't find something wrong with it, it's extremely likely that your current version of MySQL doesn't support the query you are using. If you are using the current version and the manual at http://www.mysql.com/doc.html doesn't cover the syntax you are using, MySQL doesn't support your query. In this case, your only options are to implement the syntax yourself or email If the manual covers the syntax you are using, but you have an older version of MySQL, you should check the MySQL change history to see when the syntax was implemented. See section D MySQL change history. In this case, you have the option of upgrading to a newer version of MySQL.
• If you have a problem such that your data appears corrupt or you get errors when you access some particular table, you should first check and then try repairing your tables with isamchk. See section 13 Maintaining a MySQL installation.
• If you often get corrupted tables you should try to find out when and why this happens! In this case, the `mysql-data-directory/'hostname'.err' file may contain some information about what happened. Please include any relevant information from this file in your bug report! Normally mysqld should NEVER crash a table if nothing killed it in the middle of an update! If you can find the source of why mysqld dies, it's much easier for us to provide you with a fix for the problem!
• If possible, download the most recent version of MySQL and check whether or not it solves your problem. All versions of MySQL are thoroughly tested and should work without problems! We believe in making everything as backward compatible as possible and you should be able to switch MySQL versions in minutes! See section 4.3 Which MySQL version to use.

If you are a support customer, please cross-post the bug report to the appropriate mailing list to see if someone else has experienced (and perhaps solved) the problem.

For information on reporting bugs in MyODBC, see section 16.2 How to report problems with MyODBC.

For solutions to some common problems, see See section 18 Problems and common errors.

When answers are sent to you individually and not to the mailing list, it is considered good etiquette to summarize the answers and send the summary to the mailing list so that others may have the benefit of responses you received that helped you solve your problem!

2.4 Guidelines for answering questions on the mailing list

If you consider your answer to have broad interest, you may want to post it to the mailing list instead of replying directly to the individual who asked. Try to make your answer general enough that people other than the original poster may benefit from it. When you post to the list, please make sure that your answer is not a duplication of a previous answer.

Try to summarize the essential part of the question in your reply; don't feel obliged to quote the entire original message.

Please don't post mail messages from your browser with HTML mode turned on! Many users doesn't read mail with a browser!

3 MySQL licensing and support

This chapter describes MySQL licensing and support arrangements, including:

• Our licensing policies for non-Microsoft and Microsoft operating systems
• The copyrights under which MySQL is distributed (see section 3.2 Copyrights used by MySQL)
• Sample situations illustrating when a license is required (see section 3.4 Example licensing situations)
• Licensing and support costs (see section 3.5 MySQL licensing and support costs), and support benefits (see section 3.6 Types of commercial support)

3.1 MySQL licensing policy

The formal terms of the license for non-Microsoft operating systems such as Unix or OS/2 are specified in section J The MySQL server license for non Microsoft operating systems. Basically, our licensing policy is as follows:

• For normal internal use, MySQL generally costs nothing. You do not have to pay us if you do not want to.
• A license is required if:
  • You sell the MySQL server directly or as a part of another product or service
  • You charge for installing and maintaining a MySQL server at some client site
  • You include MySQL in a distribution that is non redistributable and you charge for some part of that distribution
• For circumstances under which a MySQL license is required, you need a license per machine that runs the mysqld server. However, a multiple-CPU machine counts as a single machine, and there is no restriction on the number of MySQL servers that run on one machine, or on the number of clients concurrently connected to a server running on that machine!
• You do not need a license to include client code in commercial programs. The client access part of MySQL is in the public domain. The mysql command line client includes code from the readline library that is under the GNU Public License.
• For customers who have purchased 10 licenses or a high enough level of support, we provide additional functionality. Currently, this means we provide the pack_isam utility for creating fast compressed read-only databases. (The server includes support for reading such databases but not the packing tool used to create them.) If support agreements generate sufficient revenue, we will probably release this tool under the same license as the MySQL server.
• If your use of MySQL does not require a license, but you like MySQL and want to encourage further development, you are certainly welcome to purchase a license anyway.
• If you use MySQL in a commercial context such that you profit by its use, we ask that you further the development of MySQL by purchasing some level of support. We feel that if MySQL helps your business, it is reasonable to ask that you help MySQL. (Otherwise, if you ask us support questions, you are not only using for free something into which we've put a lot a work, you're asking us to provide free support, too.)

For use under Microsoft operating systems (Win95/Win98/WinNT), you need a MySQL license after a trial period of 30 days, with the exception that licenses may be obtained upon request at no cost for educational use or for university- or government-sponsored research settings. See section K The MySQL license for Microsoft operating systems. A shareware version of MySQL-Win32 that you can try before buying is available at http://www.mysql.com/mysql_w32.htmy. After you have paid, you will get a password that will enable you to access the newest MySQL-Win32 version.

If you have any questions as to whether or not a license is required for your particular use of MySQL, please contact us. See section 3.5.2 Contact information.

If you require a MySQL license, the easiest way to pay for it is to use the license form at TcX's secure server at https://www.mysql.com/license.htmy. Other forms of payment are discussed in section 3.5.1 Payment information.

3.2 Copyrights used by MySQL

There are several different copyrights on the MySQL distribution:

1. The MySQL-specific source needed to build the mysqlclient library and programs in the `client' directory is in the public domain. Each file that is in the public domain has a header which clearly states so. This includes everything in the `client' directory and some parts of the mysys, mystring and dbug libraries.
2. Some small parts of the source (GNU getopt) are covered by the "GNU LIBRARY LIBRARY GENERAL PUBLIC LICENSE". See the `mysys/COPYING.LIB' file.
3. Some small parts of the source (GNU readline) are covered by the "GNU GENERAL PUBLIC LICENSE". See the `readline/COPYING' file.
4. Some parts of the source (the regexp library) are covered by a Berkeley style copyright.
5. The other source needed for the MySQL server on non-Microsoft platforms is covered by the "MySQL FREE PUBLIC LICENSE", which is based on the "Aladdin FREE PUBLIC LICENSE." See section J The MySQL server license for non Microsoft operating systems. When running MySQL on any Microsoft operating system, other licensing applies.

The following points set forth the philosophy behind our copyright policy:

• The SQL client library should be totally free so that it can be included in commercial products without limitations.
• People who want free access to the software into which we have put a lot of work can have it, so long as they do not try to make money directly by distributing it for profit.
• People who want the right to keep their own software proprietary, but also want the value from our work, can pay for the privilege.
• That means normal in-house use is FREE. But if you use MySQL for something important to you, you may want to help further its development by purchasing a license or a support contract. See section 3.6 Types of commercial support.

3.2.1 Possible future copyright changes

We may choose to distribute older versions of MySQL with the GPL in the future. However, these versions will be identified as GNU MySQL. Also, all copyright notices in the relevant files will be changed to the GPL.

3.3 Distributing MySQL commercially

This section is a clarification of the license terms that are set forth in the "MySQL FREE PUBLIC LICENSE" (FPL). See section J The MySQL server license for non Microsoft operating systems.

MySQL may be used freely, including by commercial entities for evaluation or unsupported internal use. However, distribution for commercial purposes of MySQL, or anything containing or derived from MySQL in whole or in part, requires a written commercial license from TcX AB, the sole entity authorized to grant such licenses.

You may not include MySQL "free" in a package containing anything for which a charge is being made, except as noted below.

The intent of the exception provided in the second clause of the license is to allow commercial organizations operating an FTP server or a bulletin board to distribute MySQL freely from it, provided that:

1. The organization complies with the other provisions of the FPL, which include among other things a requirement to distribute the full source code of MySQL and of any derived work, and to distribute the FPL itself along with MySQL;
2. The only charge for downloading MySQL is a charge based on the distribution service and not one based on the content of the information being retrieved (i.e., the charge would be the same for retrieving a random collection of bits of the same size);
3. The server or BBS is accessible to the general public, i.e., the phone number or IP address is not kept secret, and anyone may obtain access to the information (possibly by paying a subscription or access fee that is not dependent on or related to purchasing anything else).

If you want to distribute software in a commercial context that incorporates MySQL and you do not want to meet these conditions, you should contact TcX AB to find out about commercial licensing, which involves a payment. The only ways you legally can distribute MySQL or anything containing MySQL are by distributing MySQL under the requirements of the FPL, or by getting a commercial license from TcX AB.

3.4 Example licensing situations

This section describes some situations illustrating whether or not you must license the MySQL server. Generally these examples involve providing MySQL as part of a product or service that you are selling to a customer, or requiring that MySQL be used in conjunction with your product. In such cases, it is your responsibility to obtain a license for the customer if one is necessary. (This requirement is waived if your customer already has a MySQL license. But the seller must send customer information and the license number to TcX, and the license must be a full license, not an OEM license.)

Note that a single MySQL license covers any number of CPUs/users/customers/mysqld servers on a machine!

3.4.1 Selling products that use MySQL

To determine whether or not you need a MySQL license when selling your application, you should ask whether the proper functioning of your application is contingent on the use of MySQL and whether you include MySQL with your product. There are several cases to consider:

• Does your application require MySQL to function properly? If your product requires MySQL, you need a license for any machine that runs the mysqld server. For example, if you've designed your application around MySQL, then you've really made a commercial product that requires the engine, so you need a license. If your application does not require MySQL, you need not obtain a license. For example, if MySQL just added some new optional features to your product (such as adding logging to a database if MySQL is used rather than logging to a text file), it should fall within normal use, and a license would not be required. In other words, you need a license if you sell a product designed specifically for use with MySQL or that requires the MySQL server to function at all. This is true whether or not you provide MySQL for your client as part of your product distribution. It also depends on what you're doing for the client. Do you plan to provide your client with detailed instructions on installing MySQL with your software? Then your product may be contingent on the use of MySQL; if so, you need to buy a license. If you are simply tying into a database that you expect already to have been installed by the time your software is purchased, then you probably don't need a license.
• Do you include MySQL in a distribution and charge for that distribution? If you include MySQL with a distribution that you sell to customers, you will need a license for any machine that runs the mysqld server, because in this case you are selling a system that includes MySQL. This is true whether the use of MySQL with your product is required or optional.
• Do you neither require for your product nor include MySQL with it? Suppose you want to sell a product that is designed generally to use "some database" and that can be configured to use any of several supported alternative database systems (MySQL, PostgreSQL, or something else). That is, your product does not not require MySQL, but can support any database with a base level of functionality, and you don't rely on anything that only MySQL supports. Does one of you owe us money if your customer actually does choose to use MySQL? In this case, if you don't provide, obtain or set up MySQL for the customer should the customer decide to use it, neither of you need a license. If you do perform that service, see section 3.4.2 Selling MySQL-related services.

3.4.2 Selling MySQL-related services

If you perform MySQL installation on a client's machine and any money changes hands for the service (directly or indirectly), then you must buy a MySQL license.

If you sell an application for which MySQL is not strictly required but can be used, a license may be indicated, depending on how MySQL is set up. Suppose your product neither requires MySQL nor includes it in your product distribution, but can be configured to use MySQL for those customers who so desire. (This would be the case, for example, if your product can use any of a number of database engines.)

If the customer obtains and installs MySQL, no license is needed. If you perform that service for your customer, then a license is needed because then you are selling a service that includes MySQL.

3.4.3 ISP MySQL services

Internet Service Providers (ISPs) often host MySQL servers for their customers.

If you are an ISP that allows customers to install and administer MySQL for themselves on your machine with no assistance from you, neither you nor your customer need a MySQL license.

If you charge for MySQL installation and administrative support as part of your customer service, then you need a license because you are selling a service that includes MySQL.

3.4.4 Running a web server using MySQL

If you use MySQL in conjunction with a web server, you don't have to pay for a license.

This is true even if you run a commercial web server that uses MySQL, since you are not selling MySQL itself. However, in this case we would like you to purchase MySQL support, because MySQL is helping your enterprise.

3.5 MySQL licensing and support costs

Our current license prices are shown below. All prices are in US Dollars. If you pay by credit card, the currency is EURO (European Union Euro) so the prices will differ slightly.

For high volume (OEM) purchases, the following prices apply:

For OEM purchases, you must act as the middle-man for eventual problems or extension requests from your users. We also require that OEM customers have at least an extended email support contract.

If you have a low-margin high-volume product, you can always talk to us about other terms (for example, a percent of the sale price). If you do, please be informative about your product, pricing, market and any other information that may be relevant.

After buying 10 MySQL licenses, you will get a personal copy of the pack_isam utility. You are not allowed to redistribute this utility but you can distribute tables packed with it.

A full-price license is not a support agreement and includes very minimal support. This means that we try to answer any relevant question. If the answer is in the documentation, we will direct you to the appropriate section. If you have not purchased a license or support, we probably will not answer at all.

If you discover what we consider a real bug, we are likely to fix it in any case. But if you pay for support we will notify you about the fix status instead of just fixing it in a later release.

More comprehensive support is sold separately. Descriptions of what each level of support includes are given in section 3.6 Types of commercial support. Costs for the various types of commercial support are shown below. Support level prices are in EURO (European Union Euro). One EURO is about 1.17 USD.

You may upgrade from any lower level of support to a higher level of support for the difference between the prices of the two support levels.

3.5.1 Payment information

Currently we can take SWIFT payments, cheques or credit cards.

Payment should be made to:

Postgirot Bank AB
105 06 STOCKHOLM, SWEDEN

TCX DataKonsult AB
BOX 6434
11382 STOCKHOLM, SWEDEN

SWIFT address: PGSI SESS
Account number: 96 77 06 - 3

Specify: license and/or support and your name and email address.

In Europe and Japan you can use EuroGiro (that should be less expensive) to the same account.

If you want to pay by cheque, make it payable to "Monty Program KB" and mail it to the address below:

TCX DataKonsult AB
BOX 6434, Torsgatan 21
11382 STOCKHOLM, SWEDEN

If you want to pay by credit card over the Internet, you can use TcX's secure license form.

You can also print a copy of the license form, fill it in and send it by fax to:

+46-8-729 69 05

If you want us to bill you, you can use the license form and write "bill us" in the comment field. You can also mail a message to with your company information and ask us to bill you.

3.5.2 Contact information

For commercial licensing, or if you have any questions about any of the information in this section, please contact the MySQL licensing team. The much preferred method is by E-Mail to these may take much longer (Fax +46-8-729 69 05).

David Axmark
Detron HB
Kungsgatan 65 B
753 21 UPPSALA
SWEDEN
Voice Phone +46-18-10 22 80     (Timezone GMT+1. Swedish and English spoken)

3.6 Types of commercial support

3.6.1 Basic email support

Basic email support is a very inexpensive support option and should be thought of more as a way to support our development of MySQL than as a real support option.

At this support level, the MySQL mailing lists are the preferred means of communication. Questions normally should be mailed to the primary mailing list (mysql@lists.mysql.com) or one of the other regular lists (for example, mysql-win32@lists.mysql.com for Windows-related MySQL questions), as someone else already may have experienced and solved the problem you have. See section 2.2 Asking questions or reporting bugs.

However, by purchasing basic email support, you also have access to the support address mysql-support@mysql.com, which is not available as part of the minimal support that you get by purchasing a MySQL license. This means that for especially critical questions, you can cross-post your message to mysql-support@mysql.com. (If the message contains sensitive data, you should post only to mysql-support@mysql.com.)

REMEMBER! to ALWAYS include your registration number and expiration date when you send a message to

Basic email support includes the following types of service:

• If your question is already answered in the manual, we will inform you of the correct section in which you can find the answer. If the answer is not in the manual, we will point you in the right direction to solve your problem.
• We guarantee a timely answer for your email messages. We can't guarantee that we can solve any problem, but at least you will receive an answer if we can contact you by email.
• We will help with unexpected problems when you install MySQL from a binary distribution on supported platforms. This level of support does not cover installing MySQL from a source distribution. "Supported" platforms are those for which MySQL is known to work. See section 4.2 Operating systems supported by MySQL.
• We will help you with bugs and missing features. Any bugs that are found are fixed for the next MySQL release. If the bug is critical for you, we will mail you a patch for it as soon the bug is fixed. Critical bugs always have the highest priority for us, to ensure that they are fixed as soon as possible.
• Your suggestions for the further development of MySQL will be taken into consideration. By taking email support you have already helped the further development of MySQL. If you want to have more input, upgrade to a higher level of support.
• If you want us to help optimize your system, you must upgrade to a higher level of support.

3.6.2 Extended email support

Extended email support includes everything in basic email support with these additions:

• Your email will be dealt with before mail from basic email support users and non-registered users.
• Your suggestions for the further development of MySQL will receive strong consideration. Simple extensions that suit the basic goals of MySQL are implemented in a matter of days. By taking extended email support you have already helped the further development of MySQL.
• We include a binary version of the pack_isam packing tool for creating fast compressed read-only databases (it does not support BLOB or TEXT types yet). The current server includes support for reading such databases but not the packing tool used to create them.
• Typical questions that are covered by extended email support are:
  • We will answer and (within reason) solve questions that relate to possible bugs in MySQL. As soon as the bug is found and corrected, we will mail a patch for it.
  • We will help with unexpected problems when you install MySQL from a source or binary distribution on supported platforms.
  • We will answer questions about missing features and offer hints how to work around them.
  • We will provide hints on optimizing mysqld for your situation.
• You are allowed to influence the priority of items on the MySQL TODO. This will ensure that the features you really need will be implemented sooner than they might be otherwise.

3.6.3 Login support

Login support includes everything in extended email support with these additions:

• Your email will be dealt with even before mail from extended email support users.
• Your suggestions for the further development of MySQL will be taken into very high consideration. Realistic extensions that can be implemented in a couple of hours and that suit the basic goals of MySQL will be implemented as soon as possible.
• If you have a very specific problem, we can try to log in on your system to solve the problem "in place."
• Like any database vendor, we can't guarantee that we can rescue any data from crashed tables, but if the worst happens we will help you rescue as much as possible. MySQL has proven itself very reliable, but anything is possible due to circumstances beyond our control (for example, if your system crashes or someone kills the server by executing a kill -9 command).
• We will provide hints on optimizing your system and your queries.
• You are allowed to call a MySQL developer (in moderation) and discuss your MySQL-related problems.

3.6.4 Extended login support

Extended login support includes everything in login support with these additions:

• Your email has the highest possible priority.
• We will actively examine your system and help you optimize it and your queries. We may also optimize and/or extend MySQL to better suit your needs.
• You may also request special extensions just for you. For example:

  mysql> select MY_CALCULATION(col_name1,col_name2) from tbl_name;
  

• We will provide a binary distribution of all important MySQL releases for your system, as long as we can get an account on a similar system. In the worst case, we may require access to your system to be able to create a binary distribution.
• If you can provide accommodations and pay for traveler fares, you can even get a MySQL developer to visit you and offer you help with your troubles. Extended login support entitles you to one personal encounter per year, but we are as always very flexible towards our customers!

4 Installing MySQL

This chapter describes how to obtain and install MySQL:

• For a list of sites from which you can obtain MySQL, see section 4.1 How to get MySQL.
• To see which platforms are supported, see section 4.2 Operating systems supported by MySQL.
• Several versions of MySQL are available, in both binary and source distributions. To determine which version and type of distribution you should use, see section 4.4 How and when updates are released.
• Installation instructions for binary and source distributions are described in section 4.6 Installing a MySQL binary distribution, and section 4.7 Installing a MySQL source distribution. Each set of instructions includes a section on system-specific problems you may run into.
• For post-installation procedures, see section 4.15 Post-installation setup and testing. These procedures apply whether you install MySQL using a binary or source distribution.

4.1 How to get MySQL

Check the MySQL home page for information about the current version and for downloading instructions.

However, the Internet connection at TcX is not so fast; we would prefer that you do the actual downloading from one of the mirror sites listed below.

Please report bad or out of date mirrors to webmaster@mysql.com.

Europe:

• Austria [Univ. of Technology/Vienna] WWW FTP
• Bulgaria [Naturella] FTP
• Croatia [HULK] WWW FTP
• Czech Republic [Masaryk University in Brno] WWW FTP
• Czech Republic [www.sopik.cz] WWW
• Denmark [Ake] WWW
• Denmark [SunSITE] WWW FTP
• Estonia [OKinteractive] WWW
• France [minet] WWW
• Finland [EUnet] WWW
• Finland [clinet] FTP
• Germany [Bonn University, Bonn] WWW FTP
• Germany [Wolfenbuettel] WWW FTP
• Germany [Staufen] WWW
• Germany [Cable & Wireless] FTP
• Greece [NTUA, Athens] WWW FTP
• Italy [Teta Srl] WWW
• Poland [Sunsite] WWW FTP
• Portugal [lerianet] WWW FTP
• Russia [DirectNet] WWW
• Russia [IZHCOM] WWW FTP
• Russia [Scientific Center/Chernogolovka] FTP
• Romania [Timisoara] WWW FTP
• Romania [Bucharest] WWW FTP
• Spain [MasterD] WWW
• Sweden [Sunet] WWW FTP
• Switzerland [Sunsite] WWW FTP
• UK [Omnipotent/UK] WWW FTP
• UK [PLiG/UK] WWW FTP
• UK [SunSITE] WWW FTP
• Ukraine [PACO] WWW FTP

North America:

• Canada [Tryc] WWW
• Canada [Cyberus] WWW FTP
• USA [Hurricane Electric/San Jose] WWW
• USA [Netcasting/West Coast] FTP
• USA [Circle Net/North Carolina] WWW
• USA [Gina net/Florida] WWW
• USA [pingzero/Los Angeles] WWW
• USA [Wisconsin University/Wisconsin] WWW FTP
• USA [DIGEX] FTP

South America:

• Brazil [Matrix] WWW
• Chile [Vision] WWW

Asia:

• China [Freecode] WWW
• Korea [KREONet] WWW
• Japan [Soft Agency] WWW
• Japan [Nagoya Syouka University] WWW FTP
• Singapore [HJC] WWW FTP
• Taiwan [HT] WWW

Australia:

• Australia [AARNet/Queensland] WWW FTP
• Australia [Tas] WWW FTP
• Australia [Blue Planet/Melbourne] WWW
• Australia [ITworks Consulting/Victoria] WWW

Africa:

• South-Africa [Mweb/] WWW
• South-Africa [The Internet Solution/Johannesburg] FTP

4.2 Operating systems supported by MySQL

We use GNU Autoconf so it is possible to port MySQL to all modern systems with working Posix threads and a C++ compiler. (To compile only the client code, a C++ compiler is required but not threads.) We use and develop the software ourselves primarily on Sun Solaris (versions 2.5 & 2.6) and to a lesser extent on RedHat Linux 5.0.

MySQL has been reported to compile sucessfully on the following operating system/thread package combinations. Note that for many operating systems, the native thread support works only in the latest versions.

• AIX 4.x with native threads
• BSDI 2.x with the included MIT-pthreads package
• BSDI 3.0, 3.1 and 4.x with native threads
• DEC UNIX 4.x with native threads
• FreeBSD 2.x with the included MIT-pthreads package
• FreeBSD 3.x with native threads
• HP-UX 10.20 with the included MIT-pthreads package
• HP-UX 11.x with the native threads.
• Linux 2.0+ with LinuxThreads 0.7.1 or glibc 2.0.7
• NetBSD 1.3/1.4 Intel and NetBSD 1.3 Alpha (Requires GNU make)
• OpenBSD 2.x with the included MIT-pthreads package
• OS/2 Warp 3, FixPack 29 and OS/2 Warp 4, FixPack 4
• SGI Irix 6.x with native threads
• Solaris 2.5, 2.6 and 2.7 with native threads on SPARC and x86
• SunOS 4.x with the included MIT-pthreads package
• SCO OpenServer with a recent port of the FSU Pthreads package
• SCO UnixWare 7.0.1
• Tru64 Unix
• Win95, Win98 and NT (the newest version is currently available only for users with a MySQL license or MySQL email support). For those who wish to test before they buy, we have released MySQL 3.21.29 (an older version) as shareware.

4.3 Which MySQL version to use

The first decision to make is whether you want to use the latest development release or the last stable release:

• Normally, if you are beginning to use MySQL for the first time or trying to port it to some system for which there is no binary distribution, we recommend going with the development release (currently 3.22.x). This is because there are usually no really serious bugs in the development release, and you can easily test it on your machine with the crash-me and benchmark tests. See section 11 The MySQL benchmark suite.
• Otherwise, if you are running an old system and want to upgrade, but don't want to take chances with 3.22, you should upgrade to 3.21.33. We have tried to fix only fatal bugs and make small, relatively safe changes to that version.

The second decision to make is whether you want to use a source distribution or a binary distribution:

• If you want to run MySQL on a platform for which a current binary distribution exists, use that. Generally, it will be easier to install than a source distribution.
• If you want to read (and/or modify) the C and C++ code that makes up MySQL, you should get a source distribution. The source code is always the ultimate manual. Source distributions also contain more tests and examples than binary distributions.

The MySQL naming scheme uses release numbers that consist of three numbers and a suffix. For example, a release name like mysql-3.21.17-beta is interpreted like this:

• The first number (3) describes the file format. All version 3 releases have the same file format. When a version 4 appears, every table will have to be converted to the new format (nice tools for this will be included, of course).
• The second number (21) is the release level. Normally there are two to choose from. One is the release/stable branch (currently 21) and the other is the development branch (currently 22) . Normally both are stable, but the development version may have quirks, missing documentation on new features or may fail to compile on some systems.
• The third number (17) is the version number within the release level. This is incremented for each new distribution. Usually you want the latest version for the release level you have choosen.
• The suffix (beta) indicates the stability level of the release. The possible suffixes are:
  • alpha indicates that the release contains some large section of new code that hasn't been 100% tested. Known bugs (usually there are none) should be documented in the News section. See section D MySQL change history. There are also new commands and extensions in most alpha releases.
  • beta means that all new code has been tested. No major new features were added. There should be no known bugs.
  • gamma is a beta that has been around a while and seems to work fine. This is what many other companies call a release.
  • If there is no suffix, it means that the version has been run for a while at many different sites with no reports of bugs other than platform-specific bugs. This is what we call a stable release.

All versions of MySQL are run through our standard tests and benchmarks to ensure that they are relatively safe to use. Since the standard tests are extended over time to check for all previously found bugs, the test suite keeps getting better.

Note that all releases have been tested at least with:

An internal test suite

This is part of a production system for a customer. It has many tables with hundreds of megabytes of data.

The MySQL benchmark suite

This runs a range of common queries. It is also a test to see whether the latest batch of optimizations actually made the code faster. See section 11 The MySQL benchmark suite.

The crash-me test

This tries to determine what features the database supports and what its capabilities and limitations are. See section 11 The MySQL benchmark suite.

Another test is that we use the newest MySQL version in our internal production environment, on at least one machine. We have more than 100 gigabytes of data to work with.

4.4 How and when updates are released

MySQL is evolving quite rapidly here at TcX and we want to share this with other MySQL users. We try to make a release when we have very useful features that others seem to have a need for.

We also try to help out users who request features that are easy to implement. We also take note of what our licensed users want to have and we especially take note of what our extended email supported customers want and try to help them out.

No one has to download a new release. The News section will tell you if the new release has something you really want. See section D MySQL change history.

We use the following policy when updating MySQL:

• For each minor update, the last number in the version string is incremented. When there are major new features or minor incompatibilities with previous versions, the second number in the version string is incremented. When the file format changes, the first number is increased.
• Stable tested releases are meant to appear about 1-2 times a year, but if small bugs are found, a release with only bug-fixes will be released.
• Working releases are meant to appear about every 1-8 weeks.
• Binary distributions for some platforms will be made by us for major releases. Other people may make binary distributions for other systems but probably less frequently.
• We usually make patches available as soon as we have located and fixed small bugs.
• For non-critical but annoying bugs, we will make patches available if they are sent to us. Otherwise we will combine many of them into a larger patch.
• If there is, by any chance, a fatal bug in a release we will make a new release as soon as possible. We would like other companies to do this, too. :)

The current stable release is 3.22; We have already moved active development to 3.23. Bugs will still be fixed in the stable version. We don't believe in a complete freeze, as this also leaves out bug fixes and things that "must be done". "Somewhat frozen" means that we may add small things that "almost surely will not affect anything that's already working".

4.5 Installation layouts

This section describes the default layout of the directories created by installing binary and source distributions.

A binary distribution is installed by unpacking it at the installation location you choose (typically `/usr/local/mysql') and creates the following directories in that location:

A source distribution is installed after you configure and compile it. By default, the installation step installs files under `/usr/local', in the following subdirectories:

Within an installation directory, the layout of a source installation differs from that of a binary installation in the following ways:

• The mysqld server is installed in the `libexec' directory rather than in the `bin' directory.
• The data directory is `var' rather than `data'.
• mysql_install_db is installed in the `/usr/local/bin' directory rather than in `/usr/local/mysql/scripts'.
• The header file and library directories are `include/mysql' and `lib/mysql' rather than `include' and `lib'.

4.6 Installing a MySQL binary distribution

You need the following tools to install a MySQL binary distribution:

• GNU gunzip to uncompress the distribution.
• A reasonable tar to unpack the distribution. GNU tar is known to work.

An alternative installation method under Linux is to use RPM (RedHat Package Manager) distributions. See section 4.6.1 Linux RPM notes.

If you run into problems, PLEASE ALWAYS USE mysqlbug when posting questions to mysql@lists.mysql.com. Even if the problem isn't a bug, mysqlbug gathers system information that will help others solve your problem. By not using mysqlbug, you lessen the likelihood of getting a solution to your problem! You will find mysqlbug in the `bin' directory after you unpack the distribution. See section 2.3 How to report bugs or problems.

The basic commands you must execute to install and use a MySQL binary distribution are:

shell> gunzip < mysql-VERSION-OS.tar.gz | tar xvf -
shell> ln -s mysql-VERSION-OS mysql
shell> cd mysql
shell> scripts/mysql_install_db
shell> bin/safe_mysqld &

You can add new users using the bin/mysql_setpermission script if you install the DBI and Msql-Mysql-modules Perl modules.

Here follows a more detailed description:

To install a binary distribution, follow the steps below, then proceed to section 4.15 Post-installation setup and testing, for post-installation setup and testing:

1. Pick the directory under which you want to unpack the distribution, and move into it. In the example below, we unpack the distribution under `/usr/local' and create a directory `/usr/local/mysql' into which MySQL is installed. (The following instructions therefore assume you have permission to create files in `/usr/local'. If that directory is protected, you will need to perform the installation as root.)
2. Obtain a distribution file from one of the sites listed in section 4.1 How to get MySQL. MySQL binary distributions are provided as compressed tar archives and have names like `mysql-VERSION-OS.tar.gz', where VERSION is a number (e.g., 3.21.15), and OS indicates the type of operating system for which the distribution is intended (e.g., pc-linux-gnu-i586).
3. Unpack the distribution and create the installation directory:

   shell> gunzip < mysql-VERSION-OS.tar.gz | tar xvf -
   shell> ln -s mysql-VERSION-OS mysql
   

   The first command creates a directory named `mysql-VERSION-OS'. The second command makes a symbolic link to that directory. This lets you refer more easily to the installation directory as `/usr/local/mysql'.
4. Change into the installation directory:

   shell> cd mysql
   

   You will find several files and subdirectories in the mysql directory. The most important for installation purposes are the `bin' and `scripts' subdirectories.

   `bin'

   This directory contains client programs and the server You should add the full pathname of this directory to your PATH environment variable so that your shell finds the MySQL programs properly.

   `scripts'

   This directory contains the mysql_install_db script used to initialize the server access permissions.

5. If you would like to use mysqlaccess and have the MySQL distribution in some nonstandard place, you must change the location where mysqlaccess expects to find the mysql client. Edit the `bin/mysqlaccess' script at approximately line 18. Search for a line that looks like this:

   $MYSQL     = '/usr/local/bin/mysql';    # path to mysql executable
   

   Change the path to reflect the location where mysql actually is stored on your system. If you do not do this, you will get a broken pipe error when you run mysqlaccess.
6. Create the MySQL grant tables (necessary only if you haven't installed MySQL before):

   shell> scripts/mysql_install_db
   

   Note that MySQL versions older than 3.22.10 started the MySQL server when you run mysql_install_db. This is no longer true!
7. If you want to install support for the Perl DBI/DBD interface, see section 4.10 Perl installation comments.
8. If you would like MySQL to start automatically when you boot your machine, you can copy support-files/mysql.server to the location where your system has its startup files. More information can be found in the support-files/mysql.server script itself, and in section 4.15.3 Starting and stopping MySQL automatically.

After everything has been unpacked and installed, you should initialize and test your distribution.

You can start the MySQL server with the following command:

shell> bin/safe_mysqld &

See section 4.15 Post-installation setup and testing.

4.6.1 Linux RPM notes

The recommended way to install MySQL on Linux is by using an RPM file. The MySQL RPMs are currently being built on a RedHat 5.2 system but should work on other versions of Linux that support rpm and use glibc.

If you have problems with an RPM file, for example Sorry, the host 'xxxx' could not be looked up, see section 4.6.3.1 Linux notes.

The RPM files you may want to use are:

• MySQL-VERSION.i386.rpm The MySQL server. You will need this unless you only want to connect to another MySQL server running on another machine.
• MySQL-client-VERSION.i386.rpm The standard MySQL client programs. You probably always want to install this package.
• MySQL-bench-VERSION.i386.rpm Tests and benchmarks. Requires Perl and msql-mysql-modules RPMs.
• MySQL-devel-VERSION.i386.rpm Libraries and include files needed if you want to compile other MySQL clients, such as the Perl modules.
• MySQL-VERSION.src.rpm This contains the source code for all of the above packages. It can also be used to try to build RPMs for other architectures (for example, Alpha or SPARC).

To see all files in an RPM package:

shell> rpm -qpl MySQL-VERSION.i386.rpm

To perform a standard minimal installation, run this command:

shell> rpm -i MySQL-VERSION.i386.rpm MySQL-client-VERSION.i386.rpm

To install just the client package:

shell> rpm -i MySQL-client-VERSION.i386.rpm

The RPM places data in `/var/lib/mysql'. The RPM also creates the appropriate entries in `/sbin/rc.d/' to start the server automatically at boot time. (This means that if you have performed a previous installation, you may want to make a copy of your previously-installed MySQL startup file if you made any changes to it, so you don't lose your changes.)

After installing the RPM file(s), go to the binary install section and use the instructions there, starting from the step that creates the MySQL grant tables. See section 4.6 Installing a MySQL binary distribution.

4.6.2 Building client programs

If you compile MySQL clients that you've written yourself or that you obtain from a third party, they must be linked using the -lmysqlclient option on the link command. You may also need to specify a -L option to tell the linker where to find the library. For example, if the library is installed in `/usr/local/mysql/lib', use -L/usr/local/mysql/lib -lmysqlclient on the link command.

For clients that use MySQL header files, you may need to specify a -I option when you compile them (for example, -I/usr/local/mysql/include), so the compiler can find the header files.

4.6.3 System-specific issues

The following sections indicate some of the issues that have been observed to occur on particular systems when installing MySQL from a binary distribution.

4.6.3.1 Linux notes

MySQL needs at least Linux 2.0.

The binary release is linked with -static, which means you not normally need not worry about which version of the system libraries you have. You need not install LinuxThreads, either. A program linked with -static is slightly bigger than a dynamically-linked program but also slightly faster (3-5%). One problem however is that you can't use user definable functions (UDFs) with a statically-linked program. If you are going to write or use UDF functions (this is something only for C or C++ programmers) you must compile MySQL yourself, using dynamic linking.

If you are using a libc-based system (instead of a glibc2 system), you will probably get some problems with hostname resolving and getpwnam() with the binary release. (This is because glibc unfortunately depends on some external libraries to resolve hostnames and getwpent() , even when compiled with -static). In this case you probably get the following error message when you run mysql_install_db:

Sorry, the host 'xxxx' could not be looked up

or the following error when you try to run mysqld with the --user option:

getpwnam: No such file or directory

You can solve this problem one of the following ways:

• Get a MySQL source distribution (an RPM or the tar distribution) and install this instead.
• Execute mysql_install_db --force; This will not execute the resolveip test in mysql_install_db. The downside is that you can't use host names in the grant tables; you must use IP numbers instead (except for localhost). If you are using an old MySQL release that doesn't support --force you have to remove the resolveip test in mysql_install with an editor.
• Start mysqld with su instead of using --user.

The Linux-Intel binary and RPM releases of MySQL are configured for the highest possible speed. We are always trying to use the fastest stable compiler available.

MySQL Perl support requires Perl 5.004_03 or newer.

4.6.3.2 HP-UX notes

The binary distribution of MySQL for HP-UX is distributed as an HP depot file and as a tar file. To use the depot file you must be running at least HP-UX 10.x to have access to HP's software depot tools.

The HP version of MySQL was compiled on an HP 9000/8xx server under HP-UX 10.20, and uses MIT-pthreads. It is known to work well under this configuration. MySQL 3.22.26 and newer can also be built with HP's native thread package.

Other configurations that may work:

• HP 9000/7xx running HP-UX 10.20+
• HP 9000/8xx running HP-UX 10.30

The following configurations almost definitely won't work:

• HP 9000/7xx or 8xx running HP-UX 10.x where x < 2
• HP 9000/7xx or 8xx running HP-UX 9.x

To install the distribution, use one of the commands below, where /path/to/depot is the full pathname of the depot file:

• To install everything, including the server, client and development tools:

  shell> /usr/sbin/swinstall -s /path/to/depot mysql.full
  

• To install only the server:

  shell> /usr/sbin/swinstall -s /path/to/depot mysql.server
  

• To install only the client package:

  shell> /usr/sbin/swinstall -s /path/to/depot mysql.client
  

• To install only the development tools:

  shell> /usr/sbin/swinstall -s /path/to/depot mysql.developer
  

The depot places binaries and libraries in `/opt/mysql' and data in `/var/opt/mysql'. The depot also creates the appropriate entries in `/sbin/init.d' and `/sbin/rc2.d' to start the server automatically at boot time. Obviously, this entails being root to install.

To install the HP-UX tar distribution, you must have a copy of GNU tar.

4.7 Installing a MySQL source distribution

You need the following tools to build and install MySQL from source:

• GNU gunzip to uncompress the distribution.
• A reasonable tar to unpack the distribution. GNU tar is known to work.
• A working ANSI C++ compiler. gcc >= 2.8.1, egcs >= 1.0.2, SGI C++ and SunPro C++ are some of the compilers that are known to work. libg++ is not needed when using gcc. gcc 2.7.x has a bug that makes it impossible to compile some perfectly legal C++ files, such as `sql/sql_base.cc'. If you only have gcc 2.7.x, you must upgrade your gcc to be able to compile MySQL.
• A good make program. GNU make is always recommended and is sometimes required. If you have problems, we recommend trying GNU make 3.75 or newer.

If you run into problems, PLEASE ALWAYS USE mysqlbug when posting questions to mysql@lists.mysql.com. Even if the problem isn't a bug, mysqlbug gathers system information that will help others solve your problem. By not using mysqlbug, you lessen the likelihood of getting a solution to your problem! You will find mysqlbug in the `scripts' directory after you unpack the distribution. See section 2.3 How to report bugs or problems.

4.7.1 Quick installation overview

The basic commands you must execute to install a MySQL source distribution are (from an unpacked tar file):

shell> configure
shell> make
shell> make install
shell> scripts/mysql_install_db
shell> /usr/local/mysql/bin/safe_mysqld &

If you start from a source RPM, then do the following.

shell> rpm --rebuild MySQL-VERSION.src.rpm

This will make a binary RPM that you can install.

You can add new users using the bin/mysql_setpermission script if you install the DBI and Msql-Mysql-modules Perl modules.

Here follows a more detailed description:

To install a source distribution, follow the steps below, then proceed to section 4.15 Post-installation setup and testing, for post-installation initialization and testing.

1. Pick the directory under which you want to unpack the distribution, and move into it.
2. Obtain a distribution file from one of the sites listed in section 4.1 How to get MySQL. MySQL source distributions are provided as compressed tar archives and have names like `mysql-VERSION.tar.gz', where VERSION is a number like 3.22.27.
3. Unpack the distribution into the current directory:

   shell> gunzip < mysql-VERSION.tar.gz | tar xvf -
   

   This command creates a directory named `mysql-VERSION'.
4. Change into the top-level directory of the unpacked distribution:

   shell> cd mysql-VERSION
   

5. Configure the release and compile everything:

   shell> ./configure --prefix=/usr/local/mysql
   shell> make
   

   When you run configure, you might want to specify some options. Run ./configure --help for a list of options. section 4.7.3 Typical configure options, discusses some of the more useful options. If configure fails, and you are going to send mail to lines from `config.log' that you think can help solve the problem. Also include the last couple of lines of output from configure if configure aborts. Post the bug report using the mysqlbug script. See section 2.3 How to report bugs or problems. If the compile fails, see section 4.8 Problems compiling?, for help with a number of common problems.
6. Install everything:

   shell> make install
   

   You might need to run this command as root.
7. Create the MySQL grant tables (necessary only if you haven't installed MySQL before):

   shell> scripts/mysql_install_db
   

   Note that MySQL versions older than 3.22.10 started the MySQL server when you run mysql_install_db. This is no longer true!
8. If you want to install support for the Perl DBI/DBD interface, see section 4.10 Perl installation comments.
9. If you would like MySQL to start automatically when you boot your machine, you can copy support-files/mysql.server to the location where your system has its startup files. More information can be found in the support-files/mysql.server script itself, and in section 4.15.3 Starting and stopping MySQL automatically.

After everything has been installed, you should initialize and test your distribution.

You can start the MySQL server with the following command, where BINDIR is the directory in which safe_mysqld is installed (`/usr/local/bin' by default):

shell> BINDIR/safe_mysqld &

If that command fails immediately with mysqld daemon ended then you can find some information in the file `mysql-data-directory/'hostname'.err'. The likely reason is that you already have another mysqld server running. See section 19.3 Running multiple MySQL servers on the same machine.

See section 4.15 Post-installation setup and testing.

4.7.2 Applying patches

Sometimes patches appear on the mailing list or are placed in the patches area of the MySQL FTP site.

To apply a patch from the mailing list, save the message in which the patch appears in a file, change into the top-level directory of your MySQL source tree and run these commands:

shell> patch -p1 < patch-file-name
shell> rm config.cache
shell> make clean

Patches from the FTP site are distributed as plain text files or as files compressed with gzip files. Apply a plain patch as shown above for mailing list patches. To apply a compressed patch, change into the top-level directory of your MySQL source tree and run these commands:

shell> gunzip < patch-file-name.gz | patch -p1
shell> rm config.cache
shell> make clean

After applying a patch, follow the instructions for a normal source install, beginning with the ./configure step. After running the make install step, restart your MySQL server.

You may need to bring down any currently running server before you run make install. (Use mysqladmin shutdown to do this.) Some systems do not allow you to install a new version of a program if it replaces the version that is currently executing.

4.7.3 Typical configure options

The configure script gives you a great deal of control over how you configure your MySQL distribution. Typically you do this using options on the configure command line. You can also affect configure using certain environment variables. For a list of options supported by configure, run this command:

shell> ./configure --help

Some of the more commonly-used configure options are described below:

• To compile just the MySQL client libraries and client programs and not the server, use the --without-server option:

  shell> ./configure --without-server
  

  If you don't have a C++ compiler, mysql will not compile (it is the one client program that requires C++). In this case, you can remove the code in configure that tests for the C++ compiler and then run ./configure with the --without-server option. The compile step will still try to build mysql, but you can ignore any warnings about `mysql.cc'. (If make stops, try make -k to tell it to continue with the rest of the build even if errors occur.)
• If you don't want your log files and database directories located under `/usr/local/var', use a configure command something like one of these:

  shell> ./configure --prefix=/usr/local/mysql
  shell> ./configure --prefix=/usr/local \
             --localstatedir=/usr/local/mysql/data
  

  The first command changes the installation prefix so that everything is installed under `/usr/local/mysql' rather than the default of `/usr/local'. The second command preserves the default installation prefix, but overrides the default location for database directories (normally `/usr/local/var') and changes it to /usr/local/mysql/data.
• If you are using Unix and you want the MySQL socket located somewhere other than the default location (normally in the directory `/tmp' or `/var/run', use a configure command like this:

  shell> ./configure --with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock
  

  Note that the given file must be an absolute pathname!
• If you want to compile statically-linked programs (e.g., to make a binary distribution, to get more speed or to work around problems with some RedHat distributions), run configure like this:

  shell> ./configure --with-client-ldflags=-all-static \
             --with-mysqld-ldflags=-all-static
  

• If you are using gcc and don't have libg++ or libstdc++ installed, you can tell configure to use gcc as your C++ compiler:

  shell> CC=gcc CXX=gcc ./configure
  

  When you use gcc as your C++ compiler, it will not attempt to link in libg++ or libstdc++. If the build fails and produces errors about your compiler or linker not being able to create the shared library `libmysqlclient.so.#' (`#' is a version number), you can work around this problem by giving the --disable-shared option to configure. In this case, configure will not build a shared libmysqlclient.so.# library.
• You can configure MySQL not to use DEFAULT column values for non-NULL columns (i.e., columns that are not allowed to be NULL). This causes INSERT statements to generate an error unless you explicitly specify values for all columns that require a non-NULL value. To suppress use of default values, run configure like this:

  shell> CXXFLAGS=-DDONT_USE_DEFAULT_FIELDS ./configure
  

• By default, MySQL uses the ISO-8859-1 (Latin1) character set. To change the default set, use the --with-charset option:

  shell> ./configure --with-charset=CHARSET
  

  CHARSET may be one of big5, cp1251, cp1257, czech, danish,dec8, dos, euc_kr, german1, hebrew, hp8, hungarian, koi8_ru, koi8_ukr, latin1, latin2, sjis, swe7, tis620, ujis, usa7, win1251 or win1251ukr. See section 9.1.1 The character set used for data and sorting. Note that if you want to change the character set, you must do a make distclean between configurations! If you want to convert characters between the server and the client, you should take a look at the SET OPTION CHARACTER SET command. See section 7.24 SET OPTION syntax. Warning: If you change character sets after having created any tables, you will have to run isamchk -r -q on every table. Your indexes may be sorted incorrectly otherwise. (This can happen if you install MySQL, create some tables, then reconfigure MySQL to use a different character set and reinstall it.)
• To configure MySQL with debugging code, use the --with-debug option:

  shell> ./configure --with-debug
  

  This causes a safe memory allocator to be included that can find some errors and that provides output about what is happening. See section G.1 Debugging a MySQL server.
• Options that pertain to particular systems can be found in the system-specific sections later in this chapter. See section 4.11 System-specific issues.

4.8 Problems compiling?

All MySQL programs compile cleanly for us with no warnings on Solaris using gcc. On other systems, warnings may occur due to differences in system include files. See section 4.9 MIT-pthreads notes, for warnings that may occur when using MIT-pthreads. For other problems, check the list below.

The solution to many problems involves reconfiguring. If you do need to reconfigure, take note of the following:

• If configure is run after it already has been run, it may use information that was gathered during its previous invocation. This information is stored in `config.cache'. When configure starts up, it looks for that file and reads its contents if it exists, on the assumption that the information is still correct. That assumption is invalid when you reconfigure.
• Each time you run configure, you must run make again to recompile. However, you may want to remove old object files from previous builds first, since they were compiled using different configuration options.

To prevent old configuration information or object files from being used, run these commands before rerunning configure:

shell> rm config.cache
shell> make clean

Alternatively, you can run make distclean.

The list below describes some of the problems compiling MySQL that have been found to occur most often:

• If you get errors when compiling `sql_yacc.cc' such as the ones shown below, you have probably run out of memory or swap space:

  Internal compiler error: program cc1plus got fatal signal 11
    or
  Out of virtual memory
    or
  Virtual memory exhausted
  

  The problem is that gcc requires huge amounts of memory to compile `sql_yacc.cc' with inline functions. Try running configure with the --with-low-memory option:

  shell> ./configure --with-low-memory
  

  This option causes -fno-inline to be added to the compile line if you are using gcc and -O0 if you are using something else. You should try the --with-low-memory option even if you have so much memory and swap space that you think you can't possibly have run out. This problem has been observed to occur even on systems with generous hardware configurations, and the --with-low-memory option usually fixes it.
• By default, configure picks c++ as the compiler name and GNU c++ links with -lg++. If you are using gcc, that behavior can cause problems during configuration such as this:

  configure: error: installation or configuration problem:
  C++ compiler cannot create executables.
  

  You might also observe problems during compilation related to g++, libg++ or libstdc++. One cause of these problems is that you may not have g++, or you may have g++ but not libg++ or libstdc++. Take a look at the `config.log' file. It should contain the exact reason why your c++ compiler didn't work! To work around these problems, you can use gcc as your C++ compiler. Try setting the environment variable CXX to "gcc -O3". For example:

  shell> CXX="gcc -O3" ./configure
  

  This works because gcc compiles C++ sources as well as g++ does, but does not link in libg++ or libstdc++ by default. Another way to fix these problems, of course, is to install g++, libg++ and libstdc++.
• If your compile fails with errors such as any of the following, you must upgrade your version of make to GNU make:

  making all in mit-pthreads
  make: Fatal error in reader: Makefile, line 18:
  Badly formed macro assignment
    or
  make: file `Makefile' line 18: Must be a separator (:
    or
  pthread.h: No such file or directory
  

  Solaris and FreeBSD are known to have troublesome make programs. GNU make version 3.75 is known to work.
• If you want to define flags to be used by your C or C++ compilers, do so by adding the flags to the CFLAGS and CXXFLAGS environment variables. You can also specify the compiler names this way using CC and CXX. For example:

  shell> CC=gcc
  shell> CFLAGS=-O6
  shell> CXX=gcc
  shell> CXXFLAGS=-O6
  shell> export CC CFLAGS CXX CXXFLAGS
  

  See section 4.14 TcX binaries, for a list of flag definitions that have been found to be useful on various systems.
• If you get an error message like this, you need to upgrade your gcc compiler:

  client/libmysql.c:273: parse error before `__attribute__'
  

  gcc 2.8.1 is known to work, but we recommend using egcs 1.0.3a or newer instead.
• If you get errors such as those shown below when compiling mysqld, configure didn't correctly detect the type of the last argument to accept(), getsockname() or getpeername():

  cxx: Error: mysqld.cc, line 645: In this statement, the referenced
       type of the pointer value "&length" is "unsigned long", which
       is not compatible with "int".
  new_sock = accept(sock, (struct sockaddr *)&cAddr, &length);
  

  To fix this, edit the `config.h' file (which is generated by configure). Look for these lines:

  /* Define as the base type of the last arg to accept */
  #define SOCKET_SIZE_TYPE XXX
  

  Change XXX to size_t or int, depending on your operating system. (Note that you will have to do this each time you run configure, since configure regenerates `config.h'.)
• The `sql_yacc.cc' file is generated from `sql_yacc.yy'. Normally the build process doesn't need to create `sql_yacc.cc', because MySQL comes with an already-generated copy. However, if you do need to recreate it, you might encounter this error:

  "sql_yacc.yy", line xxx fatal: default action causes potential...
  

  This is a sign that your version of yacc is deficient. You probably need to install bison (the GNU version of yacc) and use that instead.
• If you need to debug mysqld or a MySQL client, run configure with the --with-debug option, then recompile and link your clients with the new client library. See section G.2 Debugging a MySQL client.

4.9 MIT-pthreads notes

This section describes some of the issues involved in using MIT-pthreads.

Note that on Linux you should NOT use MIT-pthreads but install LinuxThreads! See section 4.11.5 Linux notes (all Linux versions).

If your system does not provide native thread support, you will need to build MySQL using the MIT-pthreads package. This includes most FreeBSD systems, SunOS 4.x, Solaris 2.4 and earlier, and some others. See section 4.2 Operating systems supported by MySQL.

• On most systems, you can force MIT-pthreads to be used by running configure with the --with-mit-threads option:

  shell> ./configure --with-mit-threads
  

  Building in a non-source directory is not supported when using MIT-pthreads, because we want to minimize our changes to this code.
• MIT-pthreads doesn't support the AF_UNIX protocol used to implement Unix sockets. This means that if you compile using MIT-pthreads, all connections must be made using TCP/IP (which is a little slower). If you find after building MySQL that you cannot connect to the local server, it may be that your client is attempting to connect to localhost using a Unix socket as the default. Try making a TCP/IP connection with mysql by using a host option (-h or --host) to specify the local host name explicitly.
• The checks that determine whether or not to use MIT-pthreads occur only during the part of the configuration process that deals with the server code. If you have configured the distribution using --without-server to build only the client code, clients will not know whether or not MIT-pthreads is being used and will use Unix socket connections by default. Since Unix sockets do not work under MIT-pthreads, this means you will need to use -h or --host when you run client programs.
• When MySQL is compiled using MIT-pthreads, system locking is disabled by default for performance reasons. You can tell the server to use system locking with the --use-locking option.
• Sometimes the pthread bind() command fails to bind to a socket without any error message (at least on Solaris). The result is that all connections to the server fail. For example:

  shell> mysqladmin version
  mysqladmin: connect to server at " failed;
  error: 'Can't connect to mysql server on localhost (146)'
  

  The solution to this is to kill the mysqld server and restart it. This has only happened to us when we have forced the server down and done a restart immediately.
• With MIT-pthreads, the sleep() system call isn't interruptible with SIGINT (break). This is only noticeable when you run mysqladmin --sleep. You must wait for the sleep() call to terminate before the interrupt is served and the process stops.
• When linking you may receive warning messages like these (at least on Solaris); they can be ignored:

  ld: warning: symbol `_iob' has differing sizes:
      (file /my/local/pthreads/lib/libpthread.a(findfp.o) value=0x4;
  file /usr/lib/libc.so value=0x140);
      /my/local/pthreads/lib/libpthread.a(findfp.o) definition taken
  ld: warning: symbol `__iob' has differing sizes:
      (file /my/local/pthreads/lib/libpthread.a(findfp.o) value=0x4;
  file /usr/lib/libc.so value=0x140);
      /my/local/pthreads/lib/libpthread.a(findfp.o) definition taken
  

• Some other warnings also can be ignored:

  implicit declaration of function `int strtoll(...)'
  implicit declaration of function `int strtoul(...)'
  

• We haven't gotten readline to work with MIT-pthreads. (This isn't needed, but may be interesting for someone.)

4.10 Perl installation comments

4.10.1 Installing Perl on Unix

Perl support for MySQL is provided by means of the DBI/DBD client interface. See section 20.5 MySQL Perl API. The Perl DBD/DBI client code requires Perl 5.004 or later. The interface will not work if you have an older version of Perl.

MySQL Perl support also requires that you've installed MySQL client programming support. If you installed MySQL from RPM files, client programs are in the client RPM, but client programming support is in the developer RPM. Make sure you've installed the latter RPM.

As of release 3.22.8, Perl support is distributed separately from the main MySQL distribution. If you want to install Perl support, the files you will need can be obtained from http://www.mysql.com/Contrib.

The Perl distributions are provided as compressed tar archives and have names like `MODULE-VERSION.tar.gz', where MODULE is the module name and VERSION is the version number. You should get the Data-Dumper, DBI, and Msql-Mysql-modules distributions and install them in that order. The installation procedure is shown below. The example shown is for the Data-Dumper module, but the procedure is the same for all three distributions.

1. Unpack the distribution into the current directory:

   shell> gunzip < Data-Dumper-VERSION.tar.gz | tar xvf -
   

   This command creates a directory named `Data-Dumper-VERSION'.
2. Change into the top-level directory of the unpacked distribution:

   shell> cd Data-Dumper-VERSION
   

3. Build the distribution and compile everything:

   shell> perl Makefile.PL
   shell> make
   shell> make test
   shell> make install
   

The make test command is important, because it verifies that the module is working. Note that when you run that command during the Msql-Mysql-modules installation to exercise the interface code, the MySQL server must be running or the test will fail.

It is a good idea to rebuild and reinstall the Msql-Mysql-modules distribution whenever you install a new release of MySQL, particularly if you notice symptoms such as all your DBI scripts dumping core after you upgrade MySQL.

If you don't have the right to install Perl modules in the system directory or if you to install local Perl modules, the following reference may help you:

http://www.iserver.com/support/contrib/perl5/modules.html

Look under the heading Installing New Modules that Require Locally Installed Modules.

4.10.2 Installing ActiveState Perl on Win32

To install the MySQL DBD module with ActiveState Perl on Win32, you should do the following:

• Open a DOS shell.
• If required, set the HTTP_proxy variable. For example, you might try: set HTTP_proxy=my.proxy.com:3128
• Start the PPM program: C:\perl\bin\ppm.pl
• If you have not already done so, install DBI: install DBI
• If this succeeds, install DBD::mysql: http://www.mysql.com/Contrib/ppd/DBD-mysql.ppd

If you can't get the above to work, you should instead install the MyODBC driver and connect to MySQL server through ODBC.

use DBI;
$dbh= DBI->connect("DBI:ODBC:$dsn","$user","$password") || 
  die "Got error $DBI::errstr when connecting to $dsn\n";

4.10.3 Installing the MySQL Perl distribution on Win32

The MySQL Perl distribution contains DBI, DBD:MySQL and DBD:ODBC.

• Get the Perl distribution for Win32 from http://www.mysql.com/download.html.
• Unzip the distribution in C: so that you get a `C:\PERL' directory.
• Add the directory `C:\PERL\BIN' to your path.
• Add the directory `C:\PERL\BIN\MSWin32-x86-thread' or `C:\PERL\BIN\MSWin32-x86' to your path.
• Test that perl works by executing perl -v in a DOS shell.

4.10.4 Problems using the Perl DBI/DBD interface

If Perl reports that it can't find the ../mysql/mysql.so module, then the problem is probably that Perl can't locate the shared library `libmysqlclient.so'.

You can fix this by any of the following methods:

• Compile the Msql-Mysql-modules distribution with perl Makefile.PL -static rather than perl Makefile.PL
• Copy libmysqlclient.so to the directory where your other shared libraries are located (probably `/usr/lib' or `/lib').
• On Linux you can add the pathname of the directory where libmysqlclient.so is located to the `/etc/ld.so.conf' file.
• Add the pathname of the directory where libmysqlclient.so is located to the LD_RUN_PATH environment variable.

If you get the following errors from DBD-mysql, you are probably using gcc (or using an old binary compiled with gcc):

/usr/bin/perl: can't resolve symbol '__moddi3'
/usr/bin/perl: can't resolve symbol '__divdi3'

Add -L/usr/lib/gcc-lib/... -lgcc to the link command when the `mysql.so' library gets built (check the output from make for `mysql.so' when you compile the Perl client). The -L option should specify the pathname of the directory where `libgcc.a' is located on your system.

Another cause of this problem may be that Perl and MySQL aren't both compiled with gcc. In this case, you can solve the mismatch by compiling both with gcc.

If you want to use the Perl module on a system that doesn't support dynamic linking (like SCO) you can generate a static version of Perl that includes DBI and DBD-mysql. The way this works is that you generate a version of Perl with the DBI code linked in and install it on top of your current Perl. Then you use that to build a version of Perl that additionally has the DBD code linked in, and install that.

On SCO, you must have the following environment variables set:

shell> LD_LIBRARY_PATH=/lib:/usr/lib:/usr/local/lib:/usr/progressive/lib
or
shell> LD_LIBRARY_PATH=/usr/lib:/lib:/usr/local/lib:/usr/ccs/lib:/usr/progressive/lib:/usr/skunk/lib
shell> LIBPATH=/usr/lib:/lib:/usr/local/lib:/usr/ccs/lib:/usr/progressive/lib:/usr/skunk/lib
shell> MANPATH=scohelp:/usr/man:/usr/local1/man:/usr/local/man:/usr/skunk/man:

First, create a Perl that includes a statically-linked DBI by running these commands in the directory where your DBI distribution is located:

shell> perl Makefile.PL LINKTYPE=static
shell> make
shell> make install
shell> make perl

Then you must install the new Perl. The output of make perl will indicate the exact make command you will need to execute to perform the installation. On SCO, this is make -f Makefile.aperl inst_perl MAP_TARGET=perl.

Next, use the just-created Perl to create another Perl that also includes a statically-linked DBD::mysql by running these commands in the directory where your Msql-Mysql-modules distribution is located:

shell> perl Makefile.PL LINKTYPE=static
shell> make
shell> make install
shell> make perl

Finally, you should install this new Perl. Again, the output of make perl indicates the command to use.

4.11 System-specific issues

The following sections indicate some of the issues that have been observed to occur on particular systems when installing MySQL from a source distribution.

4.11.1 Solaris notes

On Solaris, you may run into trouble even before you get the MySQL distribution unpacked! Solaris tar can't handle long file names, so you may see an error like this when you unpack MySQL:

x mysql-3.22.12-beta/bench/Results/ATIS-mysql_odbc-NT_4.0-cmp-db2,informix,ms-sql,mysql,oracle,solid,sybase, 0 bytes, 0 tape blocks
tar: directory checksum error

In this case, you must use GNU tar (gtar) to unpack the distribution. You can find a precompiled copy for Solaris at http://www.mysql.com/Downloads/.

Sun native threads work only on Solaris 2.5 and higher. For 2.4 and earlier versions, MySQL will automatically use MIT-pthreads. See section 4.9 MIT-pthreads notes.

If you get the following error from configure:

checking for restartable system calls... configure: error can not run test
programs while cross compiling

This means that you have something wrong with your compiler installation! In this case you should upgrade your compiler to a newer version. You may also be able to solve this problem by inserting the following row into the config.cache file:

ac_cv_sys_restartable_syscalls=${ac_cv_sys_restartable_syscalls='no'}

If you are using Solaris on a SPARC, the recommended compiler is egcs 1.1.2 or newer. You can find this at http://egcs.cygnus.com/. Note that egs 1.1.1 and gcc 2.8.1 don't work reliably on SPARC!

The recommended configure line when using egcs 1.1.2 is:

shell> CC=gcc CFLAGS="-O6" \
       CXX=gcc CXXFLAGS="-O6 -felide-constructors -fno-exceptions -fno-rtti" \
       ./configure --prefix=/usr/local/mysql --with-low-memory

If you have the Sun Workshop 4.2 compiler, you can run configure like this:

CC=cc CFLAGS="-xstrconst -Xa -xO4 -native -mt" CXX=CC CXXFLAGS="-xO4 -native -noex -mt" ./configure --prefix=/usr/local/mysql

shell> CC=cc CFLAGS="-Xa -fast -xO4 -native -xstrconst -mt" \
       CXX=CC CXXFLAGS="-noex -XO4 -mt" \
       ./configure

You may also have to edit the configure script to change this line:

#if !defined(__STDC__) || __STDC__ != 1

to this:

#if !defined(__STDC__)

If you turn on __STDC__ with the -Xc option, the Sun compiler can't compile with the Solaris `pthread.h' header file. This is a Sun bug (broken compiler or broken include file).

If mysqld issues the error message shown below when you run it, you have tried to compile MySQL with the Sun compiler without enabling the multi-thread option (-mt):

libc internal error: _rmutex_unlock: rmutex not held

Add -mt to CFLAGS and CXXFLAGS and try again.

If you get the following error when compiling MySQL with gcc, it means that your gcc is not configured for your version of Solaris!

shell> gcc -O3 -g -O2 -DDBUG_OFF  -o thr_alarm ...
./thr_alarm.c: In function `signal_hand':
./thr_alarm.c:556: too many arguments to function `sigwait'

The proper thing to do in this case is to get the newest version of egcs and compile it with your current gcc compiler! At least for Solaris 2.5, almost all binary versions of gcc have old, unusable include files that will break all programs that use threads (and possibly other programs)!

Solaris doesn't provide static versions of all system libraries (libpthreads and libdl), so you can't compile MySQL with --static. If you try to do so, you will get the error:

ld: fatal: library -ldl: not found

If too many processes try to connect very rapidly to mysqld, you will see this error in the MySQL log:

Error in accept: Protocol error

You might try starting the server with the --set-variable back_log=50 option as a workaround for this.

If you are linking your own MySQL client, you might get the following error when you try to execute it:

ld.so.1: ./my: fatal: libmysqlclient.so.#: open failed: No such file or directory

The problem can be avoided by one of the following methods:

• Link the client with the following flag (instead of -Lpath): -Wl,r/full-path-to-libmysqlclient.so.
• Copy libmysqclient.so to `/usr/lib'.
• Add the pathname of the directory where libmysqlclient.so is located to the LD_RUN_PATH environment variable before running your client.

4.11.2 Solaris 2.7 notes

You can normally use a Solaris 2.6 binary on Solaris 2.7. Most of the Solaris 2.6 issues also apply for Solaris 2.7.

Note that MySQL 3.23.4 and above should be able to autodetect Solaris 2.7 and enable workarounds for the following problems!

Solaris 2.7 has some bugs in the include files. You may see the following error when you use gcc:

/usr/include/widec.h:42: warning: `getwc' redefined
/usr/include/wchar.h:326: warning: this is the location of the previous
definition

If this occurs, you can do the following to fix the problem:

Copy /usr/include/widec.h to .../lib/gcc-lib/os/gcc-version/include and change line 41 from:

#if     !defined(lint) && !defined(__lint)

to

#if     !defined(lint) && !defined(__lint) && !defined(getwc)

Alternatively, you can edit `/usr/include/widec.h' directly. Either way, after you make the fix, you should remove `config.cache' and run configure again!

If you get errors like this when you run make, it's because configure didn't detect the `curses.h' file (probably because of the error in /usr/include/widec.h:

In file included from mysql.cc:50:
/usr/include/term.h:1060: syntax error before `,'
/usr/include/term.h:1081: syntax error before `;'

The solution to this is to do one of the following steps:

• Edit `/usr/include/widec.h' as indicted above and rerun configure
• Remove the #define HAVE_TERM line from `config.h' file and run make again.
• Configure with CFLAGS=-DHAVE_CURSES CXXFLAGS=-DHAVE_CURSES ./configure

4.11.3 Solaris x86 notes

If you are using gcc or egcs on Solaris x86 and you experience problems with core dumps under load, you should use the following configure command:

shell> CC=gcc CFLAGS="-O6 -fomit-frame-pointer" \
       CXX=gcc \
       CXXFLAGS="-O6 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" \
       ./configure --prefix=/usr/local/mysql

This will avoid problems with the libstdc++ library and with C++ exceptions.

If this doesn't help, you should compile a debug version and run it with a trace file or under gdb. See section G.1 Debugging a MySQL server.

4.11.4 SunOS 4 notes

On SunOS 4, MIT-pthreads is needed to compile MySQL, which in turn means you will need GNU make.

Some SunOS 4 systems have problems with dynamic libraries and libtool. You can use the following configure line to avoid this problem:

shell> ./configure --disable-shared --with-mysqld-ldflags=-all-static

When compiling readline, you may get warnings about duplicate defines. These may be ignored.

When compiling mysqld, there will be some implicit declaration of function warnings. These may be ignored.

4.11.5 Linux notes (all Linux versions)

MySQL uses LinuxThreads on Linux. If you are using an old Linux version that doesn't have glibc2, you must install LinuxThreads before trying to compile MySQL. http://www.mysql.com/Downloads/Linux

If you can't start mysqld or if mysql_install_db doesn't work, please continue reading! This only happens on Linux system with problems in the LinuxThreads or libc/glibc libraries. There are a lot of simple workarounds to get MySQL to work! The simplest is to use the binary version of MySQL (not the RPM) for Linux x86. One nice aspect of this version is that it's probably 10% faster than any version you would compile yourself! See section 10.3 How compiling and linking affects the speed of MySQL.

One known problem with the binary distribution is that with older Linux systems that use libc (like RedHat 4.x or Slackware), you will get some non-fatal problems with hostname resolution See section 4.6.3.1 Linux notes.

isamchk hangs with libc.so.5.3.12. Upgrading to the newest libc fixes this problem.

When using LinuxThreads you will see a minimum of three processes running. These are in fact threads. There will be one thread for the LinuxThreads manager, one thread to handle connections, and one thread to handle alarms and signals.

If you see a dead mysqld daemon process with ps, this usually means that you have found a bug in MySQL or you have got a corrupted table. See section 18.1 What to do if MySQL keeps crashing.

If you are using LinuxThreads and mysqladmin shutdown doesn't work, you must upgrade to LinuxThreads 0.7.1 or newer.

If you are using RedHat, you might get errors like this:

/usr/bin/perl is needed...
/usr/sh is needed...
/usr/sh is needed...

If so, you should upgrade your version of rpm to `rpm-2.4.11-1.i386.rpm' and `rpm-devel-2.4.11-1.i386.rpm' (or later).

You can get the upgrades of libraries to RedHat 4.2 from ftp://ftp.redhat.com/updates/4.2/i386. Or http://www.sunsite.unc.edu/pub/Linux/distributions/redhat/code/rpm/ for other distributions.

If you are linking your own MySQL client and get the error:

ld.so.1: ./my: fatal: libmysqlclient.so.4: open failed: No such file or directory

when executing them, the problem can be avoided by one of the following methods:

• Link the client with the following flag (instead of -Lpath): -Wl,r/path-libmysqlclient.so.
• Copy libmysqclient.so to `/usr/lib'.
• Add the pathname of the directory where libmysqlclient.so is located to the LD_RUN_PATH environment variable before running your client.

If you are using the Fujitsu compiler (fcc / FCC) you will have some problems compiling MySQL because the Linux header files are very gcc oriented.

The following configure line should work with fcc/FCC:

CC=fcc CFLAGS="-O -K fast -K lib -K omitfp -Kpreex -D_GNU_SOURCE -DCONST=const -DNO_STRTOLL_PROTO" CXX=FCC CXXFLAGS="-O -K fast -K lib  -K omitfp -K preex --no_exceptions --no_rtti -D_GNU_SOURCE -DCONST=const -Dalloca=__builtin_alloca -DNO_STRTOLL_PROTO '-D_EXTERN_INLINE=static __inline'" ./configure --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static --disable-shared --with-low-memory

4.11.5.1 Linux-x86 notes

MySQL requires libc version 5.4.12 or newer. It's known to work with libc 5.4.46. glibc version 2.0.6 and later should also work. There have been some problems with the glibc RPMs from RedHat so if you have problems, check whether or not there are any updates! The glibc 2.0.7-19 and 2.0.7-29 RPMs are known to work.

On some older Linux distributions, configure may produce an error like this:

Syntax error in sched.h. Change _P to __P in the /usr/include/sched.h file.
See the Installation chapter in the Reference Manual.

Just do what the error message says and add an extra underscore to the _P macro that has only one underscore, then try again.

You may get some warnings when compiling; those shown below can be ignored:

mysqld.cc -o objs-thread/mysqld.o
mysqld.cc: In function `void init_signals()':
mysqld.cc:315: warning: assignment of negative value `-1' to `long unsigned int'
mysqld.cc: In function `void * signal_hand(void *)':
mysqld.cc:346: warning: assignment of negative value `-1' to `long unsigned int'

In Debian GNU/Linux, if you want MySQL to start automatically when the system boots, do the following:

shell> cp support-files/mysql.server /etc/init.d/mysql.server
shell> /usr/sbin/update-rc.d mysql.server defaults 99

mysql.server can be found in the `share/mysql' directory under the MySQL installation directory, or in the `support-files' directory of the MySQL source tree.

If mysqld always core dumps when it starts up, the problem may be that you have an old `/lib/libc.a'. Try renaming it, then remove `sql/mysqld' and do a new make install and try again. This problem has been reported on some Slackware installations. RedHat 5.0 has also a similar problem with some new glibc versions. See section 4.11.5.2 RedHat 5.0 notes.

If you get the following error when linking mysqld, it means that your `libg++.a' is not installed correctly:

/usr/lib/libc.a(putc.o): In function `_IO_putc':
putc.o(.text+0x0): multiple definition of `_IO_putc'

You can avoid using `libg++.a' by running configure like this:

shell> CXX=gcc ./configure

4.11.5.2 RedHat 5.0 notes

If you have any problems with MySQL on RedHat, you should start by upgrading glibc to the newest possible version!

If you install all the official RedHat patches (including glibc-2.0.7-19 and glibc-devel-2.0.7-19), both the binary and source distributions of MySQL should work without any trouble!

The updates are needed since there is a bug in glibc 2.0.5 in how pthread_key_create variables are freed. With glibc 2.0.5, you must use a statically-linked MySQL binary distribution. If you want to compile from source, you must install the corrected version of LinuxThreads from http://www.mysql.com/Downloads/Linux or upgrade your glibc.

If you have an incorrect version of glibc or LinuxThreads, the symptom is that mysqld crashes after each connection. For example, mysqladmin version will crash mysqld when it finishes!

Another symptom of incorrect libraries is that mysqld crashes at once when it starts. On some Linux systems, this can be fixed by configuring like this:

shell> ./configure --with-mysqld-ldflags=-all-static

On Redhat 5.0, the easy way out is to install the glibc 2.0.7-19 RPM and run configure without the --with-mysqld-ldflags=-all-static option.

For the source distribution of glibc 2.0.7, a patch that is easy to apply and is tested with MySQL may be found at:

http://www.mysql.com/Download/Linux/glibc-2.0.7-total-patch.tar.gz

If you experience crashes like these when you build MySQL, you can always download the newest binary version of MySQL. This is statically-linked to avoid library conflicts and should work on all Linux systems!

MySQL comes with an internal debugger that can generate trace files with a lot of information that can be used to find and solve a wide range of different problems. See section G.1 Debugging a MySQL server.

4.11.5.3 RedHat 5.1 notes

The glibc of RedHat 5.1 (glibc 2.0.7-13) has a memory leak, so to get a stable MySQL version, you must upgrade glibc to 2.0.7-19, downgrade glibc or use a binary version of mysqld. If you don't do this, you will encounter memory problems (out of memory, etc., etc.). The most common error in this case is:

Can't create a new thread (errno 11). If you are not out of available
memory, you can consult the manual for any possible OS dependent bug

After you have upgraded to glibc 2.0.7-19, you can configure MySQL with dynamic linking (the default), but you cannot run configure with the --with-mysqld-ldflags=-all-static option until you have installed glibc 2.0.7-19 from source!

You can check which version of glibc you have with rpm -q glibc.

4.11.5.4 Linux-SPARC notes

In some implementations, readdir_r() is broken. The symptom is that SHOW DATABASES always returns an empty set. This can be fixed by removing HAVE_READDIR_R from `config.h' after configuring and before compiling.

Some problems will require patching your Linux installation. The patch can be found at http://www.mysql.com/patches/Linux-sparc-2.0.30.diff. This patch is against the Linux distribution `sparclinux-2.0.30.tar.gz' that is available at vger.rutgers.edu (a version of Linux that was never merged with the official 2.0.30). You must also install LinuxThreads 0.6 or newer.

Thanks to jacques@solucorp.qc.ca for this information.

4.11.5.5 Linux-Alpha notes

The big problem on Linux-Alpha is that there are still some problems with threads in glibc on this platform. You should start by getting the newest glibc version you can find.

Note that before you run any programs that use threads (like mysqld, thr_alarm or thr_lock), you should raise the shared memory limit (with ulimit). The MySQL benchmarks are known to fail if you forget to do this!

Configure MySQL with the following command:

shell> CC=gcc CCFLAGS="-Dalpha_linux_port" \
       CXX=gcc CXXFLAGS="-O3 -Dalpha_linux_port -felide-constructors -fno-exceptions -fno-rtti" \
       ./configure --prefix=/usr/local/mysql

Try to compile mysys/thr_lock and mysys/thr_alarm. Test that these programs work! (Invoke each one with no arguments. Each should end with test_succeeded if everything was okay.)

After installing MySQL, uncomment the ulimit command in safe_mysqld and add options to increase shared memory.

Note that Linux-Alpha is still an alpha-quality platform for MySQL. With the newest glibc, you have a very good chance of it working.

If you have problems with signals (MySQL dies unexpectedly under high load) you may have found an OS bug with threads and signals. In this case you can tell MySQL not to use signals by configuring with:

shell> CFLAGS=-DDONT_USE_THR_ALARM \
       CXXFLAGS=-DDONT_USE_THR_ALARM \
       ./configure ...

This doesn't affect the performance of MySQL, but has the side effect that you can't kill clients that are "sleeping" on a connection with mysqladmin kill or mysqladmin shutdown. Instead, the client will die when it issues its next command.

4.11.5.6 MkLinux notes

MySQL should work on MkLinux with the newest glibc package (tested with glibc 2.0.7).

4.11.5.7 Qube2 Linux notes

To get MySQL to work on Qube2, (Linux Mips), you need the newest glibc libraries (glibc-2.0.7-29C2 is known to work). You must also use the egcs C++ compiler (egcs-1.0.2-9 or newer).

4.11.6 Alpha-DEC-Unix notes

When compiling threaded programs under Digital UNIX, the documentation recommends using the -pthread option for cc and cxx and the libraries -lmach -lexc (in addition to -lpthread). You should run configure something like this:

shell> CC="cc -pthread" CXX="cxx -pthread -O" \
       ./configure --with-named-thread-libs="-lpthread -lmach -lexc -lc"

When compiling mysqld, you may see a couple of warnings like this:

mysqld.cc: In function void handle_connections()':
mysqld.cc:626: passing long unsigned int *' as argument 3 of
accept(int,sockadddr *, int *)'

You can safely ignore these warnings. They occur because configure can detect only errors, not warnings.

If you start the server directly from the command line, you may have problems with it dying when you log out. (When you log out, your outstanding processes receive a SIGHUP signal.) If so, try starting the server like this:

shell> nohup mysqld [options] &

nohup causes the command following it to ignore any SIGHUP signal sent from the terminal. Alternatively, start the server by running safe_mysqld, which invokes mysqld using nohup for you.

4.11.7 Alpha-DEC-OSF1 notes

If you have problems compiling and have DEC CC and gcc installed, try running configure like this:

shell> CC=cc CFLAGS=-O CXX=gcc CXXFLAGS=-O3 \
       ./configure --prefix=/usr/local/mysql

If you get problems with the `c_asm.h' file, you can create and use a 'dummy' `c_asm.h' file with:

shell> touch include/c_asm.h
shell> CC=gcc CFLAGS=-I./include \
       CXX=gcc CXXFLAGS=-O3 \
       ./configure --prefix=/usr/local/mysql

On OSF1 V4.0D and compiler "DEC C V5.6-071 on Digital UNIX V4.0 (Rev. 878)" the compiler had some strange behavior (undefined asm symbols). /bin/ld also appears to be broken (problems with _exit undefined errors occuring while linking mysqld). On this system, we have managed to compile MySQL with the following configure line, after replacing /bin/ld with the version from OSF 4.0C:

shell> CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql

In some versions of OSF1, the alloca() function is broken. Fix this by removing the line in `config.h' that defines 'HAVE_ALLOCA'.

The alloca() function also may have an incorrect prototype in /usr/include/alloca.h. This warning resulting from this can be ignored.

configure will use the following thread libraries automatically: --with-named-thread-libs="-lpthread -lmach -lexc -lc".

When using gcc, you can also try running configure like this:

shell> CFLAGS=-D_PTHREAD_USE_D4 CXX=gcc CXXFLAGS=-O3 ./configure ....

If you have problems with signals (MySQL dies unexpectedly under high load) you may have found an OS bug with threads and signals. In this case you can tell MySQL not to use signals by configuring with:

shell> CFLAGS=-DDONT_USE_THR_ALARM \
       CXXFLAGS=-DDONT_USE_THR_ALARM \
       ./configure ...

This doesn't affect the performance of MySQL, but has the side effect that you can't kill clients that are "sleeping" on a connection with mysqladmin kill or mysqladmin shutdown. Instead, the client will die when it issues its next command.

4.11.8 SGI-Irix notes

You may have to undefine some things in `config.h' after running configure and before compiling.

In some Irix implementations, the alloca() function is broken. If the mysqld server dies on some SELECT statements, remove the lines from `config.h' that define HAVE_ALLOC and HAVE_ALLOCA_H. If mysqladmin create doesn't work, remove the line from `config.h' that defines HAVE_READDIR_R. You may have to remove the HAVE_TERM_H line as well.

SGI recommends that you install all of the patches on this page as a set: http://support.sgi.com/surfzone/patches/patchset/6.2_indigo.rps.html

At the very minimum, you should install the latest kernel rollup, the latest rld rollup, and the latest libc rollup.

You definately need all the POSIX patches on this page, for pthreads support:

http://support.sgi.com/surfzone/patches/patchset/6.2_posix.rps.html

If you get the something like the following error when compiling `mysql.cc':

"/usr/include/curses.h", line 82: error(1084): invalid combination of type

Then type the following in the top-level directory of your MySQL source tree:

shell> extra/replace bool curses_bool < /usr/include/curses.h > include/curses.h
shell> make

There have also been reports of scheduling problems. If only one thread is running, things go slow. Avoid this by starting another client. This may lead to a 2-to-10-fold increase in execution speed thereafter for the other thread. This is a poorly-understood problem with Irix threads; you may have to improvise to find solutions until this can be fixed.

If you are compiling with gcc, you can use the following configure command:

shell> CC=gcc CXX=gcc CXXFLAGS=-O3 \
       ./configure --prefix=/usr/local/mysql --with-thread-safe-client --with-named-thread-libs=-lpthread

4.11.9 FreeBSD notes

The easiest and therefor the preferred way to install is to use the mysql-server and mysql-client ports available on http://www.freebsd.org

Using these gives you:

• A working MySQL with all optimizations known to work on your version of FreeBSD enabled.
• Automatic configuration and build.
• Startup scripts installed in /usr/local/etc/rc.d
• Ability to see which files that are installed with pkg_info -L. And to remove them all with pkg_delete if you no longer want MySQL on that machine.

It is recomended to use MIT-pthreads on FreeBSD 2.x and native threads on versions 3 and up. It is possible to run with with native threads on some late 2.2.x versions but you may encounter problems shutting down mysqld.

Be sure to have your name resolver setup correct. Otherwise you may experience resolver delays or failures when connecting to mysqld.

Make sure that the localhost entry in the `/etc/hosts' file is correct (otherwise you will have problems connecting to the database). The `/etc/hosts' file should start with a line:

127.0.0.1       localhost localhost.your.domain

If you notice that configure will use MIT-pthreads, you should read the MIT-pthreads notes. See section 4.9 MIT-pthreads notes.

If you get an error from make install that it can't find `/usr/include/pthreads', configure didn't detect that you need MIT-pthreads. This is fixed by executing these commands:

shell> rm config.cache
shell> ./configure --with-mit-threads

The behavior of FreeBSD make is slightly different from that of GNU make. If you have make-related problems, you should install GNU make.

FreeBSD is also known to have a very low default file handle limit. See section 18.11 File not found. Uncomment the ulimit -n section in safe_mysqld or raise the limits for the mysqld user in /etc/login.conf (and rebuild it witg cap_mkdb /etc/login.conf) also be sure you set the appropriate Class for this user in the password file if you are not using the default (use: chpass mysqld-user-name)

If you have a problem with SELECT NOW() returning values in GMT and not your local time, you have to set the TZ environment variable to your current timezone. This should be done for the environment in which the server runs, for example, in safe_mysqld or mysql.server.

To get a secure and stable system you should only use FreeBSD kernels that are marked -STABLE

4.11.10 NetBSD notes

To compile on NetBSD you need GNU make. Otherwise the compile will crash when make tries to run lint on C++ files.

4.11.11 BSD/OS notes

4.11.11.1 BSD/OS 2.x notes

If you get the following error when compiling MySQL, your ulimit value for virtual memory is too low:

item_func.h: In method `Item_func_ge::Item_func_ge(const Item_func_ge &)':
item_func.h:28: virtual memory exhausted
make[2]: *** [item_func.o] Error 1

Try using ulimit -v 80000 and run make again. If this doesn't work and you are using bash, try switching to csh or sh; some BSDI users have reported problems with bash and ulimit.

If you are using gcc, you may also use have to use the --with-low-memory flag for configure to be able to compile `sql_yacc.cc'.

If you have a problem with SELECT NOW() returning values in GMT and not your local time, you have to set the TZ environment variable to your current timezone. This should be done for the environment in which the ser