How to fix ERROR 1101: BLOB/TEXT/JSON can't have default value in MySQL

MySQLINTERMEDIATEMEDIUMUpdated Dec 22, 2025· 283 views

MySQL does not allow default values on BLOB, TEXT, GEOMETRY, or JSON columns due to how these types allocate memory. Remove the DEFAULT clause or use expression syntax (MySQL 8.0.13+) to fix this error.

Quick fix

Fixes ERROR 1101: BLOB/TEXT/JSON can't have default value

CREATE TABLE users (
  id INT PRIMARY KEY,
  profile TEXT NOT NULL DEFAULT 'N/A'
);
  1. Remove the DEFAULT clause from your column definition
  2. For MySQL 8.0.13+, use expression syntax with parentheses
  3. Use a BEFORE INSERT trigger to populate default values
  4. Switch to VARCHAR/VARBINARY if the data is small enough
mysql — error
CREATE TABLE users (
  id INT PRIMARY KEY,
  profile TEXT NOT NULL DEFAULT 'N/A'
);
ERROR 1101: BLOB/TEXT/JSON can't have default value

What this error means

This error occurs because MySQL has a fundamental limitation on BLOB, TEXT, GEOMETRY, and JSON data types—they cannot have default values assigned to them directly. This restriction exists because each BLOB, TEXT, GEOMETRY, or JSON value is represented internally by a separately allocated object, unlike other data types that have storage allocated once per column when the table is opened. When you attempt to create or alter a table with a DEFAULT clause on these column types, MySQL raises ERROR 1101 to prevent an invalid schema definition. The error is intentional and by design to maintain database integrity.

Typical symptoms

  • CREATE TABLE or ALTER TABLE statements fail with ERROR 1101
  • Migrations from ORMs (like Doctrine, Entity Framework) fail when auto-generating schema
  • Tables with BLOB, TEXT, JSON columns cannot be created if you specified a DEFAULT value
  • The error occurs even if you try to set an empty string or NULL as the default

Common causes

  1. Attempting to add a DEFAULT value to a BLOB, TEXT, GEOMETRY, or JSON column in CREATE TABLE
  2. Altering a table to add DEFAULT to an existing BLOB/TEXT/JSON column
  3. Using ORM-generated migrations that assume all NOT NULL columns need DEFAULT values
  4. Trying to set a literal default value (like DEFAULT "") on these column types before MySQL 8.0.13
  5. Misunderstanding the architectural differences between LOB (Large Object) types and standard data types

How to fix "ERROR 1101: BLOB/TEXT/JSON can't have default value"

1Remove the DEFAULT clause from your column definition

The simplest solution is to remove the DEFAULT clause entirely. BLOB, TEXT, JSON, and GEOMETRY columns will automatically accept NULL unless you explicitly mark them as NOT NULL.

Replace:

sql
CREATE TABLE users (
  id INT PRIMARY KEY,
  profile TEXT NOT NULL DEFAULT 'N/A'
);

With:

sql
CREATE TABLE users (
  id INT PRIMARY KEY,
  profile TEXT NOT NULL
);

If your application requires a value, handle the default in your application code or use a trigger instead.

2For MySQL 8.0.13+, use expression syntax with parentheses

