How to Fix “Row size too large” Error in MySQL When Storing Multiple Text Fields


4 views

When working with MySQL tables containing multiple TEXT or VARCHAR fields, you might encounter the frustrating "Row size too large" error. This occurs because MySQL has strict limits on row size (excluding BLOBs), which is 8126 bytes for most storage engines.

Interestingly, you might see this error even when individual field contents aren't very long. This is because:

  • MySQL counts the maximum potential size of VARCHAR fields, not their actual content length
  • TEXT fields have a 2-byte overhead for length storage
  • Other columns in your table also contribute to the total row size

Here are several ways to resolve this issue:

1. Convert Some Columns to TEXT/BLOB

The error message itself suggests this solution. TEXT/BLOB fields are stored separately from the main row structure:

ALTER TABLE your_table 
MODIFY COLUMN large_text_field1 TEXT,
MODIFY COLUMN large_text_field2 TEXT;

2. Adjust VARCHAR Lengths

If you're using VARCHAR, consider reducing their maximum lengths:

ALTER TABLE your_table 
MODIFY COLUMN description1 VARCHAR(255),
MODIFY COLUMN description2 VARCHAR(255);

3. Consider Table Optimization

For complex tables with many text fields, consider these structural changes:

-- Option 1: Split into multiple tables
CREATE TABLE main_data (
    id INT PRIMARY KEY,
    non_text_fields...
);

CREATE TABLE text_data (
    id INT PRIMARY KEY,
    main_id INT,
    text_field1 TEXT,
    text_field2 TEXT,
    FOREIGN KEY (main_id) REFERENCES main_data(id)
);

-- Option 2: Use JSON for multiple text fields
ALTER TABLE your_table
ADD COLUMN text_fields JSON;

4. Change Storage Engine

Different storage engines have different limits:

ALTER TABLE your_table ENGINE=InnoDB;
-- Or for larger rows:
ALTER TABLE your_table ENGINE=MyISAM;

To avoid this issue in future designs:

  • Plan your VARCHAR lengths carefully
  • Use TEXT for fields that might exceed a few hundred characters
  • Consider normalizing your data structure
  • Test with realistic data volumes during development

Here's how we fixed this issue in a content management system:

-- Original problematic table
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    summary VARCHAR(1000),
    content1 VARCHAR(2000),
    content2 VARCHAR(2000),
    notes VARCHAR(2000),
    author_bio VARCHAR(1000)
);

-- Fixed version
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    summary VARCHAR(500),
    content1 TEXT,
    content2 TEXT,
    notes TEXT,
    author_bio VARCHAR(500)
);

MySQL has a hard limitation on row size (excluding BLOB/TEXT columns) which varies depending on storage engine and page size. The error you're seeing:

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126.

typically occurs with InnoDB tables when the combined size of all non-BLOB/TEXT columns exceeds 8126 bytes (for default 16KB page size). This includes:

  • All VARCHAR, CHAR, and other string-type columns
  • All numeric columns
  • Any other fixed-length data types

Even if each text field contains just a few paragraphs, the issue arises from how MySQL counts storage:

CREATE TABLE problematic_table (
  id INT PRIMARY KEY,
  field1 VARCHAR(4000),
  field2 VARCHAR(4000),
  field3 VARCHAR(4000),
  field4 VARCHAR(4000)
);

In this case, the potential maximum row size would be 4*4000 = 16000 bytes, far exceeding the limit.

Option 1: Convert to TEXT/BLOB

The most straightforward solution is converting some VARCHAR columns to TEXT:

ALTER TABLE problematic_table 
MODIFY field1 TEXT,
MODIFY field2 TEXT;

Option 2: Optimize Column Types

Review your schema for optimization opportunities:

-- Before
CREATE TABLE example (
  description VARCHAR(5000),
  notes VARCHAR(5000)
);

-- After optimization
CREATE TABLE example (
  description TEXT,
  notes TEXT,
  status ENUM('active','inactive') -- Changed from VARCHAR(10)
);

Option 3: Vertical Partitioning

Split the table into multiple tables with 1:1 relationships:

-- Main table
CREATE TABLE main_data (
  id INT PRIMARY KEY,
  important_columns...
);

-- Text-heavy table
CREATE TABLE text_data (
  id INT PRIMARY KEY,
  main_id INT,
  field1 TEXT,
  field2 TEXT,
  FOREIGN KEY (main_id) REFERENCES main_data(id)
);

For extreme cases, you can modify InnoDB page size (requires server restart):

# In my.cnf
innodb_page_size=32k

This increases the row size limit to 16382 bytes, but affects all tables and requires careful consideration.

Here's how I recently fixed this issue in a CMS application:

-- Original schema causing errors
CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  meta_description VARCHAR(2000),
  content VARCHAR(15000),
  excerpt VARCHAR(2000),
  seo_keywords VARCHAR(2000)
);

-- Fixed schema
CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255),
  meta_description VARCHAR(1000),
  content TEXT,  -- Changed to TEXT
  excerpt TEXT,  -- Changed to TEXT
  seo_keywords VARCHAR(1000) -- Reduced size
);

Remember that with TEXT columns, you'll need to consider:

  • Full-text indexing requirements
  • Potential changes to your application's query patterns
  • Storage implications (TEXT columns are stored off-page)

For most applications, converting the largest text fields to TEXT while keeping smaller, frequently accessed fields as VARCHAR provides the best balance.