When working with MySQL, database administrators and developers frequently encounter various limitations related to data storage. One such limitation arises from the row size, leading to the error: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB. This error effectively prevents further data manipulation until the underlying issue is rectified.

Understanding the Error Code: 1118

Background

MySQL imposes limits on the storage size of rows in order to optimize database performance and ensure effective memory utilization. Typically, the maximum size for a row is constrained by the InnoDB storage engine, which is the default storage engine in MySQL. Although the specific size can vary, it generally allows for row data not exceeding 8126 bytes for certain configurations.

Cause of the Error

The reason for the "Row size too large (> 8126)" error lies in the exceeding of the maximum permissible row size by the column definitions of a table. This often happens when:

• The table has numerous `VARCHAR` or `CHAR` fields, each with large defined maximum lengths.
• There are too many columns in the table leading to an aggregated size larger than the allowed limit.
• Use of `ROW_FORMAT=COMPACT` or `ROW_FORMAT=REDUNDANT` that maintains a tight limit on the row sizes due to storage overheads.

Example Scenario

Consider a table `users` defined as follows:

• Switch `VARCHAR` to `TEXT` or `BLOB`: Convert columns with potentially large text data to `TEXT` or `BLOB`. This helps because `TEXT` and `BLOB` are stored separately from the row data, only a pointer to the actual data consumes row size.
• Reassess Column Widths: Reduce the size of `VARCHAR` and `CHAR` columns by re-evaluating realistic data needs. For example, if a `VARCHAR(255)` is only ever storing strings of length 50, reduce it to `VARCHAR(50)`.
• Use BINARY and VARBINARY: For storing binary data, leverage binary string column types (`BINARY`, `VARBINARY`) instead of large `CHAR` or `VARCHAR`.
• Eliminate Redundancy: Merge or eliminate similar columns whenever possible. Consolidating data can reduce the row size.
• Recreate the Table with a Different Row Format: