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?
