| ||||||||||||||||
 | ||||||||||||||||
 |
|
| ||||||||||||||
|
|
| ||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
|
| ||||||||||||||
|
|
| ||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
This is the MySQL reference manual; it documents MySQL version 3.23.22-beta. As MySQL is work in progress, the manual gets updated frequently. There is a very good chance that this version is out of date, unless you are looking at it online. The most recent version of this manual is available at http://www.mysql.com/documentation/ in many different formats. If you have a hard time finding information in the manual, you can try the searchable PHP version at http://www.mysql.com/documentation/manual.php .
MySQL is a very fast, multi-threaded, multi-user and robust SQL (Structured Query Language) database server.
MySQL is free software. It is licensed with the GNU GENERAL PUBLIC LICENSE http://www.gnu.org/. See section 3 MySQL licensing and support.
The MySQL home page provides the latest information about MySQL.
The following list describes some useful sections of the manual:
IMPORTANT:
Reports of errors (often called bugs), as well as questions and comments
should be sent to the mailing list at mysql@lists.mysql.com. See section 2.3
How to report bugs or problems. The mysqlbug script should be
used to generate bug reports. For source distributions, the
mysqlbug script can be found in the `scripts' directory.
For binary distributions, mysqlbug can be found in the
`bin' directory.
If you have any suggestions concerning additions or corrections to this manual, please send them to the manual team at (docs@mysql.com).
This is a reference manual; it does not provide general instruction on SQL or relational database concepts. If you want general information about SQL, see section 1.8 General SQL information and tutorials. For books that focus more specifically on MySQL, see section 1.4 Books about MySQL.
The official way to pronounce MySQL is ``My Ess Que Ell'' (not MY-SEQUEL). But we try to avoid correcting people who say MY-SEQUEL.
MySQL core values
We want MySQL to be
MySQL AB and the people of MySQL AB
This manual is currently available in Texinfo, plain text, Info, HTML, PostScript and PDF versions. Because of their size, PostScript and PDF versions are not included with the main MySQL distribution, but are available for separate download at http://www.mysql.com/.
The primary document is the Texinfo file. The HTML version is produced
automatically using a modified version of texi2html. The plain text
and Info versions are produced with makeinfo. The Postscript
version is produced using texi2dvi and dvips. The PDF
version is produced with pdftex.
This manual is written and maintained by David Axmark, Michael (Monty) Widenius, Jeremy Cole, and Paul DuBois. For other contributors, see section D Contributors to MySQL.
This manual uses certain typographical conventions:
constant
mysqladmin works, invoke it with the
--help option.''
When commands are shown that are meant to be executed by a particular
program, the program is indicated by a prompt shown before the command. For
example, shell> indicates a command that you execute from your
login shell, and mysql> indicates a command that you execute
from the mysql client program:
shell> type a shell command here mysql> type a mysql command here
Shell commands are shown using Bourne shell syntax. If you are using a
csh-style shell, you may need to issue commands slightly
differently. For example, the sequence to set an environment variable and run a
command looks like this in Bourne shell syntax:
shell> VARNAME=value some_command
For csh, you would execute the sequence like this:
shell> setenv VARNAME value shell> some_command
Often, database, table, and column names must be substituted into commands.
To indicate that such substitution is necessary, this manual uses
db_name, tbl_name and col_name. For
example, you might see a statement like this:
mysql> SELECT col_name FROM db_name.tbl_name;
This means that if you were to enter a similar statement, you would supply your own database, table and column names, perhaps like this:
mysql> SELECT author_name FROM biblio_db.author_list;
SQL statements may be written in uppercase or lowercase. When this manual
shows a SQL statement, uppercase is used for particular keywords if those
keywords are under discussion (to emphasize them) and lowercase is used for the
rest of the statement. For example, you might see the following in a discussion
of the SELECT statement:
mysql> SELECT count(*) FROM tbl_name;
On the other hand, in a discussion of the COUNT() function, the
same statement would be written like this:
mysql> select COUNT(*) from tbl_name;
If no particular emphasis is intended, all keywords are written uniformly in uppercase.
In syntax descriptions, square brackets (`[' and `]') are used to indicate optional words or clauses:
DROP TABLE [IF EXISTS] tbl_name
When a syntax element consists of a number of alternatives, the alternatives are separated by vertical bars (`|'). When one member from a set of choices may be chosen, the alternatives are listed within square brackets (`[' and `]'):
TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
When one member from a set of choices must be chosen, the alternatives are listed within braces (`{' and `}'):
{DESCRIBE | DESC} tbl_name {col_name | wild}
We once started out with the intention of using mSQL to connect
to our tables using our own fast low-level (ISAM) routines. However, after some
testing we came to the conclusion that mSQL was not fast enough nor
flexible enough for our needs. This resulted in a new SQL interface to our
database but with almost the same API interface as mSQL. This API
was chosen to ease porting of third-party code.
The derivation of the name MySQL is not perfectly clear. Our base directory and a large number of our libraries and tools have had the prefix ``my'' for well over 10 years. However, Monty's daughter (some years younger) is also named My. So which of the two gave its name to MySQL is still a mystery, even for us.
While 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. It is sometimes nice to have a bound book to read in bed or while you travel. Here is a list of books about MySQL (in English):
| Title | MySQL |
| Publisher | New Riders |
| Author | Paul DuBois |
| Pub Date | 1st Edition December 1999 |
| ISBN | 0735709211 |
| Pages | 800 |
| Price | $49.99 US |
| Downloadable examples | samp_db.tar.gz
|
| Errata | are available here |
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:
DBI and PHP APIs for developing
command-line and Web-based applications
DBI API, and PHP's MySQL-related functions
| Title | MySQL & mSQL |
| Publisher | O'Reilly |
| Authors | Randy Jay Yarger, George Reese & Tim King |
| Pub Date | 1st Edition July 1999 |
| ISBN | 1-56592-434-7, Order Number: 4347 |
| Pages | 506 |
| Price | $34.95 |
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.
| Title | Sams Teach Yourself MySQL in 21 Days |
| Publisher | Sams |
| Authors | Mark Maslakowski and Tony Butcher |
| Pub Date | June 2000 |
| ISBN | 0672319144 |
| Pages | 650 |
| Price | $39.99 |
Sams Teach Yourself MySQL in 21 Days is for intermediate Linux users who want to move into databases. A large share of the audience to be web developers needing a database to store large amounts of information that can be retrieved via the Web. Sams Teach Yourself MySQL in 21 Days is a practical, step-by-step tutorial. The reader will learn to design and employ this open source database technology into his/her web site using practical, hands-on examples to follow.
| Title | E-Commerce Solutions with MySQL |
| Publisher | Prima Communications, Inc. |
| Authors | N/A |
| Pub Date | January 2000 |
| ISBN | 0761524452 |
| Pages | 500 |
| Price | $39.99 |
No description available.
| Title | Professional MySQL Programming |
| Publisher | Wrox Press, Inc. |
| Authors | N/A |
| Pub Date | July 2000 |
| ISBN | 1861004281 |
| Pages | 1000 |
| Price | $49.99 |
No description available.
| Title | PHP3 and MySQL Web Development |
| Publisher | N/A |
| Authors | William Jason Gilmore |
| Pub Date | October 2000 |
| ISBN | 0672317842 |
| Pages | N/A |
| Price | $49.99 |
No description available.
The following list describes some of the important characteristics of MySQL:
FLOAT, DOUBLE, CHAR,
VARCHAR, TEXT, BLOB, DATE,
TIME, DATETIME, TIMESTAMP,
YEAR, SET and ENUM types. See section
7.3
Column types.
SELECT and
WHERE parts of queries. Example: mysql> SELECT CONCAT(first_name, " ", last_name) FROM tbl_name
WHERE income/dependents > 10000 AND age > 30;
GROUP BY and ORDER BY
clauses. Support for group functions (COUNT(),
COUNT(DISTINCT), AVG(), STD(),
SUM(), MAX() and MIN()).
LEFT OUTER JOIN with ANSI SQL and ODBC syntax.
CHAR or VARCHAR field.
INSERT to insert
a subset of a table's columns; those columns that are not explicitly given
values are set to their default values.
libtool for portability.
purify).
myisamchk, a very fast utility for table checking,
optimization and repair. See section 15
Maintaining a MySQL installation.
DELETE, INSERT, REPLACE, and
UPDATE return how many rows were changed (affected). It is
possible to return the number of rows matched instead by setting a flag when
connecting to the server.
ABS is a valid column name. The only restriction is that for a
function call, no spaces are allowed between the function name and the
`(' that follows it. See section 7.35
Is MySQL picky about reserved words?.
--help or -? options to obtain online assistance.
SHOW command can be used
to retrieve information about databases, tables and indexes. The
EXPLAIN command can be used to determine how the optimizer
resolves a query. This section addresses the questions, ``How stable is MySQL?'' and, ``Can I depend on MySQL in this project?'' 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.23.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 F 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:
mysql, mysqladmin and
mysqlshow, mysqldump, and mysqlimport.
fcntl()). In these cases, you should
run the MySQL daemon with the --skip-locking
flag. Problems are known to occur on some Linux systems, and on SunOS when
using NFS-mounted file systems.
fcntl() call, which
is fixed by using the --skip-locking option to
mysqld. Some people have reported lockup problems with the 0.5
release. LinuxThreads will need to be recompiled if you plan to use 1000+
concurrent connections. Although it is possible to run that many connections
with the default LinuxThreads (however, you will never go above 1021), the
default stack spacing of 2 MB makes the application unstable, and we have been
able to reproduce a coredump after creating 1021 idle connections. See Linux
Notes for more details.
SELECT statements are usually done in one time frame so there
shouldn't be a mutex locking/thread juggling.
LOAD DATA ..., INSERT ... SELECT --
Stable
ALTER TABLE -- Stable
mysqlaccess -- Stable
GRANT -- Stable
MySQL AB 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.
MySQL itself has no problems with Year 2000 (Y2K) compliance:
2069; all 2-digit years are regarded to be in the
range 1970 to 2069, which means that if you store
01 in a year column, MySQL treats
it as 2001.
YEAR column type can store years 0 and
1901 to 2155 in 1 byte and display them using 2 or 4
digits. You may run into problems with applications that use MySQL
in a way that is not Y2K-safe. For example, many old applications store or
manipulate years using 2-digit values (which are ambiguous) rather than 4-digit
values. This problem may be compounded by applications that use values such as
00 or 99 as ``missing'' value indicators.
Unfortunately, these problems may be difficult to fix, because different applications may be written by different programmers, each of whom may use a different set of conventions and date-handling functions.
Here is a simple demonstration illustrating that MySQL doesn't have any problems with dates until the year 2030!
mysql> DROP TABLE IF EXISTS y2k;
mysql> CREATE TABLE y2k (date date, date_time datetime, time_stamp timestamp);
mysql> INSERT INTO y2k VALUES ("1998-12-31","1998-12-31 23:59:59",19981231235959);
mysql> INSERT INTO y2k VALUES ("1999-01-01","1999-01-01 00:00:00",19990101000000);
mysql> INSERT INTO y2k VALUES ("1999-09-09","1999-09-09 23:59:59",19990909235959);
mysql> INSERT INTO y2k VALUES ("2000-01-01","2000-01-01 00:00:00",20000101000000);
mysql> INSERT INTO y2k VALUES ("2000-02-28","2000-02-28 00:00:00",20000228000000);
mysql> INSERT INTO y2k VALUES ("2000-02-29","2000-02-29 00:00:00",20000229000000);
mysql> INSERT INTO y2k VALUES ("2000-03-01","2000-03-01 00:00:00",20000301000000);
mysql> INSERT INTO y2k VALUES ("2000-12-31","2000-12-31 23:59:59",20001231235959);
mysql> INSERT INTO y2k VALUES ("2001-01-01","2001-01-01 00:00:00",20010101000000);
mysql> INSERT INTO y2k VALUES ("2004-12-31","2004-12-31 23:59:59",20041231235959);
mysql> INSERT INTO y2k VALUES ("2005-01-01","2005-01-01 00:00:00",20050101000000);
mysql> INSERT INTO y2k VALUES ("2030-01-01","2030-01-01 00:00:00",20300101000000);
mysql> INSERT INTO y2k VALUES ("2050-01-01","2050-01-01 00:00:00",20500101000000);
mysql> SELECT * FROM y2k;
+------------+---------------------+----------------+
| date | date_time | time_stamp |
+------------+---------------------+----------------+
| 1998-12-31 | 1998-12-31 23:59:59 | 19981231235959 |
| 1999-01-01 | 1999-01-01 00:00:00 | 19990101000000 |
| 1999-09-09 | 1999-09-09 23:59:59 | 19990909235959 |
| 2000-01-01 | 2000-01-01 00:00:00 | 20000101000000 |
| 2000-02-28 | 2000-02-28 00:00:00 | 20000228000000 |
| 2000-02-29 | 2000-02-29 00:00:00 | 20000229000000 |
| 2000-03-01 | 2000-03-01 00:00:00 | 20000301000000 |
| 2000-12-31 | 2000-12-31 23:59:59 | 20001231235959 |
| 2001-01-01 | 2001-01-01 00:00:00 | 20010101000000 |
| 2004-12-31 | 2004-12-31 23:59:59 | 20041231235959 |
| 2005-01-01 | 2005-01-01 00:00:00 | 20050101000000 |
| 2030-01-01 | 2030-01-01 00:00:00 | 20300101000000 |
| 2050-01-01 | 2050-01-01 00:00:00 | 00000000000000 |
+------------+---------------------+----------------+
13 rows in set (0.00 sec)
This shows that the DATE and DATETIME types are
will not give any problems with future dates (they handle dates until the year
9999).
The TIMESTAMP type, that is used to store the current time, has
a range up to only 2030-01-01. TIMESTAMP has a range
of 1970 to 2030 on 32-bit machines (signed value). On
64-bit machines it handles times up to 2106 (unsigned value).
Even though MySQL is Y2K-compliant, it is your responsibility to provide unambiguous input. See section 7.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).
This book has been recommended by a several people on the MySQL mailing list:
Judith S. Bowman, Sandra L. Emerson and Marcy Darnovsky The Practical SQL Handbook: Using Structured Query Language Second Edition Addison-Wesley ISBN 0-201-62623-3 http://www.awl.com
This book has also received some recommendations 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
Apart from the following links, you can find and download a lot of MySQL programs, tools and APIs from the Contrib directory.
MySQL with other programsmSQL
mSQL Tcl
DBI/DBD.
DBI/DBD modules homepage
There are also many web pages that use MySQL. See section B Some MySQL users. Send any additions to this list to webmaster@mysql.com. We now require that you show a MySQL logo somewhere (It is okay to have it on a ``used tools'' page or something similar) to be added.
To subscribe to the main MySQL mailing list, send a message to the electronic mail address mysql-subscribe@lists.mysql.com.
To unsubscribe from the main MySQL mailing list, send a message to the electronic mail address mysql-unsubscribe@lists.mysql.com.
Only the address to which you send your messages is significant. The subject line and the body of the message are ignored.
If your reply address is not valid, you can specify your address explicitly.
Adding a hyphen to the subscribe or unsubscribe command word, followed by your
address with the `@' character in your address replaced by a
`='. For example, to subscribe john@host.domain, send
a message to mysql-subscribe-john=host.domain@lists.mysql.com.
Mail to mysql-subscribe@lists.mysql.com or mysql-unsubscribe@lists.mysql.com is handled automatically by the ezmlm mailing list processor. Information about ezmlm is available at The ezmlm Website.
To post a message to the list itself, send your message to
mysql@lists.mysql.com. However, please do not send mail
about subscribing or unsubscribing to mysql@lists.mysql.com, because any mail
sent to that address is distributed automatically to thousands of other users.
Your local site may have many subscribers to mysql@lists.mysql.com. If so, it may
have a local mailing list, so that messages sent from
lists.mysql.com to your site are propagated to the local list. In
such cases, please contact your system administrator to be added to or dropped
from the local MySQL list.
The following MySQL mailing lists exist:
announce
mysql
mysql-digest
mysql list in digest form. That means you get all
individual messages, sent as one large mail message once a day.
bugs
mysqlbug script (if you are running on Windows, you should
include a description of the operating system and the MySQL
version). Preferably, you should test the problem using the latest stable or
development version of MySQL before posting! Anyone should be
able to repeat the bug by just using 'mysql test < script' on the included
test case. All bugs posted on this list will be corrected or documented in the
next MySQL release! If there are only small code changes
involved, we will also post a patch that fixes the problem.
bugs-digest
bugs list in digest form
developer
developer-digest
developer list.
java
java-digest
java list.
win32
win32-digest
win32 list.
myodbc
myodbc-digest
myodbc list.
plusplus
plusplus-digest
plusplus list.
msql-mysql-modules
msql-mysql-modules-digest
msql-mysql-modules list. You subscribe or unsubscribe to all lists in the same way as described above.
In your subscribe or unsubscribe message, just put the appropriate mailing list
name rather than mysql. For example, to subscribe to or unsubscribe
from the myodbc list, send a message to myodbc-subscribe@lists.mysql.com
or myodbc-unsubscribe@lists.mysql.com.
Before posting a bug report or question, please do the following:
http://www.mysql.com/documentation/manual.phpWe try to keep the manual up to date by updating it frequently with solutions to newly found problems!
http://www.mysql.com/documentation/
If you can't find an answer in the manual or the archives, check with your local MySQL expert. If you still can't find an answer to your question, go ahead and read the next section about how to send mail to mysql@lists.mysql.com.
Writing a good bug report takes patience, but doing it right the first time saves time for us and for you. A good bug report containing a full test case for the bug will make it very likely that we will fix it in the next release. This section will help you write your report correctly so that you don't waste your time doing things that may not help us much or at all.
We encourage everyone to use the mysqlbug script to generate a
bug report (or a report about any problem), if possible. mysqlbug
can be found in the `scripts' directory in the source distribution, or,
for a binary distribution, in the `bin' directory under your
MySQL installation directory. If you are unable to use
mysqlbug, you should still include all the necessary information
listed in this section.
The mysqlbug script helps you generate a report by determining
much of the following information automatically, but if something important is
missing, please include it with your message! Please read this section carefully
and make sure that all the information described here is included in your
report.
If you can make a test case which clearly shows the bug, you should post it
to the bugs@list.mysql.com list. Note
that on this list you should only post a full, repeatable bug report, using the
mysqlbug script (if you are running on Windows, you should include
a description of the operating system and the MySQL version).
Preferably, you should test the problem using the latest stable or development
version of MySQL before posting! Anyone should be able to
repeat the bug by just using 'mysql test < script' on the included test case
or run the shell / perl script that is included in the bug report. All bugs
posted on this list will be corrected or documented in the next
MySQL release! If there are only small code changes involved,
to correct this problem, we will also post a patch that fixes the problem.
Remember that it is possible to respond to a message containing too much information, but not to one containing too little. Often people omit facts because they think they know the cause of a problem and assume that some details don't matter. A good principle is: if you are in doubt about stating something, state it! It is a thousand times faster and less troublesome to write a couple of lines more in your report than to be forced to ask again and wait for the answer because you didn't include enough information the first time.
The most common errors are that people don't indicate the version number of the MySQL distribution they are using, or don't indicate what platform they have MySQL installed on (including the platform version number). This is highly relevant information and in 99 cases out of 100 the bug report is useless without it! Very often we get questions like ``Why doesn't this work for me?'' and then we find that the feature requested wasn't implemented in that MySQL version, or that a bug described in a report has been fixed already in newer MySQL versions. Sometimes the error is platform dependent; in such cases, it is next to impossible to fix anything without knowing the operating system and the version number of the platform.
Remember also to provide information about your compiler, if it is related to the problem. Often people find bugs in compilers and think the problem is MySQL related. Most compilers are under development all the time and become better version by version. To determine whether or not your problem depends on your compiler, we need to know what compiler is used. Note that every compiling problem should be regarded as a bug report and reported accordingly.
It is most helpful when a good description of the problem is included in the bug report. That is, a good example of all the things you did that led to the problem and the problem itself exactly described. The best reports are those that include a full example showing how to reproduce the bug or problem.
If a program produces an error message, it is very important to include the message in your report! If we try to search for something from the archives using programs, it is better that the error message reported exactly matches the one that the program produces. (Even the case sensitivity should be observed!) You should never try to remember what the error message was; instead, copy and paste the entire message into your report!
If you have a problem with MyODBC, you should try to genereate a MyODBC trace file. See section 18.6 Reporting problems with MyODBC.
Please remember that many of the people who will read your report will do so
using an 80-column display. When generating reports or examples using the
mysql command line tool, you should therefore use the
--vertical option (or the \G statement terminator) for
output which would exceed the available width for such a display (for example,
with the EXPLAIN SELECT statement; see the example below).
Please include the following information in your report:
mysqladmin version.
mysqladmin can be found in the `bin' directory under
your MySQL installation directory.
uname -a.
mysqldump --no-data db_name tbl_name1 tbl_name2 .... This is very
easy to do and is a powerful way to get information about any table in a
database that will help us create a situation matching the one you have.
SELECT statements,
you should always include the output of EXPLAIN SELECT ..., and
at least the number of rows that the SELECT statement produces.
The more information you give about your situation, the more likely it is that
someone can help you! For example, the following is an example of a very good
bug report (it should of course be posted with the mysqlbug
script): Example run using the mysql command line tool (note the
use of the \G statement terminator for statements whose output
width would otherwise exceed that of an 80-column display device): mysql> SHOW VARIABLES;
mysql> SHOW COLUMNS FROM ...\G
<output from SHOW COLUMNS>
mysql> EXPLAIN SELECT ...\G
<output from EXPLAIN>
mysql> FLUSH STATUS;
mysql> SELECT ...;
<A short version of the output from SELECT,
including the time taken to run the query>
mysql> SHOW STATUS;
<output from SHOW STATUS>
mysqladmin variables extended-status
processlist in your mail to provide some information of how your system
is performing!
ftp to transfer it
to ftp://support.mysql.com/pub/mysql/secret/.
If the data are really top secret and you don't want to show them even to us,
then go ahead and provide an example using other names, but please regard this
as the last choice.
mysqld daemon and that you use to run any MySQL
client programs. The options to programs like mysqld and
mysql, and to the configure script are often keys to
answers and very relevant! It is never a bad idea to include them anyway! If
you use any modules, such as Perl or PHP, please include the version number(s)
of those as well.
mysqldump and create a `README' file
that describes your problem. Create a compressed archive of your files using
tar and gzip or zip, and use
ftp to transfer the archive to ftp://support.mysql.com/pub/mysql/secret/.
Then send a short description of the problem to mysql@lists.mysql.com.
mysqlaccess, the output of mysqladmin
reload and all the error messages you get when trying to connect! When
you test your privileges, you should first run mysqlaccess. After
this, execute mysqladmin reload version, and last you should try
to connect with the program that gives you trouble. mysqlaccess
can be found in the `bin' directory under your MySQL
installation directory.
parse error, please check your syntax closely!
If you can't find something wrong with it, it's extremely likely that your
current version of MySQL doesn't support the query you are
using. If you are using the current version and the manual at http://www.mysql.com/documentation/manual.php
doesn't cover the syntax you are using, MySQL doesn't support
your query. In this case, your only options are to implement the syntax
yourself or email mysql-licensing@mysql.com and ask
for an offer to implement it! If the manual covers the syntax you are using,
but you have an older version of MySQL, you should check the
MySQL change history to see when the syntax was implemented.
See section E MySQL
change history. In this case, you have the option of upgrading to a newer
version of MySQL.
myisamchk or CHECK
TABLE/ REPAIR TABLE. See section 15
Maintaining a MySQL installation.
mysqld should NEVER crash a table if nothing
killed it in the middle of an update! If you can find the source of why
mysqld dies, it's much easier for us to provide you with a fix
for the problem! See section 20.1
How to determinate what is causing problems.
If you are a support customer, please cross-post the bug report to mysql-support@mysql.com for higher priority treatment, as well as to the appropriate mailing list to see if someone else has experienced (and perhaps solved) the problem.
For information on reporting bugs in MyODBC, see section 18.3 How to report problems with MyODBC.
For solutions to some common problems, see See section 20 Problems and common errors.
When answers are sent to you individually and not to the mailing list, it is considered good etiquette to summarize the answers and send the summary to the mailing list so that others may have the benefit of responses you received that helped you solve your problem!
If you consider your answer to have broad interest, you may want to post it to the mailing list instead of replying directly to the individual who asked. Try to make your answer general enough that people other than the original poster may benefit from it. When you post to the list, please make sure that your answer is not a duplication of a previous answer.
Try to summarize the essential part of the question in your reply; don't feel obliged to quote the entire original message.
Please don't post mail messages from your browser with HTML mode turned on! Many users don't read mail with a browser!
This chapter describes MySQL support and licensing arrangements:
The formal terms of the GPL license can be found at section K GNU General Public License. Basically, our licensing policy and intepretation of the GPL is as follows:
Note that older versions of MySQL are still using a more strict license. See the documentation for that version for more information. If you need a commercial MySQL license, because the GPL license doesn't suit your application, you can buy one at https://order.mysql.com/license.htmy.
For normal internal use, MySQL costs nothing. You do not have to pay us if you do not want to.
MySQL and you don't provide the
source code for your copy of the MySQL server, as defined in
the GPL license. GNU Library General Public License. The mysql
command line client includes code from the readline library that
is under the GPL.
For circumstances under which a MySQL license is required,
you need a license per machine that runs the mysqld server.
However, a multiple-CPU machine counts as a single machine, and there is no
restriction on the number of MySQL servers that run on one
machine, or on the number of clients concurrently connected to a server running
on that machine!
If you have any questions as to whether or not a license is required for your particular use of MySQL, please read this again and then contact us. See section 3.6.2 Contact information.
If you require a MySQL license, the easiest way to pay for it is to use the license form on MySQL's secure server at https://order.mysql.com/license.htmy. Other forms of payment are discussed in section 3.6.1 Payment information.
There are several different copyrights on the MySQL distribution:
mysqlclient library is licensed under the LGPL and
programs in the `client' directory is GPL. Each file has a header
that shows which copyright is used for that file.
getopt) library are covered
by the ``GNU LIBRARY GENERAL PUBLIC LICENSE''.
regexp library) are covered by
a Berkeley-style copyright.
readline) library
is covered by the ``GNU GENERAL PUBLIC LICENSE''. See section K
GNU General Public License. This is also available as the file
`COPYING' in the distributions. One goal with this is that the SQL client library should be free enough so that it is possible to add MySQL support in commercial products without any license. So in this case we choose the LGPL license.
This means that you can use MySQL for free with any program that uses any of the free software licences. MySQL is also free for any end user for his own or company usage.
But if you use MySQL for something important to you, you may want to help secure its development by purchasing licenses or a support contract, section 3.7 Types of commercial support.
The stable versions of MySQL are still using a more strict license. See the documatation for that version for more information.
This section describes some situations illustrating whether or not you must license the MySQL server. Generally these examples involve providing MySQL as a integrated part of a product.
Note that a single MySQL license covers any number of CPUs
and mysqld servers on a machine! There is no artificial limit on
the number of clients that connect to the server in any way.
To determine whether or not you need a MySQL license when selling your application, you should ask whether the proper functioning of your application is dependent on the use of MySQL and whether you include the MySQL server with your product. There are several cases to consider:
mysqld server. For example, if you've
designed your application around MySQL, then you've really
made a commercial product that requires the engine, so you need a license. If
your application does not require MySQL, you need not obtain
a license. For example, if MySQL just added some new optional
features to your product (such as adding logging to a database if
MySQL is used rather than logging to a text file), it should
fall within normal use, and a license would not be required. In other words,
you need a license if you sell a product designed specifically for use with
MySQL or that requires the MySQL server to
function at all. This is true whether or not you provide
MySQL for your client as part of your product distribution.
It also depends on what you're doing for the client. Do you plan to provide
your client with detailed instructions on installing MySQL
with your software? Then your product may be contingent on the use of
MySQL; if so, you need to buy a license. If you are simply
tying into a database that you expect already to have been installed by the
time your software is purchased, then you probably don't need a license.
Internet Service Providers (ISPs) often host MySQL servers for their customers. With the GPL license this does not require a license.
On the other hand, we do encourage people to use ISPs that have MySQL support as this will give them the confidence that if they have some problem with their MySQL installation, their ISP will be able to solve the problem for them (in some cases with the help from the MySQL development team).
All ISPs that wants to keep themself up to date should subscribe to our
announce mailing list so that they can be aware of fatal issues that may be
relevant for their MySQL installations.
Note that if the ISP doesn't have a license for MySQL, they
should give their customers at least read access to the source of the
MySQL installation so that their customer can verify that it's
patched correctly.
If you use MySQL in conjunction with a web server on Unix, you don't have to pay for a license.
This is true even if you run a commercial web server that uses MySQL, because you are not selling an embedded MySQL version yourself. However, in this case we would like you to purchase MySQL support, because MySQL is helping your enterprise.
Our current license prices are shown below. These prices are now under review because of the change to a GPL copyright. New prices and terms will be posted on the MySQL web site http://www.mysql.com/ as soon as they are ready.
All prices are in US Dollars. If you pay by credit card, the currency is EURO (European Union Euro) so the prices will differ slightly.
| 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. Note that OEM licenses only apply for products where the user doesn't have direct access to the MySQL server (embedded system). In other words: The MySQL server should only be used with the application that was supplied you.
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.
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.7 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 ``MySQL Finland AB'' 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 MySQL AB'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 sales@mysql.com (not
mysql@lists.mysql.com!) 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 mysql-licensing@mysql.com. Fax is also possible but handling of 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. We at MySQL do give a lot of free support in all the different MySQL lists and the money we get from basic email support is largely used to make this possible.
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, 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 mysql-support@mysql.com.
Note that if you have encountered a critical repeatable bug and follow the rules outlined in the manual section of how to report bugs and send it to bugs@lists.mysql.com we promise to try to fix this as soon as possible, regardless of your support level! See section 2.3 How to report bugs or problems.
Basic email support includes the following types of service:
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_FUNC(col1,col2) from table;
This chapter describes how to obtain and install MySQL:
Check the MySQL home page for information about the current version and for downloading instructions.
If you have problems downloading from our main site, try using one of the mirrors listed below.
Please report bad or out of date mirrors to webmaster@mysql.com.
Europe:
North America:
South America:
Asia:
Australia:
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.
glibc 2.0.7+ . See
section 4.11.5
Linux notes (all Linux versions).
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 12.7
Using your own benchmarks. Note that all MySQL releases
are checked with the MySQL benchmarks and an extensive test
suite before each release.
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
22) and the other is the development branch (currently
23) . 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 E
MySQL change history. There are also new commands and extensions in most
alpha releases. Active development that may involve major code changes can
occur on an alpha release, but everything will be tested before doing a
release. There should be no known bugs in any MySQL release.
beta means that all new code has been tested. No major new
features that could cause corruption on old code are added. There should be
no known bugs. A version changes from alpha to beta when there haven't been
any reported fatal bugs within an alpha version for at least a month and we
don't plan to add any features that could make any old command more
unreliable.
gamma is a beta that has been around a while and seems to
work fine. Only minor fixes are added. 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. Because 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 MySQL AB 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 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 E 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 can create your own binary installation from a compiled source distribution, by executing the script `scripts/make_binary_distribution'.
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. Sun tar is known to have
problems. 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. See section A
Environment variables.
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' daemon 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, you 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 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.gz 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.gz 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. Sun tar is known to have
problems.
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. gcc >= 2.95.2 is
recommended when compiling MySQL 3.23.x.
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.23.22-beta.
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 mysql@lists.mysql.com to ask for
assistance, please include any 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 21.4
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 Web 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. 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 optionsThe 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. See section A
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 10.1.1
The character set used for data and sorting. 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.29
SET 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 H.1 Debugging a MySQL server.
--with-thread-safe-client; this forces the library to use thread
safe functions calls for some functions that are not thread safe by default.
You pay a very small performance penalty by doing this, but generally it's
quite safe to use this option.
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, because 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 MySQL 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
gcc 2.95.2 or egcs 1.0.3a 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, because 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
H.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. Because 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 22.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/Downloads/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
ftp://ftp.de.uu.net/pub/CPAN/authors/id/JWIED/DBD-mysql-1.2212.x86.ppd
The above should work at least with ActiveState Perl 5.6.
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 interfaceIf 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
gcc 2.95.2. You can find this at http://gcc.gnu.org/. Note that egs
1.1.1 and gcc 2.8.1 don't work reliably on SPARC!
The recommended configure line when using gcc
2.95.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
gcc 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. See section 4.15.4
Command-line options.
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.
When using the --with-libwrap configure option, you must also
include the libraries that libwrap.a needs:
--with-libwrap="/opt/NUtcpwrapper-7.6/lib/libwrap.a -lnsl -lsocket
If you have problems with configure trying to link with -lz and
you don't have zlib installed, you have two options:
--with-named-z-libs=no If you are using gcc and have problems with loading UDF
functions into MySQL, try adding -lgcc to the link
line for the UDF function.
You can normally use a Solaris 2.6 binary on Solaris 2.7 and 2.8. Most of the Solaris 2.6 issues also apply for Solaris 2.7 and 2.8.
Note that MySQL 3.23.4 and above should be able to autodetect new versions of Solaris and enable workarounds for the following problems!
Solaris 2.7 / 2.8 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 get a problem that your linker can't find -lz when
linking your client program, the problem is probably that your
`libz.so' file is installed in `/usr/local/lib'. You can fix
this by one of the following methods:
LD_LIBRARY_PATH
libz.so from /lib
--with-named-z-libs=no option. 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 H.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 and the MySQL 3.22.32 contains a
temporary workaround for this bug.
If you plan to have 1000+ concurrent connections, you will need to make some changes to LinuxThreads, recompile it, and re-link MySQL against the new libpthread.a . Increase PTHREAD_THREADS_MAX in sysdeps/unix/sysv/linux/bits/local_lim.h to 4096 and decrease STACK_SIZE in internals.h to 256 KB . Note that MySQL will not be stable with around 1000 connections if STACK_SIZE is the default of 2 MB.
If you have glibc 2.1.3-65 or newer, you don't have to increase STACK_SIZE;
You can instead just change the thread_stack value for
mysqld.
If you use a lot of concurrent connections, you may suffer from a 2.2 kernel "feature" that penalizes a process for forking or cloning a child in an attempt to prevent a fork bomb attack. This will cause MySQL not to scale well as you increase the number of concurrent clients. On single CPU systems, we have seen this manifested in a very slow thread creation - which means it may take a long time to connect to MySQL ( as long as 1 minute), and it may take just as long to shut it down. On multiple CPU systems, we have observed a gradual drop in query speed as the number of clients increases. In the process of trying to find a solution, we have received a kernel patch from one of our users, who claimed it made a lot of difference for his site. We have done some limited testing in which the patch greatly improved the scalabitility of MySQL. The patch is available here - be warned, though, that we assume no reponsibility for any damage that this patch could do - use at your own risk. We have also been told by the Linux kernel developers that this problem is fixed in 2.4, although we have not yet done any testing.
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 12.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.
Note that the linux kernel and the linuxthread library can by default only have 1024 threads. This means that you can only have up to 1021 connections to MySQL on a unpatched system. The page http://www.volano.com/linuxnotes.html contains information how to go around this limit.
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 20.2
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 because 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 H.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/Downloads/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.
MySQL 3.23.12 is the first MySQL version that is tested on Linux-Alpha. If you plan to use MySQL on Linux-Alpha, you should ensure that you have this version or newer.
We have tested MySQL on Alpha with our benchmarks + test suite and it appears to work nicely. The main thing we haven't yet had time to test is how things works with many concurrent users.
When we compiled MySQL we where using SuSE 6.3, kernel 2.2.13-SMP, egcs 1.1.2 and libc-2.1.2-28.
We used the following configure line:
CFLAGS="-O6 -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O6 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --disable-shared
If you have access to Compaq's C compiler, the following configure line will give you about 9 % more performance:
CC=ccc CFLAGS="-fast -O3 -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O6 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mcpu=ev6 -Wa,-mev6" CXXLDFLAGS='/usr/lib/compaq/libots-2.2.7/libots.so /usr/lib/compaq/cpml-5.0.0/libcpml_ev6.a' ./configure --prefix=/usr/local/mysql --disable-shared
Note that the last example assumes your are using an Alpha EV6 processor.
Some known problems when running MySQL on Linux-Alpha:
test-create in the MySQL benchmarks, mysqld may
report the error Can't create/write to file '...' (Errcode: 12).
This is probably a bug in the Linux-Alpha kernel.
gdb 4.18. You should download and use gdb 5.0 instead!
--with-mysqld-ldflags=-all-static We don't yet know if the following old information is still relevant, so we leave this here until we have had time to test Linux-Alpha properly.
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, gcc 2.95.2 or newer).
To get MySQL to compile on Linux Ia64, we had to do the
following (we assume that this will be easier when next gcc version for ia64 is
released).
Using gcc-2.9-final:
CFLAGS="-O2" CXX=gcc CXXFLAGS="-O2 -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static --disable-shared --with-extra-charsets=complex
After this you have to edit `config.h' and remove the
HAVE_ATOMIC_ADD line.
After make you will get an error that
sql/opt_range.cc will not compile (internal compiler error). To fix
this, go to the sql directory and type make again. Copy the compile
line, but change -O2 to -O0. The file should now compile.
Now you can do:
cd .. make make_install
and mysqld should be ready to run.
If you are using egcs 1.1.2 on Digital UNIX, you should upgrade to gcc 2.95.2, as egcs on DEC has some serious bugs!
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 get a problem when compiling mysys/get_opt.c, just remove the line #define _NO_PROTO from the start of that file!
If you are using Compac's CC compiler, the following configure line 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/local/mysql \ --with-low-memory \ --enable-large-files \ --enable-shared=yes \ --with-named-thread-libs="-lpthread -lmach -lexc -lc" gnumake
If you get a problem with libtool, when compiling with shared libraries as
above, when linking mysql, you should be able to get around this by
issuing:
cd mysql /bin/sh ../libtool --mode=link cxx -pthread -O3 -DDBUG_OFF \ -O4 -ansi_alias -ansi_args -fast -inline speed \ -speculate all \ -arch host -DUNDEF_HAVE_GETHOSTBYNAME_R \ -o mysql mysql.o readline.o sql_string.o completion_hash.o \ ../readline/libreadline.a -lcurses \ ../libmysql/.libs/libmysqlclient.so -lm cd .. gnumake gnumake install scripts/mysql_install_db
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
Note that the following problems with the ld program can be
fixed by downloading the latest DEC (Compaq) patch kit from: http://ftp.support.compaq.com/public/unix/.
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.
With gcc 2.95.2, you will probably run into the following
compile error:
sql_acl.cc:1456: Internal compiler error in `scan_region', at except.c:2566 Please submit a full bug report.
To fix this you should change to the sql directory and do a 'cut
and paste' of the last gcc line, but change -O3 to
-O0 (or add -O0 immediately after gcc if
you don't have any -O option on your compile line. After this is
done you can just change back to the top level directly and run
make again.
If you are using Irix 6.5.3 or newer mysqld will only be able to
create threads if you run it as a user with CAP_SCHED_MGT
privileges (like root) or give the mysqld server this
privilege with the following shell command:
shell> chcap "CAP_SCHED_MGT+epi" /opt/mysql/libexec/mysqld
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 20.13 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 get problems with the current date in MySQL, setting
the TZ variable will probably help. See section A
Environment variables.
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 get problems with the current date in MySQL, setting
the TZ variable will probably help. See section A
Environment variables.
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.
On some BSDI 4.x systems, you may get problems with shared libraries. The
symptom is that you can't execute any client programs, like for example
mysqladmin. In this case you need to reconfigure not to use shared
libraries with the --disable-shared option to configure.
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 http://www.mysql.com/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.
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, because 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
If you want to use gcc, you must use gcc 2.95.2 or
newer.
Automatic detection of xlC is missing from Autoconf, so a
configure command something like this is needed when compiling
MySQL: (The example uses the IBM compiler)
shell> CC="xlc_r" CFLAGS="-ma -O3 -qstrict" \
CXX="xlC_r" CXXFLAGS="-ma -O3 -qstrict" \
./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 gcc or egcs to compile
MySQL, you MUST use the
-fno-exceptions flag, as the exception handling in
gcc/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> CC=gcc \
CXX=gcc \
CXXFLAGS="-felide-constructors -fno-exceptions -fno-rtti" \" \
./configure --prefix=/usr/local/mysql --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=/usr/local/mysql --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!
For HPUX 11.x we recommend MySQL 3.23.15 or later.
If you are using gcc 2.95.1 on a unpatched HPUX 11.x system you
will get the error:
In file included from /usr/include/unistd.h:11,
from ../include/global.h:125,
from mysql_priv.h:15,
from item.cc:19:
/usr/include/sys/unistd.h:184: declaration of C function `int pthread_atfork(void (*)(...), void (*)
(...), void (*)(...))' conflicts with
/usr/include/sys/pthread.h:440: previous declaration `int pthread_atfork(void (*)(), void (*)(), voi
d (*)())' here
In file included from item.h:306,
from mysql_priv.h:158,
from item.cc:19:
The problem is that HP-UX doesn't define pthreads_atfork()
consistently. It has conflicting prototypes in
`/usr/include/sys/unistd.h':184 and
`/usr/include/sys/pthread.h':440 (I post the details below).
One solution is to copy `/usr/include/sys/unistd.h' into `mysql/include' and edit `unistd.h' and change it to match the definition in `pthread.h'. Here's the diff:
183,184c183,184 < extern int pthread_atfork(void (*prepare)(), void (*parent)(), < void (*child)()); --- > extern int pthread_atfork(void (*prepare)(void), void (*parent)(void), > void (*child)(void));
After this, the following configure line should work:
CFLAGS="-fomit-frame-pointer -O6 -fpic" CXX=gcc CXXFLAGS="-felide-constructors -fno-exceptions -fno-rtti -O6" ./configure --prefix=/usr/local/mysql --disable-shared
Here is some information that a HPUX 11.x user sent us about compile MySQL with HPUX:x compiler
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.
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 MyODBC 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-xxxxx.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.5
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 an 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.
If mysqld doesn't start please check whether or not the
`\mysql\mysql.err' file contains any reason for this. You can also try
to start it with mysqld --standalone; In this case you may get some
useful information on the screen that may help solve this.
The last option is to start mysqld with --debug. In
this case mysqld will write a log file in `\mysqld.trace'
that should contain the reason why mysqld doesn't start. If you
make a bug report about this, please only send the lines where something seams
to go wrong to the mailing list!
The Win95/Win98 section also applies to MySQL on NT / Win2000, with the following differences:
To get MySQL to work with TCP/IP on NT, you must install service pack 3 (or newer)!
Note that everything in the following that applies for NT, also applies for Win2000!
For NT / Win2000, the server name is mysqld-nt. Normally you
should install MySQL as a service on NT/Win2000:
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 --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 old shareware version of MySQL 3.21
under Windows, the above command will fail with an error: parse error near
'SET OPTION password'. This is because the old shareware version, which
is based on MySQL 3.21, doesn't have the SET
PASSWORD command. The fix is in this case is to upgrade to the 3.22
shareware version.
With the newer MySQL versions you can easily add new users
and change privileges with GRANT and REVOKE commands.
See section 7.30
GRANT and REVOKE syntax.
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!
On windows MySQL 3.23.16 and above is compiled with the
-DUSE_SYMDIR option. This allows you to put a database on different
disk by adding a symbolic link to it (in a similar manner that symbolic links
works on Unix).
On windows you make a symbolic link to a database by creating a file that
contains the path to the destination directory and saving this in the
mysql_data directory under the filename database.sym.
Note that the symbolic link will only be used if the directory
mysql_data_dir\database doesn't exist.
For example if you want to have database foo on
`D:\data\foo' you should create the file
`C:\mysql\data\foo.sym' that should contains the text
D:\data\foo. After this, all tables created in the database
foo will be created in `D:\data\foo'.
In your source files, you should include `windows.h' before you
include mysql.h:
#if defined(_WIN32) || defined(_WIN64) #include <windows.h> #endif #include <mysql.h>
You can either link your code with the dynamic `libmysql.lib' library, which is just a wrapper to load in `libmysql.dll' on demand, or link with the static `mysqlclient.lib' library.
Note that as the mysqlclient libraries are compiled as threaded libraries, you should also compile your code to be multi-threaded!
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, because each connection in
MySQL creates a new thread! WinNT and Win98 don't suffer from
this bug.
pread() and
pwrite() calls to be able to mix INSERT and
SELECT. As windows doesn't support these calls,
MySQL can't currently handle concurrent reads on windows. We
plan to fix this by adding an extra mutex to each open file and simulate
pread()/pwrite().
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 * INTO OUTFILE 'C:/tmp/skr.txt' FROM skr;
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, we at MySQL AB provides a set of binary distributions of MySQL that are compiled at our site 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 --with-extra-charsets=complex
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
--with-extra-charsets=complex
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
--with-extra-charsets=complex
gcc 2.8.1
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
--with-low-memory --with-extra-charsets=complex
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
--with-extra-charsets=complex
gcc 2.95.2
CFLAGS="-O6 -mpentiumpro -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O6
-mpentiumpro -fomit-frame-pointer -felide-constructors -fno-exceptions
-fno-rtti" ./configure --prefix=/usr/local/mysql --enable-assembler
--with-mysqld-ldflags=-all-static --disable-shared
--with-extra-charset=complex
gcc 2.7-95q4
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
--with-extra-charsets=complex
gcc 2.7.2.2
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
--with-extra-charsets=complex
gcc 2.8.1
CC=gcc CFLAGS=-O CXX=gcc CXXFLAGS=-O3 ./configure
--prefix=/usr/local/mysql --with-low-memory
--with-extra-charsets=complex
gcc 2.8.0
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
--with-extra-charsets=complex
gcc 2.7.2.1
CC=gcc CXX=gcc CXXFLAGS=-O ./configure --prefix=/usr/local/mysql
--with-extra-charsets=complex
gcc 2.7.2
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
--with-extra-charsets=complex Anyone who has more optimal options for any of the configurations listed above can always mail them to the developer's mailing list at developer@lists.mysql.com.
RPM distributions prior to MySQL 3.22 are user-contributed. Beginning with 3.22, some RPMs are generated by us at MySQL AB.
If you want to compile a debug version of MySQL, you should
add --with-debug or --with-debug=full to the above
configure lines and remove any -fomit-frame-pointer options.
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 * Command-line options:: Command-line options 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 20.10
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_dbThis 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 &The environment variables appendix includes a list of other environment variables you can use to affect
mysqld. See section A
Environment variables. 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. See section A Environment variables. 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 as a service as follows: bin\mysqld-nt --install # Install MySQL as a serviceYou can now start/stop
mysqld as follows: NET START mysql NET STOP mysqlNote that in this case you can't use any other options for
mysqld! You can remove the service as follows: bin\mysqld-nt --remove # remove MySQL as a service
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),
`\mysql\mysql.err' on Windows. 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
If you find in the log file something like: the following:
000729 14:50:10 bdb: Recovery function for LSN 1 27595 failed 000729 14:50:10 bdb: warning: ./test/t1.db: No such file or directory 000729 14:50:10 Can't init databases
This means that you started mysqld with --bdb-recover and that
Berkeley DB found something wrong with it's log files when it tried to recover
your databases. To be able to continue, you should move away the old Berkeley DB
log file from the database directory to some other place, where you can later
examine these. The log files are named `log.0000000001', where the
number will increase over time.
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 an 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 21.4
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.
Because 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.
On Windows, you can try to start mysqld as follows:
C:\mysql\bin\mysqld --standalone --debug
This will not run in the background and it should also write a trace in `\mysqld.trace', which may help you determine the source of your problems. See section 4.12 Win32 notes.
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.5 Option files.
mysqld accepts the following command-line options:
--ansi
-b, --basedir=path
--big-tables
--bind-address=IP
--character-sets-dir=path
--chroot=path
LOAD DATA INFILE and SELECT ... INTO
OUTFILE though.
-h, --datadir=path
--default-character-set=charset
--default-table-type=type
--delay-key-write-for-all-tables
MyISAM table.
See section 12.2.3
Tuning server parameters.
--enable-locking
-T, --exit-info
--flush
-?, --help
--init-file=file
-L, --language=...
-l, --log[=file]
--log-update[=file]
file.# where # is a unique number
if not given. See section 21.3
The update log.
--log-isam[=file]
--log-long-format
--low-priority-updates
INSERT/DELETE/UPDATE) will have lower
priority than selects. It can also be done via {INSERT | REPLACE |
UPDATE | DELETE} LOW_PRIORITY ... to lower the priority of only one
query, or by SET OPTION SQL_LOW_PRIORITY_UPDATES=1 to change the
priority in one thread. See section 12.2.9
Table locking issues.
--pid-file=path
safe_mysqld.
-P, --port=...
-o, --old-protocol
--one-thread
-O, --set-variable var=option
--help lists variables. You can
find a full description for all variables in the SHOW VARIABLES
section in this manual. See section 7.24.4
SHOW VARIABLES. The tuning server parameters section includes information
of how to optimize these. See section 12.2.3
Tuning server parameters.
-Sg, --skip-grant-tables
mysqladmin
flush-privileges or mysqladmin reload.)
--safe-mode
--skip-delay-key-write.
--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 pretending to be another
host. This option also adds some sanity checks of hostnames. The option is
turned off by default in MySQL 3.21 because sometimes it
takes a long time to perform backward resolutions. MySQL 3.22
caches hostnames (unless --skip-host-cache is used) and has this
option enabled by default.
--skip-delay-key-write
delay_key_write option for all tables. See section
12.2.3
Tuning server parameters.
--skip-locking
isamchk or
myisamchk one has to shut down the server. See section 1.6
How stable is MySQL?. Note that in MySQL 3.23 one can use
REPAIR and CHECK to repair/check MyISAM
tables.
--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 highly
recommended for systems where only local requests are allowed. However, this
option is unsuitable for systems that use MIT-pthreads, because the
MIT-pthreads package doesn't support Unix sockets.
--skip-new
--skip-delay-key-write. This will also set default table type to
ISAM. See section 8.2 ISAM
tables.
--skip-host-cache
--skip-show-database
--skip-thread-priority
--socket=path
/tmp/mysql.sock.
-t, --tmpdir=path
/tmp directory resides on a partition too small to hold temporary
tables.
-u, --user=user_name
mysqld daemon as user user_name. This option
is mandatory when starting mysqld as root.
-V, --version
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. See section A Environment variables.
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_size=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 sample
configuration files named `my-xxxx.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'). Currently there is sample configuration files for
small, medium, large and very large systems. You can copy `my-xxxx.cnf'
to your home directory (rename the copy to `.my.cnf') to experiment
with this.
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 sp