MySQL Bugs: #78518: Workbench crashes on ALTER, deletes the table, and prevents recreation

Bug #78518	Workbench crashes on ALTER, deletes the table, and prevents recreation
Submitted:	22 Sep 2015 17:44	Modified:	23 Oct 2015 0:57
Reporter:	Patrick Seiter	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	6.3.4.0	OS:	MacOS (10.10.5)
Assigned to:		CPU Architecture:	Any
Tags:	crash, database corruption

Description:
I detailed this bug in vivid detail here:

http://stackoverflow.com/q/28218239

Basically, if I run an ALTER statement in MySQL Workbench, there's a chance that it will delete the table I am altering and I cannot re-create the table with the same name. This is also partly a MySQL Server bug, but I can't seem to add a second category to this bug report.

This has happened to me three times in total. Thankfully, it did not happen in production, but if it had, we could have lost an entire table of data.

How to repeat:
1. Connect to a MySQL server over an SSH tunnel.
2. Right-click - Alter table...
3. Make a change to a column of the table.
4. Click Apply.
5. Click Apply in the popup.
6. MySQL Workbench will crash, but the alter will not be applied. The database table will be deleted, and the name cannot be re-used. The user will get this message on every attempt of recreation for the table:

Error Code: 1005. Can't create table 'adstudio.data_feed_param' (errno: -1)

This is only for InnoDB tables. A table with the same name can be re-created in MyISAM.

Suggested fix:
The only way to fix this is detailed in the answer:

http://stackoverflow.com/a/30678282

The database had to be dumped and re-imported into a new schema. This would be unacceptable for a production environment.

It should also be noted that I'm tunneling into an AWS EC2 instance to connect to an AWS RDS instance. I would suggest bringing up a few micro instances for testing.

Executing Alter Table

Attachment: ssh-tunnel.png (image/png, text), 356.03 KiB.

The table was altered on Linux Box: see column name to name2

mysql> show create table account_data_feed_param\G
*************************** 1. row ***************************
       Table: account_data_feed_param                         
Create Table: CREATE TABLE `account_data_feed_param` (        
  `id` bigint(20) unsigned NOT NULL,                          
  `account_id` bigint(20) unsigned NOT NULL,                  
  `name2` varchar(64) NOT NULL,                               
  `default_value` varchar(64) NOT NULL,
  `input_type_id` smallint(5) unsigned NOT NULL,
  `lookups_json` mediumtext NOT NULL,
  `enabled` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `creation_user_id` bigint(20) unsigned NOT NULL,
  `creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_modified_user_id` bigint(20) unsigned NOT NULL,
  `last_modified_date` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01',
  `deletion_user_id` bigint(20) unsigned DEFAULT NULL,
  `deletion_date` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dfp_account_idx` (`account_id`),
  KEY `dfp_input_type_idx` (`input_type_id`),
  KEY `dfp_creation_user_idx` (`creation_user_id`),
  KEY `dfp_last_modified_user_idx` (`last_modified_user_id`),
  KEY `dfp_deletion_date_idx` (`deletion_user_id`),
  CONSTRAINT `dfp_account` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `dfp_creation_user` FOREIGN KEY (`creation_user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `dfp_deletion_date` FOREIGN KEY (`deletion_user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `dfp_input_type` FOREIGN KEY (`input_type_id`) REFERENCES `input_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `dfp_last_modified_user` FOREIGN KEY (`last_modified_user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

On my side I got the WorkBench Apply Dialog hanging after to press Apply button I needed to kill the WorkBench process. However the alter table was successful on remoter server. There are others bugs reported like: http://bugs.mysql.com/bug.php?id=75847 and http://bugs.mysql.com/bug.php?id=73343 with hanging issue and probably related among them. Please check the err log file on remote server for looking the error message when the apply button was applied at first time. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".