Mysql error 2005 unknown mysql server host

805

votes

Answer

Solution:

ERROR 2005 (HY000): Unknown MySQL server host ‘localhost’ (0)

modify list of host names for your system:

C:WindowsSystem32driversetchosts

Make sure that you have the following entry:

127.0.0.1 localhost
In my case that entry was 0.0.0.0 localhost which caussed all problem

(you may need to change modify permission to modify this file)

This performs DNS resolution of host “localhost” to the IP address 127.0.0.1.

469

votes

Answer

Solution:

I have passed through that error today and did everything described above but didn’t work for me. So I decided to view the core problem and logged onto the MySQL root folder in Windows 7 and did this solution:

1. Go to folder:

   C:AppServMySQL
   

2. Right click and Run as Administrator these files:

   mysql_servicefix.bat
   
   mysql_serviceinstall.bat
   
   mysql_servicestart.bat
   

Then close the entire explorer window and reopen it or clear cache then login to phpMyAdmin again.

952

votes

Answer

Solution:

The case is like :

 mysql connects will localhost when network is not up.
 mysql cannot connect when network is up.

You can try the following steps to diagnose and resolve the issue (my guess is that some other service is blocking port on which mysql is hosted):

1. Disconnect the network.
2. Stop mysql service (if windows, try from services.msc window)
3. Connect to network.
4. Try to start the mysql and see if it starts correctly.
5. Check for system logs anyways to be sure that there is no error in starting mysql service.
6. If all goes well try connecting.
7. If fails, try to do a telnet localhost 3306 and see what output it shows.
8. Try changing the port on which mysql is hosted, default 3306, you can change to some other port which is ununsed.

This should ideally resolve the issue you are facing.

926

votes

Answer

Solution:

Follow these steps to fix this error
Use connect [email protected] instead of connect [email protected]

if it doesn’t work then go to C:WindowsSystem32driversetchosts and check the IP address attached to host name.
use that IP, so it will be.
connect [email protected]_ip_address_you_found

Error Message Sources

• On the server side, error messages may occur during the
  startup and shutdown processes, as a result of issues that
  occur during SQL statement execution, and so forth.

  • The MySQL server writes some error messages to its error
    log. These indicate issues of interest to database
    administrators or that require DBA action.

  • The server sends other error messages to client
    programs. These indicate issues pertaining only to a
    particular client. The MySQL client library takes errors
    received from the server and makes them available to the
    host client program.

• Client-side error messages are generated from within the
  MySQL client library, usually involving problems
  communicating with the server.

• This message produced during the startup process provides a
  status or progress indicator:

  2018-10-28T13:01:32.735983Z 0 [Note] [MY-010303] [Server] Skipping
  generation of SSL certificates as options related to SSL are specified.
  

• This message indicates an issue that requires DBA action:

  2018-10-02T03:20:39.410387Z 768 [ERROR] [MY-010045] [Server] Event Scheduler:
  [evtuser@localhost][myschema.e_daily] Unknown database 'mydb'
  

Error Message Elements

• Error code: This value is numeric. It is MySQL-specific and
  is not portable to other database systems.

  Each error number has a corresponding symbolic value.
  Examples:

  • The symbol for server error number
    1146 is
    ER_NO_SUCH_TABLE.

  • The symbol for client error number
    2005 is
    CR_UNKNOWN_HOST.

  The set of error codes used in error messages is partitioned
  into distinct ranges; see
  Error Code Ranges.

  Error codes are stable across General Availability (GA)
  releases of a given MySQL series. Before a series reaches GA
  status, new codes may still be under development and are
  subject to change.

• SQLSTATE value: This value is a five-character string (for
  example, '42S02'). SQLSTATE values are
  taken from ANSI SQL and ODBC and are more standardized than
  the numeric error codes. The first two characters of an
  SQLSTATE value indicate the error class:

  • Class = '00' indicates success.

  • Class = '01' indicates a warning.

  • Class = '02' indicates “not
    found.” This is relevant within the context of
    cursors and is used to control what happens when a
    cursor reaches the end of a data set. This condition
    also occurs for SELECT ... INTO
var_list statements
    that retrieve no rows.

  • Class > '02' indicates an
    exception.

  For server-side errors, not all MySQL error numbers have
  corresponding SQLSTATE values. In these cases,
  'HY000' (general error) is used.

  For client-side errors, the SQLSTATE value is always
  'HY000' (general error), so it is not
  meaningful for distinguishing one client error from another.

• Message string: This string provides a textual description
  of the error.

Error Code Ranges

• 1 to 999: Global error codes. This error code range is
  called “global” because it is a shared range
  that is used by the server as well as by clients.

  When an error in this range originates on the server side,
  the server writes it to the error log, padding the error
  code with leading zeros to six digits and adding a prefix of
  MY-.

  When an error in this range originates on the client side,
  the client library makes it available to the client program
  with no zero-padding or prefix.

• 1,000 to 1,999: Server error codes reserved for messages
  sent to clients.

• 2,000 to 2,999: Client error codes reserved for use by the
  client library.

• 3,000 to 4,999: Server error codes reserved for messages
  sent to clients.

• 5,000 to 5,999: Error codes reserved for use by X Plugin
  for messages sent to clients.

• 10,000 to 49,999: Server error codes reserved for messages
  to be written to the error log (not sent to clients).

  When an error in this range occurs, the server writes it to
  the error log, padding the error code with leading zeros to
  six digits and adding a prefix of MY-.

• 50,000 to 51,999: Error codes reserved for use by third
  parties.

• When the server writes a message to the error log, it pads
  the error code with leading zeros to six digits and adds a
  prefix of MY- (examples:
  MY-000022,
  MY-010048).

• When the server sends a message to a client program, it adds
  no zero-padding or prefix to the error code (examples:
  1036,
  3013).

Error Log

SQL Error Message Interface

• SQL statement warning and error information is available
  through the SHOW WARNINGS and
  SHOW ERRORS statements. The
  warning_count system
  variable indicates the number of errors, warnings, and notes
  (with notes excluded if the
  sql_notes system variable
  is disabled). The
  error_count system variable
  indicates the number of errors. Its value excludes warnings
  and notes.

• The GET DIAGNOSTICS statement
  may be used to inspect the diagnostic information in the
  diagnostics area. See Section 13.6.7.3, “GET DIAGNOSTICS Statement”.

• SHOW SLAVE STATUS statement
  output includes information about replication errors
  occurring on replica servers.

• SHOW ENGINE
INNODB STATUS statement output includes
  information about the most recent foreign key error if a
  CREATE TABLE statement for an
  InnoDB table fails.

