[Sql Server] Error migrating tables

[c4ndle]

Hello, thanks for sharing this library.
Sorry for a newbie question.
I am trying to use this library with Sql Server
but I receive this error message
-- Error migrating tables --

How to Reproduce:
Run SqlServer on docker
docker run --name sql1 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=!@#p455qwe' -p 11433:1433 -d mcr.microsoft.com/mssql/server:2017-latest

Install Database Migrations(CLI) on mac
composer require 'byjg/migration-cli=4.0.*'

Change test_sqlserver.php like this

<?php

require __DIR__ . "/../../vendor/autoload.php";

/**
 * Make sure you have a database with the user 'migrateuser' and password 'migratepwd'
 * 
 * This user need to have grant for DDL commands; 
 */

$uri = new \ByJG\Util\Uri('dblib://sa:!@#p455qwe@localhost:1433/MyDBName');

$migration = new \ByJG\DbMigration\Migration($uri, __DIR__);
$migration->registerDatabase('dblib', \ByJG\DbMigration\Database\DblibDatabase::class);

$migration->prepareEnvironment();

$migration->reset();

Run this command

~ $ vendor/bin/migrate create /Users/a/test 
Created UP version: 1
Created DOWN version: 0

~ $ vendor/bin/migrate install --path /Users/a/vendor/byjg/migration/example/sql_server mssql://localhost:1433/DragonRunner
-- Error migrating tables --

I can connect to sql using this
http://localhost:8080/indexSQL.php

<?php
$serverName = "172.18.0.4,1433"; 

$connectionInfo = array("DATABASE"=>"MyDBName","UID"=>"sa","PWD"=>"!@#p455qwe");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

Question 1:
Please tell step by step how to use this library with sql server,
what file need to change sql user and password and server address
and how to test the connection established.

Question 2:
How to find more details of this -- Error migrating tables --,
such as if the error is because the connection failed

Mac php version

$ php -v                                   
PHP 7.3.11 (cli) (built: Jun  5 2020 23:50:40) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.11, Copyright (c) 1998-2018 Zend Technologies

[byjg]

First of all, thank you :)

I have some considerations about your step by step:

1. When you run the sqlserver image the port you exposed was 11433 and not the default one 1433. It means you need to specify the port when you are calling the migrate command. Another point important: you need to pass the password to the connection string. And the last but not least the schema is not mssql, it is dblib. So based on your example the correct connection string will be:

dblib://sa:!@#p455qwe@localhost:11433/DragonRunner

2. To get a more detailed error message you can call the migrate with -v argument.

3. The command create only creates the directory structure. As you are running the example, you don't need to run the create first.

4. The command install is only necessary when you want add the migration to an existing database. Again, as you are running the example from scratch you just need call the reset

vendor/bin/migrate reset dblib://localhost:1433/DragonRunner

5. Another point I noted the php you stated isconnecting to the Sql Server has a different host than you ae using in the migrate connection string. While you are using in the migrate localhost the example connects to 172.18.0.4

6. The password with the exclamation point (!) has some issues when you are using on linux terminal . You can get the error message event not found. Take a look here. I recommend you to avoid use it.

7. The latest version is 4.1.0 and not 4.0.0. I recommend update also. Sorry, the documentation isn't update.

[c4ndle]

Hello, thank you for quick reply ^^

Now I've got this error

~ $ vendor/bin/migrate reset dblib://localhost:11433/DragonRunner -v 
This will ERASE all of data in your data. Continue with this action? (y/N) y
Connection String: dblib://localhost:11433/Runner
Path: /Users/a
-- Error migrating tables --
ByJG\AnyDataset\Core\Exception\NotAvailableException
Extension 'pdo_dblib' is not loaded

so I try to install pdo_dblib but it was deprecated.
I remember that I am using sqlsrv_connect extension in indexSQL.php
that's why it can connect

Does your library support sqlsrv_connect ?
https://docs.microsoft.com/en-us/sql/connect/php/installation-tutorial-linux-mac?view=sql-server-ver15

Edit: about the example connects to 172.18.0.4, it is because I am using bridge to connect to sql server from another php container (it was okay calling localhost)

[byjg]

Right now we don't have support for sqlsrv_connect, however it is something to think about. Let me analyse what can be done and I return to you soon.

[byjg]

The problem with the Microsoft Sql Server connector for PHP Linux is the driver is a wrapper for ODBC. It means you have an intermediate layer acting making the connection slower. The dblib driver connects using the databse protocol without intermediaries directly

Meanwhile, could you try to install the DBLIB on MAC following this tutorial? https://gist.github.com/jschroed91/8235909e16f79fbb31feff1f313aa9ca

As you are using Docker you can try also use our PHP docker image. It has all necessary modules you need. You can pull the image by docker pull byjg/php:7.3-cli and is available from PHP versions 5.6 to 8.0 and we have five image flavours: base (minimal), command line, fpm, fpm+nginx, fpm+apache. More info: https://opensource.byjg.com/devops/docker-php/

[c4ndle]

Thanks for the advice

Even after I install the DBLIB on Mac,
I think it won't work because of our side (not your library)
many of our API is already written using SQLSRV Functions
https://www.php.net/manual/en/book.sqlsrv.php

• Is okay if the connection become slower
  we already get used to using the syntax
• We prefer to choose the recommendation to use official extensions from Microsoft from here:
  https://github.com/Microsoft/msphpsql

We would appreciate it if you could make this library support Microsoft SQL Server Driver for PHP
until then we will wait.

[byjg]

All right. Let me think how I'll add it.

[byjg]

Hi @c4ndle , I implemented the Sqlsrv to be deployed in the next migration version. I'd like you test it for me before I create a new version.

Please update the migration temporarly to dev-next (e.g. composer require byjg/migration:dev-next) and let me know if it is working.

The connection string is:

sqlsrv://username:password@hostname:port/database

[c4ndle]

Hello, thank you for the update,

I did try run composer require byjg/migration:dev-next
But I have problem

composer require byjg/migration:dev-next
./composer.json has been updated
Running composer update byjg/migration
Loading composer repositories with package information
Updating dependencies
Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - Root composer.json requires byjg/migration dev-next -> satisfiable by byjg/migration[dev-next].
    - byjg/migration dev-next requires byjg/anydataset-db 4.1.2.x-dev -> found byjg/anydataset-db[4.1.2.x-dev] but it does not match your minimum-stability.

Use the option --with-all-dependencies (-W) to allow upgrades, downgrades and removals for packages currently locked to specific versions.

Installation failed, reverting ./composer.json and ./composer.lock to their original content.

Try using -W option

composer require byjg/migration:dev-next -W

./composer.json has been updated
Running composer update byjg/migration --with-all-dependencies
Loading composer repositories with package information
Updating dependencies
Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - Root composer.json requires byjg/migration dev-next -> satisfiable by byjg/migration[dev-next].
    - byjg/migration dev-next requires byjg/anydataset-db 4.1.2.x-dev -> found byjg/anydataset-db[4.1.2.x-dev] but it does not match your minimum-stability.

Use the option --with-all-dependencies (-W) to allow upgrades, downgrades and removals for packages currently locked to specific versions.

Installation failed, reverting ./composer.json and ./composer.lock to their original content.

Btw I did run composer require "byjg/migration":"4.1.*" before run composer require byjg/migration:dev-next it seems succeeded

composer require "byjg/migration":"4.1.*"
./composer.json has been created
Running composer update byjg/migration
Loading composer repositories with package information
Updating dependencies
Lock file operations: 9 installs, 0 updates, 0 removals
  - Locking byjg/anydataset (4.0.1)
  - Locking byjg/anydataset-array (4.0.0)
  - Locking byjg/anydataset-db (4.1.1)
  - Locking byjg/migration (4.1.0)
  - Locking byjg/serializer (1.0.7)
  - Locking byjg/uri (2.1.0)
  - Locking byjg/xmlutil (1.0.6)
  - Locking psr/cache (1.0.1)
  - Locking psr/http-message (1.0.1)
Writing lock file
Installing dependencies from lock file (including require-dev)
Package operations: 9 installs, 0 updates, 0 removals
  - Installing byjg/xmlutil (1.0.6): Extracting archive
  - Installing byjg/serializer (1.0.7): Extracting archive
  - Installing byjg/anydataset (4.0.1): Extracting archive
  - Installing byjg/anydataset-array (4.0.0): Extracting archive
  - Installing psr/cache (1.0.1): Extracting archive
  - Installing psr/http-message (1.0.1): Extracting archive
  - Installing byjg/uri (2.1.0): Extracting archive
  - Installing byjg/anydataset-db (4.1.1): Extracting archive
  - Installing byjg/migration (4.1.0): Extracting archive
2 package suggestions were added by new dependencies, use `composer suggest` to see details.
Generating autoload files

[byjg]

OK, use the following composer.json

{
    "require": {
        "byjg/migration": "dev-next"
    },
    "minimum-stability": "dev"
}

[byjg]

Did it work as expected?

[c4ndle]

Hello, sorry I can not test it soon (busy with client side development).
I will contact you again in 2 days,
btw, is it works on your local environment ?

[byjg]

No problem.

btw, is it works on your local environment ?

Yes, it is, but I would like just to make sure everything is OK.

[c4ndle]

updating dev-next after edit composer.json looks good

$ composer require byjg/migration:dev-next
./composer.json has been updated
Running composer update byjg/migration
Loading composer repositories with package information
Updating dependencies
Nothing to modify in lock file
Installing dependencies from lock file (including require-dev)
Nothing to install, update or remove
Generating autoload files

And then I have an exception command not found

migration-cli a$ scripts/migrate --path /Users/a/migration-tool/vendor/byjg/migration/example/sql_server  reset sqlsrv://sa:MssqlP455123@sql1:1433/testdb -v

In Application.php line 676:
                                                                                              
  [Symfony\Component\Console\Exception\CommandNotFoundException]                              
  Command "/Users/a/migration-tool/vendor/byjg/migration/example/sql_server" is not defined.  
                                                                                              

Exception trace:
  at /Users/a/migration-tool/migration-cli/vendor/symfony/console/Application.php:676
 Symfony\Component\Console\Application->find() at /Users/a/migration-tool/migration-cli/vendor/symfony/console/Application.php:237
 Symfony\Component\Console\Application->doRun() at /Users/a/migration-tool/migration-cli/vendor/symfony/console/Application.php:149
 Symfony\Component\Console\Application->run() at /Users/a/migration-tool/migration-cli/vendor/byjg/migration-cli/scripts/migrate:23

Do I have to update https://github.com/byjg/migration-cli too ?

using composer require 'byjg/migration-cli=4.0.*'

vendor/bin/migrate reset sqlsrv://sa:MssqlP455123@sql1:1433/testdb -v
This will ERASE all of data in your data. Continue with this action? (y/N) y
Connection String: sqlsrv://sa:MssqlP455123@sql1:1433/testdb
Path: /Users/a/migration-tool/migration-cli
-- Error migrating tables --
ByJG\DbMigration\Exception\DatabaseDoesNotRegistered
Scheme "sqlsrv" does not found. Did you registered it?

[c4ndle]

btw, is it works on your local environment ?

Yes, it is, but I would like just to make sure everything is OK.

Could you tell more specific how do you do that from the ground up @byjg

[byjg]

Hi, I am generating a new release version where I can have all tied togheter.

After merge PR #31 in a few minutes I'll send the proper instructions.

[byjg]

I bump all component versions, so you can use without any "hacks".

• If you are using the migration CLI - the component version is 4.1.0
• If you are using the migration as a PHP library - the component version is 4.2.0

e.g.:

composer require byjg/migration-cli=4.1.*

To use the sqlsrv_connect you need to use the schema sqlsrv as sqlsrv://sa:password@localhost/database

Let me know if everything is working.

[c4ndle]

Finally it works using this command

vendor/bin/migrate reset --path /Users/a/migration-tool/vendor/byjg/migration-cli/example/sql_server sqlsrv://sa:MssqlP455123@localhost:1433/testdb

2021-01-18 06:09:22.44 spid56      Starting up database 'testdb'.
2021-01-18 06:09:22.64 spid56      Parallel redo is started for database 'testdb' with worker pool size [3].
2021-01-18 06:09:22.65 spid56      Parallel redo is shutdown for database 'testdb' with worker pool size [3].

If using API, I have an error

Fatal error: Uncaught PDOException: SQLSTATE[HYT00]: [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired in /code/vendor/byjg/anydataset-db/src/PdoSqlsrv.php:28 Stack trace: #0 /code/vendor/byjg/anydataset-db/src/PdoSqlsrv.php(28): PDO->__construct('sqlsrv:server=0...', 'sa', 'MssqlP455123') #1 /code/vendor/byjg/anydataset-db/src/Factory.php(51): ByJG\AnyDataset\Db\PdoSqlsrv->__construct(Object(ByJG\Util\Uri)) #2 /code/vendor/byjg/anydataset-db/src/Factory.php(16): ByJG\AnyDataset\Db\Factory::getDbInstance(Object(ByJG\Util\Uri), NULL) #3 /code/vendor/byjg/migration/src/Database/DblibDatabase.php(18): ByJG\AnyDataset\Db\Factory::getDbRelationalInstance('sqlsrv://sa:Mss...') #4 /code/vendor/byjg/migration/src/Migration.php(211): ByJG\DbMigration\Database\DblibDatabase::prepareEnvironment(Object(ByJG\Util\Uri)) #5 /code/vendor/byjg/migration/example/sql_server/test_sqlserver.php(16): ByJG\DbMigration\Migration->prepareEnvironment() #6 {main} thrown in /code/vendor/byjg/anydataset-db/src/PdoSqlsrv.php on line 28

Then I change localhost to sql1(container name) because both php and
sql server each in the different container but they're both linked.
I don't know why using localhost doesn't works inside docker, maybe you have clue ?
but this works
$uri = new \ByJG\Util\Uri('sqlsrv://sa:MssqlP455123@sql1/testdb');

Thanks a lot

[byjg]

I am glad to know that the solution is working.

About why localhost is not working inside the docker container is something expected because of the containerization technology. Basically, when a container is runnning the containerization technology isolates the container from the host machine. It means the container cannot share network, folders, services with the host unless explicitly stated. When you run the container with the --port or --volume we are saying to map container port or volume with the host. Inside the container the localhost is the container itself, and it explain why it works when you changed from localhost to sql1.