|
|
|
1 General Information about MySQLThis is the MySQL reference manual; it documents MySQL version 3.22.29. 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.5 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 ('sql-bench' in the distribution). 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 project, see section C Contributors to MySQL. IMPORTANT: Send bug (error) reports, questions and comments to the mailing list at
For source distributions, the If you have any suggestions concerning additions or corrections to this manual, please send them to the manual team at (docs@mysql.com). 1.1 What is MySQL?
MySQL is a true multi-user, multi-threaded SQL database
server. SQL (Structured Query Language) is the most popular and
standardized database language in the world. MySQL is a
client/server implementation that consists of a server daemon
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 needed a SQL server that could handle very large databases an order of magnitude faster than what any database vendor could offer to us on inexpensive hardware. 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 manualThis 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 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 manualThis manual uses certain typographical conventions:
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> type a shell command here mysql> type a mysql command here
Shell commands are shown using Bourne shell syntax. If you are using a
shell> VARNAME=value some_command
For 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
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 mysql> SELECT count(*) FROM tbl_name;
On the other hand, in a discussion of the 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 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 Books about MySQLWhile this manual is still the right place for up to date techical information, its primary goal is to contain everything there is to know about MySQL. And it is sometimes nice to have a bound book to read in bed or while you travel. Here are a list of books about MySQL (in English).
Foreword by Michael "Monty" Widenius, MySQL Moderator In MySQL, Paul DuBois provides you with a comprehensive guide to one of the most popular relational database systems. Paul has contributed to the online documentation for MySQL, and is an active member of the MySQL community. The principal MySQL developer, Monty Widenius, and a network of his fellow developers reviewed the manuscript, providing Paul with the kind of insight no one else could supply. Instead of merely giving you a general overview of MySQL, Paul teaches you how to make the most of its capabilities. Through two sample database applications that run throughout the book, he gives you solutions to problems you're sure to face. He helps you integrate MySQL efficiently with third-party tools, such as PHP and Perl, enabling you to generate dynamic Web pages through database queries. He teaches you to write programs that access MySQL databases, and also provides a comprehensive set of references to column types, operators, functions, SQL syntax, MySQL programming, C API, Perl DBI, and PHP API. MySQL simply gives you the kind of information you won't find anywhere else. If you use MySQL, this book provides you with:
This book teaches you how to use MySQL and mSQL, two popular and robust database products that support key subsets of SQL on both Linux and UNIX systems. Anyone who knows basic C, Java, Perl, or Python can write a program to interact with a database, either as a stand-alone application or through a Web page. This book takes you through the whole process, from installation and configuration to programming interfaces and basic administration. Includes ample tutorial material. 1.5 The main features of MySQLThe following list describes some of the important characteristics of MySQL:
1.6 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:
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.7 Year 2000 complianceMySQL itself has no problems with Year 2000 (Y2K) compliance:
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 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
The Even though MySQL is Y2K-compliant, it is your responsibility to provide unambiguous input. See section 7.3.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.8 General SQL information and tutorialsThis 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 by MySQL users: 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.9 Useful MySQL-related links1.9.1 Tutorials
1.9.2 Porting MySQL / Using MySQL on different systems
1.9.3 Perl related links1.9.4 MySQL discussion forums1.9.5 Commercial applications that support MySQL
1.9.6 SQL Clients/Report writers
1.9.7 Web development tools that support MySQL
1.9.8 Databse design tools with MySQL support1.9.9 Web servers with MySQL tools1.9.10 Extensions for other programs
1.9.11 Using
|
| Number of licenses | Price per copy | Total |
| 1 | US $200 | US $200 |
| 10 pack | US $150 | US $1500 |
| 50 pack | US $120 | US $6000 |
For high volume (OEM) purchases, the following prices apply:
| Number of licenses | Price per copy | Minimum at one time | Minimum payment |
| 100-999 | US $40 | 100 | US $4000 |
| 1000-2499 | US $25 | 200 | US $5000 |
| 2500-4999 | US $20 | 400 | US $8000 |
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 1 MySQL license, you will get a personal copy of
the myisampack 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.
| Type of support | Cost per year |
| Basic email support | EURO 170 |
| Extended email support | EURO 1000 |
| Login support | EURO 2000 |
| Extended login support | EURO 5000 |
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.
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.
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)
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:
myisampack packing tool for
creating fast compressed read-only databases. The current server includes
support for reading such databases but not the packing tool used to
create them.
Extended email support includes everything in basic email support with these additions:
mysqld for your situation.
Login support includes everything in extended email support with these additions:
kill -9 command).
Extended login support includes everything in login support with these additions:
mysql> select MY_CALCULATION(col_name1,col_name2) from tbl_name;
This chapter describes how to obtain and install 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 [Borsen]
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
Island [GM]
WWW
WWW
Italy [Teta Srl]
WWW
Ireland [Ireland On-Line/Dublin]
WWW
FTP
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 [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:
Asia:
China [Freecode]
WWW
China [Netfirm]
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:
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.
Brazil [Matrix]
Chile [Vision]
South-Africa [Mweb/]
glibc 2.0.7
The first decision to make is whether you want to use the latest development release or the last stable release:
crash-me and
benchmark tests.
See section 10.8 Using your own benchmarks.
The second decision to make is whether you want to use a source distribution or a binary distribution:
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:
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).
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.
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.
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.
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:
crash-me test
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.
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:
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".
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:
| Directory | Contents of directory |
| 'bin' | Client programs and the mysqld server
|
| 'data' | Log files, databases |
| 'include' | Include (header) files |
| 'lib' | Libraries |
| 'scripts' | mysql_install_db
|
| 'share/mysql' | Error message files |
| 'sql-bench' | Benchmarks |
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:
| Directory | Contents of directory |
| 'bin' | Client programs and scripts |
| 'include/mysql' | Include (header) files |
| 'info' | Documentation in Info format |
| 'lib/mysql' | Libraries |
| 'libexec' | The mysqld server
|
| 'share/mysql' | Error message files |
| 'sql-bench' | Benchmarks and crash-me test
|
| 'var' | Databases and log files. |
Within an installation directory, the layout of a source installation differs from that of a binary installation in the following ways:
mysqld server is installed in the 'libexec'
directory rather than in the 'bin' directory.
mysql_install_db is installed in the '/usr/local/bin' directory
rather than in '/usr/local/mysql/scripts'.
You need the following tools to install a MySQL binary distribution:
gunzip to uncompress the distribution.
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:
root.)
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).
shell> gunzip < mysql-VERSION-OS.tar.gz | tar xvf - shell> ln -s mysql-VERSION-OS mysqlThe 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'.
shell> cd mysqlYou will find several files and subdirectories in the
mysql directory.
The most important for installation purposes are the 'bin' and
'scripts' subdirectories.
PATH environment variable so that your shell finds the MySQL
programs properly.
mysql_install_db script used to initialize
the server access permissions.
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 executableChange 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.
shell> scripts/mysql_install_dbNote that MySQL versions older than 3.22.10 started the MySQL server when you run
mysql_install_db. This is no
longer true!
DBI/DBD interface,
see section 4.10 Perl installation comments.
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.
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 '/etc/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), the 'mysqld' demon should be running and you should now be able to start using MySQL. See section 4.15 Post-installation setup and testing.
If something goes wrong, can find more information in the binary installation chapter. See section 4.6 Installing a MySQL binary distribution.
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.
The following sections indicate some of the issues that have been observed to occur on particular systems when installing MySQL from a binary distribution.
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:
tar
distribution) and install this instead.
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.
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.
Some of the binary distributions 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:
The following configurations almost definitely won't work:
To install the distribution, use one of the commands below, where
/path/to/depot is the full pathname of the depot file:
shell> /usr/sbin/swinstall -s /path/to/depot mysql.full
shell> /usr/sbin/swinstall -s /path/to/depot mysql.server
shell> /usr/sbin/swinstall -s /path/to/depot mysql.client
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.
You need the following tools to build and install MySQL from source:
gunzip to uncompress the distribution.
tar to unpack the distribution. GNU tar is
known to work.
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.
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.
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.
tar
archives and have names like 'mysql-VERSION.tar.gz', where
VERSION is a number like 3.22.29.
shell> gunzip < mysql-VERSION.tar.gz | tar xvf -This command creates a directory named 'mysql-VERSION'.
shell> cd mysql-VERSION
shell> ./configure --prefix=/usr/local/mysql shell> makeWhen 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.
shell> make installYou might need to run this command as
root.
shell> scripts/mysql_install_dbNote that MySQL versions older than 3.22.10 started the MySQL server when you run
mysql_install_db. This is no
longer true!
DBI/DBD interface,
see section 4.10 Perl installation comments.
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.
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.
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:
--without-server option:
shell> ./configure --without-serverIf 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.)
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.
configure command like this:
shell> ./configure --with-unix-socket-path=/usr/local/mysql/tmp/mysql.sockNote that the given file must be an absolute pathname!
configure like this:
shell> ./configure --with-client-ldflags=-all-static \
--with-mysqld-ldflags=-all-static
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 ./configureWhen 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.
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
--with-charset option:
shell> ./configure --with-charset=CHARSET
CHARSET may be one of big5, cp1251, cp1257,
czech, danish,dec8, dos, euc_kr,
gb2312 gbk, 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.25 SET OPTION syntax.
Warning: If you change character sets after having created any
tables, you will have to run myisamchk -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.)
--with-debug option:
shell> ./configure --with-debugThis 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.
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:
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.
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:
Internal compiler error: program cc1plus got fatal signal 11 or Out of virtual memory or Virtual memory exhaustedThe 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-memoryThis 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.
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" ./configureThis 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++.
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 directorySolaris and FreeBSD are known to have troublesome
make programs.
GNU make version 3.75 is known to work.
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 CXXFLAGSSee section 4.14 TcX binaries, for a list of flag definitions that have been found to be useful on various systems.
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.
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 XXXChange
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'.)
"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.
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.
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.
configure with the --with-mit-threads option:
shell> ./configure --with-mit-threadsBuilding in a non-source directory is not supported when using MIT-pthreads, because we want to minimize our changes to this code.
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.
--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.
--use-locking option.
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.
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.
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
implicit declaration of function 'int strtoll(...)' implicit declaration of function 'int strtoul(...)'
readline to work with MIT-pthreads. (This isn't
needed, but may be interesting for someone.)
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.
shell> gunzip < Data-Dumper-VERSION.tar.gz | tar xvf -This command creates a directory named 'Data-Dumper-VERSION'.
shell> cd Data-Dumper-VERSION
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.
To install the MySQL DBD module with ActiveState Perl on
Win32, you should do the following:
set HTTP_proxy=my.proxy.com:3128
C:\perl\bin\ppm.pl
DBI: install DBI
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";
The MySQL Perl distribution contains DBI,
DBD:MySQL and DBD:ODBC.
C: so that you get a 'C:\PERL' directory.
perl works by executing perl -v in a DOS shell.
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:
Msql-Mysql-modules distribution with perl
Makefile.PL -static -config rather than perl Makefile.PL
libmysqlclient.so to the directory where your other shared
libraries are located (probably '/usr/lib' or '/lib').
Linux you can add the pathname of the directory where
libmysqlclient.so is located to the '/etc/ld.so.conf' file.
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 -static -config 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 -static -config 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.
The following sections indicate some of the issues that have been observed to occur on particular systems when installing MySQL from a source distribution.
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:
-Lpath):
-Wl,r/full-path-to-libmysqlclient.so.
libmysqclient.so to '/usr/lib'.
libmysqlclient.so is located
to the LD_RUN_PATH environment variable before running your client.
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:
#define HAVE_TERM line from 'config.h' file and
run make again.
CFLAGS=-DHAVE_CURSES CXXFLAGS=-DHAVE_CURSES ./configure
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.
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.
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
Note that glibc versions before and including 2.1.1 has a fatal bug in
pthread_mutex_timedwait handling, which is used when you do INSERT
DELAYED. If you are using INSERT DELAYED, you MUST
add the following patch to your glibc library:
http://www.mysql.com/Downloads/Patches/glibc-pthread_cond_timedwait.patch.
MySQL 3.23.7 contains a temporary workaround for this bug.
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.2.1 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.
myisamchk 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:
-Lpath):
-Wl,r/path-libmysqlclient.so.
libmysqclient.so to '/usr/lib'.
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
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
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.
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.
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.
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.
MySQL should work on MkLinux with the newest glibc package
(tested with glibc 2.0.7).
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).
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.
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
With the Digital compiler "C++ V6.1-029", the following should work:
CC=cc -pthread CFLAGS=-O4 -ansi_alias -ansi_args -fast -inline speed -speculate all -arch host CXX=cxx -pthread CXXFLAGS=-O4 -ansi_alias -ansi_args -fast -inline speed -speculate all -arch host export CC CFLAGS CXX CXXFLAGS ./configure --prefix=/usr/mysql/mysql --with-low-memory --enable-large-files --with-mysqld-ldflags=-all-static --disable-shared --with-named-thread-libs="-lmach -lexc -lc"
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.
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
FreeBSD 3.x is recommended for running MySQL since it the thread package is much more integrated.
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:
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
To compile on NetBSD you need GNU make. Otherwise the compile will crash
when make tries to run lint on C++ files.
On OpenBSD 2.5, you can compile MySQL with native threads with the following options:
CFLAGS=-pthread CXXFLAGS=-pthread ./configure --with-mit-threads=no
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 server runs, for example in safe_mysqld or mysql.server.
Upgrade to BSD/OS 3.1. If that is not possible, install BSDIpatch M300-038.
Use the following command when configuring MySQL:
shell> env CXX=shlicc++ CC=shlicc2 \
./configure \
--prefix=/usr/local/mysql \
--localstatedir=/var/mysql \
--without-perl \
--with-unix-socket-path=/var/mysql/mysql.sock
The following is also known to work:
shell> env CC=gcc CXX=gcc CXXFLAGS=-O3 \
./configure \
--prefix=/usr/local/mysql \
--with-unix-socket-path=/var/mysql/mysql.sock
You can change the directory locations if you wish, or just use the defaults by not specifying any locations.
If you have problems with performance under heavy load, try using the
--skip-thread-priority option to safe_mysqld! This will run
all threads with the same priority; on BSDI 3.1, this gives better
performance (at least until BSDI fixes their thread scheduler).
If you get the error virtual memory exhausted while compiling,
you should 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.
BSDI 4.x has some thread related bugs. If you want to use MySQL on this, you should install all thread related patches. At least M400-023 should be installed.
The current port is tested only on a "sco3.2v5.0.4" and "sco3.2v5.0.5" system. There has also been a lot of progress on a port to "sco 3.2v4.2".
For the moment the recommended compiler on OpenServer is gcc 2.95.2. With this
you should be able to compile MySQL with just:
CC=gcc CXX=gcc ./configure ... (options)
gcc 2.7.2 in Skunkware 97 does not have
GNU as. You can also use egcs 1.1.2 or newer
http://www.egcs.com/. If you are using egcs 1.1.2 you have
to execute the following command:
shell> cp -p /usr/include/pthread/stdtypes.h /usr/local/lib/gcc-lib/i386-pc-sco3.2v5.0.5/ egcs-2.91.66/include/pthread/
./configure in the 'threads/src' directory and select
the SCO OpenServer option. This command copies 'Makefile.SCO5' to
'Makefile'.
make.
cd to the 'thread/src' directory, and run make
install.
make when making MySQL.
shell> CC="gcc -DSCO" CXX="gcc -DSCO" ./configureThe
-DSCO is needed to help configure detect some thread
functions properly. If you forget -DSCO, you will get the following
error message while compiling:
my_pthread.c: In function 'my_pthread_mutex_init': my_pthread.c:374: 'pthread_mutexattr_default' undeclared (first use this function)
safe_mysqld as root, you probably will get only the
default 110 open files per process. mysqld will write a note about this
in the log file.
configure command should work:
shell> CC="gcc -belf" ./configure --prefix=/usr/local/mysql --disable-shared
configure command should work:
shell> CFLAGS="-D_XOPEN_XPG4" CXX=gcc CXXFLAGS="-D_XOPEN_XPG4" \
./configure \
--with-debug --prefix=/usr/local/mysql \
--with-named-thread-libs="-lgthreads -lsocket -lgen -lgthreads" \
--with-named-curses-libs="-lcurses"
You may get some problems with some include files. In this case, you can
find new SCO-specific include files at
ftp://www.mysql.com/pub/mysql/Downloads/SCO/SCO-3.2v4.2-includes.tar.gz.
You should unpack this file in the 'include'
directory of your MySQL source tree.
SCO development notes:
mysqld
with -lgthreads -lsocket -lgthreads.
www.mysql.com) comes linked with
GNU malloc. If you encounter problems with memory usage, make sure that
'gmalloc.o'
is included in 'libgthreads.a' and 'libgthreads.so'.
read(),
write(), getmsg(), connect(), accept(),
select() and wait().
If you want to install DBI on SCO, you have to edit the 'Makefiles' in DBI-xxx and each subdirectory:
OLD: NEW: CC = cc CC = gcc -belf CCCDLFLAGS = -KPIC -W1,-Bexport CCCDLFLAGS = -fpic CCDLFLAGS = -wl,-Bexport CCDLFLAGS = LD = ld LD = gcc -belf -G -fpic LDDLFLAGS = -G -L/usr/local/lib LDDLFLAGS = -L/usr/local/lib LDFLAGS = -belf -L/usr/local/lib LDFLAGS = -L/usr/local/lib LD = ld LD = gcc -belf -G -fpic OPTIMISE = -Od OPTIMISE = -O1 OLD: CCCFLAGS = -belf -dy -w0 -U M_XENIX -DPERL_SCO5 -I/usr/local/include NEW: CCFLAGS = -U M_XENIX -DPERL_SCO5 -I/usr/local/include
This is because the Perl dynaloader will not load the DBI modules
if they were compiled with icc or cc.
Perl works best when compiled with cc.
You must use a version of MySQL at least as recent as 3.22.13, since that version fixes some portability problems under Unixware.
We have been able to compile MySQL with the following configure
command on UnixWare 7.0.1:
shell> CC=cc CXX=CC ./configure --prefix=/usr/local/mysql
Automatic detection of xlC is missing from Autoconf, so a
configure command something like this is needed when using the IBM
compiler:
shell> CC="xlc_r -ma -O3 -qstrict -DHAVE_INT_8_16_32" \
CXX="xlC_r -ma -O3 -qstrict -DHAVE_INT_8_16_32" \
./configure
If you change the -O3 to -O2 in the above configure line,
you must also remove the -qstrict option (this is a limitation in
the IBM C compiler).
If you are using egcs to compile MySQL, you
MUST use the -fno-exceptions flag, as the exception
handling in egcs is not thread-safe! (This is tested with
egcs 1.1.) We recommend the following configure line with
egcs and gcc on AIX:
shell> CXX=gcc \
CXXFLAGS="-felide-constructors -fno-exceptions -fno-rtti" \
./configure --prefix=/home/monty --with-debug --with-low-memory
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 CXX=gcc \
CXXFLAGS="-felide-constructors -fno-exceptions -fno-rtti -DDONT_USE_THR_ALARM" \
./configure --prefix=/home/monty --with-debug --with-low-memory
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.
On some versions of AIX, linking with libbind.a makes getservbyname core
dump. This is an AIX bug and should be reported to IBM.
There are a couple of "small" problems when compiling MySQL on
HP-UX. We recommend that you use gcc instead of the HP-UX native
compiler, because gcc produces better code!
We recommend one to use gcc 2.95 on HP-UX. Don't use high optimization flags (like -O6) as this may not be safe on HP-UX.
Note that MIT-pthreads can't be compiled with the HP-UX compiler,
because it can't compile .S (assembler) files.
The following configure line should work:
CFLAGS="-DHPUX -I/opt/dce/include" CXXFLAGS="-DHPUX -I/opt/dce/include -felide-constructors -fno-exceptions -fno-rtti" CXX=gcc ./configure --with-pthread --with-named-thread-libs='-ldce' --prefix=/usr/local/mysql --disable-shared
If you are compiling gcc 2.95 yourself, you should NOT link it with
the DCE libraries (libdce.a or libcma.a) if you want to compile
MySQL with MIT-pthreads. If you mix the DCE and MIT-pthreads
packages you will get a mysqld to which you cannot connect. Remove
the DCE libraries while you compile gcc 2.95!
Here is some information that a HP-UX 11.x user sent us:
Note that some of these things are already fixed in MySQL 3.23.
Note: binary distribution for hp-ux 10.20 pa1.1 dumps core on
hp-ux 11.0 pa2.0 during scripts/mysql_install_db. As such, I
feel it necessary to build from scratch. This was a mildly
painful process so I am sharing my work so others may benefit.
Environment:
proper compilers.
setenv CC cc
setenv CXX aCC
flags
setenv CFLAGS -D_REENTRANT
setenv CXXFLAGS -D_REENTRANT
setenv CPPFLAGS -D_REENTRANT
% aCC -V
aCC: HP ANSI C++ B3910B X.03.14.06
% cc -V /tmp/empty.c
cpp.ansi: HP92453-01 A.11.02.00 HP C Preprocessor (ANSI)
ccom: HP92453-01 A.11.01.00 HP C Compiler
cc: "/tmp/empty.c", line 1: warning 501: Empty source file.
configuration:
./configure --with-pthread \
--prefix=/source-control/mysql \
--with-named-thread-libs=-lpthread \
--with-low-memory
/* Don't include std ctype.h when this is included */
#define _CTYPE_H
#define __CTYPE_INCLUDED
#define _CTYPE_INCLUDED
#define _CTYPE_USING /* Don't put names in global namespace. */
-D_REENTRANT to get the
compiler to recognize the prototype for
localtime_r. Alternatively I could have supplied the
prototype for localtime_r. But I wanted to catch other bugs
without needing to run into them. I wasn't sure where I
needed it so I added it to all flags.
char
*. This is a deprecated feature. I did not change the
behaviour.
You can get MySQL to work on MacOS X by following the links to the MacOS X ports. See section 1.9 Useful MySQL-related links.
MySQL 3.23.7 should include all patches necessary to configure it on MacOSX. You must however first install the pthread package from MySql for MacOSX Server before configuring MySQL.
You might want to also add aliases to your shell's resource file to
access mysql and mysqladmin from the command line.
alias mysql '/usr/local/mysql/bin/mysql' alias mysqladmin '/usr/local/mysql/libexec/mysqladmin'
This section describes installation and use of MySQL on Win32. This is also described in the 'README' file that comes with the MySQL Win32 distribution.
If you don't have a registered version of MySQL, you should first download the shareware version from:
If you plan to connect to MySQL from some other program, you will probably also need the MyODBC driver. You can find this at the MySQL download page.
To install either distribution, unzip it in some empty directory and run the
Setup.exe program.
By default, MySQL-Win32 is configured to be installed in
'C:\mysql'. If you want to install MySQL elsewhere, install it
in 'C:\mysql', then move the installation to where you want it. If you
do move MySQL, you must tell mysqld where everything is by
supplying options to mysqld. Use C:\mysql\bin\mysqld --help to
display all options! For example, if you have moved the MySQL
distribution to 'D:\programs\mysql', you must start mysqld with:
D:\programs\mysql\bin\mysqld --basedir D:\programs\mysql
With the registered version of MySQL, you can also create a
'C:\my.cnf' file that holds any default options for the
MySQL server. Copy the file '\mysql\my-example.cnf' to
'C:\my.cnf' and edit this to suit your setup. Note that you should
specify all paths with / instead of \. If you use
\, you need to specify this twice, as \ is the escape
character in MySQL.
See section 4.15.4 Option files.
MySQL uses TCP/IP to connect a client to a server. (This will allow any machine on your network to connect to your MySQL server). Because of this, you must install TCP/IP on your machine before starting MySQL. You can find TCP/IP on your Windows CD-ROM.
Note that if you are using an old Win95 release (for example OSR2), it's likely that you have an old Winsock package! MySQL requires Winsock 2! You can get the newest Winsock from Microsoft. Win98 has as default the new Winsock 2 library, so the above doesn't apply for Win98.
There are 2 different MySQL servers you can use:
mysqld | Compiled with full debugging and automatic memory allocation checking |
mysqld-opt | Optimized for a Pentium processor. |
Both of the above should work on any Intel processor >= i386.
To start the mysqld server, you should start a MS-DOS window and type:
C:\mysql\bin\mysqld
This will start mysqld in the background without a window.
You can kill the MySQL server by executing:
C:\mysql\bin\mysqladmin -u root shutdown
Note that Win95/Win98 don't support creation of named pipes. On Win95/Win98, you can only use named pipes to connect to a remote MySQL running on an NT server.
The Win95/Win98 section also applies to MySQL on NT, with the following differences:
To get MySQL to work with TCP/IP, you must install service pack 3 (or newer)!
For NT, the server name is mysqld-nt. Normally you should install
MySQL as a service on NT:
C:\mysql\bin\mysqld-nt --install
(You could use the mysqld or mysqld-opt servers on NT,
but those cannot be started as a service or use named pipes.)
You can start and stop the MySQL service with:
NET START mysql NET STOP mysql
Note that in this case you can't use any other options for mysqld-nt!
You can also run mysqld-nt as a standalone program on NT if you need
to start mysqld-nt with any options! If you start mysqld-nt
without options on NT, mysqld-nt tries to starts itself as a service
with the default service options. If you have stopped mysqld-nt, you
have to start it with NET START mysql.
The service is installed with the name MySql. Once installed, it must
be started using Services Control Manager (SCM) Utility (found in Control
Panel) or by using the NET START MySQL command. If any options are
desired, they must be specified as "Startup parameters" in the SCM utility
before you start the MySQL service. Once running, mysqld-nt
can be stopped using mysqladmin or from the SCM utility or by using
the command NET STOP MySQL. If you use SCM to stop mysqld-nt,
there is a strange message from SCM about mysqld shutdown normally.
When run as a service, mysqld-nt has no access to a console and so no
messages can be seen.
On NT you can get the following service error messages:
| Permission Denied | Means that it cannot find mysqld-nt.exe
|
| Cannot Register | Means that the path is incorrect |
If you have problems installing mysqld-nt as a service, try starting
it with the full path:
C:\mysql\bin\mysqld-nt --install
If this doesn't work, you can get mysqld-nt to start properly by fixing
the path in the registry!
If you don't want to start mysqld-nt as a service, you can start it as
follows:
C:\mysql\bin\mysqld-nt --standalone
or
C:\mysql\bin\mysqld-nt --standalone --debug
The last version gives you a debug trace in 'C:\mysqld.trace'.
MySQL supports TCP/IP on all Win32 platforms and named pipes on NT. The default is to use named pipes for local connections on NT and TCP/IP for all other cases if the client has TCP/IP installed. The host name specifies which protocol is used:
| protocol | |
| NULL (none) | On NT, try named pipes first; if that doesn't work, use TCP/IP. On Win95/Win98, TCP/IP is used. |
| . | Named pipes |
| localhost | TCP/IP to current host |
| hostname | TCP/IP |
You can force a MySQL client to use named pipes by specifying the
--pipe option. Use the --socket option to specify the name of
the pipe.
You can test whether or not MySQL is working by executing the following commands:
C:\mysql\bin\mysqlshow C:\mysql\bin\mysqlshow -u root mysql C:\mysql\bin\mysqladmin version status proc C:\mysql\bin\mysql test
If mysqld is slow to answer to connections on Win95/Win98, there is
probably a problem with your DNS. In this case, start mysqld with
--skip-name-resolve and use only localhost and IP numbers in
the MySQL grant tables. You can also avoid DNS when connecting to a
mysqld-nt MySQL server running on NT by using the
--pipe argument to specify use of named pipes. This works for most
MySQL clients.
There are two versions of the MySQL command line tool:
mysql | Compiled on native Win32, which offers very limited text editing capabilities. |
mysqlc | Compiled with the Cygnus GNU compiler and libraries, which offers readline editing.
|
If you want to use mysqlc.exe, you must copy
'C:\mysql\lib\cygwinb19.dll' to '\windows\system' (or similar
place).
The default privileges on Win32 give all local users full privileges
to all databases. To make MySQL more secure, you
should set a password for all users and remove the row in the
mysql.user table that has Host='localhost' and
User=".
You should also add a password for the root user:
(The following example starts by removing the anonymous user, that allows
anyone to access the 'test' database)
C:\mysql\bin\mysql mysql mysql> DELETE FROM user WHERE Host='localhost' AND User="; mysql> QUIT C:\mysql\bin\mysqladmin reload C:\mysql\bin\mysqladmin -u root password your_password
After you've set the password, if you want to take down the mysqld
server, you can do so using this command:
mysqladmin --user=root --password=your_password shutdown
If you are using the shareware version of MySQL under windows,
the above command will fail with an error: parse error near 'SET
OPTION password'. This is because the shareware version, which is
based on MySQL 3.21, doesn't have the SET PASSWORD command.
With the shareware version you can set the password for the root user as follows:
C:\mysql\bin\mysql mysql
mysql> UPDATE user SET password=PASSWORD('your password') WHERE user='root';
mysql> QUIT
C:\mysql\bin\mysqladmin reload
With the registered MySQL version you can easily add new users
and change privileges with GRANT and REVOKE commands.
See section 7.26 GRANT and REVOKE syntax. With the Windows shareware version on has to use
INSERT, UPDATE and DELETE one the tables in the
mysql database to manage users and their privileges.
See section 6.15 Causes of Access denied errors.
Here is a note about how to connect to get a secure connection to remote MySQL server with SSH (by David Carlson).
local port: 3306,
host: localhost, remote port: 3306
That's it. It works very well with a direct Internet connection. I'm having problems with SSH conflicting with my Win95 network and Wingate - but that'll be the topic of a posting on another software company's usegroup!
MySQL-Win32 has by now proven itself to be very stable. This version of MySQL has the same features as the corresponding Unix version with the following exceptions:
mysqld for an extended time on Win95 if
you do many connections, since each connection in MySQL creates
a new thread! WinNT and Win98 don't suffer from this bug.
mysqladmin kill will not work on a sleeping connection.
mysqladmin shutdown can't abort as long as there are sleeping
connections.
DROP DATABASE
mysqladmin shutdown.
my_table and as MY_TABLE:
SELECT * FROM my_table WHERE MY_TABLE.col=1;
LOAD
DATA INFILE or SELECT ... INTO OUTFILE, you must double the '\'
character or use Unix style filenames '/' characters:
LOAD DATA INFILE "C:\\tmp\\skr.txt" INTO TABLE skr; SELECT * FROM skr INTO OUTFILE 'C:/tmp/skr.txt';
Can't open named pipe error
error 2017: can't open named pipe to host: . pipe...This is because the release version of MySQL uses named pipes on NT by default. You can avoid this error by using the
--host=localhost option to the new MySQL clients
or create a file 'C:\my.cnf' that contains the following information:
[client] host = localhost
Access denied for user error
Access denied for user: 'some-user@unknown'
to database 'mysql' when accessing a MySQL server on the same
machine, this means that MySQL can't resolve your host name
properly.
To fix this, you should create a file '\windows\hosts' with the
following information:
127.0.0.1 localhost
Here are some open issues for anyone who might want to help us with the Win32 release:
MYSQL.DLL server. This should include everything in
a standard MySQL server, except thread creation. This will make
MySQL much easier to use in applications that don't need a true
client/server and don't need to access the server from other hosts.
mysqld.cc,
but it should be recoded to be more "parameter" oriented.
The tool should also be able to update the '\my.cnf' file if the user
would prefer to use this instead of the registry.
mysqld as a service with --install (on NT)
it would be nice if you could also add default options on the command line.
For the moment, the workaround is to update the 'C:\my.cnf' file
instead.
mysqld
daemon doesn't accept new connections when the laptop is resumed.
We don't know if this is a problem with Win95, TCP/IP or MySQL.
mysqld from the
task manager. For the moment, you must use mysqladmin shutdown.
readline to Win32 for use in the mysql command line tool.
mysql,
mysqlshow, mysqladmin, and mysqldump) would be nice.
mysqladmin kill on Win32.
mysqld always starts in the "C" locale and not in the default locale.
We would like to have mysqld use the current locale for the sort order.
sqlclient to Win32 (almost done) and add more features to it!
.DLLs.
Other Win32-specific issues are described in the 'README' file that comes with the MySQL-Win32 distribution.
MySQL uses quite a few open files. Because of this, you should add something like the following to your 'CONFIG.SYS' file:
SET EMXOPT=-c -n -h1024
If you don't do this, you will probably run into the following error:
File 'xxxx' not found (Errcode: 24)
When using MySQL with OS/2 Warp 3, FixPack 29 or above is required. With OS/2 Warp 4, FixPack 4 or above is required. This is a requirement of the Pthreads library. MySQL must be installed in a partition that supports long file names such as HPFS, FAT32, etc.
The 'INSTALL.CMD' script must be run from OS/2's own 'CMD.EXE' and may not work with replacement shells such as '4OS2.EXE'.
The 'scripts/mysql-install-db' script has been renamed: it is now called 'install.cmd' and is a REXX script which will set up the default MySQL security settings and create the WorkPlace Shell icons for MySQL.
Dynamic module support is compiled in but not fully tested. Dynamic modules should be compiled using the Pthreads runtime library.
gcc -Zdll -Zmt -Zcrtdll=pthrdrtl -I../include -I../regex -I.. \
-o example udf_example.cc -L../lib -lmysqlclient udf_example.def
mv example.dll example.udf
Note: Due to limitations in OS/2, UDF module name stems must not
exceed 8 characters. Modules are stored in the '/mysql2/udf'
directory; the safe-mysqld.cmd script will put this directory in
the BEGINLIBPATH environment variable. When using UDF modules,
specified extensions are ignored -- it is assumed to be '.udf'.
For example, in Unix, the shared module might be named 'example.so'
and you would load a function from it like this:
CREATE FUNCTION metaphon RETURNS STRING SONAME "example.so";
Is OS/2, the module would be named 'example.udf', but you would not specify the module extension:
CREATE FUNCTION metaphon RETURNS STRING SONAME "example";
As a service, TcX provides a set of binary distributions of MySQL that are compiled at TcX or at sites where customers kindly have given us access to their machines.
These distributions
are generated with scripts/make_binary_distribution and are
configured with the following compilers and options:
gcc 2.7.2.1
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --disable-shared
egcs 1.0.3a
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 --with-low-memory
egcs 2.90.27
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 --with-low-memory
gcc 2.8.1
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-low-memory
pgcc 2.90.29 (egcs 1.0.3a)
CFLAGS="-O6 -mpentium -mstack-align-double -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O6 -mpentium -mstack-align-double -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static
gcc 2.7-95q4
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
gcc 2.7.2.2
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
gcc 2.8.1
CC=gcc CFLAGS=-O CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-low-memory
gcc 2.8.0
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
gcc 2.7.2.1
CC=gcc CXX=gcc CXXFLAGS=-O ./configure --prefix=/usr/local/mysql
gcc 2.7.2
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
Anyone who has more optimal options for any of the configurations listed above can always mail them to the developer's mailing list at
RPM distributions prior to MySQL 3.22 are user-contributed. Beginning with 3.22, some RPMs are TcX-generated.
Once you've installed MySQL (from either a binary or source distribution), you need to initialize the grant tables, start the server and make sure that the server works okay. You may also wish to arrange for the server to be started and stopped automatically when your system starts up and shuts down.
Normally you install the grant tables and start the server like this for installation from a source distribution:
shell> ./scripts/mysql_install_db shell> cd mysql_installation_directory shell> ./bin/safe_mysqld &
For a binary distribution, do this:
shell> cd mysql_installation_directory shell> ./bin/mysql_install_db shell> ./bin/safe_mysqld &
Testing is most easily done from the top-level directory of the MySQL distribution. For a binary distribution, this is your installation directory (typically something like '/usr/local/mysql'). For a source distribution, this is the main directory of your MySQL source tree.
In the commands shown below in this section and in the following
subsections, BINDIR is the path to the location in which programs
like mysqladmin and safe_mysqld are installed. For a
binary distribution, this is the 'bin' directory within the
distribution. For a source distribution, BINDIR is probably
'/usr/local/bin', unless you specified an installation directory
other than '/usr/local' when you ran configure.
EXECDIR is the location in which the mysqld server is
installed. For a binary distribution, this is the same as
BINDIR. For a source distribution, EXECDIR is probably
'/usr/local/libexec'.
Testing is described in detail below:
mysqld server and set up the initial
MySQL grant tables containing the privileges that determine how
users are allowed to connect to the server. This is normally done with the
mysql_install_db script:
shell> scripts/mysql_install_dbTypically,
mysql_install_db needs to be run only the first time you
install MySQL. Therefore, if you are upgrading an existing
installation, you can skip this step. (However, mysql_install_db is
quite safe to use and will not update any tables that already exist, so if
you are unsure what to do, you can always run mysql_install_db.)
mysql_install_db creates six tables (user, db,
host, tables_priv, columns_priv and func) in the
mysql database. A description of the initial privileges is given in
section 6.12 Setting up the initial MySQL privileges. Briefly, these privileges allow the MySQL
root user to do anything, and allow anybody to create or use databases
with a name of 'test' or starting with 'test_'.
If you don't set up the grant tables, the following error will appear in the
log file when you start the server:
mysqld: Can't find file: 'host.frm'The above may also happens with a binary MySQL distribution if you don't start MySQL by executing exactly
./bin/safe_mysqld!
You might need to run mysql_install_db as root. However,
if you prefer, you can run the MySQL server as an unprivileged
(non-root) user, provided that user can read and write files in
the database directory. Instructions for running MySQL as an
unprivileged user are given in section 18.8 How to run MySQL as a normal user.
If you have problems with mysql_install_db, see
section 4.15.1 Problems running mysql_install_db.
There are some alternatives to running the mysql_install_db
script as it is provided in the MySQL distribution:
mysql_install_db before running it, to
change the initial privileges that are installed into the grant tables.
This is useful if you want to install MySQL on a lot of machines
with the same privileges. In this case you probably should need only to add
a few extra INSERT statements to the mysql.user and
mysql.db tables!
mysql_install_db, then use mysql -u root mysql to
connect to the grant tables as the MySQL root user and issue
SQL statements to modify the grant tables directly.
mysql_install_db.
shell> cd mysql_installation_directory shell> bin/safe_mysqld &If you have problems starting the server, see section 4.15.2 Problems starting the MySQL server.
mysqladmin to verify that the server is running. The following
commands provide a simple test to check that the server is up and responding
to connections:
shell> BINDIR/mysqladmin version shell> BINDIR/mysqladmin variablesThe output from
mysqladmin version varies slightly depending on your
platform and version of MySQL, but should be similar to that shown
below:
shell> BINDIR/mysqladmin version mysqladmin Ver 6.3 Distrib 3.22.9-beta, for pc-linux-gnu on i686 TCX Datakonsult AB, by Monty Server version 3.22.9-beta Protocol version 10 Connection Localhost via UNIX socket TCP port 3306 UNIX socket /tmp/mysql.sock Uptime: 16 sec Running threads: 1 Questions: 20 Reloads: 2 Open tables: 3To get a feeling for what else you can do with
BINDIR/mysqladmin,
invoke it with the --help option.
shell> BINDIR/mysqladmin -u root shutdown
safe_mysqld or
by invoking mysqld directly. For example:
shell> BINDIR/safe_mysqld --log &If
safe_mysqld fails, try running it from the MySQL
installation directory (if you are not already there). If that doesn't work,
see section 4.15.2 Problems starting the MySQL server.
shell> BINDIR/mysqlshow +-----------+ | Databases | +-----------+ | mysql | +-----------+ shell> BINDIR/mysqlshow mysql Database: mysql +--------------+ | Tables | +--------------+ | columns_priv | | db | | func | | host | | tables_priv | | user | +--------------+ shell> BINDIR/mysql -e "select host,db,user from db" mysql +------+--------+------+ | host | db | user | +------+--------+------+ | % | test | | | % | test_% | | +------+--------+------+There is also a benchmark suite in the 'sql-bench' directory (under the MySQL installation directory) that you can use to compare how MySQL performs on different platforms. The 'sql-bench/Results' directory contains the results from many runs against different databases and platforms. To run all tests, execute these commands:
shell> cd sql-bench shell> run-all-testsIf you don't have the 'sql-bench' directory, you are probably using an RPM for a binary distribution. (Source distribution RPMs include the benchmark directory.) In this case, you must first install the benchmark suite before you can use it. Beginning with MySQL 3.22, there are benchmark RPM files named 'mysql-bench-VERSION-i386.rpm' that contain benchmark code and data. If you have a source distribution, you can also run the tests in the 'tests' subdirectory. For example, to run 'auto_increment.tst', do this:
shell> BINDIR/mysql -vvf test < ./tests/auto_increment.tstThe expected results are shown in the './tests/auto_increment.res' file.
mysql_install_db
This section lists problems you might encounter when you run
mysql_install_db:
mysql_install_db doesn't install the grant tables
mysql_install_db fails to install the grant
tables and terminates after displaying the following messages:
starting mysqld daemon with databases from XXXXXX mysql daemon endedIn this case, you should examine the log file very carefully! The log should be located in the directory 'XXXXXX' named by the error message, and should indicate why
mysqld didn't start. If you don't understand
what happened, include the log when you post a bug report using
mysqlbug!
See section 2.3 How to report bugs or problems.
mysqld daemon running
mysql_install_db at
all. You have to run mysql_install_db only once, when you install
MySQL the first time.
mysqld daemon doesn't work when one daemon is running
Can't start server: Bind on
TCP/IP port: Address already in use or Can't start server : Bind on
unix socket... You can start the new server with a different socket and
port as follows:
shell> MYSQL_UNIX_PORT=/tmp/mysqld-new.sock shell> MYSQL_TCP_PORT=3307 shell> export MYSQL_UNIX_PORT MYSQL_TCP_PORT shell> scripts/mysql_install_db shell> bin/safe_mysqld &After this, you should edit your server boot script to start both daemons with different sockets and ports. For example, it could invoke
safe_mysqld twice, but with different --socket, --port
and --basedir options for each invocation.
mysql_install_db or when
starting or using mysqld.
You can specify a different socket and temporary directory as follows:
shell> TMPDIR=/some_tmp_dir/ shell> MYSQL_UNIX_PORT=/some_tmp_dir/mysqld.sock shell> export TMPDIR MYSQL_UNIX_PORT'some_tmp_dir' should be the path to some directory for which you have write permission. After this you should be able to run
mysql_install_db and start
the server with these commands:
shell> scripts/mysql_install_db shell> BINDIR/safe_mysqld &
mysqld crashes immediately
glibc older than
2.0.7-5, you should make sure you have installed all glibc patches!
There is a lot of information about this in the MySQL mail
archives. Links to the mail archives are available at the online
MySQL documentation page.
Also, see section 4.11.5 Linux notes (all Linux versions).
You can also start mysqld manually using the --skip-grant-tables
option and add the privilege information yourself using mysql:
shell> BINDIR/safe_mysqld --skip-grant-tables & shell> BINDIR/mysql -u root mysqlFrom
mysql, manually execute the SQL commands in
mysql_install_db. Make sure you run mysqladmin
flush-privileges or mysqladmin reload afterward to tell the server to
reload the grant tables.
Generally, you start the mysqld server in one of three ways:
mysql.server. This script is used primarily at
system startup and shutdown, and is described more fully in
section 4.15.3 Starting and stopping MySQL automatically.
safe_mysqld, which tries to determine the proper options
for mysqld and then runs it with those options.
mysqld directly.
Whichever method you use to start the server, if it fails to start up
correctly, check the log file to see if you can find out why. Log files
are located in the data directory (typically
'/usr/local/mysql/data' for a binary distribution,
'/usr/local/var' for a source distribution). Look in the data
directory for files with names of the form 'host_name.err' and
'host_name.log' where host_name is the name of your server
host. Then check the last few lines of these files:
shell> tail host_name.err shell> tail host_name.log
When the mysqld daemon starts up, it changes directory to the
data directory. This is where it expects to write log files and the pid
(process ID) file, and where it expects to find databases.
The data directory location is hardwired in when the distribution is
compiled. However, if mysqld expects to find the data directory
somewhere other than where it really is on your system, it will not work
properly. If you have problems with incorrect paths, you can find out
what options mysqld allows and what the default path settings are by
invoking mysqld with the --help option. You can override the
defaults by specifying the correct pathnames as command-line arguments to
mysqld. (These options can be used with safe_mysqld as well.)
Normally you should need to tell mysqld only the base directory under
which MySQL is installed. You can do this with the --basedir
option. You can also use --help to check the effect of changing path
options (note that --help must be the final option of the
mysqld command). For example:
shell> EXECDIR/mysqld --basedir=/usr/local --help
Once you determine the path settings you want, start the server without
the --help option.
If you get the following error, it means that some other program (or another
mysqld server) is already using the TCP/IP port or socket
mysqld is trying to use:
Can't start server: Bind on TCP/IP port: Address already in use or Can't start server : Bind on unix socket...
Use ps to make sure that you don't have another mysqld server
running. If you can't find another server running, you can try to execute
the command telnet your-host-name tcp-ip-port-number and press
RETURN a couple of times. If you don't get a error message like
telnet: Unable to connect to remote host: Connection refused,
something is using the TCP/IP port mysqld is trying to use.
See section 4.15.1 Problems running mysql_install_db, and section 19.3 Running multiple MySQL servers on the same machine.
The safe_mysqld script is written so that it normally is able to start
a server that was installed from either a source or a binary version of
MySQL, even if these install the server in slightly different
locations. safe_mysqld expects one of these conditions to be true:
safe_mysqld is invoked. safe_mysqld looks under its working
directory for 'bin' and 'data' directories (for binary
distributions) or for 'libexec' and 'var' directories (for source
distributions). This condition should be met if you execute
safe_mysqld from your MySQL installation directory (for
example, '/usr/local/mysql' for a binary distribution).
safe_mysqld attempts to locate them by absolute pathnames. Typical
locations are '/usr/local/libexec' and '/usr/local/var'.
The actual locations are determined when the distribution was built from which
safe_mysqld comes. They should be correct if
MySQL was installed in a standard location.
Since safe_mysqld will try to find the server and databases relative
to its own working directory, you can install a binary distribution of
MySQL anywhere, as long as you start safe_mysqld from the
MySQL installation directory:
shell> cd mysql_installation_directory shell> bin/safe_mysqld &
If safe_mysqld fails, even when invoked from the MySQL
installation directory, you can modify it to use the path to mysqld
and the pathname options that are correct for your system. Note that if you
upgrade MySQL in the future, your modified version of
safe_mysqld will be overwritten, so you should make a copy of your
edited version that you can reinstall.
If mysqld is currently running, you can find out what path settings
it is using by executing this command:
shell> mysqladmin variables or shell> mysqladmin -h 'your-host-name' variables
If safe_mysqld starts the server but you can't connect to it,
you should make sure you have an entry in '/etc/hosts' that looks like
this:
127.0.0.1 localhost
This problem occurs only on systems that don't have a working thread library and for which MySQL must be configured to use MIT-pthreads.
The mysql.server script can be used to start or stop the server,
by invoking it with start or stop arguments:
shell> mysql.server start shell> mysql.server stop
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.
Before mysql.server starts the server, it changes directory to
the MySQL installation directory, then invokes
safe_mysqld. You might need to edit mysql.server if you
have a binary distribution that you've installed in a non-standard
location. Modify it to cd into the proper directory before it
runs safe_mysqld. If you want the server to run as some specific
user, you can change the mysql_daemon_user=root line to use
another user. You can also modify mysql.server to pass other
options to safe_mysqld.
mysql.server stop brings down server by sending a signal to it.
You can take down the server manually by executing mysqladmin shutdown.
You might want to add these start and stop commands to the appropriate places
in your '/etc/rc*' files when you start using MySQL for
production applications. Note that if you modify mysql.server, then
if you upgrade MySQL sometime, your modified version will be
overwritten, so you should make a copy of your edited version that you can
reinstall.
If your system uses '/etc/rc.local' to start external scripts, you should append the following to it:
/bin/sh -c 'cd /usr/local/mysql ; ./bin/safe_mysqld &'
You can also add options for mysql.server in a global
'/etc/my.cnf' file. A typical '/etc/my.cnf' file might look like
this:
[mysqld] datadir=/usr/local/mysql/var socket=/tmp/mysqld.sock port=3306 [mysql.server] user=mysql basedir=/usr/local/mysql
The mysql.server script uses the following variables:
user, datadir, basedir, bindir and pid-file.
See section 4.15.4 Option files.
MySQL 3.22 can read default startup options for the server and for clients from option files.
MySQL reads default options from the following files on Unix:
| Filename | Purpose |
/etc/my.cnf | Global options |
DATADIR/my.cnf | Server-specific options |
~/.my.cnf | User-specific options |
DATADIR is the MySQL data directory (typically
'/usr/local/mysql/data' for a binary installation, or
'/usr/local/var' for a source installation). Note that this is the
directory that was specified at configuration time, not the one specified
with --datadir when mysqld starts up! (--datadir has no
effect on where the server looks for option files, because it looks for them
before it processes any command-line arguments.)
MySQL reads default options from the following files on Win32:
| Filename | Purpose |
windows-system-directory\my.ini
| |
C:\my.cnf | Global options |
C:\mysql\data\my.cnf | Server-specific options |
Note that you on Win32 should specify all paths with / instead of
\. If you use \, you need to specify this twice, as
\ is the escape character in MySQL.
MySQL tries to read option files in the order listed above. If multiple option files exist, an option specified in a file read later takes precedence over the same option specified in a file read earlier. Options specified on the command line take precedence over options specified in any option file. Some options can be specified using environment variables. Options specified on the command line or in option files take precedence over environment variable values.
The following programs support option files: mysql,
mysqladmin, mysqld, mysqldump, mysqlimport,
mysql.server, myisamchk and myisampack.
You can use option files to specify any long option that a program supports!
Run the program with --help to get a list of available options.
An option file can contain lines of the following forms:
#comment
[group]
group is the name of the program or group for which you want to set
options. After a group line, any option or set-variable lines
apply to the named group until the end of the option file or another group
line is given.
option
--option on the command line.
option=value
--option=value on the command line.
set-variable = variable=value
--set-variable variable=value on the command line.
This syntax must be used to set a mysqld variable.
The client group allows you to specify options that apply to all
MySQL clients (not mysqld). This is the perfect group to use
to specify the password you use to connect to the server. (But make
sure the option file is readable and writable only to yourself.)
Note that for options and values, all leading and trailing blanks are automatically deleted. You may use the escape sequences '\b', '\t', '\n', '\r', '\\' and '\s' in your value string ('\s' == blank).
Here is a typical global option file:
[client] port=3306 socket=/tmp/mysql.sock [mysqld] port=3306 socket=/tmp/mysql.sock set-variable = key_buffer=16M set-variable = max_allowed_packet=1M [mysqldump] quick
Here is typical user option file:
[client] # The following password will be sent to all standard MySQL clients password=my_password [mysql] no-auto-rehash
If you have a source distribution, you will find a sample configuration file
named 'my-example.cnf' in the 'support-files' directory. If you
have a binary distribution, look in the 'DIR/share/mysql' directory,
where DIR is the pathname to the MySQL installation directory
(typically '/usr/local/mysql'). You can copy 'my-example.cnf' to
your home directory (rename the copy to '.my.cnf') to experiment with.
To tell a MySQL program not to read any option files, specify
--no-defaults as the first option on the command line. This
MUST be the first option or it will have no effect!
If you want to check which options are used, you can give the option
--print-defaults as the first option.
If you want to force the use of a specific config file, you can use the option
--defaults-file=full-path-to-default-file. If you do this, only the
specified file will be read.
Note for developers: Option file handling is implemented simply by processing all matching options (i.e., options in the appropriate group) before any command line arguments. This works nicely for programs that use the last instance of an option that is specified multiple times. If you have an old program that handles multiply-specified options this way but doesn't read option files, you need add only two lines to give it that capability. Check the source code of any of the standard MySQL clients to see how to do this.
You can always move the MySQL form and data files between
different versions on the same architecture as long as you have the same
base version of MySQL. The current base version is
3. If you change the character set by recompiling MySQL (which may
also change the sort order), you must run myisamchk -r -q on all
tables. Otherwise your indexes may not be ordered correctly.
If you are paranoid and/or afraid of new versions, you can always rename your
old mysqld to something like mysqld-'old-version-number'. If
your new mysqld then does something unexpected, you can simply shut it
down and restart with your old mysqld!
When you do an upgrade you should also backup your old databases, of course. Sometimes it's good to be a little paranoid!
After an upgrade, if you experience problems with recompiled client programs,
like Commands out of sync or unexpected core dumps, you probably have
used an old header or library file when compiling your programs. In this
case you should check the date for your 'mysql.h' file and
'libmysqlclient.a' library to verify that they are from the new
MySQL distribution. If not, please recompile your programs!
If you get some problems that the new mysqld server doesn't want to
start or that you can't connect without a password, check that you don't
have some old 'my.cnf' file from your old installation! You can
check this with: program-name --print-defaults. If this outputs
anything other than the program name, you have a active my.cnf
file that will may affect things!
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.
MySQL 3.23 supports tables of the new MyISAM type and
the old ISAM type. You don't have to convert your old tables to
use these with 3.23. By default, all new tables will be created with
type MyISAM (unless you start mysqld with the
--default-table-type=isam option. You can change an ISAM
table to a MyISAM table with ALTER TABLE or the Perl script
mysql_convert_table_format.
3.22 and 3.21 clients will work without any problems with a 3.23 server.
The following lists what you have to watch out for when upgrading to 3.23:
INNER and DELAYED are now reserved words.
FLOAT(X) is now a true floating point types.
DECIMAL(length,dec) the length argument no
longer includes a place for the sign or the decimal point.
TIME string must now be of one of the following formats:
[[[DAYS] [H]H:]MM:]SS[.fraction] or
[[[[[H]H]H]H]MM]SS[.fraction]
LIKE now compares strings using the same character
comparison rules as '='. If you require the old behavior, you
can compile MySQL with the CXXFLAGS=-DLIKE_CMP_TOUPPER
flag.
REGEXP is now case insensitive for normal (not binary) strings.
myisamchk for
MyISAM tables (.MYI) and isamchk for ISAM
(.ISM) tables.
mysqldumps to be compatible between
MySQL 3.22 and 3.23, you should not use the --opt or
--full option to mysqldump.
DATE_FORMAT() to make sure there is a '%'
before each format character.
mysql_fetch_fields_direct is now a function (it was a macro) and
it returns a pointer to a MYSQL_FIELD instead of a
MYSQL_FIELD.
mysql_num_fields() can no longer be used on a MYSQL* object (it's
now a function that takes MYSQL_RES* as an argument. You should now
use mysql_field_count() instead.
MySQL 3.22, the output of SELECT DISTINCT ... was
almost always sorted. In 3.23, you must use GROUP BY or
ORDER BY to obtain sorted output.
SUM() now returns NULL, instead of 0, if there is no matching
rows. This is according to ANSI SQL.
CASE, THEN, WHEN, ELSE and END
Nothing that affects compatibility has changed between 3.21 and 3.22. The
only pitfall is that new tables that are created with DATE type
columns will use the new way to store the date. You can't access these new
fields from an old version of mysqld.
After installing MySQL 3.22, you should start the new server and
then run the mysql_fix_privilege_tables script. This will add the new
privileges that you need to use the GRANT command. If you forget
this, you will get Access denied when you try to use ALTER
TABLE, CREATE INDEX or DROP INDEX. If your MySQL root
user requires a password, you should give this as an argument to
mysql_fix_privilege_tables.
The C API interface to mysql_real_connect() has changed. If you have
an old client program that calls this function, you must place a 0 for
the new db argument (or recode the client to send the db
element for faster connections). You must also call mysql_init()
before calling mysql_real_connect()! This change was done to allow
the new mysql_options() function to save options in the MYSQL
handler structure.
If you are running a version older than 3.20.28 and want to switch to 3.21.x, you need to do the following:
You can start the mysqld 3.21 server with safe_mysqld
--old-protocol to use it with clients from the 3.20 distribution.
In this case, the new client function mysql_errno() will not
return any server error, only CR_UNKNOWN_ERROR, (but it
works for client errors) and the server uses the old password() checking
rather than the new one.
If you are NOT using the --old-protocol option to
mysqld, you will need to make the following changes:
scripts/add_long_password must be run to convert the
Password field in the mysql.user table to CHAR(16).
mysql.user table (to get 62-bit
rather than 31-bit passwords).
MySQL 3.20.28 and above can handle the new user table format
without affecting clients. If you have a MySQL version earlier than
3.20.28, passwords will no longer work with it if you convert the user
table. So to be safe, you should first upgrade to at least 3.20.28 and then
upgrade to 3.21.x.
The new client code works with a 3.20.x mysqld server, so
if you experience problems with 3.21.x, you can use the old 3.20.x server
without having to recompile the clients again.
If you are not using the --old-protocol option to mysqld,
old clients will issue the error message:
ERROR: Protocol mismatch. Server Version = 10 Client Version = 9
The new Perl DBI/DBD interface also supports the old
mysqlperl interface. The only change you have to make if you use
mysqlperl is to change the arguments to the connect() function.
The new arguments are: host, database, user,
password (the user and password arguments have changed
places).
See section 20.5.2 The DBI interface.
The following changes may affect queries in old applications:
HAVING must now be specified before any ORDER BY clause.
LOCATE() have been swapped.
DATE,
TIME and TIMESTAMP.
If you are using MySQL 3.23, you can copy the .frm, the
.MYI and the .MYD files between different architectures
that support the same floating point format. (MySQL takes care
of any byte swapping issues).
The MySQL ISAM data '*.ISD' and the index files
'*.ISM' files) are architecture-dependent and in some case
OS-dependent. If you want to move your applications to another machine
that has a different architecture or OS than your current machine, you
should not try to move a database by simply copying the files to the
other machine. Use mysqldump instead.
By default, mysqldump will create a file full of SQL statements.
You can then transfer the file to the other machine and feed it as input
to the mysql client.
Try mysqldump --help to see what options are available.
If you are moving the data to a newer version of MySQL, you should use
mysqldump --opt with the newer version to get a fast, compact dump.
The easiest (although not the fastest) way to move a database between two machines is to run the following commands on the machine on which the database is located:
shell> mysqladmin -h 'other hostname' create db_name
shell> mysqldump --opt db_name \
| mysql -h 'other hostname' db_name
If you want to copy a database from a remote machine over a slow network, you can use:
shell> mysqladmin create db_name
shell> mysqldump -h 'other hostname' --opt --compress db_name \
| mysql db_name
You can also store the result in a file, then transfer the file to the target machine and load the file into the database there. For example, you can dump a database to a file on the source machine like this:
shell> mysqldump --quick db_name | gzip > db_name.contents.gz
(The file created in this example is compressed.) Transfer the file containing the database contents to the target machine and run these commands there:
shell> mysqladmin create db_name shell> gunzip < db_name.contents.gz | mysql db_name
You can also use mysqldump and mysqlimport to accomplish
the database transfer.
For big tables, this is much faster than simply using mysqldump.
In the commands shown below, DUMPDIR represents the full pathname
of the directory you use to store the output from mysqldump.
First, create the directory for the output files and dump the database:
shell> mkdir DUMPDIR shell> mysqldump --tab=DUMPDIR db_name
Then transfer the files in the DUMPDIR directory to some corresponding
directory on the target machine and load the files into MySQL
there:
shell> mysqladmin create db_name # create database shell> cat DUMPDIR/*.sql | mysql db_name # create tables in database shell> mysqlimport db_name DUMPDIR/*.txt # load data into tables
Also, don't forget to copy the mysql database, since that's where the
grant tables (user, db, host) are stored. You may have
to run commands as the MySQL root user on the new machine
until you have the mysql database in place.
After you import the mysql database on the new machine, execute
mysqladmin flush-privileges so that the server reloads the grant table
information.
MySQL includes some extensions that you probably will not find in
other SQL databases. Be warned that if you use them, your code will not be
portable to other SQL servers. In some cases, you can write code that
includes MySQL extensions, but is still portable, by using comments
of the form /*! ... */. In this case, MySQL will parse and
execute the code within the comment as it would any other MySQL
statement, but other SQL servers will ignore the extensions. For example:
SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ...
If you add a version number after the '!', the syntax will only be
executed if the MySQL version is equal or newer than the used
version number:
CREATE /*!32302 TEMPORARY */ TABLE (a int);
The above means that if you have 3.23.02 or newer, then MySQL will use
the TEMPORARY keyword.
MySQL extensions are listed below:
MEDIUMINT, SET, ENUM and the
different BLOB and TEXT types.
AUTO_INCREMENT, BINARY,
UNSIGNED and ZEROFILL.
BINARY attribute or use the BINARY cast, which causes
comparisons to be done according to the ASCII order used on the
MySQL server host.
db_name.tbl_name syntax. Some SQL servers provide
the same functionality but call this User space.
MySQL dosen't support tablespaces like in:
create table ralph.my_table...IN my_tablespace.
LIKE is allowed on numeric columns.
INTO OUTFILE and STRAIGHT_JOIN in a SELECT
statement. See section 7.12 SELECT syntax.
SQL_SMALL_RESULT option in a SELECT statement.
EXPLAIN SELECT to get a description on how tables are joined.
INDEX or KEY in a CREATE TABLE
statement. See section 7.7 CREATE TABLE syntax.
TEMPORARY or IF NOT EXISTS with CREATE TABLE.
COUNT(DISTINCT list) where 'list' is more than one element.
CHANGE col_name, DROP col_name or DROP INDEX
in an ALTER TABLE statement. See section 7.8 ALTER TABLE syntax.
IGNORE in an ALTER TABLE statement.
ADD, ALTER, DROP or CHANGE
clauses in an ALTER TABLE statement.
DROP TABLE with the keywords IF EXISTS.
DROP TABLE statement.
LIMIT clause of the DELETE statement.
DELAYED clause of the INSERT and REPLACE
statements.
LOW_PRIORITY clause of the INSERT, REPLACE,
DELETE and UPDATE statements.
LOAD DATA INFILE. In many cases, this syntax is compatible with
Oracle's LOAD DATA INFILE. See section 7.16 LOAD DATA INFILE syntax.
OPTIMIZE TABLE statement.
See section 7.9 OPTIMIZE TABLE syntax.
SHOW statement.
See section 7.21 SHOW syntax (Get information about tables, columns,...).
SET OPTION statement. See section 7.25 SET OPTION syntax.
GROUP BY part.
This gives better performance for some very specific, but quite normal
queries.
See section 7.4.13 Functions for use with GROUP BY clauses.
|| and && operators to mean
logical OR and AND, as in the C programming language. In MySQL,
|| and OR are synonyms, as are && and AND.
Because of this nice syntax, MySQL doesn't support
the ANSI SQL || operator for string concatenation; use
CONCAT() instead. Since CONCAT() takes any number
of arguments, it's easy to convert use of the || operator to
MySQL.
CREATE DATABASE or DROP DATABASE.
See section 7.5 CREATE DATABASE syntax.
% operator is a synonym for MOD(). That is,
N % M is equivalent to MOD(N,M). % is supported
for C programmers and for compatibility with PostgreSQL.
=, <>, <= ,<, >=,>,
<<, >>, <=>, AND, OR or LIKE
operators may be used in column comparisons to the left of the
FROM in SELECT statements. For example:
mysql> SELECT col1=1 AND col2=2 FROM tbl_name;
LAST_INSERT_ID() function.
See section 20.4.29 mysql_insert_id().
REGEXP and NOT REGEXP extended regular expression
operators.
CONCAT() or CHAR() with one argument or more than two
arguments. (In MySQL, these functions can take any number of
arguments.)
BIT_COUNT(), CASE, ELT(),
FROM_DAYS(), FORMAT(), IF(), PASSWORD(),
ENCRYPT(), md5(), ENCODE(), DECODE(),
PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(), or
WEEKDAY() functions.
TRIM() to trim substrings. ANSI SQL only supports removal
of single characters.
GROUP BY functions STD(), BIT_OR() and
BIT_AND().
REPLACE instead of DELETE + INSERT.
See section 7.15 REPLACE syntax.
FLUSH flush_option statement.
:=:
SELECT @a:=SUM(total),@b=COUNT(*),@a/@b AS avg FROM test_table; SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
If you start mysqld with the --ansi option, the following behaviour
of MySQL changes.
|| is string concatenation instead of OR.
" will be a identifier quote character (like the MySQL '
quote character) and not a string quote character.
REAL will be a synonym for FLOAT instead of a synonym of
DOUBLE.
We try to make MySQL follow the ANSI SQL standard and the ODBC SQL standard, but in some cases MySQL does some things differently:
-- is only a comment if followed by a white space. See section 5.4.7 '--' as the start of a comment.
VARCHAR columns, trailing spaces are removed when the value is
stored. See section E Known errors and design deficiencies in MySQL.
CHAR columns are silently changed to VARCHAR
columns. See section 7.7.1 Silent column specification changes.
REVOKE to revoke privileges for
a table. See section 7.26 GRANT and REVOKE syntax.
The following functionality is missing in the current version of MySQL. For a prioritized list indicating when new extensions may be added to MySQL, you should consult the online MySQL TODO list. That is the latest version of the TODO list in this manual. See section F List of things we want to add to MySQL in the future (The TODO).
The following will not yet work in MySQL:
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2); SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
However, in many cases you can rewrite the query without a sub select:
SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id; SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL
For more complicated subqueries you can often create temporary tables
to hold the subquery. In some cases, however this option will not
work. The most frequently encountered of these cases arises with
DELETE statements, for which standard SQL does not support joins
(except in sub-selects). For this situation there are two options
available until subqueries are supported by MySQL.
The first option is to use a procedural programming language (such as
Perl or PHP) to submit a SELECT query to obtain the primary keys
for the records to be deleted, and then use these values to construct
the DELETE statement (DELETE FROM ... WHERE ... IN (key1,
key2, ...)).
The second option is to use interactive SQL to contruct a set of
DELETE statements automatically, using the MySQL
extension CONCAT() (in lieu of the standard || operator).
For example:
SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', tab1.pkid, ';')
FROM tab1, tab2
WHERE tab1.col1 = tab2.col2;
You can place this query in a script file and redirect input from it to
the mysql command-line interpreter, piping its output back to a
second instance of the interpreter:
prompt> mysql --skip-column-names mydb < myscript.sql | mysql mydb
MySQL only supports INSERT ... SELECT ... and
REPLACE ... SELECT ... Independent sub-selects will be probably
be available in 3.24.0. You can now use the function IN() in
other contexts, however.
SELECT INTO TABLE
MySQL doesn't yet support the Oracle SQL extension:
SELECT ... INTO TABLE .... MySQL supports instead the
ANSI SQL syntax INSERT INTO ... SELECT ..., which is basically
the same thing.
Alternatively, you can use SELECT INTO OUTFILE... or CREATE
TABLE ... SELECT to solve your problem.
Transactions are not supported. MySQL shortly will support atomic
operations, which are like transactions without rollback. With atomic
operations, you can execute a group of INSERT/SELECT/whatever
commands and be guaranteed that no other thread will interfere. In this
context, you won't usually need rollback. Currently, you can prevent
interference from other threads by using the LOCK TABLES and
UNLOCK TABLES commands.
See section 7.24 LOCK TABLES/UNLOCK TABLES syntax.
A stored procedure is a set of SQL commands that can be compiled and stored in the server. Once this has been done, clients don't need to keep reissuing the entire query but can refer to the stored procedure. This provides better performance because the query has to be parsed only once and less information needs to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server.
A trigger is a stored procedure that is invoked when a particular event occurs. For example, you can install a stored procedure that is triggered each time a record is deleted from a transaction table and that automatically deletes the corresponding customer from a customer table when all his transactions are deleted.
The planned update language will be able to handle stored procedures, but without triggers. Triggers usually slow down everything, even queries for which they are not needed.
To see when MySQL might get stored procedures, see section F List of things we want to add to MySQL in the future (The TODO).
Note that foreign keys in SQL are not used to join tables, but are used
mostly for checking referential integrity. If you want to get results from
multiple tables from a SELECT statement, you do this by joining
tables!
SELECT * from table1,table2 where table1.id = table2.id;
See section 7.13 JOIN syntax. See section 8.3.5 Using foreign keys.
The FOREIGN KEY syntax in MySQL exists only for compatibility
with other SQL vendors' CREATE TABLE commands; it doesn't do
anything. The FOREIGN KEY syntax without ON DELETE ... is
mostly used for documentation purposes. Some ODBC applications may use this
to produce automatic WHERE clauses, but this is usually easy to
override. FOREIGN KEY is sometimes used as a constraint check, but
this check is unnecessary in practice if rows are inserted into the tables in
the right order. MySQL only supports these clauses because some
applications require them to exist (regardless of whether or not they
work!).
In MySQL, you can work around the problem of ON DELETE
... not being implemented by adding the appropriate DELETE statement to
an application when you delete records from a table that has a foreign key.
In practice this is as quick (in some cases quicker) and much more portable
than using foreign keys.
In the near future we will extend the FOREIGN KEY implementation so
that at least the information will be saved in the table specification file
and may be retrieved by mysqldump and ODBC.
There are so many problems with FOREIGN KEYs that we don't
know where to start:
INSERT and UPDATE statements,
and in this case almost all FOREIGN KEY checks are useless because you
usually insert records in the right tables in the right order, anyway.
The only nice aspect of FOREIGN KEY is that it gives ODBC and some
other client programs the ability to see how a table is connected and to use
this to show connection diagrams and to help in building applicatons.
MySQL will soon store FOREIGN KEY definitions so that
a client can ask for and receive an answer how the original connection was
made. The current '.frm' file format does not have any place for it.
MySQL doesn't support views, but this is on the TODO.
Some other SQL databases use '--' to start comments. MySQL
has '#' as the start comment character, even if the mysql
command line tool removes all lines that start with '--'.
You can also use the C comment style /* this is a comment */ with
MySQL.
See section 7.29 Comment syntax.
MySQL 3.23.3 and above supports the '--' comment style
only if the comment is followed by a space. This is because this
degenerate comment style has caused many problems with automatically
generated SQL queries that have used something like the following code,
where we automatically insert the value of the payment for
!payment!:
UPDATE tbl_name SET credit=credit-!payment!
What do you think will happen when the value of payment is negative?
Because 1--1 is legal in SQL, we think it is terrible that
'--' means start comment.
In MySQL 3.23 you can however use: 1-- This is a comment
The following discussing only concerns you if you are running an MySQL version earlier than 3.23:
If you have a SQL program in a text file that contains '--' comments you should use:
shell> replace " --" " #" < text-file-with-funny-comments.sql \
| mysql database
instead of the usual:
shell> mysql database < text-file-with-funny-comments.sql
You can also edit the command file "in place" to change the '--' comments to '#' comments:
shell> replace " --" " #" -- text-file-with-funny-comments.sql
Change them back with this command:
shell> replace " #" " --" -- text-file-with-funny-comments.sql
Entry level SQL92. ODBC level 0-2.
COMMIT/ROLLBACK
MySQL doesn't support COMMIT-ROLLBACK. The problem is
that handling COMMIT-ROLLBACK efficiently would require a
completely different table layout than MySQL uses today.
MySQL would also need extra threads that do automatic cleanups on
the tables and the disk usage would be much higher. This would make
MySQL about 2-4 times slower than it is today. MySQL is
much faster than almost all other SQL databases (typically at least 2-3 times
faster). One of the reasons for this is the lack of
COMMIT-ROLLBACK.
For the moment, we are much more for implementing the SQL server
language (something like stored procedures). With this you would very
seldom really need COMMIT-ROLLBACK. This would also give much
better performance.
Loops that need transactions normally can be coded with the help of
LOCK TABLES, and you don't need cursors when you can update records
on the fly.
We have transactions and cursors on the TODO but not quite prioritized. If
we implement these, it will be as an option to CREATE TABLE. That
means that COMMIT-ROLLBACK will work only on those tables,
so that a speed penalty will be imposed on those table only.
We at TcX have a greater need for a real fast database than a 100% general database. Whenever we find a way to implement these features without any speed loss, we will probably do it. For the moment, there are many more important things to do. Check the TODO for how we prioritize things at the moment. (Customers with higher levels of support can alter this, so things may be reprioritized.)
The current problem is actually ROLLBACK. Without ROLLBACK, you
can do any kind of COMMIT action with LOCK TABLES. To support
ROLLBACK, MySQL would have to be changed to store all old
records that were updated and revert everything back to the starting point if
ROLLBACK was issued. For simple cases, this isn't that hard to do (the
current isamlog could be used for this purpose), but it would be much
more difficult to implement ROLLBACK for ALTER/DROP/CREATE
TABLE.
To avoid using ROLLBACK, you can use the following strategy:
LOCK TABLES ... to lock all the tables you want to access.
UNLOCK TABLES to release your locks.
This is usually a much faster method than using transactions with possible
ROLLBACKs, although not always. The only situation this solution
doesn't handle is when someone kills the threads in the middle of an
update. In this case, all locks will be released but some of the updates may
not have been executed.
You can also use functions to update records in a single operation. You can get a very efficient application by using the following techniques:
For example, when we are doing updates to some customer information, we
update only the customer data that have changed and test only that none of
the changed data, or data that depend on the changed data, have changed
compared to the original row. The test for changed data is done with the
WHERE clause in the UPDATE statement. If the record wasn't
updated, we give the client a message: "Some of the data you have changed
have been changed by another user". Then we show the old row versus the new
row in a window, so the user can decide which version of the customer record
he should use.
This gives us something that is similar to "column locking" but is actually
even better, because we only update some of the columns, using values that
are relative to their current values. This means that typical UPDATE
statements look something like these:
UPDATE tablename SET pay_back=pay_back+'relative change';
UPDATE customer
SET
customer_date='current_date',
address='new address',
phone='new phone',
money_he_owes_us=money_he_owes_us+'new_money'
WHERE
customer_id=id AND address='old address' AND phone='old phone';
As you can see, this is very efficient and works even if another client has
changed the values in the pay_back or money_he_owes_us columns.
In many cases, users have wanted ROLLBACK and/or LOCK
TABLES for the purpose of managing unique identifiers for some tables. This
can be handled much more efficiently by using an AUTO_INCREMENT column
and either the SQL function LAST_INSERT_ID() or the C API function
mysql_insert_id(). See section 20.4.29 mysql_insert_id().
At TcX, we have never had any need for row-level locking because we have always been able to code around it. Some cases really need row locking, but they are very few. If you want row-level locking, you can use a flag column in the table and do something like this:
UPDATE tbl_name SET row_flag=1 WHERE id=ID;
MySQL returns 1 for the number of affected rows if the row was
found and row_flag wasn't already 1 in the original row.
You can think of it as MySQL changed the above query to:
UPDATE tbl_name SET row_flag=1 WHERE id=ID and row_flag <> 1;
MySQL has an advanced but non-standard security/privilege system. This section describes how it works.
Anyone using MySQL on a computer connected to the Internet should read this chapter to avoid mistakes people do.
Under "security" we mean that our site, not only MySQL is protected against all types of applicable attacks: eavesdropping, altering, playback and Denial of Service. We do not cover all aspects of availability and fault tolerance here.
There are some security logic in MySQL: Access control lists (ACL-s) and SSL encrypted connections but much more is depending on overall usage of MySQL. Also most of this chapter isn't MySQL dependant at all. Same rules apply for most applications.
When you running a site, designing software or just doing something with MySQL then try to follow these rules:
GRANT/REVOKE commands
are for restricting access to MySQL. Do not grant anyone for
more than is must. Never grant all hosts to do something.
Checklist:
mysql -u root. If you granted a connection without asking password,
then this is bad.
SHOW GRANTS and check who is having access and to what.
MD5() or other one-way
hashing function.
; DROP ALL
DATABASES ; into form?
Checklist:
mysql_escape() API call.
escape and quote modifiers (?) for query streams.
tcpdump -l -i eth0 -w - src or dst port 3306 | strings(This works under linux and should work with small modifications under another systems). Warning: If you do not see data this doesn't actually always mean that it is encrypted. If you need high security you should consult with security expert.
When you connect to a MySQL server, you should normally use a password. The password is not transmitted in clear text over the connection.
All other information is transferred as text that can be read by anyone that
is able to watch the connection. If you are concerned about this, you can
use the compressed protocol (in MySQL 3.22 and above) to make things
much harder. To make things even more secure you should install ssh
(see http://www.cs.hut.fi/ssh). With this, you can get an encrypted
TCP/IP connection between a MySQL server and a MySQL
client.
To make a MySQL system secure, you should strongly consider the following suggestions:
mysql -u other_user db_name if
other_user has no password. It is common behavior with client/server
applications that the client may specify any user name. You can change the
password of all users by editing the mysql_install_db script before
you run it, or only the password for the MySQL root user like
this:
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
WHERE user='root';
mysql> FLUSH PRIVILEGES;
root user.
mysqld can be run as any user. You can also create a new Unix user
mysql to make everything even more secure. If you run mysqld
as another Unix user, you don't need to change the root user name in
the user table, because MySQL user names have nothing to do
with Unix user names. You can edit the mysql.server script to start
mysqld as another Unix user. Normally this is done with the su
command. For more details, see section 18.8 How to run MySQL as a normal user.
root user in the mysql.server
script, make sure this script is readable only by root.
mysqld runs as is the only user with
read/write privileges in the database directories.
mysqladmin processlist shows the text of the currently executing
queries, so any user who is allowed to execute that command might be able to
see if another user issues an UPDATE user SET
password=PASSWORD('not_secure') query.
mysqld saves an extra connection for users who have the
process privilege, so that a MySQL root user can log
in and check things even if all normal connections are in use.
mysqld daemon! To make this a bit safer, all files generated with
SELECT ... INTO OUTFILE are readable to everyone, and you can't
overwrite existing files.
The file privilege may also be used to read any file accessible
to the Unix user that the server runs as. This could be abused, for example,
by using LOAD DATA to load '/etc/passwd' into a table, which
can then be read with SELECT.
--secure option to
mysqld should make hostnames safe. In any case, you should be very
careful about using hostname values that contain wildcards!
The following mysqld options affect security:
--secure
gethostbyname() system call are checked to
make sure they resolve back to the original hostname. This makes it harder
for someone on the outside to get access by simulating another host. This
option also adds some sanity checks of hostnames. The option is turned off
by default in MySQL 3.21 since it sometimes takes a long time to
perform backward resolutions. MySQL 3.22 caches hostnames and has
this option enabled by default.
--skip-grant-tables
mysqladmin
reload.)
--skip-name-resolve
Host column values in the grant
tables must be IP numbers or localhost.
--skip-networking
mysqld must be made via Unix sockets. This option is unsuitable for
systems that use MIT-pthreads, because the MIT-pthreads package doesn't
support Unix sockets.
The primary function of the MySQL privilege system is to authenticate a user connecting from a given host, and to associate that user with select, insert, update and delete privileges on a database.
Additional functionality includes the ability to have an anonymous user and
to grant privileges for MySQL-specific functions such as LOAD
DATA INFILE and administrative operations.
There are several distinctions between the way user names and passwords are used by MySQL, and the way they are used by Unix or Windows:
-u or
--user options. This means that you can't make a database secure in
any way unless all MySQL user names have passwords. Anyone may
attempt to connect to the server using any name, and they will succeed if
they specify any name that doesn't have a password.
PASSWORD() and ENCRYPT() functions in section 7.4.12 Miscellaneous functions.
MySQL client programs generally require that you specify connection
parameters when you want to access a MySQL server: the host you want
to connect to, your user name and your password. For example, the
mysql client can be started like this (optional arguments are enclosed
between '[' and ']'):
shell> mysql [-h host_name] [-u user_name] [-pyour_pass]
Alternate forms of the -h, -u and -p options are
--host=host_name, --user=user_name and
--password=your_pass. Note that there is no space between
-p or --password= and the password following it.
Note: Specifing a password on the command line is not secure!
Any user on your system may then find out your password by typing a command
like: ps auxww. See section 4.15.4 Option files.
mysql uses default values for connection parameters that are missing
from the command line:
localhost.
-p is missing.
Thus, for a Unix user joe, the following commands are equivalent:
shell> mysql -h localhost -u joe shell> mysql -h localhost shell> mysql -u joe shell> mysql
Other MySQL clients behave similarly.
On Unix systems, you can specify different default values to be used when you make a connection, so that you need not enter them on the command line each time you invoke a client program. This can be done in a couple of ways:
[client] section of the
'.my.cnf' configuration file in your home directory. The relevant
section of the file might look like this:
[client] host=host_name user=user_name password=your_passSee section 4.15.4 Option files.
MYSQL_HOST. The MySQL user name can be
specified using USER (this is for Windows only). The password can be
specified using MYSQL_PWD (but this is insecure; see next section).
If connection parameters are specified in multiple ways, values specified on the command line take precedence over values specified in configuration files and environment variables, and values in configuration files take precedence over values in environment variables.
It is inadvisable to specify your password in a way that exposes it to discovery by other users. The methods you can use to specify your password when you run client programs are listed below, along with an assessment of the risks of each method:
-pyour_pass or --password=your_pass option on the command
line. This is convenient but insecure, since your password becomes visible
to system status programs (such as ps) that may be invoked by other
users to display command lines. (MySQL clients typically overwrite
the command line argument with zeroes during their initialization sequence,
but there is still a brief interval during which the value is visible.)
-p or --password option (with no your_pass value
specified). In this case, the client program solicits the password from
the terminal:
shell> mysql -u user_name -p Enter password: ********The client echoes '*' characters to the terminal as you enter your password so that onlookers cannot see it. It is more secure to enter your password this way than to specify it on the command line because it is not visible to other users. However, this method of entering a password is suitable only for programs that you run interactively. If you want to invoke a client from a script that runs non-interactively, there is no opportunity to enter the password from the terminal.
[client] section of the '.my.cnf' file in your
home directory:
[client] password=your_passIf you store your password in '.my.cnf', the file should not be group or world readable or writable. Make sure the file's access mode is
400
or 600.
See section 4.15.4 Option files.
MYSQL_PWD environment variable, but
this method must be considered extremely insecure and should not be used.
Some versions of ps include an option to display the environment of
running processes; your password will be in plain sight for all to see if
you set MYSQL_PWD. Even on systems without such a version of
ps, it is unwise to assume there is no other method to observe process
environments.
All in all, the safest methods are to have the client program prompt for the password or to specify the password in a properly-protected '.my.cnf' file.
Privilege information is stored in the user, db, host,
tables_priv and columns_priv tables in the mysql
database (that is, in the database named mysql). The MySQL
server reads the contents of these tables when it starts up and under the
circumstances indicated in section 6.11 When privilege changes take effect.
The names used in this manual to refer to the privileges provided by MySQL are shown below, along with the table column name associated with each privilege in the grant tables and the context in which the privilege applies:
| Privilege | Column | Context |
| select | Select_priv | tables |
| insert | Insert_priv | tables |
| update | Update_priv | tables |
| delete | Delete_priv | tables |
| index | Index_priv | tables |
| alter | Alter_priv | tables |
| create | Create_priv | databases, tables or indexes |
| drop | Drop_priv | databases or tables |
| grant | Grant_priv | databases or tables |
| references | References_priv | databases or tables |
| reload | Reload_priv | server administration |
| shutdown | Shutdown_priv | server administration |
| process | Process_priv | server administration |
| file | File_priv | file access on server |
The select, insert, update and delete privileges allow you to perform operations on rows in existing tables in a database.
SELECT statements require the select privilege only if they
actually retrieve rows from a table. You can execute certain SELECT
statements even without permission to access any of the databases on the
server. For example, you could use the mysql client as a simple
calculator:
mysql> SELECT 1+1; mysql> SELECT PI()*2;
The index privilege allows you to create or drop (remove) indexes.
The alter privilege allows you to use ALTER TABLE.
The create and drop privileges allow you to create new databases and tables, or to drop (remove) existing databases and tables.
Note that if you grant the drop privilege for the mysql
database to a user, that user can drop the database in which the
MySQL access privileges are stored!
The grant privilege allows you to give to other users those privileges you yourself possess.
The file privilege gives you permission to read and write files on
the server using the LOAD DATA INFILE and SELECT ... INTO
OUTFILE statements. Any user to whom this privilege is granted can read or
write any file that the MySQL server can read or write.
The remaining privileges are used for administrative operations, which are
performed using the mysqladmin program. The table below shows which
mysqladmin commands each administrative privilege allows you to
execute:
| Privilege | Commands permitted to privilege holders |
| reload | reload, refresh,
flush-privileges,
flush-hosts, flush-logs, flush-tables
|
| shutdown | shutdown
|
| process | processlist, kill
|
The reload command tells the server to reread the grant tables. The
refresh command flushes all tables and opens and closes the log
files. flush-privileges is a synonym for reload. The other
flush-* commands perform functions similar to refresh but are
more limited in scope, and may be preferable in some instances. For example,
if you want to flush just the log files, flush-logs is a better choice
than refresh.
The shutdown command shuts down the server.
The processlist command displays information about the threads
executing within the server. The kill command kills server threads.
You can always display or kill your own threads, but you need the
process privilege to display or kill threads initiated by other
users.
It is a good idea in general to grant privileges only to those users who need them, but you should exercise particular caution in granting certain privileges:
SELECT.
mysql database can be used to change passwords and
other access privilege information. (Passwords are stored encrypted, so a
malicious user cannot simply read them. However, with sufficient privileges,
that same user can replace a password with a different one.)
There are some things that you cannot do with the MySQL privilege system:
The MySQL privilege system ensures that all users may do exactly the things that they are supposed to be allowed to do. When you connect to a MySQL server, your identity is determined by the host from which you connect and the user name you specify. The system grants privileges according to your identity and what you want to do.
MySQL considers both your hostname and user name in identifying you
because there is little reason to assume that a given user name belongs to
the same person everywhere on the Internet. For example, the user
bill who connects from whitehouse.gov need not be the same
person as the user bill who connects from microsoft.com.
MySQL handles this by allowing you to distinguish users on different
hosts that happen to have the same name: you can grant bill one set
of privileges for connections from whitehouse.gov, and a different set
of privileges for connections from microsoft.com.
MySQL access control involves two stages:
The server uses the user, db and host tables in the
mysql database at both stages of access control. The fields in these
grant tables are shown below:
| Table name | user | db | host
|
| Scope fields | Host | Host | Host
|
User | Db | Db
| |
Password | User | ||
| Privilege fields | Select_priv | Select_priv | Select_priv
|
Insert_priv | Insert_priv | Insert_priv
| |
Update_priv | Update_priv | Update_priv
| |
Delete_priv | Delete_priv | Delete_priv
| |
Index_priv | Index_priv | Index_priv
| |
Alter_priv | Alter_priv | Alter_priv
| |
Create_priv | Create_priv | Create_priv
| |
Drop_priv | Drop_priv | Drop_priv
| |
Grant_priv | Grant_priv | Grant_priv
| |
References_priv | |||
Reload_priv | |||
Shutdown_priv | |||
Process_priv | |||
File_priv |
For the second stage of access control (request verification), the server
may, if the request involves tables, additionally consult the
tables_priv and columns_priv tables. The fields in these
tables are shown below:
| Table name | tables_priv | columns_priv
|
| Scope fields | Host | Host
|
Db | Db
| |
User | User
| |
Table_name | Table_name
| |
Column_name
| ||
| Privilege fields | Table_priv | Column_priv
|
Column_priv | ||
| Other fields | Timestamp | Timestamp
|
Grantor |
Each grant table contains scope fields and privilege fields.
Scope fields determine the scope of each entry in the tables, i.e., the
context in which the entry applies. For example, a user table entry
with Host and User values of 'thomas.loc.gov' and
'bob' would be used for authenticating connections made to the server
by bob from the host thomas.loc.gov. Similarly, a db
table entry with Host, User and Db fields of
'thomas.loc.gov', 'bob' and 'reports' would be used when
bob connects from the host thomas.loc.gov to access the
reports database. The tables_priv and columns_priv
tables contain scope fields indicating tables or table/column combinations
to which each entry applies.
For access-checking purposes, comparisons of Host values are
case insensitive. User, Password, Db and
Table_name values are case sensitive.
Column_name values are case insensitive in MySQL 3.22.12
or later.
Privilege fields indicate the privileges granted by a table entry, that is, what operations can be performed. The server combines the information in the various grant tables to form a complete description of a user's privileges. The rules used to do this are described in section 6.10 Access control, stage 2: Request verification.
Scope fields are strings, declared as shown below; the default value for each is the empty string:
| Field name | Type | |
Host | CHAR(60)
| |
User | CHAR(16)
| |
Password | CHAR(16)
| |
Db | CHAR(64) | (CHAR(60) for the
tables_priv and columns_priv tables)
|
In the user, db and host tables,
all privilege fields are declared as ENUM('N','Y') -- each can have a
value of 'N' or 'Y', and the default value is 'N'.
In the tables_priv and columns_priv tables, the privilege
fields are declared as SET fields:
| Table name | Field name | Possible set elements |
tables_priv | Table_priv | 'Select', 'Insert',
'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
|
tables_priv | Column_priv | 'Select', 'Insert',
'Update', 'References'
|
columns_priv | Column_priv | 'Select', 'Insert',
'Update', 'References'
|
Briefly, the server uses the grant tables like this:
user table scope fields determine whether to allow or reject
incoming connections. For allowed connections, the privilege fields indicate
the user's global (superuser) privileges.
db and host tables are used together:
db table scope fields determine which users can access which
databases from which hosts. The privilege fields determine which operations
are allowed.
host table is used as an extension of the db table when you
want a given db table entry to apply to several hosts. For example,
if you want a user to be able to use a database from several hosts in
your network, leave the Host value empty in the user's db table
entry, then populate the host table with an entry for each of those
hosts. This mechanism is described more detail in section 6.10 Access control, stage 2: Request verification.
tables_priv and columns_priv tables are similar to
the db table, but are more fine-grained: they apply at the
table and column level rather than at the database level.
Note that administrative privileges (reload, shutdown,
etc.) are specified only in the user table. This is because
administrative operations are operations on the server itself and are not
database-specific, so there is no reason to list such privileges in the
other grant tables. In fact, only the user table need
be consulted to determine whether or not you can perform an administrative
operation.
The file privilege is specified only in the user table, too.
It is not an administrative privilege as such, but your ability to read or
write files on the server host is independent of the database you are
accessing.
The mysqld server reads the contents of the grant tables once, when it
starts up. Changes to the grant tables take effect as indicated in
section 6.11 When privilege changes take effect.
When you modify the contents of the grant tables, it is a good idea to make
sure that your changes set up privileges the way you want. For help in
diagnosing problems, see section 6.15 Causes of Access denied errors. For advice on security issues,
section 6.2 How to make MySQL secure against crackers.
A useful
diagnostic tool is the mysqlaccess script, which Yves Carlier has
provided for the MySQL distribution. Invoke mysqlaccess with
the --help option to find out how it works.
Note that mysqlaccess checks access using only the user,
db and host tables. It does not check table- or column-level
privileges.
When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether or not you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, then enters stage 2 and waits for requests.
Your identity is based on two pieces of information:
Identity checking is performed using the three user table scope fields
(Host, User and Password). The server accepts the
connection only if a user table entry matches your hostname and user
name, and you supply the correct password.
Values in the user table scope fields may be specified as follows:
Host value may be a hostname or an IP number, or 'localhost'
to indicate the local host.
Host
field.
Host value of '%' matches any hostname. A blank Host
value is equivalent to '%'. Note that these values match any
host that can create a connection to your server!
User field, but you can
specify a blank value, which matches any name. If the user table
entry that matches an incoming connection has a blank user name, the user is
considered to be the anonymous user (the user with no name), rather than the
name that the client actually specified. This means that a blank user name
is used for all further access checking for the duration of the connection
(that is, during stage 2).
Password field can be blank. This does not mean that any password
matches, it means the user must connect without specifying a password.
Non-blank Password values represent encrypted passwords.
MySQL does not store passwords in plaintext form for anyone to see.
Rather, the password supplied by a user who is attempting to connect is
encrypted (using the PASSWORD() function) and compared to the
already-encrypted version stored in the user table. If they match,
the password is correct.
The examples below show how various combinations of Host and
User values in user table entries apply to incoming
connections:
Host value | User value | Connections matched by entry |
'thomas.loc.gov' | 'fred' | fred, connecting from thomas.loc.gov
|
'thomas.loc.gov' | " | Any user, connecting from thomas.loc.gov
|
'%' | 'fred' | fred, connecting from any host
|
'%' | " | Any user, connecting from any host |
'%.loc.gov' | 'fred' | fred, connecting from any host in the loc.gov domain
|
'x.y.%' | 'fred' | fred, connecting from x.y.net, x.y.com,x.y.edu, etc. (this is probably not useful)
|
'144.155.166.177' | 'fred' | fred, connecting from the host with IP address 144.155.166.177
|
'144.155.166.%' | 'fred' | fred, connecting from any host in the 144.155.166 class C subnet
|
Since you can use IP wildcard values in the Host field (e.g.,
'144.155.166.%' to match every host on a subnet), there is the
possibility that someone might try to exploit this capability by naming a
host 144.155.166.somewhere.com. To foil such attempts, MySQL
disallows matching on hostnames that start with digits and a dot. Thus, if
you have a host named something like 1.2.foo.com, its name will never
match the Host column of the grant tables. Only an IP number can
match an IP wildcard value.
An incoming connection may be matched by more than one entry in the
user table. For example, a connection from thomas.loc.gov by
fred would be matched by several of the entries just shown above. How
does the server choose which entry to use if more than one matches? The
server resolves this question by sorting the user table after reading
it at startup time, then looking through the entries in sorted order when a
user attempts to connect. The first matching entry is the one that is used.
user table sorting works as follows. Suppose the user table
looks like this:
+-----------+----------+- | Host | User | ... +-----------+----------+- | % | root | ... | % | jeffrey | ... | localhost | root | ... | localhost | | ... +-----------+----------+-
When the server reads in the table, it orders the entries with the
most-specific Host values first ('%' in the Host column
means "any host" and is least specific). Entries with the same Host
value are ordered with the most-specific User values first (a blank
User value means "any user" and is least specific). The resulting
sorted user table looks like this:
+-----------+----------+- | Host | User | ... +-----------+----------+- | localhost | root | ... | localhost | | ... | % | jeffrey | ... | % | root | ... +-----------+----------+-
When a connection is attempted, the server looks through the sorted entries
and uses the first match found. For a connection from localhost by
jeffrey, the entries with 'localhost' in the Host column
match first. Of those, the entry with the blank user name matches both the
connecting hostname and user name. (The '%'/'jeffrey' entry would
have matched, too, but it is not the first match in the table.)
Here is another example. Suppose the user table looks like this:
+----------------+----------+- | Host | User | ... +----------------+----------+- | % | jeffrey | ... | thomas.loc.gov | | ... +----------------+----------+-
The sorted table looks like this:
+----------------+----------+- | Host | User | ... +----------------+----------+- | thomas.loc.gov | | ... | % | jeffrey | ... +----------------+----------+-
A connection from thomas.loc.gov by jeffrey is matched by the
first entry, whereas a connection from whitehouse.gov by
jeffrey is matched by the second.
A common misconception is to think that for a given user name, all entries
that explicitly name that user will be used first when the server attempts to
find a match for the connection. This is simply not true. The previous
example illustrates this, where a connection from thomas.loc.gov by
jeffrey is first matched not by the entry containing 'jeffrey'
as the User field value, but by the entry with no user name!
If you have problems connecting to the server, print out the user
table and sort it by hand to see where the first match is being made.
Once you establish a connection, the server enters stage 2. For each request
that comes in on the connection, the server checks whether you have
sufficient privileges to perform it, based on the type of operation you wish
to perform. This is where the privilege fields in the grant tables come into
play. These privileges can come from any of the user, db,
host, tables_priv or columns_priv tables. The grant
tables are manipulated with GRANT and REVOKE commands.
See section 7.26 GRANT and REVOKE syntax. (You may find it helpful to refer to
section 6.8 How the privilege system works, which lists the fields present in each of the grant
tables.)
The user table grants privileges that are assigned to you on a global
basis and that apply no matter what the current database is. For example, if
the user table grants you the delete privilege, you can
delete rows from any database on the server host! In other words,
user table privileges are superuser privileges. It is wise to grant
privileges in the user table only to superusers such as server or
database administrators. For other users, you should leave the privileges
in the user table set to 'N' and grant privileges on a
database-specific basis only, using the db and host tables.
The db and host tables grant database-specific privileges.
Values in the scope fields may be specified as follows:
Host and
Db fields of either table.
'%' Host value in the db table means "any host." A
blank Host value in the db table means "consult the
host table for further information."
'%' or blank Host value in the host table means "any
host."
'%' or blank Db value in either table means "any database."
User value in either table matches the anonymous user.
The db and host tables are read in and sorted when the server
starts up (at the same time that it reads the user table). The
db table is sorted on the Host, Db and User scope
fields, and the host table is sorted on the Host and Db
scope fields. As with the user table, sorting puts the most-specific
values first and least-specific values last, and when the server looks for
matching entries, it uses the first match that it finds.
The tables_priv and columns_priv tables grant table- and
column-specific privileges. Values in the scope fields may be specified as
follows:
Host field of either table.
'%' or blank Host value in either table means "any host."
Db, Table_name and Column_name fields cannot contain
wildcards or be blank in either table.
The tables_priv and columns_priv tables are sorted on
the Host, Db and User fields. This is similar to
db table sorting, although since only the Host field may
contain wildcards, the sorting is simpler.
The request verification process is described below. (If you are familiar with the access-checking source code, you will notice that the description here differs slightly from the algorithm used in the code. The description is equivalent to what the code actually does; it differs only to make the explanation simpler.)
For administrative requests (shutdown, reload, etc.), the
server checks only the user table entry, since that is the only table
that specifies administrative privileges. Access is granted if the entry
allows the requested operation and denied otherwise. For example, if you
want to execute mysqladmin shutdown but your user table entry
doesn't grant the shutdown privilege to you, access is denied
without even checking the db or host tables. (Since they
contain no Shutdown_priv column, there is no need to do so.)
For database-related requests (insert, update, etc.), the
server first checks the user's global (superuser) privileges by looking in
the user table entry. If the entry allows the requested operation,
access is granted. If the global privileges in the user table are
insufficient, the server determines the user's database-specific privileges
by checking the db and host tables:
db table for a match on the Host,
Db and User fields. Host and User are matched to
the connecting user's hostname and MySQL user name. The Db
field is matched to the database the user wants to access. If there is no
entry for the Host and User, access is denied.
db table entry and its Host field is
not blank, that entry defines the user's database-specific privileges.
db table entry's Host field is blank, it
signifies that the host table enumerates which hosts should be allowed
access to the database. In this case, a further lookup is done in the
host table to find a match on the Host and Db fields.
If no host table entry matches, access is denied. If there is a
match, the user's database-specific privileges are computed as the
intersection (not the union!) of the privileges in the db and
host table entries, i.e., the privileges that are 'Y' in both
entries. (This way you can grant general privileges in the db table
entry and then selectively restrict them on a host-by-host basis using the
host table entries.)
After determining the database-specific privileges granted by the db
and host table entries, the server adds them to the global privileges
granted by the user table. If the result allows the requested
operation, access is granted. Otherwise, the server checks the user's
table and column privileges in the tables_priv and columns_priv
tables and adds those to the user's privileges. Access is allowed or denied
based on the result.
Expressed in boolean terms, the preceding description of how a user's privileges are calculated may be summarized like this:
global privileges OR (database privileges AND host privileges) OR table privileges OR column privileges
It may not be apparent why, if the global user entry privileges are
initially found to be insufficient for the requested operation, the server
adds those privileges to the database-, table- and column-specific privileges
later. The reason is that a request might require more than one type of
privilege. For example, if you execute an INSERT ... SELECT
statement, you need both insert and select privileges.
Your privileges might be such that the user table entry grants one
privilege and the db table entry grants the other. In this case, you
have the necessary privileges to perform the request, but the server cannot
tell that from either table by itself; the privileges granted by both entries
must be combined.
The host table can be used to maintain a list of "secure" servers.
At TcX, the host table contains a list of all machines on the local
network. These are granted all privileges.
You can also use the host table to indicate hosts that are not
secure. Suppose you have a machine public.your.domain that is located
in a public area that you do not consider secure. You can allow access to
all hosts on your network except that machine by using host table
entries
like this:
+--------------------+----+- | Host | Db | ... +--------------------+----+- | public.your.domain | % | ... (all privileges set to 'N') | %.your.domain | % | ... (all privileges set to 'Y') +--------------------+----+-
Naturally, you should always test your entries in the grant tables (e.g.,
using mysqlaccess) to make sure your access privileges are actually
set up the way you think they are.
When mysqld starts, all grant table contents are read into memory and
become effective at that point.
Modifications to the grant tables that you perform using GRANT,
REVOKE, or SET PASSWORD are noticed by the server immediately.
If you modify the grant tables manually (using INSERT, UPDATE,
etc.), you should execute a FLUSH PRIVILEGES statement or run
mysqladmin flush-privileges to tell the server to reload the grant
tables. Otherwise your changes will have no effect until
you restart the server.
When the server notices that the grant tables have been changed, existing client connections are affected as follows:
USE db_name
command.
Global privilege changes and password changes take effect the next time the client connects.
After installing MySQL, you set up the initial access privileges by
running scripts/mysql_install_db.
See section 4.7.1 Quick installation overview.
The scripts/mysql_install_db script starts up the mysqld
server, then initializes the grant tables to contain the following set
of privileges:
root user is created as a superuser who can do
anything. Connections must be made from the local host.
Note:
The initial root password is empty, so anyone can connect as root
without a password and be granted all privileges.
'test' or starting with 'test_'. Connections must be
made from the local host. This means any local user can connect and be
treated as the anonymous user.
mysqladmin shutdown or mysqladmin processlist.
Note: The default privileges are different for Win32. See section 4.12.4 Running MySQL on Win32.
Since your installation is initially wide open, one of the first things you
should do is specify a password for the MySQL
root user. You can do this as follows (note that you specify the
password using the PASSWORD() function):
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password')
WHERE user='root';
mysql> FLUSH PRIVILEGES;
You can in MySQL 3.22 and above use the SET PASSWORD statement:
shell> mysql -u root mysql
mysql> SET PASSWORD FOR root=PASSWORD('new_password');
Another way to set the password is by using the mysqladmin command:
shell> mysqladmin -u root password new_password
Note that if you update the password in the user table directly using
the first method, you must tell the server to reread the grant tables (with
FLUSH PRIVILEGES), since the change will go unnoticed otherwise.
Once the root password has been set, thereafter you must supply that
password when you connect to the server as root.
You may wish to leave the root password blank so that you don't need
to specify it while you perform additional setup or testing, but be sure to
set it before using your installation for any real production work.
See the scripts/mysql_install_db script to see how it sets up
the default privileges. You can use this as a basis to see how to
add other users.
If you want the initial privileges to be different than those just described
above, you can modify mysql_install_db before you run it.
To recreate the grant tables completely, remove all the '*.frm',
'*.MYI' and '*.MYD' files in the directory containing the
mysql database. (This is the directory named 'mysql' under
the database directory, which is listed when you run mysqld
--help.) Then run the mysql_install_db script, possibly after
editing it first to have the privileges you want.
NOTE: For MySQL versions older than 3.22.10, you should NOT
delete the '*.frm' files. If you accidentally do this, you should
copy them back from your MySQL distribution before running
mysql_install_db.
You can add users two different ways: by using GRANT statements
or by manipulating the MySQL grant tables directly. The
preferred method is to use GRANT statements, because they are
more concise and less error-prone.
The examples below show how to use the mysql client to set up new
users. These examples assume that privileges are set up according to the
defaults described in the previous section. This means that to make changes,
you must be on the same machine where mysqld is running, you must
connect as the MySQL root user, and the root user must
have the insert privilege for the mysql database and the
reload administrative privilege. Also, if you have changed the
root user password, you must specify it for the mysql commands
below.
You can add new users by issuing GRANT statements:
shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
IDENTIFIED BY 'something' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"
IDENTIFIED BY 'something' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> GRANT USAGE ON *.* TO dummy@localhost;
These GRANT statements set up three new users:
monty
'something' to do so. Note that we must issue
GRANT statements for both monty@localhost and
monty@"%". If we don't add the entry with localhost, the
anonymous user entry for localhost that is created by
mysql_install_db will take precedence when we connect from the local
host, because it has a more specific Host field value and thuse comes
earlier in the user table sort order.
admin
localhost without a password and who is
granted the reload and process administrative privileges.
This allows the user to execute the mysqladmin reload,
mysqladmin refresh and mysqladmin flush-* commands, as well as
mysqladmin processlist . No database-related privileges are granted.
They can be granted later by issuing additional GRANT statements.
dummy
'N' -- the USAGE privilege
type allows you to set up a user with no privileges. It is assumed that you
will grant database-specific privileges later.
You can also add the same user access information directly by issuing
INSERT statements and then telling the server to reload the grant
tables:
shell> mysql --user=root mysql
mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('something'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')
mysql> INSERT INTO user VALUES('%','monty',PASSWORD('something'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')
mysql> INSERT INTO user SET Host='localhost',User='admin',
Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
VALUES('localhost','dummy',");
mysql> FLUSH PRIVILEGES;
Depending on your MySQL version, you may have to use a different
number of 'Y' values above (versions prior to 3.22.11 had fewer
privilege columns). For the admin user, the more readable extended
INSERT syntax that is available starting with 3.22.11 is used.
Note that to set up a superuser, you need only create a user table
entry with the privilege fields set to 'Y'. No db or
host table entries are necessary.
The privilege columns in the user table were not set explicitly in the
last INSERT statement (for the dummy user), so those columns
are assigned the default value of 'N'. This is the same thing that
GRANT USAGE does.
The following example adds a user custom who can connect from hosts
localhost, server.domain and whitehouse.gov. He wants
to access the bankaccount database only from localhost,
the expenses database only from whitehouse.gov and
the customer database from all three hosts. He wants
to use the password stupid from all three hosts.
To set up this user's privileges using GRANT statements, run these
commands:
shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON bankaccount.*
TO custom@localhost
IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON expenses.*
TO custom@whitehouse.gov
IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON customer.*
TO custom@'%'
IDENTIFIED BY 'stupid';
To set up the user's privileges by modifying the grant tables directly,
run these commands (note the FLUSH PRIVILEGES at the end):
shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password)
VALUES('localhost','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
VALUES('server.domain','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
VALUES('whitehouse.gov','custom',PASSWORD('stupid'));
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES
('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES
('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv)
VALUES('%','customer','custom','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;
The first three INSERT statements add user table entries that
allow user custom to connect from the various hosts with the given
password, but grant no permissions to him (all privileges are set to the
default value of 'N'). The next three INSERT statements add
db table entries that grant privileges to custom for the
bankaccount, expenses and customer databases, but only
when accessed from the proper hosts. As usual, when the grant tables are
modified directly, the server must be told to reload them (with
FLUSH PRIVILEGES) so that the privilege changes take effect.
If you want to give a specific user access from any machine in a given
domain, you can issue a GRANT statement like the following:
mysql> GRANT ...
ON *.*
TO myusername@"%.mydomainname.com"
IDENTIFIED BY 'mypassword';
To do the same thing by modifying the grant tables directly, do this:
mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername',
PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;
You can also use xmysqladmin, mysql_webadmin and even
xmysql to insert, change and update values in the grant tables.
You can find these utilities at the MySQL
Contrib directory.
The examples in the preceding sections illustrate an important principle:
when you store a non-empty password using INSERT or UPDATE
statements, you must use the PASSWORD() function to encrypt it. This
is because the user table stores passwords in encrypted form, not as
plaintext. If you forget that fact, you are likely to attempt to set
passwords like this:
shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
VALUES('%','jeffrey','biscuit');
mysql> FLUSH PRIVILEGES;
The result is that the plaintext value 'biscuit' is stored as the
password in the user table. When the user jeffrey attempts to
connect to the server using this password, the mysql client encrypts
it with PASSWORD() and sends the result to the server. The server
compares the value in the user table (which is the plaintext value
'biscuit') to the encrypted password (which is not
'biscuit'). The comparison fails and the server rejects the
connection:
shell> mysql -u jeffrey -pbiscuit test Access denied
Since passwords must be encrypted when they are inserted in the user
table, the INSERT statement should have been specified like this
instead:
mysql> INSERT INTO user (Host,User,Password)
VALUES('%','jeffrey',PASSWORD('biscuit'));
You must also use the PASSWORD() function when you use SET
PASSWORD statements:
mysql> SET PASSWORD FOR jeffrey@"%" = PASSWORD('biscuit');
If you set passwords using the GRANT ... IDENTIFIED BY statement
or the mysqladmin password command, the PASSWORD() function
is unnecessary. They both take care of encrypting the password for you,
so you would specify a password of 'biscuit' like this:
mysql> GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY 'biscuit';
or
shell> mysqladmin -u jeffrey password biscuit
Note: PASSWORD() does not perform password encryption in the same way
that Unix passwords are encrypted. You should not assume that if your Unix
password and your MySQL password are the same, PASSWORD()
will result in the same encrypted value as is stored in the Unix password
file.
See section 6.4 MySQL user names and passwords.
Access denied errors
If you encounter Access denied errors when you try to connect to the
MySQL server, the list below indicates some courses of
action you can take to correct the problem:
mysql_install_db script after installing
MySQL, to set up the initial grant table contents? If not, do
so. See section 6.12 Setting up the initial MySQL privileges. Test the initial privileges by
executing this command:
shell> mysql -u root testThe server should let you connect without error. You should also make sure you have a file 'user.MYD' in the MySQL database directory. Ordinarily, this is 'PATH/var/mysql/user.MYD', where
PATH is the
pathname to the MySQL installation root.
shell> mysql -u root mysqlThe server should let you connect because the MySQL
root user
has no password initially. Since that is also a security risk, setting the
root password is something you should do while you're setting up
your other MySQL users.
If you try to connect as root and get this error:
Access denied for user: '@unknown' to database mysqlthis means that you don't have an entry in the
user table with a
User column value of 'root' and that mysqld cannot
resolve the hostname for your client. In this case, you must restart the
server with the --skip-grant-tables option and edit your
'/etc/hosts' or '\windows\hosts' file to add a entry for your
host.
mysql_fix_privilege_tables script? If not, do so. The structure of
the grant tables changed with MySQL 3.22.11 when the GRANT
statement became functional.
INSERT or
UPDATE statement) and your changes seem to be ignored, remember that
you must issue a FLUSH PRIVILEGES statement or execute a
mysqladmin flush-privileges command to cause the server to reread the
tables. Otherwise your changes have no effect until the next time the server
is restarted. Remember that after you set the root password, you
won't need to specify it until after you flush the privileges, because the
server still won't know you've changed the password yet!
mysqld daemon with the
--skip-grant-tables option. Then you can change the MySQL
grant tables and use the mysqlaccess script to check whether or not
your modifications have the desired effect. When you are satisfied with your
changes, execute mysqladmin flush-privileges to tell the mysqld
server to start using the new grant tables. Note: Reloading the
grant tables overrides the --skip-grant-tables option. This allows
you to tell the server to begin using the grant tables again without bringing
it down and restarting it.
mysql -u user_name db_name or mysql
-u user_name -pyour_pass db_name. If you are able to connect using the
mysql client, there is a problem with your program and not with the
access privileges. (Notice that there is no space between -p and the
password; you can also use the --password=your_pass syntax to specify
the password.)
PASSWORD() function if you set the password with the
INSERT, UPDATE or SET PASSWORD statements. The
PASSWORD() function is unnecessary if you specify the password using
the GRANT ... INDENTIFIED BY statement or the mysqladmin
password command.
See section 6.14 How to set up passwords.
localhost is a synonym for your local hostname, and is also the
default host to which clients try to connect if you specify no host
explicitly. However, connections to localhost do not work if you are
running on a system that uses MIT-pthreads (localhost connections are
made using Unix sockets, which are not supported by MIT-pthreads). To avoid
this problem on such systems, you should use the --host option to name
the server host explicitly. This will make a TCP/IP connection to the
mysqld server. In this case, you must have your real hostname in
user table entries on the server host. (This is true even if you are
running a client program on the same host as the server.)
Access denied error when trying to connect to the
database with mysql -u user_name db_name, you may have a problem
with the user table. Check this by executing mysql -u root
mysql and issuing this SQL statement:
mysql> SELECT * FROM user;The result should include an entry with the
Host and User
columns matching your computer's hostname and your MySQL user name.
Access denied error message will tell you who you are trying
to log in as, the host from which you are trying to connect, and whether
or not you were using a password. Normally, you should have one entry in
the user table that exactly matches the hostname and user name
that were given in the error message.
user table that matches that host:
Host ... is not allowed to connect to this MySQL serverYou can fix this by using the command line tool
mysql (on the server
host!) to add a row to the user table for the user/hostname combination
from which you are trying to connect. If you are not running MySQL
3.22 and you don't know the IP number or hostname of the machine from which
you are connecting, you should put an entry with '%' as the
Host column value in the user table and restart mysqld
with the --log option on the server machine. After trying to connect
from the client machine, the information in the MySQL log will
indicate how you really did connect. (Then replace the '%' in the
user table entry with the actual hostname that shows up in the log.
Otherwise, you'll have a system that is insecure.)
mysql -u root test works but mysql -h your_hostname -u root
test results in Access denied, then you may not have the correct name
for your host in the user table. A common problem here is that the
Host value in the user table entry specifies an unqualified hostname,
but your system's name resolution routines return a fully-qualified domain
name (or vice-versa). For example, if you have an entry with host
'tcx' in the user table, but your DNS tells MySQL that
your hostname is 'tcx.subnet.se', the entry will not work. Try adding
an entry to the user table that contains the IP number of your host as
the Host column value. (Alternatively, you could add an entry to the
user table with a Host value that contains a wildcard--for
example, 'tcx.%'. However, use of hostnames ending with '%' is
insecure and is not recommended!)
mysql -u user_name test works but mysql -u user_name
other_db_name doesn't work, you don't have an entry for other_db_name
listed in the db table.
mysql -u user_name db_name works when executed on the server
machine, but mysql -u host_name -u user_name db_name doesn't work when
executed on another client machine, you don't have the client machine listed
in the user table or the db table.
Access denied, remove from the
user table all entries that have Host values containing
wildcards (entries that contain '%' or '_'). A very common error
is to insert a new entry with Host='%' and
User='some user', thinking that this will allow you to specify
localhost to connect from the same machine. The reason that this
doesn't work is that the default privileges include an entry with
Host='localhost' and User=". Since that entry
has a Host value 'localhost' that is more specific than
'%', it is used in preference to the new entry when connecting from
localhost! The correct procedure is to insert a second entry with
Host='localhost' and User='some_user', or to
remove the entry with with Host='localhost' and
User=".
db or
host table:
Access to database deniedIf the entry selected from the
db table has an empty value in the
Host column, make sure there are one or more corresponding entries in
the host table specifying which hosts the db table entry
applies to.
If you get the error when using the SQL commands SELECT ...
INTO OUTFILE or LOAD DATA INFILE, your entry in the user table
probably doesn't have the file privilege enabled.
Access denied when you run a client without any options, make sure you
haven't specified an old password in any of your option files!
See section 4.15.4 Option files.
mysqld daemon with a debugging
option (for example, --debug=d,general,query). This will print host and
user information about attempted connections, as well as information about
each command issued. See section G.1 Debugging a MySQL server.
mysqldump mysql command. As always, post your problem using
the mysqlbug script. In some cases you may restart mysqld with
--skip-grant-tables to be able to run mysqldump.
A string is a sequence of characters, surrounded by either single quote ('") or double quote ('"') characters (the later only if you don't run in ANSI mode). Examples:
'a string' "another string"
Within a string, certain sequences have special meaning. Each of these sequences begins with a backslash ('\'), known as the escape character. MySQL recognizes the following escape sequences:
\0
NUL) character.
\n
\t
\r
\b
\'
\"
\\
\%
\_
Note that if you use '\%' or '\%_' in some string contexts, these will return the strings '\%' and '\_' and not '%' and '_'.
There are several ways to include quotes within a string:
The SELECT statements shown below demonstrate how quoting and
escaping work:
mysql> SELECT 'hello', '"hello"', '""hello""', 'hel"lo', '\'hello'; +-------+---------+-----------+--------+--------+ | hello | "hello" | ""hello"" | hel'lo | 'hello | +-------+---------+-----------+--------+--------+ mysql> SELECT "hello", "'hello'", ""hello"", "hel""lo", "\"hello"; +-------+---------+-----------+--------+--------+ | hello | 'hello' | "hello" | hel"lo | "hello | +-------+---------+-----------+--------+--------+ mysql> SELECT "This\nIs\nFour\nlines"; +--------------------+ | This Is Four lines | +--------------------+
If you want to insert binary data into a BLOB column, the following
characters must be represented by escape sequences:
NUL
\
'
"
If you write C code, you can use the C API function
mysql_escape_string() to escape characters for the INSERT
statement. See section 20.3 C API function overview. In Perl, you can use the
quote method of the DBI package to convert special
characters to the proper escape sequences. See section 20.5.2 The DBI interface.
You should use an escape function on any string that might contain any of the special characters listed above!
Integers are represented as a sequence of digits. Floats use '.' as a decimal separator. Either type of number may be preceded by '-' to indicate a negative value.
Examples of valid integers:
1221 0 -32
Examples of valid floating-point numbers:
294.42 -32032.6809e+10 148.00
An integer may be used in a floating-point context; it is interpreted as the equivalent floating-point number.
MySQL supports hexadecimal values. In number context these acts like an integer (64 bit precision). In string context these acts like a binary string where each pair of hex digits is converted to a character.
mysql> SELECT 0xa+0
-> 10
mysql> select 0x5061756c;
-> Paul
Hexadecimal strings is often used by ODBC to give values for BLOB columns.
NULL values
The NULL value means "no data" and is different from values such
as 0 for numeric types or the empty string for string types.
See section 18.15 Problems with NULL values.
NULL may be represented by \N when using the text file import
or export formats (LOAD DATA INFILE, SELECT ... INTO OUTFILE).
See section 7.16 LOAD DATA INFILE syntax.
Database, table, index, column and alias names all follow the same rules in MySQL:
Note that the rules changed starting with MySQL 3.23.6 when we introduced
quoting of identifiers (database, table and column names) with ' ("
will also work to quote identifiers if you run in ANSI mode).
| Identifier | max length | Allowed characters |
| Database | 64 | Any character that is allowed in a directory name execpt /.
|
| Table | 64 | Any character that is allowed in file name, execpt / or .
|
| Column | 64 | All characters |
| Alias | 255 | All characters |
Note that in addition to the above, you can't have ASCII(0) or ASCII(255) in an identifier.
Note that if the identifer is a restricted word or contains special character
you must always quote it with ' when you use it:
SELECT * from 'select' where 'select'.id > 100;
In previous versions of MySQL, the name rules are as follows:
It is recommended that you do not use names like 1e, because
an expression like 1e+1 is ambiguous. It may be interpreted as the
expression 1e + 1 or as the number 1e+1.
In MySQL you can refer to a column using any of the following forms:
| Column reference | Meaning |
col_name | Column col_name
from whichever table used in the query contains a column of that name
|
tbl_name.col_name | Column col_name from table
tbl_name of the current database
|
db_name.tbl_name.col_name | Column col_name from table
tbl_name of the database db_name. This form is available in
MySQL 3.22 or later.
|
'column_name' | A column that is a keyword or contains special characters. |
You need not specify a tbl_name or db_name.tbl_name prefix for
a column reference in a statement unless the reference would be ambiguous.
For example, suppose tables t1 and t2 each contain a column
c, and you retrieve c in a SELECT statement that uses
both t1 and t2. In this case, c is ambiguous because it
is not unique among the tables used in the statement, so you must indicate
which table you mean by writing t1.c or t2.c. Similarly, if
you are retrieving from a table t in database db1 and from a
table t in database db2, you must refer to columns in those
tables as db1.t.col_name and db2.t.col_name.
The syntax .tbl_name means the table tbl_name in the current
database. This syntax is accepted for ODBC compatibility, because some ODBC
programs prefix table names with a '.' character.
In MySQL, databases and tables correspond to directories and files within those directories. Consequently, the case sensitivity of the underlying operating system determines the case sensitivity of database and table names. This means database and table names are case sensitive in Unix and case insensitive in Win32.
Note: Although database and table names are case insensitive for
Win32, you should not refer to a given database or table using different
cases within the same query. The following query would not work because it
refers to a table both as my_table and as MY_TABLE:
mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;
Column names are case insensitive in all cases.
Aliases on tables are case sensitive. The following query would not work
because it refers to the alias both as a and as A:
mysql> SELECT col_name FROM tbl_name AS a
WHERE a.col_name = 1 OR A.col_name = 2;
Aliases on columns are case insensitive.
MySQL supports thread specific variables with the
@variablename syntax. A variable name may consist of
alphanumeric characters from the current character set and also
'_', '$', and '.' . The default character set is
ISO-8859-1 Latin1; this may be changed by recompiling
MySQL. See section 9.1.1 The character set used for data and sorting.
Variables don't have to be initialized. They contain NULL by default and can store and integer, real or a string value. All variables for a thread are automaticly freed when the thread exits.
You can set a variable with the SET syntax:
SET @variable= { integer expression | real expression | string expression }
[,@variable= ...].
You can also set a variable in an expression with the @variable:=expr
syntax:
select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; +----------------------+------+------+------+ | @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 | +----------------------+------+------+------+ | 5 | 5 | 1 | 4 | +----------------------+------+------+------+
(We had to use the := syntax here as = was reserverd for
comparisons)
MySQL supports a number of column types, which may be grouped into three categories: numeric types, date and time types, and string (character) types. This section first gives an overview of the types available and summarizes the storage requirements for each column type, then provides a more detailed description of the properties of the types in each category. The overview is intentionally brief. The more detailed descriptions should be consulted for additional information about particular column types, such as the allowable formats in which you can specify values.
The column types supported by MySQL are listed below. The following code letters are used in the descriptions:
M
D
M-2.
Square brackets ('[' and ']') indicate parts of type specifiers that are optional.
Note that if you specify ZEROFILL for a column, MySQL will
automatically add the UNSIGNED attribute to the column.
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
-128 to 127. The
unsigned range is 0 to 255.
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
-32768 to 32767. The
unsigned range is 0 to 65535.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
-8388608 to
8388607. The unsigned range is 0 to 16777215.
INT[(M)] [UNSIGNED] [ZEROFILL]
-2147483648 to
2147483647. The unsigned range is 0 to 4294967295.
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
INT.
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
-9223372036854775808 to
9223372036854775807. The unsigned range is 0 to
18446744073709551615. Note that all arithmetic is done using
signed BIGINT or DOUBLE values, so you shouldn't use
unsigned big integers larger than 9223372036854775807 (63 bits)
except with bit functions! Note that -, + and *
will use BIGINT arithmetic when both arguments are INTEGER
values! This means that if you multiply two big integers (or results
from functions that return integers) you may get unexpected results if
the result is larger than 9223372036854775807.
A floating-point number. Cannot be unsigned. precision can be
<=24 for a single precision floating point number and between 25
and 53 for a double precision floating point number.
these types are like the FLOAT and DOUBLE types described
immediately below. FLOAT(X) have the same ranges as the
corresponding FLOAT and DOUBLE types, but the display
size and number of decimals is undefined.
In MySQL 3.23, this is a true floating point value. In
earlier MySQL versions, FLOAT(precision) always has 2 decimals.
This syntax is provided for ODBC compatibility.
FLOAT[(M,D)] [ZEROFILL]
-3.402823466E+38 to
-1.175494351E-38, 0 and 1.175494351E-38 to
3.402823466E+38. The M is the display width and D is the
number of decimals. FLOAT without an argument or with an argument of
<= 24 stands for a single-precision floating point number.
DOUBLE[(M,D)] [ZEROFILL]
-1.7976931348623157E+308 to
-2.2250738585072014E-308, 0 and
2.2250738585072014E-308 to 1.7976931348623157E+308. The M
is the display width and D is the number of decimals. DOUBLE
without an argument or FLOAT(X) where 25 <= X <= 53 stands for a
double-precision floating point number.
DOUBLE PRECISION[(M,D)] [ZEROFILL]
REAL[(M,D)] [ZEROFILL]
DOUBLE.
DECIMAL[(M[,D])] [ZEROFILL]
CHAR column: "unpacked" means the number is stored as a string,
using one character for each digit of the value. The decimal point,
and, for negative numbers, the '-' sign is not counted in M. If
D is 0, values will have no decimal point or fractional part.
The maximum range of DECIMAL values is the same as for
DOUBLE, but the actual range for a given DECIMAL column
may be constrained by the choice of M and D.
If D is left out it's set to 0. If M is left out it's set to 10.
Note that in MySQL 3.22 the M argument includes the sign
and the decimal point.
NUMERIC(M,D) [ZEROFILL]
DECIMAL.
DATE
'1000-01-01' to '9999-12-31'.
MySQL displays DATE values in 'YYYY-MM-DD' format, but
allows you to assign values to DATE columns using either strings or
numbers.
DATETIME
'1000-01-01
00:00:00' to '9999-12-31 23:59:59'. MySQL displays
DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows you
to assign values to DATETIME columns using either strings or numbers.
TIMESTAMP[(M)]
'1970-01-01 00:00:00' to sometime in the
year 2037. MySQL displays TIMESTAMP values in
YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD or YYMMDD
format, depending on whether M is 14 (or missing), 12,
8 or 6, but allows you to assign values to TIMESTAMP
columns using either strings or numbers. A TIMESTAMP column is useful
for recording the date and time of an INSERT or UPDATE
operation because it is automatically set to the date and time of the most
recent operation if you don't give it a value yourself. You can also set it
to the current date and time by assigning it a NULL value. See section 7.3.6 Date and time types.
TIME
'-838:59:59' to '838:59:59'.
MySQL displays TIME values in 'HH:MM:SS' format, but
allows you to assign values to TIME columns using either strings or
numbers.
YEAR[(2|4)]
1901 to 2155, and 0000 in the 4 year format and
1970-2069 if you use the 2 digit format (70-69). MySQL displays
YEAR values in YYYY format, but allows you to assign
values to YEAR columns using either strings or numbers.
(The YEAR type is new in MySQL 3.22.)
CHAR(M) [BINARY]
M is 1 to 255 characters.
Trailing spaces are removed when the value is retrieved. CHAR values
are sorted and compared in case-insensitive fashion according to the
default character set unless the BINARY keyword is given.
NATIONAL CHAR (short form NCHAR) is the ANSI SQL way to
define that a CHAR column should use the default CHARACTER set. This is
default in MySQL.
CHAR is a shorthand for CHARACTER.
[NATIONAL] VARCHAR(M) [BINARY]
M is 1 to 255 characters.
VARCHAR values are sorted and compared in case-insensitive fashion
unless the BINARY keyword is given. See section 7.7.1 Silent column specification changes.
VARCHAR is a shorthand for CHARACTER VARYING.
TINYBLOB
TINYTEXT
BLOB or TEXT column with a maximum length of 255 (2^8 - 1)
characters. See section 7.7.1 Silent column specification changes.
BLOB
TEXT
BLOB or TEXT column with a maximum length of 65535 (2^16 - 1)
characters. See section 7.7.1 Silent column specification changes.
MEDIUMBLOB
MEDIUMTEXT
BLOB or TEXT column with a maximum length of 16777215
(2^24 - 1) characters. See section 7.7.1 Silent column specification changes.
LONGBLOB
LONGTEXT
BLOB or TEXT column with a maximum length of 4294967295
(2^32 - 1) characters. See section 7.7.1 Silent column specification changes.
ENUM('value1','value2',...)
'value1', 'value2', ..., or NULL. An ENUM
can have a maximum of 65535 distinct values.
SET('value1','value2',...)
'value1', 'value2',
... A SET can have a maximum of 64 members.
The storage requirements for each of the column types supported by MySQL are listed below by category.
| Column type | Storage required |
TINYINT | 1 byte |
SMALLINT | 2 bytes |
MEDIUMINT | 3 bytes |
INT | 4 bytes |
INTEGER | 4 bytes |
BIGINT | 8 bytes |
FLOAT(X) | 4 if X <= 24 or 8 if 25 <= X <= 53 |
FLOAT | 4 bytes |
DOUBLE | 8 bytes |
DOUBLE PRECISION | 8 bytes |
REAL | 8 bytes |
DECIMAL(M,D) | M bytes (D+2, if M < D)
|
NUMERIC(M,D) | M bytes (D+2, if M < D)
|
| Column type | Storage required |
DATE | 3 bytes |
DATETIME | 8 bytes |
TIMESTAMP | 4 bytes |
TIME | 3 bytes |
YEAR | 1 byte |
| Column type | Storage required |
CHAR(M) | M bytes, 1 <= M <= 255
|
VARCHAR(M) | L+1 bytes, where L <= M and
1 <= M <= 255
|
TINYBLOB, TINYTEXT | L+1 bytes,
where L < 2^8
|
BLOB, TEXT | L+2 bytes,
where L < 2^16
|
MEDIUMBLOB, MEDIUMTEXT | L+3 bytes,
where L < 2^24
|
LONGBLOB, LONGTEXT | L+4 bytes,
where L < 2^32
|
ENUM('value1','value2',...) | 1 or 2 bytes, depending on the number of enumeration values (65535 values maximum) |
SET('value1','value2',...) | 1, 2, 3, 4 or 8 bytes, depending on the number of set members (64 members maximum) |
VARCHAR and the BLOB and TEXT types are variable-length
types, for which the storage requirements depend on the actual length of
column values (represented by L in the preceding table), rather than
on the type's maximum possible size. For example, a VARCHAR(10)
column can hold a string with a maximum length of 10 characters. The actual
storage required is the length of the string (L), plus 1 byte to
record the length of the string. For the string 'abcd', L is 4
and the storage requirement is 5 bytes.
The BLOB and TEXT types require 1, 2, 3 or 4 bytes to record
the length of the column value, depending on the maximum possible length of
the type.
If a table includes any variable-length column types, the record format will also be variable-length. Note that when a table is created, MySQL may under certain conditions change a column from a variable-length type to a fixed-length type, or vice-versa. See section 7.7.1 Silent column specification changes.
The size of an ENUM object is determined by the number of different
enumeration values. 1 byte is used for enumerations with up to 255 possible
values. 2 bytes are used for enumerations with up to 65535 values.
The size of a SET object is determined by the number of different
set members. If the set size is N, the object occupies (N+7)/8
bytes, rounded up to 1, 2, 3, 4 or 8 bytes. A SET can have a maximum
of 64 members.
MySQL supports all of the ANSI/ISO SQL92 numeric types. These
types include the exact numeric data types (NUMERIC,
DECIMAL, INTEGER, and SMALLINT), as well as the
approximate numeric data types (FLOAT, REAL, and
DOUBLE PRECISION). The keyword INT is a synonym for
INTEGER, and the keyword DEC is a synonym for
DECIMAL.
The NUMERIC and DECIMAL types are implemented as the same
type by MySQL, as permitted by the SQL92 standard. They are
used for values for which it is important to preserve exact precision,
for example with monetary data. When declaring a column of one of these
types the precision and scale can be (and usually is) specified; for
example:
salary DECIMAL(9,2)
In this example, 9 (precision) represents the number of
significant decimal digits which will be stored for values, and
2 (scale) represents the number of digits which will be
stored following the decimal point. In this case, therefore, the range
of values which can be stored in the salary column is from
-9999999.99 to 9999999.99. In ANSI/ISO SQL92, the syntax
DECIMAL(p) is equivalent to DECIMAL(p,0). Similarly, the
syntax DECIMAL is equivalent to DECIMAL(p,0), where the
implementation is allowed to decide the value of p.
MySQL does not currently support either of these variant forms
of the DECIMAL/NUMERIC data types. This is not generally
a serious problem, as the principal benefits of these types derive from
the ability to control both precision and scale explicitly.
DECIMAL and NUMERIC values are stored as strings, rather
than as binary floating point numbers, in order to preserve the decimal
precision of those values. One character is used for each digit of the
value, the decimal point (if scale > 0) and the '-' sign
(for negative numbers). If scale is 0, DECIMAL and
NUMERIC values contain no decimal point or fractional part.
The maximum range of DECIMAL and NUMERIC values is the
same as for DOUBLE, but the actual range for a given
DECIMAL or NUMERIC column can be constrained by the
precision or scale for a given column. When such a column
is assigned a value with more digits following the decimal point than
are allowed by the specified scale, the value is rounded to that
scale. When a DECIMAL or NUMERIC column is
assigned a value whose magnitude exceeds the range implied by the
specified (or defaulted) precision and scale,
MySQL stores the value representing the corresponding end
point of that range.
As an extension to the ANSI/ISO SQL92 standard, MySQL also
supports the integral types TINYINT, MEDIUMINT, and
BIGINT as listed in the tables above. Another extension is
supported by MySQL for optionally specifying the display width
of an integral value in parentheses following the base keyword for the
type (for example, INT(4)). This optional width specification is
used to left-pad the display of values whose width is less than the
width specified for the column, but does not constrain the range of
values which can be stored in the column, nor the number of digits which
will be displayed for values whose width exceeds that specified for the
column. When used in conjunction with the optional extension attribute
ZEROFILL the default padding of spaces is replaced with zeroes.
For example, for a column declared as INT(5) ZEROFILL, a value
of 4 is retrieved as 00004. Note that if you store larger
values than the display width in an integral column, you may experience
problems when MySQL generates temporary tables for some
complicated joins as in these case MySQL trust that the data
did fit into the original column width.
All integral types can have an optional (non-standard) attribute
UNSIGNED. Unsigned values can be used when you want to allow
only positive numbers in a column and you need a little bigger numeric
range for the column.
The FLOAT type is used to represent approximate numeric data
types. The ANSI/ISO SQL92 standard allows an optional specification of
the precision (but not the range of the exponent) in bits following the
keyword FLOAT in parentheses. The MySQL implementation
also supports this optional precision specification. When the keyword
FLOAT is used for a column type without a precision
specification, MySQL uses four bytes to store the values. A
variant syntax is also supported, with two numbers given in parentheses
following the FLOAT keyword. With this option, the first number
continues to represent the storage requirements for the value in bytes,
and the second number specifies the number of digits to be stored and
displayed following the decimal point (as with DECIMAL and
NUMERIC). When MySQL is asked to store a number for
such a column with more decimal digits following the decimal point than
specified for the column, the value is rounded to eliminate the extra
digits when the value is stored.
The REAL and DOUBLE PRECISION types do not accept
precision specifications. As an extension to the ANSI/ISO SQL92
standard, MySQL recognizes DOUBLE as a synonym for the
DOUBLE PRECISION type. In contrast with the standard's
requirement that the precision for REAL be smaller than that used
for DOUBLE PRECISION, MySQL implements both as 8-byte
double-precision floating point values (when running in not "Ansi mode").
For maximum portability, code requiring storage of approximate numeric
data values should use FLOAT or DOUBLE PRECISION with no
specification of precision or number of decimal points.
When asked to store a value in a numeric column that is outside the column type's allowable range, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.
For example, the range of an INT column is -2147483648 to
2147483647. If you try to insert -9999999999 into an
INT column, the value is clipped to the lower endpoint of the range,
and -2147483648 is stored instead. Similarly, if you try to insert
9999999999, 2147483647 is stored instead.
If the INT column is UNSIGNED, the size of the column's
range is the same but its endpoints shift up to 0 and 4294967295.
If you try to store -9999999999 and 9999999999,
the values stored in the column become 0 and 4294967296.
Conversions that occur due to clipping are reported as "warnings" for
ALTER TABLE, LOAD DATA INFILE, UPDATE and
multi-row INSERT statements.
The date and time types are DATETIME, DATE,
TIMESTAMP, TIME and YEAR. Each of these has a
range of legal values, as well as a "zero" value that is used when you
specify a really illegal value. Note that MySQL allows you to store
certain 'not strictly' legal date values, for example 1999-11-31.
The reason for this is that we think it's the responsibility of the
application to handle date checking, not the SQL servers. To make the
date checking 'fast', MySQL only checks that the month is in
the range of 0-12 and the day is in the range of 0-31. The above ranges
are defined this way because MySQL allows you to store, in a
DATE or DATETIME column, dates where the day or month-day
are zero. This is extremely useful for applications that need to store
a birth-date for which you don't know the exact date. In this case you
simply store the date like 1999-00-00 or 1999-01-00. (You
can of course not expect to get a correct value from functions like
DATE_SUB() or DATE_ADD for dates like these).
Here are some general considerations to keep in mind when working with date and time types:
'98-09-04'), rather than in the
month-day-year or day-month-year orders commonly used elsewhere (e.g.,
'09-04-98', '04-09-98').
TIME values are clipped to
the appropriate endpoint of the TIME range.) The table below
shows the format of the "zero" value for each type:
| Column type | "Zero" value |
DATETIME | '0000-00-00 00:00:00'
|
DATE | '0000-00-00'
|
TIMESTAMP | 00000000000000 (length depends on display size)
|
TIME | '00:00:00'
|
YEAR | 0000
|
'0' or 0, which are easier to write.
NULL in MyODBC 2.50.12 and above, because
ODBC can't handle such values.
MySQL itself is Y2K-safe (see section 1.7 Year 2000 compliance), but input values presented to MySQL may not be. Any input containing 2-digit year values is ambiguous, since the century is unknown. Such values must be interpreted into 4-digit form since MySQL stores years internally using four digits.
For DATETIME, DATE, TIMESTAMP and YEAR types,
MySQL interprets dates with ambiguous year values using the
following rules:
00-69 are converted to 2000-2069.
70-99 are converted to 1970-1999.
Remember that these rules provide only reasonable guesses as to what your data mean. If the heuristics used by MySQL don't produce the correct values, you should provide unambiguous input containing 4-digit year values.
DATETIME, DATE and TIMESTAMP types
The DATETIME, DATE and TIMESTAMP types are related.
This section describes their characteristics, how they are similar and how
they differ.
The DATETIME type is used when you need values that contain both date
and time information. MySQL retrieves and displays DATETIME
values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is
'1000-01-01 00:00:00' to '9999-12-31 23:59:59'. ("Supported"
means that although earlier values might work, there is no guarantee that
they will.)
The DATE type is used when you need only a date value, without a time
part. MySQL retrieves and displays DATE values in
'YYYY-MM-DD' format. The supported range is '1000-01-01' to
'9999-12-31'.
The TIMESTAMP column type provides a type that you can use to
automatically mark INSERT or UPDATE operations with the current
date and time. If you have multiple TIMESTAMP columns, only the first
one is updated automatically.
Automatic updating of the first TIMESTAMP column occurs under any of
the following conditions:
INSERT or
LOAD DATA INFILE statement.
UPDATE statement and some
other column changes value. (Note that an UPDATE that sets a column
to the value it already has will not cause the TIMESTAMP column to be
updated, because if you set a column to its current value, MySQL
ignores the update for efficiency.)
TIMESTAMP column to NULL.
TIMESTAMP columns other than the first may also be set to the current
date and time. Just set the column to NULL, or to NOW().
You can set any TIMESTAMP column to a value different than the current
date and time by setting it explicitly to the desired value. This is true
even for the first TIMESTAMP column. You can use this property if,
for example, you want a TIMESTAMP to be set to the current date and
time when you create a row, but not to be changed whenever the row is updated
later:
TIMESTAMP column explicitly to its current value.
On the other hand, you may find it just as easy to use a DATETIME
column that you initialize to NOW() when the row is created and
leave alone for subsequent updates.
TIMESTAMP values may range from the beginning of 1970 to sometime in
the year 2037, with a resolution of one second. Values are displayed as
numbers.
The format in which MySQL retrieves and displays TIMESTAMP
values depends on the display size, as illustrated by the table below. The
'full' TIMESTAMP format is 14 digits, but TIMESTAMP columns may
be created with shorter display sizes:
| Column type | Display format |
TIMESTAMP(14) | YYYYMMDDHHMMSS
|
TIMESTAMP(12) | YYMMDDHHMMSS
|
TIMESTAMP(10) | YYMMDDHHMM
|
TIMESTAMP(8) | YYYYMMDD
|
TIMESTAMP(6) | YYMMDD
|
TIMESTAMP(4) | YYMM
|
TIMESTAMP(2) | YY
|
All TIMESTAMP columns have the same storage size, regardless of
display size. The most common display sizes are 6, 8, 12, and 14. You can
specify an arbitrary display size at table creation time, but values of 0 or
greater than 14 are coerced to 14. Odd-valued sizes in the range from 1 to
13 are coerced to the next higher even number.
You can specify DATETIME, DATE and TIMESTAMP values using
any of a common set of formats:
'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD
HH:MM:SS' format. A "relaxed" syntax is allowed--any punctuation
character may be used as the delimiter between date parts or time parts.
For example, '98-12-31 11:30:45', '98.12.31 11+30+45',
'98/12/31 11*30*45' and '98@12@31 11^30^45' are
equivalent.
'YYYY-MM-DD' or 'YY-MM-DD' format.
A "relaxed" syntax is allowed here, too. For example, '98-12-31',
'98.12.31', '98/12/31' and '98@12@31' are
equivalent.
'YYYYMMDDHHMMSS' or
'YYMMDDHHMMSS' format, provided that the string makes sense as a
date. For example, '19970523091528' and '970523091528' are
interpreted as '1997-05-23 09:15:28', but '971122459015' is
illegal (it has a nonsensical minute part) and becomes '0000-00-00
00:00:00'.
'YYYYMMDD' or 'YYMMDD'
format, provided that the string makes sense as a date. For example,
'19970523' and '970523' are interpreted as
'1997-05-23', but '971332' is illegal (it has nonsensical month
and day parts) and becomes '0000-00-00'.
YYYYMMDDHHMMSS or YYMMDDHHMMSS
format, provided that the number makes sense as a date. For example,
19830905132800 and 830905132800 are interpreted as
'1983-09-05 13:28:00'.
YYYYMMDD or YYMMDD
format, provided that the number makes sense as a date. For example,
19830905 and 830905 are interpreted as '1983-09-05'.
DATETIME, DATE or TIMESTAMP context, such as
NOW() or CURRENT_DATE.
Illegal DATETIME, DATE or TIMESTAMP values are converted
to the "zero" value of the appropriate type ('0000-00-00 00:00:00',
'0000-00-00' or 00000000000000).
For values specified as strings that include date part delimiters, it is not
necessary to specify two digits for month or day values that are less than
10. '1979-6-9' is the same as '1979-06-09'. Similarly,
for values specified as strings that include time part delimiters, it is not
necessary to specify two digits for hour, month or second values that are
less than 10. '1979-10-30 1:2:3' is the same as
'1979-10-30 01:02:03'.
Values specified as numbers should be 6, 8, 12 or 14 digits long. If the
number is 8 or 14 digits long, it is assumed to be in YYYYMMDD or
YYYYMMDDHHMMSS format and that the year is given by the first 4
digits. If the number is 6 or 12 digits long, it is assumed to be in
YYMMDD or YYMMDDHHMMSS format and that the year is given by the
first 2 digits. Numbers that are not one of these lengths are interpreted
as though padded with leading zeros to the closest length.
Values specified as non-delimited strings are interpreted using their length
as given. If the string is 8 or 14 characters long, the year is assumed to
be given by the first 4 characters. Otherwise the year is assumed to be
given by the first 2 characters. The string is interpreted from left to
right to find year, month, day, hour, minute and second values, for as many
parts as are present in the string. This means you should not use strings
that have fewer than 6 characters. For example, if you specify '9903',
thinking that will represent March, 1999, you will find that MySQL
inserts a "zero" date into your table. This is because the year and month
values are 99 and 03, but the day part is missing (zero), so
the value is not a legal date.
TIMESTAMP columns store legal values using the full precision with
which the value was specified, regardless of the display size. This has
several implications:
TIMESTAMP(4) or TIMESTAMP(2). Otherwise, the value will not
be a legal date and 0 will be stored.
ALTER TABLE to widen a narrow TIMESTAMP column,
information will be displayed that previously was "hidden".
TIMESTAMP column does not cause information to
be lost, except in the sense that less information is shown when the values
are displayed.
TIMESTAMP values are stored to full precision, the only
function that operates directly on the underlying stored value is
UNIX_TIMESTAMP(). Other functions operate on the formatted retrieved
value. This means you cannot use functions such as HOUR() or
SECOND() unless the relevant part of the TIMESTAMP value is
included in the formatted value. For example, the HH part of a
TIMESTAMP column is not displayed unless the display size is at least
10, so trying to use HOUR() on shorter TIMESTAMP values
produces a meaningless result.
You can to some extent assign values of one date type to an object of a different date type. However, there may be some alteration of the value or loss of information:
DATE value to a DATETIME or TIMESTAMP
object, the time part of the resulting value is set to '00:00:00',
because the DATE value contains no time information.
DATETIME or TIMESTAMP value to a DATE
object, the time part of the resulting value is deleted, because the
DATE type stores no time information.
DATETIME, DATE and TIMESTAMP
values all can be specified using the same set of formats, the types do not
all have the same range of values. For example, TIMESTAMP values
cannot be earlier than 1970 or later than 2037. This means
that a date such as '1968-01-01', while legal as a DATETIME or
DATE value, is not a valid TIMESTAMP value and will be
converted to 0 if assigned to such an object.
Be aware of certain pitfalls when specifying date values:
'10:11:12' might look like a time value
because of the ':' delimiter, but if used in a date context will be
interpreted as the year '2010-11-12'. The value '10:45:15'
will be converted to '0000-00-00' because '45' is not a legal
month.
00-69 are converted to 2000-2069.
70-99 are converted to 1970-1999.
TIME type
MySQL retrieves and displays TIME values in 'HH:MM:SS'
format (or 'HHH:MM:SS' format for large hours values). TIME
values may range from '-838:59:59' to '838:59:59'. The reason
the hours part may be so large is that the TIME type may be used not
only to represent a time of day (which must be less than 24 hours), but also
elapsed time or a time interval between two events (which may be much greater
than 24 hours, or even negative).
You can specify TIME values in a variety of formats:
'HH:MM:SS' format.
A "relaxed" syntax is allowed--any punctuation character may be
used as the delimiter between time parts. For example, '10:11:12'
and '10.11.12' are equivalent.
'HHMMSS' format, provided that
it makes sense as a time. For example, '101112' is understood as
'10:11:12', but '109712' is illegal (it has a nonsensical
minute part) and becomes '00:00:00'.
HHMMSS format, provided that it makes sense as a time.
For example, 101112 is understood as '10:11:12'.
TIME context, such as CURRENT_TIME.
For TIME values specified as strings that include a time part
delimiter, it is not necessary to specify two digits for hours, minutes or
seconds values that are less than 10. '8:3:2' is the same as
'08:03:02'.
Be careful about assigning "short" TIME values to a TIME
column. MySQL interprets values using the assumption that the
rightmost digits represent seconds. (MySQL interprets TIME
values as elapsed time, rather than as time of day.) For example, you might
think of '11:12', '1112' and 1112 as meaning
'11:12:00' (12 minutes after 11 o'clock), but MySQL
interprets them as '00:11:12' (11 minutes, 12 seconds). Similarly,
'12' and 12 are interpreted as '00:00:12'.
Values that lie outside the TIME range
but are otherwise legal are clipped to the appropriate
endpoint of the range. For example, '-850:00:00' and
'850:00:00' are converted to '-838:59:59' and
'838:59:59'.
Illegal TIME values are converted to '00:00:00'. Note that
since '00:00:00' is itself a legal TIME value, there is no way
to tell, from a value of '00:00:00' stored in a table, whether the
original value was specified as '00:00:00' or whether it was illegal.
YEAR type
The YEAR type is a 1-byte type used for representing years.
MySQL retrieves and displays YEAR values in YYYY
format. The range is 1901 to 2155.
You can specify YEAR values in a variety of formats:
'1901' to '2155'.
1901 to 2155.
'00' to '99'. Values in the
ranges '00' to '69' and '70' to '99' are
converted to YEAR values in the ranges 2000 to 2069 and
1970 to 1999.
1 to 99. Values in the
ranges 1 to 69 and 70 to 99 are converted to
YEAR values in the ranges 2001 to 2069 and 1970
to 1999. Note that the range for two-digit numbers is slightly
different than the range for two-digit strings, since you cannot specify zero
directly as a number and have it be interpreted as 2000. You
must specify it as a string '0' or '00' or it will be
interpreted as 0000.
YEAR context, such as NOW().
Illegal YEAR values are converted to 0000.
The string types are CHAR, VARCHAR, BLOB, TEXT,
ENUM and SET.
CHAR and VARCHAR types
The CHAR and VARCHAR types are similar, but differ in the
way they are stored and retrieved.
The length of a CHAR column is fixed to the length that you declare
when you create the table. The length can be any value between 1 and 255.
(As of MySQL 3.23, the length of CHAR may be 0 to 255.)
When CHAR values are stored, they are right-padded with spaces to the
specified length. When CHAR values are retrieved, trailing spaces are
removed.
Values in VARCHAR columns are variable-length strings. You can
declare a VARCHAR column to be any length between 1 and 255, just as
for CHAR columns. However, in contrast to CHAR, VARCHAR
values are stored using only as many characters as are needed, plus one byte
to record the length. Values are not padded; instead, trailing spaces are
removed when values are stored. (This space removal differs from the ANSI
SQL specification.)
If you assign a value to a CHAR or VARCHAR column that
exceeds the column's maximum length, the value is truncated to fit.
The table below illustrates the differences between the two types of columns
by showing the result of storing various string values into CHAR(4)
and VARCHAR(4) columns:
| Value | CHAR(4) | Storage required | VARCHAR(4) | Storage required |
" | ' ' | 4 bytes | " | 1 byte |
'ab' | 'ab ' | 4 bytes | 'ab' | 3 bytes |
'abcd' | 'abcd' | 4 bytes | 'abcd' | 5 bytes |
'abcdefgh' | 'abcd' | 4 bytes | 'abcd' | 5 bytes |
The values retrieved from the CHAR(4) and VARCHAR(4) columns
will be the same in each case, because trailing spaces are removed from
CHAR columns upon retrieval.
Values in CHAR and VARCHAR columns are sorted and compared in
case-insensitive fashion, unless the BINARY attribute was specified
when the table was created. The BINARY attribute means that column
values are sorted and compared in case-sensitive fashion according to the
ASCII order of the machine where the MySQL server is running.
The BINARY attribute is "sticky". This means that if a column marked
BINARY is used in an expression, the whole expression is compared as a
BINARY value.
MySQL may silently change the type of a CHAR or VARCHAR
column at table creation time.
See section 7.7.1 Silent column specification changes.
BLOB and TEXT types
A BLOB is a binary large object that can hold a variable amount of
data. The four BLOB types TINYBLOB, BLOB,
MEDIUMBLOB and LONGBLOB differ only in the maximum length of
the values they can hold.
See section 7.3.1 Column type storage requirements.
The four TEXT types TINYTEXT, TEXT, MEDIUMTEXT
and LONGTEXT correspond to the four BLOB types and have the
same maximum lengths and storage requirements. The only difference between
BLOB and TEXT types is that sorting and comparison is performed
in case-sensitive fashion for BLOB values and case-insensitive fashion
for TEXT values. In other words, a TEXT is a case-insensitive
BLOB.
If you assign a value to a BLOB or TEXT column that exceeds
the column type's maximum length, the value is truncated to fit.
In most respects, you can regard a TEXT column as a VARCHAR
column that can be as big as you like. Similarly, you can regard a
BLOB column as a VARCHAR BINARY column. The differences are:
BLOB and TEXT columns with
MySQL versions 3.23.2 and newer. Older versions of
MySQL did not support this.
BLOB and TEXT columns
when values are stored, as there is for VARCHAR columns.
BLOB and TEXT columns cannot have DEFAULT values.
MyODBC defines BLOB values as LONGVARBINARY and
TEXT values as LONGVARCHAR.
Because BLOB and TEXT values may be extremely long, you
may run up against some constraints when using them:
GROUP BY or ORDER BY on a BLOB or
TEXT column, you must convert the column value into a fixed-length
object. The standard way to do this is with the SUBSTRING
function. For example:
mysql> select comment from tbl_name,substring(comment,20) as substr ORDER BY substr;If you don't do this, only the first
max_sort_length bytes of the
column are used when sorting. The default value of max_sort_length is
1024; this value can be changed using the -O option when starting the
mysqld server. You can group on an expression involving BLOB or
TEXT values by specifying the column position or by using an alias:
mysql> select id,substring(blob_col,1,100) from tbl_name
GROUP BY 2;
mysql> select id,substring(blob_col,1,100) as b from tbl_name
GROUP BY b;
BLOB or TEXT object is determined by its
type, but the largest value you can actually transmit between the client and
server is determined by the amount of available memory and the size of the
communications buffers. You can change the message buffer size, but you must
do so on both the server and client ends. See section 10.2.3 Tuning server parameters.
Note that each BLOB or TEXT value is represented internally by
a separately-allocated object. This is in contrast to all other column types,
for which storage is allocated once per column when the table is opened.
ENUM type
An ENUM is a string object whose value normally is chosen from a list
of allowed values that are enumerated explicitly in the column specification
at table creation time.
The value may also be the empty string ("") or NULL under
certain circumstances:
ENUM (that is, a string not
present in the list of allowed values), the empty string is inserted
instead as a special error value.
ENUM is declared NULL, NULL is also a legal value
for the column, and the default value is NULL. If an ENUM is
declared NOT NULL, the default value is the first element of the
list of allowed values.
Each enumeration value has an index:
SELECT statement to find rows into which invalid
ENUM values were assigned:
mysql> SELECT * FROM tbl_name WHERE enum_col=0;
NULL value is NULL.
For example, a column specified as ENUM("one", "two", "three") can
have any of the values shown below. The index of each value is also shown:
| Value | Index |
NULL | NULL
|
"" | 0 |
"one" | 1 |
"two" | 2 |
"three" | 3 |
An enumeration can have a maximum of 65535 elements.
Lettercase is irrelevant when you assign values to an ENUM column.
However, values retrieved from the column later have lettercase matching the
values that were used to specify the allowable values at table creation time.
If you retrieve an ENUM in a numeric context, the column value's index
is returned. If you store a number into an ENUM, the number is
treated as an index, and the the value stored is the enumeration member with
that index.
ENUM values are sorted according to the order in which the enumeration
members were listed in the column specification. (In other words,
ENUM values are sorted according to their index numbers.) For
example, "a" sorts before "b" for ENUM("a", "b"), but
"b" sorts before "a" for ENUM("b", "a"). The empty
string sorts before non-empty strings, and NULL values sort before
all other enumeration values.
If you want to get all possible values for an ENUM column, you should
use: SHOW COLUMNS FROM table_name LIKE enum_column_name and parse
the ENUM definition in the second column.
SET type
A SET is a string object that can have zero or more values, each of
which must be chosen from a list of allowed values specified when the table
is created. SET column values that consist of multiple set members
are specified with members separated by commas (','). A consequence of
this is that SET member values cannot themselves contain commas.
For example, a column specified as SET("one", "two") NOT NULL can have
any of these values:
"" "one" "two" "one,two"
A SET can have a maximum of 64 different members.
MySQL stores SET values numerically, with the low-order bit
of the stored value corresponding to the first set member. If you retrieve a
SET value in a numeric context, the value retrieved has bits set
corresponding to the set members that make up the column value. If a number
is stored into a SET column, the bits that are set in the binary
representation of the number determine the set members in the column value.
Suppose a column is specified as SET("a","b","c","d"). Then the
members have the following bit values:
SET member | Decimal value | Binary value |
a | 1 | 0001
|
b | 2 | 0010
|
c | 4 | 0100
|
d | 8 | 1000
|
If you assign a value of 9 to this column, that is 1001 in
binary, so the first and fourth SET value members "a" and
"d" are selected and the resulting value is "a,d".
For a value containing more than one SET element, it does not matter
what order the elements are listed in when you insert the value. It also
doesn't not matter how many times a given element is listed in the value.
When the value is retrieved later, each element in the value will appear
once, with elements listed according to the order in which they were
specified at table creation time. For example, if a column is specified as
SET("a","b","c","d"), then "a,d", "d,a" and
"d,a,a,d,d" will all appear as "a,d" when retrieved.
SET values are sorted numerically. NULL values sort before
non-NULL SET values.
Normally, you perform a SELECT on a SET column using
the LIKE operator or the FIND_IN_SET() function:
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
But the following will also work:
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2'; mysql> SELECT * FROM tbl_name WHERE set_col & 1;
The first of these statements looks for an exact match. The second looks for values containing the first set member.
If you want to get all possible values for an SET column, you should
use: SHOW COLUMNS FROM table_name LIKE set_column_name and parse
the SET definition in the second column.
For the most efficient use of storage, try to use the most precise type in
all cases. For example, if an integer column will be used for values in the
range between 1 and 99999, MEDIUMINT UNSIGNED is the
best type.
Accurate representation of monetary values is a common problem. In
MySQL, you should use the DECIMAL type. This is stored as
a string, so no loss of accuracy should occur. If accuracy is not
too important, the DOUBLE type may also be good enough.
For high precision, you can always convert to a fixed-point type stored
in a BIGINT. This allows you to do all calculations with integers
and convert results back to floating-point values only when necessary.
See section 10.6 Choosing a table type.
All MySQL column types can be indexed. Use of indexes on the
relevant columns is the best way to improve the performance of SELECT
operations.
A table may have up to 16 indexes. The maximum index length is 256 bytes, although this may be changed when compiling MySQL.
For CHAR and VARCHAR columns, you can index a prefix of a
column. This is much faster and requires less disk space than indexing the
whole column. The syntax to use in the CREATE TABLE statement to
index a column prefix looks like this:
KEY index_name (col_name(length))
The example below creates an index for the first 10 characters of the
name column:
mysql> CREATE TABLE test (
name CHAR(200) NOT NULL,
KEY index_name (name(10)));
For BLOB and TEXT columns, you must index a prefix of the
column, you cannot index the entire thing.
MySQL can create indexes on multiple columns. An index may
consist of up to 15 columns. (On CHAR and VARCHAR columns you
can also use a prefix of the column as a part of an index).
A multiple-column index can be considered a sorted array containing values that are created by concatenating the values of the indexed columns.
MySQL uses multiple-column indexes in such a way that queries are
fast when you specify a known quantity for the first column of the index in a
WHERE clause, even if you don't specify values for the other columns.
Suppose a table is created using the following specification:
mysql> CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name));
Then the index name is an index over last_name and
first_name. The index will be used for queries that specify
values in a known range for last_name, or for both last_name
and first_name.
Therefore, the name index will be used in the following queries:
mysql> SELECT * FROM test WHERE last_name="Widenius";
mysql> SELECT * FROM test WHERE last_name="Widenius"
AND first_name="Michael";
mysql> SELECT * FROM test WHERE last_name="Widenius"
AND (first_name="Michael" OR first_name="Monty");
mysql> SELECT * FROM test WHERE last_name="Widenius"
AND first_name >="M" AND first_name < "N";
However, the name index will NOT be used in the following queries:
mysql> SELECT * FROM test WHERE first_name="Michael";
mysql> SELECT * FROM test WHERE last_name="Widenius"
OR first_name="Michael";
For more information on the manner in which MySQL uses indexes to improve query performance, see section 10.4 MySQL index use.
To make it easier to use code written for SQL implementations from other vendors, MySQL maps column types as shown in the table below. These mappings make it easier to move table definitions from other database engines to MySQL:
| Other vendor type | MySQL type |
BINARY(NUM) | CHAR(NUM) BINARY
|
CHAR VARYING(NUM) | VARCHAR(NUM)
|
FLOAT4 | FLOAT
|
FLOAT8 | DOUBLE
|
INT1 | TINYINT
|
INT2 | SMALLINT
|
INT3 | MEDIUMINT
|
INT4 | INT
|
INT8 | BIGINT
|
LONG VARBINARY | MEDIUMBLOB
|
LONG VARCHAR | MEDIUMTEXT
|
MIDDLEINT | MEDIUMINT
|
VARBINARY(NUM) | VARCHAR(NUM) BINARY
|
Column type mapping occurs at table creation time. If you create a table
with types used by other vendors and then issue a DESCRIBE tbl_name
statement, MySQL reports the table structure using the equivalent
MySQL types.
SELECT and WHERE clauses
A select_expression or where_definition in a SQL statement
can consist of any expression using the functions described below.
An expression that contains NULL always produces a NULL value
unless otherwise indicated in the documentation for the operators and
functions involved in the expression.
Note: There must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. Spaces around arguments are permitted, though.
For the sake of brevity, examples display the output from the mysql
program in abbreviated form. So this:
mysql> select MOD(29,9); 1 rows in set (0.00 sec) +-----------+ | mod(29,9) | +-----------+ | 2 | +-----------+
Is displayed like this:
mysql> select MOD(29,9);
-> 2
( ... )
mysql> select 1+2*3;
-> 7
mysql> select (1+2)*3;
-> 9
The usual arithmetic operators are available. Note that in the case of
-, + and *, the result is calculated with BIGINT
(64-bit) precision if both arguments are integers!
+
mysql> select 3+5;
-> 8
-
mysql> select 3-5;
-> -2
*
mysql> select 3*5;
-> 15
mysql> select 18014398509481984*18014398509481984.0;
-> 324518553658426726783156020576256.0
mysql> select 18014398509481984*18014398509481984;
-> 0
The result of the last expression is incorrect because the result of the integer
multiplication exceeds the 64-bit range of BIGINT calculations.
/
mysql> select 3/5;
-> 0.60
Division by zero produces a NULL result:
mysql> select 102/(1-1);
-> NULL
A division will be calculated with BIGINT arithmetic only if performed
in a context where its result is converted to an integer!
MySQL uses BIGINT (64-bit) arithmetic for bit operations, so
these operators have a maximum range of 64 bits.
|
mysql> select 29 | 15;
-> 31
&
mysql> select 29 & 15;
-> 13
<<
BIGINT) number to the left.
mysql> select 1 << 2
-> 4
>>
BIGINT) number to the right.
mysql> select 4 >> 2
-> 1
~
mysql> select 5 & ~1
-> 4
BIT_COUNT(N)
N.
mysql> select BIT_COUNT(29);
-> 4
All logical functions return 1 (TRUE) or 0 (FALSE).
NOT
!
1 if the argument is 0, otherwise returns
0.
Exception: NOT NULL returns NULL.
mysql> select NOT 1;
-> 0
mysql> select NOT NULL;
-> NULL
mysql> select ! (1+1);
-> 0
mysql> select ! 1+1;
-> 1
The last example returns 1 because the expression evaluates
the same way as (!1)+1.
OR
||
1 if either argument is not 0 and not
NULL.
mysql> select 1 || 0;
-> 1
mysql> select 0 || 0;
-> 0
mysql> select 1 || NULL;
-> 1
AND
&&
0 if either argument is 0 or NULL,
otherwise returns 1.
mysql> select 1 && NULL;
-> 0
mysql> select 1 && 0;
-> 0
Comparison operations result in a value of 1 (TRUE), 0 (FALSE)
or NULL. These functions work for both numbers and strings. Strings
are automatically converted to numbers and numbers to strings as needed (as
in Perl).
MySQL performs comparisons using the following rules:
NULL, the result of the comparison
is NULL, except for the <=> operator.
TIMESTAMP or DATETIME column and
the other argument is a constant, the constant is converted
to a timestamp before the comparison is performed. This is done to be more
ODBC-friendly.
By default, string comparisons are done in case-independent fashion using the current character set (ISO-8859-1 Latin1 by default, which also works excellently for English).
The examples below illustrate conversion of strings to numbers for comparison operations:
mysql> SELECT 1 > '6x';
-> 0
mysql> SELECT 7 > '6x';
-> 1
mysql> SELECT 0 > 'x6';
-> 0
mysql> SELECT 0 = 'x6';
-> 1
=
mysql> select 1 = 0;
-> 0
mysql> select '0' = 0;
-> 1
mysql> select '0.0' = 0;
-> 1
mysql> select '0.01' = 0;
-> 0
mysql> select '.01' = 0.01;
-> 1
<>
!=
mysql> select '.01' <> '0.01';
-> 1
mysql> select .01 <> '0.01';
-> 0
mysql> select 'zapp' <> 'zappp';
-> 1
<=
mysql> select 0.1 <= 2;
-> 1
<
mysql> select 2 <= 2;
-> 1
>=
mysql> select 2 >= 2;
-> 1
>
mysql> select 2 > 2;
-> 0
<=>
mysql> select 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-> 1 1 0
IS NULL
IS NOT NULL
NULL
mysql> select 1 IS NULL, 0 IS NULL, NULL IS NULL:
-> 0 0 1
mysql> select 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
-> 1 1 0
expr BETWEEN min AND max
expr is greater than or equal to min and expr is
less than or equal to max, BETWEEN returns 1,
otherwise it returns 0. This is equivalent to the expression
(min <= expr AND expr <= max) if all the arguments are of the
same type. (You can't compare DATE with DATETIME
arguments with BETWEEN as you can do with simple compare
operations like =) The first argument (expr) determines
how the comparison is performed. If expr is a case-insensitive
string expression, a case-insensitive string comparison is done. If
expr is a case-sensitive string expression, a case-sensitive
string comparison is done. If expr is an integer expression, an
integer comparison is done. Otherwise, a floating-point (real)
comparison is done.
mysql> select 1 BETWEEN 2 AND 3;
-> 0
mysql> select 'b' BETWEEN 'a' AND 'c';
-> 1
mysql> select 2 BETWEEN 2 AND '3';
-> 1
mysql> select 2 BETWEEN 2 AND 'x-3';
-> 0
expr IN (value,...)
1 if expr is any of the values in the IN list,
else returns 0. If all values are constants, then all values are
evaluated according to the type of expr and sorted. The search for the
item is then done using a binary search. This means IN is very quick
if the IN value list consists entirely of constants. If expr
is a case-sensitive string expression, the string comparison is performed in
case-sensitive fashion.
mysql> select 2 IN (0,3,5,'wefwf');
-> 0
mysql> select 'wefwf' IN (0,3,5,'wefwf');
-> 1
expr NOT IN (value,...)
NOT (expr IN (value,...)).
ISNULL(expr)
expr is NULL, ISNULL() returns 1, otherwise
it returns 0.
mysql> select ISNULL(1+1);
-> 0
mysql> select ISNULL(1/0);
-> 1
Note that a comparison of NULL values using = will always be
false!
COALESCE(list)
NULL element in list.
mysql> select COALESCE(NULL,1);
-> 1
mysql> select COALESCE(NULL,NULL,NULL);
-> NULL
INTERVAL(N,N1,N2,N3,...)
0 if N < N1, 1 if N < N2
and so on. All arguments are treated as integers. It is required that
N1 < N2 < N3 < ... < Nn for this function
to work correctly. This is because a binary search is used (very fast).
mysql> select INTERVAL(23, 1, 15, 17, 30, 44, 200);
-> 3
mysql> select INTERVAL(10, 1, 10, 100, 1000);
-> 2
mysql> select INTERVAL(22, 23, 30, 44, 200);
-> 0
Normally, if any expression in a string comparison is case sensitive, the comparison is performed in case-sensitive fashion.
expr LIKE pat [ESCAPE 'escape-char']
1 (TRUE) or 0
(FALSE). With LIKE you can use the following two wildcard characters
in the pattern:
% | Matches any number of characters, even zero characters |
_ | Matches exactly one character |
mysql> select 'David!' LIKE 'David_';
-> 1
mysql> select 'David!' LIKE '%D%v%';
-> 1
To test for literal instances of a wildcard character, precede the character
with the escape character. If you don't specify the ESCAPE character,
'\' is assumed:
\% | Matches one % character
|
\_ | Matches one _ character
|
mysql> select 'David!' LIKE 'David\_';
-> 0
mysql> select 'David_' LIKE 'David\_';
-> 1
To specify a different escape character, use the ESCAPE clause:
mysql> select 'David_' LIKE 'David|_' ESCAPE '|';
-> 1
LIKE is allowed on numeric expressions! (This is a MySQL
extension to the ANSI SQL LIKE.)
mysql> select 10 LIKE '1%';
-> 1
Note: Because MySQL uses the C escape syntax in strings (e.g.,
'\n'), you must double any '\' that you use in your LIKE
strings. For example, to search for '\n', specify it as '\\n'. To
search for '\', specify it as '\\\\' (the backslashes are stripped
once by the parser, and another time when the pattern match is done, leaving
a single backslash to be matched).
expr NOT LIKE pat [ESCAPE 'escape-char']
NOT (expr LIKE pat [ESCAPE 'escape-char']).
expr REGEXP pat
expr RLIKE pat
expr against a pattern
pat. The pattern can be an extended regular expression.
See section H Description of MySQL regular expression syntax. Returns 1 if expr matches pat, otherwise
returns 0. RLIKE is a synonym for REGEXP, provided for
mSQL compatibility. Note: Because MySQL uses the C escape
syntax in strings (e.g., '\n'), you must double any '\' that you
use in your REGEXP strings. In MySQL 3.23.4
REGEXP is case insensitive for normal (not binary) strings.
mysql> select 'Monty!' REGEXP 'm%y%%';
-> 0
mysql> select 'Monty!' REGEXP '.*';
-> 1
mysql> select 'new*\n*line' REGEXP 'new\\*.\\*line';
-> 1
mysql> select "a" REGEXP "A", "a" REGEXP BINARY "A";
-> 1 0
REGEXP and RLIKE use the current character set (ISO-8859-1
Latin1 by default) when deciding the type of a character.
expr NOT REGEXP pat
expr NOT RLIKE pat
NOT (expr REGEXP pat).
STRCMP(expr1,expr2)
STRCMP()
returns 0 if the strings are the same, -1 if the first
argument is smaller than the second according to the current sort order,
and 1 otherwise.
mysql> select STRCMP('text', 'text2');
-> -1
mysql> select STRCMP('text2', 'text');
-> 1
mysql> select STRCMP('text', 'text');
-> 0
BINARYBINARY operator casts the string following it to a binary string.
This is an easy way to force a column comparison to be case sensitive even
if the column isn't defined as BINARY or BLOB.
mysql> select "a" = "A";
-> 1
mysql> select BINARY "a" = "A";
-> 0
BINARY was introduced in MySQL 3.23.0
IFNULL(expr1,expr2)
expr1 is not NULL, IFNULL() returns expr1,
else it returns expr2. IFNULL() returns a numeric or string
value, depending on the context in which it is used.
mysql> select IFNULL(1,0);
-> 1
mysql> select IFNULL(0,10);
-> 0
mysql> select IFNULL(1/0,10);
-> 10
mysql> select IFNULL(1/0,'yes');
-> 'yes'
IF(expr1,expr2,expr3)
expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then
IF() returns expr2, else it returns expr3.
IF() returns a numeric or string value, depending on the context
in which it is used.
mysql> select IF(1>2,2,3);
-> 3
mysql> select IF(1<2,'yes','no');
-> 'yes'
mysql> select IF(strcmp('test','test1'),'yes','no');
-> 'no'
expr1 is evaluated as an integer value, which means that if you are
testing floating-point or string values, you should do so using a comparison
operation.
mysql> select IF(0.1,1,0);
-> 0
mysql> select IF(0.1<>0,1,0);
-> 1
In the first case above, IF(0.1) returns 0 because 0.1
is converted to an integer value, resulting in a test of IF(0). This
may not be what you expect. In the second case, the comparison tests the
original floating-point value to see whether it is non-zero. The result
of the comparison is used as an integer.
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
result where
value=compare-value. The second version returns the result for
the first condition which is true. If there was no matching result
value, then the result after ELSE is returned. If there is no
ELSE part then NULL is returned.
mysql> SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END;
-> "one"
mysql> SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END;
-> "true"
mysql> SELECT CASE BINARY "B" when "a" then 1 when "b" then 2 END;
-> NULL
All mathematical functions return NULL in case of an error.
-
mysql> select - 2;
-> -2
Note that if this operator is used with a BIGINT, the return value is a
BIGINT! This means that you should avoid using - on integers that
may have the value of -2^63!
ABS(X)
X.
mysql> select ABS(2);
-> 2
mysql> select ABS(-32);
-> 32
This function is safe to use with BIGINT values.
SIGN(X)
-1, 0 or 1, depending
on whether X is negative, zero, or positive.
mysql> select SIGN(-32);
-> -1
mysql> select SIGN(0);
-> 0
mysql> select SIGN(234);
-> 1
MOD(N,M)
%
% operator in C).
Returns the remainder of N divided by M.
mysql> select MOD(234, 10);
-> 4
mysql> select 253 % 7;
-> 1
mysql> select MOD(29,9);
-> 2
This function is safe to use with BIGINT values.
FLOOR(X)
X.
mysql> select FLOOR(1.23);
-> 1
mysql> select FLOOR(-1.23);
-> -2
Note that the return value is converted to a BIGINT!
CEILING(X)
X.
mysql> select CEILING(1.23);
-> 2
mysql> select CEILING(-1.23);
-> -1
Note that the return value is converted to a BIGINT!