insert into test1 select * from test; Query OK, 1778 rows affected (0.06 sec) Records: 1778 Duplicat..."/>
您好,登錄后才能下訂單哦!
今天執(zhí)行sql碰到 1114的錯(cuò)誤,如下:
mysql> insert into test1 select * from test;
Query OK, 1778 rows affected (0.06 sec)
Records: 1778 Duplicates: 0 Warnings: 0
mysql> insert into test1 select * from test;
ERROR 1114 (HY000): The table 'test1' is full
查看官方的文檔,并沒有答案,里面說到操作系統(tǒng)文件的限制引起了這個(gè)錯(cuò)誤,可以理解,操作系統(tǒng)單個(gè)文件大小最大是2G,那么采用innodb_file_per_table=on 時(shí),會(huì)把一個(gè)表數(shù)據(jù)創(chuàng)建在一個(gè)文件中,那么這個(gè)表數(shù)據(jù)的大小只能是2G了。
http://dev.mysql.com/doc/refman/5.7/en/full-table.html
問題是我的表沒有2G:
mysql> select * from information_schema.tables where table_name='test' \G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: test
TABLE_TYPE: BASE TABLE
ENGINE: MEMORY
VERSION: 10
ROW_FORMAT: Fixed
TABLE_ROWS: 1778
AVG_ROW_LENGTH: 9440
DATA_LENGTH: 16855944
MAX_DATA_LENGTH: 16765440
INDEX_LENGTH: 0
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2016-09-19 13:45:37
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
1 row in set (0.00 sec)
大約16M, 另一個(gè)有用的信息是這個(gè)表的存儲(chǔ)引擎是 MEMORY.
這個(gè)是由于 create table test like information_schema.tables, create table test1 like test; 而information_schema.tables是tables表是memory存儲(chǔ)引擎所致。
而 memory 的大小受到 'max_heap_table_size' 參數(shù)影響
mysql> show variables like 'max_heap_table_size';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
修改此參數(shù)大小驗(yàn)證一下:
set max_heap_table_size=167772160
還是報(bào)錯(cuò)。
根據(jù)網(wǎng)上的資料,修改my.cnf文件,然后重新啟動(dòng):
tmp_table_size = 256M
max_heap_table_size = 256M
再次執(zhí)行就可以了
mysql> insert into test2 select * from test2;
Query OK, 9216 rows affected (1.22 sec)
Records: 9216 Duplicates: 0 Warnings: 0
此時(shí)表的最大長(zhǎng)度也變?yōu)?256M了。
mysql> select * from information_schema.tables where table_name='test2' \G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: test2
TABLE_TYPE: BASE TABLE
ENGINE: MEMORY
VERSION: 10
ROW_FORMAT: Fixed
TABLE_ROWS: 18432
AVG_ROW_LENGTH: 9440
DATA_LENGTH: 174807384
MAX_DATA_LENGTH: 268313120
INDEX_LENGTH: 0
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2016-09-19 14:37:29
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。