Mysql error BLOB, TEXT, GEOMETRY or JSON column can't have a default value

Question

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 I'm getting an 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

Answer 1

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.