"Mysql 행 크기가 너무 큼"에 대한 제한 변경

"Mysql 행 크기가 너무 큼"에 대한 제한 변경

---

한도를 어떻게 변경할 수 있습니까?

행 크기가 너무 큽니다 (> 8126). 일부 열을 TEXT 또는 BLOB로 변경하거나 사용하면 ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED도움이 될 수 있습니다. 현재 행 형식에서는 BLOB접두사 768 바이트가 인라인으로 저장됩니다.

표:

id  int(11) No       
name    text    No       
date    date    No       
time    time    No       
schedule    int(11) No       
category    int(11) No       
top_a   varchar(255)    No       
top_b   varchar(255)    No       
top_c   varchar(255)    No       
top_d   varchar(255)    No       
top_e   varchar(255)    No       
top_f   varchar(255)    No       
top_g   varchar(255)    No       
top_h   varchar(255)    No       
top_i   varchar(255)    No       
top_j   varchar(255)    No       
top_title_a varchar(255)    No       
top_title_b varchar(255)    No       
top_title_c varchar(255)    No       
top_title_d varchar(255)    No       
top_title_e varchar(255)    No       
top_title_f varchar(255)    No       
top_title_g varchar(255)    No       
top_title_h varchar(255)    No       
top_title_i varchar(255)    No       
top_title_j varchar(255)    No       
top_desc_a  text    No       
top_desc_b  text    No       
top_desc_c  text    No       
top_desc_d  text    No       
top_desc_e  text    No       
top_desc_f  text    No       
top_desc_g  text    No       
top_desc_h  text    No       
top_desc_i  text    No       
top_desc_j  text    No       
status  int(11) No       
admin_id    int(11) No 

---

질문은 serverfault 에서도 요청되었습니다 .

MySQL 행 크기에 대해 많이 설명하는 이 기사 를 살펴볼 수 있습니다 . TEXT 또는 BLOB 필드를 사용하더라도 페이지의 각 필드에 대해 처음 768 바이트를 인라인으로 저장하기 때문에 행 크기가 여전히 8K (InnoDB 제한)를 초과 할 수 있습니다.

이 문제를 해결하는 가장 간단한 방법은 InnoDB 에서 Barracuda 파일 형식 을 사용하는 것 입니다. 이것은 기본적으로 처음 768 바이트를 저장하는 대신 텍스트 데이터에 대한 20 바이트 포인터 만 저장함으로써 문제를 완전히 제거합니다.

---

OP를 위해 일한 방법은 다음과 같습니다.

1. 섹션 my.cnf아래 의 파일에 다음을 추가 [mysqld]하십시오.

   innodb_file_per_table=1
   innodb_file_format = Barracuda
   

2. ALTER사용할 테이블 ROW_FORMAT=COMPRESSED.

   ALTER TABLE nombre_tabla
       ENGINE=InnoDB
       ROW_FORMAT=COMPRESSED 
       KEY_BLOCK_SIZE=8;
   

---

위의 방법으로 문제가 해결되지 않을 가능성이 있습니다. InnoDB 엔진 의 알려진 (및 검증 된) 버그 이며 현재 임시 수정 사항은 임시 저장소 로 MyISAM 엔진으로 대체하는 것 입니다. 따라서 파일에서 :my.cnf

internal_tmp_disk_storage_engine=MyISAM

---

I ran into this problem recently and solved it a different way. If you are running MySQL version 5.6.20 there is a known bug in the system. See MySQL docs

Important Due to Bug #69477, redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. To address this bug, a patch introduced in MySQL 5.6.20 limits the size of redo log BLOB writes to 10% of the redo log file size. As a result of this limit, innodb_log_file_size should be set to a value greater than 10 times the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields).

In my situation the offending blob table was around 16MB. Thus, the way I solved it was by adding a line to my.cnf that ensured I had at least 10x that amount and then some:

innodb_log_file_size = 256M

---

If you can switch the ENGINE and use MyISAM instead of InnoDB, that should help:

ENGINE=MyISAM

There are two caveats with MyISAM (arguably more):

1. You can't use transactions.
2. You can't use foreign key constraints.

---

Set followings on your my.cnf file and restart mysql server.

innodb_strict_mode = 0

---

I would like to share an awesome answer, it might be helpful. Credits Bill Karwin see here https://dba.stackexchange.com/questions/6598/innodb-create-table-error-row-size-too-large

They vary by InnoDB file format.At present there are 2 formats called Antelope and Barracuda.

The central tablespace file (ibdata1) is always in Antelope format. If you use file-per-table, you can make the individual files use Barracuda format by setting innodb_file_format=Barracuda in my.cnf.

Basic points:

1. One 16KB page of InnoDB data must hold at least two rows of data. Plus each page has a header and a footer containing page checksums and log sequence number and so on. That's where you get your limit of a bit less than 8KB per row.

2. Fixed-size data types like INTEGER, DATE, FLOAT, CHAR are stored on this primary data page and count toward the row size limit.

3. Variable-sized data types like VARCHAR, TEXT, BLOB are stored on overflow pages, so they don't count fully toward the row size limit. In Antelope, up to 768 bytes of such columns are stored on the primary data page in addition to being stored on the overflow page. Barracuda supports a dynamic row format, so it may store only a 20-byte pointer on the primary data page.

4. Variable-size data types are also prefixed with 1 or more bytes to encode the length. And InnoDB row format also has an array of field offsets. So there's an internal structure more or less documented in their wiki.

Barracuda also supports a ROW_FORMAT=COMPRESSED to gain further storage efficiency for overflow data.

I also have to comment that I've never seen a well-designed table exceed the row size limit. It's a strong "code smell" that you're violating the repeating groups condition of First Normal Form.

---

After spending hours I have found the solution: just run the following SQL in your MySQL admin to convert the table to MyISAM:

USE db_name;
ALTER TABLE table_name ENGINE=MYISAM;

---

I ran into this issue when I was trying to restore a backed up mysql database from a different server. What solved this issue for me was adding certain settings to my.conf (like in the questions above) and additionally changing the sql backup file:

Step 1: add or edit the following lines in my.conf:

innodb_page_size=32K
innodb_file_format=Barracuda
innodb_file_per_table=1

Step 2 add ROW_FORMAT=DYNAMIC to the table create statement in the sql backup file for the table that is causing this error:

DROP TABLE IF EXISTS `problematic_table`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `problematic_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
  ...
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 ROW_FORMAT=DYNAMIC;

the important change above is ROW_FORMAT=DYNAMIC; (that was not included in the orignal sql backup file)

source that helped me to resolve this issue: MariaDB and InnoDB MySQL Row size too large

---

I had the same issue, this solved it for me:

ALTER TABLE `my_table` ROW_FORMAT=DYNAMIC;

From MYSQL Documentation:

The DYNAMIC row format maintains the efficiency of storing the entire row in the index node if it fits (as do the COMPACT and REDUNDANT formats), but this new format avoids the problem of filling B-tree nodes with a large number of data bytes of long columns. The DYNAMIC format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page. With DYNAMIC format, shorter columns are likely to remain in the B-tree node, minimizing the number of overflow pages needed for any given row.

---

The other answers address the question asked. I will address the underlying cause: poor schema design.

Do not splay an array across columns. Here you have 3*10 columns that should be turned into 10 rows of 3 columns in a new table (plus id, etc)

Your Main table would have only

id  int(11) No       
name    text    No       
date    date    No       
time    time    No       
schedule    int(11) No       
category    int(11) No       
status  int(11) No       
admin_id    int(11) No 

Your extra table (Top) would have

id  int(11) No          -- for joining to Main
seq TINYINT UNSIGNED    -- containing 1..10
img   varchar(255)    No       
title varchar(255)    No       
desc  text    No    
PRIMARY KEY(id, seq)    -- so you can easily find the 10 top_titles

There would be 10 (or fewer? or more?) rows in Top for each id.

This eliminates your original problem, and cleans up the schema. (This is not "normalization", as debated in some of the Comments.)

Do not switch to MyISAM; it is going away.
Don't worry about ROW_FORMAT.

You will need to change your code to do the JOIN and to handle multiple rows instead of multiple columns.

---

I also encountered the same problem. I solve the problem by executing the following sql:

ALTER ${table} ROW_FORMAT=COMPRESSED;

But, I think u should know about the Row Storage.
There are two kinds of columns: variable-length column(such as VARCHAR, VARBINARY, and BLOB and TEXT types) and fixed-length column. They are stored in different types of pages.

Variable-length columns are an exception to this rule. Columns such as BLOB and VARCHAR that are too long to fit on a B-tree page are stored on separately allocated disk pages called overflow pages. We call such columns off-page columns. The values of these columns are stored in singly-linked lists of overflow pages, and each such column has its own list of one or more overflow pages. In some cases, all or a prefix of the long column value is stored in the B-tree, to avoid wasting storage and eliminating the need to read a separate page.

and when purpose of setting ROW_FORMAT is

When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, InnoDB can store long variable-length column values (for VARCHAR, VARBINARY, and BLOB and TEXT types) fully off-page, with the clustered index record containing only a 20-byte pointer to the overflow page.

Wanna know more about DYNAMIC and COMPRESSED Row Formats

---

If this occures on a SELECT with many columns, the cause can be that mysql is creating a temporary table. If this table is too large to fit in memory, it will use its default temp table format, which is InnoDB, to store it on Disk. In this case the InnoDB size limits apply.

You then have 4 options:

1. change the innodb row size limit like stated in another post, which requires reinitialization of the server.
2. change your query to include less columns or avoid causing it to create a temporary table (by i.e. removing order by and limit clauses).
3. changing max_heap_table_size to be large so the result fits in memory and does not need to get written to disk.

4. change the default temp table format to MYISAM, this is what i did. Change in my.cnf:

   internal_tmp_disk_storage_engine=MYISAM
   

Restart mysql, query works.

---

Here is simple tip for anyone interested:

After upgrade from Debian 9 to Debian 10 with 10.3.17-MariaDB, I have some errors from Joomla databases:

[Warning] InnoDB: Cannot add field field in table database.table because after adding it, the row size is 8742 which is greater than maximum allowed size (8126) for a record on index leaf page.

Just in case, I set innodb_default_row_format = DYNAMIC in /etc/mysql/mariadb.conf.d/50-server.cnf (it was default anyway)

Than, I have used phpmyadmin to run "Optimize table" for all the tables in Joomla database. I think table recreation done by phpmyadmin in the process helped. If you happen to have phpmyadmin installed it is just few clicks to do.

---

I am using MySQL 5.6 on AWS RDS. I updated following in parameter group.

innodb_file_per_table=1
innodb_file_format = Barracuda

I had to reboot DB instance for parameter group changes to be in effect.

Also, ROW_FORMAT=COMPRESSED was not supported. I used DYNAMIC as below and it worked fine.

ALTER TABLE nombre_tabla ENGINE=InnoDB ROW_FORMAT=DYNAMIC KEY_BLOCK_SIZE=8

참고URL : https://stackoverflow.com/questions/15585602/change-limit-for-mysql-row-size-too-large