[php] Laravel Migration table already exists, but I want to add new not the older

I previously created users table. Now I have created a new migration to create a new books table inside my schema. When I try to run the command

php artisan migrate

It shows:

[Illuminate\Database\QueryException]
SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'users' alre
ady exists (SQL: create table `users` (`id` int unsigned not null auto_incr
ement primary key, `username` varchar(255) not null, `email` varchar(255) n
ot null, `password` varchar(255) not null, `created_at` timestamp default 0
 not null, `updated_at` timestamp default 0 not null) default character set
 utf8 collate utf8_unicode_ci)

Here is my new migration table:

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateBooksTable extends Migration {
    public function up()
    {
        Schema::create('books', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('name');
            $table->string('auther');
            $table->string('area');
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::drop('books');
    }
}

How can I get rid of the error?

You can use php artisan migrate:fresh to drop all tables and migrate then. Hope it helps

I think my answer will help more. I faced this error also. Then I deleted specific migration file and tried to recreate by php artisan.

But before get this point 1 or 2 days ago while I was watching laracast videos about migation, I was thinking rollback and migrate specific table. For some reason I deleted specific migration file and tried recreate but doing so I got:

[ErrorException] include(C:\wamp64\www\laraveldeneme\vendor\composer/../../database/migrations/2017_01_09_082715_create_articles_table.php): failed to open stream: No such file or directory

When I check that file I saw the line below at top of array in the autoload_classmap.php file:

'CreateArticlesTable'=>$baseDir.'/database/migrations/2017_01_09_083946_create_articles_table.php',

Altough rollback or delete a migration file, the record related with the migration file remains in the composer autoload_classmap.php file.

To solve this problem, I have found composer command below from somewhere I can't remember.

composer dump-autoload

When I rand this code, the line related with the migration file that I deleted is gone. Then I ran:

php artisan make:migration create_articles_table --create=articles

Finally I recreated my migration file with same name

very annoying if we have to delete a table that failed to make. so I made a simple logic to delete a table before creating a new table

if (Schema::hasTable('nama_table')) { Schema::dropIfExists('nama_table'); }

if (Schema::hasTable('books'))
{
    Schema::dropIfExists('books');
}

Schema::create('books', function(Blueprint $table)
{
   $table->increments('id');
   $table->string('name');
   $table->string('auther');
   $table->string('area');
   $table->timestamps();

});

After rollback check your tables , be sure of deletion.

If there is a problem delete tables manually from database application like phpmyadmin ( I'm using sequel pro for mac ).

Correct your down methods in the migration .

Note : Do rollback then migrate .. Don't use migrate:refresh to notice where is the error been .

After that you may test with new db for testing. to detect where is the problem.

Also try reading this question

EDIT: (for laravel)

Just Came across this issue while working on a project in laravel. My tables were messed up, needed to frequent changes on columns. Once the tables were there I wasn't able to run php artisan migrate anymore.

I've done following to get rid of the issue-

1. Drop the tables in the database [every one, including the migration table]
2. $ composer dump-autoload -o
3. php artisan migrate

Previous Comment, regarding lumen

[Well, pretty late to the party (and possibly a different party than what I was looking for). I banged my head, screamed out loud and by the grace of gray skull just found a solution.]

I'm developing an restful app using lumen and I'm new to it. This is my first project/experiment using laraval and lumen. My dependencies-

"require": {
    "php": ">=5.6.4",
    "laravel/lumen-framework": "5.4.*",
    "vlucas/phpdotenv": "~2.2",
    "barryvdh/laravel-cors": "^0.8.6",
    "league/fractal": "^0.13.0"
},
"require-dev": {
    "fzaninotto/faker": "~1.4",
    "phpunit/phpunit": "~5.0",
    "mockery/mockery": "~0.9.4"
}

Anyway, everything was fine until yesterday night but suddenly phpunit started complaining about an already existed table.

Caused by
PDOException: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'items' already exists

Duh! Items table should exist in the database, or else how am i supposed to save items!

Anyway the problem only persisted in the test classes, but strangely not in the browser (I checked with chrome, firefox and postman altering headers). I was getting JSON responses with data as expected.

I dropped the database and recreated it with lots of migrate, refresh, rollback. Everything was fine but in phpunit.

Out of desperation I removed my migration files (of course I took a backup first ) then hit phpunit in the terminal. Same thing all over again.

Suddenly I remembered that I put a different database name in the phpunit.xml file for testing purpose only. I checked that database and guess what! There was a table named items. I removed this table manually, run phpunit, everything started working just fine.

I'm documenting my experience for future references only and with hope this might help someone in future.

Creating a database literally takes seconds.

Export your current database in case it has sensitive data.

Check your migrations and eliminate all wrong methods in it.

Drop database and Re-create database.

php artisan migrate

Then you can return the data previously in the database.

Edit AppServiceProvider.php will be found at app/Providers/AppServiceProvider.php and add

use Illuminate\Support\Facades\Schema;

public function boot()
{
Schema::defaultStringLength(191);
}

Then run

composer update

On your terminal. It helped me, may be it will work for you as well.

Solution :Laravel Migration table already exists... || It Works in Laravel 5.8 also

app\Providers\AppServiceProvider.php file

and inside the boot method set a default string length:

public function boot()
{
    Schema::defaultStringLength(191);
}

and open

config\database.php

'charset' =>'utf8mb4',
'collation' =>'utf8mb4_unicode_ci',

and change it to

'charset' =>'utf8',
'collation' =>'utf8_unicode_ci',

save all the files and go to command prompt

php artisan migrate

go to phpmyadmin and drop the database that you created for laravel then create it again then go to cmd(if use windows) root project and type php artisan migrate

Answer is very straight forward :

First take a backup of folder bootstrap/cache.

Then delete all the files from bootstrap/cache folder .

Now run:

php artisan migrate 

DANGER - most of these answers will wipe your database and is not recommended for production use.

It's clear there are a lot of "solutions" for this problem, but all these solutions I read through are very destructive solutions and none of them work for a production database. Based on the number of solutions, it also seems that there could be several causes to this error.

My error was being caused by a missing entry in my migrations table. I'm not sure exactly how it happened but by adding it back in I no longer received the error.

you drop all tabel in your database, then

php artisan migrate:refresh

edit your migration file

php artisan migrate:rollback

php artisan migrate again ^_^

done your table !!

1. Drop all table database
2. Update two file in folder database/migrations/: 2014_10_12_000000_create_users_table.php, 2014_10_12_100000_create_password_resets_table.php

2014_10_12_100000_create_password_resets_table.php

Schema::create('password_resets', function (Blueprint $table) {
     $table->string('email');
     $table->string('token');
     $table->timestamp('created_at')->nullable();
});

2014_10_12_000000_create_users_table.php

Schema::create('users', function (Blueprint $table) {
     $table->increments('id');
     $table->string('name');
     $table->string('email');
     $table->string('password');
     $table->rememberToken();
     $table->timestamps();
});

1. Drop all tables manually at the phpmyadmin.

2. Go to each migration file at database/migrations. Find and remove these 2 codes :

   a) ->index() (found at 2014_10_12_100000_create_password_resets_table.php at line 17)

   b) ->unique() (found at 2014_10_12_000000_create_users_table.php at line 19)