Client Error Message Interface

• Errors that originate on the client side from within the
  MySQL client library.

• Errors that originate on the server side and are sent to the
  client by the server. These are received within the client
  library, which makes them available to the host client
  program.

• mysql_errno() returns the
  MySQL error code.

• mysql_sqlstate() returns the
  SQLSTATE value.

• mysql_error() returns the
  message string.

• mysql_stmt_errno(),
  mysql_stmt_sqlstate(), and
  mysql_stmt_error() are the
  corresponding error functions for prepared statements.

• mysql_warning_count()
  returns the number of errors, warnings, and notes for the
  most recent statement.

B.3.1 How to Determine What Is Causing a Problem

• If you have one of the following symptoms, then it is
  probably a hardware problems (such as memory, motherboard,
  CPU, or hard disk) or kernel problem:

  • The keyboard does not work. This can normally be checked
    by pressing the Caps Lock key. If the Caps Lock light
    does not change, you have to replace your keyboard.
    (Before doing this, you should try to restart your
    computer and check all cables to the keyboard.)

  • The mouse pointer does not move.

  • The machine does not answer to a remote machine’s pings.

  • Other programs that are not related to MySQL do not
    behave correctly.

  • Your system restarted 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 whether there are any patches, updates, or
  service packs for your operating system that could likely
  solve your problem. Check also that all your libraries (such
  as glibc) are up to date.

  It is 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 recover by
  logging in to your machine from another machine and
  executing kbd_mode -a.

• Please examine your system log file
  (/var/log/messages or similar) for
  reasons for your problem. If you think the problem is in
  MySQL, you should also examine MySQL’s log files. See
  Section 5.4, “MySQL Server Logs”.

• If you do not think you have hardware problems, you should
  try to find out which program is causing problems. Try using
  top, ps, Task Manager,
  or some similar program, to check which program is taking
  all CPU or is locking the machine.

• Use top, df, or a
  similar program to check whether you are out of memory, disk
  space, file descriptors, or some other critical resource.

• If the problem is some runaway process, you can always try
  to kill it. If it does not want to die, there is probably a
  bug in the operating system.

• Has the program in question made a segmentation fault (did
  it dump core)?

• Is the program taking up all available CPU time? Check with
  top. Let the program run for a while, it
  may simply be evaluating something computationally
  intensive.

• If the mysqld server is causing problems,
  can you get any response from it with mysqladmin -u
  root ping or mysqladmin -u root
  processlist?

• What does a client program say when you try to connect to
  the MySQL server? (Try with mysql, for
  example.) Does the client jam? Do you get any output from
  the program?

B.3.2 Common Errors When Using MySQL Programs

A MySQL client on Unix can connect to the
mysqld server in two different ways: By
using a Unix socket file to connect through a file in the file
system (default /tmp/mysql.sock), or by
using TCP/IP, which connects through a port number. A Unix
socket file connection is faster than TCP/IP, but can be used
only when connecting to a server on the same computer. A Unix
socket file is used if you do not specify a host name or if
you specify the special host name
localhost.

If the MySQL server is running on Windows, you can connect
using TCP/IP. If the server is started with the
named_pipe system variable
enabled, you can also connect with named pipes if you run the
client on the host where the server is running. The name of
the named pipe is MySQL by default. If you
do not give a host name when connecting to
mysqld, a MySQL client first tries to
connect to the named pipe. If that does not work, it connects
to the TCP/IP port. You can force the use of named pipes on
Windows by using . as the host name.

The error (2002) Can't connect to ...
normally means that there is no MySQL server running on the
system or that you are using an incorrect Unix socket file
name or TCP/IP port number when trying to connect to the
server. You should also check that the TCP/IP port you are
using has not been blocked by a firewall or port blocking
service.

The error (2003) Can't connect to MySQL server on
'server' (10061)
indicates that the network connection has been refused. You
should check that there is a MySQL server running, that it has
network connections enabled, and that the network port you
specified is the one configured on the server.

Start by checking whether there is a process named
mysqld running on your server host. (Use
ps xa | grep mysqld on Unix or the Task
Manager on Windows.) If there is no such process, you should
start the server. See Section 2.10.2, “Starting the Server”.

If a mysqld process is running, you can
check it by trying the following commands. The port number or
Unix socket file name might be different in your setup.
host_ip represents the IP address of the
machine where the server is running.

shell> mysqladmin version
shell> mysqladmin variables
shell> mysqladmin -h `hostname` version variables
shell> mysqladmin -h `hostname` --port=3306 version
shell> mysqladmin -h host_ip version
shell> mysqladmin --protocol=SOCKET --socket=/tmp/mysql.sock version

Note the use of backticks rather than forward quotation marks
with the hostname command; these cause the
output of hostname (that is, the current
host name) to be substituted into the
mysqladmin command. If you have no
hostname command or are running on Windows,
you can manually type the host name of your machine (without
backticks) following the -h option. You can
also try -h 127.0.0.1 to connect with
TCP/IP to the local host.

Make sure that the server has not been configured to ignore
network connections or (if you are attempting to connect
remotely) that it has not been configured to listen only
locally on its network interfaces. If the server was started
with the skip_networking
system variable enabled, it cannot accept TCP/IP connections
at all. If the server was started with the
bind_address system variable
set to 127.0.0.1, it listens for TCP/IP
connections only locally on the loopback interface and does
not accept remote connections.

Check to make sure that there is no firewall blocking access
to MySQL. Your firewall may be configured on the basis of the
application being executed, or the port number used by MySQL
for communication (3306 by default). Under Linux or Unix,
check your IP tables (or similar) configuration to ensure that
the port has not been blocked. Under Windows, applications
such as ZoneAlarm or Windows Firewall may need to be
configured not to block the MySQL port.

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

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

There are three likely causes for this error message.

Usually it indicates network connectivity trouble and you
should check the condition of your network if this error
occurs frequently. If the error message includes “during
query,” this is probably the case you are experiencing.

Sometimes the “during query” form happens when
millions of rows are being sent as part of one or more
queries. If you know that this is happening, you should try
increasing net_read_timeout
from its default of 30 seconds to 60 seconds or longer,
sufficient for the data transfer to complete.

More rarely, it can happen when the client is attempting the
initial connection to the server. In this case, if your
connect_timeout value is set
to only a few seconds, you may be able to resolve the problem
by increasing it to ten seconds, perhaps more if you have a
very long distance or slow connection. You can determine
whether you are experiencing this more uncommon cause by using
SHOW GLOBAL STATUS LIKE 'Aborted_connects'.
It increases by one for each initial connection attempt that
the server aborts. You may see “reading authorization
packet” as part of the error message; if so, that also
suggests that this is the solution that you need.

