MySQL will not let a TEXT column have a default value
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'notes' can't have a default valueShort 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.
Fix it fast
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\G2Match what you see, then jump to its fix
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
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix