Dbal: findOneBy() throws 2 exceptions when used with MSODBCSQL17 and SQL Server 2008 R2

[J4VMC]

Bug Report

| Q | A
|------------ | ------
| BC Break | no
| Version | 2.7.1

Summary

Up-to-date Symfony 4 application that connects to a remote SQL Server 2008 R2 with MSODBCSQL17
and SQLSRV Doctrine DBAL driver in a Docker container running PHP-FPM 7.2 and Nginx.

Using Doctrine to persist entities and retrieve one more than one record works without problems, but the findOneBy() method throws 1 DBAL exception and 1 SQLSRV Exception.

Current behavior

When retrieving entities with the default findOneBy() method, Symfony throws the following:

SQLSTATE [42000, 102]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'OFFSET'.
SQLSTATE [42000, 153]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid usage of the option NEXT in the FETCH statement.
SQLSTATE [42000, 8180]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.

The stack trace for the DBALException:

Doctrine\DBAL\DBALException:
An exception occurred while executing 'SELECT t0.Temp_Transaction_id AS Temp_Transaction_id_1, t0.booking_code AS booking_code_2, t0.payment_date AS payment_date_3, t0.payment_value AS payment_value_4, t0.paid_as AS paid_as_5, t0.currency_code AS currency_code_6, t0.clerk_code AS clerk_code_7, t0.company_code AS company_code_8, t0.branch_code AS branch_code_9, t0.client_code AS client_code_10, t0.department_code AS department_code_11, t0.payment_reference AS payment_reference_12, t0.card_type AS card_type_13, t0.Is_Found AS Is_Found_14 FROM dbo.TEMP_PAYMENT_TRANSACTIONS t0 WHERE t0.Temp_Transaction_id = ? ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY' with params [112]:

SQLSTATE [42000, 102]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'OFFSET'.
SQLSTATE [42000, 153]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid usage of the option NEXT in the FETCH statement.
SQLSTATE [42000, 8180]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.

  at vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:187
  at Doctrine\DBAL\DBALException::wrapException(object(Driver), object(SQLSrvException), 'An exception occurred while executing \'SELECT t0.Temp_Transaction_id AS Temp_Transaction_id_1, t0.booking_code AS booking_code_2, t0.payment_date AS payment_date_3, t0.payment_value AS payment_value_4, t0.paid_as AS paid_as_5, t0.currency_code AS currency_code_6, t0.clerk_code AS clerk_code_7, t0.company_code AS company_code_8, t0.branch_code AS branch_code_9, t0.client_code AS client_code_10, t0.department_code AS department_code_11, t0.payment_reference AS payment_reference_12, t0.card_type AS card_type_13, t0.Is_Found AS Is_Found_14 FROM dbo.TEMP_PAYMENT_TRANSACTIONS t0 WHERE t0.Temp_Transaction_id = ? ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY\' with params [112]:SQLSTATE [42000, 102]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near \'OFFSET\'.SQLSTATE [42000, 153]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid usage of the option NEXT in the FETCH statement.SQLSTATE [42000, 8180]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.')
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:158)
  at Doctrine\DBAL\DBALException::driverExceptionDuringQuery(object(Driver), object(SQLSrvException), 'SELECT t0.Temp_Transaction_id AS Temp_Transaction_id_1, t0.booking_code AS booking_code_2, t0.payment_date AS payment_date_3, t0.payment_value AS payment_value_4, t0.paid_as AS paid_as_5, t0.currency_code AS currency_code_6, t0.clerk_code AS clerk_code_7, t0.company_code AS company_code_8, t0.branch_code AS branch_code_9, t0.client_code AS client_code_10, t0.department_code AS department_code_11, t0.payment_reference AS payment_reference_12, t0.card_type AS card_type_13, t0.Is_Found AS Is_Found_14 FROM dbo.TEMP_PAYMENT_TRANSACTIONS t0 WHERE t0.Temp_Transaction_id = ? ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY', array(112))
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:938)
  at Doctrine\DBAL\Connection->executeQuery('SELECT t0.Temp_Transaction_id AS Temp_Transaction_id_1, t0.booking_code AS booking_code_2, t0.payment_date AS payment_date_3, t0.payment_value AS payment_value_4, t0.paid_as AS paid_as_5, t0.currency_code AS currency_code_6, t0.clerk_code AS clerk_code_7, t0.company_code AS company_code_8, t0.branch_code AS branch_code_9, t0.client_code AS client_code_10, t0.department_code AS department_code_11, t0.payment_reference AS payment_reference_12, t0.card_type AS card_type_13, t0.Is_Found AS Is_Found_14 FROM dbo.TEMP_PAYMENT_TRANSACTIONS t0 WHERE t0.Temp_Transaction_id = ? ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY', array(112), array('integer'))
     (vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php:733)
  at Doctrine\ORM\Persisters\Entity\BasicEntityPersister->load(array('id' => 112), null, null, array(), null, 1, null)
     (vendor/doctrine/orm/lib/Doctrine/ORM/EntityRepository.php:196)
  at Doctrine\ORM\EntityRepository->findOneBy(array('id' => 112))
     (src/Controller/TransactionController.php:61)
  at App\Controller\TransactionController->importBacs(112, object(TempTransactionRepository), object(SqlQueriesService), object(TransactionImporterService))
     (vendor/symfony/http-kernel/HttpKernel.php:149)
  at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), 1)
     (vendor/symfony/http-kernel/HttpKernel.php:66)
  at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), 1, true)
     (vendor/symfony/http-kernel/Kernel.php:190)
  at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
     (public/index.php:37)

And the stack trace for SQLSRVException:

Doctrine\DBAL\Driver\SQLSrv\SQLSrvException:
SQLSTATE [42000, 102]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'OFFSET'.
SQLSTATE [42000, 153]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid usage of the option NEXT in the FETCH statement.
SQLSTATE [42000, 8180]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.

  at vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/SQLSrv/SQLSrvException.php:57
  at Doctrine\DBAL\Driver\SQLSrv\SQLSrvException::fromSqlSrvErrors()
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/SQLSrv/SQLSrvStatement.php:263)
  at Doctrine\DBAL\Driver\SQLSrv\SQLSrvStatement->execute()
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:930)
  at Doctrine\DBAL\Connection->executeQuery('SELECT t0.Temp_Transaction_id AS Temp_Transaction_id_1, t0.booking_code AS booking_code_2, t0.payment_date AS payment_date_3, t0.payment_value AS payment_value_4, t0.paid_as AS paid_as_5, t0.currency_code AS currency_code_6, t0.clerk_code AS clerk_code_7, t0.company_code AS company_code_8, t0.branch_code AS branch_code_9, t0.client_code AS client_code_10, t0.department_code AS department_code_11, t0.payment_reference AS payment_reference_12, t0.card_type AS card_type_13, t0.Is_Found AS Is_Found_14 FROM dbo.TEMP_PAYMENT_TRANSACTIONS t0 WHERE t0.Temp_Transaction_id = ? ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY', array(112), array('integer'))
     (vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php:733)
  at Doctrine\ORM\Persisters\Entity\BasicEntityPersister->load(array('id' => 112), null, null, array(), null, 1, null)
     (vendor/doctrine/orm/lib/Doctrine/ORM/EntityRepository.php:196)
  at Doctrine\ORM\EntityRepository->findOneBy(array('id' => 112))
     (src/Controller/TransactionController.php:61)
  at App\Controller\TransactionController->importBacs(112, object(TempTransactionRepository), object(SqlQueriesService), object(TransactionImporterService))
     (vendor/symfony/http-kernel/HttpKernel.php:149)
  at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), 1)
     (vendor/symfony/http-kernel/HttpKernel.php:66)
  at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), 1, true)
     (vendor/symfony/http-kernel/Kernel.php:190)
  at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
     (public/index.php:37)

How to reproduce

To reproduce this error consistently, I use $object = $entityRepository->findOneBy(['id' => $id]); and I get 2 exceptions.

The config/doctrine.yaml file looks like:

parameters:
    # Adds a fallback DATABASE_URL if the env var is not set.
    # This allows you to run cache:warmup even if your
    # environment variables are not available yet.
    # You should not need to change this value.
    env(DATABASE_URL): ''

doctrine:
    dbal:
        # configure these for your database server
        driver: 'sqlsrv'
        url: '%env(resolve:DATABASE_URL)%'
    orm:
        auto_generate_proxy_classes: '%kernel.debug%'
        naming_strategy: doctrine.orm.naming_strategy.underscore
        auto_mapping: true
        mappings:
            App:
                is_bundle: false
                type: annotation
                dir: '%kernel.project_dir%/src/Entity'
                prefix: 'App\Entity'
                alias: App

My .env file has the following contents:

# This file is a "template" of which env vars need to be defined for your application
# Copy this file to .env file for development, create environment variables when deploying to production
# https://symfony.com/doc/current/best_practices/configuration.html#infrastructure-related-configuration

###> symfony/framework-bundle ###
APP_ENV=dev
APP_SECRET=appsecretstringthathasbeenreplaced
#TRUSTED_PROXIES=127.0.0.1,127.0.0.2
#TRUSTED_HOSTS=localhost,example.com
###< symfony/framework-bundle ###

###> doctrine/doctrine-bundle ###
# Format described at http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/configuration.html#connecting-using-a-url
# For an SQLite database, use: "sqlite:///%kernel.project_dir%/var/data.db"
# Configure your db driver and server_version in config/packages/doctrine.yaml
DATABASE_URL=sqlsrv://User:[email protected]:1433/DBName?serverVersion=2008
###< doctrine/doctrine-bundle ###

Expected behavior

I would expect to be able to get the specific entity that matches the criteria used in findOneBy()

[morozov]

