Login/Register
Laravel/Lumen Incorrect Table Name '' for migrations table SQLSTATE[42000]
Asked Answered
laravelvagrantlumenartisan-migrate
F

2

6

When running php artisan migrate I get the an error when it is trying to create the migrations table in mysql:

In Connection.php line 664:

  SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name '' (SQL: create table `` (`id` int u  
  nsigned not null auto_increment primary key, `migration` varchar(255) not null, `batch` int not null) default cha  
  racter set utf8 collate 'utf8_unicode_ci')                                                                         


In Connection.php line 452:

  SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name ''

Here is the full error log:

In Connection.php line 664:

  [Illuminate\Database\QueryException (42000)]                                                                       
  SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name '' (SQL: create table `` (`id` int u  
  nsigned not null auto_increment primary key, `migration` varchar(255) not null, `batch` int not null) default cha  
  racter set utf8 collate 'utf8_unicode_ci')                                                                         


Exception trace:
 () at /home/vagrant/api/vendor/illuminate/database/Connection.php:664
 Illuminate\Database\Connection->runQueryCallback() at /home/vagrant/api/vendor/illuminate/database/Connection.php:624
 Illuminate\Database\Connection->run() at /home/vagrant/api/vendor/illuminate/database/Connection.php:459
 Illuminate\Database\Connection->statement() at /home/vagrant/api/vendor/illuminate/database/Schema/Blueprint.php:97
 Illuminate\Database\Schema\Blueprint->build() at /home/vagrant/api/vendor/illuminate/database/Schema/Builder.php:278
 Illuminate\Database\Schema\Builder->build() at /home/vagrant/api/vendor/illuminate/database/Schema/Builder.php:167
 Illuminate\Database\Schema\Builder->create() at /home/vagrant/api/vendor/illuminate/database/Migrations/DatabaseMigrationRepository.php:157
 Illuminate\Database\Migrations\DatabaseMigrationRepository->createRepository() at /home/vagrant/api/vendor/illuminate/database/Console/Migrations/InstallCommand.php:54
 Illuminate\Database\Console\Migrations\InstallCommand->handle() at n/a:n/a
 call_user_func_array() at /home/vagrant/api/vendor/illuminate/container/BoundMethod.php:32
 Illuminate\Container\BoundMethod::Illuminate\Container\{closure}() at /home/vagrant/api/vendor/illuminate/container/BoundMethod.php:90
 Illuminate\Container\BoundMethod::callBoundMethod() at /home/vagrant/api/vendor/illuminate/container/BoundMethod.php:34
 Illuminate\Container\BoundMethod::call() at /home/vagrant/api/vendor/illuminate/container/Container.php:576
 Illuminate\Container\Container->call() at /home/vagrant/api/vendor/illuminate/console/Command.php:183
 Illuminate\Console\Command->execute() at /home/vagrant/api/vendor/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /home/vagrant/api/vendor/illuminate/console/Command.php:170
 Illuminate\Console\Command->run() at /home/vagrant/api/vendor/illuminate/console/Command.php:198
 Illuminate\Console\Command->call() at /home/vagrant/api/vendor/illuminate/database/Console/Migrations/MigrateCommand.php:93
 Illuminate\Database\Console\Migrations\MigrateCommand->prepareDatabase() at /home/vagrant/api/vendor/illuminate/database/Console/Migrations/MigrateCommand.php:63
 Illuminate\Database\Console\Migrations\MigrateCommand->handle() at n/a:n/a
 call_user_func_array() at /home/vagrant/api/vendor/illuminate/container/BoundMethod.php:32
 Illuminate\Container\BoundMethod::Illuminate\Container\{closure}() at /home/vagrant/api/vendor/illuminate/container/BoundMethod.php:90
 Illuminate\Container\BoundMethod::callBoundMethod() at /home/vagrant/api/vendor/illuminate/container/BoundMethod.php:34
 Illuminate\Container\BoundMethod::call() at /home/vagrant/api/vendor/illuminate/container/Container.php:576
 Illuminate\Container\Container->call() at /home/vagrant/api/vendor/illuminate/console/Command.php:183
 Illuminate\Console\Command->execute() at /home/vagrant/api/vendor/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /home/vagrant/api/vendor/illuminate/console/Command.php:170
 Illuminate\Console\Command->run() at /home/vagrant/api/vendor/symfony/console/Application.php:921
 Symfony\Component\Console\Application->doRunCommand() at /home/vagrant/api/vendor/symfony/console/Application.php:273
 Symfony\Component\Console\Application->doRun() at /home/vagrant/api/vendor/symfony/console/Application.php:149
 Symfony\Component\Console\Application->run() at /home/vagrant/api/vendor/illuminate/console/Application.php:90
 Illuminate\Console\Application->run() at /home/vagrant/api/vendor/laravel/lumen-framework/src/Console/Kernel.php:115
 Laravel\Lumen\Console\Kernel->handle() at /home/vagrant/api/artisan:35

