ERROR 1114 (HY000): the solution of The table & # 039
An error of 1114 was encountered when executing sql today, as follows:
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
Check the official document, there is no answer, it says that the operating system file limit caused this error, it can be understood that the operating system single file size is 2G, then when using innodb_file_per_table=on, a table data will be created in a file, then the table data size can only be 2G.
Http://dev.mysql.com/doc/refman/5.7/en/full-table.html
The problem is that my watch doesn't have 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)
About 16m, another useful information is that the storage engine of this table is MEMORY.
This is due to create table test like information_schema.tables, create table test1 like test; and information_schema.tables is the tables table is the memory storage engine.
The size of memory is affected by the parameter 'max_heap_table_size''.
Mysql > show variables like 'max_heap_table_size'
+-+ +
| | Variable_name | Value |
+-+ +
| | max_heap_table_size | 16777216 | |
+-+ +
Modify the size of this parameter to verify:
Set max_heap_table_size=167772160
It's still wrong.
According to the information on the Internet, modify the my.cnf file, and then restart:
Tmp_table_size = 256m
Max_heap_table_size = 256m
Just execute it again.
Mysql > insert into test2 select * from test2
Query OK, 9216 rows affected (1.22 sec)
Records: 9216 Duplicates: 0 Warnings: 0
At this time, the maximum length of the watch has also become 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:
