I'm trying to set up a Jenkins test environment for my Laravel app which has an SQL Server for its database. For this I'm using two Docker containers:
-
A web server for the Laravel app
- Apache & PHP 7.1.30
- Debian 8 Jessie
-
The SQL Server 2017 Linux docker container from Microsoft
- Tag: 2017-latest-ubuntu
So far I've been able to set them both up on Docker and managed to connect them using Docker Compose. However, whenever I try to run any migrations on it Laravel gives me the following SQL Error:
[FreeTDS][SQL Server]Incorrect syntax near 'migrations'. (SQLExecute[102] at /usr/src/php/ext/pdo_odbc/odbc_stmt.c:260) (SQL: create table "migrations" ("id" int identity primary key not null, "migration" nvarchar(255) not null, "batch" int not null))
My web server uses the latest version of FreeTDS (1.1.11) and ODBC to connect to the Database. The connection seems to work since it's only giving a syntax error, but it's unclear why it's not allowing to build the migrations table. Furthermore, running the exact same query on the SQL Server itself seems to work just fine.
The only possible cause I could think of is the SQL Server version being too high (2017, our production environment is still on 2012). Microsoft doesn't offer any Docker images for earlier versions of the database. However that still doesn't explain why the same query does work when running it directly in the database.
Here's the error in full:
Illuminate\Database\QueryException : SQLSTATE[42000]: Syntax error or access violation: 102 [FreeTDS][SQL Server]Incorrect syntax near 'migrations'. (SQLExecute[102] at /usr/src/php/ext/pdo_odbc/odbc_stmt.c:260) (SQL: create table "migrations" ("id" int identity primary key not null, "migration" nvarchar(255) not null, "batch" int not null))
at /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
660| // If an exception occurs when attempting to run a query, we'll format the error
661| // message to include the bindings with SQL, which will make this exception a
662| // lot more helpful to the developer instead of just the database's errors.
663| catch (Exception $e) {
> 664| throw new QueryException(
665| $query, $this->prepareBindings($bindings), $e
666| );
667| }
668|
Exception trace:
1 Doctrine\DBAL\Driver\PDOException::("SQLSTATE[42000]: Syntax error or access violation: 102 [FreeTDS][SQL Server]Incorrect syntax near 'migrations'. (SQLExecute[102] at /usr/src/php/ext/pdo_odbc/odbc_stmt.c:260)")
/var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:119
2 PDOException::("SQLSTATE[42000]: Syntax error or access violation: 102 [FreeTDS][SQL Server]Incorrect syntax near 'migrations'. (SQLExecute[102] at /usr/src/php/ext/pdo_odbc/odbc_stmt.c:260)")
/var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117
This is how the connection looks like in config/database.php:
'owreg_cw' => [
'driver' => 'sqlsrv',
'odbc' => true,
'odbc_datasource_name' => 'DRIVER=FreeTDS;SERVERNAME=' . env('DB_HOST') . ';DATABASE=' . env('DB_DATABASE'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'prefix' => '',
],
The connections are saved in /etc/freetds/freetds.conf:
[global]
# TDS protocol version
tds version = 7.4
client charset = UTF-8
use ntlmv2 = yes
encryption = require
(...)
[LOCAL]
host = docker.sql-server.url.com
port = 1433
database = TEST_DATABASE
Let me know if you guys require anything else from me. Thanks!