If the cause is none of those just described, you may be
experiencing a problem with
BLOB values that are larger
than max_allowed_packet,
which can cause this error with some clients. Sometime you may
see an ER_NET_PACKET_TOO_LARGE
error, and that confirms that you need to increase
max_allowed_packet.

MySQL client programs prompt for a password when invoked with
a --password or
-p option that has no following password
value:

shell> mysql -u user_name -p
Enter password:

On some systems, you may find that your password works when
specified in an option file or on the command line, but not
when you enter it interactively at the Enter
password: prompt. This occurs when the library
provided by the system to read passwords limits password
values to a small number of characters (typically eight). That
is a problem with the system library, not with MySQL. To work
around it, change your MySQL password to a value that is eight
or fewer characters long, or put your password in an option
file.

If clients encounter Too many connections
errors when attempting to connect to the
mysqld server, all available connections
are in use by other clients.

The permitted number of connections is controlled by the
max_connections system
variable. To support more connections, set
max_connections to a larger
value.

mysqld actually permits
max_connections
+ 1 client connections. The extra connection is reserved for
use by accounts that have the
CONNECTION_ADMIN privilege (or
the deprecated SUPER
privilege). By granting the privilege to administrators and
not to normal users (who should not need it), an administrator
can connect to the server and use SHOW
PROCESSLIST to diagnose problems even if the maximum
number of unprivileged clients are connected. See
Section 13.7.7.29, “SHOW PROCESSLIST Statement”.

The server also permits administrative connections on a
dedicated interface. For more information about how the server
handles client connections, see
Section 5.1.12.1, “Connection Interfaces”.

If you issue a query using the mysql client
program and receive an error like the following one, it means
that mysql does not have enough memory to
store the entire query result:

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

To remedy the problem, first check whether your query is
correct. Is it reasonable that it should return so many rows?
If not, correct the query and try again. Otherwise, you can
invoke mysql with the
--quick option. This causes it
to use the mysql_use_result()
C API function to retrieve the result set, which places less
of a load on the client (but more on the server).

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. In this case, you normally get one of the
following error codes (which one you get is operating
system-dependent).

By default, the server closes the connection after eight hours
if nothing has happened. You can change the time limit by
setting the wait_timeout
variable when you start mysqld. See
Section 5.1.8, “Server System Variables”.

If you have a script, you just have to issue the query again
for the client to do an automatic reconnection. This assumes
that you have automatic reconnection in the client enabled
(which is the default for the mysql
command-line client).

Some other common reasons for the MySQL server has
gone away error are:

You can check whether the MySQL server died and restarted by
executing mysqladmin version and examining
the server’s uptime. If the client connection was broken
because mysqld crashed and restarted, you
should concentrate on finding the reason for the crash. Start
by checking whether issuing the query again kills the server
again. See Section B.3.3.3, “What to Do If MySQL Keeps Crashing”.

You can obtain more information about lost connections by
starting mysqld with the
log_error_verbosity system
variable set to 3. This logs some of the disconnection
messages in the hostname.err file. See
Section 5.4.2, “The Error Log”.

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

See also Section B.3.2.9, “Communication Errors and Aborted Connections”, and
Section 1.6, “How to Report Bugs or Problems”.

A communication packet is a single SQL statement sent to the
MySQL server, a single row that is sent to the client, or a
binary log event sent from a source replication server to a
replica.

The largest possible packet that can be transmitted to or from
a MySQL 8.0 server or client is 1GB.

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

Both the client and the server have their own
max_allowed_packet variable,
so if you want to handle big packets, you must increase this
variable both in the client and in the server.

If you are using the mysql client program,
its default
max_allowed_packet variable
is 16MB. To set a larger value, start mysql
like this:

shell> mysql --max_allowed_packet=32M

That sets the packet size to 32MB.

The server’s default
max_allowed_packet value is
64MB. You can increase this if the server needs to handle big
queries (for example, if you are working with big
BLOB columns). For example, to
set the variable to 128MB, start the server like this:

shell> mysqld --max_allowed_packet=128M

You can also use an option file to set
max_allowed_packet. For
example, to set the size for the server to 128MB, add the
following lines in an option file:

[mysqld]
max_allowed_packet=128M

It is safe to increase the value of this variable because the
extra memory is allocated only when needed. For example,
mysqld allocates more memory only when you
issue a long query or when mysqld must
return a large result row. The small default value of the
variable is a precaution to catch incorrect packets between
the client and server and also to ensure that you do not run
out of memory by using large packets accidentally.

You can also get strange problems with large packets if you
are using large BLOB values but
have not 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
restarting mysqld.

If connection problems occur such as communication errors or
aborted connections, use these sources of information to
diagnose problems:

If the log_error_verbosity
system variable is set to 3, you might find messages like this
in your error log:

[Note] Aborted connection 854 to db: 'employees' user: 'josh'

If a client is unable even to connect, the server increments
the Aborted_connects status
variable. Unsuccessful connection attempts can occur for the
following reasons:

If these kinds of things happen, it might indicate that
someone is trying to break into your server! If the general
query log is enabled, messages for these types of problems are
logged to it.

If a client successfully connects but later disconnects
improperly or is terminated, the server increments the
Aborted_clients status
variable, and logs an Aborted
connection message to the error log. The cause can
be any of the following:

Other reasons for problems with aborted connections or aborted
clients:

See also Section B.3.2.7, “MySQL server has gone away”.

If a table-full error occurs, it may be that the disk is full
or that the table has reached its maximum size. The effective
maximum table size for MySQL databases is usually determined
by operating system constraints on file sizes, not by MySQL
internal limits. See Section 8.4.6, “Limits on Table Size”.

If you get an error of the following type for some queries, it
means that MySQL cannot create a temporary file for the result
set in the temporary directory:

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

The preceding error is a typical message for Windows; the Unix
message is similar.

One fix is to start mysqld with the
--tmpdir option or to add the
option to the [mysqld] section of your
option file. For example, to specify a directory of
C:temp, use these lines:

[mysqld]
tmpdir=C:/temp

The C:temp directory must exist and have
sufficient space for the MySQL server to write to. See
Section 4.2.2.2, “Using Option Files”.

Another cause of this error can be permissions issues. Make
sure that the MySQL server can write to the
tmpdir directory.

Check also the error code that you get with
perror. One reason the server cannot write
to a table is that the file system is full:

