CODEWITHSUNDEEP
mysql
Brian
Brian

Reputation: 346

Unusual MySQL error 1005/1025

I seem to have run across a strange situation where there is a specific table name that I cannot use. Let me explain.

CREATE TABLE IF NOT EXISTS hotstick_work_orders (
    work_order_id BIGINT UNSIGNED NOT NULL,
    step_id TINYINT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    FOREIGN KEY (work_order_id) REFERENCES work_orders (id) ON DELETE CASCADE ON UPDATE NO ACTION,
    FOREIGN KEY (step_id) REFERENCES hotstick_steps (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
    PRIMARY KEY (work_order_id, step_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This works fine on my local MySql stack (v5.6.17), but gives me error code #1005 when I try it using PhpMyAdmin (v4.0.10.7, MySQL v5.5.42) on GoDaddy.

Okay, you say, this is obviously just another case where the FK definitions don't match perfectly, or possibly one where a referenced table is missing an index on the column. However, I can't even create the table without FKs - the following fails just the same:

CREATE TABLE IF NOT EXISTS hotstick_work_orders (
    work_order_id BIGINT UNSIGNED NOT NULL,
    step_id TINYINT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (work_order_id, step_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Even more interesting, if I run the original create table query but use a different name, even hotstick_work_order (without the final s), it works FINE. I can rename this table to anything I want (including longer names), EXCEPT for hotstick_work_orders. Trying gives me error #1025, but only with that specific name.

E.g.:

 RENAME TABLE hotstick_work_order TO hotstick_work_orders;
 Resulting Error: #1025 - Error on rename of './db/hotstick_work_order' to './db/hotstick_work_orders' (errno: -1)

 # Whereas this works fine:
 RENAME TABLE hotstick_work_order TO hotstick_work_orders_something;

I don't have any previous table with that name, nor could I find any existing constraints in the information_schema.table_constraints table.

Of course I can manage using a different table name, no big deal, but I'm very curious - what could possibly cause such behavior?

Upvotes: 2

Views: 65

Answers (1)

Marco Aurélio Deleu
Marco Aurélio Deleu

Reputation: 4367

What you're probably suffering from is a bad case of cached naming. A proper server restart might solve your problem, but as you mentioned, you can't do it due to shared server configuration.

When I asked you to create a MyISAM table with the exact name was so that we could establish that the problem was indeed cached indexes or constraints linked to your table name. Now what I recommend you to do is:

  1. Try and Repair the MyISAM table.
  2. Try to Optimize the table.
  3. Execute a SELECT SQL_NO_CACHE * FROM TABLE to stop MySQL from caching queries.
  4. Drop the Table.
  5. Re-create it as you wish it would be (with constraints and all, InnoDB).

Upvotes: 1

Related Questions