Error: There is already an object named 'django_migrations' in the database.

Using Django Using the ORM
1

Hi everyone,

Given that I have a django project with the following database setup:

‘default’ = sqlite3, mainly used for the storage of user, group, contenttype, migration etc…
‘mssql’=sql server, mainly used for data storage

both databases need read, write, and migration permission. mssql db is created since I don’t have permissions for database creation in sql server. Otherwise, I’d set mssql as default database.

Initially, the django_migrations table has been created after running the following

python manage.py makemigrations
python manage.py migrate

Let’s say a model table has been created under an app ‘team_app’, when I run the following command to get the table migrated to mssql database, the following error came out

python manage.py migrate --database mssql

Running migrations: 
  Applying contenttypes.0001_initial...

pyodbc.ProgrammingError: ('42501', "[42501] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]There is already an object named 'django_migrations' in the database.  (2714) (SQLExecDirectW")

.
.
.

django.db.migrations.exceptions.MigrationSchemaMissing: Unable to create the django_migrations table (('42501', "[42501] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]There is already an object named 'django_migrations' in the database.  (2714) (SQLExecDirectW")

Here is my router.py partial code.

class CHRouter:
.
.
.
    def allow_migration(self, db, app_label, model_name=None, **hints):
        if app_label=="contenttypes" or model_name=="django_migrations":
            return db=='default'
        elif app_label=='team_app':
            return db=='mssql'
.
.
.

Based on the information above, my I ask how to get this error fixed?

Thank you for your response in advance.

Kelvin

2

If you’re going to use the migration facilities on the mssql database, you need to allow Django to create the django_migrations model on that database. The django_migrations model exists on a per-database basis. (Otherwise, Django isn’t able to know which migrations have been applied to the database on that database.)

So your basic choices are:

  • Remove django_migrations from your router
  • Don’t use migrations with the mssql database.
3

Hi Ken,

I found that even though I set django_migrations from my router.py as follows, the django_migrations was still created in ‘mssql’ database. I wonder whether i set it correctly?

.
.
    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if model_name=="django_migrations": 
            return db=='default'

Kelvin

4

There is no “all_migration” method in routers. The method name is allow_migrate, which should return True, False, or None. It does not return a database to use on the migration.

But again, allowing any migrations on a database without a migrations table is not going to work.

5

Are you trying to migrate a db created in another project?

6

No, I’m trying to add an additional db (new data source) from SQL server to an existing Django project.

7

Hi Ken,

Sorry for the typo. The function name is allow_migrate that is used.

And the same error still occurred, even django_migrations logic has been removed from my router.

One more thing is that the table django_migrations was created successfully when running python manage.py migrate --database mssql

I have modified router.py as follows:

class AuthRouter:
    """
    A router to control all database operations on models in the
    auth and contenttypes applications.
    """

    route_app_labels = {"auth", "contenttypes"}

    def db_for_read(self, model, **hints):
        """
        Attempts to read auth and contenttypes models go to auth_db.
        """
        if model._meta.app_label in self.route_app_labels:
            return "default"
        return None

    def db_for_write(self, model, **hints):
        """
        Attempts to write auth and contenttypes models go to auth_db.
        """
        if model._meta.app_label in self.route_app_labels:
            return "default"
        return None

    def allow_relation(self, obj1, obj2, **hints):
        """
        Allow relations if a model in the auth or contenttypes apps is
        involved.
        """
        if (
            obj1._meta.app_label in self.route_app_labels
            or obj2._meta.app_label in self.route_app_labels
        ):
            return True
        return None

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """
        Make sure the auth and contenttypes apps only appear in the
        'auth_db' database.
        """
        if app_label in self.route_app_labels:
            return db == "default"
        return None


class CHRouter:
.
.
.
    def allow_migrate(self, db, app_label, model_name=None, **hints):
        allow_migrate_app_labels = {'mssql_app'}

        if app_label in allow_migrate_app_labels:
            return True
        else:
            return False

in settings.py

.
.
.

DATABASE_ROUTERS = ['myproject.dbrouters.AuthRouter','myproject.dbrouters.CHRouter']
.
.

Kelvin

8

add column? or create new table? or create new database?

9

I’m adding a new database source ‘mssql’ in a django project. A new model table in ‘mssql_app.models’ is created and needd to migrate to the new database source ‘mssql’.

10

If you’re getting an error about a table already existing, you can either drop the table to allow the migration to create it, or run the migration with the —fake parameter to allow Django to track that migration as having been applied without actually changing the database. Either option has a set of potential side effects, depending upon the other migrations in that migration file.

I’m kinda losing track here of what the current status of your databases are, it might help if you summarize what that status is.

11

Actually, I already did and dropped the existing table django_migrations in ‘mssql’ database before running python manage.py migrate --database mssql, so that means the table django_migrations had been created and then the error occurred while applying contenttypes.0001_initial within a single execution python manage.py migrate --database mssql

My current database status are that
The django_migrations table already created and existed in ‘default’ sqlite db after initially setup.
The django_migrations table is not existed in ‘mssql’ sql server db and I’m trying to add this new database source to my existing django project.

Please let me know if more information is needed.

Thank you so much for your response.

Kelvin

12

mssql database is .sqlite??? not mysql???
would you add infomation that DATABASES in settings.py without database auth info?

13

‘mssql’ is a database in SQL server.

would you add infomation that DATABASES in settings.py without database auth info? < May I ask what you mean? Could you elaborate with an example? Thanks.

14

add “DATABASES” value in your settings.py

15

Setting DATABASES in settings.py is the first thing I did and both databases have been configured in settings.py

16

please share the values ​​of the DATABASES you set up.

17

In settings.py

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.sqlite3',
    'NAME': BASE_DIR / 'db.sqlite3',
  }

  'mssql': {
    'ENGINE': 'mssql',
    'NAME': MSSQL_NAME,
    'USER': MSSQL_USER,
    'PASSWORD': MSSQL_PASSWORD,
    'HOST': MSSQL_HOST,
    'PORT': '',
    'OPTIONS': {
      'extra_params': 'Trusted_Connection=Yes',
      'driver': 'ODBC Driver 11 for SQL Server',
    }
  }
}

DATABASE_ROUTERS = ['myproject.dbrouters.AuthRouter','myproject.dbrouters.CHRouter']

django_migrations table was able to create in mssql DB, so I assume both DB connections work just fine.

Kelvin

18

did you try delete msql database and migrate??

19

I believe deleting a database is not an option to me. I am able to delete tables but not the entire database :slight_smile:

20

You said you were creating a new DB, but why can’t you delete it?