1

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").

  1. Django 1.8.18
  2. MySQL 5.6

Thank you so much

Belate
  • 11
  • 1
  • 2
  • Possible duplicate of [How can I employ "if exists" for creating or dropping an index in MySQL?](http://stackoverflow.com/questions/2480148/how-can-i-employ-if-exists-for-creating-or-dropping-an-index-in-mysql) – Shadow Apr 27 '17 at 14:04

3 Answers3

1

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)
alfonso.kim
  • 2,844
  • 4
  • 32
  • 37
  • Thank you so much I've used your idea to solve it :D – Belate Apr 27 '17 at 15:30
  • `exists = True if cursor.fetchone() else False` will always be `True`. You have to check the value – alfonso.kim Apr 28 '17 at 04:44
  • I've tested and if the query doesn't return rows, cursor.fetchone() is returning None: `In [1]: from django.db import connection` `In [2]: with connection.cursor() as cursor:` `cursor.execute("SHOW INDEX FROM table1 WHERE KEY_NAME = 'index_name'")` `print cursor.fetchone()` `(u'table1', 1L, u'index_name', 1L, u'index_name')` `In [3]: with connection.cursor() as cursor:` `cursor.execute("SHOW INDEX FROM table1 WHERE KEY_NAME = 'index_name2'")` `print cursor.fetchone()` `None` – Belate May 03 '17 at 15:56
0

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)
    ]
Belate
  • 11
  • 1
  • 2
0

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

migueloop
  • 525
  • 9
  • 21