Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

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 --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 14.2.7, “Adding and Removing InnoDB Data and Log Files”.

  • You are using 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. The default value is 6 bytes, which is enough to allow 256TB of data. See Section 5.2.2, “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.


 
 
  Published under the terms of the GNU General Public License Design by Interspire