This is the MySQL reference manual. This version documents the 3.21.33 version of MySQL.
MySQL is a basically free, very fast 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 to 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.2 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 (The TODO)..
For 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 considered a valid bug report.
The mysqlbug script can be found in the `scripts' directory in the
distribution, that is, `where-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 is available to 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 versions 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.
FLOAT, DOUBLE, CHAR, VARCHAR,
TEXT, BLOB, DATE, DATETIME, YEAR,
SET and ENUM types. See section 7.2 Column types.
select column1 + column2 from table where column1/column2 > 0.
GROUP BY and ORDER BY. Support for
group functions (COUNT, AVG, STD, SUM, MAX
and MIN).
LEFT OUTER JOIN with ANSI SQL and ODBC syntax.
autoconf for portability.
purify).
DELETE, INSERT, REPLACE, and UPDATE return
how many rows were affected.
--help and -?
for help.
SHOW command that can be used to retrieve
information about databases, tables and indexes. The EXPLAIN command
can be used to check how the optimizer resolves a query.
User commands as show tables, SHOW INDEX FROM table and
show columns from table.
There is one SQL tutorial on the net at http://w3.one.net/~jhoffman/sqltut.htm.
This book 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
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 less data need 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 (The TODO)..
Requests to be added to 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
unsubscribe mysql will suffice. If your reply address is not
valid you may use subscribe mysql your@address.your-domain or
unsubscribe 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 mailing lists exist:
mysql-announce
mysql
mysql-digest
mysql-Java
mysql-win32
myodbc
msql-mysql-modules
msql-mysql-modules-digest
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 any way
related to a MySQL version you are using!
mysqlbug should automatically find most of the following information,
but if something important is missing please include this in your question!
mysqladmin version.
uname -a.
mysqldump 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/secret.
Then send a short description of the problem to mysql@tcx.se.
mysqlaccess, the output of mysqladmin reload and
all error messages you get when trying to connect! You should do
the tests 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 know why something is crashing please do one of the following things.
First try to check whether or not things that work for others crash for you:
fork_test.pl and fork2_test.pl.
mysqld.
--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.
mysqld --log and try to determine whether or not some specific query kills
it. 95% of all bugs are related to some specific query!
--skip-locking.
mysqladmin proc when it dies?
mysqladmin -i 5 status
in a separate window to output statistics.
mysqld with gdb (or another debugger).
back (or the backtrace command in your debugger) when
mysqld core dumps.
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:
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 are 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 |
| licenses | Price/Copy | Minimum at one time | Minimum Payment |
| 100-1000 | $40 | 100 | $4000 |
| 1000-2500 | $25 | 200 | $5000 |
| 2500-5000 | $20 | 400 | $8000 |
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.
The various types of commercial support are described in the following sections. You are entitled to upgrade from any lower level of support to a higher level of support for the difference between the prices of the two support levels.
One year of basic email support costs $200 (USD).
It includes:
One year of extended email support costs $1000 (USD).
Extended basic support contains everything in basic email support with these additions:
BLOB or TEXT types
yet). The current server includes support to read such databases but not
the packing tool.
mysqld for your situation.
One year of email/phone/telnet support costs $2000 (USD).
Login support contains everything in extended basic email support with these additions:
One year of extended email/phone/telnet support costs $5000 (USD).
Extended login support contains everything in login support with these additions:
select my_calculation(column1,column2) from database;
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 copyrights on the MySQL distribution.
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
the mysys, mystring and dbug libraries.
getopt) are covered by the
"GNU LIBRARY LIBRARY GENERAL PUBLIC LICENSE".
See the `mysys/COPYING.LIB' file.
readline) are covered by the
"GNU GENERAL PUBLIC LICENSE". See the `readline/COPYING' file.
regexp library) are covered by a Berkeley
style copyright.
Our philosophy behind this is:
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:
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 so fast we would prefer if you do the actual downloading from one of the mirrors below.
Please report bad/not updated mirrors to webmaster@tcx.se.
Europe:
Austria WWW [Univ. of Technology/Vienna]
Bulgaria FTP [Naturella]
Czech Republic WWW [CESNET]
Denmark WWW [Ake]
Denmark WWW [SunSITE]
Denmark FTP [SunSITE]
Estonia WWW [Tradenet]
France WWW [minet]
Germany WWW [Wolfenbuettel]
Germany WWW [Staufen]
Hungary WWW [Xenia]
Israel WWW [Netvision]
Israel FTP [Netvision]
Italy WWW [Matrice]
Poland WWW [Sunsite]
Poland FTP [Sunsite]
Portugal WWW [Telenet]
Russia FTP [Cityline]
Romania WWW [Timisoara]
Romania FTP [Timisoara]
Romania WWW [Bucharest]
Romania FTP [Bucharest]
Sweden WWW [Sunet]
Sweden FTP [Sunet]
UK WWW [Omnipotent/UK]
UK FTP [Omnipotent/UK]
UK WWW [PLiG/UK]
UK FTP [PLiG/UK]
North America:
Canada WWW [Polaris Computing]
Canada WWW [Tryc]
Canada WWW [Cyberus]
Canada FTP [Cyberus]
USA WWW [Hurricane Electric/San Jose]
USA WWW [Buoy/New York]
USA WWW [Hypernet Communications/Dallas]
USA WWW [Hurricane Electric/California]
USA FTP [Netcasting/West Coast]
USA WWW [Savages/Oregon]
USA WWW [Circle Net/North Carolina]
USA WWW [Gina net/Florida]
USA FTP [DIGEX]
South America:
Asia:
Korea WWW [KREONet]
Japan WWW [HappySize]
Japan FTP [HappySize]
Singapore WWW [Com5 Productions]
Singapore FTP [Com5 Productions]
Taiwan WWW [NCTU]
Australia:
Australia WWW [AARNet/Queensland]
Australia FTP [AARNet/Queensland]
Australia WWW [Blue Planet/Melbourne]
Australia FTP [Blue Planet/Melbourne]
The first decision is whether 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 whether you want a source release 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:
Note that all releases have at least been tested with:
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.
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.
What you need:
gzip to uncompress the distribution.
tar to unpack the distribution. GNU tar is
known to work.
C++ compiler. gcc >= 2.7, SGI C++, and SunPro C++ are
some of the compilers that are known to work. libg++ is not
needed when using gcc.
make program. If you have problems we recommend trying GNU
make.
Unpack the tar archive in a directory. The tar file should
have a name like
`mysql-VERSION.tar.gz' (VERSION is a number like 3.21.33).
When you unpack the archive, a directory named `mysql-VERSION'
should be created.
zcat mysql-VERSION.tar.gz | tar xvf -
cd mysql-VERSION
./configure
./configure --help.
make
make install
./scripts/mysql_install_db
'installation_directory'/bin/mysqladmin version
./configure.
mysqladmin should produce output similar to that shown below, although
it likely will not be exactly the same on your system:
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 running
./configure again!
GNU make is always recommended and is sometimes required.
Some of the options you may want to use when you run ./configure
are described below:
--without-server. If you only
want to have the client library and don't have a C++ compiler, you
can remove the code in configure (in the source distribution) that tests
for the C++ compiler and then run ./configure with
--without-server. In this case, ignore
any warnings about `mysql.cc' (the only MySQL client
that needs C++).
--prefix=/usr/local or --prefix=/usr/local
--localstatedir=/usr/local/data/mysql.
--with-unix-socket-path=absolute_file_name.
Internal compiler error: program cc1plus got fatal signal 11 or
Out of virtual memory you can try the configure switch
--with-low-memory.
This adds -fno-inline to the compile line if you are using gcc
and -O0 if you are using something else. The problem is that
gcc requires
about 180M to compile `sql_yacc.cc' with inline functions.
LDFLAGS=-static ./configure.
gcc and don't have g++ or libstdc++ installed you can
configure with: CC=gcc CXX=gcc ./configure.
DEFAULT values at all,
so that you get an error for any INSERTs that don't specify all
NOT NULL columns,
configure with CXXFLAGS=-DDONT_USE_DEFAULT_FIELDS ./configure.
configure switch
--with-charset=charset,
where [charset] may be one of big5, czech, danish,
dec8, dos, german1, hp8, koi8_ru,
latin1, latin2, swe7, usa7, ujis
or sjis.
See section 9.1.1 Character set used for data & sorting.
If you want to convert characters between the server and the client,
you should take a look at the SET OPTION CHARACTER SET.
See section 7.20 SET OPTION syntax.
Warning: If you change character sets after having created a
table you will have to run isamchk -r -q on every
table. Otherwise things will be sorted incorrectly in some cases (but
not all!).
Sometimes 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 an error such as:
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 need to have libg++ installed!
If you have any problems with using g++, or libg++ or libstdc++, you can probably always solve them 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 or make: file `Makefile' line 18: Must be a separator (:This means you have to upgrade your make to GNU
make.
CC=gcc CFLAGS=-O6 CXX=gcc CXXFLAGS=-O6 export CC CFLAGS CXX CXXFLAGS
make stops with
Can't find Makefile.PLThen you should try using GNU
make. Solaris and FreeBSD
are known to have troublesome make programs.
make or error messages of the type:
pthread.h: No such file or directoryThis means you have to upgrade your
make to GNU make
(GNU make version 3.75 is known to work).
client/libmysql.c:273: parse error before `__attribute__'This means you need to upgrade your
gcc compiler (2.7.2 is known to work).
configure fails, and you are going to send mail to 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!
mysqld or a MySQL client, run
./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_DEBUGThis causes clients to generate a trace file in `/tmp/client.trace'.
mysqld like this:
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
in `config.h' (generated by configure) for 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.
(Note that you will have to do this each time you run configure,
since doing so regenerates `config.h'.)
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). On other systems, warnings may occur due to
differences in system
include files. See 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 version of yacc).
Normally you don't have to do this, since
MySQL comes with an already-compiled `sql_yacc.cc' file.
If you are using gcc and want to have statically-linked code, use
LDFLAGS="-static" ./configure ...
On most systems, you can 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-pthreads, because we want to minimize our changes to this code.
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 switch to mysql
(-h or --host). This must be done if you have
configured the distribution using --without-server to build only
the client code, because the default
connection is to use Unix sockets.
When
MySQL is compiled using MIT-pthreads, system locking is 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 is
that all connections to the server fail.
> 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-pthreads. 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 gotten readline to work with MIT-pthreads. (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 get 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 when
the `mysql.so' library gets built (check the output from make
for `mysql.so' when you
compile the perl client).
-L/... is the path to the directory where `libgcc.a'
exists.
Change it to indicate where that library actually is located on your system.
Another problem may be that perl and MySQL
aren't both compiled with gcc.
You can solve this problem by compiling everything with gcc.
If you want to use the perl module on a system that doesn't support dynamic
linking (like SCO) you can always make a static version of perl with
DBI and DBD-mysql:
First you create a statically-linked DBI by doing:
perl Makefile.PL LINKTYPE=static make make install make perl
After this you must install the new perl. The output of
make perl will
contain the exact make command! On SCO this is
make -f Makefile.aperl inst_perl MAP_TARGET=perl
Next you create a statically linked DBD::mysql:
perl Makefile.PL LINKTYPE=static make make install make perl
You should also install the new perl (see the DBI perl
installation above).
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 too many processes try to connect very rapidly to
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 CFLAGS="-Xa -fast -xstrconst -mt" CXX=CC CXXFLAGS="-xsb -noex -fast -mt"
./configure
You may also have to change the line in configure that looks like this:
#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 have tried to compile
MySQL with the Sun compiler without enabling the multi-thread switch
-mt. Add -mt to CFLAGS and CXXFLAGS and try again.
If you get the following error when compiling MySQL with gcc:
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'
This means that your gcc is not configured for your version of Solaris!
The proper thing to do in this case is to get the newest version of egcs
or 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 tar 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
In this case you have to use GNU tar to unpack the distribution.
You can find a precompiled copy of 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
if you start it yourself. Normally the demon is started by safe_mysqld
and the flag is added automatically. This flag is needed due to a bug
in Linux file locking calls. This bug is known to exist as recently as
Linux version 2.0.33.
If you can't start mysqld or if mysql_install_db doesn't work,
please
continue reading! This only happens on Linux system with problems
in the LinuxThreads or libc/glibc libraries. There are a lot of simple
workarounds to get MySQL to work! The simplest is to use the
binary version of MySQL (not the RPM) for Linux x86; One nice aspect
of this version is that it's probably 10% faster than any version
you would compile yourself!
See section 10.2 How compiling and linking affects the speed of MySQL.
When using LinuxThreads you will see a minimum of three processes running. These are in fact threads. There will be one thread for the LinuxThreads manager, one thread to handle connections, and one thread to handle alarms and signals.
if you 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 have been some problems with the glibc RPMs from RedHat so if you have problems, check whether or not there are any updates!
On some older Linux distributions configure may produce an error
like: 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 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'
In Debian GNU/Linux, if you want MySQL to start automatically when the 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. RedHat 5.0 has also a similar problem with
some new glibc versions. More about this later.
If you install all the official RedHat patches (including glibc-2.0.6-9 and glibc-devel-2.0.6-9) MySQL should work out of the box (see 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 statically-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 an incorrect glibc or LinuxThreads version 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. 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
versions of glibc as new as 2.0.7-4!
For the source distribution of glibc 2.0.7 you can find a patch at
http://www.tcx.se/Download/Linux/glibc-2.0.7-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 statically-linked to avoid library conflicts and should work on all Linux systems!
If you want to compile this yourself, check how to debug mysqld!
MySQL comes with an internal debugger, so it's very easy to get
a trace file that probably will help solve your problem very quickly!
See section 18.10 Debugging MySQL.
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'.
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 of 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.
.c file. Copy this to the glibc
`./linuxthreads' directory.
CC=gcc CCFLAGS="-Dalpha_linux_port" CXX=gcc CXXFLAGS="-O3 -Dalpha_linux_port" ./configure --prefix=/usr/local/mysql
mysys/thr_lock and mysys/thr_alarm.
Test that these work!
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 -lpthread 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 may see 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 if you start it directly
from the command line. 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 also appears 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 with the version from OSF 4.0C:
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql
In some versions of OSF1, the alloca() function is broken. Fix
this by removing 'HAVE_ALLOCA' from `config.h'.
The alloca() function also may have an incorrect prototype in
/usr/include/alloca.h. This warning resulting from this can be ignored.
configure automatically will use the following thread libraries:
-with-named-thread-libs="-lpthread -lmach -lexc -lc".
When using gcc you can also try using:
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 SELECT statements, remove
HAVE_ALLOC &
HAVE_ALLOCA_H from `config.h'. If mysqladmin create doesn't
work, remove HAVE_READDIR_R from `config.h'. You may have to
remove HAVE_TERM_H as well.
Irix 6.2 doesn't support POSIX threads out of of the box. You must install these patches, which are 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. 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 command:
CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-thread-safe-client
If you get an error on make install that it can't find
`/usr/include/pthreads', configure
didn't detect that one needs MIT-pthreads on FreeBSD. This is fixed
by doing:
rm config.cache ./configure --with-mit-threads
If you get link errors when compiling mysqlperl (such as
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:
CFLAGS = -g -O2 to
CFLAGS = -O2 -DPIC -fpic
cd client
rm *.o
make libmysqlclient.a
cd ../perl
make
This shall be handled automatically in the future, we hope.
The FreeBSD make behaviour is slightly different from that of 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 one 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.7 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.
Make sure that you modify the /etc/hosts file so that the
localhost entry is correct (otherwise you will have problems
connecting to the database).
If you are using FreeBSD 2.2.6:
Don't forget to apply the ttcp and mmap-22 patches to the OS (for security reasons). Please see http://www.freebsd.org for these CERT patches.
You have to run configure with:
--with-named-thread-libs=-lc_r
The pthreads library for FreeBSD doesn't contain the sigwait
function and there are 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. Then follow 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
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
$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
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.
env CXX=shlicc++ CC=shlicc2 ./configure --prefix=/usr/local/mysql --localstatedir=/var/mysql --without-perl --with-unix-socket-path=/var/mysql/mysql.sockThe following is also known to work (again, all on one line):
env CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-unix-socket-path=/var/mysql/mysql.sock
--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 fixes 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. There has also been a lot of progress on a port to 'sco 3.2v4.2'.
gcc 2.7.2 in Skunkware 97 does not have GNU
as.
./configure in the `threads/src' directory and select the
SCO OpenServer
option. This command copies `Makefile.SCO5' to `Makefile'.
make.
cd to
`thread/src' directory, and run make install.
make when making MySQL.
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.
configure command should work (all on one line):
CFLAGS="-D_XOPEN_XPG4" CXX=gcc CXXFLAGS="-D_XOPEN_XPG4" ./configure --with-debug=yes --prefix=/usr/local/mysql --with-named-thread-libs="-lgthreads -lsocket -lgen -lgthreads" --with-named-curses-libs="-lcurses" --without-perlYou may get some problems with some include files. In this case you can find new SCO specific include files at: ftp://www.tcx.se/pub/mysql/Downloads/SCO/SCO-3.2v4.2-includes.tar.gz. You should unpack this in the `mysql-source-distributions/include' directory!
SCO development notes:
mysqld
with -lgthreads -lsocket -lgthreads.
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'.
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 are a couple of 'small' problems when compiling MySQL on HP-UX.
Below we describe some problems and workarounds when using the HP-UX compiler
and gcc 2.8.0.
gcc 2.8.0 can't compile readline on HP-UX (an internal compiler
error occurs).
MIT-pthreads can't be compiled with HP-UX compiler, because it can't compile
.S (assembler) files.
We got MySQL to compile on HP-UX 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
This compiles MySQL with the HP-UX compiler, except for the
MIT-pthreads part of the distribution, which is compiled with gcc.
As a service TCX provides a set of binary distributions of MySQL that are compiled at TCX or at sites where customers kindly have given us access to their machines.
These distributions
are generated with scripts/make_binary_distribution and are
configured with the following compilers and options.
| SunOS 4.1.4 2 sun4c | gcc 2.7.2.1 | CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql |
| SunOS 5.5.1 sun4u | 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 |
| SunOS 5.6 sun4u | 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 |
| SunOS 5.6 i86pc | gcc 2.8.1 | CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-low-memory |
| Linux 2.0.33 i386 | 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 LDFLAGS=-static ./configure --prefix=/usr/local/mysql --enable-assembler |
| SCO 3.2v5.0.4 i386 | gcc 2.7-95q4 | CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql |
| AIX 2 4 | gcc 2.7.2.2 | CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql |
| OSF1 V4.0 564 alpha | gcc 2.8.1 | CC=gcc CFLAGS=-O CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-low-memory |
| IRIX 6.3 IP32 | gcc 2.8.0 | CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql |
| BSDI BSD/OS 3.1 i386 | gcc 2.7.2.1 | CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql |
| BSDI BSD/OS 2.1 i386 | gcc 2.7.2 | CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql |
The MySQL-win32 version has by now proven itself to be very stable. The MySQL-win32 version has the same features as the corresponding Unix version of MySQL with the following exceptions:
ALTER TABLE
ALTER TABLE on a file if it is hdld 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
Win95 and threads
mysqld for an extended time on Win95 if one does many connections;
each
connection in MySQL creates a new thread! NT doesn't suffer from
this bug.
Blocking read
Here is some open issues for someone that may want to help us with the Win32 release:
mysqld
demon 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 one must use mysqladmin shutdown.
mysqld as a service with -install (on NT)
it would be nice
if one could also add default options on the command line.
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.
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
mailing list (mysql@tcx.se). Even if the problem isn't a bug,
mysqlbug gathers some system information that will help others solve your
problem! See section 2.3 I think I have found a bug. What information do you need to help me?.
> cd /usr/local > zcat /<where ever you put it>/mysql-3.20.0-SunOS5.tgz | tar xvf - > ln -s mysql-VERSION mysql
> scripts/mysql_install_dbSee section 4.14 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.
> bin/safe_mysqld --log &
> bin/mysqladmin verThat 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:
/usr/local/mysql)
/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.
CC=gcc CFLAGS="-O6 -mpentium -mstack-align-double -fomit-frame-pointer" CXX=gcc CXXFLAGS=-O6 -mpentium -mstack-align-double -fomit-frame-pointer -felide-constructors" LDFLAGS=-static ./configure --prefix=/usr/local/mysql -enable-assemblerAll of the above is typed as one long line!
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
The default privileges 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
It may happen that mysql_install_db doesn't install the privilege
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.
Installing a second mysqld daemon doesn't work when one daemon is running.
MYSQL_UNIX_PORT=/tmp/mysqld-new.sock MYSQL_TCP_PORT=3307 export MYSQL_UNIX_PORT MYSQL_TCP_PORT scripts/mysql_install_dbAfter 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.
MySQL mail archives.
See section 4.10.3 Linux notes for all versions
Can't connect to the server (when using MIT-pthreads)
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 localhostThe above is only a problem on system that doesn't have a thread library and MySQL has to use MIT-pthreads.
You don't have write access to create a socket file (in `/tmp'?)
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 mysqlAfter this you can execute the sql commands in mysql_install_db.
The paths may be different from what mysqld expects.
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 decide to upgrade MySQL sometime!
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.
MySQL 3.22 can read default startup options for the server and clients from options files.
MySQL reads default options from the following places on Unix:
| /etc/my.cnf | Global options. |
| /mysql-data-dir/my.cnf | Server specific options. |
| ~/.my.cnf | User specific options. |
| C:\my.cnf | Global options. |
| C:\mysql\data\my.cnf | Server specific options. |
| #comment | Comment lines starts with '#' or ';'. Empty lines are ignored |
| [group] | 'group' is the name of the program or group for which one wants to set options. |
| option | Same as --option on the command line |
| option=value | Same as --option=value on the command line |
set-variable = variable=value. This corresponds to the command
line option "--set-variable variable=value".
You will find a sample my.cnf file in the bin or scripts directory named
as my-example.cnf You can copy this to your home directory to experiment
with this.
There are some predefined groups:
| client | Options will be used for all MySQL clients (not mysqld). This is the perfect group to use if one wants to save one passwords in the option file. |
[client] port=3306 socket=/tmp/mysql.sock [mysqld] port=3306 socket=/tmp/mysql.sock set-variable = key_buffer=16M set-variable = max_allowed_packet=1M [mysqldump] quickHere is typical user option file:
[client] # The following commands will be sent to all standard MySQL clients password=my_password [mysql] no-auto-rehashIf one doesn't want to use the options file one can start the program with:
--no-defaults. This MUST be the first option to have any
effect!
Note for developers: The options files handling is implemented by simply
adding all matching options before any command line arguments. This
works nicely for programs that uses the last option if an option is used
many times. To change a program to use option files one has only to
add two lines to the old programs. Check any of the standard mysql clients
about how to do this.
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.
MEDIUMINT, SET, ENUM and the
different BLOB and TEXT types.
AUTO_INCREMENT, BINARY,
UNSIGNED and ZEROFILL.
BINARY attribute.
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.
INDEX or KEY in a CREATE TABLE
statement. See section 7.6 CREATE TABLE syntax
DROP column or CHANGE column in an ALTER TABLE
statement. See section 7.7 ALTER TABLE syntax.
DROP TABLE with the keywords IF EXISTS or with
more than one table.
LOAD DATA INFILE. This syntax is in many cases compatible with
Oracle's LOAD DATA INFILE. See section 7.14 LOAD DATA INFILE syntax.
" instead of ' to enclose strings.
\ character.
SET OPTION statement. See section 7.20 SET OPTION syntax
GROUP BY part.
This gives better performance for some very specific, but quite normal
queries.
See section