MySQL appears the table is full solution "turn"

[MySQL FAQ] Series-What you don't know about the table is the full of those thingstime 2014-08-21 12:18:56 mysql Chinese web Originalhttp://imysql.com/2014/08/21/mysql-faq-howto-deal-with-table-full.shtmlTheme MySQL

When we want to write new data and the "The table is full" alarm error, do not worry, follow the following ideas to step-by-stage analysis can:

1. Check the operating system and MySQL error log files

Verify that the operating system's file system is not error-free, and that there are some of the most intuitive and visible error alerts in the MySQL error log file.

It is possible that the database files exceed the file size limits of the operating system layer, such as Fat/fat32 and the lower version of Linux, the maximum size of files can not be greater than 2G (up to 4G), which requires converting FAT32 to NTFS, or upgrading the Linux version.

2. Confirm that the disk space is not full

Perform df-h to view the remaining disk space, and if you find that the disk space is really exhausted, delete the unwanted files as soon as possible.

If you calculate the sum of each directory by Du and find that you are not going to run out of disk space, you need to be aware that a deleted file may not be fully released, causing DF to appear to be exhausted, but du is not counted.

You can execute lsof | grep-i deleted find the deleted large file, kill the corresponding process, and release the file descriptor.

If the process cannot be killed, for example, if the mysqld process is occupied, you can find in MySQL which internal thread is in use and stop the thread.

There has been an example of this:

Using VIM to open the MySQL slow query log, the exit when the "WQ" command, that is, save the exit, the result of tragedy occurred.

Because in the time of its opening, slow query log has a new log generated, will continue to write, but he exited with the method of saving the exit into a "new" file (or the new file handle handler), the "new" file can not be recognized by the MYSQLD process,

The mysqld process still writes slow query log to the original file (or file handle) that it opened, and the log file continues to grow, but the file that is manually saved is no longer growing, and viewing the file directly does not show any exceptions.

Only use lsof-p ' pidof mysqld ' to see the file at this time.

The workaround is simple, back up the original file and execute the following command:

FLUSH SLOW LOGS;

Note: MySQL 5.5 only supports keywords such as Binary/engine/error/general/relay/slow, and the previous version only refreshes all logs.

3. Confirm the data table status

• If it's the MyISAM engine,

By default, the MyISAM engine can support a maximum of 256TB ( myisam_data_pointer_size = 6,256^6 = 256TB), unless the operating system layer is limited.

In MySQL5.0, the MyISAM engine row record is the dynamic length by default, with a single table up to 256tb,myisam row pointer (myisam_data_pointer_size) length of 6 bytes.

Prior to this, the MyISAM line pointer was 4 bytes in length and supported only 4GB of data. The maximum value of a row pointer can be set to 8 bytes.

In order to increase the maximum capacity of the MyISAM table, you can modify the value of the table definition setting max_rows when the row pointer setting is small enough:

' xx ' Engine=myisam max_rows=nn

remark: in the table definition, the avg_row_length property defines the maximum length of the Blob/text field type.

• If it's the InnoDB engine,

ibdata* shared tablespace The last file is not set to self-grow or exceeds the single file size limit for 32-bit systems.

Workaround:

1, ibdata* the last file (not the last file can not be set to autogrow ) set to automatically grow;

2, check the operating system, migrated to 64-bit operating system;

3, turn into a separate table space;

4, the deletion of historical data, reorganization of the table space;

• If it's a memory engine,

1, appropriate increase max_heap_table_size Settings (note that the value is session level, do not set too large, such as 1GB, generally not recommended more than 256MB);

2, execute Alter TABLE T_MEM engine=memory; The table space is restructured, otherwise the new data cannot be written ;

3, delete some historical data or directly empty, reorganization table space;

4, set big_tables = 1 , all temporary tables are stored in disk, not in memory, the disadvantage is that if a SQL execution requires temporary tables, performance will be much worse;

By the way, if the data table has a column of self-increment int key, but the ID value reaches the int maximum value, MyISAM, MEMORY, InnoDB three kinds of engine alarm information is not the same.

The alarm information for the InnoDB engine is similar to this:

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

The MyISAM and memory engines are the same: ERROR 1062 (23000): Duplicate entry ' 4294967295′for key ' PRIMARY '

Reference

MySQL Manual: b.5.2.12 The table is full

See if you've modified

Mysql> Show variables like '%max_heap_table_size% ';

There are two reasons why MySQL has a "table is full" problem:

One. You are using the MEMORY (HEAP) storage engine; Need to increase the value of the max_heap_table_size system variable. See section 5.1.3, "Server System Variables".

Then modify the MySQL configuration file/etc/my.cnf, add/Modify two lines under [mysqld]:
Tmp_table_size = 256M
Max_heap_table_size = 256M
System defaults to 16M, restart MySQL after the completion of the modification

Two. The hard disk space is full, clean the hard drive.

MySQL appears the table is full solution "turn"