(opens in a new tab)

MySQL Error 1074: Column Length Too Big (Fix)

October 25, 2026 by Chat2DB Team

You declare a long VARCHAR and MySQL rejects the table:

ERROR 1074 (42000): Column length too big for column 'body' (max = 16383); use BLOB or TEXT instead

The number in max = ... changes from one server to the next: 16383, 21845, 65535 or 255. That is the key to understanding the error. MySQL limits columns in bytes, and the maximum length in characters depends on the column's character set and type.

This guide explains every variant, the related "row size too large" error that often comes right after it, and how to choose between VARCHAR and the TEXT types. All output is from MySQL 8.4 with the default utf8mb4 character set and strict SQL mode.

Why the Maximum Is 16383 and Not 65535

A VARCHAR value can be at most 65,535 bytes. MySQL has to reserve room for the longest possible value, so it multiplies the declared length in characters by the maximum number of bytes one character can take in the column's character set:

So with utf8mb4, VARCHAR(20000) would need up to 80,000 bytes and is rejected:

CREATE TABLE posts (id INT PRIMARY KEY, body VARCHAR(20000));
-- ERROR 1074 (42000): Column length too big for column 'body' (max = 16383); use BLOB or TEXT instead

The same column in latin1 is accepted, because each character takes one byte:

CREATE TABLE posts (id INT PRIMARY KEY, body VARCHAR(20000)) CHARSET latin1;
-- Query OK, 0 rows affected

This is why the error often appears after a database is converted from latin1 or utf8mb3 to utf8mb4, or when a schema written for an older server is loaded into MySQL 8.

The Other Variants

CHAR and BINARY: max = 255. Fixed-length types are limited to 255 characters (or bytes for BINARY), whatever the character set:

CREATE TABLE t_char (code CHAR(300));
-- ERROR 1074 (42000): Column length too big for column 'code' (max = 255); use BLOB or TEXT instead
 
CREATE TABLE t_bin2 (h BINARY(256));
-- ERROR 1074 (42000): Column length too big for column 'h' (max = 255); use BLOB or TEXT instead

VARBINARY: max = 65535. Binary strings count bytes directly:

CREATE TABLE t_bin (h VARBINARY(70000));
-- ERROR 1074 (42000): Column length too big for column 'h' (max = 65535); use BLOB or TEXT instead

ALTER TABLE. Widening an existing column gives the same error:

ALTER TABLE notes MODIFY title VARCHAR(30000);
-- ERROR 1074 (42000): Column length too big for column 'title' (max = 16383); use BLOB or TEXT instead

Passing 1074 and Hitting 1118

Even at the maximum length, the column may still not fit, because the whole row has the same 65,535-byte limit. Every other column counts too, plus 1 or 2 bytes for each VARCHAR's length and the NULL bitmap. VARCHAR(16383) passes the column check but fails as soon as you add an INT:

CREATE TABLE posts (id INT PRIMARY KEY, body VARCHAR(16383));
-- ERROR 1118 (42000): Row size too large. The maximum row size for the used table type,
-- not counting BLOBs, is 65535. This includes storage overhead, check the manual.
-- You have to change some columns to TEXT or BLOBs

The arithmetic: 16383 × 4 = 65,532 bytes, plus 2 length bytes and 4 bytes for the INT, is 65,538, over the limit. VARCHAR(16382) fits exactly:

CREATE TABLE p1 (id INT PRIMARY KEY, body VARCHAR(16382));   -- OK: 65528 + 2 + 4 + 1 null byte = 65535
CREATE TABLE p3 (body VARCHAR(16383));                       -- OK: 65532 + 2 + 1 null byte = 65535

Two moderately long columns can fail where one long column passes:

CREATE TABLE big (a VARCHAR(10000), b VARCHAR(10000));
-- ERROR 1118 (42000): Row size too large ...

To check a whole table before running it, paste the CREATE TABLE into the MySQL Row Size Calculator (opens in a new tab). It applies both the per-column 1074 check and the 65,535-byte row check, and shows each column's share.

Non-Strict Mode Silently Changes the Type

Without strict SQL mode, MySQL does not reject the column. It converts it to a TEXT type and only leaves a note:

SET SESSION sql_mode = '';
CREATE TABLE posts2 (id INT PRIMARY KEY, body VARCHAR(20000));
-- Query OK, 0 rows affected, 1 warning
 
