MySQL Troubleshooting

From Shrubbery

Jump to: navigation, search


[edit] Error 1005

See

The following table definition: 

CREATE TABLE fkey_exmpl (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  other_id INT NOT NULL,
  CONSTRAINT fkey_other
               FOREIGN KEY (other_id)
                REFERENCES other_table(id)
) TYPE=InnoDB;

will produce an error along the lines of: 

ERROR 1005: Can't create table './test/fkey_exmpl.frm' (errno: 150)

This means either:

  • The referenced table is not an InnoDB table
  • The referenced column in other_table is not unique
  • The referenced column in other_table has a different type to other_id

In MySQL prior to 5.0, this error can also indicate that:

  • No index has been defined on the column other_id

[edit] Viewing details

To get a better idea of what is going on, run the command that is failing then log in as a super user and issue SHOW INNODB STATUS;. For example: 

$ mysql -u super -p thedb
mysql> show innodb status;

In this output you will find a better description of the error, for example: 

071107 15:54:59 Error in foreign key constraint creation for table `thedb/mytable`.
A foreign key constraint of name `thedb/fk_foo_bar`
already exists. (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

[edit] Ways to solve the problem

In some cases, renaming a table may cause this problem, even if you drop the database and re-create it. This is because InnoDB stores the foreign keys in the InnoDB data files and not in the '.frm' files. The only way I've found to fix this so far is pretty drastic: get rid of the InnoDB data files.

Before clearing out the InnoDB data files:

  • First, make sure all your FK constraints are defined properly.
  • If that fails (which can happen when table renames don't work!), you might need resort to something drastic

Steps:

  1. Dump all your databases (well, if you care about the data).
  2. Stop mysql - Something like /etc/init.d/mysql stop or service mysqld stop
  3. Move the InnoDB files out of the way. These are usually in /var/lib/mysql and the file names begin with ib*
  4. Remove all directories for innodb databases (don't remove the 'mysql' directory unless you want to recreate all your users!)
  5. Restart mysql
  6. Reload all your databases from the dump files.
Retrieved from "http://shrubbery.mynetgear.net/wiki/MySQL_Troubleshooting"
Personal tools