In Connection.php line 452:

  [PDOException (42000)]                                                           
  SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name ''  


Exception trace:
 () at /home/vagrant/api/vendor/illuminate/database/Connection.php:452
 PDO->prepare() at /home/vagrant/api/vendor/illuminate/database/Connection.php:452
 Illuminate\Database\Connection->Illuminate\Database\{closure}() at /home/vagrant/api/vendor/illuminate/database/Connection.php:657
 Illuminate\Database\Connection->runQueryCallback() at /home/vagrant/api/vendor/illuminate/database/Connection.php:624
 Illuminate\Database\Connection->run() at /home/vagrant/api/vendor/illuminate/database/Connection.php:459
 Illuminate\Database\Connection->statement() at /home/vagrant/api/vendor/illuminate/database/Schema/Blueprint.php:97
 Illuminate\Database\Schema\Blueprint->build() at /home/vagrant/api/vendor/illuminate/database/Schema/Builder.php:278
 Illuminate\Database\Schema\Builder->build() at /home/vagrant/api/vendor/illuminate/database/Schema/Builder.php:167
 Illuminate\Database\Schema\Builder->create() at /home/vagrant/api/vendor/illuminate/database/Migrations/DatabaseMigrationRepository.php:157
 Illuminate\Database\Migrations\DatabaseMigrationRepository->createRepository() at /home/vagrant/api/vendor/illuminate/database/Console/Migrations/InstallCommand.php:54
 Illuminate\Database\Console\Migrations\InstallCommand->handle() at n/a:n/a
 call_user_func_array() at /home/vagrant/api/vendor/illuminate/container/BoundMethod.php:32
 Illuminate\Container\BoundMethod::Illuminate\Container\{closure}() at /home/vagrant/api/vendor/illuminate/container/BoundMethod.php:90
 Illuminate\Container\BoundMethod::callBoundMethod() at /home/vagrant/api/vendor/illuminate/container/BoundMethod.php:34
 Illuminate\Container\BoundMethod::call() at /home/vagrant/api/vendor/illuminate/container/Container.php:576
 Illuminate\Container\Container->call() at /home/vagrant/api/vendor/illuminate/console/Command.php:183
 Illuminate\Console\Command->execute() at /home/vagrant/api/vendor/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /home/vagrant/api/vendor/illuminate/console/Command.php:170
 Illuminate\Console\Command->run() at /home/vagrant/api/vendor/illuminate/console/Command.php:198
 Illuminate\Console\Command->call() at /home/vagrant/api/vendor/illuminate/database/Console/Migrations/MigrateCommand.php:93
 Illuminate\Database\Console\Migrations\MigrateCommand->prepareDatabase() at /home/vagrant/api/vendor/illuminate/database/Console/Migrations/MigrateCommand.php:63
 Illuminate\Database\Console\Migrations\MigrateCommand->handle() at n/a:n/a
 call_user_func_array() at /home/vagrant/api/vendor/illuminate/container/BoundMethod.php:32
 Illuminate\Container\BoundMethod::Illuminate\Container\{closure}() at /home/vagrant/api/vendor/illuminate/container/BoundMethod.php:90
 Illuminate\Container\BoundMethod::callBoundMethod() at /home/vagrant/api/vendor/illuminate/container/BoundMethod.php:34
 Illuminate\Container\BoundMethod::call() at /home/vagrant/api/vendor/illuminate/container/Container.php:576
 Illuminate\Container\Container->call() at /home/vagrant/api/vendor/illuminate/console/Command.php:183
 Illuminate\Console\Command->execute() at /home/vagrant/api/vendor/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /home/vagrant/api/vendor/illuminate/console/Command.php:170
 Illuminate\Console\Command->run() at /home/vagrant/api/vendor/symfony/console/Application.php:921
 Symfony\Component\Console\Application->doRunCommand() at /home/vagrant/api/vendor/symfony/console/Application.php:273
 Symfony\Component\Console\Application->doRun() at /home/vagrant/api/vendor/symfony/console/Application.php:149
 Symfony\Component\Console\Application->run() at /home/vagrant/api/vendor/illuminate/console/Application.php:90
 Illuminate\Console\Application->run() at /home/vagrant/api/vendor/laravel/lumen-framework/src/Console/Kernel.php:115
 Laravel\Lumen\Console\Kernel->handle() at /home/vagrant/api/artisan:35

