MySQL ERROR 1005: Can’t create table

I am experiencing the weirdest behaviour with MySQL. I can’t seem to be able to create a particular table.

E.g.:


mysql> create table `devices` (id bigint(20) unsigned not null auto_increment primary key) engine=InnoDb default character set=utf8 collate=utf8_bin ;
ERROR 1005 (HY000): Can't create table './couffable_global/devices.frm' (errno: 150)

However, if I change the table name to `foo`, it works:

mysql> create table foo (id bigint(20) unsigned not null auto_increment primary key) engine=InnoDb default character set=utf8 collate=utf8_bin ;
Query OK, 0 rows affected (0.00 sec)

And no, the devices table does not already exist.

I was doing this in an already created database, as part of a data migration exercise. I already had a database with a table called devices. I was uploading an SQL dump to it. The dump would first DROP TABLE IF EXISTS `devices` and then CREATE TABLE `devices` ( ....) . This would not work.

If I start from a clean database, this would work. So I am assuming the presence of some other table is causing this.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: