Alden Hosting provides professional, efficient, and reliable business-class Web hosting and Website Design services.
Table of Contents
This appendix lists common problems and errors that may occur and potential resolutions, in addition to listing the errors that may appear when you call MySQL from any host language. The first section covers problems and resolutions. Detailed information on errors is provided; The first list displays server error messages. The second list displays client program messages.
MySQL Enterprise The MySQL Network Monitoring and Advisory Service provides a “Virtual DBA” to assist with problem solving. For more information see http://www.mysql.com/products/enterprise/advisors.html.
This section lists some common problems and error messages that you may encounter. It describes how to determine the causes of the problems and what to do to solve them.
When you run into a problem, the first thing you should do is to find out which program or piece of equipment is causing it:
If after you have examined all other possibilities and you have concluded that the MySQL server or a MySQL client is causing the problem, it's time to create 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 that MySQL is causing the problem. Take into consideration all the situations in this chapter. State any problems exactly how they appear when you examine your system. Use the “copy and paste” method for any output and error messages from programs and 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 state only “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 the following information:
When sending a bug report, you should follow the outline described in Section 1.8, “How to Report Bugs or Problems”.
This section lists some errors that users frequently encounter when running MySQL programs. Although the problems show up when you try to run client programs, the solutions to many of the problems involves changing the configuration of the MySQL server.
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
If the MySQL server is running on Windows, you can connect via
TCP/IP. If the server is started with the
The error (2002)
The error (2003)
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 18.104.22.168.3, “Starting and Troubleshooting the MySQL Server”.
If a mysqld process is running, you can
check it by trying the following commands. The port number or
Unix socket filename might be different in your setup.
Note the use of backticks rather than forward quotes with the
Here are some reasons the
If you get the error message
When you're running a MySQL server on Windows with many
TCP/IP connections to it, and you're experiencing that quite
often your clients get a
By default, Windows allows 5000 ephemeral (short-lived) TCP
ports to the user. After any port is closed it will remain
With a small stack of available TCP ports (5000) and a high
number of TCP ports being open and closed over a short
period of time along with the
IMPORTANT: The following procedure involves modifying the Windows registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, view the following article in the Microsoft Knowledge Base: http://support.microsoft.com/kb/256986/EN-US/.
Note: Undoing the above should be as simple as deleting the registry entries you've created.
MySQL 5.0 uses an authentication protocol based on a password hashing algorithm that is incompatible with that used by older (pre-4.1) clients. If you upgrade the server from 4.0, attempts to connect to it with an older client may fail with the following message:
To solve this problem, you should use one of the following approaches:
Note: In older versions of
It may also be possible to compile the older
For additional background on password hashing and authentication, see Section 5.7.9, “Password Hashing as of MySQL 4.1”.
MySQL client programs prompt for a password when invoked with
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
If you get the following error, it means that
mysqld has received many connect requests
from the host
The number of interrupted connect requests allowed is
determined by the value of the
By default, mysqld blocks a host after 10 connection errors. You can adjust the value by starting the server like this:
If you get this error message for a given host, you should
first verify that there isn't anything wrong with TCP/IP
connections from that host. If you are having network
problems, it does you no good to increase the value of the
If you get a
The number of connections allowed is controlled by the
Subscribers to the MySQL Network Monitoring and Advisory
Service receive advice on dynamically configuring the
mysqld actually allows
The maximum number of connections MySQL can support depends on the quality of the thread library 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. Static Linux binaries provided by MySQL AB can support up to 4000 connections.
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
This section also covers the related
The most common reason for the
By default, the server closes the connection after eight hours
if nothing has happened. You can change the time limit by
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
Some other common reasons for the
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.1.4.2, “What to Do If MySQL Keeps Crashing”.
You can get more information about the lost connections by
starting mysqld with the
If you want to create a bug report regarding this problem, be sure that you include the following information:
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 master replication server to a slave.
The largest possible packet that can be transmitted to or from a MySQL 5.0 server or client is 1GB.
When a MySQL client or the mysqld server
receives a packet bigger than
Both the client and the server have their own
If you are using the mysql client program,
That sets the packet size to 32MB.
The server's default
You can also use an option file to set
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
The server error log can be a useful source of information
about connection problems. See Section 5.11.1, “The Error Log”. If
you start the server with the
010301 14:38:23 Aborted connection 854 to db: 'users' user: 'josh'
When any of these things happen, the server increments the
The server increments the
If these kinds of things happen, it might indicate that someone is trying to break into your server!
For reasons of security and performance the advisors
provided by the MySQL Network Monitoring and Advisory
Service pay special attention to the
Other reasons for problems with aborted clients or aborted connections:
The maximum effective table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits. The following table lists some examples of operating system file-size limits. This is only a rough guide and is not intended to be definitive. For the most up-to-date information, be sure to check the documentation specific to your operating system.
Windows users, please note that FAT and VFAT (FAT32) are not considered suitable for production use with MySQL. Use NTFS instead.
On Linux 2.2, you can get
For a detailed overview about LFS in Linux, have a look at Andreas Jaeger's Large File Support in Linux page at http://www.suse.de/~aj/linux_lfs.html.
If you do encounter a full-table error, there are several reasons why it might have occurred:
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
Another cause of this error can be permissions issues. Make
sure that the MySQL server can write to the
Check also the error code that you get with perror. One reason the server cannot write to a table is that the filesystem is full:
If you get
This can happen, for example, if you are using
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
As a result, the account is simply ignored by the permission system.
The following list indicates possible causes of and fixes for this problem:
If you get either of the following errors, it usually means that no table exists in the default database with the given name:
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
You might see an error like this if you have character set problems:
MySQL Connection Failed: Can't initialize character set
This error can have any of the following causes:
If you get
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
To change the number of file descriptors available to
mysqld, you can use the
Note: If you run the tcsh shell, ulimit does not work! tcsh also reports incorrect values when you ask for the current limits. In this case, you should start mysqld_safe using sh.
When you are linking an application program to use the MySQL
client library, you might get undefined reference errors for
symbols that start with
/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 problem by adding
The output from mysql_config might indicate other libraries that should be specified on the link command as well.
If you get
If you get
You might get
mf_format.o(.text+0x201): undefined reference to `__lxstat'
This usually means that your MySQL client library was compiled on a system that is not 100% compatible with yours. In this case, you should download the latest MySQL source distribution and compile MySQL yourself. See Section 2.4.14, “MySQL Installation Using a Source Distribution”.
You might get undefined reference errors at runtime when you
try to execute a MySQL program. If these errors specify
symbols that start with
Another way to solve this problem is by linking your program
statically with the
If you have problems with file permissions, the
ERROR: Can't find file: 'path/with/filename.frm' (Errcode: 13)
By default, MySQL creates database and
In MySQL 3.23.25 and above, MySQL assumes that the value for
If you have never set a
If you set a
The procedure under Windows:
In a Unix environment, the procedure for resetting the
MySQL Enterprise For expert advice on security-related issues, subscribe to the MySQL Network Monitoring and Advisory Service. For more information see http://www.mysql.com/products/enterprise/advisors.html.
Alternatively, on any platform, you can set the new password using the mysql client(but this approach is less secure):
Each MySQL version is tested on many platforms before it is released. This doesn't 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.11.1, “The Error Log”.
On some systems, you can find in the error log a stack trace
of where mysqld died that you can resolve
Many server crashes are caused by corrupted data files or
index files. MySQL updates the files on disk with the
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 crash for you. Please 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
When a disk-full condition occurs, MySQL does the following:
To alleviate the problem, you can take the following actions:
Exceptions to the preceding behavior are when you use
MySQL Enterprise For early notification of possible problems with your MySQL configuration subscribe to the MySQL Network Monitoring and Advisory Service. For more information see http://www.mysql.com/products/enterprise/advisors.html.
MySQL uses the value of the
In MySQL 5.0, the
If the MySQL server is acting as a replication slave, you
should not set
MySQL creates all temporary files as hidden files. This ensures that the temporary files are removed if mysqld is terminated. The disadvantage of using hidden files is that you do not see a big temporary file that fills up the filesystem in which the temporary file directory is located.
MySQL Enterprise Advisors provided by the MySQL Network Monitoring and Advisory Service automatically detect excessive temporary table storage to disk. For more information see http://www.mysql.com/products/enterprise/advisors.html.
When sorting (
(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.
The default location for the Unix socket file that the server
uses for communication with local clients is
On some versions of Unix, anyone can delete files in the
On most versions of Unix, you can protect your
You can check whether the
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:
If you have a problem with
You can set the time zone for the server with the
The allowable values for
By default, MySQL searches are not case sensitive (although
there are some character sets that are never case insensitive,
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.5, “
Simple comparison operations (
The format of a
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
When you compare a
For these exceptional cases, the comparison is done by converting the objects to strings and performing a string comparison.
To keep things safe, 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 date
Because MySQL performs the conversions described above, the following statements work:
However, the following does not work:
If you are using the
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).
If you are not using the
If you are not using the
If the date cannot be converted to any reasonable value, a
If you want MySQL to check all dates and accept only legal
dates (unless overridden by IGNORE), you should set
Date handling in MySQL 5.0.1 and earlier works like MySQL
5.0.2 with the
The concept of the
Both statements insert a value into the
To help with
In SQL, the
If you want to search for column values that are
To look for
See Section 22.214.171.124, “Working with
You can add an index on a column that can have
When reading data with
Aggregate (summary) functions such as
For some data types, MySQL handles
You can use an alias to refer to a column in
SELECT SQRT(a*b) AS root FROM
Standard SQL doesn't allow you to refer to a column alias in a
SELECT id, COUNT(*) AS cnt FROM
If you receive the following message when trying to perform a
Warning: Some non-transactional changed tables couldn't be rolled back
These non-transactional tables are not affected by the
If you were not deliberately mixing transactional and
non-transactional 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 doesn't support a storage engine, it
instead creates the table as a
You can check the storage engine for a table by using either of these statements:
SHOW TABLE STATUS LIKE '
You can check which storage engines your mysqld server supports by using this statement:
You can also use the following statement, and check the value of the variable that is associated with the storage engine in which you are interested:
SHOW VARIABLES LIKE 'have_%';
For example, to determine whether the
MySQL Enterprise Ensure that your data is adequately protected by subscribing to the MySQL Network Monitoring and Advisory Service. For more information see http://www.mysql.com/products/enterprise/advisors.html.
If the total length of the
If you have a complicated query that uses many tables but that doesn't return any rows, you should use the following procedure to find out what is wrong:
Floating-point numbers sometimes cause confusion because they
are approximate. That is, they are not stored as exact values
inside computer architecture. What you can see on the screen
usually is not the exact value of the number. The
Prior to MySQL 5.0.3,
The following example (for versions of MySQL older than 5.0.3)
demonstrates the problem. It shows that even for older
The result is correct. Although the first five records look
like they should not satisfy the comparison (the values of
As of MySQL 5.0.3, you will get only the last row in the above result.
The problem cannot be solved by using
This is what the numbers in column
Depending on your computer architecture, you may or may not see similar results. For example, on some machines you may get the “correct” results by multiplying both arguments by 1, as the following example shows.
Warning: Never use this method in your applications. It is not an example of a trustworthy method!
The reason that the preceding example seems to work is that on the particular machine where the test was done, CPU floating-point arithmetic happens to round the numbers to the same value. However, there is no rule that any CPU should do so, so this method cannot be trusted.
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:
Conversely, to get rows where the numbers are the same, the test should find differences within the tolerance value:
MySQL uses a cost-based optimizer to determine the best way to resolve a query. In many cases, MySQL can calculate the best possible query plan, but sometimes MySQL doesn't have enough information about the data at hand and has to make “educated” guesses about the data.
For the cases when MySQL does not do the "right" thing, tools that you have available to help MySQL are:
MySQL Enterprise For expert advice on configuring MySQL servers for optimal performance, subscribe to the MySQL Network Monitoring and Advisory Service. For more information see http://www.mysql.com/products/enterprise/advisors.html.
Error on rename of './database/name.frm' to './database/B-
In this case, go to the MySQL data directory and delete all
files that have names starting with
If something goes wrong with the renaming operation, MySQL
tries to undo the changes. If something goes seriously wrong
(although this shouldn't happen), MySQL may leave the old
If you use
First, consider whether you really need to change the column
order 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. The
first of the following statements returns columns in the order
If you decide to change the order of table columns anyway, you can do so as follows:
The following list indicates limitations on the use of
This section is a list of the known issues in recent versions of MySQL.
The following problems are known and fixing them is a high priority:
The following issues are known and will be fixed in due time:
MySQL programs have access to several types of error information when the server returns an error. For example, the mysql client program displays errors using the following format:
The message displayed contains three types of information:
Server error information comes from the following source files. For details about the way that error information is defined, see the MySQL Internals manual, available at http://dev.mysql.com/doc/.
MySQL Enterprise MySQL Enterprise subscribers will find numerous articles about server error messages at, Error Messages. For information about subscribing to MySQL Enterprise see http://www.mysql.com/products/enterprise/advisors.html.
Because updates are frequent, it is possible that those files will contain additional error information not listed here.
Client error information comes from the following source files:
Because updates are frequent, it is possible that those files will contain additional error information not listed here.
JSP at alden-servlet-Hosting.com
Servlets at alden-servlet-Hosting.com
Servlet at alden-servlet-Hosting.com
Tomcat at alden-servlet-Hosting.com
MySQL at alden-servlet-Hosting.com
Java at alden-servlet-Hosting.com
sFTP at alden-servlet-Hosting.com
JSP at alden-tomcat-Hosting.com
Servlets at alden-tomcat-Hosting.com
Servlet at alden-tomcat-Hosting.com
Tomcat at alden-tomcat-Hosting.com
MySQL at alden-tomcat-Hosting.com
Java at alden-tomcat-Hosting.com
sFTP at alden-tomcat-Hosting.com
JSP at alden-sftp-Hosting.com
Servlets at alden-sftp-Hosting.com
Servlet at alden-sftp-Hosting.com
Tomcat at alden-sftp-Hosting.com
MySQL at alden-sftp-Hosting.com
Java at alden-sftp-Hosting.com
sFTP at alden-sftp-Hosting.com
JSP at alden-jsp-Hosting.com
Servlets at alden-jsp-Hosting.com
Servlet at alden-jsp-Hosting.com
Tomcat at alden-jsp-Hosting.com
MySQL at alden-jsp-Hosting.com
Java at alden-jsp-Hosting.com
sFTP at alden-jsp-Hosting.com
JSp at alden-java-Hosting.com
Servlets at alden-java-Hosting.com
Servlet at alden-java-Hosting.com
Tomcat at alden-java-Hosting.com
MySQL at alden-java-Hosting.com
Java at alden-java-Hosting.com
sFTP at alden-java-Hosting.com
JSP Servlets Tomcat mysql Java JSP Servlets Tomcat mysql Java JSP Servlets Tomcat mysql Java JSP Servlets Tomcat mysql Java JSP at JSP.aldenWEBhosting.com Servlets at servlets.aldenWEBhosting.com Tomcat at Tomcat.aldenWEBhosting.com mysql at mysql.aldenWEBhosting.com Java at Java.aldenWEBhosting.com Web Hosts Portal Web Links Web Links JSP Web Links servlet Tomcat Docs Web Links Web Links JSP Web Links servlet Web Hosting Tomcat Docs JSP Solutions Web Links JSP Solutions Web Hosting Servlets Solutions Web Links Servlets Solutions Web Hosting Web Links Web Links . .
. . . . jsp hosting servlets hosting web hosting web sites designed cheap web hosting web site hosting myspace web hosting