MySQL Reference Manual for version 3.21.30.
This is the MySQL reference manual. This version documents the
3.21.30 version of MySQL.
MySQL is a basically free SQL database server. See section 3 Licensing or When do I have/want to pay for MySQL?.
The latest information about MySQL is found at
the MySQL Home page
To see what it can do. See section 1.4 The main features of MySQL.
For installation instructions See section 4 Compiling and installing MySQL. For tips on how to port
MySQL to new machines/operating systems See section G Comments on porting to other systems..
If you have any suggestions concerning additions or corrections to this
manual, please send them to the MySQL mailing list
documentation suggestion: [Insert Topic Here].
See section 2.1 Subscribing to/un-subscribing from the MySQL mailing list..
See section 8.5.1 Upgrading to 3.21 from a 3.20 version, for information about upgrading from a 3.20
release.
For examples of SQL and benchmarking information see the `bench'
directory.
For future plans See section F List of things we want to add to MySQL in the future..
A history of new features/bug fixes See section D MySQL change history.
For the currently known bugs/misfeatures (known errors) See section E Known errors and design deficiencies in MySQL.
For A list of all the contributors to this product See section C Who has helped to make MySQL..
IMPORTANT:
Send bug (error) reports, questions and comments to the mailing list at
Please use the mysqlbug script when posting bug reports or
questions about MySQL. mysqlbug will gather some
information about your system and start your editor with a form in which
you can describe your problem. Bug reports might be silently ignored by
the MySQL maintainers if there is not a good reason included in
the report as to why mysqlbug has not been used. A report that says
'MySQL does not work for me. Why?' is not consider a valid bug report.
The mysqlbug script can be found in the `scripts' directory in the
distribution, that is `there-you-installed-mysql/scripts'.
MySQL is a SQL (Structured Query Language) database server.
SQL is the most popular database language in the world. MySQL
is a client server implementation that consists of a server daemon
mysqld and many different client programs/libraries.
The main goals of MySQL are speed and robustness.
The base upon which MySQL is built is a set of routines that
have been used in a highly demanding production environment for many
years. While MySQL is still in development it already offers a
rich and highly useful function set.
The official way to pronounce MySQL is 'My Ess Que Ell' (Not
MY-SEQUEL).
This manual is currently available in TeXInfo, Raw text, Info and
HTML versions. A PostScript version as available do download separately
because of its size.
The primary document is the TeXInfo file. The HTML version is
automatically produced with a modified texi2html. The ASCII and
info version are produced with makeinfo. The Postscript version
is produced using texi2dvi and dvips.
This manual is written and maintained by David Axmark, Michael
(Monty) Widenius and Kim Aldale. For other contributors See section B Contributed programs.
We once started off with the intention to use mSQL to connect to
our own fast low level (ISAM) tables. However, after some testing we
came to the conclusion that mSQL was not fast or flexible enough
for our needs. This resulted in a new SQL interface to our database but
with almost the same API interface as mSQL. This API was chosen
to ease porting of third-party code.
It is not perfectly clear where the name MySQL derives
from. Our base directory and a large amount 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.
-
Fully multi-threaded using kernel threads. That means that it easily can
use multiple CPUs if available.
-
C, C++, JAVA, Perl, Python and TCL API's. See section 17 MySQL client tools and API's
-
Works on a lot of different platforms. See section 4.4 Which operating systems does MySQL support?.
-
Lots of column types like: signed/unsigned integers 1,2,3,4 and 8 bytes
long,
FLOAT, DOUBLE, CHAR, VARCHAR,
TEXT, BLOB, DATE, SET and ENUM
types. See section 7.2 Column types.
-
Very fast joins using a optimised one-sweep multi-join.
-
Full function support in the SELECT and WHERE parts. Example:
select column1 + column2 from table where column1/column2 > 0
-
SQL functions are implemented through a very optimised class library and
should be as fast as they can get! Usually there shouldn't be any memory
allocation at all after the query initialisation.
-
Full support for SQL
GROUP BY and ORDER BY. Support for
group functions (COUNT, AVG, SUM, MAX and
MIN).
-
Support for
LEFT OUTER JOIN with ANSI SQL and ODBC syntax.
-
A privilege and password system which is very flexible and secure. Allows
host based verification.
-
Secure passwords since all password traffic on the net is encrypted.
-
ODBC Open-DataBase-Connectivity for Windows95 (with source). All ODBC
2.5 functions and lots of others. So you can for example use Access to
connect to your MySQL server. See section 14 ODBC
-
Very fast B-tree disk tables with index compression.
-
16 indexes/table. Each index may consist of 1 to 15 columns/parts of
columns. Max index length is 256 bytes (but may be changed when compiling
MySQL. An index may be a prefix of a CHAR field.
-
Fixed and variable length records.
-
In memory hash tables which are used as temporary tables.
-
Handles large databases (we are using MySQL with some
databases that contain 50,000,000 records).
-
All columns have default values. One can always use INSERT on any subset
of columns.
-
Uses GNU
autoconf for portability.
-
Written in C and C++. Tested with a broad range of different compilers.
-
A very fast thread-based memory allocation system.
-
No memory leaks. Tested with a commercial memory leakage detector
(
purify).
-
A very fast table check,optimize and repair utility. See section 12.2 The MySQL table check, optimise and repair program
-
All data saved in ISO8859_1 format. All comparisons for normal string
columns are case insensitive.
-
Full ISO8859_1 (Latin1) support. For example Scandinavian едц is allowed
in table and column names.
-
Sorts by ISO8859_1 Latin1 (the Swedish way at the moment). It is
possible to change this in the source by adding new sort order
arrays. To see a example of a very advanced sorting, look at the Czeck
sorting code.
-
Alias on tables and columns as in the SQL92 standard.
-
DELETE, INSERT, REPLACE, and UPDATE returns
how many rows were affected.
-
Function names do not clash with table or column names. For example ABS
is a valid column name. The only restriction is that space is not
allowed between a function name and the '(' when using functions.
See section 7.26 Is MySQL picky about reserved words?
-
All MySQL commands have
--help or -? for help.
-
The server currently supports error messages to clients in many
languages. See section 9.1 Which languages are supported by MySQL?.
-
The clients use a TCP connection or Unix socket when connecting to the
MySQL server.
-
User commands as
show tables, SHOW INDEX FROM table and
show columns from table
There is one SQL tutor on the net at
http://w3.one.net/~jhoffman/sqltut.htm
This one has been recommended by a lot of 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
And another book also recommended by people on the MySQL
mailing list.
Understanding SQL
ISBN 0-89588-644-8
Publisher Sybex 510 523 8233
Alameda CA USA
1.6.1 Some web development tools which support MySQL
1.6.2 Web servers with MySQL tools
1.6.3 Examples
1.6.4 Other MySQL related links.
1.6.5 General database links.
There are also many web pages that use
MySQL. See section A Some users of MySQL.. Send any additions to this list to
A stored procedure is a set of SQL commands that can be stored and
compiled in the server. After this, the client doesn't have to issue
the whole query but can refer to the stored procedure. This gives some more
speed because the query only has to be parsed once and there is less data
that has to be sent between the server and the client. You can also raise the
conceptual level by having libraries of functions in the server.
A trigger is a stored procedure that is invoked when something happens.
For example one can install a stored procedure that checks every delete
to a transaction table and does an automatic delete on the corresponding
customer when all his transactions are deleted.
To see when MySQL might get these functions See section F List of things we want to add to MySQL in the future..
Requests to be added or dropped from the MySQL list should be
sent to the electronic mail address mdomo@tcx.se. Sending a
one line message saying either subscribe mysql or
un-subscribe mysql will suffice. If your reply address is not
valid you may use subscribe mysql your@address.your-domain or
un-subscribe mysql your@address.your-domain.
Please do not send mail about [un]subscribing to
automatically forwarded to hundreds of other users.
Your local site may have many subscribers to mysql@tcx.se. In that
case, it may have a local mailing list, so that a single message from
tcx.se is sent to the site and propagated to the local list. In
such cases, please contact your system administrator to be added to or
dropped from the local mysql@tcx.se list.
Mail to mdomo is handled automatically by majordomo.
The following mailinglists exists:
mysql-announce
-
This is form announcement of new versions of MySQL and related
programs. This is a low volume list that we think all MySQL
users should be on.
mysql
-
The main list for general MySQL discussion. Please note that
some things should go to the specialized lists. It you post to the wrong
list you may not get a answer!
mysql-digest
-
The above list in digest form. That means that tou get all messages in
one mail once a day.
mysql-Java
-
Discussion about MySQL and JAVA. Mostly about the JDBC drivers.
mysql-win32
-
All things concerning MySQL on Microsoft operating system like
Windows/NT.
myodbc
-
All things concerning connection to MySQL with ODBC.
msql-mysql-modules
-
A list about the Perl support in MySQL.
msql-mysql-modules-digest
-
A digest version of the above.
You subscribe/unsubscribe to all lists in the same way as described
above. Just exchange mysql with the list name.
Before you ask a question on the mailing list, it is a good idea to check
this manual. If you can't find an answer here, 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
If you can, please use the `mysqlbug' script that can be found in
the scripts directory in the distribution. If that is not possible,
remember to specify (if relevant) the following. Note that it is
possible to answer a letter with too much information but not one with too
little. You should always use `mysqlbug' if your question is anyway
related to a MySQL version you are using!
mysqlbug should automaticly finds most of the following information, but if
something important missing please include this in your question!
-
State which version of MySQL you are using (for example
mysql-3.21..tgz). You can find out which version you are running by
typing
mysqladmin version.
-
The manufacturer and model of machine you are working on.
-
The operating system. For most operating systems you can get this from
uname -a.
-
Sometimes the amount of memory (real and virtual) is also relevant.
-
Write in the mail that you have checked the reference manual and
mail archive so others know that you have tried to solve your
problem yourself.
-
If this is a bug when compiling: Include the exact error messages and
also a few lines around the offending code in the file from which you
got the error.
-
If this is a run-time bug, please describe exactly how you got the
error. If you can include a test program, with tables, which shows the error
you will probably get a more explicit answer.
-
f the you can't produce a test case in a few rows or if the the test table
is too big to be mailed to everyone (more than 10 rows) you should
make a mysqldump of your tables and make a README that describes your
problem. Tar and gzip or zip the files and ftp the archive to
ftp://www.tcx.se/pub/mysql/hidden. Send after this a short description of
the problem to mysql@tcx.se
-
If the questions is related to the privilege system, please include
the output of mysqlaccess, the output of 'mysqladmin reload' and
and all error messages you get when trying to connect ! You should do
the test in the above order !
If you are a support customer, please post the bug report to the
specified mailing list for higher priority treatment.
When answers are sent to you individually and not to the mailing list,
it is considered good etiquette to summarise the answers and mail them
to the mailing list.
Since it is very hard to now why something crashing please do one of the
following things.
First try to check if thigs that work for other crash for you:
-
Have you tried the benchmarks? This should test MySQL rather well. You
can also add code that simulates your application!
-
Try also fork_test.pl and fork2_test.pl
-
Check the reference manual about debugging mysqld.
-
Recompile MySQL with the
--with-debug switch to
configure. This will include a safe memory allocator that can find some
errors. This also gives a lot of output about what is happening.
-
Use mysqld --log and try to find if it some specific query that kills
it. 95% of all bugs is related to some specific query!
-
Have you applied the latest patches for your Operating System ?
-
Use --skip-locking
-
Have you tried 'mysqladmin proc' when it 'dies' ?
-
Put in some window the following command to output statistics:
mysqladmin -i 5 status
-
-
Start mysqld with gdb (or another debugger).
-
Run your test scripts.
-
Do
back (or the backtrace command in your debugger) when
mysqld core dumps.
-
Try to simulate your application with a perl script to force
MySQL to crash/misbehave.
-
Or send a normal bug report. See section 2.3 I think I have found a bug. What information do you need to help me?. But be even more
detailed than normally. Since MySQL works for a lot of people
it may that the crash comes from something that only exists on your
computer. For example, a system library error.
Try to make your answer broad enough that people other than the original
poster may benefit from it. 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. In such cases, please
make sure that your answer is not a duplication of a previous answer.
Try to summarise the essential part of the question in your reply, but
don't feel obliged to quote the whole question.
Basic licensing issues:
-
The easiest way to pay for MySQL is to use the license form at
TcX's secure server at https://www.tcx.se/license.htmy
-
We hope everybody understands that you only have to pay if you are
selling MySQL directly or selling a product which includes the
MySQL server. You may not include MySQL in a
distribution if you charge for some part of it. For internal use you do
not have to pay us if you do not want to.
-
The client code is in the Public Domain or under the GPL (readline).
You do not need a license to include client code in commercial programs.
-
We may add some additional functionality in the commercial version. The
likely test candidate for this is fast compressed read-only
databases. The current server includes support to read such databases
but not the packing tool. If we get enough revenue from support we will
probably release this under the same license as the other stuff.
-
But if you like MySQL and want to encourage further development
you are welcome to purchase a license or support.
See section J The MySQL server license.
For normal use MySQL costs nothing. When you sell
MySQL directly or as a part of another product you have to pay
for it. See section J The MySQL server license.
The client access part of MySQL is in the public domain. The command
line client includes parts that is under the GNU Public License
(readline).
These are our current license prices. All prices are in US Dollars. If you
pay by credit card, the currency is FIM (Finish Marks) so the prices will
differ slightly.
| Number of licenses | Price/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:
| licenses | Price/Copy | Minimum at one time | Minimum Payment
|
| 100-1000 | $40 | 100 | $4000
|
| 1000-2500 | $25 | 200 | $5000
|
| 2500-5000 | $20 | 400 | $8000
|
The OEM prices require that you act as a middle-man for eventual
problems/extension requests from users. We also require that the OEM
customer has a support contract.
If you have a low-margin high-volume product, you can always talk to us
about other terms (for example a percent of the sale price). If you do,
please be informative about your product, pricing, market and any other
information that may be relevant.
A full-price license includes really basic support. This means that we
are trying to answer any relevant question. If the answer is in the
documentation, we are going to direct you to the relevant
documentation. If you do not have a license/support we will probably 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:
One year of basic email support costs $200 (USD).
It includes:
-
For MySQL specific questions that doesn't belong to the
MySQL mailing list (mysql@tcx.se), you can contact
and expiration date when mailing any list to ensure a quick response.
-
We guaranty a timely answer for your emails. We can't guaranty that we
can solve ant problem, but at least you will receive an answer if we can
contact you by email.
-
Your suggestions for the further development of MySQL will be
taken into consideration. By taking email support you have already
helped the further development of MySQL. If you want to have
more input upgrade to a higher level of support.
-
Helping with unexpected problems when installing MySQL from a
binary distribution on a supported platforms.
-
We will help you with bugs and missing features. All found bugs are
fixed for the next MySQL release. If the bug is critical for
you, we will mail you a patch for it as soon the bug is fixed. Critical
bugs always have the highest priority for us, to ensure that they are
fixed as soon as possible.
-
This level of support does not cover installing MySQL from a
source distribution.
-
If you want us to help to optimize your system you have to upgrade to a
higher level of support.
-
You are entitled to upgrade to other support options for the difference
between the prices.
One year of extended email support costs $1000 (USD).
Extended basic supports contains everything in basic email support with
these additions:
-
Your email will be dealt with before normal email support users and
non-registered users.
-
Your suggestions for the further development of MySQL will
receive strong consideration. Simple extensions that suit the basic
goals of MySQL are implemented in a matter of days. By taking
extended email support you have already helped the further development
of MySQL.
-
In this we include a binary version of the pack_isam tool that supports
fast compressed read only databases (no
BLOB or TEXT types
yet). The current server includes support to read such databases but not
the packing tool.
-
Typical questions that are covered by extended email support are:
-
Answering and solving (within reason) questions that relate to (possibly)
bugs in MySQL. As soon as the bug is found and corrected, we
will mail a patch for it.
-
Helping with unexpected problems when installing MySQL from a
source/binary distribution on supported platforms.
-
Questions about missing features and offer hints how to go around them.
-
How to optimize mysqld for your situation.
-
You are allowed to slightly alter MySQL TODO. This will ensure
that the features you really need will be implemented sooner than
otherwise.
-
You are entitled to upgrade to login support for the difference between
the different support prices.
One year of email/phone/telnet support costs $2000 (USD).
Login supports contains everything in extended basic email support with
these additions:
-
Your email will be dealt with even before mails from extended support users.
-
Your suggestions for the further development of MySQL will
be taken into very high consideration Realistic extensions that can be
implemented in a a couple of hours and suits the basic goals of
MySQL will be implemented as soon as possible.
-
If you have a very specific problem we can try to log in on your system
and try to solve it 'in place'.
-
As any database vendor, we can't guaranty that we can rescue any data
from crashed tables, but if the worst happens we will help you rescue as
much as possible. MySQL has proven itself very reliable, but
because of outside interference anything is possible.
-
We will give you hints how to optimize your system and your queries.
-
You are allowed to call a MySQL developer (in moderation) and
discuss your MySQL related problems.
One year of extended email/phone/telnet support costs $5000 (USD).
Extended login supports contains everything in login support with these
additions:
-
Your email has the highest possible priority.
-
We will actively examine your system and help you optimize it and your
queries. We may also optimize and/or extend MySQL to better
suite your needs.
-
You may also request special extensions just for you. For example
select my_calculation(column1,column2) from database;
-
We will provide a binary version of all important MySQL
distributions for your system, as long as we can get an account on a
similar system. In the worst case we may require access to your system
to be able to make a binary version.
-
If you can provide accommodation and pay for traveler fares you can even
get a MySQL developer to visit you and offer you help with your
troubles. The extended login support entitles you to one personal
encounter, but we are as always very flexible towards our customers!
Currently we can take SWIFT payments, cheques or credit cards.
Payment should be made to:
Postgirot Bank AB
105 06 STOCKHOLM, SWEDEN
T.C.X 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 cheaper) to the
same account.
If you want to pay by cheque make it payable to "Monty Program KB". And
mail it to the address below.
T.C.X DataKonsult AB
BOX 6434
11382 STOCKHOLM, SWEDEN
If you want to pay with credit card over the Internet you can use
TcX's secure license form
For commercial licensing, or if you have any questions about any of the
information in this section, please contact:
David Axmark
Detron HB
Kungsgatan 65 B
753 21 UPPSALA
SWEDEN
Voice Phone +46-18-10 22 80 (Swedish and English spoken)
Fax +46-8-729 69 05 (Email *much* preferred)
E-Mail: mysql-licensing@tcx.se
There are (at least) four different copyright's on the MySQL
distribution.
-
The MySQL specific source needed to make the
mysqlclient
library and programs in the `client' directory is in the public
domain. Each file which is in the public domain has a header which clearly
states so. This is everything in the `client' directory and some parts of
mysys, mystring and dbug libraries.
-
Some small parts of the source (GNU
getopt) are covered by the
"GNU LIBRARY LIBRARY GENERAL PUBLIC LICENSE".
See the `mysys/COPYING.LIB' file.
-
Some small parts of the source (GNU
readline) are covered by the
"GNU GENERAL PUBLIC LICENSE". See the `readline/COPYING' file.
-
Some parts of the source (the
regexp library) are covered by a Berkeley
style copyright.
-
The other source needed for the MySQL server is AGPL. See the file
PUBLIC for more info.
Our philosophy behind this is:
-
The SQL client library should be totally free so it can be included in
commercial products without limitations.
-
People who want free access to the software we have put a lot of work into
can have it so long they do not try to make money directly by distributing
it for profit.
-
People who want the right to keep their own software proprietary, but also
want the value from our work, can pay for the privilege.
-
That means that normal in-house use is FREE. But if you use it for something
important to you, you may want to support further development of MySQL
by purchasing a support license.
This is a clarification of the information in the 'MySQL FREE
PUBLIC LICENSE'. See section J The MySQL server license.
MySQL may be used freely, including by commercial
entities for evaluation or unsupported internal use. However,
distribution for commercial purposes of MySQL, or
anything containing or derived from MySQL in whole or in part,
requires a written commercial license from TcX AB, the sole entity
authorised to grant such licenses.
You may not include MySQL "free" in a package containing anything for
which a charge is being made except as noted below.
The intent of the exception provided in the second clause is to allow
commercial organisations operating an FTP server or a bulletin board to
distribute MySQL freely from it, provided that:
-
The organisation complies with the other provisions of the FPL, which
include among other things a requirement to distribute the full source
code of MySQL and of any derived work, and to distribute the FPL itself
along with MySQL;
-
The only charge for downloading MySQL is a charge based on the
distribution service and not one based on the content of the information
being retrieved (i.e., the charge would be the same for retrieving a
random collection of bits of the same size);
-
The server or BBS is accessible to the general public, i.e., the phone
number or IP address is not kept secret, and anyone may obtain access to
the information (possibly by paying a subscription or access fee that is
not dependent on or related to purchasing anything else).
If you want to distribute software in a commercial context that
incorporates MySQL and you do not want to meet these
conditions, you should contact TcX AB to find out about commercial
licensing. Commercial licenses involve a payment, and include support
and other benefits. These are the only ways you legally can distribute
MySQL or anything containing MySQL: either by
distributing MySQL under the requirements of the FPL, or by
getting a commercial license from TcX AB.
I want to sell a product that can be configured to use MySQL
although my customer is responsible for obtaining/installing
MySQL (or some other supported alternative). Does one of us owe
you money if my customer chooses to use MySQL?
If your product REQUIRES MySQL to work, you would have to buy a
license. If MySQL just added some new features it should fall
inside normal use. For example, if using MySQL added logging to
a database instead of a text file it would not require a license. This
would, of course, mean that the user has to fetch and install
MySQL by himself. If the program is (almost) useless without
MySQL you would have to get a MySQL license to sell
your product.
Do I have to get a license for my copy?
No, you are not selling MySQL itself. But in this case we would
like you to purchase MySQL support. That is either your support
of MySQL or our support of you (the later is more expensive
since our time is limited).
Is your script designed for MySQL alone? Does it require
MySQL to function at all? Or is it designed for `a database'
and can run under MySQL, PostgreSQL, or something else?
If you've designed it strictly around MySQL then you've really
made a commercial product that requires the engine, so you need to buy
a license.
If, however, you can support any database with a base level of
functionality (and you don't rely on anything that only MySQL
supports) you probably DO NOT have to pay.
It also depends on what you're doing for the client. Are you tying into
a database you expect to already exist by the time your software is
purchased? Then you again probably don't have to pay. Or do you plan to
distribute MySQL or give them detailed instructions on
installing it with your software? Then you probably do.
One thing I'd like to suggest, folks. Look, development won't last
forever if nobody pays. I agree that buying a copy for every software
user is prohibitive compared to other products available but would it
not be courtesy for commercial developers to register their OWN copy
that they develop with?
We may choose to distribute older versions of MySQL with the
GPL in the future. However these versions will be identified as
GNU MySQL. Also all copyright notices in the relevant files
will be changed to the GPL.
You can always check MySQL's home
page to read the latest news.
But since the Internet connection at TcX is not very fast we would
prefer if you do the actual downloading from one of the mirrors
below.
Europe:
North America:
Asia:
Australia:
The first decision is if you want to use the latest development release
or the last stable release.
Normally if you are starting with development we recommend going with
the development release. This is because there are usually no really bad
bugs in the development release and you can easily test it on your
machine with the crash-me and benchmark tests. See section 11 MySQL benchmark suite.
The second decision is if you want a source or a binary release.
If you want to run MySQL on a platform that has a current
binary release, use that. A binary version of MySQL is easier to
install.
If you want to read (and/or modify) the C and C++ code that makes up
MySQL you should always get a source distribution. The code is
always the ultimate manual. The source distribution also contains more
tests and examples than the binary distribution.
To clarify our naming schema:
All MySQL versions are run through our standard test and the
benchmarks to ensure that they are relatively safe to use. The standard
tests are also extended the whole time to test for all previously found
bugs, so it gets better the whole time.
The MySQL release numbers consist of 3 numbers and a suffix.
So a release name like mysql-3.21.17-beta means.
- The first number. In this case 3
-
This describes the file format. So all version 3 releases has the same
file format. And when a version 4 appears every table has to be
converted to the new format (Nice tools for this will of course be
included).
- The second number. In this case 21
-
This is the release level. Normally there are two to choose
from. One is the release/stable branch and the other is the development
branch. Normally both are stable but the development version may have
quirks, missing documentation and may fail to compile on some systems.
- The third number. In this case 17
-
This is the version number. This is incremented for each
release. Usually you want the latest version for the release you have
choosen.
- The suffix. In this case beta
-
-
Alpha means that some new large code section exists which hasn't
been tested to 100%. Known bugs should be documented in the News section
(usually there are none). There are also new commands and extensions in
most alpha releases.
-
Beta means that all new code has been tested. No major new things are
added. There should be no known bugs.
-
Gamma is a beta that has been around a while and seems to work fine.
This is what many other companies call a release.
-
Release (no extension) means that the version has been run for a while
at many different sites without any bug reports (Except platform-specific
bugs).
Note that all releases have at least been tested with:
- A internal testsuite
-
This is part of a production system for a customer. It has many tables
and with many megabytes of data and lots of tables.
- The MySQL benchmark suite
-
This runs a range of common queries. It is also a test to see whether the
latest batch of optimisations actually made the code
faster. See section 11 MySQL benchmark suite.
- The crash-me test
-
This tries to find out what the database supports and which limitations it
has. See section 11 MySQL benchmark suite.
Another test is our internal production. We usually use the latest
version for this (at least on one machine) and we have more than 100
gigabytes of data to work with.
We are using the following policy when updating MySQL:
Each minor update will increment the last number in the version
string. When there are major new features or minor incompatibilities
with previous versions, the second number in the version string will be
incremented. When the file format changes the first number will be
increased.
-
Stable tested releases are meant to appear about 1-2 times a year. But
if small bugs are found, a release with only bug-fixes will be released.
-
Working releases are meant to appear about every 1-8 weeks.
-
Binary releases for some platforms will be made for major releases by
us. Other people may make binary releases for other systems but probably
less frequently.
-
We usually make patches available as soon as we have located and fixed
small bugs.
-
For non-critical but annoying bugs, we will make patches available if they
are sent to us. Otherwise we will combine many of them into a bigger
patch.
We use GNU autoconf so it is possible to port MySQL to all
modern systems with working Posix threads and a C++ compiler. The
client code requires C++ but not threads. We use/develop the software
ourselves primarily on Sun Solaris (versions 2.5 & 2.6) and some on
RedHat Linux 5.0.
The following OS/thread packages have been reported to compile
MySQL successfully. Note that for many OSes the native thread
support only works in the latest versions.
-
Solaris 2.5 & 2.6 with native threads.
-
SunOS 4.x with the included MIT threads package.
-
BSDI 2.x with the included MIT threads package.
-
BSDI 3.0 and 3.1 native threads.
-
SGI IRIX 6.x with native threads.
-
AIX 4.x whith native threads.
-
DEC UNIX 4.x with native threads.
-
Linux 2.0+ with LinuxThreads 0.5, 0.6 and 0.7.
-
FreeBSD 2.x with the included MIT threads package.
-
FreeBSD 3.x with native threads.
-
SCO OpenServer with a recent port of the FSU-threads package.
-
NetBSD 1.3 Intel and NetBSD 1.3 Alpha.
-
Openbsd 2.1
-
HPUX 10.20 with the included MIT threads package.
-
Win95 and NT. This version is currently only available for users with
a MySQL license or MySQL email support.
What you need:
-
GNU
gzip to uncompress the distribution.
-
A reasonable
tar to unpack the distribution. GNU tar is
known to work.
-
A working ANSI
C++ compiler. gcc >= 2.7, SGI C++, SunPro C++ are
some of the compilers that are known to work. libg++ is not
needed when using gcc.
-
A good
make program. If you have problems we recommend trying GNU
make.
Unpack tar archive in a directory. The tar file should be named like
mysql-VERSION.tar.gz (VERSION is a number like 3.21.30). A
directory named mysql-VERSION should be created.
zcat mysql-VERSION.tar.gz | tar xvf -
-
Unpack the distribution into the current directory.
cd mysql-VERSION
-
Change directory.
./configure
-
Configure the release. Here you might want to add some options. For a
list of configure options use `./configure --help'.
make
-
Compile everything.
make install
-
Install everything.
./scripts/mysql_install_db
-
Set up the MySQL privilige tables. This only needs to be done
the first time you install MySQL. You may want to edit this
script before running it to get right initial permissions for users to
connect to the database.
'installation_directory'/bin/mysqladmin version
-
Check to see if the server is running. Unless you added some options to
`./configure' 'installation_directory' is `/usr/local'. Here
is an example of the output you should get (You will not get this
exactly!):
mysqladmin Ver 6.3 Distrib 3.21.17, for pc-linux-gnu on i686
TCX Datakonsult AB, by Monty
Server version 3.21.17-alpha
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: 3
Remember that if you reconfigure MySQL you have to do
rm config.cache or make distclean before doing a new configure!
Usual configure switches:
GNU make is always recommended and is sometimes required.
About creating the grants database `mysql_install_db':
The default priviliges is that anybody may create/use the databases
named test or starting with test_. root can do
anyting. See section 6.2 How does the privilege system work?.
To change the defaults edit the script before running it. If this is
the first time you install MySQL you must run this command. If
you don't do it you will get the error: Can't find file:
'./mysql/host.frm'. This script also starts the mysqld daemon the first
time.
If you want to change things in the grant tables after installing you
should use mysql -u root mysql to connect to the grant tables
as the 'root' user.
Normal start of the MySQL server daemon (not needed the first
time): 'installation_directory'/bin/safe_mysqld --log
Some times patches appear on the mailing list. To apply them, do something
like this:
cd 'old-mysql-source-distribution-path'
gunzip < patch-file-name.gz | patch -p1
rm config.cache
make clean
And then follow the instructions for a normal source install from the
./configure step.
And then restart your MySQL server.
If your compile fails with something like:
configure: error: installation or configuration problem: C++ compiler
cannot create executables.
Try setting the environment variable CXX to "gcc -O3" (If
you are using gcc). For example CXX="gcc -O3"
./configure. If you use this you don't nead to have libg++ installed!
If you have any problems with using g++, or libg++ or libstdc++,
you can probably always solve these by configuring as above!
You can also install libg++. By default `configure' picks
c++ as a compiler name and GNU c++ links with
-lg++.
-
making all in mit-pthreads
make: Fatal error in reader: Makefile, line 18:
Badly formed macro assignment
This means you have to upgrade your make to GNU make.
-
If you want to add flags to your C or C++ compiler, add the flags to the
CC and CXX environment variables. For example:
CC="gcc -O4"
CXX="gcc -O4"
export CC CXX
-
If your
make stops with Can't find Makefile.PL when
you should try using GNU make. Solaris and FreeBSD
are known to have troublesome make programs.
-
If you get error messages from make or error message of type:
pthread.h: No such file or directory
This means you have to upgrade your make to GNU make
(GNU make version 3.75 is known to work).
-
If you get a error message like:
client/libmysql.c:273: parse
error before `__attribute__'
This means you need to upgrade your gcc compiler (2.7.2 is known to work).
-
If configure fails, and you are going to mail mysql@tcx.se,
please include any lines from config.log that you think can help solve
the problem. Also include a couple of lines of the last output from
configure if configure aborts. Post the bug using the 'mysqlbug' script.
PLEASE ALWAYS USE
mysqlbug
when posting questions to mysql@tcx.se. See section 2.3 I think I have found a bug. What information do you need to help me?.
Even if the problem isn't a bug, mysqlbug gathers some system
information that will help others solve your problem!
-
If you need to debug mysqld or a MySQL client, run configure
with:
configure --with-debug=yes and link your clients with
the new client library.
Before running a client you should do:
MYSQL_DEBUG=d:t:O,/tmp/client.trace
export MYSQL_DEBUG
You will now get a trace file in `/tmp/client.trace'.
-
If you get three errors when compiling mysqld like:
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);
Then configure didn't detect the type of the last argument to
accept(), getsockname() and getpeername(). Search
after the line:
/* Define as the base type of the last arg to accept */
#define SOCKET_SIZE_TYPE ###
and change ### to size_t or int depending on your operating system.
-
If you have problem with your own client code, test first with
mysql --debug=d:t:o,/tmp/client.trace before mailing a bug
report. See section 2.3 I think I have found a bug. What information do you need to help me?.
All MySQL programs compile clean (no warnings) for us (on
Solaris using gcc). But warning may appear because of different system
include files. Se below for warnings that may occur when using
mit-pthreads.
You probably have to use bison to compile sql_yacc.yy. If you get an
error like:
"sql_yacc.yy", line xxx fatal: default action causes potential...
you have to install bison (the GNU yacc).
If you want to have static linked code, use (with gcc):
LDFLAGS="-static" ./configure ...
You can on most systems force the usage of mit-pthreads with the configure
switch --with-mit-threads.
Building in a non source directory is not supported when using
MIT-threads. This is because we want to keep our changes to this code
minimal.
MIT-pthreads doesn't support the AF_UNIX protocol so we must use
the TCP/IP protocol for all connections (which is a little slower). If
you can't connect to a table, try using the host (-h or
--host) switch to mysql. This must be done if you have
compiled the client code --without-server because the default
connection is to use Unix sockets.
MySQL compiled with MIT threads has system locking disabled by
default for performance reasons. One can start the server with system
locking with the --use-locking switch.
Sometimes (at least on Solaris) the pthread bind() command fails
to bind to a socket without any error message. The result of this is
that all connections to server fails.
> mysqladmin ver 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 daemon and restart it.
This has only happened to us when we have forced the daemon down and done
a restart immediately.
sleep() isn't interruptible with SIGINT (break) with
MIT-threads. This is only notable in mysqladmin --sleep. One must
wait for the end of the sleep() before the interrupt is served
and the process stops.
We haven't got readline to work with MIT threads. (This isn't needed,
but may be interesting for someone)
When linking (at least on Solaris) you will receive warning messages
like:
ld: warning: symbol `_iob' has differing sizes:
(file /my/local/pthreads/lib/libpthread.a(findfp.o) value=0x4;
file /usr/lib/libc.so value=0x140);
/my/local/pthreads/lib/libpthread.a(findfp.o) definition taken
ld: warning: symbol `__iob' has differing sizes:
(file /my/local/pthreads/lib/libpthread.a(findfp.o) value=0x4;
file /usr/lib/libc.so value=0x140);
/my/local/pthreads/lib/libpthread.a(findfp.o) definition taken
Some other warnings which also can be ignored:
implicit declaration of function `int strtoll(...)'
implicit declaration of function `int strtoul(...)'
The included perl client code requires perl5.004 or later.
If you got a the following error (from mysqlperl or DBD-mysql):
/usr/bin/perl: can't resolve symbol '__moddi3'
/usr/bin/perl: can't resolve symbol '__divdi3'
You are probably using gcc (or using an old binary compiled with
gcc). Add -L/usr/lib/gcc-lib/... -lgcc to the link command where
-L/... is the path to the directory where libgcc.a
exists.
Sun native threads only work on Solaris 2.5 and higher. For 2.4 and
earlier versions, you can use MIT-pthreads. See section 4.8 MIT-pthreads notes. (FreeBSD).
If there are too many processes that try to connect very rapidly to the
mysqld one will get Error in accept : Protocol error in
the mysql log.
If you have the Sun Workshop 4.2 compiler you can configure with:
CC="cc -Xa -fast -xstrconst" CXX="CC -xsb -noex -fast"
./configure
You may also have to change the row in configure:
#if !defined(__STDC__) || __STDC__ != 1 to #if
!defined(__STDC__) because if you turn on __STDC__ with the
-Xc switch, the Sun compiler can't compile with the Solaris
`pthread.h' header files anymore. This is a Sun bug (Broken
compiler or broken include file).
If the compiled mysqld gives a error like: libc internal
error: _rmutex_unlock: rmutex not held. you probably are using the Sun
pro compiler on Solaris 2.6. Either ask sun for a working compiler or
upgrade to gcc (gcc also produces about 10% faster C++ code).
The tar in Solaris can't handle long file names; You may get the
following error (or something similar) when unpacking the MySQL
distribution:
x mysql-3.21.21a-beta-sun-solaris2.6-sparc/perl/Mysql-modules/blib/
lib/auto/Msql-Mysql-modules, 0 bytes, 0 tape blocks
tar: directory checksum error
You have to use gnu tar to unpack the distribution.
You can find copy of precompiled gnu tar (gtar) for Solaris at
http://www.mysql.com/Downloads/
On SunOS 4, MIT-pthreads is needed. You must have GNU make to compile
(because of MIT-pthreads).
In readline you may get warnings about duplicate defines. These may be
ignored.
When compiling mysqld there will be some warnings about implicit
declaration of function. These may be ignored.
On Linux you should use the --skip-locking flag to mysqld
(it is added automatically by safe_mysqld). This is because a bug
in Linux file locking calls. This bug is known to exist as recently as
Linux version 2.0.32.
When using LinuxThreads you will see a minimum of three processes
running. These are in fact threads. There will be one thread for the
Linux Threads manager, one thread to handle connections, and one thread
to handle alarms and signals.
if you are using RedHat you might get errors like:
/usr/bin/perl is needed...
/usr/sh is needed...
/usr/sh is needed...
If so upgrade rpm itself to rpm-2.4.11-1.i386.rpm &
rpm-devel-2.4.11-1.i386.rpm (or later versions).
You can get the 4.2 updates from
ftp://ftp.redhat.com/updates/4.2/i386. Or
http://www.sunsite.unc.edu/pub/Linux/distributions/redhat/code/rpm/
for other distributions.
LinuxThreads should be installed before configuring MySQL!
MySQL requires libc version 5.4.12 or newer. glibc version 2.0.6 and
later should also work. There has been some problems with the glibc rpms
from RedHat so if you have problems, check if there are any updates!
On some older Linux distributions configure may give a error
about: 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 says and add a extra underscore to the _P macro that only
has one underscore. Then try again.
You may get some warnings when compiling: (these 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'
If you want in Debian GNU/Linux to get MySQL to auto start when
system boots, do the following:
> cp scripts/mysql.server /etc/init.d/mysql.server
> /usr/sbin/update-rc.d mysql.server defaults 99
If mysqld always core dumps when starting, the problem may be that you
have an old `/lib/libc.a'. Try renaming this, remove sql/mysqld
and do a new make install and try again. This problem has been reported
on some Slackware installations.
If you install all the official redhat patches (including glibc-2.0.6-9
and glibc-devel-2.0.6-9) it should work out of the box (se above for how
to configure).
The updates are needed since there is a bug in glibc 2.0.5 in how
pthread_key_create variables are freed. With glibc 2.0.5 you must use
the static-linked MySQL binary distribution. If you want to
compile from source you must install the corrected version of
Linuxthreads from http://www.tcx.se/Downloads/Linux or
upgrade your glibc.
If you have a wrong glibc or linuxthread version the symptom is that
mysqld crashes after each connections. For example mysqladmin version
will crash mysqld when it finishes!
Another symptom of wrong libraries is that mysqld crashes at once when it
starts. One some Linux systems this can be fixed by configuring with
LDFLAGS=-static ./configure. On some RedHat 5.0 system it will only
work WITHOUT LDFLAGS=-static. This is known to happen even with
new versions as glibc 2.0.7-4 !
For the source distribution of glibc 2.0.6 you can find a patch at
http://www.tcx.se/Download/Linux/glibc-2.0.6-total-patch.tgz
that is easy to apply and is tested with MySQL!
If you experience crashes like these when you build MySQL, you can
always download the newest binary version of MySQL. This is compiled
staticly to avoid library conflicts and should work on all Linux systems!
In some implementations readdir_r is broken. This will be
noticed when SHOW DATABASES always returns an empty set. This
can be fixed by removing HAVE_READDIR_R from `config.h'.
Some problems will require patching your Linux installation. The patch can
be found at
http://www.tcx.se/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. This is a version Lf linux which 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 the above information.
The first problem is linuxthreads. You must patch linuxthreads for
alpha because the RedHat distribution uses an old (broken) linuxthreads
version.
-
Obtain the glibc2.5c source from any GNU ftp site.
-
Get the file
ftp://www.tcx.se/pub/mysql/linux/patched-glibc-linuxthreads-0.6.tgz.
This includes a fixed .c file. Copy this to the glibc
`./linuxthreads' directory.
-
Configure and compile glibc (You have to read the manual how to do this
together with linuxthreads). Don't install this!
-
Rename your old version of /usr/lib/libpthread.a to /usr/lib/libpthread.a-old
-
Copy the file glibc.../linuxthreads/libpthread.a to /usr/lib.
-
Configure MySQL with (everything on one row):
CC=gcc CCFLAGS="-Dalpha_linux_port" CXX=gcc
CXXFLAGS="-O3 -Dalpha_linux_port" ./configure --prefix=/usr/local/mysql
-
Try to compile mysys/thr_lock and mysys/thr_alarm. Test that these
work!
-
Recompile mysqld.
Note that Alpha-Linux is still an alpha platform for MySQL. With RedHat
5.0 and the patched linuxthreads you have a very good chance of it working.
When compiling threaded programs under Digital UNIX using CC / CXX the
documentation recommends the switch to cc and cxx and the libraries
-lmach -lexc (in addition to -lpthread).
So you have to configure with something like this:
CC="cc -pthread" CXX="cxx -pthread -O" ./configure
-with-named-thread-libs="-lpthread -lmach -lexc -lc"
When compiling mysqld you will may this warning for mysqld for a couple
of lines:
mysqld.cc: In function void handle_connections()':
mysqld.cc:626: passing long unsigned int *' as argument 3 of accept(int,sockad
ddr *, int *)'
You can safely ignore these. This is because configure can't detect
warnings, only errors.
You may get problems with the server exiting directly. If so, try
starting it with nohup safe_mysqld [options]
nohup is a command that ignores any SIGHUP sent from the
terminal.
If you have problems compiling and have Dec CC and gcc installed you can try
the following compile line (with sh or bash):
CC=cc CFLAGS=-O CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
On OSF1 V4.0D and compiler "DEC C V5.6-071 on Digital UNIX V4.0 (Rev. 878)"
the compiler had some strange behaviour (One gets undefined 'asm' symbols).
/bin/ld appears also to be broken (one gets _exit undefined when linking
mysqld).
On this we have managed to compile MySQL with the following configure line,
after replacing /bin/ld from OSF 4.0C:
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
In some versions of OSF1, the alloca() functions is broken. Fix
this by removing 'HAVE_ALLOCA' from config.h.
The alloca() function can also have a wrong prototype in
/usr/include/alloca.h. This warning can be ignored.
Configure will automaticly use the following thread libraries:
-with-named-thread-libs="-lpthread -lmach -lexc -lc"
When using gcc you can also try to use:
CFLAGS=-D_PTHREAD_USE_D4 CXX=gcc CXXFLAGS=-O3 ./configure ....
You may have to undefine some things in `config.h' (generated by
`./configure').
In some Irix implementations the alloca() function is broken. If
the mysqld server dies on some selects, remove HAVE_ALLOC &
HAVE_ALLOCA_H from `config.h'. If mysqladmin create doesn't
work, remove HAVE_READDIR_R from config.h. Also you may have to
remove HAVE_TERM_H.
Irix 6.2 doesn't support POSIX threads out of of the box. You have
install these patches, available from SGI if you have support:
1403, 1404, 1644, 1717, 1918, 2000, 2044
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
Type the following in the mysql installation directory:
> extra/replace bool curses_bool
< /usr/include/curses.h > include/curses.h
> make
There have also been reports about scheduling problems. This is because
if only one thread is running, things go slow. Avoid this by starting
another client. This may lead to a 2-10 fold increase in execution speed
thereafter for the other thread.
This is a poorly-understood problem with IRIS threads, so 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 line:
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-thread-safe-client
If you get a error on 'make install' that it can't find /usr/include/pthreads,
configure didn't detect that one neads mit-threads on FreeBSD. This is fixed
by doing:
rm config.cache
./configure --with-mit-threads
If you get link errors when compiling mysqlperl (Type
ient.a(my_getwd.o): RRS text relocation at 0x9a9f for
"__db_pargs_")
You must recompile the Perl code with -DPIC -fpic.
Do the following:
-
First install everything.
-
Change in `client/Makefile' the line:
CFLAGS = -g -O2 to
CFLAGS = -O2 -DPIC -fpic
-
cd client
-
rm *.o
-
make libmysqlclient.a
-
cd ../perl
-
make
This shall hopefully be handled automatically in the future.
The FreeBSD make behaviour is slightly different from GNU
make. If you have a problem that `perl/Makefile'
doesn't get generated, you should install GNU make.
If mysql or mysqladmin takes a long time to respond, a user said the
following:
Are you running the ppp user process? On ine FreeBSD box (2.2.5) MySQL
clients takes a couple of seconds to connect to mysqld if the ppp
process is running.
FreeBSD is also known to have a very low default file handle limit.
See section 15.6 File not found
If you have a problem that select NOW() returns GMT and not your local time,
you have to set the TZ environment variable to your current timezone.
You have to configure with:
--with-named-thread-libs=-lc_r
The pthreads library for FreeBSD doesn't contain the sigwait
function and there is some bugs in it. To fix this, get the
`FreeBSD-3.0-libc_r-1.0.diff' file and apply this in the
`/usr/src/lib/libc_r/uthread' directory. Follow after this the
instructions that can be found with man pthread about how to
recompile the libc_r library.
You can test if you have a 'modern' libpthread.a with:
> nm /usr/lib/libc_r.a | grep sigwait.
If the above doesn't find sigwait you have to use the above patch
and recompile libc_r.
From Jan Legenhausen jleg@csl-gmbh.net.
I finally got mysqlperl working on BSDI2.1.
What i did was almost nothing:
-
cd client
-
Leave the Makefile as it is! (i use
GCC="shlicc2" per default;
perl5 automagically uses shlicc2 - you should use _one_ version (either
gcc or shlicc2) for both Mysql.c and
libmysqlclient.a!)
-
rm *.o
-
gmake libmysqlclient.a
-
cd ../perl/mysqlperl
-
make clean
-
add
$sysliblist=" -L$tmp -lgcc -lcompat"; to Makefile.PL line 45
(just to be sure - i didn't check if one could leave out this one)
-
perl Makefile.PL
-
remove all old
libmysqlclient.a's in /usr/lib and
/usr/contrib/lib
-
make install
If you get the following error when compiling MySQL:
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
Then your ulimit for virtual memory is too low. Try using: ulimit
-v 80000 and do make again.
If you are using gcc you can also add the flag '-fno-inline' to the compile
line when compiling sql_yacc.cc.
If you have a problem that select NOW() returns GMT and not your local time,
you have to set the TZ environment variable to your current timezone.
-
Upgrade to BSD/OS 3.1. If that is not possible, install BSDIpatch M300-038.
-
Use the following configuration when installing MySQL:
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:
env CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
--with-unix-socket-path=/var/mysql/mysql.sock
-
If you have problems with performance under heavy load, try using the
--skip-thread-prior switch to safe_mysqld! This will run all threads
with the same priority and on BSDI 3.1 this gives better performance.
(At least until BSDI has fixed their thread scheduler).
You can change the directory locations if you wish, or just use their
defaults by not specifying them.
The current port is only tested on a 'sco3.2v5.0.4' system.
-
For OpenServer 5.0.X You need to use GDS in Skunkware 95 (95q4c). This
is necessary because GNU gcc 2.7.2 in Skunkware 97 does not have GNU
as.
-
You need the port of GCC 2.5.? for this product and the Developement
system. They are required on this version of SCO UNIX. You can not
just use the GCC Dev system.
-
One should get FSU thread package and install this first. This can be
found at:
http://www.cs.wustl.edu/~schmidt/ACE_wrappers/FSU-threads.tar.gz
You can also get a precompiled package from:
ftp://www.tcx.se/pub/mysql/SCO/fsu-threads.tar.gz
-
FSU pthreads can be compiled with SCO UNIX 4.2 with tcpip. Or
OpenServer 3.0 or Open Desktop 3.0 (OS 3.0 ODT 3.0), with the SCO
Developement System installed using a good port of GCC 2.5.X ODT or OS
3.0 you will need a good port of GCC 2.5.? There are a lot of problems
with out a good port. The port for this product requires the SCO UNIX
Developement system. Without it, you are missing the libraries and the
linker that is needed.
-
To build FSU pthreads in your system do the following:
-
Run ./configure in threads/src directory and select the SCO OpenServer
option. This command copy Makefile.SCO5 to Makefile.
-
Run make.
-
To install in default /usr/include directory, login as root and cd to
thread/src directory, run make install.
-
Remember to use GNU
make when making MySQL.
-
If you don't start safe_mysqld as root, you will probably only get the
default 110 open files per process. mysqld will write a note about this
in the log file.
SCO development notes:
-
MySQL should automaticly detect FPU-threads and link mysqld
with:
-lgthreads -lsocket -lgthreads
-
The SCO development libraries are reentrant in FSU pthreads. SCO claims
that its libraries function are reentrant so they must be reentrant with
FSU pthreads. FSU pthreads on OpenServer tries to use the SCO scheme to
make reentrant library.
-
FSU threads (at least the version at www.tcx.se) comes linked with
GNU malloc. If you get problems with memory usage, check that gmalloc.o
is included in libgthreads.a and libgthreads.so
-
In FSU pthreads, the following system calls are pthread aware: read,
write, getmsg, connect, accept, select and wait.
When using the IBM compiler, something like this is needed:
CC="xlc_r -ma -O3 -qstrict" CXX="xlC_r -ma -O3 -qstrict" ./configure
Automatic detection of xlC is missing from autoconf.
There is a couple of 'small' problems when compiling mysql on HPUX.
Below we describe some problems and workarounds when using the HPUX compiler
and gcc 2.8.0 .
gcc 2.8.0 can't compile readline on HPUX (internal compiler error).
mit-pthreads can't be compiled with HPUX compiler, because it can't compile
.S (assembler) files.
We got MySQL to compile on HPUX 10.20 by doing the following:
CC=cc CFLAGS="+z +e -Dhp9000s800 -D__hpux__" CXX=gcc
CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-low-memory
cd mit-pthreads
rm config.cache
CC=gcc CXX=gcc ./configure
cd ..
make
make install
scripts/mysql_install_db
The MySQL-win32 version has by now proven itself to be very
stable. The MySQL-win32 version has all the features as the
corresponding Unix version of MySQL with the following exceptions:
ALTER TABLE
-
One can't do a
ALTER TABLE on file if it is hold open by another thread
or the table cache. On MySQL-win32 I have added code to close the
file owned by the thread that does ALTER TABLE but MySQL
can't yet close the the file descriptors used by other threads. We have to do
a major recode of the file lock system to handle this. For now, when using
ALTER TABLE, one must be sure that no other threads are using the table.
One can be sure of this by doing a 'mysqladmin refresh' before doing an
ALTER TABLE.
Increasing the table cache
-
Win32 has only a very limited number of open files it can handle at the
the same time (about 255). Because of this one shouldn't increase the
number of open connections or number of cached tables very much on Win32.
Win95 and threads
-
Win95 leaks memory for each threads. Because of this one shouldn't
run mysqld for an extended time on Win95 if one does many connections as each
connection in MySQL creates a new thread! NT doesn't suffer from this bug.
Other win32 specific issues are described in the README file that comes
with the MySQL-win32 distribution.
Please always use the mysqlbug script when posting questions to the
mailinglist (mysql@tcx.se). Even if the problem isn't a bug,
mysqlbug gathers some system information that will help other solve your
problem! See section 2.3 I think I have found a bug. What information do you need to help me?.
-
Get the distribution. That is a file called something like
mysql-version-OS.tgz. For example a file holding a MySQL
version 3.21.15 for a intel linux machine is called
`mysql-3.21.15-alpha-pc-linux-gnu-i586.tgz'.
-
Pick a directory to put MySQL in: In the following we will use
`/usr/local/mysql' as the installation directory and MySQL
version VERSION (something like 3.21.15) for SunOS5 (Solaris), for
example.
-
All of the following instructions assume you have permission to create
files in `/usr/local'.
Use the following commands to create the directory and unpack the
distribution:
> cd /usr/local
> zcat /<where ever you put it>/mysql-3.20.0-SunOS5.tgz | tar xvf -
> ln -s mysql-VERSION mysql
-
After this you should install the MySQL privilege tables.
In some binary versions the grant tables may already be created, for
others distributions you should edit the `scripts/mysql_install_db'
script to have the privileges you want and run it. The default
privileges is that anybody may create/use the databases named 'test' or
starting with "test_". The MySQL user root can do
anything. Note that you do not have to run the MySQL server as
root. Any user is ok as long as it can read and write in the installtion
directories/files.
If you want to recreate the privilege tables remove all the *.ISM and
*.ISD files in the mysql database directory, edit the
scripts/mysql_install_db script to have the privileges you want and run
it.
This creates the privilige tables if they don't exist:
> scripts/mysql_install_db
See section 4.13 Problems running mysql_install_db
If you want to change things in the grant tables after installing you
should use mysql -u root mysql to connect to the grant tables
as the 'root' user.
The mysql_install_db script also starts the mysqld daemon.
-
Normally, start the MySQL server daemon (not needed the first
time):
> bin/safe_mysqld --log &
-
You can test that the daemon is running by doing this:
> bin/mysqladmin ver
That should print something like this. The exact output depends on you
platfrom and use.
bin/mysqladmin Ver 6.3 Distrib 3.21.15-alpha, for SOLARIS 2.5
on SPARCstation
TCX Datakonsult AB, by Monty
Server version 3.21.15-alpha
Protocol version 9
Connection Localhost via UNIX socket
TCP port 3306
UNIX socket /tmp/mysql.sock
Uptime: 2 days 1 hour 42 min 3 sec
Running threads: 2 Questions: 450378 Reloads: 17 Open tables: 64
cd perl/DBI
perl Makefile.PL
make
make install
cd ../Mysql-modules
perl Makefile.PL
make
make install
You should use the safe_mysqld script to the server.
safe_mysqld expects one of two conditions to be true:
-
You're executing the script from the base mysql installation directory
(for example
/usr/local/mysql)
-
The server should reside in
/my/. To get it to run correctly,
you should cd to /usr/local/mysql and then execute
safe_mysqld or modify the script so that it expects the base
mysql directory to be `/usr/local/mysql' rather than the default
`/my/'.
When you execute this:
> bin/mysqld --help
You will get the options for mysqld (and safe_mysqld) and the current
paths. Normally you only should need to change the
--basedir=path. You can test the path switches by executing:
> bin/mysqld --basedir=/usr/local --help
If you would like to use mysqlaccess and have the mysql distribution in
some nonstandard place, you must change the path to mysql in
mysqlaccess. bin/mysqlaccess about line 308:
$MYSQL = '/usr/local/bin/mysql --batch --unbuffered';
If you don't change the path, you will get a 'broken pipe' error when
using mysqlaccess.
If you would like MySQL to start when you boot your machine,
you can copy bin/mysql.server to where your system has it startup
files. More information can be bound in the bin/mysql.server script
itself.
-
MySQL needs at least Linux 2.0.
-
The binary release is linked with -static, which means that you don't have
to worry about which version of your system libraries. You don't have
to install LinuxThreads either. A program linked with -static is slightly
bigger but also slighty faster (3-5%) than a dynamicly linked program. The
only problem is that you can't use UDF (user definable functions) with
a -static program. If you are going to write or use UDF functions (this is
only something for C or C++ programmers) you have to compile MySQL yourself.
-
The Linux-intel binary release is compiled with
"pgcc -O6 -mpentium -mstack-align-double" for more speed.
-
The perl distribution needs perl5.004.03 or newer.
The binary distribution of MySQL for HP/UX is distributed as an
HP depot file. This means that you must be running at least HP/UX 10.x
to have access to HP's software depot tools.
This 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. This version does not use HP's native
thread package. It is highly unlikely that MySQL will use HP native
threads on anything but HP/UX 10.30 or later.
Other configurations that may work:
9000/7xx - HP/UX 10.20+
9000/8xx - HP/UX 10.30 (does not use HP native threads)
The following configurations almost definitely won't work:
9000/7xx or 8xx - HP/UX 10.x where x < 2
9000/7xx or 8xx - HP/UX 9.x
To install (everything, including server, client and development tools):
/usr/sbin/swinstall -s <full path to the depot file> mysql.full
To install server only:
/usr/sbin/swinstall -s <full path to the depot file> mysql.server
To install client pack only:
/usr/sbin/swinstall -s <full path to the depot file> mysql.client
To install development tools only:
/usr/sbin/swinstall -s <full path to the depot file> mysql.developer
The depot will place binaries/libraries in /opt/mysql and data in
/var/opt/mysql. The depot will also create the appropriate entries in
/sbin/init.d and /sbin/rc2.d to automatically start the server on boot. This
obviously entails being root to install.
Clients have to be linked with: -lmysqlclient
It may happen that mysql_install_db doesn't install the privilige
tables but ends with:
Starting mysql server
starting mysqld demon with databases from xxxxxx
mysql demon ended
In this case you should examine the log in the xxxxxx directory very
carefully! This contains the reason why mysqld didn't start. If you
can't understand what happens, you should at least include the log when
you post a bug report using mysqlbug!
Possible problems when running mysql_install_db are:
There is already a mysqld deamon running.
-
In this case you have probably don't have to run mysql_install_db at all.
One only have to run mysql_install_db once when one install MySQL
the first time.
Installing a second mysqld daemon doesn't work when one daemon is running.
-
The problem is the new server tries to use the same socket and port as the old one.
You can start the new server with a different socket and port as follows:
MYSQL_UNIX_PORT=/tmp/mysqld-new.sock
MYSQL_TCP_PORT=3307
export MYSQL_UNIX_PORT MYSQL_TCP_PORT
scripts/mysql_install_db
After this you should edit your server boot script to start both daemon
with different sockets and ports (safe_mysqld --socket=... --port=....).
mysqld crashes at once.
-
If you are running RedHat 5.0 and a glibc version before glibc 2.0.7-5
you should check that you have installed all glibc patches! There is a
lot of information about this in the
MySQL mail archives.
See section 4.10.3 Linux notes for all versions
Can't connect to the server (when using mit-threads)
-
If
mysql_install_db can't connect to the server you should check
that you have an entry in `/etc/hosts' like:
127.0.0.1 localhost
The above is only a problem on system that doesn't have a thread library
and MySQL has to use mit-threads.
You don't have write access to create a socket file (in /tmp ?)
-
In this case you have to start mysqld manually and add the privilege
information yourself. If you are using a binary version and are not
installing in /usr/local/mysql, you have specify the paths mysqld
should use with arguments. You can get information about which
paths mysqld uses and the how to change them with
mysqld --help.
You can also specify paths for safe_mysqld by doing the following:
MYSQL_UNIX_PATH=/some_directory_for_tmp_files/mysqld.sock
MYSQL_TCP_PORT=3306
TMPDIR=/some_directory_for_tmp_files/
export MYSQL_UNIX_PATH MYSQL_TCP_PORT TMPDIR
scripts/mysql_install_db
or
bin/mysqld --skip-grant
bin/mysql -u root mysql
After this you can execute the sql commands in mysql_install_db.
The paths may be different from what mysqld expects.
-
You can override all paths to mysqld with command line arguments.
Use
mysqld --help for more information. You can edit
bin/safe_mysqld to reflect the paths for your installation.
A simple test to see that everything is working is:
bin/mysqladmin version
Check the log file to see if mysqld started up correctly.
mysqld daemon starts with a cd to 'mysql-data-dir'. After this,
mysqld-data-dir is changed to './' (current dir). All paths (databases,
pid file, and log file) have this directory as base path './'. If you
have any problems with wrong paths, try mysqld --help to see
your current paths. Every path can be changed by a startup option to
safe_mysqld or mysqld
cd <localstatedir default /usr/local/var>
tail <your host name>.log
To verify that MySQL is working run the following tests:
> cd /usr/local/bin
> ./mysqlshow
+-----------+
| Databases |
+-----------+
| mysql |
+-----------+
> ./mysqlshow mysql
Database: mysql
+--------+
| Tables |
+--------+
| db |
| host |
| user |
+--------+
> ./mysql -e "select host,db,user from db" mysql
+------+--------+------+
| host | db | user |
+------+--------+------+
| % | test | |
| % | test_% | |
+------+--------+------+
There is also a benchmark suite so you can compare how MySQL
performs on different platforms. In the near future this will also be used to
compare MySQL to other SQL databases.
> cd bench
> run-auto-increment-test
You can also run the tests in the test subdirectory. To run
`auto_increment.tst':
./mysql -vf test < ./tests/auto_increment.tst
Expected results are shown in the file
`./tests/auto_increment.res'.
The safe_mysqld script is written that it should be able to start a source and
a binary version of mysqld, even if these have sligtly different paths!
You can install a binary release of MySQL anywhere as long as you start
safe_mysqld from installation directory:
cd mysql_installation_directory
bin/safe_mysqld &
If you want to change the startup options to mysqld you can always edit
safe_mysqld! In this case you should copy safe_mysqld to some other
location that it will not be overwritten if you sometimes decide to upgrade
MySQL!
To start or stop MySQL use the following commands:
scripts/mysql.server stop
scripts/mysql.server start
You might want to add these start and stop commands in the appropriate
places in your `/etc/rc*' files when you start using MySQL
for production applications. You can edit the mysql.server script to start
safe_mysqld from some specific location and as some specific user. You
can also add startup options to mysqld here.
The following are useful extensions in MySQL that you probably
will not find in other SQL databases. Be warned that if you use them,
your code will not be portable to other SQL servers.
-
The field types
MEDIUMINT, SET, ENUM and the
different BLOB and TEXT types.
-
The field attributes
AUTO_INCREMENT, BINARY,
UNSIGNED and ZEROFILL.
-
All string comparisons are by default are case independent with
case according to ISO-8859-1 Latin1. If you don't like this you should
declare your strings with the
BINARY attribute.
-
MySQL maps all tables to filenames and with MySQL one
can use standard system tools to backup, rename, move, delete and copy
tables. This forces MySQL to be case sensitive on table names
on operating systems that have case sensitive filenames (like most Unix
systems). If you have a problem remembering table names, create
everything in lowercase.
-
LIKE is allowed on numerical columns.
-
Use of
INTO OUTFILE and STRAIGHT_JOIN in a SELECT
statement. See section 7.10 SELECT syntax.
-
EXPLAIN SELECT to get a description on how tables are joined.
-
Use of index names, indexes on a subpart of a field, and use of
INDEX or KEY in a CREATE TABLE
statement. See section 7.6 CREATE TABLE syntax.
-
Use of
DROP column or CHANGE column in an ALTER TABLE
statement. See section 7.7 ALTER TABLE syntax.
-
Use of
LOAD DATA INFILE. This syntax is in many cases compatible with
Oracle's LOAD DATA INFILE. See section 7.14 LOAD DATA INFILE syntax.
-
Using
" instead of ' to enclose strings.
-
Using the escape
\ character.
-
The
SET OPTION statement. See section 7.20 SET OPTION syntax.
-
One doesn't have to have all columns in the
GROUP BY part.
See section 7.3.12 Functions for GROUP BY clause.
-
To make it easier for a user that comes from different SQL environments
mysql supports a a lot of aliases for many functions. For example all
string functions support both the ANSI SQL and the ODBC syntax.
-
The
|| and && operators are, in MySQL, synonyms for
OR and AND, like in the C programming language.
Likewise | and & stand for bitwise OR and
AND. Because of this nice syntax, MySQL doesn't support
the ANSI SQL operator || for string concatenation, one has to use
CONCAT() instead. As CONCAT() takes any number
of arguments it's easy to convert use of the || operator to
MySQL.
-
Use of any of the following functions or commands:
-
CREATE DATABASE or DROP DATABASE.
See section 7.4 Create database syntax.
-
% instead of mod(). % is supported for C programmers and
for compatibility with PostgreSQL.
-
=, <>, <= ,<, >=,>, AND,
OR, or LIKE in a column statement
-
LAST_INSERT_ID.
See section 17.1.3 How can I get the unique ID for the last inserted row?
-
REGEXP or NOT REGEXP.
-
CONCAT() or CHAR() with one or more than two arguments. In
MySQL the above functions can take any number of arguments.
-
BIT_COUNT(), ELT(), FROM_DAYS(), FORMAT(),
IF(), PASSWORD(), ENCRYPT(),
PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS()