According to our implementation, the FETCH NEXT X ROWS ONLY syntax is only supposed to be used with SQL Server 2012 and newer.

Could you make sure you're not passing serverVersion=2012 or greater in your DATABASE_URL?

[J4VMC]

@morozov I copied and pasted an anonymised version of our DATABASE_URL. We don't make use of or mention Server 2012 anywhere in the code.

[Ocramius]

Can you dump the output of Doctrine\DBAL\Connection#getPlatform() please?

On Wed, 23 May 2018, 09:37 Javier, [e-mail] wrote:

@morozov https://github.com/morozov I copied and pasted an anonymised
version of our DATABASE_URL. We don't make use of or mention Server 2012
anywhere in the code.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/doctrine/dbal/issues/3159#issuecomment-391250463, or mute
the thread
https://github.com/notifications/unsubscribe-auth/AAJakOZlfG_X3NbAyr5w22TC3bFbmEn5ks5t1RG4gaJpZM4UIhFJ
.

[J4VMC]

@Ocramius shall I dump that in the controller on in the repo?

[Ocramius]

@J4VMC wherever you want: that will give you the selected platform, which is what @morozov is trying to figure out :-)

[J4VMC]

@Ocramius it is in fact using 2012 Platform

SQLServer2012Platform {#744 ▼
  #doctrineTypeMapping: null
  #doctrineTypeComments: null
  #_eventManager: ContainerAwareEventManager {#414 ▶}
  #_keywords: null
}

But I thought SQLServer2008Platform was the default one? Do I specify the platform on doctrine.yaml?

[J4VMC]

It's all sorted now, I've been able to get it to use SQLServer2008Platform.

Thanks @morozov and @Ocramius

[Ocramius]

@J4VMC can you clarify what had to be changed, so that other lost souls that are forced onto SQL Server 2008 can get some help here? :-)

[J4VMC]

So in my case, I had to change a few things to get it all sorted:

The DATABASE_URL now looks like

DATABASE_URL=sqlsrv://User:[email protected]:1433/DBName

(like any other else) instead of

DATABASE_URL=sqlsrv://User:[email protected]:1433/DBName?serverVersion=2008

Then after your post, I had a moment of enlightenment and I ran SELECT @@VERSION in an SQL console, which gave me:

Microsoft SQL Server 2008 (SP2) - 10.0.4067.0 (X64) 
    Jun 14 2012 19:26:05 
    Copyright (c) 1988-2008 Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

I modified config/doctrine.yaml to add server_version in the following fashion:

parameters:
    # Adds a fallback DATABASE_URL if the env var is not set.
    # This allows you to run cache:warmup even if your
    # environment variables are not available yet.
    # You should not need to change this value.
    env(DATABASE_URL): ''

doctrine:
    dbal:
        # configure these for your database server
        driver: 'sqlsrv'
        url: '%env(resolve:DATABASE_URL)%'
        server_version: 10.0.4067.0 #This should match the numeric version of the output of SELECT @@VERSION
    orm:
        auto_generate_proxy_classes: '%kernel.debug%'
        naming_strategy: doctrine.orm.naming_strategy.underscore
        auto_mapping: true
        mappings:
            App:
                is_bundle: false
                type: annotation
                dir: '%kernel.project_dir%/src/Entity'
                prefix: 'App\Entity'
                alias: App

I'm also using MSODBCSQL17 drivers from Microsoft within a docker-compose setup that has NGINX and PHP-FPM containers to connect to an SQL Server 2008 R2.

After that, Doctrine\DBAL\Connection#getPlatform() was showing SQLServer2008Platform and everything works as good as usual.

[Ocramius]

@J4VMC thanks for clarifying, top explanation :+1:

[J4VMC]

I'll write a blog post about it, because it's been a proper mission trying to get the app working as I wanted. I had many challenges, this issue being one of them.

[morozov]

@jwage would it make sense to update the documentation?

Two facts to be highlighted:

1. For the SQL Server, the version should be not the product name (2008, 2012, etc.) but the internal version (e.g. 10.0.4067.0). As far as I understand, the originally provided 2012 is greater than 11.00.2100, therefore, the SQLServer2012Platform was used for SQL Server 2008.
2. The version of the server could be obtained from the connection as:
   php echo $conn->getWrappedConnection()->getServerVersion(); // 5.7.22 (my current MySQL server)

@Ocramius as an advocate of type systems, could you think of a way to improve the current comparison logic?
https://github.com/doctrine/dbal/blob/9f3188ab3d56db6f6fc76c2ddf6b0b4aae06192f/lib/Doctrine/DBAL/Driver/AbstractSQLServerDriver.php#L64-L73

Obviously, 2008 is not comparable with either of the valid versions, it just happens to work in the current implementation.

[Ocramius]

@Ocramius as an advocate of type systems, could you think of a way to improve the current comparison logic?

I'd rather say that the SQL platform can be made a constructor dependency, with LazyPlatform as the current thing that is leading to so many headaches (unsure about dependency direction for now).