I'm trying to drop an index using migrations.RunSQL but I having the issue that doesn't exist, is there a way to Drop an index only in the case of exist? Something like migrations.RunSQL("DROP INDEX IF EXISTS index_id ON table").
Thank you so much
Since IF EXISTS is not supported in indexing by MySQL, you may want to write your own migration:
def drop_index_if_exists(apps, schema_editor):
# access to the connection since schema_editor.execute does not return the cursor
with schema_editor.connection.cursor() as cursor:
cursor.execute("SHOW INDEX FROM table_name WHERE KEY_NAME = 'index_name'");
exists = int(cursor.fetchone()) > 0
# outside with to close the cursor
if exists:
schema_editor.execute("CREATE INDEX index_name ON ...")
operations = [
migrations.RunPython(drop_index_if_exists)
]
For consistency, you can write a create_index_if_not_exists method to un-apply the migration, and call it:
migrations.RunPython(drop_index_if_exists, create_index_if_not_exists)
Here there is one solution, thank you for idea @alfonso.kim
# -*- coding: utf-8 -*-
from __future__ import unicode_literals
from django.db import migrations
def drop_index_if_exists_and_create(apps, schema_editor):
# access to the connection since schema_editor.execute does not return the cursor
with schema_editor.connection.cursor() as cursor:
cursor.execute("SHOW INDEX FROM table_name WHERE KEY_NAME = 'index_name'")
exists = True if cursor.fetchone() else False
# outside with to close the cursor
if exists:
schema_editor.execute("DROP INDEX index_name ON table_name")
schema_editor.execute("CREATE INDEX index_name ON table_name(index_name(191))")
class Migration(migrations.Migration):
dependencies = [
('table_name', ''),
]
operations = [
migrations.RunPython(drop_index_if_exists_and_create)
]
I'm getting a Transaction Error after executing your code:
django.db.transaction.TransactionManagementError: Executing DDL statements while in a transaction on databases that can't perform a rollback is prohibited.
Django 2.5 MySql 5.7