Bug #75923 Dropping the whole table with data, when alter table
Submitted: 17 Feb 2015 4:21 Modified: 20 Feb 2015 21:43
Reporter: Silan Liu Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.6.14 OS:Windows (Windows 7 Pro)
Assigned to: CPU Architecture:Any

[17 Feb 2015 4:21] Silan Liu
Description:
I have a table. Many other tables have foreign keys to this table. It's also got data in it. 

I used Workbench | Alter Table, I renamed a column, and moved it ahead for a few columns. I also added another column, then a foreign key from this new column to another table. When I clicked "Apply", some strange error comes up. 

I cancelled out, tried to redo the same thing, every time it comes up some error.

I restarted Workbench, to my horror, the whole table has gone!

When did cowboys start working on MySQL???

Such horror will never happen in the professional databases such as Oracle and SQL Server!

How to repeat:
I don't know.
[17 Feb 2015 22:31] Todd Farmer
I'm sorry to hear the problems you've experienced using MySQL Workbench.  The actual SQL issued to the server would be very useful in diagnosing this problem.  The SQL to be executed is displayed on the page where you clicked the "Apply" button - do you recall having reviewed this SQL and ensuring it contained the commands you expected (e.g., no DROP TABLE command)?

Do you happen to have general query, audit or binary logging enabled when the problem occurred?
[17 Feb 2015 22:34] Silan Liu
I did not check the generated SQL statement. I have been using Oracle and SQL Server for ten years and I never need to check the generated SQL statement and the DDL operations never had any problem.

I have none of those features turned on.
[17 Feb 2015 22:38] Silan Liu
And, after the table is gone, I tried to add that table again, I got "Table already exists" error. But when I say "drop table xxx", I got "Table does not exist". So I had to drop the whole schema, and recreate it from the backup of the previous day, and redo all the changes I did in the day. I appears to me there is some very serious lack of integrity of the process.
[18 Feb 2015 21:40] Todd Farmer
The behavior you describe suggests a problem with MySQL Server, rather than MySQL Workbench.  If Workbench mistakenly sent a DROP TABLE command to the server, it wouldn't be left in a state where the table couldn't be recreated ("table already exists").  Do you have server error logs from the affected time?

It's possible that certain operations executed in a specific order could cause a crash of the storage engine (or server), and possibly corrupt data (leading to table not being found, though remnants still exist).  Obviously, any such problem is a high priority to identify and solve - can you tell me what MySQL Server version you are running (and provide server error log, if available)?
[19 Feb 2015 5:14] Silan Liu
Please advise how to show the version and where to find the error log. Thanks.
[19 Feb 2015 11:24] MySQL Verification Team
The version of the server: mysqld.exe --version and the error log in \data directory a file called your_host_name_machine.err .
[19 Feb 2015 22:26] Silan Liu
Error log

Attachment: ING16WS.err (application/octet-stream, text), 142.30 KiB.

[19 Feb 2015 22:28] Silan Liu
I have uploaded the error log file. The DB version is 5.6.14.0.
[20 Feb 2015 19:41] Todd Farmer
Thank you for the error log.  It does appear that the ALTER TABLE statement caused corruption due to modifying the column which was used as a foreign key.  This was reported in Bug#74862, and was fixed in MySQL Server version 5.6.23.  Here are the relevant sections of the server error log:

2015-02-17 14:29:55 4292 [ERROR] InnoDB: dict_load_foreigns() returned 38 for ALTER TABLE `ingeneus_cloud`.`user_account` 
CHANGE COLUMN `created_by_account_id` `parent_account_id` INT(11) NOT NULL COMMENT 'The account which created this account' AFTER `business_domain_id`
2015-02-17 14:31:36 1c78  InnoDB: Error: in ALTER TABLE `ingeneus_cloud`.`user_account`
InnoDB: has or is referenced in foreign key constraints
InnoDB: which are not compatible with the new table definition.
2015-02-17 14:31:36 1c78  InnoDB: Error: in ALTER TABLE `ingeneus_cloud`.`user_account`
InnoDB: has or is referenced in foreign key constraints
InnoDB: which are not compatible with the new table definition.

Subsequently, the table could not be opened because while the tablespace (and all data) existed, the table definition could not be found in the InnoDB data dictionary:

2015-02-17 15:33:21 1c78  InnoDB: Operating system error number 80 in a file operation.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
2015-02-17 15:33:21 4292 [ERROR] InnoDB: Cannot create file '.\ingeneus_cloud\user_account.ibd'

2015-02-17 15:33:21 4292 [ERROR] InnoDB: The file '.\ingeneus_cloud\user_account.ibd' already exists though the corresponding table did not exist in the InnoDB data dictionary. Have you moved InnoDB .ibd files around without using the SQL commands DISCARD TABLESPACE and IMPORT TABLESPACE, or did mysqld crash in the middle of CREATE TABLE? You can resolve the problem by removing the file '.\ingeneus_cloud\user_account.ibd' under the 'datadir' of MySQL.
[20 Feb 2015 21:43] Silan Liu
Thank you that is good news. Is there an "update" feature or do I have to reinstall?