Mysql error BLOB, TEXT, GEOMETRY or JSON colum cant have default value

user_8530696 0
Viewed 15456

My vps linux ubuntu is Server type: MySQL Server version: 5.7.33 ubuntu0.18.04.1 - (Ubuntu) Apache/2.4.29 (Ubuntu) Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 - PHP version: 7.2.24-

Trying to add a column in my database but im getting error I tried that on windows xampp its work without any problem

ALTER TABLE users ADD COLUMN house LONGTEXT NOT NULL DEFAULT '{"owns":false,"furniture":[],"houseId":0}';

Error

SQL query:


ALTER TABLE users ADD COLUMN house LONGTEXT NOT NULL DEFAULT '{"owns":false,"furniture":[],"houseId":0}'
MySQL said: Documentation

#1101 - BLOB, TEXT, GEOMETRY or JSON column 'house' can't have a default value
phpmyadmin mysql
2 Answers

I'm going to guess that your local XAMPP development server is running MySQL 8.0.13 or newer — or MariaDB 10.2.1 or newer. Prior to that version, MYSQL did not allow a DEFAULT value other than NULL for JSON columns. In MariaDB, JSON is an alias for LONGTEXT, which likewise (starting with version 10.2.1) allows DEFAULT values.

Probably your development environment allows the defaults and the production environment doesn't. You can either upgrade your production system or not use that feature.

user_23854790

Remove the default value:

ALTER TABLE users ADD COLUMN house LONGTEXT NOT NULL;
user_149562340
Related