DBAL-1068: Microsoft SQL Server issues with ANSI_NULLS=OFF

[doctrinebot]

Jira issue originally created by user it.manfred:

So here what I have (see my Environment for more details)

$modules = array(
    'Application', //skeleton
    'DoctrineModule',
    'DoctrineORMModule',
    'DoctrineDataFixtureModule',
    'ZfcBase',
    'ZfcUser',
    'ZfcUserDoctrineORM',
    'BjyAuthorize',
    'User', // Entity copied from BjyAuthorize vendor folder
);

//module/User/Entity/User.php (copied from vendor\bjyoungblood\bjy-authorize\data\User.php.dist)
...
    /****
     * @var string
     * @ORM\Column(type="string", length=255, unique=true, nullable=true)
     */
    protected $username;
...

./vendor/doctrine/doctrine-module/bin/doctrine-module orm:schema-tool:create --dump-sql
CREATE TABLE [users] (id INT IDENTITY NOT NULL, username NVARCHAR(255), email NVARCHAR(255) NOT NULL, displayName NVARCHAR(50), password NVARCHAR(128) NOT NULL, state INT NOT NULL, PRIMARY KEY (id));
CREATE UNIQUE INDEX UNIQ_1483A5E9F85E0677 ON [users] (username) WHERE username IS NOT NULL;
...
and so on...

on create

{quote} [Doctrine\ORM\Tools\ToolsException] Schema-Tool failed with Error 'An exception occurred while executing 'CREATE UNIQUE INDEX UNIQ_1483A5E9F85E0677 ON users WHERE username IS NOT NULL': SQLSTATE[HY000]: General error: 20018 Cannot create index. Object 'users' was created with the following SET options off: 'ANSI_NULLS'. [20018](severity 16) [CREATE UNIQUE INDEX UNIQ_1483A5E9F85E0677 ON users WHERE username IS NOT NULL]' while executing DDL: CREATE UNIQUE INDEX UNIQ_1483A5E9F85E06 77 ON users WHERE username IS NOT NULL .... {quote}

Maybe this helps:

My database created in MSSQL and has default option ANSI_NULLS = OFF

see my options (bug1.png) !bug1.png|thumbnail!

ok, changed it to ANSI_NULLS = ON according to documentation (but for now this sets OFF by default on create database)

got a new exception

{quote} [Doctrine\ORM\Tools\ToolsException] Schema-Tool failed with Error 'An exception occurred while executing 'CREATE UNIQUE INDEX UNIQ_1483A5E9F85E0677 ON users WHERE username IS NOT NULL': SQLSTATE[HY000]: General error: 20018 CREATE INDEX failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query noti fications and/or XML data type methods and/or spatial index operations. [20018](severity 16) [CREATE UNIQUE INDEX UNIQ_1483A5E9F85E0677 ON users WHERE username IS NOT NULL]' while executing DDL: CREATE UNIQUE INDEX UNIQ_1483A5E9F85E0677 ON users WHERE username IS NOT NULL {quote}

Then I copied to Microsoft SQL Management Studio code above. No error!

Then I chcked off this checkbox in SQL Management Studio (see bug2.png) !bug2.png|thumbnail! and SQL query now failed with almost the same error, but now in Management Studio!

{quote} Msg 1934, Level 16, State 1, Line 2 CREATE INDEX failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. {quote}

So it seems that problem starts when we set for username unique=true and using MSSQL that trying to create UNIQUE INDEX ... WHERE fieled IS NOT NULL

Some related topics: ANSI_NULLS CONCAT_NULL_YIELDS_NULL ANSI_WARNINGS ANSI_PADDING Creating a unique constraint that ignores nulls in SQL Server

Going to move my project to MySQL if this bug are not my fault :D

[doctrinebot]

Comment created by @deeky666:

This is nothing we can control in Doctrine. According to the documentation http://msdn.microsoft.com/en-us/library/ms189292.aspx there are some requirements server-side that have to be met in order for such unique constraints to work. You will have to configure your server properly.

[doctrinebot]

Comment created by it.manfred:

Yep, thx for answer, but according to the same documentation:

{quote} The ANSI_NULLS connection-level option must be set to ON when the CREATE TABLE or ALTER TABLE statement that defines the computed column is executed. The OBJECTPROPERTY function reports whether the option is on through the IsAnsiNullsOn property.

The connection on which the index is created, and all connections trying INSERT, UPDATE, or DELETE statements that will change values in the index, must have six SET options set to ON and one option set to OFF. The optimizer ignores an index on a computed column for any SELECT statement executed by a connection that does not have these same option settings. The NUMERIC_ROUNDABORT option must be set to OFF, and the following options must be set to ON: ANSI_NULLS ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIER Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher. {quote}

So my fixtures are created without correct connection-level option

Am I wrong?

[doctrinebot]

Comment created by it.manfred:

I think that problem is still persist. According to documentation connection must have some options (see http://msdn.microsoft.com/en-us/library/ms189292.aspx and my comment)

Propose to pay more attention to the problem.

[doctrinebot]

Comment created by @deeky666:

Hmmm I suppose that there is something wrong with your setup/configuration then. I just tested this using DBAL sqlsrv driver SQL Server 2012 and it adds the unique index on a nullable column without problems. Not sure what's wrong with your client/server setup then. Also I don't see how one would set those settings in the connection handle... Which database driver are you using? sqlsrv or pdo_sqlsrv?

[doctrinebot]

Comment created by it.manfred:

Sorry for bothering you/ Now I began to suspect that the case in the client I'll test today booth setup on a diffirent clients

[doctrinebot]

Comment created by @deeky666:

Nevermind :) If there truly is a bug we'll fix that somehow but as this is really common core functionality and nobody complained so far I suppose it's really related to your environment. Please let me know if I can close the issue as soon as you have checked your env. Thanks!

[doctrinebot]

Comment created by it.manfred:

Steve, it seems, that problem persist only with FreeTDS/dblib driver (my env). Probably sqlsrv driver does set this parameters automatically. Unfortunately there is no other mssql drivers for linux, so situation is very strange. Maybe there is nobody using linux + mssql env? Crzy :) Also forced to move to pdo_mysql.

Untill dblib not supported for doctrine - we can mark this as invalid. Thx for your help

[doctrinebot]

Comment created by it.manfred:

Am I right at conclusion that:

1. Linux users can use dblib+mssql and had this issues. To fix this they can set connection-level paramters somehow before ALTER TABLE by querying "SET ANSI_NULLS ON; SET ANSI_PADDING ON; ..." etc...
2. There is only windows users are able to use doctrine DBAL with mssql without any problems thru sqlsrv driver provided by microsoft? it bothers me in some way

[doctrinebot]

Comment created by @deeky666:

Hehe we could have saved a lot of time if you had mentioned earlier that your are using dblib driver ;) But nevermind. dblib is not supported by Doctrine as it is an experimental driver and quite buggy. Not even sure how you managed to get DBAL connecting through dblib without a custom driver implementation. Whatsoever, unfortunately you are right, there currently is no officially supported way of connecting with DBAL to a SQL Server using a linux client. And yes only Windows users can do that by using either pdo_sqlsrv or native sqlsrv drivers provided by M$ ;) If you are not bound to a Microsoft SQL Server backend and can use an alternative such as MySQL or any other database vendor I would then advise you to do that. I highly discourage you to start messing with dblib and its bugs/incompatibilities. If you are really bound to SQL Server you'll have to let your PHP application run on a windows machine and utilize one of Microsoft's PHP drivers. Hope that helps for now... Closing then...

[doctrinebot]

Issue was closed with resolution "Invalid"

[github-actions[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.