📋

Description

MySQL Error 1118 indicates that the combined size of the data in a table row exceeds the maximum limit for the storage engine being used. This typically happens when a table is designed with too many large columns, particularly fixed-length or variable-length columns defined with substantial maximum lengths, leading to an overflow of the allocated row storage.

💬

Error Message

Row size too large. The maximum row size for the used table type, not counting BLOBs, is %ld. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

🔍

Known Causes

3 known causes

⚠️

Overly Wide Table Design

Creating tables with an excessive number of columns, or many columns defined with large fixed-length data types (e.g., CHAR(255)) or large maximum lengths for variable-length types (VARCHAR(255)).

⚠️

Multi-Byte Character Sets

Using character sets like UTF8MB4, which require more bytes per character, can significantly increase the storage size of string columns, pushing rows over the limit.

⚠️

Excessive Storage Overhead

MySQL adds internal overhead for variable-length columns, NULL values, and other metadata within each row, which contributes to the total row size calculation.

🛠️

Solutions

3 solutions available

1. Convert Large VARCHAR Columns to TEXT Types medium

Reduces row size by moving large string data to separate storage.

1

Identify columns that are frequently storing large amounts of text and are likely contributing to the large row size. Look for `VARCHAR` columns with a high maximum length that are being used extensively.

2

Alter the table to change these `VARCHAR` columns to a `TEXT` data type (e.g., `TEXT`, `MEDIUMTEXT`, `LONGTEXT`). The `TEXT` types store data separately from the main row, effectively reducing the row's immediate size.

ALTER TABLE your_table_name MODIFY COLUMN large_varchar_column TEXT;

3

If the data is consistently very large, consider `MEDIUMTEXT` or `LONGTEXT` for even larger storage capacity.

ALTER TABLE your_table_name MODIFY COLUMN very_large_varchar_column MEDIUMTEXT;

4

Rebuild or optimize your table after the alteration to ensure the changes are fully applied and to reclaim any freed space.

OPTIMIZE TABLE your_table_name;

2. Normalize Your Database Schema advanced

Separates large or frequently changing data into related tables.

1

Analyze your table for columns that contain data that could logically belong in a separate table. This often includes repeating groups of data or large text/binary fields.

2

Create a new table to store this related data. This new table will have a foreign key referencing the original table.

CREATE TABLE new_related_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    your_table_id INT,
    related_data VARCHAR(255),
    -- other columns for related data
    FOREIGN KEY (your_table_id) REFERENCES your_table_name(id)
);

3

Move the identified data from the original table to the new related table, ensuring the foreign key relationship is maintained.

INSERT INTO new_related_table (your_table_id, related_data)
SELECT id, large_data_column FROM your_table_name WHERE large_data_column IS NOT NULL;

ALTER TABLE your_table_name DROP COLUMN large_data_column;

4

Update your application queries to join the original table with the new related table when the related data is needed.

3. Review and Reduce Column Lengths easy

Shortens `VARCHAR` or `CHAR` column lengths if they are unnecessarily large.

1

Examine your table schema and identify `VARCHAR` or `CHAR` columns that have very large defined lengths (e.g., `VARCHAR(255)` or more) but are only ever storing much shorter strings.

2

Determine the actual maximum length of data being stored in these columns by analyzing your existing data.

SELECT MAX(LENGTH(column_name)) FROM your_table_name;

3

Alter the table to reduce the length of these columns to a more appropriate size. This directly reduces the storage allocated per row.

ALTER TABLE your_table_name MODIFY COLUMN column_name VARCHAR(100); -- Adjust length as needed

4

Be cautious with this approach. If you reduce the length too much, you might truncate existing data.

🔗

Related Errors

5 related errors

MySQL Error 1118: Row Size Too Large