('23000', "[23000] [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of UNIQUE KEY constraint 'cms_page_publisher_is_draft_8c776642_uniq'. Cannot insert duplicate key in object 'dbo.cms_page'. The duplicate key value is (1, 1, <NULL>). (2627) (SQLExecDirectW)")

[mkane848]

Sorry, back again! Still same setup as last time: Django on a Windows Server 2012, MS SQL Server. Working with djangoCMS and having the above issue when creating new pages. Full traceback:

('23000', "[23000] [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of UNIQUE KEY constraint 'cms_page_publisher_is_draft_8c776642_uniq'. Cannot insert duplicate key in object 'dbo.cms_page'. The duplicate key value is (1, 1, <NULL>). (2627) (SQLExecDirectW)")
Request Method: POST
Request URL:    http://pilweb01/en/admin/cms/page/add/?language=en&target=1&position=last-child
Django Version: 1.9.2
Exception Type: IntegrityError
Exception Value:    
('23000', "[23000] [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of UNIQUE KEY constraint 'cms_page_publisher_is_draft_8c776642_uniq'. Cannot insert duplicate key in object 'dbo.cms_page'. The duplicate key value is (1, 1, <NULL>). (2627) (SQLExecDirectW)")
Exception Location: C:\Python34_x86\lib\site-packages\sql_server\pyodbc\base.py in execute, line 537
Python Executable:  C:\Python34_x86\python.exe
Python Version: 3.4.2
Python Path:    
['.',
 'C:\\inetpub\\wwwroot\\Django\\masernet',
 'C:\\Windows\\SYSTEM32\\python34.zip',
 'C:\\Python34_x86\\DLLs',
 'C:\\Python34_x86\\lib',
 'C:\\Python34_x86',
 'C:\\Python34_x86\\lib\\site-packages']

I tried to flush the database from the command line to see if it was something I did, but I also get this error when running python manage.py flush:

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "C:\Python34_x86\lib\site-packages\django\core\management\__init__.py", l
ine 353, in execute_from_command_line
    utility.execute()
  File "C:\Python34_x86\lib\site-packages\django\core\management\__init__.py", l
ine 345, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Python34_x86\lib\site-packages\django\core\management\base.py", line
348, in run_from_argv
    self.execute(*args, **cmd_options)
  File "C:\Python34_x86\lib\site-packages\django\core\management\base.py", line
399, in execute
    output = self.handle(*args, **options)
  File "C:\Python34_x86\lib\site-packages\django\core\management\commands\flush.
py", line 49, in handle
    allow_cascade=allow_cascade)
  File "C:\Python34_x86\lib\site-packages\django\core\management\sql.py", line 1
9, in sql_flush
    statements = connection.ops.sql_flush(style, tables, seqs, allow_cascade)
  File "C:\Python34_x86\lib\site-packages\sql_server\pyodbc\operations.py", line
 352, in sql_flush
    cursor.execute("SELECT COUNT(*) FROM %s" % self.quote_name(seq["table"]))
  File "C:\Python34_x86\lib\site-packages\django\db\backends\utils.py", line 79,
 in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "C:\Python34_x86\lib\site-packages\django\db\backends\utils.py", line 64,
 in execute
    return self.cursor.execute(sql, params)
  File "C:\Python34_x86\lib\site-packages\django\db\utils.py", line 95, in __exi
t__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "C:\Python34_x86\lib\site-packages\django\utils\six.py", line 685, in rer
aise
    raise value.with_traceback(tb)
  File "C:\Python34_x86\lib\site-packages\django\db\backends\utils.py", line 62,
 in execute
    return self.cursor.execute(sql)
  File "C:\Python34_x86\lib\site-packages\sql_server\pyodbc\base.py", line 537,
in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC SQL Server
 Driver][SQL Server]Invalid object name 'django_auth_ldap_testuser'. (208) (SQLE
xecDirectW)")

Thank you for the fix last time, and anything you can do to help with this!

[michiya]

@mkane848 Thank you for reporting again.

The error you receive is due to incorrect implementation of the SQL standard by Microsoft. You are not allowed to put multiple null values for columns under a unique constraint in MS SQL Server. See the following page for more details:
https://connect.microsoft.com/SQLServer/Feedback/Details/299229

We can use filtered unique indexes instead of unique constraints as a workaround like @tax does in the pull request #43 . I've made similar changes to the pull request from him in my local repository to test this technique and confirmed that I can resolve the error.

But I've found that we can't use MS SQL Server as a backend of django CMS for another reason. django-treebeard, a dependency of django CMS, has an implementation to generate an SQL statement without using Django's database functions, and it generates an SQL statement inside django CMS that is invalid for MS SQL Server. You can find the actual code for this in the following page:
https://github.com/tabo/django-treebeard/blob/4.0/treebeard/mp_tree.py#L250

django-treebeard generates an SQL statement like the following one when you create a new page on django CMS:
UPDATE [cms_page] SET path=?||SUBSTR(path, ?), depth=LENGTH(?||SUBSTR(path, ?))/? WHERE path LIKE ?

And you will receive an error like the following even if you resolve the first error:
[42000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near '|'. (102) (SQLExecDirectW)

For MS SQL Server the SQL statement should be like:
UPDATE [cms_page] SET path=?+SUBSTRING(path, 0, ?), depth=LEN(?+SUBSTRING(path, 0, ?))/? WHERE path LIKE ?

Unfortunately we are unable to employ MS SQL Server for django CMS backend unless the error above is resolved. If you really need to use MS SQL Server for django CMS backend, why don't you ask django-treebeard guys to support MS SQL Server in addition to other database engines or to use Django's database functions instead of hard coding SQL fragments in their codes to generate SQL statements?

[mkane848]

Thank you for the update! It was incredibly informative, and now I've actually got a clue as to how to go about resolving this :)

Thanks again for the info and help