InnoDb working some of the time but not others

Question

I'm in the process of swapping over a database for a rewrite of my program and part of that is writing both a conversion script and a script to create new tables.

I'm renaming tables, changing indexes and generally altering most of the table in some way, part of that is that I'm changing from MyISAM to InnoDB tables.

The conversion script works flawlessly but the script to create new tables falls over at a specific point.

Query:
create table team_resources
(
     amount double not null default 0,
     resource int unsigned not null default 0,
     team int unsigned not null default 0,
     primary key (resource,team)
) ENGINE = InnoDB

I get error 121 which is the error given when a table cannot be created. The script is run from a Python file but I get the same error in both my SQL program and phpMyAdmin in both raw script and the table wizard helper form thingie.

The tables all converted to InnoDB just fine so I'm stumped as to why it has issues creating new ones. This query works if I take out the InnoDB part.

Any suggestions?

Answer 1

I'm able to run that statement fine on a MySQL 5.0.32 install. It may be a bug that's been fixed.

Answer 2

Bug 26507 sheds some light on this. Looks like creating/dropping tables isn't quite atomic.

One option is to do a mysqldump and try loading into a freshly installed database.

Another way to handle this is described at the end of Bug 17546, but you should verify the issues is with the frm file.