Set up cross database foreign key with Laravel sail

[paolocarrara]

• Laravel Version: 8.12
• PHP Version: 7.4
• Database Driver & Version: mysql:8.0

Description:

Hello,

I configured two Laravel applications using the sail up command, both have a laravel and mysql instance.

I configured these two applications to belong to the same network.

I can get them to make requests for each other without a problem.

What I would like help with is to make a foreign key reference in a migration. The foreign key must point to the id of a table that is in another database (within the same network).

I found information out there that tells me to do it as follows:

public function up()
    {
        Schema::create('sites', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->unsignedBigInteger('user_id');
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->unsignedBigInteger('place_id');
            $table->foreign('domain_id')->references('id')->on('second_application_db.domains');
            $table->timestamps();
        });
    }

However when I run the command:

sail artisan migrate

I received the following error message:

Illuminate\Database\QueryException 
SQLSTATE[42000]: Syntax error or access violation: 1142 REFERENCES command denied to user 'root'@'172.31.0.5' for table 'second_application_db.domains' (SQL: alter table `sites` add constraint `sites_domain_id_foreign` foreign key (`domain_id`) references `second_application_db`.`domains` (`id`))

at vendor/laravel/framework/src/Illuminate/Database/Connection.php:678
    674▕         // If an exception occurs when attempting to run a query, we'll format the error
    675▕         // message to include the bindings with SQL, which will make this exception a
    676▕         // lot more helpful to the developer instead of just the database's errors.
    677▕         catch (Exception $e) {
  ➜ 678▕             throw new QueryException(
    679▕                 $query, $this->prepareBindings($bindings), $e
    680▕             );
    681▕         }
    682▕ 

      +9 vendor frames 
  10  database/migrations/2021_01_30_121055_create_companies_table.php:29
      Illuminate\Support\Facades\Facade::__callStatic()

      +21 vendor frames 
  32  artisan:37
      Illuminate\Foundation\Console\Kernel::handle()

I would like to know how to set up a foreign key for a table belonging to another database, if anyone can help me I would be very grateful.

I tested it with two separate databases in a local environment as it is the migration and it worked, that is, the problem is happening when using Sail.

Here is the content of the .env file of the first application:

APP_NAME=Laravel
APP_ENV=local
APP_KEY=xxx
APP_DEBUG=true
APP_URL=local.first-application

APP_SERVICE=local.first-application
APP_PORT=580

LOG_CHANNEL=stack
LOG_LEVEL=debug

DB_CONNECTION=first-application-connection

DB_HOST=mysql-first-application
DB_PORT=3306
DB_DATABASE=first_application_db
DB_USERNAME=root
DB_PASSWORD=123456

SECOND_APPLICATION_DB_HOST=mysql-second-application
SECOND_APPLICATION_DB_PORT=3306
SECOND_APPLICATION_DB_DATABASE=second_application_db
SECOND_APPLICATION_DB_USERNAME=root
SECOND_APPLICATION_DB_PASSWORD=123456

BROADCAST_DRIVER=log
CACHE_DRIVER=file
QUEUE_CONNECTION=sync
SESSION_DRIVER=file
SESSION_LIFETIME=120

MEMCACHED_HOST=memcached

REDIS_HOST=redis
REDIS_PASSWORD=null
REDIS_PORT=6379

Here is the content of the database.php file of the first application:

<?php

use Illuminate\Support\Str;

return [
    'default' => env('DB_CONNECTION', 'mysql'),

    'connections' => [
        'first-application-connection' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

        'second-application-connection' => [
            'driver' => 'mysql',
            'url' => env('SECOND_APPLICATION_DATABASE_URL'),
            'host' => env('SECOND_APPLICATION_DB_HOST', '127.0.0.1'),
            'port' => env('SECOND_APPLICATION_DB_PORT', '3306'),
            'database' => env('SECOND_APPLICATION_DB_DATABASE', 'forge'),
            'username' => env('SECOND_APPLICATION_DB_USERNAME', 'forge'),
            'password' => env('SECOND_APPLICATION_DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ]
    ],

    'migrations' => 'migrations',

    'redis' => [

        'client' => env('REDIS_CLIENT', 'phpredis'),

        'options' => [
            'cluster' => env('REDIS_CLUSTER', 'redis'),
            'prefix' => env('REDIS_PREFIX', Str::slug(env('APP_NAME', 'laravel'), '_').'_database_'),
        ],

        'default' => [
            'url' => env('REDIS_URL'),
            'host' => env('REDIS_HOST', '127.0.0.1'),
            'password' => env('REDIS_PASSWORD', null),
            'port' => env('REDIS_PORT', '6379'),
            'database' => env('REDIS_DB', '0'),
        ],

        'cache' => [
            'url' => env('REDIS_URL'),
            'host' => env('REDIS_HOST', '127.0.0.1'),
            'password' => env('REDIS_PASSWORD', null),
            'port' => env('REDIS_PORT', '6379'),
            'database' => env('REDIS_CACHE_DB', '1'),
        ],

    ],

];

Follows the contents of the docker-compose.yml file for each application.

First application:

version: '3'
services:
    "local.first-application":
        build:
            context: ./vendor/laravel/sail/runtimes/8.0
            dockerfile: Dockerfile
            args:
                WWWGROUP: '${WWWGROUP}'
        image: sail-8.0/app
        ports:
            - '${APP_PORT:-80}:80'
        environment:
            WWWUSER: '${WWWUSER}'
            LARAVEL_SAIL: 1
        volumes:
            - '.:/var/www/html'
        networks:
            - application-network
        depends_on:
            - mysql-first-application
    mysql-first-application:
        image: 'mysql:8.0'
        ports:
            - '${FORWARD_DB_PORT:-3307}:3306'
        environment:
            MYSQL_ROOT_PASSWORD: '${DB_PASSWORD}'
            MYSQL_DATABASE: '${DB_DATABASE}'
            MYSQL_USER: '${DB_USERNAME}'
            MYSQL_PASSWORD: '${DB_PASSWORD}'
            MYSQL_ALLOW_EMPTY_PASSWORD: 'yes'
        volumes:
            - 'sailmysql:/var/lib/mysql'
        networks:
            - application-network
networks:
    application-network:
        external:
          name: application-network
volumes:
    sailmysql:
        driver: local
    sailredis:
        driver: local

Second application:

version: '3'
services:
    "local.second-application":
        build:
            context: ./vendor/laravel/sail/runtimes/8.0
            dockerfile: Dockerfile
            args:
                WWWGROUP: '${WWWGROUP}'
        image: sail-8.0/app
        ports:
            - '${APP_PORT:-80}:80'
        environment:
            WWWUSER: '${WWWUSER}'
            LARAVEL_SAIL: 1
        volumes:
            - '.:/var/www/html'
        networks:
            - application-network
        depends_on:
            - mysql-second-application
    mysql-second-application:
        image: 'mysql:8.0'
        ports:
            - '${FORWARD_DB_PORT:-3306}:3306'
        environment:
            MYSQL_ROOT_PASSWORD: '${DB_PASSWORD}'
            MYSQL_DATABASE: '${DB_DATABASE}'
            MYSQL_USER: '${DB_USERNAME}'
            MYSQL_PASSWORD: '${DB_PASSWORD}'
            MYSQL_ALLOW_EMPTY_PASSWORD: 'yes'
        volumes:
            - 'sailmysql:/var/lib/mysql'
        networks:
            - application-network
networks:
    application-network:
        external:
          name: application-network
volumes:
    sailmysql:
        driver: local
    sailredis:
        driver: local

Steps To Reproduce:

[paolocarrara]

Sorry, missed the steps to reproduce:

Basically to reproduce, you must start two Laravel applications using sail, previously you must create a network in the docker, place both applications on the same network.

You must create any table with an id in the second application (that is, in the database of the second application), then, in the first application you must create a migration with a foreign key pointing to the id created by the second application.

After the migration is created, trying to perform the migration with the command sail artisan migrate, instead of working and creating a foreign key for the id in the database table of the second application, an error occurs.

[driesvints]

Hi there,

Thanks for reporting but it looks like this is a question which can be asked on a support channel. Please only use this issue tracker for reporting bugs with the library itself. If you have a question on how to use functionality provided by this repo you can try one of the following channels:

• Laracasts Forums
• Laravel.io Forums
• StackOverflow
• Discord
• Larachat
• IRC

However, this issue will not be locked and everyone is still free to discuss solutions to your problem!

Thanks.

[paolocarrara]

Ok, I already reached those channels, for example https://stackoverflow.com/questions/28950070/is-it-possible-to-reference-a-foreign-key-in-a-different-database-in-laravel.

But given the fact that I did exactly what is needed for this functionality to work and even after that it did not work, I can only assume it is a bug.

I would like to reinforce that I managed to make it work following the stack overflow answer running with php artisan serve, but changing the configuration to run from sail makes the migration brake.

[rs-sliske]

looks like the "issue" here is that cross db fks only work when both dbs are on the same instance of mysql, but sail runs each app with its own independant instance of mysql

in general i would advise against having fk constraints to a different apps database, but if you do want to i expect you will need to manage the setup yourself