MySQL的常见错误的解决方法(英文)(1)(3)

Badly configured TCP/IP.

Faulty Ethernets, hubs, switches, cables, and so forth. This can be diagnosed properly only by replacing hardware.

The max_allowed_packet variable value is too small or queries require more memory than you have allocated for mysqld. See Section 9, “Packet too large”.

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

11. The table is full
There are several ways a full-table error can occur:

You are using a MySQL server older than 3.23 and an in-memory temporary table becomes larger than tmp_table_size bytes. To avoid this problem, you can use the -O tmp_table_size=val option to make mysqld increase the temporary table size or use the SQL option SQL_BIG_TABLES before you issue the problematic query. See Section 13.5.3, “SET Syntax”.

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

As of MySQL 3.23, this problem should not occur. If an in-memory temporary table becomes larger than tmp_table_size, the server automatically converts it to a disk-based MyISAM table.

You are using InnoDB tables and run out of room in the InnoDB tablespace. In this case, the solution is to extend the InnoDB tablespace. See Section 15.7, “Adding and Removing InnoDB Data and Log Files”.

You are using ISAM or MyISAM tables on an operating system that supports files only up to 2GB in size and you have hit this limit for the data file or index file.

You are using a MyISAM table and the space required for the table exceeds what is allowed by the internal pointer size. If you don't specify the MAX_ROWS table option when you create a table, MySQL uses the myisam_data_pointer_size system variable. From MySQL 5.0.6 on, the default value is 6 bytes, which is enough to allow 65,536TB of data. Before MySQL 5.0.6, the default value is 4 bytes, which is enough to allow only 4GB of data. See the section called “Server System Variables”.

You can check the maximum data/index sizes by using this statement:

SHOW TABLE STATUS FROM database LIKE 'tbl_name';

You also can use myisamchk -dv /path/to/table-index-file.

If the pointer size is too small, you can fix the problem by using ALTER TABLE:

ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

You have to specify AVG_ROW_LENGTH only for tables with BLOB or TEXT columns; in this case, MySQL can't optimize the space required based only on the number of rows.

12. Can't create/write to file
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.3.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 filesystem is full:

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

13. Commands out of sync
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.

14. Ignoring user
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.

The following list indicates possible causes of and fixes for this problem:

You may be running a new version of mysqld with an old user table. You can check this by executing mysqlshow mysql user to see whether the Password column is shorter than 16 characters. If so, you can correct this condition by running the scripts/add_long_password script.

The account has an old password (eight characters long) and you didn't start mysqld with the --old-protocol option. Update the account in the user table to have a new password or restart mysqld with the --old-protocol option.

You have specified a password in the user table without using the PASSWORD() function. Use mysql to update the account in the user table with a new password, making sure to use the PASSWORD() function:

mysql> UPDATE user SET Password=PASSWORD('newpwd')
-> WHERE User='some_user' AND Host='some_host';

15. Table 'tbl_name' doesn't exist
If you get either of the following errors, it usually means that no table exists in the current 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:

Because MySQL uses directories and files to store databases and tables, database and table names are case sensitive if they are located on a filesystem that has case-sensitive filenames.

Even for filesystems that are not case sensitive, such as on Windows, all references to a given table within a query must use the same lettercase.

You can check which tables are in the current database with SHOW TABLES. See Section 13.5.4, “SHOW Syntax”.

16. Can't initialize character set
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:

The character set is a multi-byte character set and you have no support for the character set in the client. In this case, you need to recompile the client by running configure with the --with-charset=charset_name or --with-extra-charsets=charset_name option. See Section 2.8.2, “Typical configure Options”.

All standard MySQL binaries are compiled with --with-extra-character-sets=complex, which enables support for all multi-byte character sets. See Section 5.10.1, “The Character Set Used for Data and Sorting”.

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

In this case, you need to use one of the following methods to solve the problem:

Recompile the client with support for the character set. See Section 2.8.2, “Typical configure Options”.

Specify to the client the directory where the character set definition files are located. For many clients, you can do this with the --character-sets-dir option.

Copy the character definition files to the path where the client expects them to be.

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

shell> perror 23
Error code  23:  File table overflow
shell> perror 24
Error code  24:  Too many open files
shell> perror 11
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_cache system variable (the default value is 64). 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 (as of MySQL 3.23.30) set the open_files_limit system variable. See the section called “Server System Variables”. The easiest way to set these values is to add an option to your option file. See Section 4.3.2, “Using Option Files”. If you have an old version of mysqld that doesn't 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.

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.