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.
    $objSaveData->saveData(
        array(
            'id'=>111,
            '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:
    sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    Change it to:
    sql-mode="" 
    Restart the MySQL Service.