SHOW WARNINGS;
-- Note | 1246 | Converting column 'body' from VARCHAR to TEXT
 
SHOW CREATE TABLE posts2\G
--   `body` mediumtext,

The note says TEXT, but the column actually became MEDIUMTEXT, because 20,000 × 4 bytes does not fit in TEXT's 65,535 bytes. This is how old schemas end up with unexpected MEDIUMTEXT columns. They were created on a server without strict mode and nobody read the note. Strict mode (STRICT_TRANS_TABLES) is the default since MySQL 5.7, which is why the same script fails on a newer server.

Do not turn off strict mode to get past the error. Change the column definition.

How to Fix It

Option 1: Use TEXT or MEDIUMTEXT

If the column holds long free-form content (article bodies, comments, JSON documents, descriptions), use a TEXT type. TEXT columns are stored mostly off-page and count only 9 to 12 bytes toward the row limit:

ALTER TABLE notes MODIFY title TEXT;
-- or, if values can exceed 64 KB (16383 utf8mb4 characters):
ALTER TABLE notes MODIFY title MEDIUMTEXT;

Remember that TEXT limits are in bytes too. A TEXT column holds at most 16,383 four-byte characters, so pick MEDIUMTEXT for content that may be longer.

Things that change with TEXT:

• Indexes need a prefix length. KEY (body) fails with ERROR 1170 (42000): BLOB/TEXT column 'body' used in key specification without a key length. Use KEY (body(255)), or a generated hash column for uniqueness.
• Defaults. Since MySQL 8.0.13 a TEXT column can have a default only as an expression, such as DEFAULT ('').
• Temporary tables. Queries that sort or group by TEXT columns may need on-disk temporary tables, which is slower. Select long columns only when you need them.

Option 2: Reduce the Length to What You Really Need

Many 1074 errors come from guessing a large length. A URL rarely needs more than 2,048 characters, an email address 320, and a title a few hundred. A realistic VARCHAR keeps validation in the database and keeps the column indexable:

CREATE TABLE links (id INT PRIMARY KEY, url VARCHAR(2048) NOT NULL);

Option 3: Use a Smaller Character Set for ASCII-Only Data

Hashes, codes, base64 tokens and similar values never contain multi-byte characters. Declaring them ascii (or using VARBINARY) cuts their byte size by four:

CREATE TABLE tokens (
  id    BIGINT PRIMARY KEY,
  token VARCHAR(20000) CHARACTER SET ascii NOT NULL
);

Do not do this for user-facing text. Anything that may contain accents, CJK characters or emoji needs utf8mb4.

ORMs and Migration Tools

Frameworks map string lengths directly to VARCHAR(n), so the error usually comes from model code:

• Laravel: $table->string('body', 20000) creates VARCHAR(20000). Use $table->text('body') or mediumText.
• Django: models.CharField(max_length=20000) creates varchar(20000). Use models.TextField().
• JPA/Hibernate: @Column(length = 20000) on a String may be emitted as varchar(20000) depending on the dialect and version. Use @Lob or columnDefinition = "MEDIUMTEXT".
• Rails: t.string :body, limit: 20000. Use t.text :body (and size: :medium for MEDIUMTEXT).

Quick Reference

When you inherit a schema with many oversized VARCHAR columns, a visual table editor speeds up the cleanup. Chat2DB (opens in a new tab) lets you change column types in a grid and preview the generated ALTER TABLE, and its AI assistant can suggest realistic lengths based on the data that is actually stored.

FAQ

Is VARCHAR(65535) possible in MySQL?

Only for a single-byte character set such as latin1 or ascii, and in practice not even then, because the 2 length bytes push the row over 65,535 bytes. The longest single latin1 column is about VARCHAR(65532), and for utf8mb4 it is about VARCHAR(16383) on a table with no other columns.

Is TEXT slower than VARCHAR?

For reading and writing short values the difference is small. With the DYNAMIC row format, short TEXT values are stored in the row like VARCHAR. The real costs of TEXT are the need for prefix indexes and possible on-disk temporary tables for sorts and GROUP BY. For long content, TEXT is the right type.

Why does the same CREATE TABLE work on another server?

That server probably uses a different default character set (for example latin1 or utf8mb3 instead of utf8mb4), or runs without strict SQL mode, so it silently converted the column to TEXT. Compare SELECT @@character_set_server, @@sql_mode; on both servers.