1

I'm working on a Laravel application for a while now, but when I run php artisan migrate now, all out of a sudden I always get the error:

SQLSTATE[HY000]: General error: 1364 Field 'id' doesn't have a default value (SQL: insert into `migrations` (`migration`, `batch`) values (2019_06_05_080701_create_departments_table, 22))

and I have no clue why this happens since thsi never happened before. When I try to migrate a specific migration I get the same error, so what is going on here?

The migration looks like this:

public function up()
{
    Schema::create('departments', function (Blueprint $table) {
        $table->increments('id');
        $table->unsignedInteger('company_id');
        $table->string('name');
        $table->string('p_number')->nullable();
        $table->string('address')->nullable();
        $table->string('zipcode')->nullable();
        $table->string('city')->nullable();
        $table->string('contact_name')->nullable();
        $table->string('contact_email')->nullable();
        $table->string('contact_phone')->nullable();

        $table->timestamps();
    });
}

Result of SHOW COLUMNS FROM migrations;

enter image description here

ST80
  • 3,565
  • 16
  • 64
  • 124

3 Answers3

8

Fire this query should work for autoincrement, that's the reason value of id is not getting auto incrementing

ALTER TABLE migrations MODIFY id INTEGER NOT NULL AUTO_INCREMENT;

Note: If there are ids with 0 then you should change it to some other unique id value, and later get the max value of id by query and set autoincrement to +1

SET @new_index = (SELECT MAX(id) FROM migrations );
SET @sql = CONCAT('ALTER TABLE migrations AUTO_INCREMENT = ', @new_index);
PREPARE st FROM @sql;
EXECUTE st;

Reference.

Rahul
  • 18,271
  • 7
  • 41
  • 60
  • I get the error: `1062 - ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'` – ST80 Jun 26 '19 at 06:46
  • 1
    You can get help with this specific problem from [here](https://stackoverflow.com/questions/5402949/mysql-cant-make-column-auto-increment). But I am sure your problem is with autoincrement. – Rahul Jun 26 '19 at 06:49
  • have you checked if id column has 0 values? – Rahul Jun 26 '19 at 07:03
  • Hmm yes, the first entry has id = 0 – ST80 Jun 26 '19 at 09:36
  • @ST80 Once check my answer now – Rahul Jun 26 '19 at 10:57
  • @ST80 Does My changes helped you solve your problem? – Rahul Jun 28 '19 at 12:28
  • Yeah, your answer is similiar with the one I found [here](https://stackoverflow.com/questions/5402949/mysql-cant-make-column-auto-increment) . So I'm gonna accept it .-) – ST80 Jun 28 '19 at 12:39
0

After a lot of research and good answers I found this solution, which solved it for me:

ALTER TABLE migrations CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT = 123456;

The solution is based on this answer from a similiar question- Thanks to @Dracula Predator for the good hint.

ST80
  • 3,565
  • 16
  • 64
  • 124
0

You have been getting this error because probably you have modified migrations table. You can try few options

  1. Run php artisan migrate:fresh command
  2. Drop all tables and rerun all migration
Ashutosh Kamble
  • 193
  • 5
  • As you can read in the question the error throws during inserting something into the migrations table which (normally and in this case) is not affected by migration scripts. So your solution is not usefull. – thmspl Jan 08 '20 at 12:44