Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL will not let a TEXT column have a default value

ErrorERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'notes' can't have a default value

Short answer

MySQL only accepts a default on BLOB, TEXT, GEOMETRY and JSON columns when it is written as an expression, which means wrapped in parentheses. A plain literal such as DEFAULT 'abc' raises error 1101. Write DEFAULT ('abc'), or switch the column to VARCHAR if the text is short.

  • Applies to MySQL 8.0, MySQL 8.4 LTS
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Look at the column type in the statement that failed. If it is TEXT, BLOB, GEOMETRY or JSON and the default is a plain literal, this page applies.

    SHOW CREATE TABLE posts\G
  2. 2Match what you see, then jump to its fix

    1. 1The failing statement has DEFAULT '...' or DEFAULT '' after a TEXT, BLOB or JSON column.The default is written as a literalFix
    2. 2The values are short, for example a status or a label.The column does not need to be TEXTFix

What does the error look like?

  • ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'notes' can't have a default value

Cause 1 of 2

The default is written as a literal

1Check

The manual says these types can have a default only if it is written as an expression, even when the expression gives a constant. A bare literal is the error case.

SHOW COLUMNS FROM posts LIKE 'notes';

2Fix

Put the value in parentheses so the server treats it as an expression.

ALTER TABLE posts MODIFY notes TEXT NOT NULL DEFAULT ('');

CREATE TABLE t2 (b BLOB DEFAULT ('abc'));

3Verify

Insert a row without the column and read the default back.

INSERT INTO posts (title) VALUES ('test');
SELECT notes FROM posts ORDER BY id DESC LIMIT 1;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 2

The column does not need to be TEXT

1Check

The restriction in the manual is limited to BLOB, TEXT, GEOMETRY and JSON columns. Find the longest value you store.

SELECT MAX(CHAR_LENGTH(notes)) FROM posts;

2Fix

Use a VARCHAR column sized for the longest value and give it an ordinary default.

ALTER TABLE posts MODIFY notes VARCHAR(255) NOT NULL DEFAULT '';

3Verify

The column definition now shows the default.

SHOW COLUMNS FROM posts LIKE 'notes';

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Pick TEXT only for content that can be long; use VARCHAR for labels and codes.
  • Wrap defaults for JSON and BLOB columns in parentheses from the first CREATE TABLE.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.