shell> perror 28
OS error code  28:  No space left on device

If you get an error of the following type during startup, it
indicates that the file system or directory used for storing
data files is write protected. Provided that the write error
is to a test file, the error is not serious and can be safely
ignored.

Can't create test file /usr/local/mysql/data/master.lower-test

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 calling
mysql_use_result() or
mysql_store_result() in
between.

If you get the following error, it means that when
mysqld was started or when it reloaded the
grant tables, it found an account in the
user table that had an invalid password.

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

As a result, the account is simply ignored by the permission
system. To fix this problem, assign a new, valid password to
the account.

If you get either of the following errors, it usually means
that no table exists in the default database with the given
name:

Table 'tbl_name' doesn't exist
Can't find file: 'tbl_name' (errno: 2)

In some cases, it may be that the table does exist but that
you are referring to it incorrectly:

You can check which tables are in the default database with
SHOW TABLES. See
Section 13.7.7, “SHOW Statements”.

You might see an error like this if you have character set
problems:

MySQL Connection Failed: Can't initialize character set charset_name

This error can have any of the following causes:

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

shell> perror 23
OS error code  23:  File table overflow
shell> perror 24
OS error code  24:  Too many open files
shell> perror 11
OS error code  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 reducing the
value of the table_open_cache
system variable (the default value is 64). This may not
entirely prevent running out of file descriptors because in
some circumstances the server may attempt to extend the cache
size temporarily, as described in
Section 8.4.3.1, “How MySQL Opens and Closes Tables”. Reducing the value of
max_connections also reduces
the number of open files (the default value is 100).

To change the number of file descriptors available to
mysqld, you can use the
--open-files-limit option
to mysqld_safe or set the
open_files_limit system
variable. See Section 5.1.8, “Server System Variables”. The
easiest way to set these values is to add an option to your
option file. See Section 4.2.2.2, “Using Option Files”. If you have
an old version of mysqld that does not
support setting the open files limit, 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 set the number of file descriptors
to be made available to mysqld.

--open-files-limit and
ulimit 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 be
overridden only if you start mysqld_safe or
mysqld as root (just
remember that you also need to start the server with the
--user option in this case so
that it does not continue to run as root
after it starts up). If you need to increase the operating
system limit on the number of file descriptors available to
each process, consult the documentation for your system.

If you have started mysqld with the
myisam_recover_options system
variable set, MySQL automatically checks and tries to repair
MyISAM tables if they are marked as ‘not
closed properly’ or ‘crashed’. If this happens, MySQL writes
an entry in the hostname.err file
'Warning: Checking table ...' which is
followed by Warning: Repairing table if the
table needs to be repaired. If you get a lot of these errors,
without mysqld having died unexpectedly
just before, then something is wrong and needs to be
investigated further.

When the server detects MyISAM table
corruption, it writes additional information to the error log,
such as the name and line number of the source file, and the
list of threads accessing the table. Example: Got an
error from thread_id=1, mi_dynrec.c:368. This is
useful information to include in bug reports.

See also Section 5.1.7, “Server Command Options”, and
Section 5.9.1.7, “Making a Test Case If You Experience Table Corruption”.

B.3.3 Administration-Related Issues

If you have problems with file permissions, the
UMASK or UMASK_DIR
environment variable might be set incorrectly when
mysqld starts. For example,
mysqld might issue the following error
message when you create a table:

ERROR: Can't find file: 'path/with/file_name' (Errcode: 13)

The default UMASK and
UMASK_DIR values are
0640 and 0750,
respectively. mysqld assumes that the value
for UMASK or UMASK_DIR
is in octal if it starts with a zero. For example, setting
UMASK=0600 is equivalent to
UMASK=384 because 0600 octal is 384
decimal.

Assuming that you start mysqld using
mysqld_safe, change the default
UMASK value as follows:

UMASK=384  # = 600 in octal
export UMASK
mysqld_safe &

By default, mysqld creates database
directories with an access permission value of
0750. To modify this behavior, set the
UMASK_DIR variable. If you set its value,
new directories are created with the combined
UMASK and UMASK_DIR
values. For example, to give group access to all new
directories, start mysqld_safe as follows:

UMASK_DIR=504  # = 770 in octal
export UMASK_DIR
mysqld_safe &

For additional details, see
Section 4.9, “Environment Variables”.

If you have never assigned a root password
for MySQL, the server does not require a password at all for
connecting as root. However, this is
insecure. For instructions on assigning a password, see
Section 2.10.4, “Securing the Initial MySQL Account”.

If you know the root password and want to
change it, see Section 13.7.1.1, “ALTER USER Statement”, and
Section 13.7.1.10, “SET PASSWORD Statement”.

If you assigned a root password previously
but have forgotten it, you can assign a new password. The
following sections provide instructions for Windows and Unix
and Unix-like systems, as well as generic instructions that
apply to any system.

Each MySQL version is tested on many platforms before it is
released. This does not mean that there are no bugs in MySQL,
but if there are bugs, they should be very few and can be hard
to find. If you have a problem, it always helps if you try to
find out exactly what crashes your system, because you have a
much better chance of getting the problem fixed quickly.

First, you should try to find out whether the problem is that
the mysqld server 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 and restarted, you may find
the reason by looking in the server’s error log. See
Section 5.4.2, “The Error Log”.

On some systems, you can find in the error log a stack trace
of where mysqld died. Note that the
variable values written in the error log may not always be
100% correct.

Many unexpected server exits are caused by corrupted data
files or index files. MySQL updates the files 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 the
delay_key_write system
variable enabled, in which case data files are written but not
index files.) This means that data file contents are safe even
if mysqld crashes, because the operating
system ensures that the unflushed data is written to disk. You
can force MySQL to flush everything to disk after every SQL
statement by starting mysqld with the
--flush option.

The preceding means that normally you should not get corrupted
tables unless one of the following happens:

Because it is very difficult to know why something is
crashing, first try to check whether things that work for
others result in an unexpected exit for you. Try the following
things:

This section describes how MySQL responds to disk-full errors
(such as “no space left on device”), and to
quota-exceeded errors (such as “write failed” or
“user block limit reached”).

This section is relevant for writes to
MyISAM tables. It also applies for writes
to binary log files and binary log index file, except that
references to “row” and “record”
should be understood to mean “event.”

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

To alleviate the problem, take the following actions:

Exceptions to the preceding behavior are when you use
REPAIR TABLE or
OPTIMIZE TABLE or when the
indexes are created in a batch after LOAD
DATA or after an ALTER
TABLE statement. All of these statements may create
large temporary files that, if left to themselves, would cause
big problems for the rest of the system. If the disk becomes
full while MySQL is doing any of these operations, it removes
the big temporary files and mark the table as crashed. The
exception is that for ALTER
TABLE, the old table is left unchanged.

On Unix, MySQL uses the value of the TMPDIR
environment variable as the path name of the directory in
which to store temporary files. If TMPDIR
is not set, MySQL uses the system default, which is usually
/tmp, /var/tmp, or
/usr/tmp.

On Windows, MySQL checks in order the values of the
TMPDIR, TEMP, and
TMP environment variables. For the first
one found to be set, MySQL uses it and does not check those
remaining. If none of TMPDIR,
TEMP, or TMP are set,
MySQL uses the Windows system default, which is usually
C:windowstemp.

If the file system containing your temporary file directory is
too small, you can use the mysqld
--tmpdir option to specify a
directory in a file system where you have enough space.

The --tmpdir option can be set
to a list of several paths that are used in round-robin
fashion. Paths should be separated by colon characters
(:) on Unix and semicolon characters
(;) on Windows.

If the MySQL server is acting as a replica, you can set the
slave_load_tmpdir system
variable to specify a separate directory for holding temporary
files when replicating LOAD
DATA statements. This directory should be in a
disk-based file system (not a memory-based file system) so
that the temporary files used to replicate LOAD DATA can
survive machine restarts. The directory also should not be one
that is cleared by the operating system during the system
startup process. However, replication can now continue after a
restart if the temporary files have been removed.

MySQL arranges that temporary files are removed if
mysqld is terminated. On platforms that
support it (such as Unix), this is done by unlinking the file
after opening it. The disadvantage of this is that the name
does not appear in directory listings and you do not see a big
temporary file that fills up the file system in which the
temporary file directory is located. (In such cases,
lsof +L1 may be helpful in identifying
large files associated with mysqld.)

When sorting (ORDER BY or GROUP
BY), MySQL normally uses one or two temporary files.
The maximum disk space required is determined by the following
expression:

(length of what is sorted + sizeof(row pointer))
* number of matched rows
* 2

The row pointer size is usually four bytes, but may grow in
the future for really big tables.

For some statements, MySQL creates temporary SQL tables that
are not hidden and have names that begin with
#sql.

Some SELECT queries creates
temporary SQL tables to hold intermediate results.

DDL operations that rebuild the table and are not performed
online using the ALGORITHM=INPLACE
technique create a temporary copy of the original table in the
same directory as the original table.

Online DDL operations may use temporary log files for
recording concurrent DML, temporary sort files when creating
an index, and temporary intermediate tables files when
rebuilding the table. For more information, see
Section 15.12.3, “Online DDL Space Requirements”.

InnoDB user-created temporary tables and
on-disk internal temporary tables are created in a temporary
tablespace file named ibtmp1 in the MySQL
data directory. For more information, see
Section 15.6.3.5, “Temporary Tablespaces”.

See also
Section 15.15.7, “InnoDB INFORMATION_SCHEMA Temporary Table Info Table”.

The optional EXTENDED modifier causes
SHOW TABLES to list hidden
tables created by failed ALTER
TABLE statements. See Section 13.7.7.39, “SHOW TABLES Statement”.

The default location for the Unix socket file that the server
uses for communication with local clients is
/tmp/mysql.sock. (For some distribution
formats, the directory might be different, such as
/var/lib/mysql for RPMs.)

On some versions of Unix, anyone can delete files in the
/tmp directory or other similar
directories used for temporary files. If the socket file is
located in such a directory on your system, this might cause
problems.

On most versions of Unix, you can protect your
/tmp directory so that files can be
deleted only by their owners or the superuser
(root). To do this, set the
sticky bit on the /tmp
directory by logging in as root and using
the following command:

shell> chmod +t /tmp

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

Another approach is to change the place where the server
creates the Unix socket file. If you do this, you should also
let client programs know the new location of the file. You can
specify the file location in several ways:

You can test whether the new socket location works by
attempting to connect to the server with this command:

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

If you have a problem with SELECT NOW()
returning values in UTC and not your local time, you have to
tell the server your current time zone. The same applies if
UNIX_TIMESTAMP() returns the
wrong value. This should be done for the environment in which
the server runs (for example, in
mysqld_safe or
mysql.server). See
Section 4.9, “Environment Variables”.

You can set the time zone for the server with the
--timezone=timezone_name
option to mysqld_safe. You can also set it
by setting the TZ environment variable
before you start mysqld.

The permissible values for
--timezone or
TZ are system dependent. Consult your
operating system documentation to see what values are
acceptable.

B.3.4 Query-Related Issues

For nonbinary strings (CHAR,
VARCHAR,
TEXT), string searches use the
collation of the comparison operands. For binary strings
(BINARY,
VARBINARY,
BLOB), comparisons use the
numeric values of the bytes in the operands; this means that
for alphabetic characters, comparisons are case-sensitive.

A comparison between a nonbinary string and binary string is
treated as a comparison of binary strings.

Simple comparison operations (>=, >, =, <,
<=, sorting, and grouping) are based on each
character’s “sort value.” Characters with the
same sort value are treated as the same character. For
example, if e and
é have the same sort value in a
given collation, they compare as equal.

The default character set and collation are
utf8mb4 and
utf8mb4_0900_ai_ci, so nonbinary string
comparisons are case-insensitive by default. This means that
if you search with
col_name LIKE
'a%', you get all column values that start with
A or a. To make this
search case-sensitive, make sure that one of the operands has
a case-sensitive or binary collation. For example, if you are
comparing a column and a string that both have the
utf8mb4 character set, you can use the
COLLATE operator to cause either operand to
have the utf8mb4_0900_as_cs or
utf8mb4_bin collation:

col_name COLLATE utf8mb4_0900_as_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE utf8mb4_0900_as_cs
col_name COLLATE utf8mb4_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE utf8mb4_bin

If you want a column always to be treated in case-sensitive
fashion, declare it with a case-sensitive or binary collation.
See Section 13.1.20, “CREATE TABLE Statement”.

To cause a case-sensitive comparison of nonbinary strings to
be case-insensitive, use COLLATE to name a
case-insensitive collation. The strings in the following
example normally are case-sensitive, but
COLLATE changes the comparison to be
case-insensitive:

mysql> SET NAMES 'utf8mb4';
mysql> SET @s1 = 'MySQL' COLLATE utf8mb4_bin,
           @s2 = 'mysql' COLLATE utf8mb4_bin;