If you are using MySQL 8.0.13 or later, you can assign default values to BLOB, TEXT, GEOMETRY, and JSON columns, but only if the default is written as an expression (even if it's a literal value).

For JSON columns, use expression syntax:

sql
CREATE TABLE settings (
  id INT PRIMARY KEY,
  config JSON DEFAULT (JSON_OBJECT())
);

For TEXT columns with literal defaults, wrap in parentheses:

sql
CREATE TABLE posts (
  id INT PRIMARY KEY,
  content TEXT DEFAULT ('')
);

For empty arrays in JSON:

sql
CREATE TABLE items (
  id INT PRIMARY KEY,
  tags JSON DEFAULT (JSON_ARRAY())
);

The key difference is the parentheses around the expression.

3Use a BEFORE INSERT trigger to populate default values

If you need automatic value initialization for LOB columns and you're on MySQL < 8.0.13 or need more control, create a BEFORE INSERT trigger:

sql
CREATE TABLE documents (
  id INT PRIMARY KEY AUTO_INCREMENT,
  content LONGTEXT,
  metadata JSON
);

DELIMITER //
CREATE TRIGGER set_defaults_before_insert
BEFORE INSERT ON documents
FOR EACH ROW
BEGIN
  IF NEW.content IS NULL THEN
    SET NEW.content = 'Default document content';
  END IF;
  IF NEW.metadata IS NULL THEN
    SET NEW.metadata = JSON_OBJECT('created', NOW());
  END IF;
END //
DELIMITER ;

This approach gives you more flexibility and works across all MySQL versions.

4Switch to VARCHAR/VARBINARY if the data is small enough

If your data is relatively small (under 65,535 bytes), consider using VARCHAR instead of TEXT or VARBINARY instead of BLOB. These types support DEFAULT values:

sql
-- Instead of TEXT with DEFAULT
CREATE TABLE posts (
  id INT PRIMARY KEY,
  excerpt VARCHAR(500) DEFAULT 'No excerpt provided'
);

-- Instead of BLOB with DEFAULT
CREATE TABLE files (
  id INT PRIMARY KEY,
  thumbnail VARBINARY(1000) DEFAULT 0x0000
);

VARCHAR(n) allows up to n characters, and VARBINARY(n) allows up to n bytes. This is a viable solution if your content fits within these limits.

5Check and adjust your SQL_MODE if necessary

In STRICT modes (like STRICT_TRANS_TABLES or STRICT_ALL_TABLES), this error is raised immediately. While not recommended, you can temporarily check your SQL mode:

sql
SELECT @@sql_mode;

Do NOT disable STRICT_TRANS_TABLES permanently—it protects data integrity. Instead, fix your schema to comply with MySQL requirements.

Advanced notes

Why does MySQL have this restriction?

BLOB, TEXT, GEOMETRY, and JSON columns have a fundamental architectural difference from standard data types. For standard columns (INT, VARCHAR, etc.), MySQL allocates storage once when the table is opened. For LOB (Large Object) types, each value requires separate memory allocation due to their variable size nature. This makes it impossible to pre-allocate a default value at table-definition time.

MariaDB difference: MariaDB 10.2.1+ supports literal default values for BLOB and TEXT columns natively, so if you migrate between MySQL and MariaDB, be aware of this compatibility difference.

ORM workarounds: If you use ORMs like Doctrine, Entity Framework, or Eloquent, configure them to not generate DEFAULT clauses for LOB column types. Many ORMs have options to skip defaults for BLOB/TEXT columns.

Generated columns: Starting with MySQL 5.7, you can use GENERATED columns to derive values from other columns, which can be helpful if you need computed defaults for JSON or TEXT columns.

Frequently asked questions

What does "ERROR 1101: BLOB/TEXT/JSON can't have default value" mean?
This error occurs because MySQL has a fundamental limitation on BLOB, TEXT, GEOMETRY, and JSON data types—they cannot have default values assigned to them directly. This restriction exists because each BLOB, TEXT, GEOMETRY, or JSON value is represented internally by a separately allocated object, unlike other data types that have storage allocated once per column when the table is opened. When you attempt to create or alter a table with a DEFAULT clause on these column types, MySQL raises ERROR 1101 to prevent an invalid schema definition.
How do I fix "ERROR 1101: BLOB/TEXT/JSON can't have default value"?
MySQL does not allow default values on BLOB, TEXT, GEOMETRY, or JSON columns due to how these types allocate memory. Remove the DEFAULT clause or use expression syntax (MySQL 8.0.13+) to fix this error.
What causes "ERROR 1101: BLOB/TEXT/JSON can't have default value"?
Attempting to add a DEFAULT value to a BLOB, TEXT, GEOMETRY, or JSON column in CREATE TABLE Altering a table to add DEFAULT to an existing BLOB/TEXT/JSON column Using ORM-generated migrations that assume all NOT NULL columns need DEFAULT values
Is the "ERROR 1101: BLOB/TEXT/JSON can't have default value" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading