Admin Panel Not Loading When Using Two Different Databases one for aimeos and other for laravel base database.

[siddharthghedia]

The admin panel fails to load when configuring Aimeos to use two different databases. When trying to open admin routes URL then gives an unauthorised access error.

I tried the following steps:

1. Set up and install Aimeos in existing Laravel project.
2. Configure two different database connections in the config/database.php file.

// for Aimeos database

'mysql_aimeos' => [
            'driver' => 'mysql',
            'host' => env('DB_AIMEOS_HOST', '127.0.0.1'),
            'port' => env('DB_AIMEOS_PORT', '3306'),
            'database' => env('DB_AIMEOS_DATABASE', ''),
            'username' => env('DB_AIMEOS_USERNAME', ''),
            'password' => env('DB_AIMEOS_PASSWORD', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'stmt'                => ["SET SESSION sort_buffer_size=2097144; SET NAMES 'utf8mb4'; SET SESSION sql_mode='ANSI'"],
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => [
                PDO::ATTR_EMULATE_PREPARES => true,
            ],
        ],

// for laravel database

'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', ''),
            'username' => env('DB_USERNAME', ''),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8',
            'stmt'    => ["SET SESSION sort_buffer_size=2097144; SET NAMES 'utf8mb4'; SET SESSION sql_mode='ANSI'"],
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => false,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

3. Assign one database connection for the application and another for Aimeos.
4. Configure admin route to config/shop.php file.

'admin' => ['prefix' => 'store-admin', 'middleware' => ['web']],
'jqadm' => ['prefix' => 'store-admin/{site}/jqadm', 'middleware' => ['web']],

// for aimeos database

'db' => [
            'adapter' => config('database.connections.'.config('database.default', 'mysql').'.driver', 'mysql'),
            'host' => config('DB_AIMEOS_HOST', 'localhost'),
            'port' => config('DB_AIMEOS_PORT', '3306'),
            'socket' => config('database.connections.'.config('database.default', 'mysql').'.unix_socket', ''),
            'database' => config('DB_AIMEOS_DATABASE', ''),
            'username' => config('DB_AIMEOS_USERNAME', ''),
            'password' => config('DB_AIMEOS_PASSWORD', ''),
            'stmt' => config('database.default', 'mysql') === 'mysql' ? ["SET SESSION sort_buffer_size=2097144; SET NAMES 'utf8'; SET SESSION sql_mode='ANSI'"] : [],
            'limit' => 3, // maximum number of concurrent database connections
            'defaultTableOptions' => [
                'charset' => 'utf8',
                'collate' => 'utf8_unicode_ci',
            ],
            'driverOptions' => config('database.connections.'.config('database.default', 'mysql').'.options'),
        ],

// for Laravel database

'db-customer' => [
            'adapter' => 'mysql',
            'host' => '127.0.0.1',
            'port' => '3306',
            'database' => env('DB_DATABASE', ''),
            'username' => env('DB_USERNAME', ''),
            'password' => env('DB_PASSWORD', ''),
            'stmt' => ["SET SESSION sort_buffer_size=2097144; SET NAMES 'utf8'; SET SESSION sql_mode='ANSI'; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED"],
            'limit' => 2,
            'opt-persistent' => 0,
            'defaultTableOptions' => [
                'charset' => 'utf8',
                'collate' => 'utf8_unicode_ci',
            ],
        ],

6. Run the following command after configuring database.

php artisan vendor:publish --tag=config --tag=public
php artisan migrate
php artisan aimeos:setup --option=setup/default/demo:1

7. Then created a super user

php artisan aimeos:account --super <email>

8. Attempt to access the Aimeos admin panel.

Additional context

• Laravel version: ^9.26
• Aimeos version: ^2023.10
• PHP version: 8.0

After, above steps I am able to run Aimeos frontend and login. Also, display logged-in user details in frontend profile page but when I try to open admin routes then It says unauthorised to access the page.

I think actually the problem with role because of using different database. Aimeos table don't have users table. I also followed below steps but couldn't make it work.
https://github.com/aimeos/aimeos-laravel/issues/267

[aimeos]

This won't work because it must be env('DB_AIMEOS_...'):

            'host' => config('DB_AIMEOS_HOST', 'localhost'),
            'port' => config('DB_AIMEOS_PORT', '3306'),
            'database' => config('DB_AIMEOS_DATABASE', ''),
            'username' => config('DB_AIMEOS_USERNAME', ''),
            'password' => config('DB_AIMEOS_PASSWORD', ''),

[aimeos]

Works without problem when using this configuration in ./config/shop.php in combination with your ./config/database.php and .env settings:

