0
Follow
0
View

This error occurs when the sql is executed in mysql8.0.29

mxliuyc 注册会员
2023-02-26 16:17

[cause of the problem]

summarizes the following reason: mysql-innodb stores data according to page, max size of each page is 16k, and then each page has two lines of data, so each line has a maximum of 8k data. If your field is a blob or something, it will be stored in an overflow area outside the page.

But innodb's default approach store format stores the first 864 bytes of each blob field in the page, so if you exceed a certain number of blobs, the single line size will exceed 8k, so an error

is reported.

solution is to use innodb's Barracuda storage format

This format handles blob fields by storing only a 20byte pointer in the page, and everything else has an overflow area, so you can't easily go beyond 8k

.
dahai32623313 注册会员
2023-02-26 16:17

This error is caused by the row size exceeding the MySQL default limit(8126 bytes).

Change data type: Change the data type from VARCHAR or CHAR to TEXT or BLOB because they can store more data while taking up more space. Note, however, that this may affect query performance.

Changed row format: Changed the row format to ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED to allow for a larger row size. This allows large columns, such as blobs, to be stored in separate pages.

Reduce row size: If you are unable to change the data type or row format, you can try reducing the row size. This can be done by splitting a large column into multiple smaller columns, or by splitting a table into multiple tables.

Increase page size: Larger row sizes can also be allowed by increasing the page size from the default 16KB to 32KB or 64KB. Note, however, that this may affect memory usage and disk space.

Note that whatever solution you take, you should test it in a test environment to ensure that data integrity and query performance are not compromised.

yaogunlong 注册会员
2023-02-26 16:17

: < br / > 1. The default log file size is too small because there is too much data in the table.
2. Different engines use different storage formats. MyISAM has three row storage formats: fixed/dynamic/compressed. InnoDb has introduced Barracuda on top of MyIASM. As for the default Settings for different engines and the differences between various storage formats,

Solution
Method 1: Modify the Mysql database configuration file
Change the my.ini configuration item innodb_log_file_size=512M to 1024M and restart the database service.

Method two: Modify other engines
Method three:
Try adding the following options to your configuration file:
To activate a new file format: innodb_file_format = Barracuda
Store each table in its own file: innodb_file_per_table = 1
To prevent surprises: innodb_strict_mode = ON
To store long variable length column values, use dynamic or compressed row format for large tables.
For example, ALTER TABLE LargeTable ENGINE = InnoDB ROW_FORMAT = DYNAMIC;

laojiawei69 注册会员
2023-02-26 16:17


< span > https://blog.csdn.net/weixin_42272869/article/details/114304403 < / span > < / a > < br / > error because in MySQL table definition too many columns Or the size of some columns exceeds the maximum row size limit allowed by MySQL(8126 bytes by default). The
step solves this problem:

  • 1. With TEXT or BLOB data types: Change some columns to TEXT or BLOB data types that can be stored outside the row, thereby reducing the row size. However, doing so may affect query performance because they require more disk I/O to retrieve the data.
  • 2. ROW_FORMAT=DYNAMIC or COMPRESSED: Changes the table row format to DYNAMIC or COMPRESSED. This will allow the maximum row size to exceed the 8126 byte limit and store large data types outside the row. However, this can also affect query performance because they require more disk I/O to retrieve data.
  • 3. Reduce the number and size of columns: Reduce the number and size of columns in the table as much as possible so that the total size of each row is less than 8126 bytes. Consider splitting some columns into multiple tables, or storing some information in a file with the file path stored in the table.
  • 4. Use InnoDB storage engine: If you are using MyISAM storage engine, consider converting your tables to InnoDB storage engine. The InnoDB storage engine supports larger row sizes and better performance.