"Incorrect syntax near 'migrations'. " error when migrating with SQL Server 2017 on Laravel in Docker

Question

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!

score 0 · Answer 1 · answered Jul 31 '19 at 10:12

I've upgraded the Docker image from Debian Jessie to Debian Buster, and somehow that seemed to solve the problem.

In the Docker file for the web server, all I needed to change was this:

FROM php:7.1.30-apache-jessie

Into this:

FROM php:7.1.30-apache-buster

And the error simply went away. However that still doesn't explain what caused it of course.

score 0 · Answer 2 · answered Nov 24 '21 at 14:54

I came here hoping to find a fix (long tail Google search)

The fix for me was ensure the ODBC drivers were absolutely up to the latest to get connections working at all, and then a permissions issue where I had to go to the SQL Server (2017) admin opened up create permission, which had been left off for some reason.

I'm on Laravel 8.12 and PHP 7.4.3 and Ubuntu 20.04

The docker change answer above most likely fixed an ODBC driver issue.

Adding this answer here, because I couldn't find anything similar anywhere else and it might help someone on Linux ODBC and SQL Server.

"Incorrect syntax near 'migrations'. " error when migrating with SQL Server 2017 on Laravel in Docker

2 Answers2