A. Problems and Common Errors

This chapter lists some common problems and error messages that users have run into. You will learn how to figure out what the problem is, and what to do to solve it. You will also find proper solutions to some common problems.

A.1 How to Determine What Is Causing Problems

When you run into problems, the first thing you should do is to find out which program / piece of equipment is causing problems:

• If you have one of the following symptoms, then it is probably a hardware (like memory, motherboard, CPU, or hard disk) or kernel problem:
  • - The keyboard doesn't work. This can normally be checked by pressing Caps Lock. If the Caps Lock light doesn't change you have to replace your keyboard. (Before doing this, you should try to reboot your computer and check all cables to the keyboard.)
  • - The mouse pointer doesn't move.
  • - The machine doesn't answer to a remote machine's pings.
  • - Different, unrelated programs don't behave correctly.
  • - If your system rebooted unexpectedly (a faulty user level program should never be able to take down your system).

  In this case you should start by checking all your cables and run some diagnostic tool to check your hardware! You should also check if there are any patches, updates, or service packs for your operating system that could likely solve your problems. Check also that all your libraries (like glibc) are up to date.

  It's always good to use a machine with ECC memory to discover memory problems early!

• If your keyboard is locked up, you may be able to fix this by logging into your machine from another machine and execute kbd_mode -a on it.
• Please examine your system log file (/var/log/messages or similar) for reasons for your problems. If you think the problem is in MySQL then you should also examine MySQL's log files. See section The Binary Log.
• If you don't think you have hardware problems, you should try to find out which program is causing problems.

  Try using top, ps, taskmanager, or some similar program, to check which program is taking all CPU or is locking the machine.

• Check with top, df, or a similar program if you are out of memory, disk space, open files, or some other critical resource.
• If the problem is some runaway process, you can always try to kill it. If it doesn't want to die, there is probably a bug in the operating system.

If after you have examined all other possibilities and you have concluded that it's the MySQL server or a MySQL client that is causing the problem, it's time to do a bug report for our mailing list or our support team. In the bug report, try to give a very detailed description of how the system is behaving and what you think is happening. You should also state why you think it's MySQL that is causing the problems. Take into consideration all the situations in this chapter. State any problems exactly how they appear when you examine your system. Use the 'cut and paste' method for any output and/or error messages from programs and/or log files!

Try to describe in detail which program is not working and all symptoms you see! We have in the past received many bug reports that just state "the system doesn't work". This doesn't provide us with any information about what could be the problem.

If a program fails, it's always useful to know:

• Has the program in question made a segmentation fault (core dumped)?
• Is the program taking up the whole CPU? Check with top. Let the program run for a while, it may be evaluating something heavy.
• If it's the mysqld server that is causing problems, can you do mysqladmin -u root ping or mysqladmin -u root processlist?
• What does a client program say (try with mysql, for example) when you try to connect to the MySQL server? Does the client jam? Do you get any output from the program?

When sending a bug report, you should of follow the outlines described in this manual. See section Asking Questions or Reporting Bugs.

A.2 Common Errors When Using MySQL

This section lists some errors that users frequently get. You will find descriptions of the errors, and how to solve the problem here.

A.2.1 Access denied Error

See section Causes of Access denied Errors. See section How the Privilege System Works.

A.2.2 MySQL server has gone away Error

This section also covers the related Lost connection to server during query error.

The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection. By default, the server closes the connection after 8 hours if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld.

Another common reason to receive the MySQL server has gone away error is because you have issued a "close" on your MySQL connection and then tried to run a query on the closed connection.

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

You normally can get the following error codes in this case (which one you get is OS-dependent):

You will also get this error if someone has kills the running thread with kill #threadid#.

You can check that the MySQL hasn't died by executing mysqladmin version and examining the uptime. If the problem is that mysqld crashed you should concentrate one finding the reason for the crash. You should in this case start by checking if issuing the query again will kill MySQL again. See section What To Do If MySQL Keeps Crashing.

You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld gets a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by starting mysqld with the -O max_allowed_packet=# option (default 1M). The extra memory is allocated on demand, so mysqld will allocate more memory only when you issue a big query or when mysqld must return a big result row!

You will also get a lost connection if you are sending a packet >= 16M if your client is older than 4.0.8 and your server is 4.0.8 and above, or the other way around.

If you want to make a bug report regarding this problem, be sure that you include the following information:

• Include information if MySQL died or not. (You can find this in the hostname.err file. See section What To Do If MySQL Keeps Crashing.
• If a specific query kills mysqld and the involved tables where checked with CHECK TABLE before you did the query, can you do a test case for this? See section Making a Test Case If You Experience Table Corruption.
• What is the value of the wait_timeout variable in the MySQL server ? mysqladmin variables gives you the value of this
• Have you tried to run mysqld with --log and check if the issued query appears in the log ?

See section Asking Questions or Reporting Bugs.

A.2.3 Can't connect to [local] MySQL server Error

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

On Windows, if the mysqld server is running on 9x/Me, you can connect only via TCP/IP. If the server is running on NT/2000/XP and mysqld is started with --enable-named-pipe, you can also connect with named pipes. The name of the named pipe is MySQL. If you don't give a hostname when connecting to mysqld, a MySQL client will first try to connect to the named pipe, and if this doesn't work it will connect to the TCP/IP port. You can force the use of named pipes on Windows by using . as the hostname.

The error (2002) Can't connect to ... normally means that there isn't a MySQL server running on the system or that you are using a wrong socket file or TCP/IP port when trying to connect to the mysqld server.

Start by checking (using ps or the task manager on Windows) that there is a process running named mysqld on your server! If there isn't any mysqld process, you should start one. See section Problems Starting the MySQL Server.

If a mysqld process is running, you can check the server by trying these different connections (the port number and socket pathname might be different in your setup, of course):

shell> mysqladmin version
shell> mysqladmin variables
shell> mysqladmin -h `hostname` version variables
shell> mysqladmin -h `hostname` --port=3306 version
shell> mysqladmin -h 'ip for your host' version
shell> mysqladmin --protocol=socket --socket=/tmp/mysql.sock version

Note the use of backquotes rather than forward quotes with the hostname command; these cause the output of hostname (that is, the current hostname) to be substituted into the mysqladmin command.

Here are some reasons the Can't connect to local MySQL server error might occur:

• mysqld is not running.
• You are running on a system that uses MIT-pthreads. If you are running on a system that doesn't have native threads, mysqld uses the MIT-pthreads package. See section Operating Systems Supported by MySQL. However, not all MIT-pthreads versions support Unix sockets. On a system without sockets support you must always specify the hostname explicitly when connecting to the server. Try using this command to check the connection to the server:

  shell> mysqladmin -h `hostname` version

• Someone has removed the Unix socket that mysqld uses (default `/tmp/mysqld.sock'). You might have a cron job that removes the MySQL socket (for example, a job that removes old files from the `/tmp' directory). You can always run mysqladmin version and check that the socket mysqladmin is trying to use really exists. The fix in this case is to change the cron job to not remove `mysqld.sock' or to place the socket somewhere else. See section How to Protect or Change the MySQL Socket File `/tmp/mysql.sock'.
• You have started the mysqld server with the --socket=/path/to/socket option. If you change the socket pathname for the server, you must also notify the MySQL clients about the new path. You can do this by providing the socket path as an argument to the client. See section How to Protect or Change the MySQL Socket File `/tmp/mysql.sock'.
• You are using Linux and one thread has died (core dumped). In this case you must kill the other mysqld threads (for example, with the mysql_zap script before you can start a new MySQL server. See section What To Do If MySQL Keeps Crashing.
• You may not have read and write privilege to either the directory that holds the socket file or privilege to the socket file itself. In this case you have to either change the privilege for the directory / file or restart mysqld so that it uses a directory that you can access.

If you get the error message Can't connect to MySQL server on some_hostname, you can try the following things to find out what the problem is :

• Check if the server is up by doing telnet your-host-name tcp-ip-port-number and press Enter a couple of times. If there is a MySQL server running on this port you should get a responses that includes the version number of the running MySQL server. If you get an error like telnet: Unable to connect to remote host: Connection refused, then there is no server running on the given port.
• Try connecting to the mysqld daemon on the local machine and check the TCP/IP port that mysqld it's configured to use (variable port) with mysqladmin variables.
• Check that your mysqld server is not started with the --skip-networking option.

A.2.4 Client does not support authentication protocol error

MySQL 4.1 uses an authentication protocal based on a password hashing algorithm that is incompatible with that used by older clients. If you upgrade the server to 4.1, attempts to connect to a it with an older client may fail with the following message:

shell> mysql
Client does not support authentication protocol requested
by server; consider upgrading MySQL client

To solve this problem, you need to either tell the server to use the older password hashing algorithm, or upgrade the client programs to use the 4.1 client library. For background on password hashing and authentication, see Password Hashing in MySQL 4.1.

A.2.5 Host '...' is blocked Error

If you get an error like this:

Host 'hostname' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'

this means that mysqld has gotten a lot (max_connect_errors) of connect requests from the host 'hostname' that have been interrupted in the middle. After max_connect_errors failed requests, mysqld assumes that something is wrong (like an attack from a cracker), and blocks the site from further connections until someone executes the command mysqladmin flush-hosts.

By default, mysqld blocks a host after 10 connection errors. You can easily adjust this by starting the server like this:

shell> mysqld_safe -O max_connect_errors=10000 &

Note that if you get this error message for a given host, you should first check that there isn't anything wrong with TCP/IP connections from that host. If your TCP/IP connections aren't working, it won't do you any good to increase the value of the max_connect_errors variable!

A.2.6 Too many connections Error

If you get the error Too many connections when you try to connect to MySQL, this means that there is already max_connections clients connected to the mysqld server.

If you need more connections than the default (100), then you should restart mysqld with a bigger value for the max_connections variable.

Note that mysqld actually allows (max_connections+1) clients to connect. The last connection is reserved for a user with the SUPER privilege. By not giving this privilege to normal users (they shouldn't need this), an administrator with this privilege can log in and use SHOW PROCESSLIST to find out what could be wrong. See section SHOW PROCESSLIST.

The maximum number of connects MySQL is depending on how good the thread library is on a given platform. Linux or Solaris should be able to support 500-1000 simultaneous connections, depending on how much RAM you have and what your clients are doing.

A.2.7 Some non-transactional changed tables couldn't be rolled back Error

If you get the error/warning: Warning: Some non-transactional changed tables couldn't be rolled back when trying to do a ROLLBACK, this means that some of the tables you used in the transaction didn't support transactions. These non-transactional tables will not be affected by the ROLLBACK statement.

The most typical case when this happens is when you have tried to create a table of a type that is not supported by your mysqld binary. If mysqld doesn't support a table type (or if the table type is disabled by a startup option) , it will instead create the table type with the table type that is most resembles to the one you requested, probably MyISAM.

You can check the table type for a table by doing:

SHOW TABLE STATUS LIKE 'table_name'. See section SHOW TABLE STATUS.

You can check the extensions your mysqld binary supports by doing:

show variables like 'have_%'. See section SHOW VARIABLES.

A.2.8 Out of memory Error

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

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

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

To remedy the problem, first check that your query is correct. Is it reasonable that it should return so many rows? If so, you can use mysql --quick, which uses mysql_use_result() to retrieve the result set. This places less of a load on the client (but more on the server).

A.2.9 Packet too large Error

When a MySQL client or the mysqld server gets a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection.

In MySQL 3.23 the biggest possible packet is 16M (due to limits in the client/server protocol). In MySQL 4.0.1 and up, this is only limited by the amount on memory you have on your server (up to a theoretical maximum of 2G).

A communication packet is a single SQL statement sent to the MySQL server or a single row that is sent to the client.

When a MySQL client or the mysqld server gets a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection. With some clients, you may also get Lost connection to MySQL server during query error if the communication packet is too big.

Note that both the client and the server has it's own max_allowed_packet variable. If you want to handle big packets, you have to increase this variable both in the client and in the server.

It's safe to increase this variable as memory is only allocated when needed; this variable is more a precaution to catch wrong packets between the client/server and also to ensure that you don't accidentally use big packets so that you run out of memory.

If you are using the mysql client, you may specify a bigger buffer by starting the client with mysql --set-variable=max_allowed_packet=8M. Other clients have different methods to set this variable. Please note that --set-variable is deprecated since MySQL 4.0, just use --max-allowed-packet=8M instead.

You can use the option file to set max_allowed_packet to a larger size in mysqld. For example, if you are expecting to store the full length of a MEDIUMBLOB into a table, you'll need to start the server with the set-variable=max_allowed_packet=16M option.

You can also get strange problems with large packets if you are using big blobs, but you haven't given mysqld access to enough memory to handle the query. If you suspect this is the case, try adding ulimit -d 256000 to the beginning of the mysqld_safe script and restart mysqld.

A.2.10 Communication Errors / Aborted Connection

Starting with MySQL 3.23.40 you only get the Aborted connection error of you start mysqld with --warnings.

If you find errors like the following in your error log.

010301 14:38:23  Aborted connection 854 to db: 'users' user: 'josh'

See section The Error Log.

This means that something of the following has happened:

• The client program did not call mysql_close() before exit.
• The client had been sleeping more than wait_timeout or interactive_timeout without doing any requests. See section wait_timeout. See section interactive_timeout.
• The client program ended abruptly in the middle of the transfer.

When the above happens, the server variable Aborted_clients is incremented.

The server variable Aborted_connects is incremented when:

• When a connection packet doesn't contain the right information.
• When the user didn't have privileges to connect to a database.
• When a user uses a wrong password.
• When it takes more than connect_timeout seconds to get a connect package. See section connect_timeout.

Note that the above could indicate that someone is trying to break into your database!

Other reasons for problems with Aborted clients / Aborted connections.

• Usage of Ethernet protocol with Linux, both half and full duplex. Many Linux Ethernet drivers have this bug. You should test for this bug by transferring a huge file via ftp between these two machines. If a transfer goes in burst-pause-burst-pause ... mode then you are experiencing a Linux duplex syndrome. The only solution is switching duplex mode for both your network card and Hub/Switch to either full duplex or to half duplex and testing the results to decide on the best setting.
• Some problem with the thread library that causes interrupts on reads.
• Badly configured TCP/IP.
• Faulty Ethernets or hubs or switches, cables ... This can be diagnosed properly only by replacing hardware.
• max_allowed_packet is too small or queries require more memory than you have allocated for mysqld. See section Packet too large Error.

A.2.11 The table is full Error

There are a couple of different cases when you can get this error:

• You are using an older MySQL version (before 3.23.0) when an in-memory temporary table becomes larger than tmp_table_size bytes. To avoid this problem, you can use the -O tmp_table_size=# option to make mysqld increase the temporary table size or use the SQL option BIG_TABLES before you issue the problematic query. See section SET.

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

  In MySQL Version 3.23, in-memory temporary tables will automatically be converted to a disk-based MyISAM table after the table size gets bigger than tmp_table_size.

• You are using InnoDB tables and run out of room in the InnoDB tablespace. In this case the solution is to extend the InnoDB tablespace.
• You are using ISAM or MyISAM tables on an OS that only supports files of 2G in size and you have hit this limit for the datafile or index file.
• You are using MyISAM tables and the needed data or index size is bigger than what MySQL has allocated pointers for. (If you don't specify MAX_ROWS to CREATE TABLE MySQL will only allocate pointers to hold 4G of data).

  You can check the maximum data/index sizes by doing

  SHOW TABLE STATUS FROM database LIKE 'table_name';

  or using myisamchk -dv database/table_name.

  If this is the problem, you can fix it by doing something like:

  ALTER TABLE table_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

  You only have to specify AVG_ROW_LENGTH for tables with BLOB/TEXT fields as in this case MySQL can't optimise the space required based only on the number of rows.

A.2.12 Can't create/write to file Error

If you get an error for some queries of type:

Can't create/write to file '\\sqla3fe_0.ism'.

this means that MySQL can't create a temporary file for the result set in the given temporary directory. (The above error is a typical error message on Windows, and the Unix error message is similar.) The fix is to start mysqld with --tmpdir=path or to add to your option file:

[mysqld]
tmpdir=C:/temp

assuming that the `c:\\temp' directory exists. See section `my.cnf' Option Files.

Check also the error code that you get with perror. One reason may also be a disk full error;

shell> perror 28
Error code  28:  No space left on device

A.2.13 Commands out of sync Error in Client

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

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

A.2.14 Ignoring user Error

If you get the following error:

Found wrong password for user: 'some_user@some_host'; ignoring user

this means that when mysqld was started or when it reloaded the permissions tables, it found an entry in the user table with an invalid password. As a result, the entry is simply ignored by the permission system.

Possible causes of and fixes for this problem:

• You may be running a new version of mysqld with an old user table. You can check this by executing mysqlshow mysql user to see if the password field is shorter than 16 characters. If so, you can correct this condition by running the scripts/add_long_password script.
• The user has an old password (8 characters long) and you didn't start mysqld with the --old-protocol option. Update the user in the user table with a new password or restart mysqld with --old-protocol.
• You have specified a password in the user table without using the PASSWORD() function. Use mysql to update the user in the user table with a new password. Make sure to use the PASSWORD() function:

  mysql> UPDATE user SET password=PASSWORD('your password') -> WHERE user='XXX';

A.2.15 Table 'xxx' doesn't exist Error

If you get the error Table 'xxx' doesn't exist or Can't find file: 'xxx' (errno: 2), this means that no table exists in the current database with the name xxx.

Note that as MySQL uses directories and files to store databases and tables, the database and table names are case-sensitive! (On Windows the databases and tables names are not case-sensitive, but all references to a given table within a query must use the same case!)

You can check which tables you have in the current database with SHOW TABLES. See section SHOW.

A.2.16 Can't initialize character set xxx error

If you get an error like:

MySQL Connection Failed: Can't initialize character set xxx

This means one of the following things:

• The character set is a multi-byte character set and you have no support for the character set in the client.

  In this case you need to recompile the client with --with-charset=xxx or with --with-extra-charsets=xxx. See section Typical configure Options.

  All standard MySQL binaries are compiled with --with-extra-character-sets=complex which will enable support for all multi-byte character sets. See section The Character Set Used for Data and Sorting.

• The character set is a simple character set which is not compiled into mysqld and the character set definition files are not in the place where the client expects to find them.

  In this case you need to:

  • Recompile the client with support for the character set. See section Typical configure Options.
  • Specify to the client where the character set definition files are. For many clients you can do this with the --character-sets-dir=path-to-charset-dir option.
  • Copy the character definition files to the path where the client expects them to be.

A.2.17 File Not Found

If you get ERROR '...' not found (errno: 23), Can't open file: ... (errno: 24), or any other error with errno 23 or errno 24 from MySQL, it means that you haven't allocated enough file descriptors for MySQL. You can use the perror utility to get a description of what the error number means:

shell> perror 23
File table overflow
shell> perror 24
Too many open files
shell> perror 11
Resource temporarily unavailable

The problem here is that mysqld is trying to keep open too many files simultaneously. You can either tell mysqld not to open so many files at once or increase the number of file descriptors available to mysqld.

To tell mysqld to keep open fewer files at a time, you can make the table cache smaller by using the -O table_cache=32 option to mysqld_safe (the default value is 64). Reducing the value of max_connections will also reduce the number of open files (the default value is 90).

To change the number of file descriptors available to mysqld, you can use the option --open-files-limit=# to mysqld_safe or -O open-files-limit=# to mysqld. See section open_files_limit. The easiest way to do that is to add the option to your option file. See section `my.cnf' Option Files. If you have an old mysqld version that doesn't support this, you can edit the mysqld_safe script. There is a commented-out line ulimit -n 256 in the script. You can remove the '#' character to uncomment this line, and change the number 256 to affect the number of file descriptors available to mysqld.

ulimit (and open-files-limit) can increase the number of file descriptors, but only up to the limit imposed by the operating system. There is also a 'hard' limit that can only be overridden if you start mysqld_safe or mysqld as root (just remember that you need to also use the --user=... option in this case). If you need to increase the OS limit on the number of file descriptors available to each process, consult the documentation for your operating system.

Note that if you run the tcsh shell, ulimit will not work! tcsh will also report incorrect values when you ask for the current limits! In this case you should start mysqld_safe with sh!

A.3 Installation Related Issues

A.3.1 Problems When Linking with the MySQL Client Library

If you are linking your program and you get errors for unreferenced symbols that start with mysql_, like the following:

/tmp/ccFKsdPa.o: In function `main':
/tmp/ccFKsdPa.o(.text+0xb): undefined reference to `mysql_init'
/tmp/ccFKsdPa.o(.text+0x31): undefined reference to `mysql_real_connect'
/tmp/ccFKsdPa.o(.text+0x57): undefined reference to `mysql_real_connect'
/tmp/ccFKsdPa.o(.text+0x69): undefined reference to `mysql_error'
/tmp/ccFKsdPa.o(.text+0x9a): undefined reference to `mysql_close'

you should be able to solve this by adding -Lpath-to-the-mysql-library -lmysqlclient last on your link line.

If you get undefined reference errors for the uncompress or compress function, add -lz last on your link line and try again!

If you get undefined reference errors for functions that should exist on your system, like connect, check the man page for the function in question, for which libraries you should add to the link line!

If you get undefined reference errors for functions that don't exist on your system, like the following:

mf_format.o(.text+0x201): undefined reference to `__lxstat'

it usually means that your library is compiled on a system that is not 100% compatible with yours. In this case you should download the latest MySQL source distribution and compile this yourself. See section Installing a MySQL Source Distribution.

If you are trying to run a program and you then get errors for unreferenced symbols that start with mysql_ or that the mysqlclient library can't be found, this means that your system can't find the share `libmysqlclient.so' library.

The fix for this is to tell your system to search after shared libraries where the library is located by one of the following methods:

• Add the path to the directory where you have `libmysqlclient.so' the LD_LIBRARY_PATH environment variable.
• Add the path to the directory where you have `libmysqlclient.so' the LD_LIBRARY environment variable.
• Copy `libmysqlclient.so' to some place that is searched by your system, like `/lib', and update the shared library information by executing ldconfig.

Another way to solve this problem is to link your program statically, with -static, or by removing the dynamic MySQL libraries before linking your code. In the second case you should be sure that no other programs are using the dynamic libraries!

A.3.2 How to Run MySQL As a Normal User

The MySQL server mysqld can be started and run by any user. In order to change mysqld to run as a Unix user user_name, you must do the following:

1. Stop the server if it's running (use mysqladmin shutdown).
2. Change the database directories and files so that user_name has privileges to read and write files in them (you may need to do this as the Unix root user):

   shell> chown -R user_name /path/to/mysql/datadir

   If directories or files within the MySQL data directory are symlinks, you'll also need to follow those links and change the directories and files they point to. chown -R may not follow symlinks for you.

3. Start the server as user user_name, or, if you are using MySQL Version 3.22 or later, start mysqld as the Unix root user and use the --user=user_name option. mysqld will switch to run as the Unix user user_name before accepting any connections.
4. To start the server as the given user name automatically at system startup time, add a user line that specifies the user name to the [mysqld] group of the `/etc/my.cnf' option file or the `my.cnf' option file in the server's data directory. For example:

   [mysqld] user=user_name

At this point, your mysqld process should be running fine and dandy as the Unix user user_name. One thing hasn't changed, though: the contents of the permissions tables. By default (right after running the permissions table install script mysql_install_db), the MySQL user root is the only user with permission to access the mysql database or to create or drop databases. Unless you have changed those permissions, they still hold. This shouldn't stop you from accessing MySQL as the MySQL root user when you're logged in as a Unix user other than root; just specify the -u root option to the client program.

Note that accessing MySQL as root, by supplying -u root on the command-line, has nothing to do with MySQL running as the Unix root user, or, indeed, as another Unix user. The access permissions and user names of MySQL are completely separate from Unix user names. The only connection with Unix user names is that if you don't provide a -u option when you invoke a client program, the client will try to connect using your Unix login name as your MySQL user name.

If your Unix box itself isn't secured, you should probably at least put a password on the MySQL root users in the access tables. Otherwise, any user with an account on that machine can run mysql -u root db_name and do whatever he likes.

A.3.3 Problems with File Permissions

If you have problems with file permissions, for example, if mysql issues the following error message when you create a table:

ERROR: Can't find file: 'path/with/filename.frm' (Errcode: 13)

then the environment variable UMASK might be set incorrectly when mysqld starts up. The default umask value is 0660. You can change this behaviour by starting mysqld_safe as follows:

shell> UMASK=384  # = 600 in octal
shell> export UMASK
shell> /path/to/mysqld_safe &

By default MySQL will create database and RAID directories with permission type 0700. You can modify this behaviour by setting the UMASK_DIR variable. If you set this, new directories are created with the combined UMASK and UMASK_DIR. For example, if you want to give group access to all new directories, you can do:

shell> UMASK_DIR=504  # = 770 in octal
shell> export UMASK_DIR
shell> /path/to/mysqld_safe &

In MySQL Version 3.23.25 and above, MySQL assumes that the value for UMASK and UMASK_DIR is in octal if it starts with a zero.

See section Environment Variables.

A.4 Administration Related Issues

A.4.1 What To Do If MySQL Keeps Crashing

All MySQL versions are tested on many platforms before they are released. This doesn't mean that there aren't any bugs in MySQL, but it means if there are bugs, they are very few and can be hard to find. If you have a problem, it will always help if you try to find out exactly what crashes your system, as you will have a much better chance of getting this fixed quickly.

First, you should try to find out whether the problem is that the mysqld daemon dies or whether your problem has to do with your client. You can check how long your mysqld server has been up by executing mysqladmin version. If mysqld has died, you may find the reason for this in the file `mysql-data-directory/`hostname`.err'. See section The Error Log.

On some systems you can find in this file a stack trace of where mysqld died that you can resolve with resolve_back_stack. See section Using a Stack Trace. Note that the variable values written in the .err file may not always be 100 percent correct.

Many crashes of MySQL are caused by corrupted index files or datafiles. MySQL will update the data on disk, with the write() system call, after every SQL statement and before the client is notified about the result. (This is not true if you are running with delay_key_write, in which case only the data is written.) This means that the data is safe even if mysqld crashes, as the OS will ensure that the not flushed data is written to disk. You can force MySQL to sync everything to disk after every SQL command by starting mysqld with --flush.

The above means that normally you shouldn't get corrupted tables unless:

• Someone/something killed mysqld or the machine in the middle of an update.
• You have found a bug in mysqld that caused it to die in the middle of an update.
• Someone is manipulating the data/index files outside of mysqld without locking the table properly.
• If you are running many mysqld servers on the same data on a system that doesn't support good filesystem locks (normally handled by the lockd daemon ) or if you are running multiple servers with --skip-external-locking
• You have a crashed index/datafile that contains very wrong data that got mysqld confused.
• You have found a bug in the data storage code. This isn't that likely, but it's at least possible. In this case you can try to change the file type to another storage engine by using ALTER TABLE on a repaired copy of the table!

Because it is very difficult to know why something is crashing, first try to check whether things that work for others crash for you. Please try the following things:

• Take down the mysqld daemon with mysqladmin shutdown, run myisamchk --silent --force */*.MYI on all tables, and restart the mysqld daemon. This will ensure that you are running from a clean state. See section Database Administration.
• Use mysqld --log and try to determine from the information in the log whether some specific query kills the server. About 95% of all bugs are related to a particular query! Normally this is one of the last queries in the log file just before MySQL restarted. See section The General Query Log. If you can repeatedly kill MySQL with one of the queries, even when you have checked all tables just before doing the query, then you have been able to locate the bug and should do a bug report for this! See section How to Report Bugs or Problems.
• Try to make a test case that we can use to reproduce the problem. See section Making a Test Case If You Experience Table Corruption.
• Try running the included mysql-test test and the MySQL benchmarks. See section MySQL Test Suite. They should test MySQL rather well. You can also add code to the benchmarks that simulates your application! The benchmarks can be found in the `bench' directory in the source distribution or, for a binary distribution, in the `sql-bench' directory under your MySQL installation directory.
• Try fork_test.pl and fork2_test.pl.
• If you configure MySQL for debugging, it will be much easier to gather information about possible errors if something goes wrong. Reconfigure MySQL with the --with-debug option or --with-debug=full to configure and then recompile. See section Debugging a MySQL server.
• Configuring MySQL for debugging causes a safe memory allocator to be included that can find some errors. It also provides a lot of output about what is happening.
• Have you applied the latest patches for your operating system?
• Use the --skip-external-locking option to mysqld. On some systems, the lockd lock manager does not work properly; the --skip-external-locking option tells mysqld not to use external locking. (This means that you cannot run 2 mysqld servers on the same data and that you must be careful if you use myisamchk, but it may be instructive to try the option as a test.)
• Have you tried mysqladmin -u root processlist when mysqld appears to be running but not responding? Sometimes mysqld is not comatose even though you might think so. The problem may be that all connections are in use, or there may be some internal lock problem. mysqladmin processlist will usually be able to make a connection even in these cases, and can provide useful information about the current number of connections and their status.
• Run the command mysqladmin -i 5 status or mysqladmin -i 5 -r status or in a separate window to produce statistics while you run your other queries.
• Try the following:
  1. Start mysqld from gdb (or in another debugger). See section Debugging mysqld under gdb.
  2. Run your test scripts.
  3. Print the backtrace and the local variables at the 3 lowest levels. In gdb you can do this with the following commands when mysqld has crashed inside gdb:

     backtrace info local up info local up info local

     With gdb you can also examine which threads exist with info threads and switch to a specific thread with thread #, where # is the thread id.

• Try to simulate your application with a Perl script to force MySQL to crash or misbehave.
• Send a normal bug report. See section How to Report Bugs or Problems. Be even more detailed than usual. Because MySQL works for many people, it may be that the crash results from something that exists only on your computer (for example, an error that is related to your particular system libraries).
• If you have a problem with tables with dynamic-length rows and you are not using BLOB/TEXT columns (but only VARCHAR columns), you can try to change all VARCHAR to CHAR with ALTER TABLE. This will force MySQL to use fixed-size rows. Fixed-size rows take a little extra space, but are much more tolerant to corruption!

  The current dynamic row code has been in use at MySQL AB for at least 3 years without any problems, but by nature dynamic-length rows are more prone to errors, so it may be a good idea to try the above to see if it helps!

A.4.2 How to Reset a Forgotten Root Password

If you never set a root password for MySQL, then the server will not require a password at all for connecting as root. It is recommended to always set a password for each user. See section How to Make MySQL Secure Against Crackers.

If you have set a root password, but forgot what it was, you can set a new password with the following procedure:

1. Take down the mysqld server by sending a kill (not kill -9) to the mysqld server. The pid is stored in a `.pid' file, which is normally in the MySQL database directory:

   shell> kill `cat /mysql-data-directory/hostname.pid`

   You must be either the Unix root user or the same user mysqld runs as to do this.

2. Restart mysqld with the --skip-grant-tables option.
3. Set a new password with the mysqladmin password command:

   shell> mysqladmin -u root password 'mynewpassword'

4. Now you can either stop mysqld and restart it normally, or just load the privilege tables with:

   shell> mysqladmin -h hostname flush-privileges

5. After this, you should be able to connect using the new password.

Alternatively, you can set the new password using the mysql client:

1. Take down and restart mysqld with the --skip-grant-tables option as described above.
2. Connect to the mysqld server with:

   shell> mysql -u root mysql

3. Issue the following commands in the mysql client:

   mysql> UPDATE user SET Password=PASSWORD('mynewpassword') -> WHERE User='root'; mysql> FLUSH PRIVILEGES;

4. After this, you should be able to connect using the new password.
5. You can now stop mysqld and restart it normally.

A.4.3 How MySQL Handles a Full Disk

When a disk-full condition occurs, MySQL does the following:

• It checks once every minute to see whether there is enough space to write the current row. If there is enough space, it continues as if nothing had happened.
• Every 6 minutes it writes an entry to the log file warning about the disk full condition.

To alleviate the problem, you can take the following actions:

• To continue, you only have to free enough disk space to insert all records.
• To abort the thread, you must send a mysqladmin kill to the thread. The thread will be aborted the next time it checks the disk (in 1 minute).
• Note that other threads may be waiting for the table that caused the disk full condition. If you have several "locked" threads, killing the one thread that is waiting on the disk-full condition will allow the other threads to continue.

Exceptions to the above behaveour is when you use REPAIR or OPTIMIZE or when the indexes are created in a batch after an LOAD DATA INFILE or after an ALTER TABLE statement.

All of the above commands may use big temporary files that left to themself would cause big problems for the rest of the system. If MySQL gets disk full while doing any of the above operations, it will remove the big temporary files and mark the table as crashed (except for ALTER TABLE, in which the old table will be left unchanged).

A.4.4 Where MySQL Stores Temporary Files

MySQL uses the value of the TMPDIR environment variable as the pathname of the directory in which to store temporary files. If you don't have TMPDIR set, MySQL uses the system default, which is normally `/tmp' or `/usr/tmp'. If the filesystem containing your temporary file directory is too small, you should edit mysqld_safe to set TMPDIR to point to a directory in a filesystem where you have enough space! You can also set the temporary directory using the --tmpdir option to mysqld.

MySQL creates all temporary files as hidden files. This ensures that the temporary files will be removed if mysqld is terminated. The disadvantage of using hidden files is that you will not see a big temporary file that fills up the filesystem in which the temporary file directory is located.

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, MySQL also creates temporary SQL tables. These are not hidden and have names of the form `SQL_*'.

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

If you use MySQL 4.1 or later you can spread load between several physical disks by setting --tmpdir to a list of paths separated by colon : (semicolon ; on Windows). They will be used in round-robin fashion. Note: These paths should end up on different physical disks, not different partitions of the same disk.

A.4.5 How to Protect or Change the MySQL Socket File `/tmp/mysql.sock'

If you have problems with the fact that anyone can delete the MySQL communication socket `/tmp/mysql.sock', you can, on most versions of Unix, protect your `/tmp' filesystem by setting the sticky bit on it. Log in as root and do the following:

shell> chmod +t /tmp

This will protect your `/tmp' filesystem so that files can be deleted only by their owners or the superuser (root).

You can check if the sticky bit is set by executing ls -ld /tmp. If the last permission bit is t, the bit is set.

You can change the place where MySQL uses / puts the socket file the following ways:

• Specify the path in a global or local option file. For example, put in /etc/my.cnf:

  [client] socket=path-for-socket-file [mysqld] socket=path-for-socket-file

  See section `my.cnf' Option Files.

• Specifying this on the command-line to mysqld_safe and most clients with the --socket=path-for-socket-file option.
• Specify the path to the socket in the MYSQL_UNIX_PORT environment variable.
• Defining the path with the configure option --with-unix-socket-path=path-for-socket-file. See section Typical configure Options.

You can test that the socket works with this command:

shell> mysqladmin --socket=/path/to/socket version

A.4.6 Time Zone Problems

If you have a problem with SELECT NOW() returning values in GMT and not your local time, you have to set the TZ environment variable to your current time zone. This should be done for the environment in which the server runs, for example, in mysqld_safe or mysql.server. See section Environment Variables.

A.5 Query Related Issues

A.5.1 Case-Sensitivity in Searches

By default, MySQL searches are case-insensitive (although there are some character sets that are never case-insensitive, such as czech). That means that if you search with col_name LIKE 'a%', you will get all column values that start with A or a. If you want to make this search case-sensitive, use something like INSTR(col_name, "A")=1 to check a prefix. Or use STRCMP(col_name, "A") = 0 if the column value must be exactly "A".

Simple comparison operations (>=, >, = , < , <=, sorting and grouping) are based on each character's "sort value". Characters with the same sort value (like E, e and é) are treated as the same character!

In older MySQL versions LIKE comparisons were done on the uppercase value of each character (E == e but E <> é). In newer MySQL versions LIKE works just like the other comparison operators.

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

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.

A.5.2 Problems Using DATE Columns

The format of a DATE value is 'YYYY-MM-DD'. According to standard SQL, no other format is allowed. You should use this format in UPDATE expressions and in the WHERE clause of SELECT statements. For example:

mysql> SELECT * FROM tbl_name WHERE date >= '1997-05-05';

As a convenience, MySQL automatically converts a date to a number if the date is used in a numeric context (and vice versa). It is also smart enough to allow a "relaxed" string form when updating and in a WHERE clause that compares a date to a TIMESTAMP, DATE, or a DATETIME column. (Relaxed form means that any punctuation character may be used as the separator between parts. For example, '1998-08-15' and '1998#08#15' are equivalent.) MySQL can also convert a string containing no separators (such as '19980815'), provided it makes sense as a date.

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

Because MySQL performs the conversions described above, the following statements work:

mysql> INSERT INTO tbl_name (idate) VALUES (19970505);
mysql> INSERT INTO tbl_name (idate) VALUES ('19970505');
mysql> INSERT INTO tbl_name (idate) VALUES ('97-05-05');
mysql> INSERT INTO tbl_name (idate) VALUES ('1997.05.05');
mysql> INSERT INTO tbl_name (idate) VALUES ('1997 05 05');
mysql> INSERT INTO tbl_name (idate) VALUES ('0000-00-00');

mysql> SELECT idate FROM tbl_name WHERE idate >= '1997-05-05';
mysql> SELECT idate FROM tbl_name WHERE idate >= 19970505;
mysql> SELECT MOD(idate,100) FROM tbl_name WHERE idate >= 19970505;
mysql> SELECT idate FROM tbl_name WHERE idate >= '19970505';

However, the following will not work:

mysql> SELECT idate FROM tbl_name WHERE STRCMP(idate,'19970505')=0;

STRCMP() is a string function, so it converts idate to a string and performs a string comparison. It does not convert '19970505' to a date and perform a date comparison.

Note that MySQL does very limited checking whether the date is correct. If you store an incorrect date, such as '1998-2-31', the wrong date will be stored.

Because MySQL packs dates for storage, it can't store any given date as it would not fit onto the result buffer. The rules for accepting a date are:

• If MySQL can store and retrieve a given date, the wrong date is accepted for DATE and DATETIME columns.
• All days values between 0-31 are accepted for any date. This makes it very convenient for web applications where you ask year, month and day in 3 different fields.
• The day or month field may be zero. This is convenient if you want to store a birthdate in a DATE column and you only know part of the date.

If the date cannot be converted to any reasonable value, a 0 is stored in the DATE field, which will be retrieved as 0000-00-00. This is both a speed and convenience issue as we believe that the database's responsibility is to retrieve the same date you stored (even if the data was not logically correct in all cases). We think it is up to the application to check the dates, and not the server.

A.5.3 Problems with NULL Values

The concept of the NULL value is a common source of confusion for newcomers to SQL, who often think that NULL is the same thing as an empty string "". This is not the case! For example, the following statements are completely different:

mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ("");

Both statements insert a value into the phone column, but the first inserts a NULL value and the second inserts an empty string. The meaning of the first can be regarded as "phone number is not known" and the meaning of the second can be regarded as "she has no phone".

In SQL, the NULL value is always false in comparison to any other value, even NULL. An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression. All columns in the following example return NULL:

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

If you want to search for column values that are NULL, you cannot use the =NULL test. The following statement returns no rows, because expr = NULL is FALSE, for any expression:

mysql> SELECT * FROM my_table WHERE phone = NULL;

To look for NULL values, you must use the IS NULL test. The following shows how to find the NULL phone number and the empty phone number:

mysql> SELECT * FROM my_table WHERE phone IS NULL;
mysql> SELECT * FROM my_table WHERE phone = "";

Note that you can only add an index on a column that can have NULL values if you are using MySQL Version 3.23.2 or newer and are using the MyISAM or InnoDB table type. In earlier versions and with other table types, you must declare such columns NOT NULL. This also means you cannot then insert NULL into an indexed column.

When reading data with LOAD DATA INFILE, empty columns are updated with ''. If you want a NULL value in a column, you should use \N in the text file. The literal word 'NULL' may also be used under some circumstances. See section LOAD DATA.

When using ORDER BY, NULL values are presented first, or last if you specify DESC to sort in descending order. Exception: In MySQL versions 4.0.2 through 4.0.10, NULL values sort first regardless of sort order.

When using GROUP BY, all NULL values are regarded as equal.

Aggregate (summary) functions such as COUNT(), MIN(), and SUM() ignore NULL values. The exception to this is COUNT(*), which counts rows and not individual column values. For example, the following statement would produce two counts. The first is a count of the number of rows in the table, and the second is a count of the number of non-NULL values in the age column:

mysql> SELECT COUNT(*), COUNT(age) FROM person;

To help with NULL handling, you can use the IS NULL and IS NOT NULL operators and the IFNULL() function.

For some column types, NULL values are handled specially. If you insert NULL into the first TIMESTAMP column of a table, the current date and time is inserted. If you insert NULL into an AUTO_INCREMENT column, the next number in the sequence is inserted.

A.5.4 Problems with alias

You can use an alias to refer to a column in the GROUP BY, ORDER BY, or in the HAVING part. Aliases can also be used to give columns better names:

SELECT SQRT(a*b) as rt FROM table_name GROUP BY rt HAVING rt > 0;
SELECT id,COUNT(*) AS cnt FROM table_name GROUP BY id HAVING cnt > 0;
SELECT id AS "Customer identity" FROM table_name;

Note that standard SQL doesn't allow you to refer to an alias in a WHERE clause. This is because when the WHERE code is executed the column value may not yet be determined. For example, the following query is illegal:

SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0 GROUP BY id;

The WHERE statement is executed to determine which rows should be included in the GROUP BY part while HAVING is used to decide which rows from the result set should be used.

A.5.5 Deleting Rows from Related Tables

As MySQL doesn't support subqueries (prior to Version 4.1), nor the use of more than one table in the DELETE statement (prior to Version 4.0), you should use the following approach to delete rows from 2 related tables:

1. SELECT the rows based on some WHERE condition in the main table.
2. DELETE the rows in the main table based on the same condition.
3. DELETE FROM related_table WHERE related_column IN (selected_rows).

If the total number of characters in the query with related_column is more than 1,048,576 (the default value of max_allowed_packet, you should split it into smaller parts and execute multiple DELETE statements. You will probably get the fastest DELETE by only deleting 100-1000 related_column ids per query if the related_column is an index. If the related_column isn't an index, the speed is independent of the number of arguments in the IN clause.

A.5.6 Solving Problems with No Matching Rows

If you have a complicated query that has many tables and that doesn't return any rows, you should use the following procedure to find out what is wrong with your query:

1. Test the query with EXPLAIN and check if you can find something that is obviously wrong. See section EXPLAIN.
2. Select only those fields that are used in the WHERE clause.
3. Remove one table at a time from the query until it returns some rows. If the tables are big, it's a good idea to use LIMIT 10 with the query.
4. Do a SELECT for the column that should have matched a row against the table that was last removed from the query.
5. If you are comparing FLOAT or DOUBLE columns with numbers that have decimals, you can't use '='. This problem is common in most computer languages because floating-point values are not exact values. In most cases, changing the FLOAT to a DOUBLE will fix this. See section Problems with Floating-Point Comparison.
6. If you still can't figure out what's wrong, create a minimal test that can be run with mysql test < query.sql that shows your problems. You can create a test file with mysqldump --quick database tables > query.sql. Open the file in an editor, remove some insert lines (if there are too many of these), and add your select statement at the end of the file.

   Test that you still have your problem by doing:

   shell> mysqladmin create test2 shell> mysql test2 < query.sql

   Post the test file using mysqlbug to the general MySQL mailing list. See section The MySQL Mailing Lists.

A.5.7 Problems with Floating-Point Comparison

floating-point numbers cause confusion sometimes, because these numbers are not stored as exact values inside computer architecture. What one can see on the screen usually is not the exact value of the number.

Field types FLOAT, DOUBLE and DECIMAL are such.

CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2));
INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),
(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
(2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),
(4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),
(5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),
(6, 0.00, 0.00), (6, -51.40, 0.00);

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b
    -> FROM t1 GROUP BY i HAVING a <> b;
+------+--------+-------+
| i    | a      | b     |
+------+--------+-------+
|    1 |  21.40 | 21.40 |
|    2 |  76.80 | 76.80 |
|    3 |   7.40 |  7.40 |
|    4 |  15.40 | 15.40 |
|    5 |   7.20 |  7.20 |
|    6 | -51.40 |  0.00 |
+------+--------+-------+

The result is correct. Although the first five records look like they shouldn't pass the comparison test, they may do so because the difference between the numbers show up around tenth decimal, or so depending on computer architecture.

The problem cannot be solved by using ROUND() (or similar function), because the result is still a floating-point number. Example:

mysql> SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b
    -> FROM t1 GROUP BY i HAVING a <> b;
+------+--------+-------+
| i    | a      | b     |
+------+--------+-------+
|    1 |  21.40 | 21.40 |
|    2 |  76.80 | 76.80 |
|    3 |   7.40 |  7.40 |
|    4 |  15.40 | 15.40 |
|    5 |   7.20 |  7.20 |
|    6 | -51.40 |  0.00 |
+------+--------+-------+

This is what the numbers in column 'a' look like:

mysql> SELECT i, ROUND(SUM(d1), 2)*1.0000000000000000 AS a,
    -> ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i HAVING a <> b;
+------+----------------------+-------+
| i    | a                    | b     |
+------+----------------------+-------+
|    1 |  21.3999999999999986 | 21.40 |
|    2 |  76.7999999999999972 | 76.80 |
|    3 |   7.4000000000000004 |  7.40 |
|    4 |  15.4000000000000004 | 15.40 |
|    5 |   7.2000000000000002 |  7.20 |
|    6 | -51.3999999999999986 |  0.00 |
+------+----------------------+-------+

Depending on the computer architecture you may or may not see similar results. Each CPU may evaluate floating-point numbers differently. For example in some machines you may get 'right' results by multiplying both arguments with 1, an example follows.

WARNING: NEVER TRUST THIS METHOD IN YOUR APPLICATION, THIS IS AN EXAMPLE OF A WRONG METHOD!!!

mysql> SELECT i, ROUND(SUM(d1), 2)*1 AS a, ROUND(SUM(d2), 2)*1 AS b
    -> FROM t1 GROUP BY i HAVING a <> b;
+------+--------+------+
| i    | a      | b    |
+------+--------+------+
|    6 | -51.40 | 0.00 |
+------+--------+------+

The reason why the above example seems to be working is that on the particular machine where the test was done, the CPU floating-point arithmetics happens to round the numbers to same, but there is no rule that any CPU should do so, so it cannot be trusted.

The correct way to do floating-point number comparison is to first decide on what is the wanted tolerance between the numbers and then do the comparison against the tolerance number. For example, if we agree on that floating-point numbers should be regarded the same, if they are same with precision of one of ten thousand (0.0001), the comparison should be done like this:

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
    -> GROUP BY i HAVING ABS(a - b) > 0.0001;
+------+--------+------+
| i    | a      | b    |
+------+--------+------+
|    6 | -51.40 | 0.00 |
+------+--------+------+
1 row in set (0.00 sec)

And vice versa, if we wanted to get rows where the numbers are the same, the test would be:

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
    -> GROUP BY i HAVING ABS(a - b) < 0.0001;
+------+-------+-------+
| i    | a     | b     |
+------+-------+-------+
|    1 | 21.40 | 21.40 |
|    2 | 76.80 | 76.80 |
|    3 |  7.40 |  7.40 |
|    4 | 15.40 | 15.40 |
|    5 |  7.20 |  7.20 |
+------+-------+-------+

A.6 Optimiser Related Issues

MySQL uses a cost based optimiser to find out the best way to resolve a query. In many cases MySQL can calculate the best possible query plan but in some cases MySQL doesn't have enough information about the data at hand and have to do some 'educated' guesses about the data.

This manual section is intended for the cases when MySQL doesn't get it right.

The tools one has available to help MySQL do the 'right' things are:

• EXPLAIN. See section EXPLAIN.
• ANALYZE TABLE. See section ANALYZE TABLE.
• USE INDEX, FORCE INDEX and IGNORE INDEX. See section SELECT Syntax.
• Global and table level STRAIGHT JOIN. See section SELECT Syntax.
• Setting thread specific variables. See section SHOW VARIABLES.

A.6.1 How to avoid table scan,,,

EXPLAIN will show ALL in the type column when MySQL uses a table scan to resolve a query. This happens usually when:

• The table is so small that it's faster to do a table scan than a key lookup. This is a common case for tables with < 10 rows and a short row length.
• There is no usable restrictions in the ON or WHERE clause for indexed columns.
• You are comparing indexed columns with constants and MySQL has calculated (based on the index tree) that the constants covers a too big part of the table and a table scan would be faster. See section How MySQL Optimises WHERE Clauses.
• You are using a key with low cardinality (= many matching rows) through another column. MySQL will in this case assume that by using the key it will probably do a lot of key lookups and in this case the table scan would be faster.

What you can do to avoid a 'wrong' table scan for big tables are:

• Use ANALYZE TABLE for the scanned table to update key distributions. See section ANALYZE TABLE Syntax.
• Use FORCE INDEX for the scanned table to tell MySQL that table scans are very expensive compared to use one of the given index. See section SELECT Syntax.

  SELECT * FROM t1,t2 force index(index_for_column) WHERE t1.column=t2.column;

• Start mysqld with --max-seeks-for-key=1000 or do SET MAX_SEEKS_FOR_KEY=1000 to tell the optimiser that for no key scan will cause more than 1000 key seeks.

A.7 Table Definition Related Issues

A.7.1 Problems with ALTER TABLE.

ALTER TABLE changes a table to the current character set. If you get a duplicate key error during ALTER TABLE, then the cause is either that the new character sets maps two keys to the same value or that the table is corrupted, in which case you should run REPAIR TABLE on the table.

If ALTER TABLE dies with an error like this:

Error on rename of './database/name.frm' to './database/B-a.frm' (Errcode: 17)

the problem may be that MySQL has crashed in a previous ALTER TABLE and there is an old table named `A-something' or `B-something' lying around. In this case, go to the MySQL data directory and delete all files that have names starting with A- or B-. (You may want to move them elsewhere instead of deleting them.)

ALTER TABLE works the following way:

• Create a new table named `A-xxx' with the requested changes.
• All rows from the old table are copied to `A-xxx'.
• The old table is renamed `B-xxx'.
• `A-xxx' is renamed to your old table name.
• `B-xxx' is deleted.

If something goes wrong with the renaming operation, MySQL tries to undo the changes. If something goes seriously wrong (this shouldn't happen, of course), MySQL may leave the old table as `B-xxx', but a simple rename on the system level should get your data back.

A.7.2 How To Change the Order of Columns in a Table

The whole point of SQL is to abstract the application from the data storage format. You should always specify the order in which you wish to retrieve your data. For example:

SELECT col_name1, col_name2, col_name3 FROM tbl_name;

will return columns in the order col_name1, col_name2, col_name3, whereas:

SELECT col_name1, col_name3, col_name2 FROM tbl_name;

will return columns in the order col_name1, col_name3, col_name2.

If you want to change the order of columns anyway, you can do it as follows:

1. Create a new table with the columns in the right order.
2. Execute INSERT INTO new_table SELECT fields-in-new_table-order FROM old_table.
3. Drop or rename old_table.
4. ALTER TABLE new_table RENAME old_table.

You should never, in an application, use SELECT * and retrieve the columns based on their position, because the order and position in which columns are returned cannot may not remain the same (if you add/move/delete columns). A simple change to your database structure would then cause your application to fail. Of course SELECT * is quite suitable for testing queries.

A.7.3 TEMPORARY TABLE problems

The following are a list of the limitations with TEMPORARY TABLES.

• A temporary table can only be of type HEAP, ISAM, MyISAM, MERGE, or InnoDB.
• You can't use temporary tables more than once in the same query. For example, the following doesn't work.

  mysql> SELECT * FROM temporary_table, temporary_table AS t2;

• You can't use RENAME on a TEMPORARY table. Note that ALTER TABLE org_name RENAME new_name works!

Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:44