A Little Noise

errno: 121 (Duplicate key) with CREATE TABLE

Trying to create a table, and getting something like this?

ERROR 1005 (HY000): Can't create table '<db>.<table>' (errno: 121)

Discovered that perror 121 says this?

MySQL error code 121: Duplicate key on write or update

Really confused how you might get a duplicate key error while creating a table?

If the table you're trying to create includes a foreign key constraint, and you've provided your own name for that constraint, remember that it must be unique within the database. Run this query to see if that name is in use somewhere:

SELECT
  constraint_name,
  table_name
FROM
  information_schema.table_constraints
WHERE
  constraint_type = 'FOREIGN KEY'
  AND table_schema = DATABASE()
ORDER BY
  constraint_name;

(If you're still on 4.1 or earlier,

mysqldump --no-data yourDbName | grep CONSTRAINT

to get a similar list)

Thanks to [raymond] on Freenode.

Error 1307 creating stored procedure

Trying to create a stored procedure, and getting a cryptic error like this?

ERROR 1307 (HY000): Failed to CREATE PROCEDURE <procName>

Try this simple diagnostic query first:

SELECT
  IF(
    COUNT(*) XOR (CAST(VERSION() AS decimal(2, 1)) >= 5.1),
    'Wrong mysql.proc table version. Did you forget to run mysql_upgrade?',
    'The mysql.proc table looks ok. Keep hunting.'
  ) AS troubleshooting
FROM
  information_schema.columns
WHERE
  table_schema = 'mysql'
  AND table_name = 'proc'
  AND column_name LIKE 'body_utf8';