mysql> SELECT @s1 = @s2;
+-----------+
| @s1 = @s2 |
+-----------+
|         0 |
+-----------+
mysql> SELECT @s1 COLLATE utf8mb4_0900_ai_ci = @s2;
+--------------------------------------+
| @s1 COLLATE utf8mb4_0900_ai_ci = @s2 |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

A binary string is case-sensitive in comparisons. To compare
the string as case-insensitive, convert it to a nonbinary
string and use COLLATE to name a
case-insensitive collation:

mysql> SET @s = BINARY 'MySQL';
mysql> SELECT @s = 'mysql';
+--------------+
| @s = 'mysql' |
+--------------+
|            0 |
+--------------+
mysql> SELECT CONVERT(@s USING utf8mb4) COLLATE utf8mb4_0900_ai_ci = 'mysql';
+----------------------------------------------------------------+
| CONVERT(@s USING utf8mb4) COLLATE utf8mb4_0900_ai_ci = 'mysql' |
+----------------------------------------------------------------+
|                                                              1 |
+----------------------------------------------------------------+

To determine whether a value is compared as a nonbinary or
binary string, use the
COLLATION() function. This
example shows that VERSION()
returns a string that has a case-insensitive collation, so
comparisons are case-insensitive:

mysql> SELECT COLLATION(VERSION());
+----------------------+
| COLLATION(VERSION()) |
+----------------------+
| utf8_general_ci      |
+----------------------+

For binary strings, the collation value is
binary, so comparisons are case sensitive.
One context in which you can expect to see
binary is for compression functions, which
return binary strings as a general rule: string:

mysql> SELECT COLLATION(COMPRESS('x'));
+--------------------------+
| COLLATION(COMPRESS('x')) |
+--------------------------+
| binary                   |
+--------------------------+

To check the sort value of a string, the
WEIGHT_STRING() may be helpful.
See Section 12.8, “String Functions and Operators”.

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

SELECT * FROM t1 WHERE date >= '2003-05-05';

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

When you compare a DATE,
TIME,
DATETIME, or
TIMESTAMP to a constant string
with the <, <=,
=, >=,
>, or BETWEEN
operators, MySQL normally converts the string to an internal
long integer for faster comparison (and also for a bit more
“relaxed” string checking). However, this
conversion is subject to the following exceptions:

For those exceptions, the comparison is done by converting the
objects to strings and performing a string comparison.

To be on the safe side, assume that strings are compared as
strings and use the appropriate string functions if you want
to compare a temporal value to a string.

The special “zero” date
'0000-00-00' can be stored and retrieved as
'0000-00-00'. When a
'0000-00-00' date is used through
Connector/ODBC, it is automatically converted to
NULL because ODBC cannot handle that kind
of date.

Because MySQL performs the conversions just described, the
following statements work (assume that
idate is a
DATE column):

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

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

However, the following statement does not work:

SELECT idate FROM t1 WHERE STRCMP(idate,'20030505')=0;

STRCMP() is a string function,
so it converts idate to a string in
'YYYY-MM-DD'
format and performs a string comparison. It does not convert
'20030505' to the date
'2003-05-05' and perform a date comparison.

If you enable the
ALLOW_INVALID_DATES SQL
mode, MySQL permits you to store dates that are given only
limited checking: MySQL requires only that the day is in the
range from 1 to 31 and the month is in the range from 1 to 12.
This makes MySQL very convenient for Web applications where
you obtain year, month, and day in three different fields and
you want to store exactly what the user inserted (without date
validation).

MySQL permits you to store dates where the day or month and
day are zero. This is convenient if you want to store a
birthdate in a DATE column and
you know only part of the date. To disallow zero month or day
parts in dates, enable the
NO_ZERO_IN_DATE mode.

MySQL permits you to store a “zero” value of
'0000-00-00' as a “dummy
date.” This is in some cases more convenient than using
NULL values. If a date to be stored in a
DATE column cannot be converted
to any reasonable value, MySQL stores
'0000-00-00'. To disallow
'0000-00-00', enable the
NO_ZERO_DATE mode.

To have MySQL check all dates and accept only legal dates
(unless overridden by IGNORE), set the
sql_mode system variable to
"NO_ZERO_IN_DATE,NO_ZERO_DATE".

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 “the person is known to
have no phone, and thus no phone number.”

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

In SQL, the NULL value is never true 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);

To search for column values that are NULL,
you cannot use an expr = NULL test. The
following statement returns no rows, because expr =
NULL is never true 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
statements show 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 = '';

See Section 3.3.4.6, “Working with NULL Values”, for additional
information and examples.

You can add an index on a column that can have
NULL values if you are using the
MyISAM, InnoDB, or
MEMORY storage engine. Otherwise, you must
declare an indexed column NOT NULL, and you
cannot insert NULL into the column.

When reading data with LOAD
DATA, empty or missing columns are updated with
''. To load a NULL value
into a column, use N in the data file. The
literal word NULL may also be used under
some circumstances. See Section 13.2.7, “LOAD DATA Statement”.

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

When using ORDER BY,
NULL values are presented first, or last if
you specify DESC to sort in descending
order.

Aggregate (group) 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 produces 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;

For some data types, MySQL handles NULL
values specially. If you insert NULL into a
TIMESTAMP column, the current
date and time is inserted. If you insert
NULL into an integer or floating-point
column that has the AUTO_INCREMENT
attribute, the next number in the sequence is inserted.

An alias can be used in a query select list to give a column a
different name. You can use the alias in GROUP
BY, ORDER BY, or
HAVING clauses to refer to the column:

SELECT SQRT(a*b) AS root FROM tbl_name
  GROUP BY root HAVING root > 0;
SELECT id, COUNT(*) AS cnt FROM tbl_name
  GROUP BY id HAVING cnt > 0;
SELECT id AS 'Customer identity' FROM tbl_name;

Standard SQL disallows references to column aliases in a
WHERE clause. This restriction is imposed
because when the WHERE clause is evaluated,
the column value may not yet have been determined. For
example, the following query is illegal:

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

The WHERE clause determines which rows
should be included in the GROUP BY clause,
but it refers to the alias of a column value that is not known
until after the rows have been selected, and grouped by the
GROUP BY.

In the select list of a query, a quoted column alias can be
specified using identifier or string quoting characters:

SELECT 1 AS `one`, 2 AS 'two';

Elsewhere in the statement, quoted references to the alias
must use identifier quoting or the reference is treated as a
string literal. For example, this statement groups by the
values in column id, referenced using the
alias `a`:

