Sunday 29 March 2020

General error: 1364 Field city_id doesn't have a default value

I have upgraded the MySQL from 5.5 to 5.6 and then getting below type of errors.
General error: 1364 Field 'city_id' doesn't have a default value

(I was not getting such type of error, it must come after upgrading my MySQL Version from 5.5 to 5.6)

Solution: It have 3 Solutions

  1. In Your Code (PHP), always set a default value (if no value) before save.
            'name'=>'New user',
            'city_id'=>0, //default value
  2. Create fields with default values set in datatable. It could be empty string, which is fine for MySQL server running in strict mode.
    like below:
    ALTER TABLE `users` CHANGE `city_id` `city_id` INT(10) UNSIGNED NOT NULL DEFAULT '0';
  3. Disable MySQL Strict Mode (Most appropriate)
    Disable it by setting your own SQL_MODE in the my.cnf file, then restart MySQL.

    Look for the following line:
    Change it to:
    Restart the MySQL Service.