Error when executing first migration against SQLServer

[kabeza]

Hi
I've executed php spark migrate -all just after composer install, and got the following error
My main database is SQLServer

CodeIgniter v4.1.9 Command Line Tool - Server Time: 2022-04-19 09:30:38 UTC-03:00
Running all new migrations...
[Exception]
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]La clave externa 'auth_tokens_user_id_foreign' hace referencia a la tabla no válida 'users'.
at SYSTEMPATH/Database/SQLSRV/Connection.php:459

Translation:
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The foreign key 'auth_tokens_user_id_foreign' references invalid table 'users'.

Thanks a lot for any advise

[manageruz]

Hi kabeza. What is your database engine? And show the result of php spark migrate:status command.

[kabeza]

Hi kabeza. What is your database engine? And show the result of php spark migrate:status command.

SQL Server Express 2017

[manageruz]

Try to do this:

1. php spark migrate:rollback
2. php spark migrate -n 'App'

Is the error still present?

[kabeza]

Try to do this:

1. php spark migrate:rollback
2. php spark migrate -n 'App'

Is the error still present?

I've executed those 2 commands and this is the output

CodeIgniter v4.1.9 Command Line Tool - Server Time: 2022-05-17 12:07:00 UTC-03:00

Rolling back migrations to batch:  -1
Done rolling back migrations.

Running all new migrations...

[Exception]

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The foreign key 'auth_tokens_user_id_foreign' refers to non-valid 'users' table.

at SYSTEMPATH/Database/SQLSRV/Connection.php:459

Backtrace:
  1    SYSTEMPATH/Database/BaseConnection.php:670
       CodeIgniter\Database\SQLSRV\Connection()->execute()

  2    SYSTEMPATH/Database/BaseConnection.php:598
       CodeIgniter\Database\BaseConnection()->simpleQuery()

  3    SYSTEMPATH/Database/Forge.php:513
       CodeIgniter\Database\BaseConnection()->query()

  4    APPPATH/Database/Migrations/2021-12-14-000000_create_auth_tables.php:76
       CodeIgniter\Database\Forge()->createTable()

  5    SYSTEMPATH/Database/MigrationRunner.php:863
       CI4\Auth\Database\Migrations\CreateAuthTables()->up()

  6    SYSTEMPATH/Database/MigrationRunner.php:186
       CodeIgniter\Database\MigrationRunner()->migrate()

  7    SYSTEMPATH/Commands/Database/Migrate.php:84
       CodeIgniter\Database\MigrationRunner()->latest()

  8    SYSTEMPATH/CLI/Commands.php:63
       CodeIgniter\Commands\Database\Migrate()->run()

  9    SYSTEMPATH/CLI/CommandRunner.php:70
       CodeIgniter\CLI\Commands()->run()

 10    SYSTEMPATH/CLI/CommandRunner.php:56
       CodeIgniter\CLI\CommandRunner()->index()

 11    SYSTEMPATH/CodeIgniter.php:830
       CodeIgniter\CLI\CommandRunner()->_remap()

 12    SYSTEMPATH/CodeIgniter.php:419
       CodeIgniter\CodeIgniter()->runController()

 13    SYSTEMPATH/CodeIgniter.php:326
       CodeIgniter\CodeIgniter()->handleRequest()

 14    SYSTEMPATH/CLI/Console.php:48
       CodeIgniter\CodeIgniter()->run()

 15    ROOTPATH/spark:63
       CodeIgniter\CLI\Console()->run()

[kovijanny]

Hi, This is indeed a problem, but only with php 8 and higher releases. On php 7.4 everything works fine. Enrique Becerra ***@***.***> ezt írta (időpont: 2022. máj. 17., K, 17:09):

…

Try to do this: 1. php spark migrate:rollback 2. php spark migrate -n 'App' Is the error still present? I've executed those 2 commands and this is the output CodeIgniter v4.1.9 Command Line Tool - Server Time: 2022-05-17 12:07:00 UTC-03:00 Rolling back migrations to batch: -1 Done rolling back migrations. Running all new migrations... [Exception] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The foreign key 'auth_tokens_user_id_foreign' refers to non-valid 'users' table. at SYSTEMPATH/Database/SQLSRV/Connection.php:459 Backtrace: 1 SYSTEMPATH/Database/BaseConnection.php:670 CodeIgniter\Database\SQLSRV\Connection()->execute() 2 SYSTEMPATH/Database/BaseConnection.php:598 CodeIgniter\Database\BaseConnection()->simpleQuery() 3 SYSTEMPATH/Database/Forge.php:513 CodeIgniter\Database\BaseConnection()->query() 4 APPPATH/Database/Migrations/2021-12-14-000000_create_auth_tables.php:76 CodeIgniter\Database\Forge()->createTable() 5 SYSTEMPATH/Database/MigrationRunner.php:863 CI4\Auth\Database\Migrations\CreateAuthTables()->up() 6 SYSTEMPATH/Database/MigrationRunner.php:186 CodeIgniter\Database\MigrationRunner()->migrate() 7 SYSTEMPATH/Commands/Database/Migrate.php:84 CodeIgniter\Database\MigrationRunner()->latest() 8 SYSTEMPATH/CLI/Commands.php:63 CodeIgniter\Commands\Database\Migrate()->run() 9 SYSTEMPATH/CLI/CommandRunner.php:70 CodeIgniter\CLI\Commands()->run() 10 SYSTEMPATH/CLI/CommandRunner.php:56 CodeIgniter\CLI\CommandRunner()->index() 11 SYSTEMPATH/CodeIgniter.php:830 CodeIgniter\CLI\CommandRunner()->_remap() 12 SYSTEMPATH/CodeIgniter.php:419 CodeIgniter\CodeIgniter()->runController() 13 SYSTEMPATH/CodeIgniter.php:326 CodeIgniter\CodeIgniter()->handleRequest() 14 SYSTEMPATH/CLI/Console.php:48 CodeIgniter\CodeIgniter()->run() 15 ROOTPATH/spark:63 CodeIgniter\CLI\Console()->run() — Reply to this email directly, view it on GitHub <#512 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA2CBDDV6XECQRCVH75W4FDVKOZBFANCNFSM5TYW66TA> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

-- Üdvözlettel: Kovács János

[manageruz]

Hi Kabeza.
Try to find respective dll for the Microsoft Driver for PHP for SQL for your installed php version, copy it to your php ext folder, add to extensions. Then try again with php spark migrate -all

[kabeza]

Hi, This is indeed a problem, but only with php 8 and higher releases. On php 7.4 everything works fine.

I have php 7.4 @kovijanny :)

Hi Kabeza. Try to find respective dll for the Microsoft Driver for PHP for SQL for your installed php version, copy it to your php ext folder, add to extensions. Then try again with php spark migrate -all

I've already setup SQLServer extensions for php (php and php-cli. There're no dll files because I'm on Ubuntu Linux)
If there were any error on SQLServer extensions, I would not get this string on the Exception: "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]"