MySQL Reference Manual

MySQL Information Sources

MySQL Portals

The MySQL Portals (http://www.mysql.com/portal/) represent the ultimate resource to find MySQL AB Partners, as well as books, or other MySQL-related solutions that you may be looking for. Items are categorised and rated in order to make it easy for you to locate information.

By registering as a user, you will have the ability to comment on and rate items presented in portals. You will also receive relevant newsletters according to your user profile that you may update at any time.

Some of the current MySQL Portal categories include:

Partners

Find MySQL AB partners worldwide.

Books

Comment on, vote for, and buy books related to MySQL.

Development

Various links to different sites that are using MySQL server for different purposes, with a description of each site. This information can give you an idea of who uses the MySQL database software and how MySQL server can fulfill requirements.

Let us know about your site or success story, too! Visit http://www.mysql.com/feedback/testimonial.php.

Software

Find, buy, and download several applications and wrappers that make use of the MySQL server.

Distributions

From here you can find the various Linux distributions and other software packages that contain the MySQL software.

Service Providers

Companies providing MySQL-related services.

MySQL Mailing Lists

This section introduces you to the MySQL mailing lists, and gives some guidelines as to how to use them. By subscribing to a mailing list, you will receive as email messages all other postings on the list, and you will be able to send in your own questions and answers.

The MySQL Mailing Lists

To subscribe to the main MySQL mailing list, send a message to the electronic mail address [e-mail].

To unsubscribe from the main MySQL mailing list, send a message to the electronic mail address [e-mail].

Only the address to which you send your messages is significant. The subject line and the body of the message are ignored.

If your reply address is not valid, you can specify your address explicitly, by adding a hyphen to the subscribe or unsubscribe command word, followed by your address with the @ character in your address replaced by a =. For example, to subscribe [e-mail], send a message to mysql-subscribe-your_name=[e-mail].

Mail to [e-mail] or [e-mail] is handled automatically by the ezmlm mailing list processor. Information about ezmlm is available at the ezmlm web site (http://www.ezmlm.org/).

To post a message to the list itself, send your message to [e-mail]. However, please do not send mail about subscribing or unsubscribing to [e-mail] because any mail sent to that address is distributed automatically to thousands of other users.

Your local site may have many subscribers to [e-mail]. If so, it may have a local mailing list, so messages sent from lists.mysql.com to your site are propagated to the local list. In such cases, please contact your system administrator to be added to or dropped from the local MySQL list.

If you wish to have traffic for a mailing list go to a separate mailbox in your mail program, set up a filter based on the message headers. You can use either the List-ID: or Delivered-To: headers to identify list messages.

The following MySQL mailing lists exist:

[e-mail] announce

This is for announcement of new versions of MySQL and related programs. This is a low-volume list all MySQL users should subscribe to.

[e-mail] mysql

The main list for general MySQL discussion. Please note that some topics are better discussed on the more-specialised lists. If you post to the wrong list, you may not get an answer!

[e-mail] mysql-digest

The mysql list in digest form. That means you get all individual messages, sent as one large mail message once a day.

[e-mail] bugs

On this list you should only post a full, repeatable bug report using the mysqlbug script (if you are running on Windows, you should include a description of the operating system and the MySQL version). Preferably, you should test the problem using the latest stable or development version of MySQL server before posting! Anyone should be able to repeat the bug by just using mysql test < script on the included test case. All bugs posted on this list will be corrected or documented in the next MySQL release! If only small code changes are needed, we will also post a patch that fixes the problem.

[e-mail] bugs-digest

The bugs list in digest form.

[e-mail] internals

A list for people who work on the MySQL code. On this list one can also discuss MySQL development and post patches.

[e-mail] internals-digest

A digest version of the internals list.

[e-mail] java

Discussion about the MySQL server and Java. Mostly about the JDBC drivers.

[e-mail] java-digest

A digest version of the java list.

[e-mail] win32

All things concerning the MySQL software on Microsoft operating systems such as Windows 9x/Me/NT/2000/XP.

[e-mail] win32-digest

A digest version of the win32 list.

[e-mail] myodbc

All things about connecting to the MySQL server with ODBC.

[e-mail] myodbc-digest

A digest version of the myodbc list.

[e-mail] mycc

All things about the MySQL MyCC graphical client.

[e-mail] mycc-digest

A digest version of the mycc list.

[e-mail] plusplus

All things concerning programming with the C++ API to MySQL.

[e-mail] plusplus-digest

A digest version of the plusplus list.

[e-mail] msql-mysql-modules

A list about the Perl support for MySQL with msql-mysql-modules.

[e-mail] msql-mysql-modules-digest

A digest version of the msql-mysql-modules list.

You subscribe or unsubscribe to all lists in the same way as described previously. In your subscribe or unsubscribe message, just put the appropriate mailing list name rather than mysql. For example, to subscribe to or unsubscribe from the myodbc list, send a message to [e-mail] or [e-mail].

If you can’t get an answer for your questions from the mailing list, one option is to pay for support from MySQL AB, which will put you in direct contact with MySQL developers. See Section 1.4.1.

The following table shows some MySQL mailing in languages other than English. Note that these are not operated by MySQL AB, so we can’t guarantee the quality on these.

[e-mail]

A French mailing list

[e-mail]

A Korean mailing list. Email subscribe mysql [e-mail] to this list.

[e-mail]

A German mailing list. Email subscribe mysql-de [e-mail] to this list. You can find information about this mailing list at http://www.4t2.com/mysql/.

[e-mail]

A Portugese mailing list. Email subscribe mysql-br [e-mail] to this list.

[e-mail]

A Spanish mailing list. Email subscribe mysql [e-mail] to this list.

Asking questions or reporting bugs

Before posting a bug report or question, please do the following:

• Start by searching the MySQL online manual at:

  http://www.mysql.com/doc/

  We try to keep the manual up to date by updating it frequently with solutions to newly found problems!

• Search the MySQL mailing list archives:

  http://lists.mysql.com/

• You can also use http://www.mysql.com/search.html to search all the web pages (including the manual) that are located at http://www.mysql.com/.

If you can’t find an answer in the manual or the archives, check with your local MySQL expert. If you still can’t find an answer to your question, go ahead and read the next section about how to send mail to [e-mail].

How to report bugs or problems

Writing a good bug report takes patience, but doing it right the first time saves time for us and for you. A good bug report containing a full test case for the bug will make it very likely that we will fix it in the next release. This section will help you write your report correctly so that you don’t waste your time doing things that may not help us much or at all.

We encourage everyone to use the mysqlbug script to generate a bug report (or a report about any problem), if possible. mysqlbug can be found in the scripts directory in the source distribution, or for a binary distribution, in the bin directory under your MySQL installation directory. If you are unable to use mysqlbug, you should still include all the necessary information listed in this section.

The mysqlbug script helps you generate a report by determining much of the following information automatically, but if something important is missing, please include it with your message! Please read this section carefully and make sure that all the information described here is included in your report.

The normal place to report bugs and problems is [e-mail]. If you can make a test case that clearly demonstrates the bug, you should post it to the [e-mail] list. Note that on this list you should only post a full, repeatable bug report using the mysqlbug script. If you are running on Windows, you should include a description of the operating system and the MySQL version. Preferably, you should test the problem using the latest stable or development version of MySQL server before posting! Anyone should be able to repeat the bug by just using "mysql test < script" on the included test case or run the shell or Perl script that is included in the bug report. All bugs posted on the bugs list will be corrected or documented in the next MySQL release! If only small code changes are needed to correct this problem, we will also post a patch that fixes the problem.

If you have found a sensitive security bug in MySQL, you should send an email to [e-mail].

Remember that it is possible to respond to a message containing too much information, but not to one containing too little. Often people omit facts because they think they know the cause of a problem and assume that some details don’t matter. A good principle is: if you are in doubt about stating something, state it! It is a thousand times faster and less troublesome to write a couple of lines more in your report than to be forced to ask again and wait for the answer because you didn’t include enough information the first time.

The most common errors are that people don’t indicate the version number of the MySQL distribution they are using, or don’t indicate what platform they have the MySQL server installed on (including the platform version number). This is highly relevant information, and in 99 cases out of 100 the bug report is useless without it! Very often we get questions like, “Why doesn’t this work for me?” Then we find that the feature requested wasn’t implemented in that MySQL version, or that a bug described in a report has been fixed already in newer MySQL versions. Sometimes the error is platform-dependent; in such cases, it is next to impossible to fix anything without knowing the operating system and the version number of the platform.

Remember also to provide information about your compiler, if it is related to the problem. Often people find bugs in compilers and think the problem is MySQL-related. Most compilers are under development all the time and become better version by version. To determine whether your problem depends on your compiler, we need to know what compiler is used. Note that every compiling problem should be regarded as a bug report and reported accordingly.

It is most helpful when a good description of the problem is included in the bug report. That is, a good example of all the things you did that led to the problem and the problem itself exactly described. The best reports are those that include a full example showing how to reproduce the bug or problem. See Section D.1.6.

If a program produces an error message, it is very important to include the message in your report! If we try to search for something from the archives using programs, it is better that the error message reported exactly matches the one that the program produces. (Even the case should be observed!) You should never try to remember what the error message was; instead, copy and paste the entire message into your report!

If you have a problem with MyODBC, you should try to generate a MyODBC trace file. See Section 8.3.7.

Please remember that many of the people who will read your report will do so using an 80-column display. When generating reports or examples using the mysql command line tool, you should therefore use the --vertical option (or the \G statement terminator) for output that would exceed the available width for such a display (for example, with the EXPLAIN SELECT statement; see the example later in this section).

Please include the following information in your report:

• The version number of the MySQL distribution you are using (for example, MySQL Version 3.22.22). You can find out which version you are running by executing mysqladmin version. mysqladmin can be found in the bin directory under your MySQL installation directory.

• The manufacturer and model of the machine you are working on.

• The operating system name and version. For most operating systems, you can get this information by executing the Unix command uname -a.

• Sometimes the amount of memory (real and virtual) is relevant. If in doubt, include these values.

• If you are using a source distribution of the MySQL software, the name and version number of the compiler used is needed. If you have a binary distribution, the distribution name is needed.

• If the problem occurs during compilation, include the exact error message(s) and also a few lines of context around the offending code in the file where the error occurred.

• If mysqld died, you should also report the query that crashed mysqld. You can usually find this out by running mysqld with logging enabled. See Section D.1.5.

• If any database table is related to the problem, include the output from mysqldump --no-data db_name tbl_name1 tbl_name2 .... This is very easy to do and is a powerful way to get information about any table in a database that will help us create a situation matching the one you have.

• For speed-related bugs or problems with SELECT statements, you should always include the output of EXPLAIN SELECT ..., and at least the number of rows that the SELECT statement produces. The more information you give about your situation, the more likely it is that someone can help you! For example, the following is an example of a very good bug report (it should of course be posted with the mysqlbug script):

  Example run using the mysql command line tool (note the use of the \G statement terminator for statements whose output width would otherwise exceed that of an 80-column display device):

  mysql> SHOW VARIABLES;
  mysql> SHOW COLUMNS FROM ...\G
         <output from SHOW COLUMNS>
  mysql> EXPLAIN SELECT ...\G
         <output from EXPLAIN>
  mysql> FLUSH STATUS;
  mysql> SELECT ...;
         <A short version of the output from SELECT,
         including the time taken to run the query>
  mysql> SHOW STATUS;
         <output from SHOW STATUS>

• If a bug or problem occurs while running mysqld, try to provide an input script that will reproduce the anomaly. This script should include any necessary source files. The more closely the script can reproduce your situation, the better. If you can make a reproduceable test case, you should post this to [e-mail] for a high-priority treatment!

  If you can’t provide a script, you should at least include the output from mysqladmin variables extended-status processlist in your mail to provide some information of how your system is performing!

• If you can’t produce a test case in a few rows, or if the test table is too big to be mailed to the mailing list (more than 10 rows), you should dump your tables using mysqldump and create a README file that describes your problem.

  Create a compressed archive of your files using tar and gzip or zip, and use ftp to transfer the archive to ftp://support.mysql.com/pub/mysql/secret/. Then send a short description of the problem to [e-mail].

• If you think that the MySQL server produces a strange result from a query, include not only the result, but also your opinion of what the result should be, and an account describing the basis for your opinion.

• When giving an example of the problem, it’s better to use the variable names, table names, etc., that exist in your actual situation than to come up with new names. The problem could be related to the name of a variable or table! These cases are rare, perhaps, but it is better to be safe than sorry. After all, it should be easier for you to provide an example that uses your actual situation, and it is by all means better for us. In case you have data you don’t want to show to others, you can use ftp to transfer it to ftp://support.mysql.com/pub/mysql/secret/. If the data is really top secret and you don’t want to show it even to us, then go ahead and provide an example using other names, but please regard this as the last choice.

• Include all the options given to the relevant programs, if possible. For example, indicate the options that you use when you start the mysqld daemon and that you use to run any MySQL client programs. The options to programs like mysqld and mysql, and to the configure script, are often keys to answers and are very relevant! It is never a bad idea to include them anyway! If you use any modules, such as Perl or PHP, please include the version number(s) of those as well.

• If your question is related to the privilege system, please include the output of mysqlaccess, the output of mysqladmin reload, and all the error messages you get when trying to connect! When you test your privileges, you should first run mysqlaccess. After this, execute mysqladmin reload version and try to connect with the program that gives you trouble. mysqlaccess can be found in the bin directory under your MySQL installation directory.

• If you have a patch for a bug, that is good. But don’t assume the patch is all we need, or that we will use it, if you don’t provide some necessary information such as test cases showing the bug that your patch fixes. We might find problems with your patch or we might not understand it at all; if so, we can’t use it.

  If we can’t verify exactly what the patch is meant for, we won’t use it. Test cases will help us here. Show that the patch will handle all the situations that may occur. If we find a borderline case (even a rare one) where the patch won’t work, it may be useless.

• Guesses about what the bug is, why it occurs, or what it depends on are usually wrong. Even the MySQL team can’t guess such things without first using a debugger to determine the real cause of a bug.

• Indicate in your mail message that you have checked the reference manual and mail archive so that others know you have tried to solve the problem yourself.

• If you get a parse error, please check your syntax closely! If you can’t find something wrong with it, it’s extremely likely that your current version of MySQL server doesn’t support the query you are using. If you are using the current version and the manual at http://www.mysql.com/doc/ doesn’t cover the syntax you are using, MySQL server doesn’t support your query. In this case, your only options are to implement the syntax yourself or email [e-mail] and ask for an offer to implement it!

  If the manual covers the syntax you are using, but you have an older version of MySQL server, you should check the MySQL change history to see when the syntax was implemented. In this case, you have the option of upgrading to a newer version of MySQL server. See http://www.mysql.com/doc/N/e/News.html.

• If you have a problem such that your data appears corrupt or you get errors when you access some particular table, you should first check and then try repairing your tables with myisamchk or CHECK TABLE and REPAIR TABLE. See Chapter 4.

• If you often get corrupted tables you should try to find out when and why this happens. In this case, the mysql-data-directory/'hostname'.err file may contain some information about what happened. See Section 4.9.1. Please include any relevant information from this file in your bug report. Normally mysqld should never crash a table if nothing killed it in the middle of an update! If you can find the cause of mysqld dying, it’s much easier for us to provide you with a fix for the problem. See Section A.1.

• If possible, download and install the most recent version of MySQL server and check whether it solves your problem. All versions of the MySQL software are thoroughly tested and should work without problems. We believe in making everything as backward-compatible as possible, and you should be able to switch MySQL versions without any hassle. See Section 2.2.3.

If you are a support customer, please cross-post the bug report to [e-mail] for higher-priority treatment, as well as to the appropriate mailing list to see if someone else has experienced (and perhaps solved) the problem.

For information on reporting bugs in MyODBC, see Section 8.3.4.

For solutions to some common problems, see Appendix A.

When answers are sent to you individually and not to the mailing list, it is considered good etiquette to summarise the answers and send the summary to the mailing list so that others may have the benefit of responses you received that helped you solve your problem!

Guidelines for answering questions on the mailing list

If you consider your answer to have broad interest, you may want to post it to the mailing list instead of replying directly to the individual who asked. Try to make your answer general enough that people other than the original poster may benefit from it. When you post to the list, please make sure that your answer is not a duplication of a previous answer.

Try to summarise the essential part of the question in your reply; don’t feel obliged to quote the entire original message.

Please don’t post mail messages from your browser with HTML mode turned on! Many users don’t read mail with a browser!