I´m still in shock with what happened to me yesterday. I have a mysql database running in production. Yesterday trying to fix a performance issue in our software, I figured out that we were missing a a foreign key in a table. I run this alter table (IN PRODUCTION) using workbench:
ALTER TABLE discounts ADD INDEX (product_id),
ADD FOREIGN KEY (product_id) REFERENCES products (id);
Surprisingly, I got a connection lost error, and to my surprise the whole table discounts has been dropped. Lucky I got a backup, but now I´m pretty scared.
I´m trying to re create the table:
, but I´m getting Error Code: 1005. Can't create table 'yanpyprod.boats_discounts' (errno: 150). I have read other posts such us MySQL: Can't create table (errno: 150) but just for testing, even if I try to create next table without any foreign keys, I´m getting the error.
CREATE TABLE discounts (
id INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
);
UPDATE: To answer @Eden comment, I have exactly the same database replicated in development and I can create the table without problems.
UPDATE 2: For debugging purposes, I run.
SET FOREIGN_KEY_CHECKS=0;
I DROP TABLE boats_discounts;
CREATE TABLE boats_discounts (
id INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
);
In development, all three queries worked perfectly. In production, I got: Error Code: 1051. Unknown table 'boats_discounts' when tried to drop the table. - Error Code: 1005. Can't create table 'yanpyprod.boats_discounts' (errno: 150) when tried to create it.
UPDATE 3:
I have run next query to find out if there is any foreign key referencing table discounts. I can confirm that there isn´t any foreign key to discounts.id
SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = 'prod' AND
REFERENCED_TABLE_NAME = 'discounts';
UPDATE 4:
After running this SHOW ENGINE INNODB STATUS; that I read here: Error Code: 1005. Can't create table '...' (errno: 150) I can see this interesting information error. It´s totally related, but still doesn´t make sense to me. I mean,
LATEST FOREIGN KEY ERROR
200226 16:21:14 Error in foreign key constraint of table schema/discounts: there is no index in the table which would contain the columns as the first columns, or the data types in the table do not match the ones in the referenced table or one of the ON ... SET NULL columns is declared NOT NULL. Constraint: , CONSTRAINT "discounts_ibfk_1" FOREIGN KEY ("discount_type_id") REFERENCES "discount_types" ("id")
This is the discount_types DDL:
CREATE TABLE `discount_types` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(60) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
But, using this query that I found here: List of Constraints from MySQL Database I cannot find the 'discounts_ibfk_1' foreign key constraint:
select *
from information_schema.table_constraints
where constraint_schema = 'yanpyprod'
UPDATE 5: I´m desperate. I´m running all queries in MySQL workbench. When I try to run the same query:
CREATE TABLE discounts (
id INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
);
directly in mysql console, I get a different error: ERROR 1146 (42S02): Table 'discounts' doesn't exist
It´s pretty clear that the table doesn´t exist, because I´m trying to create it. So, I think something got corrupted. However, no idea how to fix it.
Please also see this example. Note that I have just created table discounts2 exactly as discounts, then created the indexes and foreign keys and then tried to rename discounts2 to discounts. Please see the result:
mysql> drop table discounts;
ERROR 1051 (42S02): Unknown table 'discounts'
mysql> select * from discounts;
ERROR 1146 (42S02): Table 'discounts' doesn't exist
mysql> ALTER TABLE discounts2 RENAME TO discounts;
ERROR 1050 (42S01): Table './schema/discounts' already exists
Again, I would say the database is corrupted, but I don´t know how to fix this.
