Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

ERROR 1114 (HY000): the solution of The table & # 039

2025-01-23 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

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:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report