Victor's room

Overview & Root Cause Summary: The error ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 768 bytes is stored inline occurs in MySQL and MariaDB when adding columns, running database migrations, or executing wide schema definitions. It stems from InnoDB’s internal B-tree architecture constraint requiring every table row to fit within half of an InnoDB page (default 16KB / 2 = 8,126 bytes), compounded by legacy row formats storing large column prefixes inline on the page.

Understanding the Root Causes

• InnoDB Half-Page Constraint (8,126 Bytes): To ensure B-tree index efficiency, InnoDB requires at least two rows to fit on every 16KB index page, enforcing a strict maximum record size limit of 8,126 bytes.
• Legacy ROW_FORMAT=COMPACT Prefix Storage: Older tables or default configurations created under COMPACT or REDUNDANT row formats store the first 768 bytes of each variable-length column (VARCHAR, VARBINARY, BLOB, TEXT) inline on the primary page. Having 10+ wide columns rapidly breaches the 8,126-byte boundary.
• Multi-Byte Character Sets (utf8mb4): Modern character encodings like utf8mb4 reserve up to 4 bytes per character. A VARCHAR(255) column calculates internally as up to 1,020 bytes of potential storage.
• Excessive Wide Columns in CMS / ORM Models: Frameworks (such as WordPress custom postmeta, Django models, or Drupal) generating wide tables with dozens of VARCHAR columns.

Step 1: Quick Fix (Convert Table Row Format to DYNAMIC)

Convert the affected table’s storage format to DYNAMIC, which stores only a 20-byte pointer inline and pushes the remaining data off-page into overflow pages.

# 1. Connect to MySQL as an administrator:
mysql -u root -p

# 2. Inspect the current row format of the problematic table:
SHOW TABLE STATUS FROM mydb WHERE Name = 'my_table'\G
# Look for: Row_format: Compact (or Redundant)

# 3. Alter the table to use DYNAMIC row format:
ALTER TABLE mydb.my_table ROW_FORMAT=DYNAMIC;

# 4. Re-run your previously failing ALTER TABLE or migration query:
ALTER TABLE mydb.my_table ADD COLUMN additional_field VARCHAR(255);
# Expected output: Query OK, 0 rows affected

Step 2: Configure Global DYNAMIC Row Format in my.cnf

Configure MySQL server defaults so all future tables and migrations automatically use the modern Barracuda DYNAMIC format.

# 1. Open your MySQL configuration file:
# Ubuntu/Debian: /etc/mysql/mysql.conf.d/mysqld.cnf
# CentOS/RHEL:   /etc/my.cnf
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# 2. Add the following directives under the [mysqld] section:
[mysqld]
# Enforce DYNAMIC format for all new tables:
innodb_default_row_format = DYNAMIC

# Ensure each table uses its own .ibd tablespace file:
innodb_file_per_table = 1

# 3. Apply settings dynamically without downtime:
mysql -u root -p -e "SET GLOBAL innodb_default_row_format = DYNAMIC;"

# 4. Restart MySQL to persist across reboots:
sudo systemctl restart mysql

Step 3: Column Optimization & Off-Page Storage Refactoring

If a table still exceeds row limits under DYNAMIC format (which occurs with > 80+ wide columns), refactor column data types.

-- 1. Convert non-indexed wide VARCHAR columns to TEXT or BLOB:
-- (TEXT/BLOB data types store only a 20-byte overflow pointer inline under DYNAMIC):
ALTER TABLE my_table 
    MODIFY COLUMN user_biography TEXT,
    MODIFY COLUMN metadata_json JSON;

-- 2. Reduce inflated VARCHAR lengths to realistic maximums:
-- Instead of: VARCHAR(255) for two-letter country codes
ALTER TABLE my_table MODIFY COLUMN country_code VARCHAR(2);

-- 3. In extreme cases (tables with hundreds of columns), normalize into separate vertical tables:
-- Split auxiliary metadata into a 1-to-1 extension table:
-- CREATE TABLE my_table_metadata (
--     id INT PRIMARY KEY,
--     FOREIGN KEY (id) REFERENCES my_table(id) ON DELETE CASCADE,
--     extra_field_1 VARCHAR(255), ...
-- );

Verification & Testing Steps

Confirm that the row format is active and verify that row modifications execute without 1118 errors.

# 1. Verify table row format in MySQL catalog:
SELECT table_name, row_format, table_rows, data_length 
FROM information_schema.tables 
WHERE table_schema = 'mydb' AND table_name = 'my_table';
# Expected output: row_format: Dynamic

# 2. Test inserting a full record with populated columns:
mysql -u root -p mydb -e "INSERT INTO my_table VALUES (...);"

# 3. Check global configuration variables:
SHOW VARIABLES LIKE 'innodb_default_row_format';

Summary Comparison Table

Row Format	Inline Prefix Storage	Off-Page Handling	Max Practical Columns
COMPACT (Legacy)	Stores 768 bytes inline per column	Overflows after 768 bytes	~15 – 20 wide columns
DYNAMIC (Modern Standard)	Stores 20-byte pointer inline	Pushes entire payload off-page	~150+ columns
REDUNDANT	Stores header + prefix inline	Inefficient overflow	Avoid in modern databases
COMPRESSED	Stores 20-byte pointer inline	Compressed zlib pages	High CPU overhead; use DYNAMIC