Non-default schemas are not used/respected #496
Description
I have found an issue where non-default schemas are not fully supported by commands such as flush and schemas are not migrated as normal tables. With inter connecting tables in non dbo schemas the flush fails to run (see below) and tables do not auto-create without adding custom migrations for creating schemas.
Software versions
- Django: 5.2
- mssql-django: 1.6
- python: 3.10.19
- SQL Server: 2017 and 2022
- OS: Windows and Linux
Table schema and Model
class ParentSchemaModel(models.Models): name = models.CharField(max_length=128) class Meta: db_table = '[test].[ParentSchemaModel]' class ChildSchemaModel(models.Models): name = models.CharField(max_length=128) first = models.ForeignKey(FirstSchemaModel, on_delete=models.CASCADE) class Meta: db_table = '[test].[ChildSchemaModel]'Database Connection Settings
{ "default": { "ENGINE": "mssql", "NAME": "test_server", "USER": "TestUser", "PASSWORD": "<password>", "HOST": "localhost\\SQLEXPRESS", "PORT": "", "OPTIONS": { "driver": "SQL Server Native Client 11.0", "extra_params": "Trusted_Connection=Yes", "query_timeout": 300 }, "TEST": { "NAME": "test_server", "DEPENDENCIES": [], "CHARSET": None, "COLLATION": True, "MIGRATE": True, "MIRROR": None }, "ATOMIC_REQUESTS": False, "AUTOCOMMIT": True, "CONN_MAX_AGE": 0, "CONN_HEALTH_CHECKS": False, "TIME_ZONE": None } }
Problem description and steps to reproduce
When using the Meta.db_table with a defined schema that is not dbo, the table is created and managed correctly. However flushing the table using python manage.py flush errors, reporting that:
[42S02] [Microsoft][SQL Server Native Client 11.0][sql Server]Cannot find the object "ChildSchemaModel" because it does not exist or you do not have permissions. (4902) (SQLExecDirectW)
When running python manage.py sqlflush it outputs the following:
ALTER TABLE [ChildSchemaModel] NOCHECK CONSTRAINT [test_name_of_foreign_key] ... ALTER TABLE [ChildSchemaModel] CHECK CONSTRAINT [test_name_of_foreign_key] There is also no DELETE FROM [ChildSchemaModel] or DELETE FROM [ParentSchemaModel] This means that neither table is being cleared and when there are constraints none of the models in that DB are being flushed, due to the error
Expected behavior and actual behavior
Creating models with different, non default (dbo) schemas should be supported and running flush should clear the tables as expected, rather than erroring, or not running for alternative schemas.
Error message/stack trace
This stack trace is from one of our production tables, rather than the test examples used above.
Traceback (most recent call last): File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\db\backends\utils.py", line 103, in _execute return self.cursor.execute(sql) File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\mssql\base.py", line 679, in execute return self.cursor.execute(sql, params) pyodbc.ProgrammingError: ('42S02', '[42S02] [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot find the object "clincode" because it does not exist or you do not have permissions. (4902) (SQLExecDirectW)') The above exception was the direct cause of the following exception: Traceback (most recent call last): File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\core\management\commands\flush.py", line 74, in handle connection.ops.execute_sql_flush(sql_list) File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\db\backends\base\operations.py", line 473, in execute_sql_flush cursor.execute(sql) File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\db\backends\utils.py", line 79, in execute return self._execute_with_wrappers( File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\db\backends\utils.py", line 92, in _execute_with_wrappers return executor(sql, params, many, context) File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\db\backends\utils.py", line 100, in _execute with self.db.wrap_database_errors: File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\db\utils.py", line 91, in __exit__ raise dj_exc_value.with_traceback(traceback) from exc_value File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\db\backends\utils.py", line 103, in _execute return self.cursor.execute(sql) File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\mssql\base.py", line 679, in execute return self.cursor.execute(sql, params) django.db.utils.ProgrammingError: ('42S02', '[42S02] [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot find the object "clincode" because it does not exist or you do not have permissions. (4902) (SQLExecDirectW)') The above exception was the direct cause of the following exception: Traceback (most recent call last): File "c:\Users\clarben2\Desktop\Git\api_lib\.\ehs\api\test\case\mixin.py", line 141, in setUpClass call_command("flush", database=db, verbosity=0, interactive=False) File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\core\management\__init__.py", line 194, in call_command return command.execute(*args, **defaults) File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\core\management\base.py", line 460, in execute output = self.handle(*args, **options) File "c:\Users\clarben2\Desktop\Git\api_lib\.\ehs\api\management\commands\flush.py", line 16, in handle return super().handle(*args, **options) File "C:\Users\clarben2\AppData\Local\miniconda3\envs\api_lib\lib\site-packages\django\core\management\commands\flush.py", line 76, in handle raise CommandError( django.core.management.base.CommandError: Database test_server couldn't be flushed. Possible reasons: * The database isn't running or isn't configured correctly. * At least one of the expected database tables doesn't exist. * The SQL was invalid. Hint: Look at the output of 'django-admin sqlflush'. That's the SQL this command wasn't able to run.