最後更新: 2016-01-14

Error:

MySQL Error: 1114

原因:

By default MySQL has a data limit of 4GB(2^32 = 4GB) for a MyISAM table.

show table status like 'mail_content';

Rows   | Avg_row_length | Data_length  | Max_data_length
821573 |          5227  |  4294967284  |      4294967295

Data_length = Rows X Avg_row_length  < Max_data_length

Resize it

# 將 Avg_row_length 設定成比現在的值大
# 主要係修改 MAX_ROWS

USE mydb

REPAIR TABLE mytables

ALTER TABLE mytables MAX_ROWS=1000000000 AVG_ROW_LENGTH=6000;

# "Max_data_length" has been increased.
# MySQL for 32-bit processors:  4.2 billion rows record

# Rows: MyISAM  store the exact count; InnoDB, this value is an approximation
# Data_length: The length of the data file.

* run CHECK TABLE before the ALTER TABLE

Lost rows after changing MAX_ROWS on 4GB table

The ALTER TABLE created a new table, copied all data over, dropped the old one, and renamed the new one.

The smaller size of the new table might result from deleted rows in the old table, which are not copied over.

If you want to be sure, rename the new table, and restore the old table from a backup. Run CHECK TABLE on both.

If the number of rows is still different, run a proper JOIN to find the rows in the old table, that are missing in the new table.

在設定控制 tables size

/etc/my.cnf

# The default pointer size in bytes
# used by CREATE TABLE for MyISAM tables when no MAX_ROWS option is specified.
# 2-7 (256^7 – 1 bytes)
# 4 permits tables up to 4GB
# 5 1T
# 6 256TB (Default)
# Check: SHOW VARIABLES LIKE 'myisam_data_pointer_size';

[mysqld]
myisam_data_pointer_size = 7