I have tried destroying and rebuilding the vagrant instance, plus removed and reinstalled composer packages, but the problem persists.

As requested, migration files:

    <?php

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

    class CreateTeachersTable extends Migration
    {
        /**
         * Run the migrations.
         *
         * @return void
         */
        public function up()
        {
            Schema::create('teachers', function (Blueprint $table) {
                $table->bigIncrements('id');
                $table->string('name');
                $table->string('address');
                $table->string('phone');
                $table->string('profession');
                $table->timestamps();
            });
        }

        /**
         * Reverse the migrations.
         *
         * @return void
         */
        public function down()
        {
            Schema::dropIfExists('teachers');
        }
    }

    <?php

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

    class CreateStudentsTable extends Migration
    {
        /**
         * Run the migrations.
         *
         * @return void
         */
        public function up()
        {
            Schema::create('students', function (Blueprint $table) {
                $table->bigIncrements('id');
                $table->string('name');
                $table->string('address');
                $table->string('phone');
                $table->string('career');
                $table->timestamps();
            });
        }

        /**
         * Reverse the migrations.
         *
         * @return void
         */
        public function down()
        {
            Schema::dropIfExists('students');
        }
    }

<?php

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

class CreateCoursesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('courses', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('title');
            $table->text('description');
            $table->integer('value')->unsigned();
            $table->bigInteger('teacher_id')->unsigned();
            $table->foreign('teacher_id')->references('id')->on('teachers');
            $table->timestamps();
        });

    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('courses');
    }
}

<?php

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

class CreateCourseStudentTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('course_student', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->bigInteger('course_id')->unsigned();
            $table->foreign('course_id')->references('id')->on('courses');
            $table->bigInteger('student_id')->unsigned();
            $table->foreign('student_id')->references('id')->on('students');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('course_student');
    }
}

<?php

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

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->bigInteger('role_id');
            $table->string('email')->unique();
            $table->string('first_name');
            $table->string('last_name');
            $table->string('password');
            $table->tinyInteger('status')->default(1);
            $table->tinyInteger('master')->default(0);
            $table->softDeletes();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

<?php

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

class CreateSystemsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('systems', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->tinyInteger('status');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('systems');
    }
}

<?php

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

class CreateUserRolesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('user_roles', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->bigInteger('system_id');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('user_roles');
    }
}

<?php

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

class CreateUserRolePermissionsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('user_role_permissions', function (Blueprint $table) {
            $table->bigInteger('user_role_id');
            $table->bigInteger('permission_id');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('user_role_permissions');
    }
}

<?php

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

class CreateUserRolePermissionsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('user_role_permissions', function (Blueprint $table) {
            $table->bigInteger('user_role_id');
            $table->bigInteger('permission_id');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('user_role_permissions');
    }
}

<?php

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

class CreateLeadTypesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('lead_types', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->bigInteger('branch_id');
            $table->string('name');
            $table->string('token')->nullable();
            $table->softDeletes();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('lead_types');
    }
}

<?php

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

class CreateLeadStatusesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('lead_statuses', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->bigInteger('branch_id');
            $table->string('name');
            $table->softDeletes();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('lead_statuses');
    }
}

<?php

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

class CreateLeadsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('leads', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->bigInteger('lead_type_id');
            $table->bigInteger('status_id');
            $table->bigInteger('owner_id');
            $table->string('description');
            $table->softDeletes();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('leads');
    }
}

On a side note the most recent package I added to the system was for mongodb jenssegers/mongodb but disabling this has made no difference.

config/database.php

