A.2.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=# option to make mysqld increase the temporary table size or use the SQL option SQL_BIG_TABLES before you issue the problematic query.

• 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 9.8, "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. Its default value of 4 bytes is enough to allow only 4GB of data.) See Section 4.2.3, "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.

A.2.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. The 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 already exist. See Section 3.3.2, "Using Option Files."

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

A.2.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.

A.2.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';

A.2.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.

A.2.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.3.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 4.7.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.3.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.

  

This chapter is from MySQL Administrator's Guide, by MySQL AB. (Sams, 2004, ISBN: 0672326345). Check it out at your favorite bookstore today. Buy this book now.

Next: File Not Found >>

Discuss Troubleshooting Problems with MySQL Programs   hi this is what is happening when i try to get into my email. I really need please... >>> Post your comment now!

>>> More MySQL Articles          >>> More By Sams Publishing