Laravel Migration table already exists, but I want to add new not the older
PhpMysqlLaravelPhp Problem Overview
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?
Php Solutions
Solution 1 - Php
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.)
Solution 2 - Php
You need to run
php artisan migrate:rollback
if that also fails just go in and drop all the tables which you may have to do as it seems your migration table is messed up or your user table when you ran a previous rollback did not drop the table.
EDIT:
The reason this happens is that you ran a rollback previously and it had some error in the code or did not drop the table. This still however messes up the laravel migration table and as far as it's concerned you now have no record of pushing the user table up. The user table does already exist however and this error is throw.
Solution 3 - Php
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.
Solution 4 - Php
You can use
php artisan migrate:fresh
to drop all tables and migrate then.
Hope it helps
Solution 5 - Php
Also u may insert befor
Schema::create('books', function(Blueprint $table) the following code Schema::drop('books');
Solution 6 - Php
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-
- Drop the tables in the database [every one, including the migration table]
$ composer dump-autoload -ophp 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.
Solution 7 - Php
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
Solution 8 - Php
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.
Solution 9 - Php
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.
Solution 10 - Php
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 !!
Solution 11 - Php
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
Solution 12 - Php
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
Solution 13 - Php
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();
});
}
Solution 14 - Php
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
Solution 15 - Php
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.
Solution 16 - Php
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
Solution 17 - Php
Add this to AppServiceProvider.php
use Illuminate\Support\Facades\Schema;
public function boot() {
Schema::defaultStringLength(191);
}
Solution 18 - Php
- Drop all table database
- 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();
});
Solution 19 - Php
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
Solution 20 - Php
-
Drop all tables manually at the phpmyadmin.
-
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)
-
Run
php artisan migrate -
Done.
I think this happens because the latest laravel class (at 12th February 2018) has removed the function of ->index() and ->unique()
Solution 21 - Php
For anybody still looking for an answer, if the migration is stuck at trying to run one of the migrations that have already been run (hence, the "Table already exists" problem) simply do the following:
- Open the PHP file for the table causing the issue and comment out all the code in the "up()" function.
- Run "php artisan migrate" again. The file/table causing the problem will process with it's empty function.
The migration will then move on to your new waiting migrations. Once done, uncomment the lines previously commented out (optional but recommended so as not to confuse other developers)
Solution 22 - Php
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.
Solution 23 - Php
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);
}
Solution 24 - Php
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.
Solution 25 - Php
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.
Solution 26 - Php
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.
Solution 27 - Php
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.
Solution 28 - Php
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
Solution 29 - Php
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.
Solution 30 - Php
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
Solution 31 - Php
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();
});
Solution 32 - Php
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 33 - Php
Drop all database table and run this line in your project path via CMD
php artisan migrate