<?php
return [
    'default' => 'mysql',
    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST'),
            'database' => env('DB_DATABASE'),
            'username' => env('DB_USERNAME'),
            'password' => env('DB_PASSWORD'),
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
        ],
        'mongodb' => [
            'driver' => 'mongodb',
            'host' => env('MONGODB_HOST'),
            'port' => env('MONGODB_PORT'),
            'database' => env('MONGODB_DATABASE'),
            'username' => env('MONGODB_USERNAME'),
            'password' => env('MONGODB_PASSWORD'),
            'options' => [
                'database' => 'admin'
            ]
        ],
    ]
];

Requested .env file:

APP_NAME=APP
APP_ENV=local
APP_KEY=ka0mdHChmnMoQzSqWle7Zez
APP_DEBUG=true
APP_URL=http://localhost
APP_TIMEZONE=UTC

LOG_CHANNEL=stack
LOG_SLACK_WEBHOOK_URL=

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret

CACHE_DRIVER=file
QUEUE_CONNECTION=sync

JWT_SECRET=VJXaIerVvIMXAF7w0Tt6FsMU8
Frieze answered 13/8, 2019 at 7:56 Comment(10)
Please post your migration codes – Guard
Provide the migration files – Lisalisabet
When you created the migration file did you use the artisan command? try using php artisan make:migration MIGRATION_NAME_HERE --table=TABLENAME This will specify the table name for you in the migration. – Gilt
Did you change anything in config/database.php? – Ottie
I have added migration file and config/database.php file above – Frieze
Can you provide the DB information from your .env file please? sometimes vagrant likes "localhost" as the DB host instead of "127.0.0.1" if you have that configured. – Gilt
@Gilt Added .env file – Frieze
When your migration is executing it is trying to create a table with no name - create table `` here the name of the table is blank – Attorneyatlaw
@MayankPandeyz yes, it is trying to create the migrations table, but with no name. – Frieze
@Frieze Try php artisan migrate:install or php artisan migrate:install --database=mysql to specify the database connection to use.. – Edna
F
17

After much trial and error I discovered that the issue was in the config/database.php file. I had to add a migrations variable ('migrations' => 'migrations')

So this:

<?php
return [
    'default' => 'mysql',
    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST'),
            'database' => env('DB_DATABASE'),
            'username' => env('DB_USERNAME'),
            'password' => env('DB_PASSWORD'),
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
        ],
        'mongodb' => [
            'driver' => 'mongodb',
            'host' => env('MONGODB_HOST'),
            'port' => env('MONGODB_PORT'),
            'database' => env('MONGODB_DATABASE'),
            'username' => env('MONGODB_USERNAME'),
            'password' => env('MONGODB_PASSWORD'),
            'options' => [
                'database' => 'admin'
            ]
        ],
    ]
];

became this: 

<?php
return [
    'default' => 'mysql',
    'migrations' => 'migrations',
    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST'),
            'database' => env('DB_DATABASE'),
            'username' => env('DB_USERNAME'),
            'password' => env('DB_PASSWORD'),
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
        ],
        'mongodb' => [
            'driver' => 'mongodb',
            'host' => env('MONGODB_HOST'),
            'port' => env('MONGODB_PORT'),
            'database' => env('MONGODB_DATABASE'),
            'username' => env('MONGODB_USERNAME'),
            'password' => env('MONGODB_PASSWORD'),
            'options' => [
                'database' => 'admin'
            ]
        ],
    ]
];

Now the system is working as expected. Thanks for everyones help :)

Frieze answered 13/8, 2019 at 11:41 Comment(7)
Hi, do you have any idea how can I do this on lumen? – Barbera
@Barbera This file is on the config/ folder: config/database.php. BUT it will only be there in Lumen if you create it manually, for instance, when you need to add a second database connection. – Jhvh
I created mine, added a second connection to another dtabase, but the example I got also didn't specify the 'migrations' attribute, so I had the error and this answer solved it for me. Thanks :) – Jhvh
@Frieze How did you find that this was the issue? – Slapjack
this is the right answer to be able to use the command PHP artisan migrate if database was configured in config. – Mot
Bro, please make it as a legit answer. Thanks for helping. – Hammons
This was my problem as well, thanks for the hint. 🍻 – Litta
C
0

add 'migrations' => 'migrations', to database.php it will work

Conventicle answered 18/2, 2021 at 6:13 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.