Laravel Migration Error: SQLSTATE[42000]: Syntax e…

I am learning migration in Laravel. I have created a migration file and migrations seems to work fine from command line. But inside my mysql file the username column is not created after migration.

>php artisan make:migration add_username_to_users_table
 Created Migration: 2022_07_15_123530_add_username_to_users_table
>php artisan migrate 
 Migrating: 2022_07_15_123530_add_username_to_users_table 
 Migrated:  2022_07_15_123530_add_username_to_users_table (31.34ms)

Migration file:

<?php

use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateSupportFacadesSchema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->string('username');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropColumn('username');
        });
    }
};

users table in mysql database: No username column created

MariaDB [julai_pro]> describe users;
+-------------------+---------------------+------+-----+---------+----------------+
| Field             | Type                | Null | Key | Default | Extra          |
+-------------------+---------------------+------+-----+---------+----------------+
| id                | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name              | varchar(255)        | NO   |     | NULL    |                |
| email             | varchar(255)        | NO   | UNI | NULL    |                |
| email_verified_at | timestamp           | YES  |     | NULL    |                |
| password          | varchar(255)        | NO   |     | NULL    |                |
| remember_token    | varchar(100)        | YES  |     | NULL    |                |
| created_at        | timestamp           | YES  |     | NULL    |                |
| updated_at        | timestamp           | YES  |     | NULL    |                |
+-------------------+---------------------+------+-----+---------+----------------+
8 rows in set (1.265 sec)

And when refresh my migration it gives me the below error

>php artisan migrate:refresh 
 Rolling back: 2022_07_15_123530_add_username_to_users_table

   IlluminateDatabaseQueryException

  SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP COLUMN `username`; check that it exists (SQL: alter table `users` drop `username`)

 
-appvendorlaravelframeworksrcIlluminateDatabaseConnection.php:759
    755▕         // If an exception occurs when attempting to run a query, we'll format the error
    756▕         // message to include the bindings with SQL, which will make this exception a
    757▕         // lot more helpful to the developer instead of just the database's errors.
    758▕         catch (Exception $e) {   ➜ 759▕             throw new QueryException(
    760▕                 $query, $this->prepareBindings($bindings), $e
    761▕             );
    762▕         }
    763▕     }

  1  
-appvendorlaravelframeworksrcIlluminateDatabaseConnection.php:544
      PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP COLUMN `username`; check that it exists")

  2  
-appvendorlaravelframeworksrcIlluminateDatabaseConnection.php:544
      PDOStatement::execute()

>php artisan migrate 
 Nothing to migrate.

Option1 : I tried to solve this with the below command with success.

>php artisan cache:clear
Application cache cleared successfully.

>php artisan config:cache
Configuration cache cleared successfully.
Configuration cached successfully.

Option 2 : I also tried the option from @Udhav Sarvaiya

Open your app/Providers/AppServiceProvider.php file and inside the boot() method set a default string length:

use IlluminateSupportFacadesSchema;

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

None of the options solved the problem the username Column is not created in users table. Kindly assist.

Edit: the migration table in database

MariaDB [julai_pro]> select * from migrations;
+----+-------------------------------------------------------+-------+
| id | migration                                             | batch |
+----+-------------------------------------------------------+-------+
|  1 | 2014_10_12_000000_create_users_table                  |     1 |
|  2 | 2014_10_12_100000_create_password_resets_table        |     1 |
|  3 | 2019_08_19_000000_create_failed_jobs_table            |     1 |
|  4 | 2019_12_14_000001_create_personal_access_tokens_table |     1 |
|  5 | 2022_07_15_123530_add_username_to_users_table         |     2 |
+----+-------------------------------------------------------+-------+
5 rows in set (0.056 sec)

Answer

php artisan migrate:refresh first rolls back all your migrations then runs it again you would want to do php artisan migrate to add a migration and php artisan migrate:fresh if you would like it to wipe the database first

> php artisan migrate
Nothing to migrate.

you can look in the migrations table to see which migrations ran

Laravel Migration Error: SQLSTATE[42000]: Syntax error or access violation: 1091 with mysql

Advertisement

Answer