'db' => [
	'adapter' => config( 'database.connections.' . config( 'database.default', 'mysql' ) . '.driver', 'mysql' ),
	'host' => config( 'database.connections.' . config( 'database.default', 'mysql' ) . '.host', '127.0.0.1' ),
	'port' => config( 'database.connections.' . config( 'database.default', 'mysql' ) . '.port', '3306' ),
	'socket' => config( 'database.connections.' . config( 'database.default', 'mysql' ) . '.unix_socket', '' ),
	'database' => config( 'database.connections.' . config( 'database.default', 'mysql' ) . '.database', 'forge' ),
	'username' => config( 'database.connections.' . config( 'database.default', 'mysql' ) . '.username', 'forge' ),
	'password' => config( 'database.connections.' . config( 'database.default', 'mysql' ) . '.password', '' ),
	'stmt' => config( 'database.default', 'mysql' ) === 'mysql' ? ["SET SESSION sort_buffer_size=2097144; SET NAMES 'utf8mb4'; SET SESSION sql_mode='ANSI'"] : [],
	'limit' => 3, // maximum number of concurrent database connections
	'defaultTableOptions' => [
		'charset' => config( 'database.connections.' . config( 'database.default', 'mysql' ) . '.charset' ),
		'collate' => config( 'database.connections.' . config( 'database.default', 'mysql' ) . '.collation' ),
	],
	'driverOptions' => config( 'database.connections.' . config( 'database.default', 'mysql' ) . '.options' ),
],
'db-customer' => [
	'adapter' => config( 'database.connections.' . config( 'database.default', 'mysql_aimeos' ) . '.driver', 'mysql' ),
	'host' => config( 'database.connections.' . config( 'database.default', 'mysql_aimeos' ) . '.host', '127.0.0.1' ),
	'port' => config( 'database.connections.' . config( 'database.default', 'mysql_aimeos' ) . '.port', '3306' ),
	'socket' => config( 'database.connections.' . config( 'database.default', 'mysql_aimeos' ) . '.unix_socket', '' ),
	'database' => config( 'database.connections.' . config( 'database.default', 'mysql_aimeos' ) . '.database', 'forge' ),
	'username' => config( 'database.connections.' . config( 'database.default', 'mysql_aimeos' ) . '.username', 'forge' ),
	'password' => config( 'database.connections.' . config( 'database.default', 'mysql_aimeos' ) . '.password', '' ),
	'stmt' => config( 'database.default', 'mysql_aimeos' ) === 'mysql' ? ["SET SESSION sort_buffer_size=2097144; SET NAMES 'utf8mb4'; SET SESSION sql_mode='ANSI'"] : [],
	'limit' => 3, // maximum number of concurrent database connections
	'defaultTableOptions' => [
		'charset' => config( 'database.connections.' . config( 'database.default', 'mysql_aimeos' ) . '.charset' ),
		'collate' => config( 'database.connections.' . config( 'database.default', 'mysql_aimeos' ) . '.collation' ),
	],
	'driverOptions' => config( 'database.connections.' . config( 'database.default', 'mysql_aimeos' ) . '.options' ),
],

[siddharthghedia]

@aimeos , I have added db-customer and db in ./config/database.php and .env settings. Now, I am able to run admin panel of aimeos successfully. But at a time of fresh migration I encounter with foreign key errors in aimeos mshop_customer table with project base table.

After configuring all of the above setting, I run php artisan migrate command which run successfully and migrate all base database tables which contains some of the aimeos tables such as mshop_customer, mshop_customer_address etc.

then I run php artisan aimeos:setup --option=setup/default/demo:1 command which gives below error.

I need to run aimeos table in seperate database and main project database should be diffferent.

[aimeos]

This will happen if you run artisan aimeos:setup before artisan migrate

[siddharthghedia]

@aimeos, No, I have executed artisan migrate and then run artisan aimeos:setup

Screen.Recording.2024-06-21.at.10.44.07.AM.mov

[aimeos]

One of your migrations loads a complete database schema which causes the issue

[jasperf]

What would you recommend here? That we generate migrations based on database schema? We pruned migrations and generated a dumb as a base to clean things up and allow for easier base app setup so would prefer to keep this.

We did try to generate migrations with a package to see if we can get passed this error . See kitloong/laravel-migrations-generator#221 but now we are running into collation issues..

[aimeos]

For development, it might be possible to import database dumps but only if it's the first migration that is executed. The dumps need to contain the two different databases (Laravel and Aimeos) and the setup must be exactly the same in all dev environments.

[jasperf]

We are trying to work with migrations pre pruning now. Thanks for the suggestions. I am sure @siddharthghedia appreciates them too.