3. Run php artisan migrate

4. Done.

I think this happens because the latest laravel class (at 12th February 2018) has removed the function of ->index() and ->unique()

I solved your problem by deleting the "user" table in sequel-pro (there is no data in my user table) and then you can run php artisan migrate

Here are before and after screen shots

before I delete the user table user

after I delete the table user

Also u may insert befor Schema::create('books', function(Blueprint $table) the following code Schema::drop('books');

First drop the users table in the database.Then go to command propmt and type

php artisan migrate

all sets.I think this answer helps.

The problem is the name saved in the table migrations inside database, because in my database exists 2017_10_18_200000_name and in files 2016_10_18_200000_name, after change the name of file that work.

So please don't change your file's name for migrations files because the name must be the same according to the last migration.

php artisan migrate:rollback 

Check solution: Laravel Official Solution

As outlined in the Migrations guide to fix this all you have to do is edit your app\Providers\AppServiceProvider.php file and inside the boot method set a default string length:

use Illuminate\Support\Facades\Schema;

public function boot()
{
    Schema::defaultStringLength(191);
}

After above command you need to delete all remaining tables manually and then run the command:

php artisan migrate:fresh

I had a similar problem after messing with foreign key constraints. One of my tables (notes) was gone and one kept coming back (tasks) even after dropping it in MySQL, preventing me from running: php artisan migrate/refresh/reset, which produced the above 42s01 exception.

What I did to solve it was ssh into vagrant then go into MySQL (vagrant ssh, mysql -u homestead -p secret), then: DROP DATABASE homestead; Then CREATE DATABASE homestead; Then exit mysql and run:php artisan migrate`.

Obviously this solution will not work for people not using vagrant/homestead. Not claiming in any way this is a proper workflow but it solved my problem which looks a lot like the above one.

Drop all database table and run this line in your project path via CMD

php artisan migrate

I had the same trouble. The reason is that your file name in migrations folder does not match with name of migration in your database (see migrations table). They should be the same.

You can always check for a table existence before creating it.

    if(!Schema::hasTable('books')){
 Schema::create('books', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('name');
            $table->string('auther');
            $table->string('area');
            $table->timestamps();
        });
}

I was also facing the same problem, I followed the same process but my problem was not resolving so I try another thing. I dropped the tables from my database and use a header file

use Illuminate\Support\Facades\Schema;

and increase the default string length in boot method to add this:

Schema::defaultStringLength(191);

then again php artisan migrate. Problem is resolved, all tables are created in the database.

I inherited some real bad code from someone who wasn't using migrations!?, so manually pasted the filenames into the migrations, forgetting to remove the trailing .php

So that caused the 'table exists' error despite the filename and migration matching.

2018_05_07_142737_create_users_table.php - WRONG 2018_05_07_142737_create_users_table - CORRECT

In laravel 5.4, If you are having this issue. Check this link

-or-

Go to this page in app/Providers/AppServiceProvider.php and add code down below

use Illuminate\Support\Facades\Schema;

public function boot()
{
Schema::defaultStringLength(191);
}

You can delete all the tables but it will be not a good practice, instead try this command

php artisan migrate:fresh

Make sure to use the naming convention correctly. I have tested this in version laravel 5.7 It is important to not try this command when your site is on server because it drops all information.

Add this to AppServiceProvider.php

use Illuminate\Support\Facades\Schema;
public function boot() {
    Schema::defaultStringLength(191);
}

In v5.x, you might still face the problem. So, try to delete related table manually first using

php artisan tinker

Then

Schema::drop('books')

(and exit with q)

Now, you can successfully php artisan migrate:rollback and php artisan migrate.

If this happens repeatedly you should check that the down() method in your migration is showing the right table name. (Can be a gotcha if you've changed your table names.)

I also had this issue, just came across this answer on a Youtube Video. Not sure if it's ideal, but it's the best I've seen.

Seems the AppServiceProvider.php file of the providers directory by giving the Schema a length. In this case 191. Works like magic.Screenshot. He then ran: php artisan migrate:fresh. Hope this works.

First check the migrations table in your DB and sure that your migration files in database folder in your project is equal to this table data. Sometimes if you create migration files by manual appear this error when run migrate command in composer.