Migration table not found error in SQL Server despite existence of migrations table when running migrate command

[kou-tech]

Laravel Version

11.1.0

PHP Version

8.2.17

Database Driver & Version

SQL Server 2022

Description

I am in the process of upgrading Laravel from version 10 to 11 for a project that uses SQLServer as the database. In Laravel 10, after executing php artisan migrate command, the migrations table is successfully created, and running php artisan migrate:status yields the expected output.

However, after upgrading to Laravel 11, while the php artisan migrate command still creates the migrations table as expected, running php artisan migrate:status results in an error message stating ERROR Migration table not found.
This issue arises despite the fact that the migrations table does indeed exist in the SQLServer database, as confirmed through manual inspection.

This behavior is inconsistent with the expected outcome and differs from the results in Laravel 10 under the same conditions. It suggests there might be an issue with how the migration status command interacts with the SQLServer database in Laravel 11, specifically in recognizing the existence of the migrations table.

I am looking for guidance on resolving this error and understanding whether this is a known issue with a workaround or a newly introduced bug in Laravel 11.

Steps To Reproduce

1. Create a fresh Laravel 11 project and configure it to use SQLServer.
2. Execute php artisan migrate to initialize the database schemas, including the creation of the migrations table in your SQLServer database.
3. Immediately following the successful migration, run php artisan migrate:status to assess the migration status. Despite the migrations table clearly existing in the SQLServer database, this command unexpectedly results in an error stating ERROR Migration table not found.

[github-actions]

Thank you for reporting this issue!

As Laravel is an open source project, we rely on the community to help us diagnose and fix issues as it is not possible to research and fix every issue reported to us via GitHub.

If possible, please make a pull request fixing the issue you have described, along with corresponding tests. All pull requests are promptly reviewed by the Laravel team.

Thank you!

[staudenmeir]

Sorry, I could only reproduce it once but not anymore.

Please put dd(\Schema::getTables()); into a route/controller and share the (expanded) result.

[kou-tech]

Thank you for your response!
I have attached the image showing the expanded result.
Please note that the presentation might be somewhat basic due to the omission of production code for privacy reasons.

[staudenmeir]

Your schema is not actually called sample, right? But you are using a custom one (i.e. not dbo)?

[hafezdivandari]

And what is the value of config('database.migrations')?

[kou-tech]

@staudenmeir
You are correct. In our project, we manage multiple schemas within a single SQL Server database, hence we are utilizing custom schemas. "sample" is indeed one such example.

[staudenmeir]

How did you make the custom schema work with Laravel (since there is no config option for it)? Did you set it as the default schema for the database user?

[hafezdivandari]

As @staudenmeir said, there is no config option for setting default schema when using SQL Server connection. So it's hardcoded to dbo unless you explicitly pass the custom schema name. So on your application's config/database.php file, you may change the migrations option as follow and let us know if it solves your issue:

'migrations' => 'sample.migrations', // as your default schema is `sample`

Or using new Laravel 11 options:

'migrations' => [
    'table' => 'sample.migrations', // as your default schema is `sample`
    'update_date_on_publish' => true,
],

[kou-tech]

@staudenmeir @hafezdivandari

Thank you for your response!
Indeed, by changing the default schema, it has been working even without a configuration option in previous versions of Laravel.

I've confirmed that adding the following code works without any issues!

'migrations' => [
    'table' => 'sample.migrations',
],

Is there any plan to support the traditional behavior in the future?

[hafezdivandari]

@Hikaru-Giannis It's a new feature on Laravel 11 that you can use custom schema name on almost all Schema methods, you may check PR #50019 and #49965.

Is there any plan to support the traditional behavior in the future?

Just sent PR #50855 to also support "traditional behavior".

[kou-tech]

@hafezdivandari
Thank you for your support! I will also check the PRs you mentioned.

How did you make the custom schema work with Laravel (since there is no config option for it)? Did you set it as the default schema for the database user?

My apologies for the oversight. In previous versions of Laravel, I was able to use a custom schema by executing SQL statements like the following:

CREATE LOGIN [sample] WITH PASSWORD = 'sample';
CREATE SCHEMA [sample];
CREATE USER [sample] FOR LOGIN [sample];
ALTER USER [sample] WITH DEFAULT_SCHEMA = [sample];
GRANT CREATE TABLE TO [sample];
GRANT SELECT ON SCHEMA::role TO [sample];
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER ON SCHEMA::sample TO [sample];
GRANT CREATE TABLE TO [sample];
GRANT ALTER ON SCHEMA::sample TO [sample];

Does this align with your intended answer?

[gmxtrra]

Sorry, I could only reproduce it once but not anymore.

Please put dd(\Schema::getTables()); into a route/controller and share the (expanded) result.

Can you please add some details ?