Migration 0010 fails, when using MySQL

[smileychris]

Migration 0010 fails, when using MySQL

[SmileyChris]

Hrm... that's not sounding positive. I'll check with winhamwr who provided the mysql patch

[winhamwr]

Hrm indeed. I'll take a look at this and see what might be different about my environment.

[appel268576]

I get the same result, using MySQL

[SmileyChris]

I'm very willing to accept suggestions on what should be changed to fix this.

[appel268576]

well if you feel that the app is good where it is at right now, maybe its best to remove your migrations, remove the history from mysql southmigrations table, and then start fresh, meaning that you'll now only have one migration which should render a successful result...

[SmileyChris]

That is the plan when we hit beta. But it would be nice to have working transational migrations so that users can upgrade their database and then switch to beta, resetting the ghost migrations.

[kgrinberg]

Could the issue here be that some people are creating the tables as MyISAM, while others are creating them as InnoDB? Unless it's explicitly specified, the storage engine will be taken from the user's default - which is often MyISAM.

MyISAM doesn't support foreign keys, however:

"For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it."

So it can "create" them without throwing an error, but removing one might get the result reported here.

I'll try to test this theory as soon as get a few more minutes, but this has that flavor...

[SmileyChris]

Cheers, help is most appreciated :)

[winhamwr]

That would make sense. I'm using InnoDB exclusively.

[kgrinberg]

I've now confirmed this - it's a MyISAM vs. InnoDB issue. Although MyISAM doesn't support foreign keys, it doesn't scream if you try to create them; deleting them, though, gets you an error.

Since many out-of-the-box installations of MySQL have MyISAM configured as the default table type - indeed, I think that's the default setting - I'd imagine it's a fairly common error condition.

Possible solutions:

1. As an earlier poster suggested, wrap the whole thing up in one migration, thereby obviating the need to remove keys.

2. I'm not sure if South lets you test for storage engines specifically - if so, then I suppose detect MyISAM and avoid those FK migrations accordingly.

3. Specifically tell the migrations to create the tables as InnoDB - not sure you can do that, though (sorry, haven't looked yet).

4. Just tell people to watch out and don't use MyISAM... it's bad for you anyway! (In this case at any rate).

As a sidenote, I imagine this sort of failure mode would be pretty common, so I'm wondering if there's another variable screwing it up here... I'll try to reduce it to a nicer test case later, but I've definitely confirmed that the error does or does not occur based on the default table type (and whether the tables get created as MyISAM or InnoDB) - so hopefully that helps resolve the immediate problems.

[SmileyChris]

I've just pulled a commit from stefanfoulis that should at least make forward migrations work.

[dbanck]

That's great! Thanks a lot