Can't Execute SQL File Event After Disabling Strict Mode

Question

I'm using Ubuntu 18.04 with Mysql and I'm trying to drop this SQL file:

CREATE TABLE IF NOT EXISTS `ammunition` 
(
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `owner` text DEFAULT NULL,
    `original_owner` text NOT NULL,
    `hash` text NOT NULL,
    `weapon_id` char(60) NOT NULL,
    `count` int(11) NOT NULL DEFAULT 0,
    `attach` text NOT NULL DEFAULT '[]',
    PRIMARY KEY (`id`),
    UNIQUE KEY `weapon_id` (`weapon_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

I keep getting this error:

SQL ERROR (1101): BLOB, TEXT, GEOMETRY or JSON column attach can't have a default value

I've tried solving it, but the only suggestion I could find was to disable strict mode.

I disabled it permanently:

[mysqld]
sql_mode=""

And restarted the mysql service afterwards.

The temporary way without restart:

set global sql_mode='';

I'm still getting the error message, however. What am I doing wrong?

Does this answer your question? [Why can't a text column have a default value in MySQL?](https://stackoverflow.com/questions/3466872/why-cant-a-text-column-have-a-default-value-in-mysql) — Jorge Campos, Jul 30 '21 at 22:37

score 1 · Accepted Answer · answered Jul 30 '21 at 23:43

When you read the error message you see that TEXT..column attach can't have a default value.

To correct this, don't put a default value there.

If you do need a default value, use a VARCHAR column.

Disabling sql strict mode is is worst thing you can do. It will get you into trouble later.

Can't Execute SQL File Event After Disabling Strict Mode

1 Answers1