MySQL Reference Manual for version 3.21.33.


1 General Information about MySQL

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'.

1.1 What is MySQL?

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).

1.2 About this manual

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.

1.3 History of MySQL

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.

1.4 The main features of MySQL

1.5 General SQL information and tutorials

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

1.6 Useful MySQL-related links

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

1.7 What are stored procedures and triggers and so on?

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)..

2 MySQL mailing lists and how to ask questions/give error (bug) reports

2.1 Subscribing to/un-subscribing from the MySQL mailing list

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
This is for announcement of new versions of MySQL and related programs. This is a low volume list that we think all MySQL users should be on.
mysql
The main list for general MySQL discussion. Please note that some things should go to the specialized lists. It you post to the wrong list you may not get an answer!
mysql-digest
The above list in digest form. That means you 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 systems 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.

2.2 Asking questions or reporting bugs

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

2.3 I think I have found a bug. What information do you need to help me?

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!

  1. State which version of MySQL you are using (for example mysql-3.21.22.tgz). You can find out which version you are running by typing mysqladmin version.
  2. The manufacturer and model of machine you are working on.
  3. The operating system. For most operating systems you can get this from uname -a.
  4. Sometimes the amount of memory (real and virtual) is also relevant.
  5. 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.
  6. If the bug occurs during compilation, include the exact error messages and also a few lines around the offending code in the file from which you got the error.
  7. 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.
  8. If 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 dump your tables using 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.
  9. If the question is related to the privilege system, please include the output of 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!
  10. 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.

2.3.1 MySQL keeps crashing

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:

2.4 Guidelines for answering questions on the mailing list

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.

3 Licensing or When do I have/want to pay for MySQL?

Basic licensing issues:

See section J The MySQL server license.

3.1 How much does MySQL cost?

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
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.

3.2 How do I get commercial support?

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.

3.2.1 Types of commercial support

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.

3.2.1.1 Basic email support

One year of basic email support costs $200 (USD).

It includes:

  1. For MySQL-specific questions that don't belong on the MySQL mailing list (mysql@tcx.se), you can contact and expiration date when mailing any list to ensure a quick response.
  2. We guarantee a timely answer for your emails. We can't guarantee that we can solve any problem, but at least you will receive an answer if we can contact you by email.
  3. 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.
  4. Helping with unexpected problems when installing MySQL from a binary distribution on a supported platforms.
  5. We will help you with bugs and missing features. Any bugs that are found are fixed for the next MySQL release. If the bug is critical for you, we will mail you a patch for it as soon the bug is fixed. Critical bugs always have the highest priority for us, to ensure that they are fixed as soon as possible.
  6. This level of support does not cover installing MySQL from a source distribution.
  7. If you want us to help to optimize your system you have to upgrade to a higher level of support.

3.2.1.2 Extended email support

One year of extended email support costs $1000 (USD).

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

  1. Your email will be dealt with before normal email support users and non-registered users.
  2. 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.
  3. 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.
  4. Typical questions that are covered by extended email support are:
  5. You are allowed to slightly alter MySQL TODO. This will ensure that the features you really need will be implemented sooner than otherwise.

3.2.1.3 Login support

One year of email/phone/telnet support costs $2000 (USD).

Login support contains everything in extended basic email support with these additions:

  1. Your email will be dealt with even before mail from extended support users.
  2. Your suggestions for the further development of MySQL will be taken into very high consideration Realistic extensions that can be implemented in a couple of hours and that suit the basic goals of MySQL will be implemented as soon as possible.
  3. If you have a very specific problem we can try to log in on your system to solve the problem 'in place'.
  4. As any database vendor, we can't guarantee that we can rescue any data from crashed tables, but if the worst happens we will help you rescue as much as possible. MySQL has proven itself very reliable, but because of outside interference anything is possible.
  5. We will give you hints how to optimize your system and your queries.
  6. You are allowed to call a MySQL developer (in moderation) and discuss your MySQL related problems.

3.2.1.4 Extended login support

One year of extended email/phone/telnet support costs $5000 (USD).

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

  1. Your email has the highest possible priority.
  2. We will actively examine your system and help you optimize it and your queries. We may also optimize and/or extend MySQL to better suit your needs.
  3. You may also request special extensions just for you. For example:
    select my_calculation(column1,column2) from database;
    
  4. 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.
  5. 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!

3.3 How do I pay for licenses/support?

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

3.4 Who do I contact when I want more information about licensing/support?

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

3.5 What Copyright does MySQL use?

There are (at least) four different copyrights on the MySQL distribution.

  1. 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 the mysys, mystring and dbug libraries.
  2. Some small parts of the source (GNU getopt) are covered by the "GNU LIBRARY LIBRARY GENERAL PUBLIC LICENSE". See the `mysys/COPYING.LIB' file.
  3. Some small parts of the source (GNU readline) are covered by the "GNU GENERAL PUBLIC LICENSE". See the `readline/COPYING' file.
  4. Some parts of the source (the regexp library) are covered by a Berkeley style copyright.
  5. The other source needed for the MySQL server is AGPL. See the file PUBLIC for more info.

Our philosophy behind this is:

3.6 When may I distribute MySQL commercially without a fee?

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:

  1. 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;
  2. The only charge for downloading MySQL is a charge based on the distribution service and not one based on the content of the information being retrieved (i.e., the charge would be the same for retrieving a random collection of bits of the same size);
  3. The server or BBS is accessible to the general public, i.e., the phone number or IP address is not kept secret, and anyone may obtain access to the information (possibly by paying a subscription or access fee that is not dependent on or related to purchasing anything else).

If you want to distribute software in a commercial context that incorporates MySQL and you do not want to meet these conditions, you should contact TcX AB to find out about commercial licensing. 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.

3.7 I want to sell a product that can be configured to use MySQL

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.

3.8 I am running a commercial web server using MySQL

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).

3.9 Do I need a license to sell commercial perl/tcl/PHP/Web+ etc applications?

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?

3.10 Possible future changes in the licensing

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.

4 Compiling and installing MySQL

4.1 How do I get MySQL?

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:

North America:

South America:

Asia:

Australia:

4.2 Which MySQL version should I use?

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:

The first number. In this case 3
This describes the file format. So all version 3 releases have the same file format. When a version 4 appears, every table will have to be converted to the new format (nice tools for this will be included, of course).
The second number. 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

Note that all releases have at least been tested with:

An internal test suite
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.

4.3 How/when will you release updates?

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.

4.4 Which operating systems does MySQL support?

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.

4.5 Compiling MySQL from source code

What you need:

  1. GNU gzip to uncompress the distribution.
  2. A reasonable tar to unpack the distribution. GNU tar is known to work.
  3. A working ANSI 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.
  4. A good make program. If you have problems we recommend trying GNU make.

4.5.1 Quick installation overview

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 -
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 privilege tables. This needs to be done only 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. See section 4.14 Problems running mysql_install_db
'installation_directory'/bin/mysqladmin version
Check to see whether or not the server is running. 'installation_directory' is `/usr/local' unless you added some options to ./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.

4.5.2 Usual configure switches

Some of the options you may want to use when you run ./configure are described below:

4.5.3 Applying a patch

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.

4.6 Problems compiling?

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++.

  1. If your compile fails with the following error:
    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.
  2. If you want to add flags to your C or C++ compiler, add the flags to the CFLAGS and CXXFLAGS environment variables. For example:
    CC=gcc
    CFLAGS=-O6
    CXX=gcc
    CXXFLAGS=-O6
    export CC CFLAGS CXX CXXFLAGS
    
  3. If your make stops with
    Can't find Makefile.PL
    
    Then you should try using GNU make. Solaris and FreeBSD are known to have troublesome make programs.
  4. If you get error messages from make or error messages of the 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).
  5. 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).
  6. If 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!
  7. If you need to debug 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_DEBUG
    
    This causes clients to generate a trace file in `/tmp/client.trace'.
  8. If you get three errors when compiling 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'.)
  9. 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?.

4.7 General compilation notes

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 ...

4.8 MIT-pthreads notes (FreeBSD)

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(...)'

4.9 Perl installation comments

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).

4.10 Special things to consider for some machine/OS combinations

4.10.1 Solaris notes

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/.

4.10.2 SunOS 4 notes

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.

4.10.3 Linux notes for all versions

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.

4.10.3.1 Linux-x86 notes.

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.

4.10.3.2 RedHat 5.0

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.

4.10.3.3 Linux-Sparc notes.

In some implementations readdir_r is broken. The symptom is that SHOW DATABASES always returns an empty set. This can be fixed by removing HAVE_READDIR_R from `config.h'.

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.

4.10.3.4 Linux-Alpha notes.

The first problem is LinuxThreads. You must patch LinuxThreads for Alpha because the RedHat distribution uses an old (broken) LinuxThreads version.

  1. Obtain the glibc2.5c source from any GNU FTP site.
  2. 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.
  3. Configure and compile glibc (You have to read the manual how to do this together with LinuxThreads). Don't install this!
  4. Rename your old version of `/usr/lib/libpthread.a' to `/usr/lib/libpthread.a-old'.
  5. Copy the file `glibc.../linuxthreads/libpthread.a' to `/usr/lib'.
  6. Configure MySQL with the following command (everything on one row):
    CC=gcc CCFLAGS="-Dalpha_linux_port" CXX=gcc CXXFLAGS="-O3 -Dalpha_linux_port" ./configure --prefix=/usr/local/mysql
    
  7. Try to compile mysys/thr_lock and mysys/thr_alarm. Test that these work!
  8. 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.

4.10.4 Alpha-DEC-Unix notes

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.

4.10.5 Alpha-DEC-OSF1 notes

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 ....

4.10.6 SGI-IRIX notes

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

4.10.7 FreeBSD notes

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:

  1. First install everything.
  2. Change the line in `client/Makefile' that says CFLAGS = -g -O2 to CFLAGS = -O2 -DPIC -fpic
  3. cd client
  4. rm *.o
  5. make libmysqlclient.a
  6. cd ../perl
  7. 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.

4.10.7.1 FreeBSD-3.0 notes.

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.

4.10.8 BSD/OS 2.# notes

From Jan Legenhausen jleg@csl-gmbh.net:

I finally got mysqlperl working on BSDI2.1.

What I did was almost nothing:

  1. cd client
  2. 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!)
  3. rm *.o
  4. gmake libmysqlclient.a
  5. cd ../perl/mysqlperl
  6. make clean
  7. 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)
  8. perl Makefile.PL
  9. remove all old libmysqlclient.a's in /usr/lib and /usr/contrib/lib
  10. 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.

4.10.8.1 BSD/OS 3.# notes.

  1. Upgrade to BSD/OS 3.1. If that is not possible, install BSDIpatch M300-038.
  2. Use the following configuration command when installing MySQL (all on one line):
    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 (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 
    
  3. 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 fixes their thread scheduler).

You can change the directory locations if you wish, or just use their defaults by not specifying them.

4.10.9 SCO notes

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'.

  1. 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.
  2. You need the port of GCC 2.5.? for this product and the Development system. They are required on this version of SCO UNIX. You cannot just use the GCC Dev system.
  3. 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/Downloads/SCO/FSU-threads-3.5c.tar.gz.
  4. 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 Development 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 without a good port. The port for this product requires the SCO UNIX Development system. Without it, you are missing the libraries and the linker that is needed.
  5. To build FSU pthreads in your system do the following:
    1. Run ./configure in the `threads/src' directory and select the SCO OpenServer option. This command copies `Makefile.SCO5' to `Makefile'.
    2. Run make.
    3. To install in the default `/usr/include' directory, login as root and cd to `thread/src' directory, and run make install.
  6. Remember to use GNU make when making MySQL.
  7. 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.
  8. With SCO 3.2V4.2 you must use a FSU-pthreads version 3.5c or newer. The following 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-perl
    
    You 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:

4.10.10 IBM-AIX notes

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.

4.10.11 HP-UX notes

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.

4.11 TCX binaries

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
Anyone who has more optimal switches for any of the above configurations can always mail us at mysql-developer@tcx.se about this. The RPM distributions are user-contributed distributions.

4.12 Win32 notes

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
One can't do an 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
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 thread. Because of this one shouldn't run 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
MySQL uses a blocking read for each connection. For the moment this means that 'mysqladmin shutdown' will not take down MySQL completely before all clients has closed the connection. We plan to fix this in the near future.

Here is some open issues for someone that may want to help us with the Win32 release:

When one does a suspend on a laptop running Win95, the 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.
It would also be real nice to be able to kill mysqld from the task manager. For the moment one must use mysqladmin shutdown.
When registering mysqld as a service with -install (on NT) it would be nice if one could also add default options on the command line.
<<<<<<< manual.texi GUI versions of the standard mysql clients (mysql, mysqlshow, mysqladmin, and mysqldump would be nice.
Port readline to Win32 for use in the mysql command line tool. GUI versions of the standard MySQL clients (mysql, mysqlshow, mysqladmin, and mysqldump) would be nice.
It would be nice if the 'read' and 'write' to sockets in `net.c' would be interruptible. This would make it possible to kill open threads with mysqladmin kill on Win32.
Documentation of which windows programs work with MySQL-WIN32/MyODBC and what one has to do to get them working.
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.
Add more options to MysqlManager
Change the communication protocol between the server/client to use windows intern communication instead of sockets and TCP/IP.

Other Win32 specific issues are described in the README file that comes with the MySQL-win32 distribution.

4.13 Installation instructions for MySQL binary releases

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?.

  1. 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'.
  2. 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.
  3. 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
    
  4. 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 privilege tables if they don't exist:
    > scripts/mysql_install_db
    
    See 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.
  5. Normally, start the MySQL server daemon (not needed the first time):
    > bin/safe_mysqld --log &
    
  6. 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
    

4.13.1 To get perl work do the following:

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:

  1. You're executing the script from the base mysql installation directory (for example /usr/local/mysql)
  2. 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.

4.13.2 Linux notes

4.13.3 HP-UX notes

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.

4.13.4 Linking client libraries

Clients have to be linked with: -lmysqlclient

4.14 Problems running mysql_install_db

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.
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-pthreads)
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-pthreads.
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

4.15 Problems starting MySQL

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!

4.16 Automatic start/stop of 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.

4.17 Option files

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.
Note the that 'mysql-data-dir' in the above is the directory that is specified compiled time! (Note the one specified with --data) MySQL reads default option from the following places on Win32:
C:\my.cnf Global options.
C:\mysql\data\my.cnf Server specific options.
The same option in a later file overrides the same option in a earlier file. Command line options overrides file options. The following programs supports options files: mysql, mysqladmin, mysqld, mysqldump, mysqlimport, isamchk and pack_isam. One can in the options file use all long options that a program supports! Run the program with --help to get a list of available options. A option file can contain lines with the following syntaxes:
#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
Note that for options and values all pre- and end blanks are automatically deleted. You may use the escape sequences: \b \t \n \r \\ and \s (\s == blank) in your value string. If you want to set a mysqld variable you have to use the syntax: 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.
Here is a typical global option file:
[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
socket=/tmp/mysql.sock
set-variable = key_buffer=16M
set-variable = max_allowed_packet=1M

[mysqldump]
quick
Here is typical user option file:
[client]
# The following commands will be sent to all standard MySQL clients
password=my_password

[mysql]
no-auto-rehash
If 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.

5 How standards-compatible is MySQL?

5.1 What extensions has MySQL to ANSI SQL92?

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.

5.2 What functionality is missing in MySQL?

The following functionality is missing in the current version of MySQL. For the priority of new extensions you should consult: The MySQL Todo list. That is the latest version of the Todo list in this manual. See section F List of things we want to add to MySQL in the future (The TODO)..

5.2.1 Sub-selects

The following will not work in MySQL:

SELECT * from table WHERE id IN (SELECT id from table2)

MySQL only supports INSERT ... SELECT... and REPLACE ... SELECT.... Independent sub-selects will be probably be available in 3.22.0. One can now use the function IN() in other context however.

5.2.2 SELECT INTO TABLE

MySQL doesn't yet support SELECT ... INTO TABLE.... Currently MySQL only supports SELECT ... INTO OUTFILE..., which is basically the same thing..

5.2.3 Transactions

Transactions are not supported. MySQL will shortly support atomic operations which is like transactions without rollback. With atomic operations you can make a bunch of insert/select/whatever commands and be guaranteed that no other thread will interfere. In this context you won't usually need rollback. Currently you can do this with the help of the LOCK TABLES/UNLOCK TABLES command. See section 7.19 LOCK TABLES syntax

5.2.4 Triggers

Triggers are not supported. The planned update language will be able to handle stored procedures, but without triggers. Triggers usually slow down everything, even for queries when they aren't needed.

5.2.5 Foreign Keys

The FOREIGN KEY syntax in MySQL exists only for compatibility with other SQL vendors CREATE TABLE commands: It doesn't do anything. The FOREIGN KEY syntax without ON DELETE .. is mostly used for documentation purposes. Some ODBC applications may use this to produce automatic WHERE clauses though, but this is usually easy to override. FOREIGN KEY is sometimes used as a constraint check, but this check is in practice unnecessary if rows are inserted into the tables in the right order. MySQL only supports these commands because some application require them to exists (but not work!).

In MySQL one can work around the problem that ON DELETE ... isn't implemented by adding the approative DELETE statement to the application when one deletes records from a table that has FOREIGN KEY. In practice this is as quick (in some case quicker) and much more portable than using FOREIGN KEY.

Foreign keys are something that makes life very complicated, because the foreign key definition must be stored in a database and implementing them would mean that the whole 'nice approach' of using files that can be moved, copied and removed would be destroyed.

In the near future we will extend FOREIGN KEYS so that at least the information will be saved and may be retrieved by mysqldump and ODBC.

5.2.5.1 Some reasons NOT to use FOREIGN KEYS

There are so many problems with the FOREIGN KEYs that we don't know where to start.

The only nice aspect of foreign key is that it gives ODBC and some other client programs the ability to see how a table is connected and use this to show connection diagrams and to help building applicatons.

MySQL will soon store the FOREIGN KEY definitions so that a client can ask and receive an answer how the original connection was made. The current .frm file format does not have any place for it.

5.2.6 Views

MySQL doesn't support views, but this is on the TODO.

5.2.7 -- as start of a comment

Some other SQL databases have -- as start comment. MySQL has # as the start comment character, even if the mysql command line tool removes all lines that starts with --. You can also use the C comment style /* this is a comment */ with MySQL. See section 7.24 Comment syntax

MySQL will not support this degenerated comment style because we have had many problems with automatically generated SQL queries that have used something like the following code:

UPDATE table_name SET credit=credit-!payment!

Where instead of !payment! we automatically insert the value of the payment.

What do you think will happen when 'payment' is negative?

Because 1--1 is legal in SQL, we think is terrible that '--' means start comment.

If you have a sql program in a textfile that contains -- comments you should use:

replace " --" " #" < text-file-with-funny-comments.sql | mysql database.

instead of the normal

mysql database < text-file-with-funny-comments.sql

You can also change the -- to # comments in the command file:

replace " --" " #" -- text-file-with-funny-comments.sql

Change them back with:

replace " #" " --" -- text-file-with-funny-comments.sql

5.3 What standards does MySQL follow?

Entry level SQL92. ODBC level 0-2.

5.4 What functions exist only for compatibility?

5.5 Limitations of BLOB and TEXT types

If you want to GROUP BY or ORDER BY on a BLOB or TEXT field, you must make the field into a fixed length object. The standard way to do this is with the SUBSTRING functions. If you don't do this only the first max_sort_length (default=1024) will considered when sorting.

SELECT comment from table order by substring(comment,20);

5.6 How to cope without COMMIT-ROLLBACK

MySQL doesn't support COMMIT-ROLLBACK. The problem with COMMIT-ROLLBACK is that to handle this efficiently it would require a completely different table layout than MySQL uses today. MySQL would also need extra threads that do automatic cleanups on the tables and the disk usage would be much higher. This would make MySQL about 2-4 times slower than it is today. One of the reasons that MySQL is so much faster than almost all other SQL databases (typical times are at least 2-3 times faster) is the lack of COMMIT-ROLLBACK.

For the moment, we are much more for implementing the SQL server language (someting like stored procedures). With this you would very seldom really need COMMIT-ROLLBACK. This would also give much better performance.

Loops that need transactions can normally be coded with the help of LOCK TABLES, and one doesn't need cursors when one can update records on the fly.

We have transactions and cursors on the TODO but not quite prioritised. If it is implemented it will be as a option to CREATE TABLE. That means that COMMIT-ROLLBACK will only work on those tables and only those tables will be slower.

We at TcX have a greater need for a real fast database than a 100% general database. Whenever we find a way to implement these without any speed loss we will probably do it. For the moment there are many more important things to do. Check the TODO for how we prioritise things at the moment. Customers with higher levels of support can alter this, so things may be reprioritised.

The current problem is actually ROLLBACK. Without ROLLBACK you can do anything with LOCK TABLES. To support ROLLBACK MySQL would have to be changed to store all old records that were updated and revert everything back to the starting point if ROLLBACK was issued. For simple cases this isn't that hard to do (the current isamlog could be used for this), but if one wants to have ROLLBACK with ALTER/DROP/CREATE TABLE it would make everything much harder to implement.

To avoid using ROLLBACK one can do:

  1. LOCK TABLES ...
  2. Test conditions.
  3. Update if everything is ok.
  4. UNLOCK TABLES

This is usually much faster, but not always. The only thing this doesn't handle if someone does a kill on the process.

One can also use functions to update things in one operation. By doing all updates relatively and/or only updating those fields that actually have changed one can get a very efficient application.

For example, when we are doing updates on some customer information, we only update the customer data that has changed and only test that none of the changed data, or data that depends on the changed data, has changed in the original row. The test for change is done with the WHERE clause in the UPDATE statement. If the record wasn't updated we give the client a message: "Some of the data you have changed has been changed by another user", and then we show the old row versus the new row in a window. The user can then decide which version of the customer record he should use.

This gives us something like 'column locking' but actually even better, because we only update some of the columns with relative information. This means that a typical update statement looks something like:

UPDATE tablename SET pay_back=pay_back+'relative change'

UPDATE customer set customer_date='current_date', address='new address',
phone='new phone', money_he_owes_us=money_he_owes+'new_money' where
customer_id=id and address='old address' and phone='old phone';

As you can see, this is very efficient and even if another client has changed the 'money_he_owes_us' or 'pay_back' amount this will still work.

In many cases, users have wanted ROLLBACK and/or LOCK TABLES to manage unique identifiers for some tables. This can be handled much more efficiently by using an AUTO_INCREMENT column and the MySQL API function mysql_insert_id. See section 17.1.3 How can I get the unique ID for the last inserted row?

At TcX we have never had any need for row level locking as we have always been able to code around it. I know some cases that really need row locking, but they are very few. If you want to have row level locking you can do something like:

UPDATE table_name SET row_flag=1 WHERE id=ID;

MySQL returns affected rows = 1 if the row was found and row_flag wasn't 1 in the original document. On the TODO there is GET_LOCK and RELEASE_LOCK for those that want to implement application level locking.

6 How do MySQL privileges work?

MySQL has an advanced but non-standard security/privilege system.

6.1 What can the privilege system do?

The basic function of the MySQL privilege system is to give a username on a host select, insert, update and delete privileges on a database.

Extra functionality includes the ability to have a anonymous user and give permission to use MySQL specific funtions like LOAD DATA INFILE.

Please note that the user names for a SQL database, like MySQL, has nothing to do with Unix users. As a convenience most MySQL clients tries to log in with the current user name, but this may be changed with the --user switch. This means that you can't in any way make a database secure without having passwords for all users.

6.2 How does the privilege system work?

In MySQL the combination of host and user is the unique identity. Don't think of users, think of host+user and everything should be much clearer. You can for example have a user named 'Robb' at two different hosts (with different privileges) in MySQL without any conflicts.

The MySQL privilege system makes sure that each user may do exactly the things that they are supposed to be allowed to do. The system decides to grant different privileges depending on which xuser connects from which host to which database.

You can always test your privileges with the script mysqlaccess, which Yves Carlier has provided for the MySQL distribution. See section 6.7 Why do I get this Access denied? error? See section 6.8 How to make MySQL secure against crackers

All privileges are stored in three tables. user, host and db.

Everything granted in the user table is valid for every database that cannot be found in the db table. For this reason, it might be wise to grant users (apart from superusers) privileges on a per-database basis only.

The host table is mainly there to maintain a list of "secure" servers. At TcX host contains a list of all machines on the local network. These are granted all privileges.

The connecting user's privileges are calculated by the following algorithm:

  1. First sort the tables by:
    Table Sorted by
    host host without wild/hosts with wild/empty hosts
    db host without wild/hosts with wild/empty hosts
    user host/user
    Host by putting hosts without wildcards first, followed by hosts with wildcards and entries with host = "". Within each host, sort by user using the same rules. Finally, in the db table, sort by db using the same rules. In the steps below, we will look through the sorted tables and always use the first match found.
  2. Get the privileges for the connecting user from the user table using the first match found. Call this set of privileges Priv.
  3. Get the privileges for the connecting user from the db table using the first match found.
  4. If host = "" for the entry found in the db table, AND Priv with the privileges for the host in the host table, i.e. remove all privileges that are not "Y" in both. (If host <> "", Priv is not affected. In such cases, host must have matched the connecting host's name at least partially. Therefore it can be assumed that the privileges found in this row match the connecting host's profile.)
  5. OR (add) Priv with the privileges for the user from the user table, i.e. add all privileges that are "Y" in user.
Remember that if you change the tables you must do a mysqladmin reload to make the changes take effect. The connecting user gets the set of privileges Priv. Let's show an example of the sorting and matching! Suppose that the user table contains this:
+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| %         | root     | ...
| %         | jeffrey  | ...
| localhost | root     | ...
| localhost |          | ...
+-----------+----------+-
Then the search order will be: So jeffrey attempting to connect on localhost will be matched by the localhost/any line, not by the any/jeffrey line. The first match found is used! So if you have access problems, print out the user table, sort it by hand, and see where the match is being made. Here follows an example to add a user 'custom' that can connect from hosts 'localhost', 'server.domain' and 'whitehouse.gov'. He wants to have password 'stupid'. The database 'bankaccount' he only want to use from 'localhost' and the 'customer' database he wants to be able to reach from all three hosts.
shell> mysql mysql.
mysql> insert into user (host,user,password)
       values('localhost','custom',password('stupid'));
mysql> insert into user (host,user,password)
       values('server.domain','custom',password('stupid'));
mysql> insert into user (host,user,password)
       values('whitehouse.gov','custom',password('stupid'));

mysql> insert into db
       (host,db,user,Select_priv,Insert_priv,Update_priv,Delete_priv,
        Create_priv,Drop_priv)
       values
       ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysql> insert into db
       (host,db,user,Select_priv,Insert_priv,Update_priv,Delete_priv,
        Create_priv,Drop_priv)
       values
       ('%','customers','custom','Y','Y','Y','Y','Y','Y');
You can of course also use xmysqladmin, mysql_webadmin, mysqladmin and even xmysql to insert/change and update values in the privilege tables. You can find these utilities in the Contrib directory.

6.3 The privilege tables

The grant tables privileges on rows are select, insert, update and delete.

The table and database privileges are create and drop. Create and drop are for both tables and databases. Since a user with a drop grant can delete any table, this is the same thing as a drop grant for the database.

Other privileges give the right to use files (for LOAD DATA INFILE and SELECT INTO OUTFILE) and to use the administrative commands shutdown, reload, refresh and process.

The privilege system is based on 3 tables.

user table
Contains all host+user combinations that are allowed to connect to the mysql server, together with their optional passwords. The user table has the following columns:
Field Type Key Default
Host char(60) PRI ""
User char(16) PRI ""
Password char(16) - ""
Select_priv enum('N','Y') - N
Insert_priv enum('N','Y') - N
Update_priv enum('N','Y') - N
Delete_priv enum('N','Y') - N
Create_priv enum('N','Y') - N
Drop_priv enum('N','Y') - N
Reload_priv enum('N','Y') - N
Shutdown_priv enum('N','Y') - N
Process_priv enum('N','Y') - N
File_priv enum('N','Y') - N
  • db table Contains which databases a host+user is allowed to use, and what he can do with the tables in each database. The db table has the following columns:
    Field Type Key Default
    Host char(60) PRI ""
    Db char(64) PRI ""
    User char(16) PRI ""
    Select_priv enum('N','Y') - N
    Insert_priv enum('N','Y') - N
    Update_priv enum('N','Y') - N
    Delete_priv enum('N','Y') - N
    Create_priv enum('N','Y') - N
    Drop_priv enum('N','Y') - N
  • host table Is only used in big networks as a lookup for empty host entries in the db table. This means that if you want a user to be able to use the database from all hosts in your network, you should put " as the host name in the db table. In this case the host table should contain a entry for every host in your network. The host table has the following columns:
    Field Type Key Default
    Host char(60) PRI ""
    Db char(64) PRI ""
    Select_priv enum('N','Y') - N
    Insert_priv enum('N','Y') - N
    Update_priv enum('N','Y') - N
    Delete_priv enum('N','Y') - N
    Create_priv enum('N','Y') - N
    Drop_priv enum('N','Y') - N
  • You can use an entry like 123.444.444.% in the host table to give every user on an IP C-net access. To avoid the possibility that somebody tries to fool this setup by naming a host 123.444.444.somewhere.com, MySQL disallows all hostnames that start with digits and a dot. So if your host is named something like 1.2.foo.com it will never be allowed with name matching. Use the IP number in this case.

    6.4 Adding new user privileges to MySQL

    To add privileges to the MySQL database:

    This assumes the current user has insert privileges for the mysql db table and reload privileges. The server (mysqld) has to be running. If it is not, start it with safe_mysqld --log &.

    > mysql mysql
      insert into user values ('%','monty',password('something'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y') ;
      insert into user (host,user,password) values('localhost','dummy',") ;
      insert into user values ('%','admin',",'N','N','N','N','N','N','Y','N','Y','Y') ;
      quit
    > mysqladmin reload
    

    This makes three new users:

    Monty
    Full superuser, but must use password when using MySQL.
    admin
    Doesn't need a password but is only allowed to use mysqladmin reload, mysqladmin refresh and mysqladmin processlist. May be granted individual database privileges through table db.
    dummy
    Must be granted individual database privileges through table db.

    6.5 Default privileges

    The default privileges (set in `scripts/mysql_install_db') let root do anything. Any user can do anything with any database whose name is 'test' or starts with 'test_'. A normal user can't use mysqladmin shutdown or mysqladmin processlist. See the script (`scripts/mysql_install_db') for an example on how to add other users.

    The privilege tables are read into mysqld with mysqladmin reload.

    6.6 An example of permission setup

    A common mistake is to forget that passwords are stored encrypted, which leads to something like:

    INSERT INTO user VALUES ('%','jeffrey','bLa81m0','Y','Y','Y','N','N','N','N','N', 'N','N');

    Then (of course) a mysqladmin reload to make the authentication change take effect, then trying to connect to the server:

    $ ./mysql -h sqlserver -u jeffrey -p bLa81m0 test
    Access denied
    

    Try this instead:

    INSERT INTO user VALUES
    ('%','jeffrey',password('bLa81m0'),'Y','Y','Y','N','N','N','N','N','N','N');
    

    As before, mysqladmin reload to make the authentication change take effect.

    Now things should work.

    6.7 Why do I get this Access denied? error?

    6.8 How to make MySQL secure against crackers

    To make a MySQL system secure you should think about the following:

    The following options to mysqld affects security:

    --secure
    Check that the ip that was returned from get_hostbyname resolves back to the original hostname. This is done to make it harder for someone on the outside to get access by simulating another host. This options also adds some sanity checks of hostnames. This is turned off by default in MySQL 3.21 since it sometimes takes a long time to check this. MySQL 3.22 caches hostnames and has this enabled as default.
    --skip-grant-tables
    Do not use the privilege system att all. This gives everyone full access to all databases!
    --skip-name-resolve
    Hostnames are not resolved. All hostnames in the privilege tables must be IP-numbers or 'localhost'.
    --skip-networking
    Don't allow connections over the network (TCP/IP). All connections to mysqld are done with Unix sockets. This options doesn't work very good on systems that uses MIT-pthreads as MIT-pthreads doesn't support Unix sockets.

    7 MySQL language reference

    7.1 Literals. How do you write strings and numbers?

    7.1.1 Strings

    A string may have ' or " around it.

    \ is a escape character. The following escape characters are recognised:

    \0
    An ascii 0 character.
    \n
    A newline character.
    \t
    A tab character.
    \r
    A return character.
    \b
    A backspace character.
    \'
    A ' character.
    \"
    A " character.
    \\
    A \ character.
    \%
    A % character. This is used in wildcard strings to search for %.
    \_
    A _ character. This is used in wildcard strings to search for _.
    A ' inside a string started with ' may be written as ".
    A " inside a string started with " may be written as "".
    

    Some example selects that shows how it works.

    MySQL> select 'hello', "'hello'", '""hello""', "'h"e"l"l"o"', "hel""lo";
    1 rows in set (0.00 sec)
    
    +-------+---------+-----------+-------------+--------+
    | hello | 'hello' | ""hello"" | 'h'e'l'l'o' | hel"lo |
    +-------+---------+-----------+-------------+--------+
    | hello | 'hello' | ""hello"" | 'h'e'l'l'o' | hel"lo |
    +-------+---------+-----------+-------------+--------+
    
    mysql> select 'hello', "hello", '""hello""', "'ello", 'e"l"lo', '\'hello';
    1 rows in set (0.00 sec)
    
    +-------+-------+-----------+-------+--------+--------+
    | hello | hello | ""hello"" | 'ello | e'l'lo | 'hello |
    +-------+-------+-----------+-------+--------+--------+
    | hello | hello | ""hello"" | 'ello | e'l'lo | 'hello |
    +-------+-------+-----------+-------+--------+--------+
    
    mysql> select "This\nIs\nFour\nlines";
    1 rows in set (0.00 sec)
    
    +--------------------+
    | This
    Is
    Four
    lines |
    +--------------------+
    | This
    Is
    Four
    lines |
    +--------------------+
    

    If you want to insert binary data into a blob the following characters must be represented by escape sequences:

    \0
    Ascii 0. Should be replaced with "\0" (A backslash and a 0 digit).
    \
    Ascii 92, backslash
    '
    Ascii 39, Single quote
    "
    Ascii 33, Double quote

    One doesn't have to escape " inside ' and " inside '.

    If you write C code you can use the C API function mysql_escape_string(char *to,char *from,uint length) to escape characters for the INSERT clause. (Note that 'to' must be at least 2 times bigger than from). In perl you can use the quote function.

    You should run the escape function on every possible string that may have a one of the above special characters!

    7.1.2 Numbers

    Integers are just a sequence of digits. Floats use . as a decimal separator.

    Examples of valid numbers are: 1221, 294.42, -32032.6809e+10.

    7.1.3 NULL

    When using the text file export formats, NULL may be represented by \N. See section 7.14 LOAD DATA INFILE syntax

    7.1.4 Database, table, index and column names

    Database, table, index and column names all follow the same rules in MySQL.

    A name consist of alphanumeric characters from the default character set and also '_' and '$'. The default character set is ISO-8859-1 Latin1, but may be changed when compiling MySQL.

    Since MySQL needs to be able to decide if something is a name or a number the following special cases occurs.

    Punctuation characters like . and @ are not allowed in names since they will be used to extend MySQL.

    In MySQL you can refer to a column with some of the following syntaxes:

    If you are using 'column' or 'table.column' you will get an error if the name is not unique among the used tables!

    7.2 Column types

    The following column types are supported:

    Name Description Size
    TINYINT[(M)] [UNSIGNED] [ZEROFILL] A very small integer. Signed range -128 - 127. Unsigned range 0 - 255. 1
    SMALLINT[(M)]. [UNSIGNED] [ZEROFILL] A small integer. Signed range -32768 - 32767. Unsigned range 0 - 65535. 2
    MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] A medium integer. Signed range -8388608-8388607. Unsigned range 0 - 16777215. 3
    INT[(M)] [UNSIGNED] [ZEROFILL] A normal integer. Signed range -2147483648 - 2147483647. Unsigned range 0 - 4294967295. 4
    BIGINT[(M)] [UNSIGNED] [ZEROFILL] A large integer. Signed range -9223372036854775808 - 9223372036854775807. Unsigned Range 0 - 18446744073709551615. Because all arithmetic is done with signed BIGINT or DOUBLE, one shouldn't use unsigned big integers bigger than 9223372036854775807 (63 bits) with anything else than bit functions! 8
    FLOAT(Precision) A small floating point number. Precision can be 4 or 8. FLOAT(4) is a single precision number and FLOAT(8) is a double precision number (se the DOUBLE entry). This syntax is for ODBC compatibility. Range -3.402823466E+38F - -1.175494351E-38, 0, -1.175494351E-38 - 3.402823466E+38F. 4
    FLOAT[(M,D)] A small floating point number. Cannot be unsigned. Range -3.402823466E+38F - -1.175494351E-38, 0, -1.175494351E-38 - 3.402823466E+38F. 4
    DOUBLE PRECISION[(M,D)] A normal floating point number. Cannot be unsigned. Range -1.7976931348623157E+308 - -2.2250738585072014E-308, 0, 2.2250738585072014E-308 - 1.7976931348623157E+308. 8
    REAL[(M,D)] Same as DOUBLE 8
    DECIMAL [(M,D)] An unpacked floating point number. Cannot be unsigned. Currently the same range maximum range as a double. Behaves as a CHAR column M+D
    NUMERIC [(M,D)] Same as DECIMAL M+D
    TIMESTAMP [(M)] An automatic timestamp. If you have many TIMESTAMP columns only the first one is automatic 4
    DATE A type to store date information. Uses the "YYYY-MM-DD" syntax, but may be updated with a number or a string. Understands at least the following syntaxes: 'YY-MM-DD', 'YYYY-MM-DD', 'YYMMDD' and full timestamps (YYYYMMDDHHMMDD). Range 0000-00-00 to 9999-12-31. 3
    TIME A type to store time information. Uses the "HH:MM:SS" syntax, but may be updated with a number or a string. Understands at least the following syntaxes: 'HH:MM:SS, 'HHMMSS', 'HHMM', 'HH'. 3
    DATETIME A type to store date and time information. Format "YYYY-MM-DD HH:MM:SS". Takes 8 bytes. Range '0000-01-01 00:00:00' - '9999-12-31 23:59:59'. 8
    YEAR A type to store years. Format "YYYY" or "YY". Takes 1 byte. Range 0, 1901-2155. 2 digits years in the range 00-69 is assumed to be 2000-2069 and will be sorted correctly. (now type for MySQL 3.22) 1
    CHAR(M) [binary] A fixed length string that is always filled up with spaces to the specified length. Range 1 - 255 characters. All end space are removed when retrieved. Is sorted and compared case insensitively unless the binary keyword is given. M
    VARCHAR(M) [binary] A variable length string that is stored with its length. NOTE: All end space are removed when storing it (not ANSI SQL). Maximum range 1 - 255 characters. Is sorted and compared case insensitively unless the binary keyword is given. @tab L+1
    TINYTEXT and TINYBLOB A TEXT/BLOB with max length of 255 characters. L+1
    TEXT and BLOB A TEXT/BLOB with max length of 65535 characters. L+2
    MEDIUMTEXT and MEDIUMBLOB A TEXT/BLOB with max length of 16777216 characters. L+3
    LONGTEXT and LONGBLOB A TEXT/BLOB with max length of 4294967295 characters. L+4
    ENUM('value','value2',...) A string object that can have only one set of allowed values (or NULL). See section 7.2.1 More about data types 1 or 2
    SET('value','value2',...) A string object that can have one or many values of a set of allowed values. See section 7.2.1 More about data types. 1-8

    7.2.1 More about data types

    7.2.1.1 Database size info.

    In the above table L means the actual length of a instance and M the maximum length. So L+1 for "abcd" means 5 bytes in the database.

    If you use any data type with an L in the length field you will get a variable length record format.

    7.2.1.2 The numeric types

    All integer types can have an optional argument unsigned. This can be used when you only want to allow positive numbers in the column or you need a little bigger numerical range for the column.

    Also for all integer columnsn, the optional argument ZEROFILL means that the column will be padded with zeroes up to the maximum length.

    Max display size and decimals are for formatting and calculation of maximum column width.

    When storing a value in an integer that is outside its range, MySQL stores the maximum (or minimum) possible value. When doing an ALTER TABLE or LOAD DATA INFILE one gets these conversions as 'warnings'. We have on the TODO to fix INSERT and UPDATE so they can return warnings, but this is scheduled for the next protocol change.

    For example when storing -999999999999999 into an int column the value ends up as -2147483648. And 9999999999999999 ends up as 2147483647.

    And if the int is unsigned the stored values above becomes 0 and 4294967296.

    The same rules go for all other integer types.

    When returning data for an int(4) column that exceeds the allocated space, MySQL will return 9.99. If the operation is an UPDATE a warning will be issued.

    Note that a type like decimal(4,2) means maximum 4 characters with two decimal points. That gives a range between -.99 -> 9.99.

    To avoid some rounding problems, MySQL always rounds everything that it stores in any floating point column according to the number of decimals. This means that 2.333 stored into float(8,2) is stored as 2.33.

    7.2.1.3 TIMESTAMP type

    Has a range of 1 Dec 1970 time 0.00 to sometime in the year 2106 and a resolution of one second. A TIMESTAMP column will automatically be updated on INSERT and UPDATE statements if set to NULL or if the column is not updated in the statement. Can be (part of) an index. Note that if you have many timestamp columns in a row, then only the first timestamp column will be automatically updated. Any timestamp column will be set to the current time if set to NULL. Depending on the display size one gets one of the following formats: "YYYY-MM-DD HH:MM:SS", "YY-MM-DD HH:MM:SS", "YYYY-MM-DD" or "YY-MM-DD".

    7.2.1.4 TEXT and BLOB types

    These are objects that can have a variable length without upper limit. All TEXT and BLOB objects are stored with their length (saved in 1 to 4 bytes depending on the type of object). The maximum TEXT and BLOB length you can use is dependent on available memory and client buffers. The only differences between TEXT and BLOB is that TEXT is sorted and compared case insensitively while BLOB is compared case sensitively (by character values). TEXT and BLOB objects CANNOT be an index.

    A BLOB is a binary large object which can hold any amount of data. There are 4 kinds of blobs See section 7.2 Column types. Normally one can regard a BLOB as a VARCHAR without a specified limit.

    TEXT is a BLOB that is sorted and compared case insensitively.

    A BLOB/TEXT column may not be bigger that the message buffer. Note that you have to change the message buffer on both the server and the client. See section 10.1 How does one change the size of MySQL buffers?.

    MyODBC defines BLOBs as LONGVARBINARY and TEXTs as LONGVARCHAR.

    Restrictions for BLOB and TEXT columns:

    1. A BLOB or TEXT cannot be an index or a part of an index
    2. When one sorts or groups a BLOB or TEXT, only the first max_sort_length (default 1024) of the blob is used. This value can be changed by the -O option when starting the mysqld daemon. One can group on an expression involving a BLOB/ TEXT: SELECT id,SUBSTR(blob,1,100) GROUP BY 2
    3. There is no end space truncation for BLOB and TEXT as there is for CHAR and VARCHAR.

    7.2.1.5 ENUM type

    A string object that can have only one of a set of allowed values. The value to be stored may be given case independently. If one tries to store a non-existing value, "" is stored. If used in a number context this object returns/stores the value index. If there is less than 255 possible values this object occupies 1 byte, else two bytes (with a maximum of 65535 different values). Note that if an integer is put in the ENUM you get the corresponding string with the first counting as number 1. (0 is reserved for wrong enum values). Sorting on ENUM types are done according to the order of the strings in the enum. If declared NOT NULL the default value is the first value, else the default value is NULL.

    For example the column test ENUM("one","two", "three") can have any of these values:

    NULL
    "one"
    "two"
    "three"
    

    7.2.1.6 SET type

    A string object that can have one or many values from a set of allowed values. Each value is separated by a ','. If used in a number context this object returns/stores the bit positions of the used values. This object occupies (number_of_different_values-1)/8+1 bytes, rounded up to 1,2,3,4 or 8. One can't have more than 64 different values. Note that if an integer is put in the SET you get the corresponding string with the first bit corresponding to the first string. Sorting on SET types are done numerically.

    For example the column test SET("one","two") NOT NULL can have any of these values:

    ""
    "one"
    "two"
    "one,two"
    

    Normally on SELECT on a SET column with LIKE or FIND_IN_SET():

    SELECT * from banner where banner_group LIKE '%value%';
    SELECT * from banner where FIND_IN_SET('value',banner_group)>0;
    

    But the following will also work:

    SELECT * from banner where banner_group = 'v1,v2';  ;Exact match
    SELECT * from banner where banner_group & 1;        ;Is in first group
    

    7.2.2 Choosing the right type for a column

    Try to use the most precise type in all cases. For example for an integer between 1-99999 a unsigned mediumint is the best type.

    A common problem is representing monetary values accurately. In MySQL you should use the DECIMAL type. This is stored as a string so no loss of accuracy should occur. If accuracy is not to too important the DOUBLE type may also be good enough.

    For high precision you can always convert to a fixed point type stored in a BITINT. This allows you to do all calculations with integers and only convert the result back to floating point.

    See section 10.12 What are the different row formats? Or when to use VARCHAR/CHAR?.

    7.2.3 Column indexes

    You can have indexes on all MySQL columns except BLOB and TEXT types. Using indexes on the relevant columns is the best way to improve the performance of selects.

    For CHAR and VARCHAR columns you can have an index on a prefix. The example below shows how to create an index for the first 10 characters of a column. This is much faster and requires less disk space than having an index on the whole column.

    CREATE TABLE test (
    name CHAR(200) NOT NULL,
    KEY index_name (name(10));
    

    7.2.4 Multiple column indexes

    MySQL can have one index on parts of different columns.

    A multiple-column index can be considered a sorted array where the columns are concatenated. This makes for fast queries where the first column in the index is a known quantity and the other columns are not.

    Suppose that you have a table:

    CREATE TABLE test (
    id INT NOT NULL,
    last_name CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name));
    

    Then the index name is an index over last_name and first_name.

    The name index will be used in the following queries:

    SELECT * FROM test WHERE last_name="Widenius";
    
    SELECT * FROM test WHERE last_name="Widenius" AND first_name="Michael";
    
    SELECT * FROM test WHERE last_name="Widenius" AND
                             (first_name="Michael" OR first_name="Monty");
    
    SELECT * FROM test WHERE last_name="Widenius" and
                             first_name >="M" and first_name < "N";
    

    The name index will NOT be used in the following queries:

    SELECT * FROM test WHERE first_name="Michael";
    
    SELECT * FROM test WHERE last_name="Widenius" or first_name="Michael";
    

    7.2.5 Type mapping to ease moving table definitions between different databases engines

    To support easier use of code from different SQL vendors, MySQL does supports the following mappings:

    binary(num) char(num) binary
    char varying varchar
    float4 float
    float8 double
    int1 tinyint
    int2 smallint
    int3 mediumint
    int4 int
    int8 bigint
    long varbinary blob
    long varchar text
    middleint mediumint
    varbinary(num) varchar(num) binary

    7.3 Functions for use in SELECT and WHERE clauses

    A select_expression or where_definition can consist of any expression using the following functions:

    In the examples below the output of the mysql program has been shortened. So this:

    mysql> select mod(29,9);
    1 rows in set (0.00 sec)
    
    +-----------+
    | mod(29,9) |
    +-----------+
    |         2 |
    +-----------+
    

    Has been converted to:

    mysql> select mod(29,9);                ->      2
    

    7.3.1 Grouping functions

    (
    )
    Parenthesis. Force order of evaluation in a expression.
    mysql> select 1+2*3;                     ->      7
    mysql> select (1+2)*3;                   ->      9
    

    7.3.2 Normal arithmetic operations

    +
    Addition
    -
    Subtraction.
    *
    Multiplication
    /
    Division. A division by zero results in a NULL.
    mysql> select 102/(1-1);                 ->      NULL
    

    7.3.3 Bit functions

    These have a range of maximum 64 bits because MySQL uses bigint (64 bit) arithmetic.

    |
    Bitwise OR.
    mysql> select 29 | 15;                   ->     31
    
    &
    Bitwise and.
    mysql> select 29 & 15;                   ->      13
    
    <<
    Shift a longlong number to the right
    mysql > select 1 << 2			->	4
    
    <<
    Shift a longlong number to the left
    mysql > select 4 >> 2			->	1
    
    BIT_COUNT()
    Number of set bits in an argument.
    mysql> select bit_count(29);             ->      4
    

    7.3.4 Logical operations

    All logical function return 1 (TRUE) or 0 (FALSE).

    NOT
    !
    Logical NOT. Return 1 if argument is 0 else return 0.
    mysql> select NOT 1;                    ->      0
    mysql> select NOT NULL;                 ->      NULL
    mysql> select ! (1+1);                  ->      0
    mysql> select ! 1+1;                    ->      1
    
    OR
    ||
    Logical OR. Return 1 if any of the arguments are non 0 and not NULL.
    mysql> select 1 || 0;                   ->      1
    mysql> select 0 || 0;                   ->      0
    mysql> select 1 || NULL;                ->      1
    
    
    AND
    &&
    Logical AND. Return 1 if all of the arguments are non 0 or NULL
    mysql> select 1 && NULL;                ->      0
    mysql> select 1 && 0;                   ->      0
    

    7.3.5 Comparison operators

    Returns 1 (TRUE), 0 (FALSE) or NULL. These functions work for both numbers and strings. MySQL uses the following rules to decide how the compare is done:

    If one or both of the arguments are NULL the result of the comparison is NULL.

    =
    Equal.
    mysql> select 1 = 0;                    ->      0
    mysql> select '0' = 0;                  ->      1
    mysql> select '0.0' = 0;                ->      1
    mysql> select '0.01' = 0;               ->      0
    mysql> select '.01' = 0.01;             ->      1
    
    <>
    !=
    Not equal.
    mysql> select '.01' <> '0.01';          ->      1
    mysql> select .01 <> '0.01';            ->      0
    mysql> select 'zapp' <> 'zappp';        ->      1
    
    <=
    Smaller than or equal.
    mysql> select 0.1 <= 2;                 ->      1
    
    <
    Smaller than.
    mysql> select 2 <= 2;                   ->      1
    
    >=
    Bigger than or equal.
    mysql> select 2 >= 2;                   ->      1
    
    >
    Bigger than.
    mysql> select 2 > 2;                    ->      0
    
    ISNULL(A)
    Returns 1 if A is NULL else 0.
    mysql> select isnull(1+1);              ->      0
    mysql> select isnull(1/0);              ->      1
    
    A BETWEEN B AND C
    A is bigger or equal as B and A is smaller or equal to C. Does the same thing as (A >= B AND A <= C) if all arguments are of the same type. It's the first argument (A) that decides how the comparison should be done! If A is a string expression, compare as case insensitive strings. If A is a binary string, compare as binary strings. If A is an integer expression compare as integers, else compare as reals.
    mysql> select 1 between 2 and 3;        ->      0
    mysql> select 'b' between 'a' and 'c';  ->      1
    mysql> select 2 between 2 and '3';      ->      1
    mysql> select 2 between 2 and 'x-3';    ->      0
    

    7.3.6 String comparison functions

    Normally if one expression that is compared is not case sensitive than the compare is done case insensitively.
    expr IN (value,...)
    Returns 1 if expr is any of the values in the IN list, else it returns 0. If all values are constants, then all values are evaluated according to the type of expr and sorted. The search for the item is then done by using a binary search. This means IN is very quick when used with constants in the IN part. If expr is a string expression then string compare is done case sensitively if expr is case sensitive.
    mysql> select 2 in (0,3,5,'wefwf');               ->      0
    mysql> select 'wefwf' in (0,3,5,'wefwf');         ->      1
    
    expr NOT IN (value,...)
    Same as NOT (expr IN (value,...))
    expr LIKE expr
    SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). With LIKE you have two wild characters.
    % Matches any number of characters, even zero characters.
    _ Matches exactly one character.
    \% Matches one %.
    \_ Matches one _.
    mysql> select 'David!' like 'David_';             ->      1
    mysql> select 'David!' like 'David\_';            ->      0
    mysql> select 'David_' like 'David\_';            ->      1
    mysql> select 'David!' like '%D%v%';              ->      1
    mysql> select 10 like '1%';                       ->      1
    
    LIKE is allowed on numerical expressions! (Extension)
  • expr NOT LIKE expr Same as NOT (expr LIKE expr).
  • expr REGEXP text_literal
  • expr RLIKE text_literal Checks string against extended regular expr. See section H Description of MySQL regular expression syntax.. RLIKE is for mSQL compatibility. NOTE: Because MySQL uses the C escape syntax in strings (\n) You must double any '\' that you uses in your REGEXP strings.
    mysql> select 'Monty!' regexp 'm%y%%';            ->      0
    mysql> select 'Monty!' regexp '.*';               ->      1
    mysql> select 'new*\n*line' regexp 'new\\*.\\*line'
    
  • expr NOT REGEXP expr Same as NOT (expr REGEXP expr).
  • STRCMP() Returns 0 if the strings are the same. Otherwise return -1 if the first argument is smaller according to the current sort-order, otherwise return 1.
    mysql> select strcmp('text', 'text2');            -> -1
    mysql> select strcmp('text2', 'text');            -> 1
    mysql> select strcmp('text', 'text');             -> 0
    
  • 7.3.7 Control flow functions

    IFNULL(A,B)
    If A is not NULL it returns A, else B.
    mysql> select ifnull(1,0);        -> 1
    mysql> select ifnull(0,10);       -> 0
    mysql> select ifnull(1/0,10);     -> 10
    mysql> select ifnull(1/0,'yes');  -> 'yes'
    
    IF(A,B,C)
    If A is true (A <> 0 and A <> NULL) then return B, else return C. A is evaluated as an INTEGER, which means that if you are using floats you should also use a comparison operation.
    mysql> select if(1>2,2,3);        -> 3
    mysql> select if(1<2,'yes','no'); -> 'yes'
    

    7.3.8 Mathematical functions

    All mathematical functions returns NULL in the case of a error.

    -
    Sign. Changes sign of argument.
    mysql> select - 2;                -> -2
    
    ABS()
    Absolute value.
    mysql> select abs(2);             -> 2
    mysql> select abs(-32);           -> 32
    
    SIGN()
    Sign of argument. Returns -1, 0 or 1.
    mysql> select sign(-32);          -> -1
    mysql> select sign(0);            -> 0
    mysql> select sign(234);          -> 1
    
    MOD()
    %
    Modulo (like % in C).
    mysql> select mod(234, 10);       -> 4
    mysql> select 253 % 7;            -> 1
    mysql> select mod(29,9);                -> 2
    
    FLOOR()
    Largest integer value not greater than x.
    mysql> select floor(1.23);              -> 1
    mysql> select floor(-1.23);             -> -2
    
    CEILING()
    Smallest integer value not less than x.
    mysql> select ceiling(1.23);            -> 2
    mysql> select ceiling(-1.23);           -> -1
    
    ROUND(N)
    Round argument N to an integer.
    mysql> select round(-1.23);             -> -1
    mysql> select round(-1.58);             -> -2
    mysql> select round(1.58);              -> 2
    
    ROUND(Number,Decimals)
    Round argument Number to a number with Decimals decimals.
    mysql> select ROUND(1.298, 1);          -> 1.3
    
    EXP(N)
    Returns the value of e (the base of natural logarithms) raised to the power of N.
    mysql> select exp(2);                   -> 7.389056
    mysql> select exp(-2);                  -> 0.135335
    
    LOG(X)
    Return the natural logarithm of X.
    mysql> select log(2);             -> 0.693147
    mysql> select log(-2);            -> NULL
    
    LOG10(X)
    return the base-10 logarithm of X.
    mysql> select log10(2);           -> 0.301030
    mysql> select log10(100);                 -> 2.000000
    mysql> select log10(-100);                -> NULL
    
    POW(X,Y)
    POWER(X,Y)
    Return the value of X raised to the power of Y.
    mysql> select pow(2,2);           -> 4.000000
    mysql> select pow(2,-2);                  -> 0.250000
    
    sqrt(X)
    Returns the non-negative square root of X.
    mysql> select sqrt(4);            -> 2.000000
    mysql> select sqrt(20);           -> 4.472136
    
    PI()
    Return the value of PI.
    mysql> select PI();               -> 3.141593
    
    COS(X)
    Return the cosine of X, where X is given in radians.
    mysql> select cos(PI());                  -> -1.000000
    
    SIN(X)
    Return the sine of X, where X is given in radians.
    mysql> select sin(PI());                  -> 0.000000
    
    TAN(X)
    Returns the tangent of X, where X is given in radians.
    mysql> select tan(PI()+1);                -> 1.557408
    
    ACOS(X)
    Return the arc cosine of X; that is the value whose cosine is X. If X is not in the range -1 to 1 NULL is returned.
    mysql> select ACOS(1);                  -> 0.000000
    mysql> select ACOS(1.0001);             -> NULL
    mysql> select ACOS(0);          -> 1.570796
    
    ASIN(X)
    Return the arc sine of X; that is the value whose sine is X. If X is not in the range -1 to 1 NULL is returned.
    mysql> select ASIN(0.2);                -> 0.201358
    mysql> select ASIN('foo');              -> 0.000000
    
    ATAN(X)
    Return the arc tangent of X; that is the value whose tangent is X.
    mysql> select ATAN(2);          -> 1.107149
    mysql> select ATAN(-2);         -> -1.107149
    
    ATAN2(X,Y)
    Return the arc tangent of the two variables X and Y. It is similar to calculating the arc tangent of Y / X, except that the signs of both arguments are used to determine the quadrant of the result.
    mysql> select ATAN(-2,2);               -> -0.785398
    mysql> select ATAN(PI(),0);             -> 1.570796
    
    COT(N)
    Return the cotangens of N.
    mysql> select COT(12);          -> -1.57267341
    mysql> select COT(0);           -> NULL
    
    RAND([X])
    Returns a random float, 0 <= x <= 1.0, using the integer expression X as the optional seed value.
    mysql> SELECT RAND();           -> 0.5925
    mysql> SELECT RAND(20);         -> 0.1811
    mysql> SELECT RAND(20);         -> 0.1811
    mysql> SELECT RAND();           -> 0.2079
    mysql> SELECT RAND();           -> 0.7888
    
    One can't do a ORDER BY on a column with RAND() values because ORDER BY would evaluate the column multiple times.
    MIN(X,Y...)
    Min value of arguments. Must have 2 or more arguments, else these are GROUP BY functions. The arguments are compared as numbers. If no records are found NULL is returned.
    mysql> SELECT MIN(2,0);                         -> 0
    mysql> SELECT MIN(34,3,5,767);                  -> 3
    mysql> SELECT MIN(a) from table where 1=0;      -> NULL
    
    MAX(X,Y...)
    Max value of arguments. Must have 2 or more arguments, else these are GROUP BY functions. The arguments are compared as numbers. If no records are found NULL is returned.
    mysql> SELECT MAX(34,3,5,767);                  -> 767
    mysql> SELECT MAX(2,0,4,5,34);                  -> 34
    mysql> SELECT MAX(a) from table where 1=0;      -> NULL
    
    DEGREES(N)
    Return N converted from radians to degrees.
    mysql> select DEGREES(PI());                    -> 180.000000
    
    RADIANS(N)
    Return N converted from degrees to radians.
    mysql> select RADIANS(90);                      -> 1.570796
    
    TRUNCATE(Number, Decimals)
    Truncate number Number to Decimals decimals.
    mysql> select TRUNCATE(1.223,1);                -> 1.2
    mysql> select TRUNCATE(1.999,1);                -> 1.9
    mysql> select TRUNCATE(1.999,0);                -> 1
    

    7.3.9 String functions

    ASCII(S)
    Returns the ASCII code value of the leftmost character of S. If S is NULL return NULL.
    mysql> SELECT ascii(2);                         -> 50
    mysql> SELECT ascii('dx');                      -> 100
    
    CONV(NUMBER,FROM_BASE,TO_BASE)
    Converts numbers between different number bases. If any of the arguments is a NULL value, the return value is NULL. Max base is 36 and min is 2. CONV works with 64 bit precision. If TO_BASE is a negative number, the NUMBER is regarded as a signed number.
    mysql> SELECT conv("a",16,2);                   -> 1010
    mysql> SELECT conv("6E",18,8);                  -> 172
    mysql> SELECT conv(-17,10,-18);                 -> -H
    mysql> SELECT conv(10+"10"+'10'+0xa,10,10);     -> 40
    
    BIN(N)
    Returns the binary value of N where N is a longlong number. This is same as conv(N,10,2). If n is NULL the return value is NULL.
    mysql> SELECT bin(12);                          -> 1100
    
    OCT(N)
    Returns the octal value of N where N is a longlong number. This is same as conv(N,10,8). If n is NULL the return value is NULL.
    mysql> SELECT oct(12);                          -> 14
    
    HEX(N)
    Returns the hexadecimal value of N where N is a longlong number. This is same as conv(N,10,16). If n is NULL the return value is NULL.
    mysql> SELECT hex(255);                         -> FF
    
    CHAR(X,...)
    Returns a string that consists of the characters given by the ASCII code values of the arguments. NULLs are skipped.
    mysql> SELECT char(77,121,83,81,'76');          -> 'MySQL'
    
    CONCAT(X,Y...)
    Concatenates strings. May have more than 2 arguments.
    mysql> SELECT CONCAT('My', 'S', 'QL');          -> 'MySQL'
    
    LENGTH(S)
    OCTET_LENGTH(S)
    CHAR_LENGTH(S)
    CHARACTER_LENGTH(S)
    Length of string.
    mysql> SELECT length('text');                   -> 4
    mysql> SELECT octet_length('text');             -> 4
    
    LOCATE(A,B)
    POSITION(B IN A)
    Returns position of A substring in B. The first position is 1. Returns 0 if A is not in B.
    mysql> select locate('bar', 'foobarbar');         -> 4
    mysql> select locate('xbar', 'foobar');           -> 0
    
    INSTR(A,B)
    Returns position of first substring B in string A. This is the same as LOCATE with swapped parameters.
    mysql> select instr('foobarbar', 'bar');         -> 4
    mysql> select instr('xbar', 'foobar');           -> 0
    
    LPAD(A,B,C)
    Left pad (at the start) the string A with C until A's length is B.
    mysql> select lpad('hi',4,'??')			 -> '??hi'
    
    RPAD(A,B,C)
    Right pad (at the end) the string A with C until A's length is B.
    mysql> select rpad('hi',5,'?')			 -> 'hi???'
    
    LOCATE(A,B,C)
    Returns position of first substring A in string B starting at C.
    mysql> select locate('bar', 'foobarbar',5);        -> 7
    
    LEFT(str,length)
    Gets length in characters from beginning of string.
    mysql> select left('foobarbar', 5);             -> 'fooba'
    
    RIGHT(A,B)
    SUBSTRING(A FROM B)
    Gets B characters from end of string A.
    mysql> select right('foobarbar', 5);            -> 'arbar'
    mysql> select substring('foobarbar' from 5);    -> 'arbar'
    
    LTRIM(str)
    Removes space characters from the beginning of string.
    mysql> select ltrim('  barbar');                -> 'barbar'
    
    RTRIM(str)
    Removes space characters from the end of string.
    mysql> select rtrim('barbar   ');             -> 'barbar'
    
    TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
    Returns a character string with all A prefixes and/or suffixes removed from B. If BOTH, LEADING and TRAILING isn't used BOTH are assumed. If A is not given, then spaces are removed.
    mysql> select trim('  bar   ');                      -> 'bar'
    mysql> select trim(leading 'x' from 'xxxbarxxx');    -> 'barxxx'
    mysql> select trim(both 'x' from 'xxxbarxxx');       -> 'bar'
    mysql> select trim(trailing 'xyz' from 'barxxyz');   -> 'barx'
    
    SOUNDEX(S)
    Gets a soundex string from S. Two strings that sound 'about the same' should have identical soundex strings. A 'standard' soundex string is 4 characters long, but this function returns an arbitrary long string. One can use SUBSTRING on the result to get a 'standard' soundex string. All non alpha characters are ignored in the given string. All characters outside the A-Z range are treated as vocals.
    mysql> select soundex('Hello');                 -> 'H400'
    mysql> select soundex('Bättre');                -> 'B360'
    mysql> select soundex('Quadratically');         -> 'Q36324'
    
    SUBSTRING(A, B, C)
    SUBSTRING(A FROM B FOR C)
    MID(A, B, C)
    Returns substring from A starting at B with C chars. The variant with FROM is ANSI SQL 92 syntax.
    mysql> select substring('Quadratically',5,6);          -> ratica
    
    SUBSTRING(A,B)
    Returns substring from A starting at B.
    mysql> select substring('Quadratically',5);            -> ratically
    
    SUBSTRING_INDEX(String, Delimiter, Count)
    Returns the substring from String after Count Delimiters. If Count is positive the strings are searched from left else if count is negative the substrings are searched and returned from right.
    mysql> select substring_index('www.tcx.se', '.', 2);   -> 'www.tcx'
    mysql> select substring_index('www.tcx.se', '.', -2);  -> 'tcx.se'
    
    SPACE(N)
    Return a string of N spaces.
    mysql> select SPACE(6);         -> '      '
    
    REPLACE(A, B, C)
    Replaces all occurrences of string B in string A with string C.
    mysql> select replace('www.tcx.se', 'w', 'Ww');  -> 'WwWwWw.tcx.se'
    
    REPEAT(String, Count)
    Repeats String Count times. If Count <= 0 returns a empty string. If String or Count is NULL or LENGTH(string)*count > max_allowed_size returns NULL.
    mysql> select repeat('MySQL', 3);                -> 'MySQLMySQLMySQL'
    
    REVERSE(String)
    Reverses all characters in string.
    mysql> select reverse('abc');  -> 'cba'
    
    INSERT(Org, Start, Length, New)
    Replaces substring in Org starging at Start and Length long with New. First position in Org is numbered 1.
    mysql> select insert('Quadratic', 3, 4, 'What');   -> 'QuWhattic'
    
    INTERVAL(N, N1, N2, N3...)
    It is required that Nn > N3 > N2 > N1 is this function shall work. This is because a binary search is used (Very fast). Returns 0 if N < N1, 1 if N < N2 and so on. All arguments are treated as numbers.
    mysql> select INTERVAL(23, 1, 15, 17, 30, 44, 200);     -> 3
    mysql> select INTERVAL(10, 1, 10, 100, 1000);           -> 2
    mysql> select INTERVAL(22, 23, 30, 44, 200);            -> 0
    
    ELT(N, A1, A2, A3...)
    Returns A1 if N = 1, A2 if N = 2 and so on. If N is less than 1 or bigger than the number of arguments NULL is returned.
    mysql> select elt(1, 'ej', 'Heja', 'hej', 'foo');  -> 'ej'
    mysql> select elt(4, 'ej', 'Heja', 'hej', 'foo');  -> 'foo'
    
    FIELD(S, S1, S2, S3...)
    Returns index of S in S1, S2, S3... list. The complement of ELT(). Return 0 when S is not found.
    mysql> select FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');       -> 2
    mysql> select FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');       -> 0
    
    FIND_IN_SET(string,string of strings)
    Returns a value 1 to N if the 'string' is in 'string of strings'. A 'string of strings' is a string where each different value is separated with a ','. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET is optimized to use bit arithmetic!
    mysql> SELECT FIND_IN_SET('b','a,b,c,d')		-> 2
    
    This function will not work properly if the first argument contains a ','.
    LCASE(A)
    LOWER(A)
    Changes A to lower case according to current character set ,dmappings (Default Latin1).
    mysql> select lcase('QUADRATICALLY');           -> 'quadratically'
    
    UCASE(A)
    UPPER(A)
    Changes A to upper case.
    mysql> select ucase('Hej');             -> 'HEJ'
    

    7.3.10 Date and time functions

    Some examples using more than one date function:

    Select all record with a date_field from the last 30 days.

    SELECT something FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(date_field) <= 30;
    

    A Date expression may be a date string, a datetime string, a timestamp([6 | 8 | 14]) or a number of format YYMMDD or YYYYMMDD.

    In a date expression a year may be 2 or 4 digits. 2 digits is assumed to be in the range 1970-2069. Dates 100-199 is converted to 2000-2999 to make year arithmetic easier! The special date '0000-00-00' can be stored and retrieved as 0000-00-00.

    If you use a date function with a number, then if the length of the number is 4, 8 or >= 14 then the year is assumed to have 4 digits. In all other cases the year is assumed to be the 2 first digits in the given number. To be on the safe side when using dates as numbers (not strings) one should always use 4 digit dates! If not you will get in trouble with year 2000 when a number 002001 is sent to the date functions as '2001' instead of the date '20002001'. '002001' will of course work correctly!

    A Time expression may be a date string, a datetime string, a timestamp([6 | 8 | 14]) or a number of format HHMMSS or YYYYMMDDHHMMSS.

    DAYOFWEEK(date expr)
    Gets weekday for Date (1 = Sunday, 2 = Monday, 2 = Tuesday ..) This is according to the ODBC standard.
    mysql> select dayofweek('1998-02-03');		-> 3
    
    WEEKDAY(date expr)
    Gets weekday for Date (0 = Monday, 1 = Tuesday ..)
    mysql> select WEEKDAY('1997-10-04 22:23:00');	-> 5
    mysql> select WEEKDAY('1997-11-05');            -> 2
    
    DAYOFMONTH(date expr)
    Returns day of month (1-31)
    mysql> select DAYOFMONTH('1998-02-03');		-> 3
    
    DAYOFYEAR(date expr)
    Returns day of year (1-366)
    mysql> select DAYOFYEAR('1998-02-03');		-> 34
    
    MONTH(date expr)
    Returns month (1-12)
    mysql> select MONTH('1998-02-03');		-> 02
    
    DAYNAME(date expr)
    Returns the name of the day.
    mysql> select dayname("1998-02-05");            -> Thursday
    
    MONTHNAME(date expr)
    Returns the name of the month.
    mysql> select monthname("1998-02-05");          -> February
    
    QUARTER(date expr)
    Returns quarter (1-4).
    mysql> select QUARTER('98-04-01');		-> 2
    
    WEEK(date expr)
    Returns week (1-53) on locations where Sunday is the first day of the year
    mysql> select WEEK('98-02-20');			-> 7
    
    YEAR(date expr)
    Returns year (1000-9999).
    mysql> select YEAR('98-02-03');			-> 1998
    
    HOUR(time expr)
    Returns hour (0-23)
    mysql> select HOUR('10:05:03');			-> 10
    
    MINUTE(time expr)
    Returns minute (0-59).
    mysql> select MINUTE('98-02-03 10:05:03');	-> 5
    
    SECOND(time expr)
    Returns seconds (1000-9999).
    mysql> select SECOND('10:05:03');		-> 3
    
    PERIOD_ADD(P, N)
    Adds N months to period P (of type YYMM or YYYYMM). Returns YYYYMM.
    mysql> select PERIOD_ADD(9801,2);               -> 199803
    
    PERIOD_DIFF(A, B)
    Returns months between periods A and B. A and B should be of format YYMM or YYYYMM.
    mysql> select PERIOD_DIFF(9802,199703);         -> 11
    
    DATE_ADD(date, INTERVAL expr TYPE), DATE_SUB(date, INTERVAL expr TYPE)
    Adds or subtracts an intervall expression from a datetime or date column. TYPE should be one of the following keywords This is new for MySQL 3.22.
    SECOND Seconds.
    MINUTE Minutes
    HOUR Hours
    DAY Days
    MONTH Months
    YEAR Years
    MINUTE_SECOND Minutes and seconds in format "MINUTES:SECONDS"
    HOUR_MINUTE Hours and minutes in format "HOURS:MINUTES"
    DAY_HOUR Days and hours in format "DAYS HOURS"
    YEAR_MONTH Years and months in format "YEARS-MONTH"
    HOUR_SECOND Hours, minutes and seconds in format "HOUR:MINUTES:SECONDS"
    DAY_MINUTE Days, Hours and minutes in format "DAYS HOUR:MINUTES"
    DAY_SECOND Days, Hours, minutes and seconds in format "DAYS HOUR:MINUTES:SECONDS"
    MySQL allows any separator in the format, the above are the suggested formats. The expr is a string that may start with a - for negative intervals. If the date is a date column and one only uses calculation with YEAR, MONTH and DAY then the result is a DATE, else it's a DATETIME. If one uses wrong dates or interval of wrong type the result is NULL.
    date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND) 1998-01-01 00:00:00
    date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND) 1998-01-01 00:01:00
    date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND) 1997-12-30 22:58:59
    select date_add("1997-12-31 23:59:59",INTERVAL 1 DAY) 1998-01-01 23:59:59
    date_add("1998-01-01 00:00:00",INTERVAL "-1 10" DAY_HOUR) 1997-12-30 14:00:00
    date_sub("1998-01-02",INTERVAL 31 DAY) 1997-12-02
  • TO_DAYS(Date) Changes a Date to a daynumber (Number of days since year 0). Date may be a DATE string, a DATETIME string, a TIMESTAMP([6 | 8 | 14]) or a number of format YYMMDD or YYYYMMDD.
    mysql> select TO_DAYS(9505);                    -> 733364
    mysql> select TO_DAYS('1997-10-07);             -> 729669
    
  • FROM_DAYS() Changes a daynumber to a DATE.
    mysql> select from_days(729669);                -> 1997-10-07       
    
  • DATE_FORMAT(Date, Format) Formats the Date (a date or a timestamp) according to the Format string. The following format commands are known:
    M Month name
    W Weekday name
    D Day of the month with english suffix
    Y Year with 4 digits
    y Year with 2 digits
    a Abbreviated weekday name (Sun..Sat)
    d Day of the month, numeric
    m Month, numeric
    b Abbreviated month name (Jan.Dec)
    j Day of year (001..366)
    H Hour (00..23)
    k Hour ( 0..23)
    h Hour (01..12)
    I Hour (01..12)
    l Hour ( 1..12)
    i Minutes, numeric
    r Time, 12-hour (hh:mm:ss [AP]M)
    T Time, 24-hour (hh:mm:ss)
    S Seconds (00..59)
    s Seconds (00..59)
    p AM or PM
    w Day of the week (0=Sunday..)
    % single % are ignored. Use %% for a % (for future extensions).
    All other characters are copied to the result.
    mysql> select date_format('1997-10-04 22:23:00', '%W %M %Y %h:%i:%s');
            -> 'Saturday October 1997 22:23:00'
    mysql> select date_format('1997-10-04 22:23:00', '%D %y %a %d %m %b %j %H %k %I %r %T %S %w');
            -> '4th 97 Sat 04 10 Oct 277 22 22 10 10:23:00 PM 22:23:00 00 6'
    
    For the moment % is optional. In future versions of MySQL % will be required.
  • TIME_FORMAT(time expr, format) This can be used like the DATE_FORMAT above, but only with the format options which handle hours, minutes and seconds. Other options give NULL value or 0.
  • CURDATE()
  • CURRENT_DATE Returns today's date. In form YYYYMMDD or 'YYYY-MM-DD' depending on whether CURDATE() is used in a number or string context.
    mysql> select CURDATE();                -> '1997-12-15'
    mysql> select CURDATE()+0;              -> 19971215
    
  • CURTIME()
  • CURRENT_TIME Returns the current time in the form HHMMSS or 'HH:MM:SS', depending on whether CURTIME() is used in a number or string context.
    mysql> select CURTIME();                -> '23:50:20'
    mysql> select CURTIME()+0;              -> 235026
    
  • NOW()
  • SYSDATE()
  • CURRENT_TIMESTAMP Returns the current time. In format YYYYMMDDHHMMSS or 'YYYY-MM-DD HH:MM:SS' depending on whether NOW() is used in a number or string context.
    mysql> select NOW();            -> '1997-12-15 23:51:26'
    mysql> select NOW()+0;          -> 19971215235131
    
  • UNIX_TIMESTAMP([date expression]) If called without any arguments, a unix timestamp (seconds in GMT since 1970.01.01 00:00:00). Normally it is called with a TIMESTAMP column as an argument in which case it returns the columns value in seconds. Date may also be a date string, a datetime string, or a number of format YYMMDD or YYYMMDD in local time.
    mysql> select UNIX_TIMESTAMP();                         -> 882226357
    mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');    -> 875996580
    
  • FROM_UNIXTIME(Unix_timestamp) Returns a string of the timestamp in YYYY-MM-DD HH:MM:SS or YYYYMMDDHHMMSS format depending on context (numeric/string).
    mysql> select FROM_UNIXTIME(875996580);   -> '1997-10-04 22:23:00'
    
  • FROM_UNIXTIME(Unix_timestamp, Format_string) Returns a string of the timestamp formated according to the Format_string. The format string may contain:
    M Month, textual
    W Day (of the week), textual
    D Day (of the month), numeric plus english suffix
    Y Year, numeric, 4 digits
    y Year, numeric, 2 digits
    m Month, numeric
    d Day (of the month), numeric
    h Hour, numeric
    i Minutes, numeric
    s Seconds, numeric
    w Day (of the week), numeric
    All other All other characters are just copied.
    mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(), 'Y D M h:m:s x');
            -> '1997 23rd December 03:12:30 x'
    
  • SEC_TO_TIME(Seconds) Returns the hours, minutes and seconds of the argument in H:MM:SS or HMMSS format depending on context.
    mysql> select SEC_TO_TIME(2378);                -> '00:39:38'
    mysql> select SEC_TO_TIME(2378)+0;              -> 3938
    
  • TIME_TO_SEC(Time) Converts Time to seconds.
    mysql> select TIME_TO_SEC('22:23:00');  -> 80580
    mysql> select TIME_TO_SEC('00:39:38');  -> 2378
    
  • 7.3.11 Miscellaneous functions

    DATABASE()
    Returns current database name.
    mysql> select DATABASE();               -> 'test'
    
    USER()
    SYSTEM_USER()
    SESSION_USER()
    Returns current user name.
    mysql> select USER();                   -> 'davida'
    
    PASSWORD(String)
    Calculates a password string from plaintext password String. This must be used to store a password in the 'user' grant table.
    mysql> select PASSWORD('badpwd');       -> '7f84554057dd964b'
    
    ENCRYPT(String[, Salt])
    Crypt String with the unix crypt() command. The Salt should be a string with 2 characters. If crypt() was not found NULL will always be returned.
    
    
    
    
    LAST_INSERT_ID()
    Returns the last automatically generated value that was set in an auto_increment column. See section 17.1.3 How can I get the unique ID for the last inserted row?.
    mysql> select LAST_INSERT_ID();         -> 1
    
    FORMAT(Nr, Num)
    Formats number Nr to a Format like '#,###,###.##' with Num decimals.
    mysql> select FORMAT(12332.33, 2);      -> '12,332.33'
    
    VERSION
    Return the version of the MySQL server.
    mysql> select version();                -> '3.21.16-beta-log'
    
    GET_LOCK(String,timeout)
    Tries to get a lock on named 'String' with a timeout of 'timeout' seconds. Returns 1 if one got the lock, 0 on timeout and NULL on error (like out of memory or if thread was killed with mysqladmin kill. A lock is released if one executes RELEASE_LOCK, executes a new GET_LOCK or if the thread ends. This function can be used to implement application locks or simulate record locks.
    mysql> select get_lock("automatically released",10);   -> 1
    mysql> select get_lock("test",10);		       -> 1
    mysql> select release_lock("test");                    -> 1
    mysql> select release_lock("automatically released")   -> NULL
    
    RELEASE_LOCK(String)
    Releases a lock this thread has got with GET_LOCK. Returns 1 if the lock was released, 0 if lock wasn't locked by this thread and NULL if the lock 'String' didn't exist.

    7.3.12 Functions for GROUP BY clause

    COUNT(Expr)
    Count number of non NULL rows. count(*) is optimised to return very quickly if no other column is used in the SELECT.
    select count(*) from student;
    select count(if(length(name)>3,1,NULL)) from student;
    
    AVG(expr)
    Average value of expr.
    MIN(expr)
    MAX(expr)
    Minimum/Maximum value of expr. min() and max() may take a string argument and will then return the minimum/maximum string value.
    SUM(expr)
    Sum of expr.
    STD(expr)
    STDDEV(expr) (Oracle format)
    Standard derivative of expression. This is a extension to ANSI SQL.
    BIT_OR(expr)
    The bitwise OR of all bits in expr. Calculation done with 64 bit precision.
    BIT_AND(expr)
    The bitwise AND of all bits in expr. Calculation done with 64 bit precision.

    MySQL has extended the use of GROUP BY. You can use columns or calculations in the SELECT expressions which don't appear in the GROUP BY part. This stands for any possible value for this group. By using this, one can get a higher performance by avoiding sorting and grouping on unnecessary items. For example, in the following query one doesn't need to group on b.name:

    SELECT a.id,b.name,COUNT(*) from a,b WHERE a.id=b.id GROUP BY a.id
    

    In ANSI SQL you would have to add the customer.name in the GROUP BY for the following query. In MySQL the name redundant.

    SELECT order.custid,customer.name,max(payments) from order,customer
    WHERE order.custid = customer.custid GROUP BY order.custid;
    

    Don't use this feature if the columns you leave out from the GROUP BY part isn't unique in the group!

    In some specific cases you can use MIN() and MAX() to get a specific column even it isn't unique. The following gives the value from the row with the smallest 'sort' value.

    SUBSTR(MIN(concat(sort,space(6-length(sort)),column),7,length(column)))
    

    Note that you can't yet use expressions in the GROUP BY or ORDER BY clause. You can on the other hand use an alias on an expression and use this to solve the problem:

    SELECT id,FLOOR(value/100) AS val FROM table_name GROUP BY id,val ORDER BY val
    

    7.4 Create database syntax

    CREATE DATABASE database_name
    

    Creates a database with the given name. The name can only contain letters, numbers or the '_' character and must start with a letter or a _. The maximum length of a database name is 64 characters. All databases in MySQL are directories, so a CREATE DATABASE only creates a directory in the MySQL database directory. You can also create databases with mysqladmin. See section 12.1 Overview of the different MySQL programs

    7.5 Drop database syntax

    DROP DATABASE [IF EXISTS] database_name
    

    Drop all tables in the database and deleted the database. You have to be VERY carefull with this command! DROP DATABASE returns how many files was removed from the directory. Normally this is number of tables*3. You can also drop databases with mysqladmin. See section 12.1 Overview of the different MySQL programs

    In MySQL 3.22 one can use the new keywords IF EXISTS to not get an error for a database that doesn't exits.

    7.6 CREATE TABLE syntax

    CREATE TABLE table_name ( create_definition,... )
    
    create_definition:
      column_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
                       [ PRIMARY KEY ] [reference_definition]
      or	PRIMARY KEY ( index_column_name,... )
      or	KEY [index_name] KEY( index_column_name,...)
      or	INDEX [index_name] ( index_column_name,...)
      or	UNIQUE [index_name] ( index_column_name,...)
      or    FOREIGN KEY index_name ( index_column_name,...) [reference_definition]
      or	CHECK (expr)
    
    type:
            TINYINT[(length)] [UNSIGNED] [ZEROFILL]
      or    SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
      or    MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
      or    INT[(length)] [UNSIGNED] [ZEROFILL]
      or    INTEGER[(length)] [UNSIGNED] [ZEROFILL]
      or    BIGINT[(length)] [UNSIGNED] [ZEROFILL]
      or    REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
      or    DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
      or	FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
      or	DECIMAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
      or	NUMERIC[(length,decimals)] [UNSIGNED] [ZEROFILL]
      or    CHAR(length) [BINARY],
      or    VARCHAR(length) [BINARY],
      or    DATE
      or    TIME
      or    TIMESTAMP
      or    DATETIME
      or    TINYBLOB
      or    BLOB
      or    MEDIUMBLOB
      or    LONGBLOB
      or    TINYTEXT
      or    TEXT
      or    MEDIUMTEXT
      or    ENUM(value1,value2,value3...)
      or    SET(value1,value2,value3...)
    
    index_column_name:
            column_name [ (length) ]
    
    reference_definition:
            REFERENCES table_name [( index_column_name,...)]
                       [ MATCH FULL | MATCH PARTIAL]
                       [ ON DELETE reference_option]
                       [ ON UPDATE reference_option]
    
    reference_option:
            RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
    

    See section 7.2 Column types

    The FOREIGN KEY, CHECK and REFERENCE syntax are only for compatibility. (To make it easier to port code from other SQL servers and run applications that create tables with references). They don't actually do anything. See section 5.2 What functionality is missing in MySQL?

    If a column doesn't have a DEFAULT value and is not declared as NOT NULL, the default value is NULL.

    If a column doesn't have a DEFAULT value and is declared as NOT NULL, MySQL will automatically assign a default value for the field.

    7.7 ALTER TABLE syntax

    ALTER [IGNORE] TABLE table_name alter_spec [, alter_spec ...]
    
    alter_specification:
    	ADD [COLUMN] create_definition [AFTER column_namn | FIRST]
    or	CHANGE [COLUMN] old_column_name create_definition
    or	ALTER [COLUMN] column_name { SET DEFAULT literal | DROP DEFAULT }
    or	ADD INDEX [index_name] ( index_column_name,...)
    or	ADD UNIQUE [index_name] ( index_column_name,...)
    or	DROP [COLUMN] column_name
    or	DROP PRIMARY KEY
    or	DROP INDEX key_name
    or	RENAME [AS] new_table_name
    

    Some examples of using ALTER TABLE:

    CREATE TABLE t1 (a INTEGER,b CHAR(10));
    INSERT INTO t1 VALUES(1,"testing");
    ALTER TABLE t1 RENAME t2;
    ALTER TABLE t2 CHANGE a a TINYINT NOT NULL, CHANGE b c CHAR(20);
    ALTER TABLE t2 ADD d TIMESTAMP;
    ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
    ALTER TABLE t2 DROP COLUMN c;
    ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);
    DROP TABLE t2;
    

    7.8 DROP TABLE syntax

    DROP TABLE [IF EXISTS] table_name [, table_name...]

    Removes one or more tables. All the data and the definition are removed so take it easy with this command!

    In MySQL 3.22 one can use the new keywords IF EXISTS to not get an error for tables that doesn't exits.

    7.9 DELETE syntax

    DELETE FROM table_name WHERE where_definition

    Returns records affected.

    If one does a delete without a WHERE clause then the table is recreated, which is much faster than doing a delete for each row. In these cases, the command returns zero as affected records. MySQL can't return the number of deleted row because the recreate is done without opening the data files to make sure that one can recreate the table as long as the table definition file table_name.frm is valid.

    7.10 SELECT syntax

    SELECT [STRAIGHT_JOIN] [DISTINCT | ALL] select_expression,... [INTO OUTFILE 'file_name' ...] [ FROM table_references [WHERE where_definition ] [GROUP BY column,...] [HAVING where_definition] [ ORDER BY column [ASC | DESC] ,..] [LIMIT [offset,] rows] [PROCEDURE procedure_name]]

    All used keywords must come in exactly the above order. For example a HAVING clause must come after any GROUP BY and before any ORDER BY clause.

    Strings are automatically converted to numbers and numbers to strings when needed (a-la Perl). If in a compare operation ((=, <>, <= ,<, >=, >)) either of the arguments are numerical, the arguments are compared as numbers, else the arguments are compared as strings. All string comparisons are by default done case-independent by ISO8859-1 (The Scandinavian letter set which also works excellently with English).

    select 1 > '6x';        ->      0
    
    select 7 > '6x';        ->      1
    
    select 0 > 'x6';        ->      0 
    
    select 0 = 'x6';        ->      1
    

    7.11 Join syntax

    MySQL supports the following JOIN syntaxes:

    table_reference, table_reference
    table_reference [CROSS] JOIN table_reference
    table_reference STRAIGHT_JOIN table_reference
    table_reference LEFT [OUTER] JOIN table_reference ON conditional-expr
    table_reference LEFT [OUTER] JOIN table_reference USING (column-commalist)
    table_reference NATURAL LEFT [OUTER] JOIN table_reference
    { oj table_reference LEFT OUTER JOIN table_reference ON conditional-expr }
    

    The last version is ODBC syntax.

    Some examples:

    SELECT * from table1,table2 where table1.id=table2.id;
    SELECT * from table1 LEFT JOIN table2 ON table1.id=table2.id;
    SELECT * from table1 LEFT JOIN table2 USING (id);
    SELECT table1.* from table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;
    SELECT * from table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table3.id=table2.id;
    

    The fourth example is noteworthy as it finds all rows in table1 that doesn't have an row in table2.

    7.12 INSERT syntax

    	INSERT INTO table [ (column_name,...) ] VALUES (expression,...)
    or	INSERT INTO table [ (column_name,...) ] SELECT ...
    

    An expression may use any previous column in column_name list (or table if no column name list is given).

    The following holds for a multi-row INSERT statement:

    7.13 REPLACE syntax

    	REPLACE INTO table [ (column_name,...) ] VALUES (expression,...)
    or	REPLACE INTO table [ (column_name,...) ] SELECT ...
    

    This works exactly like INSERT, except that if there was some old record in the table with the same unique index the old record or records will be deleted before this record is inserted. See section 7.12 INSERT syntax.

    7.14 LOAD DATA INFILE syntax

    LOAD DATA INFILE 'text_file_name.text' [REPLACE | IGNORE] INTO TABLE table_name [FIELDS [TERMINATED BY ',' [OPTIONALLY] ENCLOSED BY '"' ESCAPED BY '\\' ]] [LINES TERMINATED BY '\n'] [(Field1, Field2...)]

    This is used to read rows from a text file, which must be located on the server, at a very high speed. The server-client protocol doesn't yet support files over a connection. If you only have the file on the client, use rcp or ftp to copy it, possibly compressed, to the server before using LOAD DATA INFILE. All paths to the text file are relative to the database directory.

    To write data to a text file, use the SELECT ... INTO OUTFILE 'interval.txt' fields terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\n' FROM ... syntax.

    Normally you don't have to specify any of the text file type options. The default is a compact text file with columns separated with tab characters and all rows end with a newline. Tabs, newlines and \ inside fields are prefixed with a \. NULLs are read and written as \N.

    FIELDS TERMINATED BY has the default value of \t.

    FIELDS [OPTIONALLY] ENCLOSED BY has the default value of ".

    FIELDS ESCAPED BY has the default value of '\\'.

    LINES TERMINATED BY has the default value of '\n'.

    FIELDS TERMINATED BY and LINES TERMINATED BY may be more than one character.

    If LINES TERMINATED BY is an empty string and FIELDS TERMINATED BY is non-empty then lines are also terminated with FIELDS TERMINATED BY.

    If FIELDS TERMINATED BY and FIELDS ENCLOSED BY both are empty strings (") then this gives a fixed row format ("not delimited" import format). With a fixed row size NULL values are output as a blank string. If you specify OPTIONALLY in ENCLOSED BY, then only strings are enclosed in ENCLOSED BY by the SELECT ... INTO statement.

    Duplicated ENCLOSED BY chars are removed from strings that start with ENCLOSED BY. For example: With ENCLOSED BY '"':

    "The ""BIG"" boss"  -> The "BIG" boss
    The "BIG" boss	    -> The "BIG" boss
    

    If ESCAPED BY is not empty then the following characters will be prefixed with the escape character: ESCAPED BY, ASCII 0, and the first character in any of FIELDS TERMINATED BY, FIELDS ENCLOSED BY and LINES TERMINATED BY.

    If FIELDS ENCLOSED BY is not empty then NULL is read as a NULL value. If FIELDS ESCAPED BY is not empty then \N is also read as a NULL value.

    If REPLACE is used, then the new row will replace all rows which have the same unique index. If IGNORE is used, the row will then be skipped if a record already exists with an identical unique key. If none of the above options are used an error will be issued. The rest of the text file will be ignored if one gets a duplicate index error.

    Some possible cases that are not supported by LOAD DATA:

    All rows are read into the table. If a row has too few columns, the rest of the columns are set to default values. TIMESTAMP columns are only set to the current time if there is a NULL value for the column or if the TIMESTAMP column is left out from the field list when the field list is used (the last case only holds for the first TIMESTAMP column).

    For security reasons the text file must either reside in the database directory or be readable by all. Each user that wants to use LOAD DATA INFILE must also have 'Y' in the 'File_priv' column in the user privilege table! See section 6.2 How does the privilege system work?

    Because LOAD DATA INFILE regards all input as strings you can't use number values for enum or set columns as you can with INSERT statements. All enum and set must be given as strings!

    For more information about the escaped syntax, See section 7.1 Literals. How do you write strings and numbers?.

    When the LOAD DATA query is done, one can get the following info string with the C API function mysql_info().

    Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

    Warnings are incremented for each column which can't be stored without loss of precision, for each column which didn't get a value from the read text line (happens if the line is too short) and for each line which has more data than can fit into the given columns. A warning is also given for any time, date, timestamp or datetime column that is set to 0.

    An example that loads all columns:

    LOAD DATA INFILE 'persondata.text' INTO TABLE persondata;
    

    See section 10.8 How should I arrange my table to be as fast/small as possible?

    7.15 UPDATE syntax

    UPDATE table SET column=expression,... WHERE where_definition
    

    All updates are done from left to right. If one accesses a column in the expression, update will then use the current value (a given value or the default value) of the column.

    UPDATE persondata SET count=count+1
    

    A UPDATE statements returns how many rows was actually changed. In MySQL 3.22 mysql_info() returns the number of rows that was matched and updated and how warnings one got during the update.

    7.16 SHOW syntax. Get information about tables, columns...

    	SHOW DATABASES [LIKE wild]
    or 	SHOW TABLES [FROM database] [LIKE wild]
    or	SHOW COLUMNS FROM table [FROM database] [LIKE wild]
    or	SHOW INDEX FROM table [FROM database]
    or	SHOW STATUS
    or	SHOW VARIABLES [LIKE wild]
    

    Gives information about databases, tables or columns. If the LIKE wild part is used the wild string is a normal SQL wildcard (with % and _). FIELDS may be used as an alias for COLUMNS and KEYS may be used as an alias for INDEXES.

    STATUS gives status information from the server like mysqladmin status). The output may differ from the following:

    Uptime Running_threads Questions Reloads Open_tables
    119 1 4 1 3
    VARIABLES shows the values of the some of MySQL system variables. Most of these variables can be changed by different options to mysqld!

    7.17 EXPLAIN syntax. Get information about a SELECT

    	EXPLAIN SELECT select_options
    

    Gives information about how and in which order tables are joined. With the help of EXPLAIN one can see when one has to add more indexes to tables to get a faster select that uses indexes to find the records. You can also see if the optimiser joins the tables in an optimal order. One can force the optimiser to use a specific join order with the STRAIGHT_JOIN option to select.

    The different join types are:

    system
    The table has only one record (= system table)
    const
    The table has at most one matching record which will be read at the start of the query. All columns in this table will be regarded as constants by the rest of the optimiser.
    eq_ref
    One record will be read from this table for each combination of the previous tables.
    ref
    All rows with matching indexes will be read from this table for each combination of the previous tables.
    range
    Only rows that is in a given index range will be retrieved trough an index. The extra column will tell which index is used.
    all
    A full table scan will be done for each combination of the previous tables.

    Here is a example of a join which is optimised with the help of EXPLAIN.

    EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
            tt.ProjectReference, tt.EstimatedShipDate,
            tt.ActualShipDate, tt.ClientID,
            tt.ServiceCodes, tt.RepetitiveID,
            tt.CurrentProcess, tt.CurrentDPPerson,
            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
            et_1.COUNTRY, do.CUSTNAME
            FROM tt, et, et AS et_1,
            do
            WHERE tt.SubmitTime Is Null and tt.ActualPC =
            et.EMPLOYID and tt.AssignedPC =
            et_1.EMPLOYID and tt.ClientID =
            do.CUSTNMBR;
    

    The EXPLAIN returns the following:

    table   type    possible_keys   key     key_len ref     rows    Extra
    et      ALL     PRIMARY NULL    NULL    NULL    74
    do      ALL     PRIMARY NULL    NULL    NULL    2135
    et_1    ALL     PRIMARY NULL    NULL    NULL    74
    tt      ALL     AssignedPC,ClientID,ActualPC    NULL    NULL    NULL   3872
            range checked for each record (key map: 35)
    

    In this case MySQL is doing a full join for all tables! This will take quite a long time as the product of the number of rows in each table must be examined! So if all tables had 1000 records MySQL has to look at 1000^4 = 1000000000000 rows. If the tables are bigger you can only imagine how long it would take...

    In this case the first error is that MySQL can't yet use efficiently indexes on columns that are declared differently: (varchar() and char() are not different in this context)

    In this case tt.ActualPC is char(10) and et.EMPLOYID is char(15).

    Fix:

    mysql> alter table tt change ActualPC ActualPC varchar(15);
    

    And the above explanation shows:

    table   type    possible_keys   key     key_len ref     rows    Extra
    tt      ALL     AssignedPC,ClientID,ActualPC    NULL    NULL    NULL   3872
            where used
    do      ALL     PRIMARY NULL    NULL    NULL    2135
            range checked for each record (key map: 1)
    et_1    ALL     PRIMARY NULL    NULL    NULL    74
            range checked for each record (key map: 1)
    et     eq_ref  PRIMARY PRIMARY 15      tt.ActualPC    1
    

    Which is not perfect but much better. This version is executed in a couple of seconds.

    After

    mysql> alter table tt change AssignedPC AssignedPC varchar(15),
                          change ClientID Clientid     varchar(15);
    

    You get the following from EXPLAIN:

    table   type    possible_keys   key     key_len ref     rows    Extra
    et      ALL     PRIMARY NULL    NULL    NULL    74
    tt      ref     AssignedPC,ClientID,ActualPC    ActualPC        15
            et.EMPLOYID   52      where used
    et_1    eq_ref  PRIMARY PRIMARY 15      tt.AssignedPC  1
    do      eq_ref  PRIMARY PRIMARY 15      tt.Clientid    1
    

    Which is 'almost' as good as it can get. The problem is that MySQL assumes that tt.AcutalPC is evenly distributed which isn't the case in the tt.

    Fortunately it is easy to tell MySQL about this:

    shell> isamchk --analyze PATH_TO_MYSQL_DATABASE/tt
    shell> mysqladmin refresh
    

    And now the join is 'perfect':

    table   type    possible_keys   key     key_len ref     rows    Extra
    tt      ALL     AssignedPC,ClientID,ActualPC    NULL    NULL    NULL
            3872     where used
    et      eq_ref  PRIMARY PRIMARY 15      tt.ActualPC    1
    et_1    eq_ref  PRIMARY PRIMARY 15      tt.AssignedPC  1
    do      eq_ref  PRIMARY PRIMARY 15      tt.Clientid    1
    

    7.18 DESCRIBE syntax. Get information about columns

    (DESCRIBE | DESC) table [column]

    Gives information about columns. This command is for Oracle compatibility. See section 7.16 SHOW syntax. Get information about tables, columns.... Column may be a column name or a string. Strings may contain wild cards.

    7.19 LOCK TABLES syntax

    LOCK TABLES table_name [AS alias] READ|WRITE [, table_name READ|WRITE]
    ...
    UNLOCK TABLES
    

    Locks tables for this thread. If a thread has a READ lock on a table, the thread (and all other threads) can only read from the table. If a thread has a WRITE lock one a table, then only this thread can READ and WRITE on the table. All threads waits until they get all locks (no timeouts).

    When one uses LOCK TABLES one must lock all tables one is going to use! This policy ensures that table locking is deadlock free.

    LOCK TABLES trans READ, customer AS c WRITE
    SELECT SUM(value) FROM trans WHERE customer_id= #some_id#;
    UPDATE customer SET total_value=#value_from_last_statement# WHERE
    customer_id=#some_id#
    UNLOCK TABLES
    

    All tables are automatically unlocked when one issues another LOCK TABLES or if the connection to the server is closed.

    Normally you don't have to lock tables. There is a couple of cases when you would like to lock tables anyway:

    You can also solve some cases by using user level locks: GET_LOCK() and RELEASE_LOCK(). These locks are saved in a hash table in the server and implemented with pthread_mutex for high speed. See section 7.3.11 Miscellaneous functions

    7.20 SET OPTION syntax

    SET [OPTION] SQL_VALUE_OPTION=value, ...
    

    The used options remain in effect for the whole current session.

    The different options are:

    SQL_SELECT_LIMIT=value
    The maximum number of records to return in any select. If a select has a limit clause it overrides this statement. The default value for a new connection is 'unlimited'.
    SQL_BIG_TABLES= 0 | 1
    If set to 1 then all temporary tables are stored on disk instead of in memory. This will be a little slower, but one will not get the error The table ### is full for big selects that require a big temporary table. The default value for a new connection is 0 (use in memory temporary tables).
    SQL_BIG_SELECTS= 0 | 1
    If set to 1 then MySQL will abort if a select is attempted that will probably take a very long time. This is useful when an erroneous WHERE statement has been issued. A big query is defined as a SELECT that will probably have to examine more than max_join_size rows. The default value for a new connection is 0 (which will allow all SELECT's).
    CHARACTER SET character_set_name | DEFAULT
    This maps all strings from and to the client with the given mapping. Currently the only option for character_set_name is cp1251_koi8, but one can easily add new mappings by editing the file mysql_source_directory/sql/convert.cc. One can restore the default mapping by using DEFAULT as the character_set_name.
    SQL_LOG_OFF= 0 | 1
    If set to 1 then no logging will done to the standard log for this client if the client has process list privileges. This doesn't affect the update log!
    TIMESTAMP= timestamp_value | DEFAULT
    Set the time for this client. This is used to get the original timestamp if one uses the update log to restore rows.
    LAST_INSERT_ID= #
    Set the value to be returned from LAST_INSERT_ID(). This is stored in the update log when one uses LAST_INSERT_ID() in a command that updates a table.

    7.21 GRANT syntax (Compatibility function)

    GRANT (ALL PRIVILEGES | (SELECT, INSERT, UPDATE, DELETE,
     REFERENCES (column list), USAGE))
     ON table TO user,... [WITH GRANT OPTION]
    

    This command doesn't do anything. It is only in MySQL for compatibility reasons. (To make it easier to port code from other SQL servers). Privileges in MySQL are handled with the MySQL grant tables. See section 6.2 How does the privilege system work?

    7.22 CREATE INDEX syntax (Compatibility function)

    CREATE [UNIQUE] INDEX index_name ON table_name ( column_name[(length]),... )

    This function doesn't do anything in MySQL version before version 3.22. This is mapped to a ALTER TABLE call to create indexes. See section 7.7 ALTER TABLE syntax

    Normally one creates all INDEX at the same time with CREATE TABLE See section 7.6 CREATE TABLE syntax

    (col1, col2) creates a multiple index over the two columns. The index can be seen as a concatenation of the given columns. If you in CREATE TABLE use INDEX(col1), INDEX(col2) instead of INDEX(col1,col2) you get two separate indexes instead of one multiple index.

    SELECT * FROM table WHERE col1=# AND col2=#
    

    In a case of an index on (col1,col2) the right row(s) can be fetched directly. In a case of (col1), (col2) the optimizer decides which index will find fewer rows and this index will be used to fetch the rows.

    If the table has an index (col1,col2,col3...) the prefix of this can be used by the optimiser to find the rows. This means that the above gives you search capabilities on: (col1) and (col1,col2) and (col1,col2,col3)...

    MySQL can't use a portion of an index to locate rows through an index.

    With the definition (col1,col2,col3):

    SELECT * FROM table WHERE col1=#
    SELECT * FROM table WHERE col2=#
    SELECT * FROM table WHERE col2=# and col3=#
    

    only the first query will use indexes.

    MySQL will also use indexes if the LIKE argument is a constant string that doesn't start with a wild character:

    The following will use indexes:

    SELECT * from table WHERE key_column like "Patrick%";
    SELECT * from table WHERE key_column like "Pat%_ck%";
    

    In the above cases only rows with Patrick <= key_column < Patricl and Pat <= key_column < Pau will be considered.

    The following selects will not use indexes:

    SELECT * from table WHERE key_column like "%Patrick%";
    SELECT * from table WHERE key_column like other_column;
    

    With column_name(length) syntax one can specify an index which is only a part of a string column. This can make the index file much smaller.

    CREATE INDEX part_of_name ON customer (name(10))
    

    As it's quite normal that most names differs in the first 10 characters, the above definition should not slow down searches on names, but it could save a lot of disk and even speed up inserts!

    7.23 DROP INDEX syntax (Compatibility function)

    DROP INDEX index_name

    This function doesn't do anything in MySQL before version 3.22. This is mapped to a ALTER TABLE call to drop the INDEX or UNIQUE definition. See section 7.7 ALTER TABLE syntax

    7.24 Comment syntax

    MySQL supports the # to end of line and /* multiple line */ comment styles.

    select 1+1;	# This comment is to the end of line
    select 1 /* in-line-comment */ + 1;
    select 1+
    /*
    This will be ignored
    */
    1;
    

    MySQL doesn't support the -- ANSI SQL style comments. See section 5.2.7 -- as start of a comment.

    7.25 CREATE FUNCTION syntax

    CREATE FUNCTION <function_name> RETURNS [string|real|integer]
           SONAME <name_of_shared_library>
    
    DROP FUNCTION <function_name>
    

    User definable functions (UDF) is way to extend MySQL with new functions that works as native MySQL functions like ABS() and concat(). UDF's are written in C or C++ and require that dynamic loading works on the operating system. The source distribution includes the file `udf_example.cc' that defines 5 new functions.

    The functions name, type and shared library is saved in the new system table 'func' in the 'mysql' database. To be able to create new functions one must have write privilege for the database 'mysql'. If one starts MySQL with --skip-grant-tables, then UDF initialization will also be skipped.

    Each defined function may have a xxxx_init function and a xxxx_deinit function. The init function should alloc memory for the function and tell the main function about the max length of the result (for string functions), number of decimals (for double functions) and if the result may be a null value.

    If a function sets the 'error' argument to 1 the function will not be called anymore and mysqld will return NULL for all calls to this instanse of the function.

    All strings arguments to functions are given as string pointer + length to allow handling of binary data. Remember that all functions must be thread safe. This means that one is not allowed to alloc any global or static variables that changes! If one needs memory one should alloc this in the init function and free this on the __deinit function.

    A dynamically loadable file should be compiled sharable (something like: gcc -shared -o udf_example.so myfunc.cc). You can easily get all switches right by doing: cd sql ; make udf_example.o Take the compile line that make writes, remove the '-c' near the end of the line and add -o udf_example.so to the end of the compile line. The resulting library (udf_example.so) should be copied to some dir searched by ld, for example /usr/lib.

    Some notes about the example functions:

    After the library is installed one must notify mysqld about the new functions with the commands:

    CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so";
    CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so";
    CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so";
    CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so";
    CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so";
    

    Functions should be created only once. The functions can be deleted by:

    DROP FUNCTION metaphon;
    DROP FUNCTION myfunc_double;
    DROP FUNCTION myfunc_int;
    DROP FUNCTION lookup;
    DROP FUNCTION reverse_lookup;
    

    The CREATE FUNCTION and DROP FUNCTION update the func table. All active function will be reloaded on every restart of server (if --skip-grant-tables is not given).

    7.26 Is MySQL picky about reserved words?

    A common problem stems from trying to create a table with column names timestamp or group, the names of datatypes and functions built into MySQL. You're allowed to do it (for example, ABS is an allowed column name), but whitespace is not allowed between a function name and the '(' when using the functions whose names are also column names.

    The following are explictly reserved words in MySQL. Most of them (for example) group, are forbidden by ANSI SQL92 as column and/or table names. A few are because MySQL needs them and is (currently) using a yacc parser:

    action add all alter
    and as asc auto_increment
    between bigint bit binary
    blob both by cascade
    char character change check
    column columns create data
    database databases date datetime
    day day_hour day_minute day_second
    dayofweek dec decimal default
    delete desc describe distinct
    double drop escaped enclosed
    enum explain fields float
    float4 float8 foreign from
    for full grant group
    having hour hour_minute hour_second
    ignore in index infile
    insert int integer interval
    int1 int2 int3 int4
    int8 into is join
    key keys leading left
    like lines limit lock
    load long longblob longtext
    match mediumblob mediumtext mediumint
    middleint minute minute_second month
    natural numeric no not
    null on option optionally
    or order outer outfile
    partial precision primary procedure
    privileges read real references
    rename regexp repeat replace
    restrict rlike select set
    show smallint sql_big_tables sql_big_selects
    sql_select_limit sql_log_off straight_join starting
    table tables terminated text
    time timestamp tinyblob tinytext
    tinyint trailing to use
    using unique unlock unsigned
    update usage values varchar
    varying varbinary with write
    where year year_month zerofill
    The following symbols (from the table above) are disallowed by ANSI SQL but allowed by MySQL as column/table names. This is because some of these names are very natural names and a lot of people have already used them.

    8 How safe/stable is MySQL

    8.1 How stable is MySQL?

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

    Each release of MySQL has been usable and there have only been problems when users start to use code from 'the gray zones'. Naturally, outside users can't know what the gray zones are and I hope this section will clarify those currently known.

    Here we will try to answer some of the more important questions that seems to concern a lot of people and clarify some issues. This section has been put together from the information that has come forward in the mailing list which is very active in reporting bugs.

    How stable is MySQL? Can I depend on MySQL in this project?

    This is about the 3.21.x version of MySQL. All known and reported bugs are fixed in the latest version with the exception of the bugs listed in the BUGS file which are things that are 'design' related.

    MySQL is written in multiple layers and different independent modules. Here is a list of the different modules and how tested each of them are.

    The ISAM table handler. Stable
    This is how all the data is stored. In all MySQL releases there hasn't been a single (reported) bug in this code. The only known way to get a corrupted table is to kill the server in the middle of an update and because all data is flushed to disk between each query even this is unlikely to destroy any data beyond rescue. There hasn't been a single bug report about lost data because of bugs in MySQL either.
    The parser and lexical analyser. Stable
    There hasn't been a single reported bug in this system for a couple of months.
    The C client code. Stable
    No known problems. In early 3.20 releases there were some limitations in the send/receive buffer size. In 3.21.x the send/receive buffer is now dynamic up to a default of 512K.
    mysql, mysqladmin and mysqlshow. Stable
    The command line clients have had very few bugs.
    mysqldump and mysqlimport. Beta
    Rewritten for 3.21.
    Basic SQL. Stable
    The basic SQL function system and string classes and dynamic memory handling. Not a single reported bug on this system.
    Query optimiser. Gamma
    Some changes in 3.21.
    Range optimiser. Alpha
    Totally rewritten for 3.21.x
    Join optimizer. Gamma
    Small changes for 3.21.
    GROUP BY, ORDER BY and related function COUNT(). Beta
    Rewritten for 3.21 and throughly tested.
    Locking. Gamma
    This is very system dependent. On some systems there are big problems using standard OS locking (fcntl). In these case one should run the MySQL daemon with the --skip-locking flag. Known problems are some Linux systems and SunOS when using NFS-mounted file systems.
    Linux threads. Gamma
    The only problem found is with the fcntl() call, which is fixed by using --skip-locking. Some people have reported lockup problems with the 0.5 release.
    Solaris 2.5+ pthreads. Stable
    We use this for all our production work.
    MIT-pthreads (Other systems). Beta
    No reported bugs since 3.20.15 and no known bugs since 3.20.16. On some system there is 'misfeature' where some operations are quote slow (a 1/20 second sleep is done between each query). Of course MIT-pthreads may slow down everything a bit, but for index based selects a select is usually done in one time frame so there shouldn't be a mutex locking/thread juggling.
    Other thread implementions. Alpha
    The ports to other systems are still very new and may have bugs, either in MySQL but most often in the thread implementation itself.
    LOAD DATA..., INSERT ... SELECT. Stable
    Some people have thought they have found bugs in this but have turned up being misunderstandings. So check the manual before reporting bugs!
    ALTER TABLE. Gamma
    Partly rewritten for 3.21.
    mysqlperl. Stable
    No bugs reported except a lot of compiling and linking problems.
    DBD. Beta
    Now maintained by Jochen Wiedmann.
    mysqlaccess. Beta
    Written and maintained by
    The Technical Documentation. Beta
    It is improving.
    MyODBC (uses ODBC SDK 2.5). Beta
    It seems to work well with some programs.

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

    8.2 Why are there is so many release of MySQL?

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

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

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

    If there is, by any chance, a fatal bug in a release we will make a new release as soon as possible. We would like other companies to do this too. :)

    The 3.21.x version incorporates major portability changes for many different systems. When the 3.21 release is stable we will remove the alpha/beta suffix and move active development to 3.22. Bugs will still be fixed in the stable version. We don't believe in a complete freeze, as this also leaves out bug fixes and things that 'must be done'. 'Somewhat freezed' means that we will maybe add small things that 'almost surely will not affect anything thats already working'.

    If you are running and old system and want to upgrade, but you don't want to take chances with 3.21 you should upgrade to 3.20.32. I have tried to only fix fatal bugs and make small, relatively safe changes in this version.

    If you are trying MySQL for the first time or have a little time to test out that your current system, you should use 3.21.

    8.3 Checking a table for errors

    If MySQL crashed (for example if the computer is turned off) when all data is not written to disk the tables may have become corrupted. To check a table use:

    isamchk table_name
    This finds 99.99 % of all errors. What it can't find is when only the data file has been corrupted.
    isamchk -e table_name
    This goes a complete and through check of all data. It does a check-read of all keys for every row to check that this indeed points to the right row. This may take a LONG time on a big tables with many keys. isamchk will normally stop after the first found error. If you want more information you can add the --verbose (-v) switch to isamchk. In normal usage a simple 'isamchk' is safe enough!
    isamchk -e -i table_name
    As the above but it also prints some statistics.

    We at TcX run a cron job on all our important tables once a week.

    35 0 * * 0 /path/to/isamchk -s /path/to/dbs/*/*.ISM
    

    This prints out any crashed tables so we can go and examine and repair them when needed.

    As we haven't had any unexpected crashes (without hardware trouble) tables for a couple of years now (this is really true), once a week is more than enough for us.

    Of course, whenever the machine has done a reboot in the middle of a update one usually has to check all the tables that could have been affected. (This is an 'expected crashed table')

    We recommend that to start with, one should do a isamchk -s on all updated tables each night until one comes to trust MySQL as much as we do.

    Naturally, one could add a check to safe_mysql that, if there is an old pid file left after a reboot, it should check all tables that have been modified the last 24 hours.

    8.4 How to repair tables

    The file format that MySQL uses to store data has been extensively tested, but there are always instances (like a hard kill on the mysqld process in the middle of a write, a hardware error or a unexpected shutdown of the computer) when some tables may be corrupted.

    The sign of a corrupted table is usually when queries abort unexpectedly and one gets errors like:

    In these cases you have to repair your tables. The isamchk external utility can usually detect and fix most things that go wrong. See section 12.2 The MySQL table check, optimise and repair program.

    If you are going to use isamchk on very large files, you should first decide how much memory you want to give to isamchk. More memory gives more speed. For example, if you have more than 32M ram, try:

    isamchk -O sortbuffer=16M -O keybuffer=16M -O readbuffer=1M
            -O writebuffer=1M ....
    

    8.5 Is there anything special to do when upgrading/downgrading MySQL?

    One can always move the MySQL form and data files between different versions on the same architecture as long as one has the same base version of MySQL. The current base version is of course 3. If one changes the character set (sort order) by recompiling MySQL one has to do a isamchk -r -q on all tables.

    If you are paranoid and/or afraid of new versions you can always rename your old mysqld to something like mysqld-'old-version-number'. If your new mysqld then does something unexpected you can simple shut it down and restart with your old mysqld!

    When you do a upgrade you should of course also take a backup of your old databases. Sometimes its good to be a little paranoid!

    8.5.1 Upgrading to 3.22 from a 3.21 version

    Nothing that affects compatibility has changed. The only pitfall is that new tables that are created with a DATE type will use the new way to store the date. This means that you can't access this field from an old mysqld version.

    The C interface to mysql_real_connect() has changed. If you have an old client program that calls this, you must place a 0 for the new DB argument (or recode the client to send the db element for faster connections).

    8.5.2 Upgrading to 3.21 from a 3.20 version

    If you already have a version older than 3.20.28 running and want to switch to 3.21.# you need to do the following:

    You can start the mysqld 3.21 server with safe_mysqld --old-protocol to use it with clients from the 3.20 distribution. In this case the new client function, mysql_errno(), will not return any server error, only CR_UNKNOWN_ERROR, (but it works for client errors) and the server uses the old password() checking instead of the new one.

    If you are NOT using --old-protocol:

    MySQL 3.20.28 and above can handle the new user table format without affecting clients. If you have a MySQL version below 3.20.28, passwords will not work on it anymore if you convert the user table. So to be safe, you should first upgrade to at least 3.20.28 and then upgrade to 3.21.#.

    The new client code works with a 3.20.# mysqld server, so you can use the old 3.20.# server if you experience problems with 3.21.# without having to recompile the clients again.

    If you are not using the option --old-protocol to mysqld, old clients will issue the error message:

    ERROR: Protocol mismatch. Server Version = 10 Client Version = 9

    The new perl interface DBI/DBD also supports the old mysqlperl interface. The only change one has to do if one uses mysqlperl is to change the arguments to the connect() function. The new arguments are: host,database,user,password (the user & password arguments has changed places).

    The following changes may effect queries in old applications.

    8.5.3 Upgrading to another architecture

    Currently the MySQL data files *.ISM and *.ISD are architecture dependent. If you want to move your applications to another architecture you should use mysqldump.

    mysqldump will (by default) create a file full of SQL statements which you can then transfer to the other machine and then feed as input to the mysqld server.

    Try mysqldump --help to check what options you can use.

    The easist way (not the fastest) way to move a database between two connected machines are running this on the machine where the databases are:

    mysqladmin -h 'other hostname' create database
    mysqldump --quick --drop database | mysql -h 'other hostname' database
    

    You can also store the result on file (compressed in this example):

    mysqldump --quick databasename | gzip > databasename.contents.gz
    
    # transfer the contents files to the target machine and use this
    # on the target machine
    
    mysqladmin create databasename
    gunzip < databasename.contents.gz | mysql databasename
    

    You can also use mysqldump and mysqlimport to do this: (This is much faster than simply using mysqldump for big tables)

    mkdir full-path-to-some_dir
    mysqldump --tab=full-path-to-some-dir database
    
    # transfer the contents files to the target machine and use this
    # on the target machine
    
    mysqladmin create database
    cat full-path-to-some_dir/*.sql | mysql database
    mysqlimport database some_dir/*.txt
    

    Don't forget to also copy the 'mysql' database. You may have to use the MySQL user "root" on the new machine until you've got the mysql database in place.

    Finally (or straight after you import the 'mysql' database) do a : mysqladmin reload on the new machine.

    8.6 Year 2000 compliance

    MySQL uses Unix times functions and has no problems with dates until 2069; All 2 digit years are regarded to be in the range 1970-2069, which means that if you store 01 in a year column, MySQL treats it as 2001. In MySQL 3.22 the new YEAR column type can store years 0, 1901-2155 in 1 byte and display them with 2 or 4 digits.

    The year 2000 is only a problem for old applications that has stored date with 2 digits and manipulates the date in many different programs in with different functions. All MySQL date functions are stored in one file `sql/time.cc' and coded very carefully to be 2000 safe.

    9 MySQL Server functions

    9.1 Which languages are supported by MySQL?

    mysqld can give error messages in the following languages: Czech, Dutch, English (default), French, German, Norwegian, Norwegian-ny, Polish, Portuguese, Spanish and Swedish.

    To start mysqld with a language use one of the --language=lang or -L lang switches:

    mysqld --language=swedish or mysqld --language=/usr/local/share/swedish

    Note that all the language names are in lowercase.

    The language files are located (by default) in `mysql_base_dir/share/LANGUAGE/'.

    If you want to update the error message file, you should edit the errmsg.txt file and execute comp_err errmsg.txt errmsg.sys to generate the errmsg.sys file.

    9.1.1 Character set used for data & sorting

    By default, MySQL will use the ISO8859-1 (Latin1) character set. This is the character set used in the USA and western Europe.

    The character set decides what characters are allowed in names and how things are sorted by the ORDER BY and GROUP BY commands.

    You may change this at compile time by the configure switch --with-charset=charset. See section 4.5.1 Quick installation overview.

    If you want to add another character set to MySQL you must:

    If you are making a multiple-character char set, you can use the _MB macros. In `strings/m_ctype.h.in' add:

    #define MY_CHARSET_<C>		X
    #if MY_CHARSET_CURRENT == MY_CHARSET_<C>
    #define USE_MB
    #define USE_MB_IDENT
    #define ismbchar(p, end)	(...)
    #define ismbhead(c)		(...)
    #define mbcharlen(c)		(...)
    #define MBMAXLEN		N
    #endif
    
    MY_CHARSET_<C> unique value.
    USE_MB This charset have mb-char.
    USE_MB_IDENT: Use mb-char as identifier. (optional)
    ismbchar(p, e) return 0 if not mb-char, or size of char if mb-char. Check from (char*)p to (char*)e-1.
    ismbhead(c) Is c first char of mb or not?
    mbcharlen(c) Size of char if c is first char of mb.
    MBMAXLEN Maximum size of one character.

    9.2 The Update log

    When started with the --log-update=file_name switch mysqld makes a log file with all SQL command that update data. It results in a file with name of file_name.# where # is a number that is increased for each refresh. If you do not give a file name the current hostname is used.

    The logging is smart since it only writes statements that really update data. So an UPDATE or a DELETE with a WHERE that finds no rows is not written to the log. It even skips UPDATEs that updates a column to the value it had before.

    If you want to update a database according from a update log you could do the following:

    cat file-name.* | mysql
    

    9.3 How big can MySQL tables be?

    Currently a table is limited to the operation system file size. On Linux the current limit is 2G, on Solaris 2.5.1 the limit is 4G and on Solaris 2.6 the limit is going to be 1000G. To get more that 4G requires some changes to MySQL that are on the Todo. See section F List of things we want to add to MySQL in the future (The TODO)..

    If your big table is going to be read_only, you could use pack_isam (See section 12.3 The MySQL compressed read only table generator) to merge and compress many tables to one. As pack_isam usually compresses a table by at least 50%, you can have much bigger tables.

    Another solution can be the included MERGE library, which allows one to handle a collection of identical tables as one. Currently MERGE can only be used to scan a collection of tables because it doesn't support indexes. We will add indexes to this in the near future. Identical in this case means that all tables are created with identical column information.

    10 How to get maximum performance out of MySQL

    10.1 How does one change the size of MySQL buffers?

    You can get the current buffer sizes with:

    > ./mysqld --help

    This should result in a list of all mysqld options and configurable variables like the following.

    Possibly variables to option --set-variable (-O) are: 
    back_log              current value: 5
    join_buffer           current value: 131072
    key_buffer            current value: 1048568
    max_allowed_packet    current value: 65536
    max_connections       current value: 90
    max_join_size         current value: 4294967295
    max_sort_length       current value: 1024
    net_buffer_length     current value: 8192
    record_buffer         current value: 131072
    sort_buffer           current value: 2097144
    table_cache           current value: 64
    tmp_table_size        current value: 1048576
    thread_stack          current value: 65536
    
    back_log How many outstanding connection requests may MySQL have. This comes into play when the main MySQL thread gets VERY many connection requests in a very short time. It then takes some time (but very short) for the main thread to check the connection and start a new thread. The back_log is how many connects can be stacked during this short time before MySQL momentarily stops answering new requests. You only need to increase this if you expect a large number of connections in a short period of time. In other words, the size of the listen queue for incoming tcp/ip connections. The manual page for the unix system call listen(2) should have more details. Check your OS documentation for the maximum value for this variable.
    join_buffer This buffer is used for full joins (without indexes). It is allocated one time for each full join between two tables. Increase this to get a faster full join when adding indexes is not possible. Normally the best way to get fast joins is by adding indexes.
    key_buffer Buffers index blocks and are shared by all threads. You might want to increase this when doing many delete/inserts on a table with lots of indexes. To get even more speed use LOCK TABLES. See section 7.19 LOCK TABLES syntax.
    max_allowed_packet Max size of one packet. This allows the message buffer to grow up to this limit when needed (it is initiated to net_buffer_length). May be set very big because this is mainly to find erroneous packets. You must increase this if you are using big BLOBS. It should be as big as the biggest BLOB you want to use.
    max_connections How many simultaneous clients are allowed. If you increase this you probably has to increase the number of file descriptors mysqld has. This is Operating system depended so look at you OS documentation.
    max_join_size Joins that touch more records than max_join_size return an error. Set this if you have users to tend to make joins without a WHERE that take a long time and return millions of rows.
    max_sort_length The number of bytes to use when sorting on BLOB or TEXT columns.
    net_buffer_length The communication buffer is reset to this size between queries. This should not normally be changed, but if you have very little memory you can set it to the expected size of a query.
    record_buffer Each thread that is doing a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans you may want to increase this.
    sort_buffer Each thread that needs to do a sort allocates a buffer of this size. Increase this for faster ORDER BY or GROUP BY. See section 15.3 Where MySQL stores temporary files
    table_cache Number of open tables for all threads. If this is increased you must see to that the number of open file descriptor is also increased. MySQL needs two file descriptors for each unique table.
    tmp_table_size If a temporary table gets bigger than this a The table ### is full error will be generated. Increase this if you do many advanced GROUP BY queries.
    thread_stack How big will each threads C stack be. A lot of the limits detected by crash-me are dependent on this. The default is normally enough.
    MySQL uses algorithms that are very scalable so one can usually run with very little memory or give MySQL more memory to get better performance. If you have much memory and many tables and want maximum performance with a moderate number of clients you should use something like:
    > safe_mysqld -O key_buffer=16M -O table_cache=128 \
            -O sort_buffer=4M -O record_buffer=1M &
    
    If you have little memory with lots of connections, use something like:
    > safe_mysqld -O key_buffer=512k -O sort_buffer=100k -O record_buffer=100k &
    
    or even
    > safe_mysqld -O key_buffer=512k -O sort_buffer=16k -O table_cache=32 \
            -O record_buffer=8k -O net_buffer=1K &
    
    Note that if you change an option to mysqld it is only for that instance of the server. To see the effects of a parameter change, do something like this mysqld -O key_buffer=32m --help. You can check the parameters in effect with mysqladmin variables. If there are very many connections, 'swapping problems' may occur if mysqld has not been configured to use very little memory for each connection. It also works better if you have a enough memory for all connections of course. For example, for 200 open connections one should have a table cache of at least 200 * (max_number of tables in join).

    10.2 How compiling and linking affects the speed of MySQL

    Most of the following test are done on Linux and with the MySQL benchmarks, but they should give some indication for other operating systems:

    On Linux you will get the fastest code when compiling with pgcc and -O6. To compile sql_yacc.cc with these options one needs 180M memory as gcc/pgcc needs a lot of memory to make all functions inline. One should also set CXX=gcc when configuring MySQL to avoid inclusion of the libstdc++ library.

    One gets the fastest executable when one links with -static. Using Unix socket instead of TCP/IP to connect to a database gives also better performance.

    The MySQL-linux distribution that is distributed by TCX is compiled with pgcc and linked statically.

    10.3 How does MySQL use memory?

    When running mysqld ps and other programs may report that it uses a lot of memory. This may be caused by thread-stacks on different memory addresses. For example, the Solaris ps calculates the unused memory between stacks as used memory. You can verify this by checking available swap with 'swap -s'. We have tested mysqld with commercial memory-leakage detectors so there should not be any memory leaks.

    10.4 How does MySQL use indexes?

    All indexes, PRIMARY, UNIQUE and INDEX(), are stored in B trees. Strings are automatically prefix- and end-space compressed. See section 7.22 CREATE INDEX syntax (Compatibility function)

    10.5 What kind of optimisation is done on the WHERE clause?

    (Incomplete, MySQL does a lot of optimisations.)

    The first issue about making a slow SELECT ... WHERE faster is to check if one could add an index. All references between different tables should usually be done with indexes. One can use the EXPLAIN command to check which indexes are used in a select. See section 7.17 EXPLAIN syntax. Get information about a SELECT. See section 10.4 How does MySQL use indexes?

    10.6 How does MySQL open & close tables?

    The cache of open tables can grow to a maximum of table-cache (default 64, changeable with -O table_cache=#). A table is never closed, except when the cache is full and another thread tries to open a table or if one uses 'mysqladmin refresh'.

    When the limit is reached, MySQL closes as many tables as possible, until the cache size has been reached or there are no more unused tables. This means that if all tables are in use by some threads, there will be more open tables than the cache limit, but the extra tables will be closed eventually. Tables are closed according to last-used order.

    A table is opened (again) for each concurrent access. This means that if one has two threads running on the same table or access the table twice in the same query (with AS) the table needs to be opened twice. The first open of any table takes two file descriptors, each following use of the table takes only one file descriptor.

    You can move tables and databases from the database directory to other disks and put a symbolic links instead of the old files/directories.

    If MySQL notices that a table is a symbolic linked it will resolve the symlink and use the table it points to instead. This works on all system that supports the realpath() call (At least Linux and Solaris supports realpath())! On system that doesn't supports realpath() you should not use the symlink and the table at the same time! The tables will be inconsistent after any update to the tables.

    MySQL doesn't support linking of databases by default. Things will work fine as long as you don't make a symbolic link between databases. The following shows a case that DOES NOT work:

    db2->db1
    db1/
    

    If you really need this you have to change in mysys/mf_format.c:

        if (!lstat(to,&stat_buff))		/* Check if it's a symbolic link */
          if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff))
    
    to
          if (realpath(to,buff))
    

    10.6.0.1 What are the drawbacks of creating possibly thousands of tables in a database?

    Each table is actually three files. If you have many files in a directory open, close and create will be slow. If you also do selects on many different tables there will be a little overhead because when the table cache is full, for every table that has to be opened another has to be closed. One can make the overhead smaller by making the table cache larger.

    10.7 How does MySQL lock tables?

    All locking in MySQL is deadlock free. This is managed by always requesting all needed locks at once at query start and always locking the tables in the same order.

    The locking method MySQL uses for WRITE lock works as follows:

    If there is no locks on the table, put a write lock on it, else put the lock in the write lock queue.

    The locking method MySQL uses for READ locks works as follows:

    If there is no write locks on the table, put a read lock on it else put the lock in the read lock queue.

    When a lock is released first use give the lock to the threads in the write lock queue and after this to the threads in the read lock queue.

    This means that if you have many updates on the same table, select statements will be waiting until there is no more updates.

    To fix this in the case where you do many inserts and many selects on the same table you could insert rows in another table and once in a while update the other table with all records from the temporary table.

    This can be done with the following code:

    LOCK TABLES real_table WRITE, insert_table WRITE
    insert into real_table select * from insert_table
    delete from insert_table
    UNLOCK TABLES
    

    One could also change the locking code in mysys/thr_lock.c to use only one queue. In this case write locks would have the same priority that read locks and this could help some applications.

    10.8 How should I arrange my table to be as fast/small as possible?

    To check how you are doing, run isamchk -evi on the .ISM file. @c See section 12.2 The MySQL table check, optimise and repair program.

    10.9 What affects the speed of the INSERT statement?

    The time to insert a record consists of:

    Where (number) is proportional time. This does not take into consideration the initial overhead to open tables (which is done once for each simultaneous running query).

    The size of the table slows down the insert of indexes by N log N (B-trees).

    A way of speeding up inserts is to lock your table during the inserts.

    LOCK TABLES a WRITE;
    
    INSERT INTO a VALUES (1,23)
    INSERT INTO a VALUES (2,34)
    INSERT INTO a VALUES (4,33)
    INSERT INTO a VALUES (8,26)
    INSERT INTO a VALUES (6,29)
    
    UNLOCK TABLES;
    

    The main speed difference is that the index buffer is only flushed once to disk for all inserts. Normally there would be as many index buffer flushes as there are inserts.

    Locking will also lower the total time of multi-connection test but the maximum wait time for some threads will go up.

    For example:

    thread 1 does 1000 inserts
    thread 2, 3, and 4 does 1 insert
    thread 5 does 1000 inserts
    

    If you don't use locking, 2, 3 and 4 will finish before 1 and 5. If you use locking 2,3,4 may finish before 1 or 5 but probably not, but the total time should be about 40% faster.

    As INSERTs, UPDATEs and DELETEs are very fast in MySQL, one will obtain better overall performance by adding locks around everything that does more than about 5 inserts/updates in a row. If one does very many inserts in a row one could do a UNLOCK TABLES followed by a LOCK TABLES once in a while (about each 1000 rows) to give other threads access to the table. This would still give a nice performance gain.

    Of course LOAD DATA INFILE is much faster still.

    10.10 What affects the speed of DELETE statement?

    The delete time of a record is exactly proportional to the number of indexes. To increase the speed of deletes you can increase the size of the index cache. The default index cache is 1M and to get faster deletes it should be increased by several factors (try 16M if you have enough memory).

    10.11 How do I get MySQL to run at full speed?

    Start with benchmarking your problem! You can take any of the programs from the MySQL benchmark suite (normally found in the sql-bench directory) and modify it for your needs. By doing this you can try different solutions to your problem and test which is really fastest solution for you.

    10.12 What are the different row formats? Or when to use VARCHAR/CHAR?

    MySQL dosen't have true SQL VARCHAR() types.

    MySQL has instead 3 different ways to store records and uses this to emulate VARCHAR():

    If one doesn't use any of the VARCHAR, BLOB or TEXT column types a fixed row size is used, otherwise a dynamic row size is used. CHAR() and VARCHAR() are treated identically from the applications point of view; Both truncates end space from the column when the column is accessed.

    You can check the format used in a table with isamchk -d.

    MySQL has three different table formats:

    1. Fixed length tables.
    2. Dynamic tables
    3. Compressed tables:

    MySQL can support different index types, but the normal one is NISAM. This is a B-tree index and one can roughly calculate the size for the index file as: sum over all keys:

    (key_length+4)*0.67

    (This is for the worst case when all keys are inserted in sorted order.

    String index are space compressed and if the first index part is a string it will also be prefix compressed. This will usually make the index file smaller if the columns are not filled up to 100% or if there are many duplicates.

    10.13 Why so many open tables?

    When you run mysqladmin status you get something like:

    Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
    

    This can be somewhat perplexing if you only have 6 tables.

    As MySQL is multithreaded it may have many queries on the same table at once. To minimise the problem with two threads having different states on the same file, I open the table again for each concurrent thread. This takes some memory and one extra file descriptor for the data file. The index file descriptor is shared between all threads.

    11 MySQL benchmark suite

    This should contain a technical description of the MySQL benchmark suite (and crash-me) but that description is not written yet. Currently see the code and results in the `bench' directory in the distribution. And of course on the web page.

    It is meant to be a benchmark that will tell any user what things a given SQL implementation is good or bad at.

    crash-me tries to find what a database supports by actually running queries. It find for example:

    12 MySQL Utilites

    12.1 Overview of the different MySQL programs

    All MySQL clients that communicate with the server using the mysqlclient library use the following environment variables:

    Name Description
    MYSQL_UNIX_PORT The default socket. Used with 'localhost'.
    MYSQL_TCP_PORT The default TCP port.
    MYSQL_PWD The default password.
    MYSQL_DEBUG Debug-trace options when debugging.
    TMPDIR Directory where temporary tables/files are created.
    All MySQL programs take many different options. Use the switch --help to get a full description of the different options. Try for example mysql --help.
    mysql
    mysql is a simple SQL shell (with GNU readline). It supports interactive use and non-interactive querying. When used interactively, the result are given in an ascii-table format, but when used as a filter the result is a tab-separated output (this as other things can be changed with startup options). You can simply run scripts with mysql database < script.sql > output.tab. If you have a problem with that the memory in the client is not enough, use the --quick switch! This will force mysql to use mysql_use_result() instead of mysql_store_result() to retrieve the result set.
    mysqlaccess
    Script to check the privileges for a host, user and database combination.
    mysqladmin
    Administration utility. Create/Drop databases, reload (read new users) and refresh (flush tables to disk, reopen log files). Also gives version, process and status information from the server.
    mysqld
    The SQL daemon. This should always be running.
    mysqldump
    Dump a MySQL database into a file with SQL statements or tab separated text files. Enhanced freeware originally by Igor Romanenko.
    mysqlimport
    Imports one or more text files into respective tables. Can use all formats supported by LOAD DATA INFILE. See section 7.14 LOAD DATA INFILE syntax
    mysqlshow
    Shows information about databases, tables, columns and indexes.
    mysqlbug
    This script should always be used when filing a bug report to the MySQL list.
    mysql_install_db
    Creates the MySQL grant tables with default privileges. This is usually only executed once when installing the first MySQL release on a new system.
    isamchk
    Check, optimise and repair MySQL tables.
    make_binary_release
    Makes a binary release of a compiled MySQL. This could be sent by ftp to /pub/mysql/Incoming on www.tcx.se for the convenience of other MySQL users.
    msql2mysql
    A shell script to convert a mSQL program to MySQL. Doesn't handle all cases but gives a good start when converting.
    replace
    Binary used for msql2mysql. Utility program to change strings in place in files or on stdin. Uses a finite state machine to match longer strings first. Can be used to swap strings, for example replace a b b a -- files swaps a and b in the given files.
    safe_mysqld
    Starts the mysqld daemon with some safety features. Restarts on error and has logging of runtime information to a log file.

    12.2 The MySQL table check, optimise and repair program

    You can get all options for isamchk with isamchk --help.

    For information about how to use isamchk to repair a crashed table: See section 8.4 How to repair tables.

    12.2.1 isamchk memory use

    isamchk doesn't use any more memory than you define with the -O options. The default is to use only about 3M to fix things. By using bigger values you can get isamchk to operate faster. Using -O sortbuffer=16M should probably be enough for most cases.

    But isamchk uses temporary files in TMPDIR. If TMPDIR points to a memory file system you may easily get out of memory errors.

    12.2.2 Getting low level table information

    To get a description/statistics from a table use the methods below. We will explain some of the information in more detail later.

    isamchk -d table_name
    isamchk in 'describe mode'. If one uses --skip-locking isamchk may report an error for a table that is updated while isamchk runs, but there isn't any risk of destroying data.
    isamchk -d -v table_name
    A little more verbose.
    isamchk -eis table_name
    Shows only the most important information from a table. It is slow since it must read the whole table.
    isamchk -eiv table_name
    Same as above but tells you what it is being done.

    Example of isamchk -d output:

    ISAM file:     company.ISM
    Data records:           1403698  Deleted blocks:         0
    Recordlength:               226
    Record format: Fixed length
    
    table description:
    Key Start Len Index   Type
    1   2     8   unique  double
    2   15    10  multip. text packed stripped
    3   219   8   multip. double
    4   63    10  multip. text packed stripped
    5   167   2   multip. unsigned short
    6   177   4   multip. unsigned long
    7   155   4   multip. text
    8   138   4   multip. unsigned long
    9   177   4   multip. unsigned long
        193   1           text
    

    Example of isamchk -d -v output:

    ISAM file:     company.ISM
    Isam-version:  2
    Creation time: 1996-08-28 11:44:22
    Recover time:  1997-01-12 18:35:29
    Data records:           1403698  Deleted blocks:              0
    Datafile: Parts:        1403698  Deleted data:                0
    Datafilepointer (bytes):      3  Keyfile pointer (bytes):     3
    Max datafile length: 3791650815  Max keyfile length: 4294967294
    Recordlength:               226
    Record format: Fixed length
    r
    table description:
    Key Start Len Index   Type                      Root Blocksize Rec/key
    1   2     8   unique  double                15845376      1024       1
    2   15    10  multip. text packed stripped  25062400      1024       2
    3   219   8   multip. double                40907776      1024      73
    4   63    10  multip. text packed stripped  48097280      1024       5
    5   167   2   multip. unsigned short        55200768      1024    4840
    6   177   4   multip. unsigned long         65145856      1024    1346
    7   155   4   multip. text                  75090944      1024    4995
    8   138   4   multip. unsigned long         85036032      1024      87
    9   177   4   multip. unsigned long         96481280      1024     178
        193   1           text
    

    Example of isamchk -eis output:

    Checking ISAM file: company.ISM
    Key:  1:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
    Key:  2:  Keyblocks used:  98%  Packed:   50%  Max levels:  4
    Key:  3:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
    Key:  4:  Keyblocks used:  99%  Packed:   60%  Max levels:  3
    Key:  5:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
    Key:  6:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
    Key:  7:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
    Key:  8:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
    Key:  9:  Keyblocks used:  98%  Packed:    0%  Max levels:  4
    Total:    Keyblocks used:  98%  Packed:   17%
    
    Records:          1403698    M.recordlength:     226   Packed:             0%
    Recordspace used:     100%   Empty space:          0%  Blocks/Record:   1.00
    Recordblocks:     1403698    Deleteblocks:         0
    Recorddata:     317235748    Deleted data:         0
    Lost space:             0    Linkdata:             0
    
    User time 1626.51, System time 232.36
    Maximum resident set size 0, Integral resident set size 0
    Non physical pagefaults 0, Physical pagefaults 627, Swaps 0
    Blocks in 0 out 0, Messages in 0 out 0, Signals 0
    Voluntary contexts switches 639, Involuntary contexts switches 28966
    

    Example of isamchk -eiv output:

    Checking ISAM file: company.ISM
    Data records: 1403698   Deleted blocks:       0
    - check file-size
    - check delete-chain
    index  1:
    index  2:
    index  3:
    index  4:
    index  5:
    index  6:
    index  7:
    index  8:
    index  9:
    No recordlinks
    - check index reference
    - check data record references index: 1
    Key:  1:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
    - check data record references index: 2
    Key:  2:  Keyblocks used:  98%  Packed:   50%  Max levels:  4
    - check data record references index: 3
    Key:  3:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
    - check data record references index: 4
    Key:  4:  Keyblocks used:  99%  Packed:   60%  Max levels:  3
    - check data record references index: 5
    Key:  5:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
    - check data record references index: 6
    Key:  6:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
    - check data record references index: 7
    Key:  7:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
    - check data record references index: 8
    Key:  8:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
    - check data record references index: 9
    Key:  9:  Keyblocks used:  98%  Packed:    0%  Max levels:  4
    Total:    Keyblocks used:   9%  Packed:   17%
    
    - check records and index references
    [LOTS OF ROW NUMBERS DELETED]
    
    Records:          1403698    M.recordlength:     226   Packed:             0%
    Recordspace used:     100%   Empty space:          0%  Blocks/Record:   1.00
    Recordblocks:     1403698    Deleteblocks:         0
    Recorddata:     317235748    Deleted data:         0
    Lost space:             0    Linkdata:             0
    
    User time 1639.63, System time 251.61
    Maximum resident set size 0, Integral resident set size 0
    Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0
    Blocks in 4 out 0, Messages in 0 out 0, Signals 0
    Voluntary contexts switches 10604, Involuntary contexts switches 122798
    

    Here are the data file sizes of the table used above.

    -rw-rw-r--   1 monty    tcx     317235748 Jan 12 17:30 company.ISD
    -rw-rw-r--   1 davida   tcx      96482304 Jan 12 18:35 company.ISM
    

    Explanations for the things isamchk prints:

    ISAM file
    Name of isam file.
    Isam-version
    Version of isam format. Currently always 2.
    Creation time
    When was the data file created.
    Recover time
    When was the index/data file last reconstructed.
    Data records
    How many records/rows.
    Deleted blocks
    How many deleted blocks still have reserved space. See section 8.4 How to repair tables.
    Datafile: Parts
    For dynamic record format this shows how many data blocks there are. For an optimised table without splits this is the same as Data records.
    Deleted data
    How many bytes of non-reclaimed deleted data.
    Datafile pointer
    Size (in bytes) of the datafile pointer. It is usually 2, 3, 4 or 5 bytes. Most tables manage with 2 bytes but this cannot be controlled from MySQL yet. For fixed tables this is a record address. For dynamic tables this is a byte address.
    Keyfile pointer
    How many bytes has the datafile pointer. It is usually 1, 2 or 3 bytes. Most tables manage with 2 bytes but this is calculated automatically by MySQL. It is always a block address.
    Max datafile length
    How long (in bytes) can the table's data file (.ISD) get.
    Max keyfile length
    How long (in bytes) can the table's key file (.ISM) get.
    Recordlength
    How much space does each record/row take.
    Record format
    Which format does each record/row have. This example uses Fixed length.
    table description
    A list of all keys in the table. For each key some low level information is presented.
    Key
    This key's number.
    Start
    Where in the record/row does this index-part start.
    Len
    How long is this index part. For packed numbers this should always be the full length of the column. For strings it may be shorter than the full length.
    Index
    unique or multip.. If one value can exist multiple times in this index.
    Type
    What data-type does this index part have. This is a C data-type with the options packed, stripped or empty.
    Root
    Address of the root index block.
    Blocksize
    The size of each index block. This is by default 1024 but may be changed at compile time.
    Rec/key
    This is a statistical value used by the optimiser. It tells how many records there are per value for this key. A unique key always has a value of 1. This may be updated after a table is loaded (or greatly changed) with isamchk -a. If this is not updated at all, a default value of 30 is given.
    The 9th key is a multi-part key with two parts.
    Keyblocks used
    What percentage of the keyblocks are used. Since this table has just been reorganised with isamchk the values are very high (very near theoretical maximum).
    Packed
    MySQL tries to pack keys with a common suffix. This can only be used for CHAR/VARCHAR/DECIMAL keys. For long strings like names, this can significantly reduce the space used. In the above example the 4th key is 10 characters long and gets a 60% reduction in space.
    Max levels
    How deep is the btree for this key. Large tables with long keys get high values.
    Records
    How many rows does the table have.
    M.recordlength
    Average recordlength. For fixed tables this is the recordlength.
    Packed
    MySQL strips spaces from the end of strings. What percentage did we save by doing this.
    Recordspace used
    What percentage of the datafile is used.
    Empty space
    What percentage of the datafile is unused.
    Blocks/Record
    How many blocks are there per record. This is always 1 for fixed format tables. This value should stay as close to 1.0 as possible. If it gets too big you can reorganise the table with isamchk. See section 8.4 How to repair tables.
    Recordblocks
    How many blocks are used. For fixed format, this is the same as the number of records.
    Deleteblocks
    How many blocks are deleted.
    Recorddata
    How many bytes of actual user data there are in the datafile.
    Deleted data
    How many bytes of deleted data there are in the datafile.
    Lost space
    If a record is updated to a shorter length, some space is lost. This is the sum of all such losses.
    Linkdata
    When the dynamic format is used, blocks are linked with pointers (length 4-7 bytes). This is the sum of all such pointers.

    12.3 The MySQL compressed read only table generator

    pack_isam is an extra that you get when ordering more than 10 licenses or extended support. Since pack_isam is distributed binary only, pack_isam is only available on some platforms.

    Of course, all future updates to pack_isam is included in the price. pack_isam may at some time be included as standard when we get some kind of turnover for MySQL.

    pack_isam works by compressing each column in the table separately. The information needed to decompress is read into memory when the table is opened. This gives a much better performance when accessing individual records as one only has to uncompress exactly one record, not a much larger disk block like when using Stacker on MSDOS.

    MySQL uses memory mapping (mmap) on compressed tables and falls back to normal read/write file usage if mmap doesn't work.

    Usually, pack_isam packs the datafile 40-70%.

    There is currently two limitations with pack_isam:

    Fixing these limitations is on our TODO but with low priority.

    pack_isam options:

    > pack_isam --help
    pack_isam Ver 5.0 for SOLARIS 2.5 on SPARCstation
    Copyright (C) 1994-1997 TcX AB & Monty Program KB & Detron HB.
    This is not free software. You must have a license to use this program
    This software comes with ABSOLUTELY NO WARRANTY
    
    Pack an ISAM-database to take much smaller space
    Keys are not updated, one must run isamchk -rq on datafile afterwards
    Usage: pack_isam [OPTIONS]
    
      -b, --backup          Make a backup of the table as table_name.OLD
      -f, --force           Force packing of table even if it's gets bigger or
                            tempfile exists.
      -j, --join=#          Join all given tables into table.
                            All tables MUST be identical.
      -p, --packlength=#    Force storage size of recordlength (1,2 or 3)
      -s, --silent          Be more silent.
      -t, --test            Don't pack table only test packing it
      -v, --verbose         Write info about progress and packing result
      -w, --wait            Wait and retry if table is in use
      -T, --tmp_dir=#       Use temporary directory to store temporary table
      -#, --debug=...       output debug log. Often this is 'd:t:o,filename`
      -?, --help            display this help and exit
      -V, --version         output version information and exit
    

    Typical run:

    (/my/monty/tmp) ls -l station.*
    -rw-rw-r--   1 monty    my         994128 Apr 17 19:00 station.ISD
    -rw-rw-r--   1 monty    my          53248 Apr 17 19:00 station.ISM
    -rw-rw-r--   1 monty    my           5767 Apr 17 19:00 station.frm
    
    (/my/monty/tmp) isamchk -dvv station
    
    ISAM file:     station
    Isam-version:  2
    Creation time: 1996-03-13 10:08:58
    Recover time:  1997-02-02  3:06:43
    Data records:              1192  Deleted blocks:              0
    Datafile: Parts:           1192  Deleted data:                0
    Datafilepointer (bytes):      2  Keyfile pointer (bytes):     2
    Max datafile length:   54657023  Max keyfile length:   33554431
    Recordlength:               834
    Record format: Fixed length
    
    table description:
    Key Start Len Index   Type                       Root  Blocksize    Rec/key
    1   2     4   unique  unsigned long              1024       1024          1
    2   32    30  multip. text                      10240       1024          1
    
    column Start Length Type
    1     1     1                                         
    2     2     4                                         
    3     6     4                                         
    4     10    1                                         
    5     11    20                                        
    6     31    1                                         
    7     32    30                                        
    8     62    35                                        
    9     97    35                                        
    10    132   35                                        
    11    167   4                                         
    12    171   16                                        
    13    187   35                                        
    14    222   4                                         
    15    226   16                                        
    16    242   20                                        
    17    262   20                                        
    18    282   20                                        
    19    302   30                                        
    20    332   4                                         
    21    336   4                                         
    22    340   1                                         
    23    341   8                                         
    24    349   8                                         
    25    357   8                                         
    26    365   2                                         
    27    367   2                                         
    28    369   4                                         
    29    373   4                                         
    30    377   1                                         
    31    378   2                                         
    32    380   8                                         
    33    388   4                                         
    34    392   4                                         
    35    396   4                                         
    36    400   4                                         
    37    404   1                                         
    38    405   4                                         
    39    409   4                                         
    40    413   4                                         
    41    417   4                                         
    42    421   4                                         
    43    425   4                                         
    44    429   20                                        
    45    449   30                                        
    46    479   1                                         
    47    480   1                                         
    48    481   79                                        
    49    560   79                                        
    50    639   79                                        
    51    718   79                                        
    52    797   8                                         
    53    805   1                                         
    54    806   1                                         
    55    807   20                                        
    56    827   4                                         
    57    831   4
    
    Compressing station.ISD: (1192 records)
    - Calculating statistics
                
    normal:     20  empty-space:      16  empty-zero:        12  empty-fill:  11
    pre-space:   0  end-space:        12  intervall-fields:   5  zero:         7
    Original trees:  57  After join: 17
    - Compressing file
    87.14%     
    
    (/my/monty/tmp) ls -l station.*
    -rw-rw-r--   1 monty    my         127874 Apr 17 19:00 station.ISD
    -rw-rw-r--   1 monty    my          55296 Apr 17 19:04 station.ISM
    -rw-rw-r--   1 monty    my           5767 Apr 17 19:00 station.frm
    
    (my/monty/tmp) isamchk -dvv station
    
    ISAM file:     station
    Isam-version:  2
    Creation time: 1996-03-13 10:08:58
    Recover time:  1997-04-17 19:04:26
    Data records:              1192  Deleted blocks:              0
    Datafile: Parts:           1192  Deleted data:                0
    Datafilepointer (bytes):      3  Keyfile pointer (bytes):     1
    Max datafile length:   16777215  Max keyfile length:     131071
    Recordlength:               834
    Record format: Compressed
    
    table description:
    Key Start Len Index   Type                       Root  Blocksize    Rec/key
    1   2     4   unique  unsigned long             10240       1024          1
    2   32    30  multip. text                      54272       1024          1
    
    Field Start Length Type                         Huff tree  Bits
    1     1     1      constant                             1     0
    2     2     4      zerofill(1)                          2     9
    3     6     4      no zeros, zerofill(1)                2     9
    4     10    1                                           3     9
    5     11    20     table-lockup                         4     0
    6     31    1                                           3     9
    7     32    30     no endspace, not_always              5     9
    8     62    35     no endspace, not_always, no empty    6     9
    9     97    35     no empty                             7     9
    10    132   35     no endspace, not_always, no empty    6     9
    11    167   4      zerofill(1)                          2     9
    12    171   16     no endspace, not_always, no empty    5     9
    13    187   35     no endspace, not_always, no empty    6     9
    14    222   4      zerofill(1)                          2     9
    15    226   16     no endspace, not_always, no empty    5     9
    16    242   20     no endspace, not_always              8     9
    17    262   20     no endspace, no empty                8     9
    18    282   20     no endspace, no empty                5     9
    19    302   30     no endspace, no empty                6     9
    20    332   4      allways zero                         2     9
    21    336   4      allways zero                         2     9
    22    340   1                                           3     9
    23    341   8      table-lockup                         9     0
    24    349   8      table-lockup                        10     0
    25    357   8      allways zero                         2     9
    26    365   2                                           2     9
    27    367   2      no zeros, zerofill(1)                2     9
    28    369   4      no zeros, zerofill(1)                2     9
    29    373   4      table-lockup                        11     0
    30    377   1                                           3     9
    31    378   2      no zeros, zerofill(1)                2     9
    32    380   8      no zeros                             2     9
    33    388   4      allways zero                         2     9
    34    392   4      table-lockup                        12     0
    35    396   4      no zeros, zerofill(1)               13     9
    36    400   4      no zeros, zerofill(1)                2     9
    37    404   1                                           2     9
    38    405   4      no zeros                             2     9
    39    409   4      allways zero                         2     9
    40    413   4      no zeros                             2     9
    41    417   4      allways zero                         2     9
    42    421   4      no zeros                             2     9
    43    425   4      allways zero                         2     9
    44    429   20     no empty                             3     9
    45    449   30     no empty                             3     9
    46    479   1                                          14     4
    47    480   1                                          14     4
    48    481   79     no endspace, no empty               15     9
    49    560   79     no empty                             2     9
    50    639   79     no empty                             2     9
    51    718   79     no endspace                         16     9
    52    797   8      no empty                             2     9
    53    805   1                                          17     1
    54    806   1                                           3     9
    55    807   20     no empty                             3     9
    56    827   4      no zeros, zerofill(2)                2     9
    57    831   4      no zeros, zerofill(1)                2     9
    

    13 Adding functions to MySQL

    13.1 Adding new functions to MySQL

    You have two ways to add new functions to mysql:

    To add a new native MySQL function (like SOUNDEX()), you only have to do the following:

    1. Add one line in sql_lex.cc defining the function name in the sql_functions array.
    2. Add two lines in sql_yacc.y. On defines the preprocessor symbol yacc can define (this should be added at the beginning of the file). Then define the function parameters and create an 'item' with these parameters. Check, for example, all occurrences of SOUNDEX in sql_yacc.y
    3. In item_func.h declare a class inheriting from Item_num_func or Item_str_func depending on whether your function returns a number or a string.
    4. In `item_func.cc' add: double *Item_func_newname::val() If you are defining a number function or String *Item_func_newname::Str(String *str) if you are defining a string function.
    5. You should probably also define the following function: void Item_func_newname::fix_length_and_dec() This should at least calcutate max_length based on the given arguments. max_length is the maximum number of chars the function may return. If the function can't return a NULL, one should set maybe_null = 0.

    About string functions:

    1. For string functions the String *str argument provides a string buffer that may be used to hold the result.
    2. A string function should return the string that holds the result.
    3. All current string functions try to avoid allocating any memory unless absolutely necessary!

    14 ODBC

    14.1 Which operating systems does MySQL ODBC support?

    MySQL ODBC is a 32 bit ODBC (2.50) level 0 driver for Windows95 and NT. We hope somebody will port it to Windows 3.x.

    14.2 How should I report problems with MySQL ODBC?

    We have only tested ODBC with Admndemo, some C programs, Msquery and Excel.

    To give some light about any problem we would like to have the log file from the ODBC manager (the log you get when requesting logs from ODBCADMIN) and a MYODBC log.

    To get a MYODBC log, please add 4 to the 'Options' argument on the MyODBC connect/configure screen.

    The log will be written to file `c:\tmp\myodbc.log'.

    14.3 Programs known to work with MyODBC

    Most programs should work with MyODBC, but we we have tested these ourselves or got a confirmation from some user that is works:

    Program
    Comment
    Access
    To make Access work:
    Excel
    Works. Some tips
    odbcadmin
    Test program for ODBC.
    Delphi
    One must use DBE 3.2 or newer.
    C++Builder
    Tested with BDE 3.0. The only known problem is that when the table schema changes, query fields are not updated. BDE however does not seem to recognise primary keys, only the index PRIMARY, though this has not been a problem.

    14.4 How do I fill in the various fields in the ODBC administrator program?

    There are three possibilities for specifying the server name on Windows95:

    1. Use the IP address of the server.
    2. Add a file lmhosts with the following info: ip hostname For example:
      194.216.84.21 my
      
    3. Configure the PC to use DNS.

    Example of how to fill in the 'ODBC setup'.

    Windows DNS name:   test
    Description:        This is my test database
    MySql Database:     test 
    Server:             194.216.84.21
    User:               monty
    Password:           my_password
    Port:
    

    The 'Windows DNS name' is any name that is unique in your windows ODBC setup.

    When you fill in the values in the ODBC setup, these will be used as default values when prompting for a Driver connect. You don't have to give 'server', 'user' or 'password' in the ODBC setup screen.

    When connecting to an ODBC source you have the option to change the server, user, password and port.

    If port is not given the default port (3306) is used.

    14.5 How to get the value of a AUTO_INCREMENT column in ODBC

    A common problem is how to get the value of an automatically-generated id from an INSERT. With ODBC you can do something like this:

    INSERT INTO foo (auto,text) VALUES(NULL,'text')
    select LAST_INSERT_ID()
    

    or if you are just going to insert in into another table:

    INSERT INTO foo (auto,text) VALUES(NULL,'text')
    INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text')
    

    For the benefit of some ODBC applications (at least Delphi and Access) the following query can be used to find a newly inserted row:

    SELECT * from table_name WHERE auto_increment_column IS NULL
    

    15 Problems and common errors

    15.1 Some common errors when using MySQL

    15.1.1 MySQL server has gone away error

    The most common reason for the MySQL server has gone away error is that the server closed the connection because of a timeout. By default the server closes the connection after 8 hours if nothing has happened.

    If you have a script you just have to issue the query again for the client to do a automatically reconnection.

    One normally can get these error codes in this case: (Which one you get is OS dependent)

    CR_SERVER_GONE_ERROR The client couldn't send a question to the server.
    CR_SERVER_LOST The client didn't get an error when writing to the server but it didn't get full answer (or any answer) to the question.
    You can also get this error if you send a wrong or too big query to the server. If mysqld gets a wrong block it assumes that something has gone wrong with the client and closes the connection. If you need big queries, if you are for example working with big blobs, you can increase the query limit by starting mysqld with the option -O max_query_size=# (default 65536). The extra memory is allocated by demand, so mysqld will only use more memory when you issue a big query or when mysqld must return a big result row!

    15.1.2 Can't connect to local MySQL server error

    A MySQL client can connect to the mysqld server in two different ways: Unix sockets, which connects trough a file in the file system, (default `/tmp/mysqld.sock'), or TCP/IP which connects trough a port number. Unix sockets are faster than TCP/IP but can only be used when connection to a server on the same computer. Unix sockets are used if one doesn't specify a hostname or if one specifies the special hostname 'localhost'.

    Here follows some reasons to the error Can't connect to local MySQL server.

    15.1.3 Out of memory errors

    If you do a query and get something like the following error:

    mysql: Out of memory at line 42, 'malloc.c'
    mysql: neaded 8136 byte (8k), memory in use: 12481367 bytes (12189k))
    ERROR 2008: MySQL client got out of memory                
    

    Note that the error refers to the MySQL client. The reason for this error is simply that the client does not have enough memory to store the whole result.

    15.1.4 Packet to large

    When the client gets a block bigger that net_buffer_length it issues a Packet too large error.

    If the mysql client is used you may set a bigger buffer by starting the client with mysql --set-variable= net_buffer_length=1m.

    15.1.5 The table is full

    This error occurs when an in-memory temporary table gets bigger than tmp_table_size. To avoid this problem one can increase the -O tmp_table_size=# option to mysqld or use the SQL option SQL_BIG_TABLES before you do the problem query. See section 7.20 SET OPTION syntax.

    One can also start mysqld with the option: --big-tables. This is exactly the same as using SQL_BIG_TABLES for all queries.

    15.1.6 Commands out of sync in client

    If you get Commands out of sync; You can't run this command now in youe client code, you are calling clients functions in the wrong order!

    This can for example happen if you are using 'mysql_use_result()' and try to execute a new query before you have done a 'mysql_free_result()'. This can also happen if you try to execute two queries, that returns data, without a mysql_use_result() or mysql_store_result() between.

    15.1.7 Removing user

    If you get the following error:

    Found wrong password for user: 'XXX@localhost'; Removing user

    This means that mysqld on start or 'reload' found and entry in the user table that didn't have a legal password.

    The row is not removed from the user table, it's only not used by the permission system

    Possible causes and fixes for this:

    15.2 How does MySQL handle a full disk

    On a disk full condition MySQL does the following:

    15.3 Where MySQL stores temporary files

    MySQL uses the value of the TMPDIR environment variable as where to store temporary files. If you don't have TMPDIR set, MySQL will use the system default, normally /tmp or /usr/tmp. If your TMPDIR is too small, you should edit safe_mysqld to set TMPDIR to point at some directory where you have enough space! One can also use the --tmpdir option for mysqld to set the temporary directory.

    MySQL creates all temporary files as 'hidden files'. This ensures that the temporary files will be removed if mysqld is terminated. The 'bad' side with using hidden files is that you will not see a big temporary file that fills up a too small temporary disk.

    When sorting (ORDER BY or GROUP BY) MySQL normally uses one or two temporary files. The maximum disk-space needed is (length_of_what_is_sorted + sizeof(database_pointer)) * number_of_matched_rows * 2. sizeof(database_pointer) is usually 4 but may grow in the future for really big tables.

    For some SELECT queries will also create temporary SQL tables. These are not hidden and will have a name that starts with SQL_.

    ALTER TABLE will create a temporary table in the same directory as the original table.

    15.4 Access denied? error

    See section 6.2 How does the privilege system work?. And especially See section 6.7 Why do I get this Access denied? error?.

    15.5 How to run MySQL as a normal user

    mysqld (the MySQL server) can be started and run by any user. In order to change mysqld to run as user USER, you'd have to the following:

    You don't have to do anything to safe_mysqld to not run as root.

    At this point, your mysqld process should be running fine and dandy as user 'USER'. One thing hasn't changed though - the access permissions. By default (right after running the permissions table install script), only user 'root' has access permission to the database. Unless you have changed that, it's still true. This shouldn't stop you from accessing MySQL when you're logged in under a user other than root, just specify -u root to the client program. Note that accessing MySQL as root, by supplying -u root in the command line, doesn't have ANYTHING to do with MySQL running as root, as a user or as anyone else. The access permissions and userbase of MySQL are completely separate from the UNIX users. The only connection to the UNIX users is if you don't use the -u option to clients. In this case the client will try to login into MySQL with your UNIX login name. If your UNIX box itself isn't secured, you should probably at least put a password on the root users in the MySQL access tables, since any johndoe user can run 'mysql -u root dbname' and do whatever he likes.

    15.6 Problems with file permissions

    If you have problems with file permissions, for example when creating a table mysql gives: "ERROR: Can't find file: 'path/with/filename.frm' (Errcode: 13)", then you might have the wrong value for environment variable UMASK. Default umask is 0664. Fix:

    UMASK=432
    export UMASK
    ./bin/safe_mysqld
    

    15.7 File not found

    If you get ERROR '...' not found (Errcode: 23) or any other error with errcode 23 from MySQL this means that you haven't allocated enough file descriptors for MySQL. perror # will give you the error message in a more readable form.

    There is a commented line ulimit -n 256 in `safe_mysqld'. You can remove this comment and of course increase or decrease the value if you want. You can also make the table cache smaller with: safe_mysqld -O table_cache=32 (the default is 64).

    15.8 Problems using DATE columns

    The format of DATE is 'YYYY-MM-DD'. According to ANSI SQL nothing else is allowed. One should use this format to update or in the WHERE clause, ie select * from table_1 where date >= '1997-05-05';

    As a convenience, MySQL automatically converts the date to a number if used in a number context. It is also smart enough to allow a 'relaxed' string form when updating and in a WHERE with a compare to a TIMESTAMP, DATE or a DATETIME column.

    The special date '0000-00-00' can be stored and retrieved as 0000-00-00. When using a '0000-00-00' date trough MyODBC it will automatically be converted to NULL in MyODBC 2.50.12 and above, because ODBC can't handle this kind of dates.

    This means that the following works:

    insert into table_1 (idate) values (19970505) ;
    insert into table_1 (idate) values ('19970505') ;
    insert into table_1 (idate) values ('97-05-05');
    insert into table_1 (idate) values ('1997.05.05');
    insert into table_1 (idate) values ('1997 05 05');
    insert into table_1 (idate) values ('0000-00-00');
    
    select idate from table_1 where idate >= '1997-05-05';
    select idate from table_1 where idate >= 19970505;
    select mod(idate,100)1 from table_1 where idate >= 19970505;
    select idate from table_1 where idate >= '19970505';
    

    The following will not work:

    select idate from table_1 where strcmp(idate,'19970505')=0;
    Because '19970505' is compared as a string to '1997-05-05'.
    

    Note that MySQL does no checking if the date is correct. If you store a wrong date, like 1998-2-31, the wrong date will be stored. If the date is totally impossible a 0 is stored in the date field.. This is mainly a speed issue and we think it's up to the application to check the dates and not the server.

    15.9 Case sensitivity in searches.

    By default a MySQL column is case insensitive (although there are some character sets that never are case insensitive). That means that if you search with column like 'a%'; you will get all columns that start with A or a. If you want to make this search case sensitive use something like INDEX(column, "A")=0 to check a prefix. Or STRCMP(column, "A") = 0 if the whole string should be the same.

    Simple compare operations >=, >, = , < , <=, sorting and grouping are done on the characters 'sort value'. Characters with the same sort value (like E, e and é) are treated as the same character!

    LIKE comparing is done on the uppercase value of each character (E == e but E <> é)

    If you want column to always be treated in a case sensitive manner, declare it as BINARY. See section 7.6 CREATE TABLE syntax.

    If you are using Chinese data in the so-called big5 encoding you want to make all character columns BINARY. This works because the sorting order of big5 encoding characters is based on the order of ascii codes.

    15.10 Problems with NULL

    A common confusion for newcomers to SQL is that they think that NULL is the same things as a empty string ". This is not the case! For example the following statements are compleatly different:

    INSERT INTO my_table (name,phone) values ("my",NULL);
    INSERT INTO my_table (name,phone) values ("my","");
    

    The first inserts a NULL value into address and the second inserts an empty string into address. The first can be regarded as 'phone number is not known' and the second can be regarded as 'she has no phone'.

    In SQL NULL is a value that is always false when comparing to all other values, even NULL. An expression that contains NULL will always produce a NULL value if nothing else is mentioned. All following columns return NULL:

    SELECT NULL,1+NULL,CONCAT('Invisible',NULL);
    

    If you want to search for a column that has NULL, you must use the IS NULL test. The following shows how to find the NULL phone number and the empty phone number:

    SELECT * from my_table where phone IS NULL;
    SELECT * from my_table where phone = "";
    

    In MySQL, as in many other SQL servers, you can't index columns that can have NULL values. You have to declare such columns NOT NULL.

    When reading data with LOAD DATA INFILE empty columns are updated with ". If you want a NULL value in a column you should use NULL or \N in the textfile.

    When using ORDER BY NULL values are presented first (if not using DESC). When using GROUP BY all NULL values are regarded as equal.

    To help with NULL handling you can use the functions: IS NULL, IS NOT NULL and IFNULL()

    16 Solving some common problems with MySQL

    16.1 Database replication

    The most general way to do database replication is using the update log. See section 9.2 The Update log. This requires that one database acts as a master (all data changes are done here) and one or more others as slaves. To update a slave just run mysql < update_log.

    If you never do deletes, you can use timestamps.

    It is possible to make a two-way updating system using both the update log (for deletes) and timestamps (on both sides). But in that case you must be able to handle confilicts when the same data has been changed in both ends. You probably want to keep the old version to help with deciding what has been updated.

    Because replication in this case is done with SQL statements, you should not use the following functions in statements that updates the database, because they may return a different value in the other MySQL server:

    All time functions are safe to use as the timestamp is sent to the mirror if needed. LAST_INSERT_ID() is also safe to use.

    16.2 Backup of databases

    Since MySQL tables are stored as files it is easy to do a backup. To get a consistent backup, do a LOCK TABLES on the relevant tables. See section 7.19 LOCK TABLES syntax. You only need a read lock so other threads can continue to query the tables while making a copy of the files in the database directory. Or if you want to make a SQL level backup you can use SELECT INTO OUTFILE.

    Another way is to use the mysqldump program.

    When you have to restore something (if isamchk -r can't restore all data as it can in 99.9% of all cases):

    The ls in the last command is done to get all log files in the right order

    You can also do selective backups with select * into outfile from table and restore with LOAD DATA FROM INFILE 'file_name' REPLACE .... To avoid duplicate records you need a PRIMARY KEY in the table. The REPLACE means that if there is a 'duplicate index' conflict when inserting new records the old record will be replaced with the new one.

    17 MySQL client tools and API's

    17.1 MySQL C API

    The C API is distributed with MySQL. It is included in the libmysqlclinet library. It allows C programs to access a database.

    Most of the other client APIs (all except Java) use this library to connect. So for example you can use the same environment variables.

    The client has a maximum communication buffer size. This is automatically increased up to the maximum size (the default for this is 512Kb). As buffers are increased (but not decreased until close) on demand, it will not take any resources if one increases this. This size check is mostly a check for erroneous queries and communication packets.

    The communication buffer must be big enough to contain a single SQL statement and one row of returned data (but of course not at the same time). Each thread's communication buffer is dynamically enlarged to handle any row or query up to the imposed limit.

    So if you have BLOBs that contains data up to 16M you must have at least 16M as your communication buffer limit. See section 10.1 How does one change the size of MySQL buffers?.

    MySQL shrinks each communication buffer to net_buffer_length after each query.

    mysql_affected_rows Retrieves the number of affected rows by the last UPDATE, DELETE or INSERT.
    mysql_close Closes a server connection.
    mysql_connect Establish a connection to a MySQL server.
    mysql_create_db Create a database.
    mysql_data_seek Seeks to an arbitrary row in a query result set.
    mysql_drop_db Drop a database.
    mysql_eof Determine if last row has been read.
    mysql_escape_string Escape a string for a SQL statement. See section 7.1 Literals. How do you write strings and numbers?
    mysql_error The error message from last MySQL function.
    mysql_fetch_field Find out what type a table field is.
    mysql_fetch_lengths Returns the length of all columns in a query result set.
    mysql_fetch_row Fetch the 'next' row in the query result.
    mysql_field_seek Put the column cursor on column number field.
    mysql_free_result Free memory used to store a query result.
    mysql_get_client_info Return version information for the current client library.
    mysql_get_host_info Returns name of server host.
    mysql_get_proto_info Get protocol version used by connection.
    mysql_get_server_info Returns the version number of the server.
    mysql_insert_id Returns ID generated for a AUTO_INCREMENT field.
    mysql_list_dbs Return matching database names.
    mysql_list_fields Return matching field names.
    mysql_list_processes Get a list of the current server threads.
    mysql_list_tables Return matching table names.
    mysql_num_fields Return the number of columns in a result set.
    mysql_num_rows Returns the number of rows in result set.
    mysql_query Executes a SQL query.
    mysql_real_query Executes a SQL query with length information.
    mysql_reload Reload the user permissions table in the server.
    mysql_select_db Connect to a database.
    mysql_shutdown Shut down the database server.
    mysql_stat Return server status in a string.
    mysql_store_result Reads a result set to the client.
    mysql_use_result Initiate a dynamic result set for each row. This uses much less memory than mysql_store_result() but will put more strain on the server.
    int mysql_affected_rows(MYSQL *mysql)
    Retrieves the number of affected rows by the last UPDATE, DELETE or INSERT.
    void mysql_close(MYSQL *mysql)
    Closes a server connection.
    MYSQL *mysql_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd)
    Establish a connection to a MySQL server.
    int mysql_create_db(MYSQL *mysql, const char *db)
    Create a database.
    void mysql_data_seek(MYSQL_RES *res, uint offset)
    Seeks to an arbitrary row in a query result set.
    int mysql_drop_db(MYSQL *mysql, const char *db)
    Drop a database.
    int mysql_eof(MYSQL_RES *)
    Determine if last row has been read.
    char *mysql_error(MYSQL *mysql)
    The error message from last MySQL function.
    uint mysql_escape_string(char *to,char *from,uint length)
    Escape a string for a SQL statement.
    MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *handle)
    Find out what type a table field is. When querying for the length of a BLOB without retreving a result, MySQL returns the 'default blob length', which is 8192, when doing a select on the table. When one retrevies a result, column_object->max_length contains the real max_length for the specific query. The 8192 size is chosen because MySQL doesn't know the max length for the BLOB. This should be made configurable sometime.
    unsigned int *mysql_fetch_lengths(MYSQL_RES *mysql)
    Returns the length of all columns in a query result set.
    MYSQL_ROW mysql_fetch_row(MYSQL_RES *mysql)
    Fetch the 'next' row in the query result.
    void mysql_field_seek(MYSQL_RES *result, int field)
    Put the column cursor on column number field.
    void mysql_free_result(MYSQL_RES *result)
    Free memory used to store a query result. Must be called after you are finished with a result set..
    char *mysql_get_client_info(void)
    Return version information for the current client library.
    char *mysql_get_host_info(MYSQL *mysql)
    Returns name of server host.
    int mysql_get_proto_info(MYSQL *mysql)
    Get protocol version used by connection.
    char *mysql_get_server_info(MYSQL *mysql)
    Returns the version number of the server.
    unsigned long mysql_insert_id(MYSQL *mysql)
    Returns ID generated for AUTO_INCREMENT field. As the API returns a unsigned long one can't use this with BIGINT columns. In this case one has to use the LAST_INSERT_ID() SQL function.
    MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild)
    Return matching database names.
    MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char *wild)
    Return matching field names.
    MYSQL_RES *mysql_list_processes(MYSQL *mysql)
    Get a list of the current server threads.
    MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char *wild)
    Return matching table names.
    int mysql_num_fields(MYSQL_RES *result)
    Return the number of columns in a result set.
    int mysql_num_rows(MYSQL_RES *result)
    Returns the number of rows in result set.
    int mysql_query(MYSQL *mysql, const char *query)
    Executes a SQL query.
    int mysql_real_query(MYSQL *mysql, const char *query, uint length)
    Executes a SQL query with length information.
    int mysql_reload(MYSQL *mysql)
    Reload the user permissions table in the server.
    int mysql_select_db(MYSQL *mysql, const char *db)
    Connect to a database.
    int mysql_shutdown(MYSQL *mysql)
    Shut down the database server.
    char *mysql_stat(MYSQL *mysql)
    Return server status in a string.
    MYSQL_RES *mysql_store_result(MYSQL *mysql)
    Reads a result set to the client.
    MYSQL_RES *mysql_use_result(MYSQL *mysql)
    Initiate a dynamic result set for each row.

    17.1.1 Why is it that after mysql_query() returns success, mysql_store_result() sometimes returns NULL?

    It means one of the following:

    1. Malloc failure.
    2. The data couldn't be read (Error on connection).
    3. The statement was a statement which never returns data (INSERT or UPDATE or DELETE).

    You can always check if the statement should have given a result by checking that mysql_num_fields(MYSQL *) isn't 0. If this is 0 the last query was a statement that does not return values. For example a INSERT or a DELETE.

    You have got an error if mysql_error(MYSQL *) isn't empty!

    17.1.2 What results can I get from a query?

    mysql_affected_rows(MYSQL *) returns the number of affected rows in the last query when doing an INSERT, UPDATE or DELETE. Except, if DELETE is used without a WHERE clause then the table is truncated which is much faster! In this case it returns the number of records affected as zero.

    mysql_insert_id(MYSQL *) returns the given ID of the last query when inserting a row into a table with an AUTO_INCREMENT index. See section 17.1.3 How can I get the unique ID for the last inserted row?

    Some queries, LOAD DATA INFILE... and INSERT INTO ... SELECT ..., UPDATE return additional info. The result is returned in mysql_info(MYSQL *). mysql_info() returns a null pointer if there is no additional information.

    17.1.3 How can I get the unique ID for the last inserted row?

    If you insert a record that has a AUTO_INCREMENT index then you can get the given id with mysql_insert_id(MYSQL *).

    The last value is also stored in the server and can be retrieved with the LAST_INSERT_ID() function.

    You can check if an auto_increment index is used by the following code. This also checks if the query was an INSERT with an auto_increment index.

    if (mysql_error(MYSQL)[0] == 0 &&
        mysql_num_fields(MYSQL_RESULT) == 0 &&
        mysql_insert_id(MYSQL) != 0)
      used_id = mysql_insert_id(MYSQL);
    

    The id that LAST_INSERT_ID() returns is maintained in the server per connection. It will not be botched by another client. It will not even be changed if you update another auto_increment column with a non magic value (that is a not NULL or 0).

    17.1.4 What is the difference between mysql_use_result() and mysql_store_result() modes?

    mysql_use_results reads the result directly from the server without storing it in a temporary table or local buffer. This is somewhat faster and uses much less memory than mysql_store_result. One shouldn't use mysql_use_results if there is a lot of processing being done for each row at the client side, or if the output is sent to a screen on which the user may do a ^S (stop scroll). Doing this would tie up the server and then other threads couldn't update the used tables. One can't use mysql_data_seek mysql_num_rows or issue other queries while using mysql_use_result.

    When using mysql_use_result one must execute mysql_fetch_row() until one gets a NULL pointer back, because else the next query would get results from the previous query. The C API will give the error: Commands out of sync; You can't run this command now, if you forget to do this!

    17.1.5 Problems linking with the C API

    When linking with the C API you can get the following errors on some systems:

    gcc -g -o client test.o -L/usr/local/lib/mysql -lmysqlclient -lsocket -lnsl
    
    Undefined        first referenced
     symbol          in file
    floor            /usr/local/lib/mysql/libmysqlclient.a(password.o)
    ld: fatal: Symbol referencing errors. No output written to client
    

    This means that on your system you have to include the math library (-lm) last in the compile/link line.

    17.1.6 How to make a threadsafe client

    The client is 'almost' thread-safe. The biggest problem is that net.c (the subroutines that read from sockets) are not interrupt safe. This was done with the thought that one may want to have one's own alarm that can break a long read to a server.

    The standard client libraries are not compiled with the thread options.

    To get a thread safe client use the -lmysys, -lstring and -ldbug libraries and net_serv.o that the server uses.

    When using a threaded client one can have great use of the thr_alarm.c routine. If you are using the mysys routines, the only thing one has to remember is to call my_init() first!

    All functions except mysql_connect() are currently thread safe.

    To get connect thread_safe you have to do the following:

    Recompile the client with:

    CPPFLAGS=-DTHREAD_SAFE_CLIENT ./configure ...
    

    You may get some errors because of undefined symbols when linking the standard client as the pthread libraries are not included by default.

    The resulting libmysqld.a library is now thread safe.

    Two threads can't use the same handle (returned by mysql_connect()) at the same time, even if two threads can use different MYSQL_RES handles that were created with mysql_store_result().

    17.2 MySQL Perl API's

    Since DBI/DBD now is the recommended perl interface mysqlperl is not documented here.

    17.2.1 DBI with DBD::mysql

    DBI is a generic interface for many databases. That means that you can write a script what works with many different database engines without change. You need a DataBase Driver (DBD) defined for each database type. For MySQL, this driver is called DBD::mysql.

    For more information on the Perl5 DBI, please visit DBIs web page and read the documentation. For more information on Object Oriented Programming (OOP) as defined in Perl5, see the perl OOP page.

    17.2.1.1 The DBI interface

    Portable DBI methods.

    connect Establish a connection to a database server
    prepare Get a SQL statement ready for execution
    do Prepares and executes a SQL statement
    disconnect Disconnect from the database server
    quote Quote strings/blobs to be inserted
    execute Executes prepared statements
    fetchrow_array fetch the next row as an array of fields.
    fetchrow_arrayref fetch next row as a reference array of fields
    fetchrow_hashref fetch next row as a reference to a hashtable
    fetchall_arrayref Get all data as a array of arrays
    finish finish a statment and let the system free resources
    rows Returns the number of rows affected
    data_sources Return an array of databases available on localhost
    ChopBlanks Shall fetchrow trim spaces
    NUM_OF_PARAMS Number of placeholders in the prepared statement
    NULLABLE Which columns can be NULL
    MySQL specific methods.
    insertid The latest auto_increment value
    is_blob Which column ar BLOBs
    is_key Which columns are keys
    is_num Which columns are numeric
    is_pri_key Which columns are primary keys
    is_not_null Which columns can NOT be NULL. See NULLABLE
    length Maximum theoretically possible column sizes
    max_length Maximum physical present column sizes
    NAME Column names
    NUM_OF_FIELDS Number of fields returned.
    table Table names in returned set
    type All coulumn types
    _CreateDB Create a database
    _DropDB Drop a database. THIS IS DANGEROUS
    connect
    You use the connect method to make a database connection to the data source. The $data_source value should begin with DBI:driver_name:. Example connect methods with the DBD::mysql driver:
    $dbh = DBI->connect("DBI:mysql:$database", $user, $password);
    $dbh = DBI->connect("DBI:mysql:$database:$hostname",
                        $user, $password);
    $dbh = DBI->connect("DBI:mysql:$database:$hostname:$port",
                        $user, $password);
    
    If the username and/or password are undefined, then the DBI will use the values of the DBI_USER, DBI_PASS environment variables respectively. If you don't specify a hostname, then it will default to "localhost". If you don't specify a port, then it defaults to the default mysql port (3306).
    prepare
    Prepare gets a SQL statement ready for execution by the database engine and returns a statement handle ($sth) which invokes the execute method. Example:
    $sth = $dbh->prepare($statement) or die "Can't prepare $statement: 
    $dbh->errstr\n";
    
    do
    The "do" method prepares and executes a SQL statement and returns the number of rows effected. This method is generally used for "non-select" statements which can not be prepared in advance (driver limitation) or which do not need to executed more than once (inserts, deletes, etc.). Examples:
    $rc = $dbh->do($statement) or
            die "Can't execute $statement: $dbh- >errstr\n";
    
    disconnect
    Disconnect will disconnect the database handle from the database. This is typically called right before you exit from the program. Example:
    $rc = $dbh->disconnect;
    
    quote
    The quote method is used to "escape" any special characters contained in the string and to add the required outer quotation marks.
    $sql = $dbh->quote($string)
    
    execute
    This method executes the prepared statement. For non-select satements, it returns the number of rows affected. For select statements, execute only starts the SQL query in the database. You need to use one of the fetch_* methods below to retrieve the data. Example:
    $rv = $sth->execute or die "can't execute the query: $sth->errstr;
    
    fetchrow_array
    This method "fetches" the next row of data and returns it as an array of field values. Example:
    while(@row = $sth->fetchrow_array) {
            print qw($row[0]\t$row[1]\t$row[2]\n); 
    }
    
    fetchrow_arrayref
    This method "fetches" the next row of data and returns it as a reference to an array of field values. Example:
    while($row_ref = $sth->fetchrow_arrayref) {
            print qw($row_ref->[0]\t$row_ref->[1]\t$row_ref->[2]\n);
    }
    
    fetchrow_hashref
    This method fetches a row of data and returns a reference to a hash table containing field name/value pairs. This method is not nearly as efficient as using array references as demonstrated above. Example:
    while($hash_ref = $sth->fetchrow_hashref) {
            print qw($hash_ref->{firstname}\t$hash_ref->{lastname}\t\
                    $hash_ref- > title}\n);
    }
    
    fetchall_arrayref
    This method is used to get all the data (rows) to be returned from the SQL statement. It returns a reference to an array of arrays of references to each row. You access/print the data by using a nested loop. Example:
    my $table = $sth->fetchall_arrayref or die "$sth->errstr\n";
    my($i, $j);
    for $i ( 0 .. $#{$table} ) {
            for $j ( 0 .. $#{$table->[$i]} ) {
                    print "$table->[$i][$j]\t";
            }
            print "\n";
    }
    
    finish
    Indicates that no more data will be fetched from this statement handle. You call this method to free up the statement handle and any system resources it may be holding. Example:
    $rc = $sth->finish;
    
    rows
    Returns the number of rows affected (updated, deleted, etc.) from the last command. This is usually used after a do() or non-select execute() statement.
    $rv = $sth->rows;
    
    NULLABLE
    A reference to an array of boolean values; TRUE indicates that this column may contain NULLs.
    $null_possible = $sth->{NULLABLE}; 
    
    NUM_OF_FIELDS
    Number of fields returned by a SELECT or LISTFIELDS statement. You may use this for checking whether a statement returned a result: A zero value indicates a non-SELECT statement like INSERT, DELETE or UPDATE.
    $nr_of_fields = $sth->{NUM_OF_FIELDS};
    
    data_sources
    This method returns an array of databases available to the mysql daemon on localhost.
    @dbs = DBI->data_sources("mysql"); 
    
    ChopBlanks
    This determines whether a fetchrow will chop preceding and trailing blanks off the returned values.
    $sth->{'ChopBlanks') =1; 
    
    MySQL specific methods.
    insertid
    If you use the auto-increment feature of mysql, the new auto-incremented values will be stored here.
    $new_id = $sth->{insertid};
    
    is_blob
    Reference to an array of boolean values; TRUE indicates that the respective column is a blob.
    $keys = $sth->{is_blob};
    
    is_key
    Reference to an array of boolean values; TRUE indicates, that the respective column is a key.
    $keys = $sth->{is_key};
    
    is_num
    Reference to an array of boolean values; TRUE indicates, that the respective column contains numeric values.
    $nums = $sth->{is_num};
    
    is_pri_key
    Reference to an array of boolean values; TRUE indicates, that the respective column is a primary key.
    $pri_keys = $sth->{is_pri_key};
    
    is_not_null
    A reference to an array of boolean values; FALSE indicates that this column may contain NULLs. You should better use the NULLABLE attribute above which is a DBI standard.
    $not_nulls = $sth->{is_not_null};
    
    max_length
    length
    A reference to an array of maximum column sizes. The max_length is the maximum physically present in the result table, length gives the theoretically possible maximum.
    $max_lengts = $sth->{max_length};
    $lengts = $sth->{length};
    
    NAME
    A reference to an array of column names.
    $names = $sth->{NAME};
    
    table
    Returns a reference to an array of table names.
    $tables = $sth->{table};
    

    17.2.1.2 More DBI/DBD information

    You can use the perldoc command to get more information about DBI.

    perldoc DBI
    perldoc DBI::FAQ
    perldoc mysql
    

    You can also use the pod2man, pod2html, etc.. tools to translate to other formats.

    And of course you can find the latest DBI information at the DBI web page
    .

    17.3 MySQL Java connectivity (JDBC)

    Insert pointers/descriptions of JDBC.

    17.4 MySQL PHP API's

    PHP documentation

    17.5 MySQL C++ API's

    Insert pointers/descriptions for C++.

    17.6 MySQL Python API's

    Insert pointers/descriptions for Python.

    17.7 MySQL TCL API's

    Insert pointers/descriptions for TCL.

    18 How does MySQL compare with other databases

    18.1 How does MySQL compare with mSQL

    This section has been written by the MySQL developers so it should be read with that in mind. But there are NO factual errors that we know of.

    For a list of all supported limits, functions and types see the
    crash-me web page.

    Performance.
    For a true comparison of speed see the growing MySQL benchmark suite. See section 11 MySQL benchmark suite MySQL is significantly quicker on complex selects. mSQL can get patalogically slow if you chage the order of tables in a select. In the benchmark suite a time more that 15000 times slower than MySQL was seen. Because of no thread creation overhead, small parser, few features and simple security mSQL should be quicker at: As these operations are so simple, it is hard to be better at them when you have a higher start overhead. After the connection is established MySQL should perform much better. MySQL on the other hand is much faster than mSQL and most other SQL implementions on the following:
    SQL Features
    1. GROUP BY & HAVING MySQL supports a full GROUP BY with both HAVING and the following functions count(), avg(), min(), max(), sum() and std(). min() and max() may take string arguments. count(*) is optimised to return very quickly if this is the only thing in the query. mSQL does not support GROUP BY at all.
    2. INSERT & UPDATE with calculations. MySQL can do calculations in a INSERT or UPDATE.
      UPDATE SET x=x*10+y WHERE x<20;
      
    3. Aliasing MySQL has column aliasing.
    4. Qualifying column names. If a column name is unique you do not have to use the full qualifier.
    5. SELECT with functions MySQL has too many functions to list here. See section 7.3 Functions for use in SELECT and WHERE clauses.
    Disk space efficiency
    That is, how small can you make your tables. MySQL has very precise types so that use can create tables that take very little space. An example of a useful MySQL datatype is the mediumint that is 3 bytes long. If you have 10.000.0000 records even saving one byte per record is very important. Since mSQL2 only has 4 types (char,text,int,real) it is hard to get small tables.
    Stability
    This is harder to judge objectively. For MySQL stability see See section 8.1 How stable is MySQL?. We have no experience with mSQL stability so we can not say anything about that.
    Price
    Another important issue is of course the license. MySQL has a more flexible license than mSQL and is also cheaper than mSQL. Remember to at least consider paying for a license or email support for whatever product you choose to use. If you sell a product with MySQL you are of course required to get a license for this.
    Perl interfaces
    MySQL has basically the same interfaces to perl as mSQL with some added features.
    JDBC (Java)
    MySQL has a Java interface by GWE technologies that has been improved by Xiaokun Kelvin ZHU. We know that mSQL has one but we have too little experience with it to compare.
    Rate of development
    MySQL has a very small team of developers, but we are quite used to coding C and C++ very rapidly. Since threads, functions, group by and so on are still not implemented in mSQL, it has a lot of catching up to do. To get some perspective on this you can view the mSQL HISTORY file for the last year and compare it with the News section. See section D MySQL change history. It should be pretty obvious which one has developed most rapidly.
    Utility programs
    Both mSQL and MySQL have a lot of interesting third-party tools. Since it is very easy to port upwars (mSQL -> MySQL) MySQL has almost all interesting mSQL applications. MySQL comes with a simple msql2mysql program that fixes the different spelling of the most used functions. A conversion of a client program from mSQL to mySQL usually takes a couple of minutes.

    18.1.1 How about mSQL tools like msql-tcl, msqljava?

    According to our experience it would just take a few hours to convert a tool using the mSQL C API to the MySQL C API.

    The procedure:

    1. Run the shell script msql2mysql on the source. This needs the binary program replace, which is distributed with MySQL.
    2. Compile
    3. Fix all compiler errors:

    Differences between the MySQL and mSQL C API's.

    18.1.2 How different from mSQL are the MySQL client/server communications protocols?

    There are enough differences that it is impossible (at least not easy) to support both.

    The greatest differences between MySQL and mSQL are:

    18.1.3 What are the differences in the SQL syntax between MySQL & mSQL 2.0?

    18.2 How does MySQL compare with PostgreSQL

    For a list of all supported limits, functions and types see the crash-me web page.

    PostgreSQL has some more advanced features like user-defined types, triggers, rules and transactions. But it lacks a lot of the standard types and functions from ANSI SQL and ODBC. See the crash-me web page for a complete list of supported/unsupported types/function.

    Normally PostgreSQL is much slower than MySQL. See section 11 MySQL benchmark suite.

    A Some users of MySQL.

    Send any additions to this list to webmaster@tcx.se.

    B Contributed programs

    Many users of MySQL has contributed very useful support tools and addons.

    Here is a list of what is available at http://www.tcx.se/Contrib (Or any mirror). The links below only works on the these pages.

    Db.py
    Python module with caching. By gandalf@rosmail.com.
    Old-Versions
    Previous versions of things found here that you probably wont be interested in.
    00-README
    This listing.
    Vdb-dflts-2.1.tgz
    This is a new version of a set of library utilities whose intention is to provide a generic interface to SQL database engines such that your application becomes a 3 tiered application. The advantage is that you can easily switch between and move to other database engines by implementing one file for the new backend without needing to make any changes to your applications. By damian@cablenet.net.
    access_to_mysql.txt
    Paste this function into an Access module of a database which has the tables you want to export. See also exportsql. By Brian Andrews.
    dbf2mysql-1.10d.tar.gz
    Convert between .dbf files and MySQL tables.
    By Maarten Boekhold
    delphi-interface.gz
    Delphi interface to libmysql.dll, by Blestan Tabakov.
    dump2h-1.00.gz
    Converter from a mysqldump output to a C header file. By Harry Brueckner
    emacs-sql-mode.tar.gz
    Raw port of a SQL mode for XEmacs. Supports completion. Original by Peter D. Pezaris pez@atlantic2.sbi.com and MySQL port by David Axmark
    exportsql.txt
    A script that is similar to the "access_to_mysql.txt". Only this one is fully configurable, has better type conversion (including detection of TIMESTAMP fields), gives out warnings and suggestions while converting, quotes *all* special characters on text and binary data, and so on. And it will also convert to mSQL v1 and v2, and is free of charge for anyone. See http://www.cynergi.net/prod/exportsql/ for latest version. By Pedro Freire support@cynergi.net
    findres.pl
    Find reserved words in tables. By Nem W Schlecht.
    handicap.tar.gz
    Performance handicapping system for yachts. Uses PHP. By
    hylalog-1.0.tgz
    Store hylafax outgoing faxes in MySQL database. By Sinisa Milivojevic
    importsql.txt
    A script that does the exact reverse of 'exportsql.txt'. That is, it imports data from MySQL into an Access database via ODBC. This is very handy when combined with exportSQL, since it lets you use Access for all DB design and administration, and synchronize with your actual MySQL server either way. Free of charge. See http://www.netdive.com/freebies/importsql/ for any updates. Created by Laurent Bossavit of NetDIVE.
    jms130b.zip
    JDBC driver for MySQL. Also contains command line client and other examples. By Xiaokun Kelvin ZHU and GWE Technologies
    twz1jdbcForMysql-0.7.11-beta.tar.gz
    New JDBC driver for MySQL. This is a beta release and is actively developed. By Terrence W. Zellers. You can always find the newest driver at http://www.voicenet.com/~zellert/tjFM
    mod_auth_mysql-2.11.tar.gz
    Apache authentication module for MySQL. By Zeev Suraski
    mod_log_mysql-1.05.tar.gz
    MySQL logging module for Apache. By Zeev Suraski
    mrtg-mysql-1.0.tar.gz
    MySQL status plotting with MRTG, by Luuk de Boer luuk@wxs.nl.
    msqltcl-1.50.tar.gz
    Tcl interface for msql. See also mysqltcl.c-patch. By Hakan Soderstrom,
    mysqltcl.c-patch
    Patch for msqltcl-1.50.tar.gz.
    mysqltcl-1.53.tar.gz
    Tcl interface for mysql. Based on msqltcl-1.50.tar.gz. Updated by Tobias Ritzau tobri@ida.liu.se
    MySQLmodule-1.1.tar.gz
    Pyton interface for MySQL. By Joseph Skinner (joe@earthlight.co.nz)
    mypasswd-2.0.tgz
    Extra for mod_auth_mysql. This is a little tool which allows to add/change user records storing group and/or password entries in MySQL tables. By Harry Brueckner
    mysql-c++-0.02.tar.gz
    MySQL C++ wrapper library. By Roland Haenel
    http://sunsite.unc.edu/kevina/mysql++/
    MySQL C++ API; More than just a wrapper library. By kevina@clark.net
    mysql-passwd.README
    mysql-passwd-1.2.tar.gz
    Extra for mod_auth_mysql. This is a two part system for use with mod_auth_mysql.
    mysql_watchdog.pl
    Monitor MySQL Daemon for possible lockups. By Yermo Lamers
    mysql-webadmin-1.0a8-rz.tar.gz
    A tool written in PHP-FI to administrate MySQL databases remotely over the web within a Web-Browser. By Peter Kuppelwieser
    mysqladm.tar.gz
    MySQL Web Database Administration written in perl. By Tim Sailer.
    mysqladm-2.tgz
    Updated version of mysqladm.tar.gz, by High Tide.
    nsapi_auth_mysql.tar
    Netscape Web Server API (NSAPI) functions to authenticate (BASIC) users against MySQL tables. By Yuan John Jiang
    mysqlwinadmn.zip
    Win32 GUI (binary only) to administrate a database, by David B. Mansel.
    pam_mysql.tgz
    This module authenticate user via a pam , using mysql.
    pike-mysql-1.4.tar.gz
    MySQL module for pike. For use with the Roxen web server.
    radius-0.3.tar.gz
    Patches for radiusd to make it support MySQL. By Wim Bonis
    sqlscreens-0.3.2.tar.gz
    TCL/TK code to generate database screens. By Jean-Francois Dockes.
    squile.tar.gz
    Module for guile that allows guile to interact with sql databases. By Hal Roberts.
    twz1jdbcForMysql-0.7.11-beta.tar.gz
    New java JDBC driver, by Terrence W. Zellers
    mm.mysql.jdbc-0.6.tar.gz
    Another new java JDBC driver, by Mark Matthews
    wmtcl.doc
    wmtcl.lex
    With this you can write html files with inclusions of TCL code. By
    wuftpd-2.4.2b12+mysql_support.tar.gz
    Pacthes to add logging to MySQL for WU-ftpd. By Zeev Suraski
    www-sql-0.5.1.lsm
    www-sql-0.5.1.md5
    www-sql-0.5.1.tar.gz
    A CGI program that parses an HTML file containing special tags, parses them and inserts data from a MySQL database.
    xmysql-1.8.tar.gz
    A front end to the MySQL database engine. It allows for simple queries and table maintenance, as well as batch queries. By Rick Mehalick dblhack@wt.net
    xmysqladmin-0.4.4.tar.gz
    A front end to the MySQL database engine. It allows reloads, status check, process control, isamcheck, grant/revoke privileges, creating databases, drop databases, create, alter, browse and drop tables. By Gilbert Therrien gilbert@ican.net

    C Who has helped to make MySQL.

    Contributors to the MySQL distribution in somewhat random order:

    Michael (Monty) Widenius
    Has written the following parts of MySQL:
    David Axmark
    Kim Aldale
    Rewriting Monty's and David's attempts at English into English.
    Allan Larsson (The BOSS at TcX)
    For all the time he has allowed Monty to spend on this 'maybe useful' tool (MySQL). Dedicated user (and bug finder) of UNIREG & MySQL.
    Per Eric Olsson
    For more or less constructive criticism and real testing of the dynamic record format.
    David J. Hughes
    For the effort to make a shareware SQL database. We at TcX started with mSQL but found that it couldn't satisfy our purposes so instead we wrote a SQL interface to our application builder UNIREG. mysqladmin & mysql are programs that were largely influenced by their mSQL counterparts. We have put a lot of effort into making the MySQL syntax a superset of mSQL. Much of the API:s ideas are borrowed from mSQL to make it easy to port free mSQL programs to MySQL. MySQL doesn't contain any code from mSQL. Two files (`client/insert_test.c' and `client/select_test.c') in the distribution are non copyrighted files from the mSQL distribution (Copyrighted David J. Hughes). These are included as a example of the changes that must be done to change from mSQL to MySQL.
    Fred Fish
    For his excellent C debugging and trace library. Monty has made a number of smaller improvements to the library (speed and additional options).
    Richard A. O'Keefe
    For his public domain string library.
    Henry Spencer
    For his regex library, used in 'WHERE column REGEXP regexp'.
    Free Software Foundation
    From whom we got an excellent compiler (gcc) and for their libc from which I have borrowed strto.c to get some code working in Linux. Also readline for the client.
    Free Software Foundation & The XEmacs development team
    For a really great editor/environment used by almost everybody at tcx/detron.
    Igor Romanenko (igor@frog.kiev.ua)
    mysqldump (Previously msqldump but ported and enhanced by Monty)
    Tim Bunce, Alligator Descartes
    For the DBD (perl) interface.
    Andreas Koenig <a.koenig@mind.de>
    For the perl interface to MySQL
    Eugene Chan <eugene@acenet.com.sg>
    For porting PHP to MySQL.
    Michael J. Miller Jr. <mke@terrapin.turbolift.com>
    For the growing MySQL user manual. And a lot of spelling/language fixes for the FAQ.
    Giovanni Maruzzelli <maruzz@matrice.it>
    For porting iODBC (unix ODBC).
    Chris Provenzano
    Portable user level pthreads. From the copyright: This product includes software developed by Chris Provenzano, the University of California, Berkeley, and contributors. We are currently using version 1_60_beta6 patched by Monty (see mit-pthreads/Changes-mysql).
    Xavier Leroy <Xavier.Leroy@inria.fr>
    The author of LinuxThreads (used by MySQL on Linux).
    Zarko Mocnik <zarko.mocnik@dem.si>
    Sorting for Slovenian language and the cset.tgz module for easier adding of other character sets.
    "TAMITO" <tommy@valley.ne.jp>
    The _MB character sets macros and ujis and sjis character sets.
    Yves Carlier <Yves.Carlier@rug.ac.be>
    mysqlaccess ; A program to show how the access right for a user.
    Rhys Jones <rhys@wales.com> (And GWE Technologies Limited)
    For the JDBC; A module to extract data from MySQL with a Java client.
    Dr Xiaokun Kelvin ZHU <X.Zhu@brad.ac.uk>
    Futher development of the JDBC driver and other MySQL related Java tools.
    James Cooper <pixel@organic.com>
    For setting up a searchable mailing list archive at his site.
    Rick_Mehalick <Rick_Mehalick@i-o.com>
    For xmysql a graphical X client for MySQL.
    Doug Sisk <sisk@wix.com>
    For providing RPM packages of MySQL for RedHat Linux.
    "Diemand Alexander V." <axeld@vial.ethz.ch>
    For providing RPM packages of MySQL for RedHat Linux/Alpha.
    Antoni Pamies Olive <toni@readysoft.es>
    For providing RPM versions of a lot of MySQL clients for Intel and Sparc.
    Jay Bloodworth <jay@pathways.sde.state.sc.us>
    For providing RPM versions for MySQL 3.21 versions.
    Jochen Wiedmann <wiedmann@neckar-alb.de>
    Maintaining of the perl module DBD::mysql
    Therrien Gilbert <gilbert@ican.net>, Jean-Marc Pouyot <jmp@scalaire.fr>
    French error messages.
    Petr snajdr, <snajdr@pvt.net>
    Czech error messages.
    Jaroslaw Lewandowski <jotel@itnet.com.pl>
    Polish error messages
    Miguel Angel Fernandez Roiz
    Spanish error messages
    Roy-Magne Mo <rmo@www.hivolda.no>
    Norwegian error messages and testing of 3.21.#
    Timur I. Bakeyev <root@timur.tatarstan.ru>
    Russian error messages
    brenno@dewinter.com
    Italian error messages
    Dirk Munzinger <dirk@trinity.saar.de>
    German error messages
    David Sacerdote <davids@secnet.com>
    Ideas for secure checking of DNS hostnames
    Gianmassimo Vigazzola" <qwerg@mbox.vol.it> or <qwerg@tin.it>
    Port to Win32/NT.
    Irena Pancirov <irena@mail.yacc.it>
    Win32 port with Borland compiler.
    Wei-Jou Chen <jou@nematic.ieo.nctu.edu.tw>
    Some support for Chinese(BIG5) characters.
    Zeev Suraski <bourbon@netvision.net.il>
    from_unixtime time formating, ENCRYPT() functions, and bison adviser. Active mailing list member.
    Luuk de Boer <luuk@wxs.nl>
    Ported (and extended) the benchmark suite to DBI/DBD. Some new date functions.
    Jay Flaherty <fty@utk.edu>
    Big parts of the perl DBI/DBD section in the manual.
    Paul Southworth <pauls@etext.org>, Ray Loyzaga <yar@cs.su.oz.au>, Paul DuBois <paul@snake.net>.
    Proof-reading of the reference manual.
    Alexis Mikhailov <root@medinf.chuvashia.su>
    User definable functions (UDF); CREATE FUNCTION and DROP FUNCTION.
    Ross Wakelin <R.Wakelin@march.co.uk>
    Help to set up InstallShield for MySQL-win32.
    Jethro Wright III <jetman@li.net>
    libmysql.dll
    James Pereria <jpereira@iafrica.com>
    Mysqlmanager; A Win32 gui tool for administrating MySQL.
    Curt Sampson <cjs@portal.ca>
    Porting of MIT-pthreads to NetBSD/Alpha and NetBSD 1.3/i386.
    Sinisa Milivojevic <sinisa@coresinc.com>
    Added compression (with zlib) to the client/server protocol.

    Other contributors, bugfinders and testers: James H. Thompson, Maurizio Menghini, Wojciech Tryc, Luca Berra, Zarko Mocnik, Wim Bonis, Elmar Haneke, jehamby@lightside, psmith@BayNetworks.COM, duane@connect.com.au, "Ted Deppner <ted@psyber.com>, Mike Simons, Jaakko Hyvätti.

    And lots of bug report/patches from the folks on the mailing list.

    And a big tribute to those that helps us answer question on the mysql@tcx.se mailing list.

    "Daniel Koch" <dkoch@amcity.com>
    IRIX setup.
    "Luuk de Boer" <luuk@wxs.nl>
    Benchmark questions.
    Tim Sailer <tps@users.buoy.com>
    DBD-mysql questions.
    "Boyd Lynn Gerber (801) 250-O795 Work" <gerberb@zenez.com>
    SCO related questions.
    "Mehalick RE (Richard) at MSXSEPC" <RM186061@shellus.com>
    XmySQL releated questions and basic installation questions.
    Zeev Suraski <bourbon@netvision.net.il>
    Apache module configuration questions (log & auth), PHP related questions, SQL syntax related questions and other general questions.
    Francesc Guasch <frankie@citel.upc.es>
    General questions
    Jonathan J Smith <jsmith@wtp.net>
    Questions pertaining to OS specifics with Linux, SQL syntax, and other things that might be needing some work.
    David Sklar <sklar@student.net>
    Using MySQL from PHP and Perl.
    Alistair MacDonald <A.MacDonald@uel.ac.uk>
    Not yet specified, but is flexible and can handle Linux and maybe HP-UX. Will try to get user to use mysqlbug.
    John Lyon <jlyon@imag.net>
    Questions about installing MySQL on Linux systems, using either .rpms, or compiling from source.
    "Lorvid Ltd." lorvid@WOLFENET.com
    Simple billing/license/support/copyright issues.
    Patrick Sherrill <patrick@coconet.com>
    ODBC and VisualC++ interface questions.
    Randy Harmon <rjharmon@uptimecomputers.com>
    DBD, Linux, Some SQL syntax questions.

    D MySQL change history

    18.3 Changes in release 3.22.x (Alpha version)

    The 3.22 version has faster and safer connect code and a lot of new nice enhancements. The reason for not including these changes in the 3.21 version is mainly that we are trying to avoid big changes to 3.21 to keep it as stable as possible. As there aren't really any MAJOR changes, upgrading to 3.22 should be very easy and painless.

    3.22 also includes the new DBD-mysql (1.1830) driver that can use the new connect protocol!

    18.3.1 Changes in release 3.22.4

    18.3.2 Changes in release 3.22.3

    18.3.3 Changes in release 3.22.2

    18.3.4 Changes in release 3.22.1

    18.3.5 Changes in release 3.22.0

    18.4 Changes in release 3.21.x

    18.4.1 Changes in release 3.21.33

    18.4.2 Changes in release 3.21.32

    18.4.3 Changes in release 3.21.31

    18.4.4 Changes in release 3.21.30

    18.4.5 Changes in release 3.21.29

    18.4.6 Changes in release 3.21.28

    18.4.7 Changes in release 3.21.27

    18.4.8 Changes in release 3.21.26

    18.4.9 Changes in release 3.21.25

    18.4.10 Changes in release 3.21.24

    18.4.11 Changes in release 3.21.23

    18.4.12 Changes in release 3.21.22

    18.4.13 Changes in release 3.21.21a

    18.4.14 Changes in release 3.21.21

    18.4.15 Changes in release 3.21.20

    18.4.16 Changes in release 3.21.19

    18.4.17 Changes in release 3.21.18

    18.4.18 Changes in release 3.21.17

    18.4.19 Changes in release 3.21.16

    18.4.20 Changes in release 3.21.15

    18.4.21 Changes in release 3.21.14b

    18.4.22 Changes in release 3.21.14a

    18.4.23 Changes in release 3.21.13

    18.4.24 Changes in release 3.21.12

    18.4.25 Changes in release 3.21.11

    18.4.26 Changes in release 3.21.10

    18.4.27 Changes in release 3.21.9

    18.4.28 Changes in release 3.21.8

    18.4.29 Changes in release 3.21.7

    18.4.30 Changes in release 3.21.6

    18.4.31 Changes in release 3.21.5

    18.4.32 Changes in release 3.21.4

    18.4.33 Changes in release 3.21.3

    18.4.34 Changes in release 3.21.2

    18.4.35 Changes in release 3.21.0

    18.5 Changes in release 3.20.x

    Changes from 3.20.18 to 3.20.32b are not documented here since the the 3.21 release bransched here. And the relevant changes are also documented as changes to the 3.21 version.

    18.5.1 Changes in release 3.20.18

    18.5.2 Changes in release 3.20.17

    18.5.3 Changes in release 3.20.16

    18.5.4 Changes in release 3.20.15

    18.5.5 Changes in release 3.20.14

    18.5.6 Changes in release 3.20.13

    18.5.7 Changes in release 3.20.11

    18.5.8 Changes in release 3.20.10

    18.5.9 Changes in release 3.20.9

    18.5.10 Changes in release 3.20.8

    18.5.11 Changes in release 3.20.7

    18.5.12 Changes in release 3.20.6

    18.5.13 Changes in release 3.20.3

    18.5.14 Changes in release 3.20.0

    18.6 Changes in release 3.19.x

    18.6.1 Changes in release 3.19.5

    18.6.2 Changes in release 3.19.4

    18.6.3 Changes in release 3.19.3

    E Known errors and design deficiencies in MySQL

    For platform specific bugs see the sections about compiling and porting.

    F List of things we want to add to MySQL in the future (The TODO).

    Everything in this list is in the order it will be done. If you want to affect the priority order, please register a licence or support us and tell us what you want to have done more quickly. See section 3 Licensing or When do I have/want to pay for MySQL?.

    18.7 Things what has to be done in the real near future

    18.8 Things that have to be done sometime

    Time is given according to amount of work, not real time. TcX's main business is the use of MySQL not the development of it. But since TcX is a very flexible company and we have put a lot of resources into the development of MySQL.

    18.9 Some things we don't have any plans to do

    G Comments on porting to other systems.

    A working Posix thread library is needed for the server. On Solaris 2.5 we use SUN PThreads (the native thread support in 2.4 and earlier versions are not good enough) and on Linux we use LinuxThreads by Xavier Leroy Xavier.Leroy@inria.fr.

    The hard part of porting to a new UNIX variant without good native thread support is probably to port MIT-pthreads. See `mit-pthreads/README' and Programming POSIX Threads.

    The MySQL distribution includes a patched version of Provenzano's Pthreads from MIT (see MIT Pthreads web page). This can be used for some operating systems that does not have posix threads.

    It is also possible to use another user level thread package named FSU Pthreads (see FSU pthread home page). This implementation is being used for the SCO port.

    See the `thr_lock.c' and `thr_alarm.c' programs in the mysys directory for some tests/examples of these problems

    Both the server and the client needs a working C++ compiler (we use gcc and have tried SparcWorks). Other compiler that is known to work is the IRIX cc.

    To compile only the client use `./configure --without-server'

    There currently no support for only compiling the server. Nor is it likly to be added unless someone has a good reason for it.

    If you want/need to change any Makefile or the configure script you must get automake and autoconf. We have used autoconf-2.12 and automake-1.2.

    All steps needed to remake everything from the most basic files.

    /bin/rm */.deps/*.P
    /bin/rm -f config.cache
    aclocal
    autoheader
    aclocal
    automake
    autoconf
    ./configure --with-debug=yes --prefix='your installation directory'
    
    # The makefiles generated above needs GNU make (called gmake below)
    gmake clean all install init-db
    

    If you run into problem with a new port, you may have to do some debugging of mysql! See section 18.10 Debugging MySQL

    NOTE: Before you start debugging mysqld, first get the test programs mysys/thr_alarm and mysys/thr_lock to work. This will ensure that your thread installation has even a remotely change to work!

    18.10 Debugging MySQL

    If you have some very specific problem, you can always try to debug MySQL. To do this you must configure MySQL with the option --with-debug=yes. You can check if MySQL if compiled with debugging by doing: mysqld --help. If the --debug flag is listed with the options then you have debugging enabled. mysqladmin ver also lists the mysqld version as mysql ... -debug in this case.

    Start the mysql server with a trace log in /tmp/mysql.trace. The log file will get very BIG.

    mysqld --debug

    or you can start it with

    mysqld --debug=d,info,error,query,general,where:O,/tmp/mysql.trace

    which only prints information with the most interesting tags.

    When you configure MySQL for debugging you automatically enable a lot of extra safety check functions that monitor the health of mysqld. If they find something 'unexpected' a entry will be written to stderr, which safe_mysqld directs to the error log! This also means that if you are having some unexpected problems with MySQL, the first thing you, if you are using a source distribution, is to configure MySQL for debugging! (The second thing is of course to mail to mysql@tcx.se and ask for help. Please use the mysqlbug script for all bug reports or questions regarding a MySQL version you are using!

    On most system (except Linux) you can also start mysqld in gdb to get more information if mysqld crashes.

    shell> gdb libexec/mysqld
    gdb>   run
    ...
    back   # Do this when mysqld crashes
    quit
    

    18.11 Comments about RTS threads

    I have tried to use the RTS thread packages with MySQL but stumbled on the following problems:

    They use old version of a lot of POSIX calls and it is very tedious to make wrappers for all functions. I am inclined to think that it would be easier to change the thread libraries to the newest POSIX specification.

    Some wrappers are already written. Se mysys/my_pthread.c for more info.

    At least the following should be changed:

    pthread_get_specific should use on argument. sigwait should take two arguments. A lot of functions (at least pthread_cond_wait, pthread_cond_timedwait) should return the error code on error. Now they return -1 and set errno.

    Another problem is that user level threads uses the ALRM signal and this aborts a lot of functions (read, write, open...). MySQL should do a retry on interrupt on all of these but it not that easy to verify it.

    The biggest unsolved problem is the following:

    To get thread_level alarms I changed mysys/thr_alarm.c to wait between alarms with pthread_cond_timedwait() but this aborts with error EINTR. I tried to debug the thread library why this happens but couldn't find any easy solution.

    If someone wants to try MySQL with RTS threads I suggest the following:

    18.12 What is the difference between different thread packages?

    MySQL is very dependent on the used thread package. So when choosing a good platform for MySQL the thread package is very important.

    There are at least three types of thread packages.

    In some systems kernel threads are managed by integrating user level threads in the system libraries. In such cases, the thread switching can only be done by the thread library and the kernel isn't really 'thread aware'.

    H Description of MySQL regular expression syntax.

    Regular expressions are a powerful way of specifying complex searches.

    MySQL uses regular Henry Spencers inplementation of regular expressions. And that is aimed to conform to POSIX 1003.2. MySQL uses the extended version.

    To get more exact information see Henry Spencers regex.7 manual that is included in the source distribution. See section C Who has helped to make MySQL..

    This is a simplistic reference that skips the details. From here on a regualr expressions is called a regexp.

    A regular expression describes a set of strings. The simplest case is one that has no special characters in it. For example the regexp hello matches hello and nothing else.

    Nontrivial regular expressions use certain special constructs so that they can match more than one string. For example, the regexp hello|word matches either the string hello or the string word.

    And a more comples example regexp B[an]*s matches any of the strings Bananas, Baaaaas, Bs and all other string starting with a B and continuing with any number of a n and ending with a s.

    The following special characters/constructs are known.

    ^
    Start of whole string.
    mysql> select "fo\nfo" regexp "^fo$";           -> 0
    mysql> select "fofo" regexp "^fo";              -> 1
    
    $
    End of whole string.
    mysql> select "fo\no" regexp "^fo\no$";         -> 1
    mysql> select "fo\no" regexp "^fo$";            -> 0
    
    .
    Any character (including newline).
    mysql> select "fofo" regexp "^f.*";             -> 1
    mysql> select "fo\nfo" regexp "^f.*";           -> 1
    
    a*
    Any sequence of zero or more a's.
    mysql> select "Ban" regexp "^Ba*n";             -> 1
    mysql> select "Baaan" regexp "^Ba*n";           -> 1
    mysql> select "Bn" regexp "^Ba*n";              -> 1
    
    a+
    Any sequence of one or more a's.
    mysql> select "Ban" regexp "^Ba+n";             -> 1
    mysql> select "Bn" regexp "^Ba+n";              -> 0
    
    a?
    Either zero or one a.
    mysql> select "Bn" regexp "^Ba?n";              -> 1
    mysql> select "Ban" regexp "^Ba?n";             -> 1
    mysql> select "Baan" regexp "^Ba?n";            -> 0
    
    de|abc
    Either the sequence de or abc.
    mysql> select "pi" regexp "pi|apa";             -> 1
    mysql> select "axe" regexp "pi|apa";            -> 0
    mysql> select "apa" regexp "pi|apa";            -> 1
    mysql> select "apa" regexp "^(pi|apa)$";        -> 1
    mysql> select "pi" regexp "^(pi|apa)$";         -> 1
    mysql> select "pix" regexp "^(pi|apa)$";        -> 0
    
    (abc)*
    Zero or more times the sequence abc.
    mysql> select "pi" regexp "^(pi)+$";            -> 1
    mysql> select "pip" regexp "^(pi)+$";           -> 0
    mysql> select "pipi" regexp "^(pi)+$";          -> 1
    
    {1}
    {2,3}
    The is a more general way of writing regexps that match many occurences.
    a*
    Can be written as a{0,}.
    +
    Can be written as a{1,}.
    ?
    Can be written as a{0,1}.
    To be more precice an atom followed by a bound containing one integer i and no comma matches a sequence of exactly i matches of the atom. An atom followed by a bound containing one integer i and a comma matches a sequence of i or more matches of the atom. An atom followed by a bound containing two integers i and j matches a sequence of i through j (inclusive) matches of the atom. Both arguments must 0 >= value <= RE_DUP_MAX (default 255), and if there are two of them, the second must be bigger or equal to the first.
    [a-dX]
    [^a-dX]
    Any character which is (not if ^ is used) either a, b, c, d or X. To include ] it has to be written first. To include - it has to be written first or last. So [0-9] matches any decimal digit. All character that does not have a defined mening inside a [] pair has no special meaning and matches only itself.
    mysql> select "aXbc" regexp "[a-dXYZ]";         -> 1
    mysql> select "aXbc" regexp "^[a-dXYZ]$";       -> 0
    mysql> select "aXbc" regexp "^[a-dXYZ]+$";      -> 1
    mysql> select "aXbc" regexp "^[^a-dXYZ]+$";     -> 0
    mysql> select "gheis" regexp "^[^a-dXYZ]+$";    -> 1
    mysql> select "gheisa" regexp "^[^a-dXYZ]+$";   -> 0
    
    [[.characters.]]
    The sequence of characters of that collating element. The sequence is a single element of the bracket expression's list. A bracket expression containing a multi-character collating element can thus match more than one character, e.g. if the collating sequence includes a ch collating element, then the RE [[.ch.]]*c matches the first five characters of chchcc.
    [=character-class=]
    An equivalence class, standing for the sequences of characters of all collating elements equivalent to that one, including itself. For example, if o and (+) are the members of an equivalence class, then [[=o=]], [[=(+)=]], and [o(+)] are all synonymous. An equivalence class may not be an endpoint of a range.
    [:character_class:]
    Within a bracket expression, the name of a character class enclosed in [: and :] stands for the list of all characters belonging to that class. Standard character class names are:
    alnum digit punct
    alpha graph space
    blank lower upper
    cntrl print xdigit
    These stand for the character classes defined in ctype(3). A locale may provide others. A character class may not be used as an endpoint of a range.
    mysql> select "justalnums" regexp "[[:alnum:]]+";       -> 1
    mysql> select "!!" regexp "[[:alnum:]]+";               -> 0
    
  • [[:<:]]
  • [[:>:]] These match the null string at the beginning and end of a word respectively. A word is defined as a sequence of word characters which is neither preceded nor followed by word characters. A word character is an alnum character (as defined by ctype(3)) or an underscore.
    mysql> select "a word a" regexp "[[:<:]]word[[:>:]]";      -> 1
    mysql> select "a xword a" regexp "[[:<:]]word[[:>:]]";     -> 0
    
  • mysql> select "weeknights" regexp "^(wee|week)(knights|nights)$"; -> 1
    

    I What is UNIREG?

    Unireg is our tty interface builder, but it uses a low level connection to our NISAM (which is used by MySQL) and because of this it is very quick. It has existed since 1979 (on Unix in C since ~1986).

    Unireg has the following components:

    We update most of our production databases with the UNIREG interface and serve web pages through MySQL (and in some extreme cases the UNIREG report generator).

    Unireg takes about 3M of disk space and works on at least the following platforms: SUN OS 4.x, Solaris, Linux, HP-UX, ICL Unix, DNIX, SCO and MSDOS.

    Unireg is currently only available in Swedish and Finnish.

    The price tag for UNIREG is 10,000 Swedish kr (about 1500$ US), but this includes support. UNIREG is distributed as a binary. (But all the ISAM sources can be found in MySQL). Usually we compile the binary for the customer at their site.

    All new development is concentrated to MySQL.

    J The MySQL server license

    MySQL FREE PUBLIC LICENSE (Version 4, March 5, 1995)

    Copyright (C) 1995, 1996 TcX AB & Monty Program KB & Detron HB Stockholm SWEDEN, Helsingfors FINLAND and Uppsala SWEDEN All rights reserved.

    NOTE: This license is not the same as any of the GNU Licenses published by the Free Software Foundation. Its terms are substantially different from those of the GNU Licenses. If you are familiar with the GNU Licenses, please read this license with extra care.

    This License applies to the computer program known as "MySQL". The "Program", below, refers to such program, and a "work based on the Program" means either the Program or any derivative work of the Program, as defined in the United States Copyright Act of 1976, such as a translation or a modification. The Program is a copyrighted work whose copyright is held by TcX Datakonsult AB and Monty Program KB and Detron HB.

    This License does not apply when running "MySQL" on any Microsoft operating system. Microsoft operating systems include all versions of Microsoft Windows NT and Microsoft Windows.

    BY MODIFYING OR DISTRIBUTING THE PROGRAM (OR ANY WORK BASED ON THE PROGRAM), YOU INDICATE YOUR ACCEPTANCE OF THIS LICENSE TO DO SO, AND ALL ITS TERMS AND CONDITIONS FOR COPYING, DISTRIBUTING OR MODIFYING THE PROGRAM OR WORKS BASED ON IT. NOTHING OTHER THAN THIS LICENSE GRANTS YOU PERMISSION TO MODIFY OR DISTRIBUTE THE PROGRAM OR ITS DERIVATIVE WORKS. THESE ACTIONS ARE PROHIBITED BY LAW. IF YOU DO NOT ACCEPT THESE TERMS AND CONDITIONS, DO NOT MODIFY OR DISTRIBUTE THE PROGRAM.

    1. Licenses. Licensor hereby grants you the following rights, provided that you comply with all of the restrictions set forth in this License and provided, further, that you distribute an unmodified copy of this License with the Program:
      1. You may copy and distribute literal (i.e., verbatim) copies of the Program's source code as you receive it throughout the world, in any medium.
      2. You may modify the Program, create works based on the Program and distribute copies of such throughout the world, in any medium.
    2. Restrictions. This license is subject to the following restrictions:
      1. Distribution of the Program or any work based on the Program by a commercial organization to any third party is prohibited if any payment is made in connection with such distribution, whether directly (as in payment for a copy of the Program) or indirectly (as in payment for some service related to the Program, or payment for some product or service that includes a copy of the Program "without charge"; these are only examples, and not an exhaustive enumeration of prohibited activities). However, the following methods of distribution involving payment shall not in and of themselves be a violation of this restriction:
        1. Posting the Program on a public access information storage and retrieval service for which a fee is received for retrieving information (such as an on-line service), provided that the fee is not content-dependent (i.e., the fee would be the same for retrieving the same volume of information consisting of random data).
        2. Distributing the Program on a CD-ROM, provided that the files containing the Program are reproduced entirely and verbatim on such CD-ROM, and provided further that all information on such CD-ROM be redistributable for non-commercial purposes without charge.
      2. Activities other than copying, distribution and modification of the Program are not subject to this License and they are outside its scope. Functional use (running) of the Program is not restricted, and any output produced through the use of the Program is subject to this license only if its contents constitute a work based on the Program (independent of having been made by running the Program).
      3. You must meet all of the following conditions with respect to the distribution of any work based on the Program:
        1. If you have modified the Program, you must cause your work to carry prominent notices stating that you have modified the Program's files and the date of any change;
        2. You must cause any work that you distribute or publish, that in whole or in part contains or is derived from the Program or any part thereof, to be licensed as a whole and at no charge to all third parties under the terms of this License;
        3. If the modified program normally reads commands interactively when run, you must cause it, at each time the modified program commences operation, to print or display an announcement including an appropriate copyright notice and a notice that there is no warranty (or else, saying that you provide a warranty). Such notice must also state that users may redistribute the Program only under the conditions of this License and tell the user how to view the copy of this License included with the Program. (Exception: if the Program itself is interactive but does not normally print such an announcement, your work based on the Program is not required to print an announcement.);
        4. You must accompany any such work based on the Program with the complete corresponding machine-readable source code, delivered on a medium customarily used for software interchange. The source code for a work means the preferred form of the work for making modifications to it. For an executable work, complete source code means all the source code for all modules it contains, plus any associated interface definition files, plus the scripts used to control compilation and installation of the executable code. However, the source code distributed need not include anything that is normally distributed (in either source or binary form) with the major components (compiler, kernel, and so on) of the operating system on which the executable runs, unless that component itself accompanies the executable code;
        5. If you distribute any written or printed material at all with the Program or any work based on the Program, such material must include either a written copy of this License, or a prominent written indication that the Program or the work based on the Program is covered by this License and written instructions for printing and/or displaying the copy of the License on the distribution medium;
        6. You may not impose any further restrictions on the recipient's exercise of the rights granted herein. If distribution of executable or object code is made by offering the equivalent ability to copy from a designated place, then offering equivalent ability to copy the source code from the same place counts as distribution of the source code, even though third parties are not compelled to copy the source code along with the object code.
    3. Reservation of Rights. No rights are granted to the Program except as expressly set forth herein. You may not copy, modify, sublicense, or distribute the Program except as expressly provided under this License. Any attempt otherwise to copy, modify, sublicense or distribute the Program is void, and will automatically terminate your rights under this License. However, parties who have received copies, or rights, from you under this License will not have their licenses terminated so long as such parties remain in full compliance.
    4. Other Restrictions. If the distribution and/or use of the Program is restricted in certain countries for any reason, Licensor may add an explicit geographical distribution limitation excluding those countries, so that distribution is permitted only in or among countries not thus excluded. In such case, this License incorporates the limitation as if written in the body of this License.
    5. Limitations. THE PROGRAM IS PROVIDED TO YOU "AS IS," WITHOUT WARRANTY. THERE IS NO WARRANTY FOR THE PROGRAM, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT OF THIRD PARTY RIGHTS. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE PROGRAM IS WITH YOU. SHOULD THE PROGRAM PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, REPAIR OR CORRECTION. IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN WRITING WILL LICENSOR, OR ANY OTHER PARTY WHO MAY MODIFY AND/OR REDISTRIBUTE THE PROGRAM AS PERMITTED ABOVE, BE LIABLE TO YOU FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE PROGRAM (INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A FAILURE OF THE PROGRAM TO OPERATE WITH ANY OTHER PROGRAMS), EVEN IF SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

    SQL command, type and function index.

  • !
  • !=
  • %
  • &
  • &&
  • (
  • )
  • *
  • +
  • -, -
  • /
  • <
  • <<, <<
  • <=
  • <>
  • =
  • >
  • >=
  • A

  • A BETWEEN B AND C
  • ABS()
  • ACOS(X)
  • ALTER TABLE
  • AND
  • Aritmetic functions
  • ASCII(S)
  • ASIN(X)
  • ATAN(X)
  • ATAN2(X,Y)
  • AVG(expr)
  • B

  • BIGINT
  • BIN(N)
  • Bit functions
  • BIT_AND(expr)
  • BIT_COUNT()
  • BIT_OR(expr)
  • BLOB
  • BLOB (Generic)
  • C

  • CEILING()
  • CHAR
  • CHAR(X,...)
  • CHAR_LENGTH(S)
  • CHARACTER_LENGTH(S)
  • ChopBlanks
  • Comment syntax
  • Comparison operators
  • CONCAT(X,Y...)
  • connect
  • Control flow functions
  • CONV(NUMBER,FROM_BASE,TO_BASE)
  • COS(X)
  • COT(N)
  • COUNT(Expr)
  • CREATE DATABASE
  • CREATE FUNCTION
  • CREATE INDEX
  • CREATE TABLE
  • CURDATE()
  • CURRENT_DATE
  • CURRENT_TIME
  • CURRENT_TIMESTAMP
  • CURTIME()
  • D

  • data_sources
  • DATABASE()
  • DATE
  • Date and time functions
  • DATE_ADD(date, INTERVAL expr TYPE), DATE_SUB(date, INTERVAL expr TYPE)
  • DATE_FORMAT(Date, Format)
  • DATETIME
  • DAYNAME(date expr)
  • DAYOFMONTH(date expr)
  • DAYOFWEEK(date expr)
  • DAYOFYEAR(date expr)
  • DECIMAL
  • DEGREES(N)
  • DELETE
  • DELETE TABLE
  • DESC
  • DESCRIBE
  • disconnect
  • do
  • DOUBLE PRECISION
  • DROP DATABASE
  • DROP FUNCTION
  • DROP INDEX
  • E

  • ELT(N, A1, A2, A3...)
  • ENCRYPT(String[, Salt])
  • ENUM, ENUM
  • execute
  • EXP(N)
  • EXPLAIN
  • expr IN (value,...)
  • expr LIKE expr
  • expr NOT IN (value,...)
  • expr NOT LIKE expr
  • expr NOT REGEXP expr
  • expr REGEXP text_literal
  • expr RLIKE text_literal
  • F

  • fetchall_arrayref
  • fetchrow_array
  • fetchrow_arrayref
  • fetchrow_hashref
  • FIELD(S, S1, S2, S3...)
  • FIND_IN_SET(string,string of strings)
  • finish
  • FLOAT
  • FLOAT(4)
  • FLOAT(8)
  • FLOAT(M,D)
  • FLOOR()
  • FORMAT(Nr, Num)
  • FROM_DAYS()
  • FROM_UNIXTIME(Unix_timestamp)
  • FROM_UNIXTIME(Unix_timestamp, Format_string)
  • G

  • GET_LOCK(String,timeout)
  • GRANT
  • Group by functions
  • H

  • HEX(N)
  • HOUR(time expr)
  • I

  • IF(A,B,C)
  • IFNULL(A,B)
  • INSERT
  • INSERT(Org, Start, Length, New)
  • insertid
  • INSTR(A,B)
  • INT
  • INTERVAL(N, N1, N2, N3...)
  • is_blob
  • is_key
  • is_not_null
  • is_num
  • is_pri_key
  • ISNULL(A)
  • J

  • JOIN
  • L

  • LAST_INSERT_ID()
  • LCASE(A)
  • LEFT JOIN
  • LEFT(str,length)
  • length
  • LENGTH(S)
  • LIKE
  • LOAD DATA INFILE
  • LOCATE(A,B)
  • LOCATE(A,B,C)
  • LOCK TABLES
  • LOG(X)
  • LOG10(X)
  • Logical functions
  • LONGBLOB
  • LONGTEXT
  • LOWER(A)
  • LPAD(A,B,C)
  • LTRIM(str)
  • M

  • Mathematical functions
  • MAX(expr)
  • MAX(X,Y...)
  • max_length
  • MEDIUMBLOB
  • MEDIUMINT
  • MEDIUMTEXT
  • MID(A, B, C)
  • MIN(expr)
  • MIN(X,Y...)
  • MINUTE(time expr)
  • Miscellaneous functions
  • MOD()
  • MONTH(date expr)
  • MONTHNAME(date expr)
  • mysql_affected_rows
  • mysql_close
  • mysql_connect
  • mysql_create_db
  • mysql_data_seek
  • mysql_drop_db
  • mysql_eof
  • mysql_error
  • mysql_escape_string
  • mysql_fetch_field
  • mysql_fetch_lengths
  • mysql_fetch_row
  • mysql_field_seek
  • mysql_free_result
  • mysql_get_client_info
  • mysql_get_host_info
  • mysql_get_proto_info
  • mysql_get_server_info
  • mysql_insert_id
  • mysql_list_dbs
  • mysql_list_fields
  • mysql_list_processes
  • mysql_list_tables
  • mysql_num_fields
  • mysql_num_rows
  • mysql_query
  • mysql_real_query
  • mysql_reload
  • mysql_select_db
  • mysql_shutdown
  • mysql_stat
  • mysql_store_result
  • mysql_use_result
  • N

  • NAME
  • NOT
  • NOW()
  • NULLABLE
  • NUM_OF_FIELDS
  • NUMERIC
  • O

  • OCT(N)
  • OCTET_LENGTH(S)
  • OR
  • P

  • Parenthesis
  • PASSWORD(String)
  • PERIOD_ADD(P, N)
  • PERIOD_DIFF(A, B)
  • PI()
  • POSITION(B IN A)
  • POW(X,Y)
  • POWER(X,Y)
  • prepare
  • Q

  • QUARTER(date expr)
  • quote
  • R

  • RADIANS(N)
  • RAND([X])
  • REAL
  • RELEASE_LOCK(String)
  • REPEAT(String, Count)
  • REPLACE
  • REPLACE(A, B, C)
  • REVERSE(String)
  • RIGHT(A,B)
  • ROUND(N)
  • ROUND(Number,Decimals)
  • rows
  • RPAD(A,B,C)
  • RTRIM(str)
  • S

  • SEC_TO_TIME(Seconds)
  • SECOND(time expr)
  • SELECT
  • SESSION_USER()
  • SET, SET
  • SET OPTION
  • SHOW COLUMNS
  • SHOW DATABASES
  • SHOW FIELDS
  • SHOW INDEXE
  • SHOW KEYS
  • SHOW STATUS
  • SHOW TABLES
  • SHOW VARIABLES
  • SIGN()
  • SIN(X)
  • SMALLINT
  • SOUNDEX(S)
  • SPACE(N)
  • sqrt(X)
  • STD(expr)
  • STDDEV(expr) (Oracle format)
  • STRCMP()
  • String comparison functions
  • String functions
  • SUBSTRING(A FROM B FOR C)
  • SUBSTRING(A FROM B)
  • SUBSTRING(A, B, C)
  • SUBSTRING(A,B)
  • SUBSTRING_INDEX(String, Delimiter, Count)
  • SUM(expr)
  • SYSDATE()
  • SYSTEM_USER()
  • T

  • table
  • TAN(X)
  • TEXT
  • TEXT (Generic)
  • TIME
  • TIME_FORMAT(time expr, format)
  • TIME_TO_SEC(Time)
  • TIMESTAMP, TIMESTAMP
  • TINYBLOB
  • TINYINT
  • TINYTEXT
  • TO_DAYS(Date)
  • TRIM([[ BOTH | LEADING | TRAILING] [ A ] FROM ] B)
  • TRUNCATE(Number, Decimals)
  • Types
  • U

  • UCASE(A)
  • UDF functions
  • UNIX_TIMESTAMP([date expression])
  • UNLOCK TABLES
  • UPDATE
  • UPPER(A)
  • USER()
  • V

  • VARCHAR
  • VERSION
  • W

  • WEEK(date expr)
  • WEEKDAY(date expr)
  • Y

  • YEAR
  • YEAR(date expr)
  • |
  • ||
  • Concept Index

    A

  • Aritmetic expressions
  • B

  • Backup
  • Big5 Chinese character encoding
  • Bug reports
  • C

  • Case sensitivity
  • Casts
  • Checking a table for errors
  • Chinese
  • Choosing types
  • Choosing version
  • Client libraries
  • Commands out of sync
  • Copyright
  • Cost
  • D

  • default options
  • Disk full
  • Downloading
  • E

  • Environment variables.
  • F

  • Full disk
  • Functions for select & where
  • G

  • Getting MySQL
  • Grouping of expressions
  • H

  • How to pronounce MySQL
  • I

  • Index
  • Indexes
  • isamchk
  • K

  • kewords
  • Keys
  • L

  • Linking
  • M

  • make_binary_release
  • Manual information
  • Memory use
  • msql2mysql
  • multi-part-index
  • mysql
  • MySQL
  • MySQL binary
  • MySQL mailing lists
  • MySQL mailing lists, un/subscribing to
  • MySQL source
  • MySQL version, MySQL version
  • mysql_install_db
  • mysqlaccess
  • mysqladmin
  • mysqlbug
  • mysqld
  • mysqldump
  • mysqlimport
  • mysqlshow
  • N

  • Net etiquette, Net etiquette
  • O

  • ODBC
  • optimizations
  • P

  • Pack-ISAM
  • Paying
  • Performance
  • Protocol mismatch
  • Q

  • Quoting of binary data
  • R

  • Release numbers
  • Release policy
  • replace
  • Replication
  • Reporting errors
  • Reserved words
  • S

  • safe_mysqld
  • Server functions
  • Size of tables
  • Stability
  • Startup parameters
  • Strings, How to escape things
  • Support
  • Symbolic links
  • T

  • Table size
  • The table is full
  • TODO
  • Type conversions
  • Type portability
  • Types, Choosing
  • U

  • Update log
  • V

  • Version, Choosing
  • Version, Latest
  • W

  • Which languages are supported by MySQL?
  • Windows
  • Y

  • Year 2000 compliance

  • This document was generated on 8 July 1998 using the texi2html translator version 1.52 (extended by davida@detron.se).