Ticket #776 (closed defect: fixed)

Opened 3 years ago

Last modified 4 months ago

Cannot drop index that was created when the table was created.

Reported by: tapocol@… Owned by: andrew
Priority: major Milestone: 1.0
Component: databaseapi Version: 0.7.2
Keywords: Cc:

Description

To reproduce with MySQL:

  • Make a model with a field that has db_index: True
  • Generate migration with --initial
  • Run Migration
  • Remove db_index: True from field settings.
  • Generate migration with --auto
  • Run Migration to see the error.

db_create_index and db_create_table with a field that has the setting 'db_index' seem to create two different naming styles for the index. db_delete_index uses the naming style used by db_create_index. So, when it is trying to delete the index that was created in db_create_table, MySQL returns the error message "Can't DROP 'xxx_xxx'; check that column/key exists".

Change History

comment:1 Changed 3 years ago by andrew

  • Status changed from new to assigned
  • Component changed from commands to databaseapi
  • Milestone set to 1.0

comment:2 Changed 3 years ago by Petri Lehtinen <petri@…>

This affects me too. I have a foreign key and would like to remove its index.

./manage.py shemamigration app --auto generates a call like db.delete_index('app_model', ['field_id']) which, when applied, tries to delete the index app_table_field_id. In the database, the index's actual name is app_table_hexstr, so there are two different naming styles indeed.

comment:3 Changed 3 years ago by thasonic@…

I've made the patch and created the pull request: https://bitbucket.org/andrewgodwin/south/pull-request/34/fixed-issue-776

comment:4 Changed 17 months ago by anonymous

..did this ever get merged? I'm having this problem.

comment:5 Changed 17 months ago by thasonic@…

Yes, it was merged more than year ago. And everything worked.

I've investigated that problem started to reproduce after this commit in Django repo: https://github.com/django/django/commit/4285571c5a9bf6ca3cb7c4d774942b9ae5b537e4

I'll try to make an independent on Django version patch.

comment:7 Changed 17 months ago by andrew

  • Status changed from assigned to closed
  • Resolution set to fixed

comment:8 Changed 15 months ago by Similanblu

I'm encoutering this issue on
Django==1.5.1
MySQL-python==1.2.3
South==0.8.1

To reproduce:
With a fresh project with syncdb done,

  • create a new app with a new model having a SlugField?
  • schemamigration newapp --initial
  • migrate
  • change model's SlugField? to CharField?
  • schemamigration newapp --auto
  • migrate

FATAL ERROR - The following SQL query failed: DROP INDEX newapp_newmodel_2dbcba41 ON newapp_newmodel
The error was: (1091, "Can't DROP 'newapp_newmodel_2dbcba41'; check that column/key exists")

When I inspected the database, I found that the existing indices are 'PRIMARY' and 'myslug' (my SlugField?), evidently South is using the wrong name of index when deleting (or creating).

However, if I first started with a CharField? then change it to SlugField?, then the hashed index will be created correctly (and also deleted correctly when changed back to CharField? again later). Though in any case the index 'myslug' will also be created from in the initial migration regardless of using SlugField? or CharField?.

This happens on both my local database (MySQL 5.6.10 InnoDb? 1.2.10) on Windows 7 and my Amazon RDS (MySQL 5.5.31) connected from Amazon Linux 64bit.

comment:9 follow-up: ↓ 10 Changed 15 months ago by Petri Lehtinen <petri@…>

I also have this issue (again) when I tried with SQLite database. It works correctly with PostgreSQL, though.

These are the schemas of the table and the index that are created by South:

CREATE TABLE "emailer_subscriptionlog" ("id" integer NOT NULL PRIMARY KEY, "source_id" integer NOT NULL, "recipient" varchar(75) NOT NULL, "type" varchar(50) NOT NULL, "timestamp" datetime NOT NULL);
CREATE INDEX "emailer_subscriptionlog_a34b03a6" ON "emailer_subscriptionlog" ("source_id");

And this is what South tries to remove:

db.delete_index('emailer_subscriptionlog', ['source_id'])
DatabaseError: no such index: emailer_subscriptionlog_0afd9202

So this time there's a hash in the index name, but it's a wrong hash.

comment:10 in reply to: ↑ 9 ; follow-up: ↓ 11 Changed 12 months ago by andrew.gin@…

Replying to Petri Lehtinen <petri@…>:

So this time there's a hash in the index name, but it's a wrong hash.

I am having this exact same error. Did you manage to solve this?

comment:11 in reply to: ↑ 10 Changed 12 months ago by Petri Lehtinen <petri@…>

Replying to andrew.gin@…:

I am having this exact same error. Did you manage to solve this?

No I didn't.

comment:12 Changed 4 months ago by maxim.boyarskiy@…

  • Status changed from closed to reopened
  • Resolution fixed deleted

Django 1.4/1.5 have different index naming:

https://github.com/django/django/blob/1.4.13/django/db/backends/creation.py#L24
https://github.com/django/django/blob/1.5/django/db/backends/creation.py#L27

south.db.generic.DatabaseOperations?._digest uses it.

Is it possible to provide backward (django 1.4/1.5) compatibility (some more advanced/guessing naming method)?

comment:13 follow-ups: ↓ 14 ↓ 15 Changed 4 months ago by andrew

  • Status changed from reopened to closed
  • Resolution set to fixed

Please do not reopen tickets if nothing has changed!

I regard this as fixed by Django 1.7 migrations (like most of the South bugs); once Django 1.7 is out I'll be officially EOLing South, so I'm not going to fix this.

The "proper" fix would be to introspect the database to find indexes by referenced columns, not by name; the introspection only gained this ability in 1.7, alas.

comment:14 in reply to: ↑ 13 Changed 4 months ago by anonymous

Replying to andrew:

The "proper" fix would be to introspect the database to find indexes by referenced columns, not by name; the introspection only gained this ability in 1.7, alas.

Ok. Thank you for your clarification.

comment:15 in reply to: ↑ 13 Changed 4 months ago by Petri Lehtinen <petri@…>

Replying to andrew:

once Django 1.7 is out I'll be officially EOLing South, so I'm not going to fix this.

I'm sorry to hear that South will be EOL'd. Django 1.7 migrations seem to lack the --update flag that I find very useful myself.

comment:16 Changed 4 months ago by andrew

Petri: It has squashmigrations which fulfills a similar itch, though. --update is harder on 1.7 as it's much more stringent about dependencies, and so running it might mean changing several different apps' migrations.

Note: See TracTickets for help on using tickets.