MySql Error: 1364 Field 'display_name' doesn't have default value

Question

I have just switched from a MAMP installation to a native Apache, MySql and PHP installation. I have got everything working, but I have started using my web app in the new environment and suddenly any INSERT commands are resulting in the following error:

SQLSTATE[HY000]: General error: 1364 Field 'display_name' doesn't have a default value

It seems the I am unable to leave a field blank now where I was able to before. I am using MySql version 5.6.13

Is there a way to change this setting in MySql?

possible duplicate of [How do I alter a mysql table column defaults?](http://stackoverflow.com/questions/1770203/how-do-i-alter-a-mysql-table-column-defaults) — JimL, Sep 11 '13 at 21:08
Is display_name a field with NOT NULL constraint and no default value? Is your insert statement not providing a value for display_name? Knowing your table structure and the insert statement would be helpful. — zedfoxus, Sep 11 '13 at 21:09
Remove the strict settings or set the default values to `NULL` — Robot Boy, Jul 02 '15 at 12:39
I faced same issue , the problem was I created trigger on table and I was not able to delete records from the table and it show same error.after delete trigger it works now. — Prashant Patel, Nov 30 '18 at 13:53

score 145 · Accepted Answer · edited Apr 22 '18 at 02:47

145

MySQL is most likely in STRICT mode.

Try running SET GLOBAL sql_mode='' or edit your my.cnf to make sure you aren't setting STRICT_ALL_TABLES or the like.

edited Apr 22 '18 at 02:47

Muhammad Hassaan

7,296
6
30
50

answered Sep 11 '13 at 21:12

Tim B

1,879
1
13
8

9

Is it a bad practice to use strict mode in MySQL? – Artemis Nov 12 '15 at 11:43
1

Before you do it, you can see current value by this: SELECT @@GLOBAL.sql_mode – Kamil Kiełczewski Jun 21 '16 at 16:31
Is this only for >= 5.7 ? – Yasar Arafath Dec 08 '17 at 05:41

calcinai · Answer 2 · 2017-07-31T22:35:54.787

21

MySQL is most likely in STRICT mode, which isn't necessarily a bad thing, as you'll identify bugs/issues early and not just blindly think everything is working as you intended.

Change the column to allow null:

ALTER TABLE `x` CHANGE `display_name` `display_name` TEXT NULL

or, give it a default value as empty string:

ALTER TABLE `x` CHANGE `display_name` `display_name` TEXT NOT NULL DEFAULT ''

edited Jul 31 '17 at 22:35

answered Sep 11 '13 at 21:13

calcinai

2,567
14
25

2

This is a much better solution. As a developer, you shouldn't rely on lazy coding. Know exactly what you are putting into your database at all time. Strict mode is a great benefit to developers, as it helps identify sloppy code. – hackel Jul 20 '16 at 01:02
3

This results in "... TEXT column can't have a default value". – some-non-descript-user Feb 10 '17 at 14:22
Empty string is not a valid default value -- it should be a whitespace string `' '` – Martin May 24 '19 at 12:15

score 18 · Answer 3 · edited Mar 06 '19 at 11:29

18

All of these answers are a good way, but I don't think you want to always go to your DB and modify the default value that you have set to NULL.

I suggest you go to the app/User.php and modify the protected $fillable so it will take your new fields in the data array used to register.

Let's say you add a new input field called "first_name", your $fillable should be something like this :

protected $fillable = [ 'first_name', 'email', 'password',];

This did it for me. Good luck!

edited Mar 06 '19 at 11:29

Udhav Sarvaiya

9,380
13
53
64

answered Nov 27 '16 at 00:15

Stefan Paius

199
1
9

5

Why are you assuming the OP is using laravel/eloquent? – jdunk Dec 27 '16 at 19:35
This was the gotcha that got me. Will upvote once you mention that this is spcific to Laravel and Eloquent – Vanlalhriata Jan 16 '17 at 15:55
$fillable was why I was getting this error message. Added columns to my table and to the store() method, but forgot to add the column names to $fillable! – Andrew Koper Jan 20 '19 at 00:27

score 6 · Answer 4 · edited Mar 06 '19 at 11:42

I also faced that problem and there are two ways to solve this in laravel.

first one is you can set the default value as null. I will show you an example:

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('gender');
        $table->string('slug');
        $table->string('pic')->nullable();
        $table->string('email')->unique();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
    });
}

as the above example, you can set nullable() for that feature. then when you are inserting data MySQL set the default value as null.

second one is in your model set your input field in protected $fillable field. as example:
```
protected $fillable = [
    'name', 'email', 'password', 'slug', 'gender','pic'
];
```

I think the second one is fine than the first one and also you can set nullable feature as well as fillable in the same time without a problem.

Ryan Jarvis · Answer 5 · 2018-06-01T16:48:57.930

Also, I had this issue using Laravel, but fixed by changing my database schema to allow "null" inputs on a table where I plan to collect the information from separate forms:

public function up()
{

    Schema::create('trip_table', function (Blueprint $table) {
        $table->increments('trip_id')->unsigned();
        $table->time('est_start');
        $table->time('est_end');
        $table->time('act_start')->nullable();
        $table->time('act_end')->nullable();
        $table->date('Trip_Date');
        $table->integer('Starting_Miles')->nullable();
        $table->integer('Ending_Miles')->nullable();
        $table->string('Bus_id')->nullable();
        $table->string('Event');
        $table->string('Desc')->nullable();
        $table->string('Destination');
        $table->string('Departure_location');
        $table->text('Drivers_Comment')->nullable();
        $table->string('Requester')->nullable();
        $table->integer('driver_id')->nullable();
        $table->timestamps();
    });

}

The ->nullable(); Added to the end. This is using Laravel. Hope this helps someone, thanks!

Andrew Koper · Answer 6 · 2019-12-03T16:00:46.283

2

I got this error when I forgot to add new form fields/database columns to the $fillable array in the Laravel model - the model was stripping them out.

edited Dec 03 '19 at 16:00

answered Feb 09 '19 at 22:27

Andrew Koper

6,481
6
42
50

score 0 · Answer 7 · answered Sep 15 '20 at 09:52

0

I also had this issue using Lumen, but fixed by setting DB_STRICT_MODE=false in .env file.

answered Sep 15 '20 at 09:52

saee

433
1
3
15

MySql Error: 1364 Field 'display_name' doesn't have default value

7 Answers7

Linked

Related