SELECT id AS 'a', COUNT(*) AS cnt FROM tbl_name
  GROUP BY `a`;

This statement groups by the literal string
'a' and does not work as you may expect:

SELECT id AS 'a', COUNT(*) AS cnt FROM tbl_name
  GROUP BY 'a';

If you receive the following message when trying to perform a
ROLLBACK, it
means that one or more of the tables you used in the
transaction do not support transactions:

Warning: Some non-transactional changed tables couldn't be rolled back

These nontransactional tables are not affected by the
ROLLBACK
statement.

If you were not deliberately mixing transactional and
nontransactional tables within the transaction, the most
likely cause for this message is that a table you thought was
transactional actually is not. This can happen if you try to
create a table using a transactional storage engine that is
not supported by your mysqld server (or
that was disabled with a startup option). If
mysqld does not support a storage engine,
it instead creates the table as a MyISAM
table, which is nontransactional.

You can check the storage engine for a table by using either
of these statements:

SHOW TABLE STATUS LIKE 'tbl_name';
SHOW CREATE TABLE tbl_name;

See Section 13.7.7.38, “SHOW TABLE STATUS Statement”, and
Section 13.7.7.10, “SHOW CREATE TABLE Statement”.

To check which storage engines your mysqld
server supports, use this statement:

SHOW ENGINES;

See Section 13.7.7.16, “SHOW ENGINES Statement” for full details.

If the total length of the
DELETE statement for
related_table is more than the default
value of the
max_allowed_packet system
variable, you should split it into smaller parts and execute
multiple DELETE statements. You
probably get the fastest DELETE
by specifying only 100 to 1,000
related_column values per statement if the
related_column is indexed. If the
related_column isn’t indexed, the speed is
independent of the number of arguments in the
IN clause.

If you have a complicated query that uses many tables but that
returns no rows, you should use the following procedure to
find out what is wrong:

Floating-point numbers sometimes cause confusion because they
are approximate and not stored as exact values. A
floating-point value as written in an SQL statement may not be
the same as the value represented internally. Attempts to
treat floating-point values as exact in comparisons may lead
to problems. They are also subject to platform or
implementation dependencies. The
FLOAT and
DOUBLE data types are subject
to these issues. For DECIMAL
columns, MySQL performs operations with a precision of 65
decimal digits, which should solve most common inaccuracy
problems.

The following example uses
DOUBLE to demonstrate how
calculations that are done using floating-point operations are
subject to floating-point error.

mysql> CREATE TABLE t1 (i INT, d1 DOUBLE, d2 DOUBLE);
mysql> 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.4 | 21.4 |
|    2 |  76.8 | 76.8 |
|    3 |   7.4 |  7.4 |
|    4 |  15.4 | 15.4 |
|    5 |   7.2 |  7.2 |
|    6 | -51.4 |    0 |
+------+-------+------+

The result is correct. Although the first five records look
like they should not satisfy the comparison (the values of
a and b do not appear to
be different), they may do so because the difference between
the numbers shows up around the tenth decimal or so, depending
on factors such as computer architecture or the compiler
version or optimization level. For example, different CPUs may
evaluate floating-point numbers differently.

If columns d1 and d2 had
been defined as DECIMAL rather
than DOUBLE, the result of the
SELECT query would have
contained only one row—the last one shown above.

The correct way to do floating-point number comparison is to
first decide on an acceptable tolerance for differences
between the numbers and then do the comparison against the
tolerance value. For example, if we agree that floating-point
numbers should be regarded the same if they are same within a
precision of one in ten thousand (0.0001), the comparison
should be written to find differences larger than the
tolerance value:

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.4 |    0 |
+------+-------+------+
1 row in set (0.00 sec)

Conversely, to get rows where the numbers are the same, the
test should find differences within the tolerance value:

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.4 | 21.4 |
|    2 | 76.8 | 76.8 |
|    3 |  7.4 |  7.4 |
|    4 | 15.4 | 15.4 |
|    5 |  7.2 |  7.2 |
+------+------+------+
5 rows in set (0.03 sec)

Floating-point values are subject to platform or
implementation dependencies. Suppose that you execute the
following statements:

CREATE TABLE t1(c1 FLOAT(53,0), c2 FLOAT(53,0));
INSERT INTO t1 VALUES('1e+52','-1e+52');
SELECT * FROM t1;

On some platforms, the SELECT statement
returns inf and -inf. On
others, it returns 0 and
-0.

An implication of the preceding issues is that if you attempt
to create a replica by dumping table contents with
mysqldump on the source and reloading the
dump file into the replica, tables containing floating-point
columns might differ between the two hosts.

B.3.5 Optimizer-Related Issues

• Use the EXPLAIN statement to
  get information about how MySQL processes a query. To use
  it, just add the keyword
  EXPLAIN to the front of your
  SELECT statement:

  mysql> EXPLAIN SELECT * FROM t1, t2 WHERE t1.i = t2.i;
  

  EXPLAIN is discussed in more
  detail in Section 13.8.2, “EXPLAIN Statement”.

• Use ANALYZE TABLE
tbl_name to update the
  key distributions for the scanned table. See
  Section 13.7.3.1, “ANALYZE TABLE Statement”.

• Use FORCE INDEX for the scanned table to
  tell MySQL that table scans are very expensive compared to
  using the given index:

  SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
  WHERE t1.col_name=t2.col_name;
  

  USE INDEX and IGNORE
INDEX may also be useful. See
  Section 8.9.4, “Index Hints”.

• Global and table-level STRAIGHT_JOIN. See
  Section 13.2.10, “SELECT Statement”.

• You can tune global or thread-specific system variables. For
  example, start mysqld with the
  --max-seeks-for-key=1000
  option or use SET max_seeks_for_key=1000
  to tell the optimizer to assume that no key scan causes more
  than 1,000 key seeks. See
  Section 5.1.8, “Server System Variables”.

B.3.6 Table Definition-Related Issues

If you get a duplicate-key error when using
ALTER TABLE to change the
character set or collation of a character column, the cause is
either that the new column collation maps two keys to the same
value or that the table is corrupted. In the latter case, you
should run REPAIR TABLE on the
table. REPAIR TABLE works for
MyISAM, ARCHIVE, and
CSV tables.

If you use ALTER TABLE on a
transactional table or if you are using Windows,
ALTER TABLE unlocks the table
if you had done a
LOCK
TABLE on it. This is done because
InnoDB and these operating systems cannot
drop a table that is in use.

Temporary tables created with
CREATE TEMPORARY
TABLE have the following limitations:

B.3.7 Known Issues in MySQL

• Subquery optimization for IN is not as
  effective as for =.

• Even if you use lower_case_table_names=2
  (which enables MySQL to remember the case used for databases
  and table names), MySQL does not remember the case used for
  database names for the function
  DATABASE() or within the
  various logs (on case-insensitive systems).

• Dropping a FOREIGN KEY constraint does
  not work in replication because the constraint may have
  another name on the replica.

• REPLACE (and
  LOAD DATA with the
  REPLACE option) does not
  trigger ON DELETE CASCADE.

• DISTINCT with ORDER BY
  does not work inside
  GROUP_CONCAT() if you do not
  use all and only those columns that are in the
  DISTINCT list.

• When inserting a big integer value (between
  2⁶³ and
  2⁶⁴−1) into a decimal or
  string column, it is inserted as a negative value because
  the number is evaluated in signed integer context.

• With statement-based binary logging, the source server
  writes the executed queries to the binary log. This is a
  very fast, compact, and efficient logging method that works
  perfectly in most cases. However, it is possible for the
  data on the source and replica to become different if a
  query is designed in such a way that the data modification
  is nondeterministic (generally not a recommended practice,
  even outside of replication).

  For example:

  • CREATE
TABLE ... SELECT or
    INSERT
... SELECT statements that insert zero or
    NULL values into an
    AUTO_INCREMENT column.

  • DELETE if you are
    deleting rows from a table that has foreign keys with
    ON DELETE CASCADE properties.

  • REPLACE ...
SELECT, INSERT IGNORE ...
SELECT if you have duplicate key values in the
    inserted data.

  If and only if the preceding queries
  have no ORDER BY clause guaranteeing a
  deterministic order.

  For example, for
  INSERT ...
SELECT with no ORDER BY, the
  SELECT may return rows in a
  different order (which results in a row having different
  ranks, hence getting a different number in the
  AUTO_INCREMENT column), depending on the
  choices made by the optimizers on the source and replica.

  A query is optimized differently on the source and replica
  only if:

  • The table is stored using a different storage engine on
    the source than on the replica. (It is possible to use
    different storage engines on the source and replica. For
    example, you can use InnoDB on the
    source, but MyISAM on the replica if
    the replica has less available disk space.)

  • MySQL buffer sizes
    (key_buffer_size, and
    so on) are different on the source and replica.

  • The source and replica run different MySQL versions, and
    the optimizer code differs between these versions.

  This problem may also affect database restoration using
  mysqlbinlog|mysql.

  The easiest way to avoid this problem is to add an
  ORDER BY clause to the aforementioned
  nondeterministic queries to ensure that the rows are always
  stored or modified in the same order. Using row-based or
  mixed logging format also avoids the problem.

• Log file names are based on the server host name if you do
  not specify a file name with the startup option. To retain
  the same log file names if you change your host name to
  something else, you must explicitly use options such as
  --log-bin=old_host_name-bin.
  See Section 5.1.7, “Server Command Options”. Alternatively, rename
  the old files to reflect your host name change. If these are
  binary logs, you must edit the binary log index file and fix
  the binary log file names there as well. (The same is true
  for the relay logs on a replica.)

• mysqlbinlog does not delete temporary
  files left after a LOAD DATA
  statement. See Section 4.6.8, “mysqlbinlog — Utility for Processing Binary Log Files”.

• RENAME does not work with
  TEMPORARY tables or tables used in a
  MERGE table.

• When using SET CHARACTER SET, you cannot
  use translated characters in database, table, and column
  names.

• Prior to MySQL 8.0.17, you cannot use _
  or % with ESCAPE in
  LIKE ...
ESCAPE.

• The server uses only the first
  max_sort_length bytes when
  comparing data values. This means that values cannot
  reliably be used in GROUP BY,
  ORDER BY, or DISTINCT
  if they differ only after the first
  max_sort_length bytes. To
  work around this, increase the variable value. The default
  value of max_sort_length is
  1024 and can be changed at server startup time or at
  runtime.

• Numeric calculations are done with
  BIGINT or
  DOUBLE (both are normally 64
  bits long). Which precision you get depends on the function.
  The general rule is that bit functions are performed with
  BIGINT precision,
  IF() and
  ELT() with
  BIGINT or
  DOUBLE precision, and the
  rest with DOUBLE precision.
  You should try to avoid using unsigned long long values if
  they resolve to be larger than 63 bits (9223372036854775807)
  for anything other than bit fields.

• You can have up to 255 ENUM
  and SET columns in one table.

• In MIN(),
  MAX(), and other aggregate
  functions, MySQL currently compares
  ENUM and
  SET columns by their string
  value rather than by the string’s relative position in the
  set.

• In an UPDATE statement,
  columns are updated from left to right. If you refer to an
  updated column, you get the updated value instead of the
  original value. For example, the following statement
  increments KEY by 2,
  not 1:

  mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1;
  

• You can refer to multiple temporary tables in the same
  query, but you cannot refer to any given temporary table
  more than once. For example, the following does not work:

  mysql> SELECT * FROM temp_table, temp_table AS t2;
  ERROR 1137: Can't reopen table: 'temp_table'
  

• The optimizer may handle DISTINCT
  differently when you are using “hidden” columns
  in a join than when you are not. In a join, hidden columns
  are counted as part of the result (even if they are not
  shown), whereas in normal queries, hidden columns do not
  participate in the DISTINCT comparison.

  An example of this is:

  SELECT DISTINCT mp3id FROM band_downloads
         WHERE userid = 9 ORDER BY id DESC;
  

  and

  SELECT DISTINCT band_downloads.mp3id
         FROM band_downloads,band_mp3
         WHERE band_downloads.userid = 9
         AND band_mp3.id = band_downloads.mp3id
         ORDER BY band_downloads.id DESC;
  

  In the second case, you may get two identical rows in the
  result set (because the values in the hidden
  id column may differ).

  Note that this happens only for queries that do not have the
  ORDER BY columns in the result.

• If you execute a PROCEDURE on a query
  that returns an empty set, in some cases the
  PROCEDURE does not transform the columns.

• Creation of a table of type MERGE does
  not check whether the underlying tables are compatible
  types.

• If you use ALTER TABLE to add
  a UNIQUE index to a table used in a
  MERGE table and then add a normal index
  on the MERGE table, the key order is
  different for the tables if there was an old,
  non-UNIQUE key in the table. This is
  because ALTER TABLE puts
  UNIQUE indexes before normal indexes to
  be able to detect duplicate keys as early as possible.