MySQL Bugs: #66182: Create Table editor can not apply changes after an invalid trigger is applied.

Bug #66182	Create Table editor can not apply changes after an invalid trigger is applied.
Submitted:	3 Aug 2012 11:24	Modified:	28 Aug 2012 19:10
Reporter:	Atli Jnsson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.41, 5.2.42	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Attempting to make a new table using the Create Table editor, which includes a trigger with an error, correctly causes an error message when applied. However, after that point you can not use the "Apply" button again without getting the following error message:

"Selected name conflicts with existing table `<tablename>`." 

It appears that the table is created, even though the trigger is invalid, and the editor is not undoing that or taking it into account when applying further changes.

How to repeat:
1) Choose to create a new table in the SQL Editor.
2) Create some columns and add a trigger with an error. (Like trying to set a non-existent column.)
3) Apply the changes. An error will appear in the window. Press Cancel to get back to the Table editor.
4) Try to Apply any further changes.

Hello Atli,

thanks for your report, I can't reproduce with the steps you share, can you please share the trigger you use and your environment details, you can get the details on Workbech, go to Help-> System Info and paste the info here.

Thanks in advance!

Hey, Rafael.

Sure. I've already tested this one two Windows 7 systems and a Ubuntu 12.04 system, where I installed Workbench from the Ubuntu repos. The problem exists on all of them.

This is the system info for one of the Windows 7 systems.

=====
MySQL Workbench CE for Windows version 5.2.41  revision 9727
Configuration Directory: C:\Users\Atli\AppData\Roaming\MySQL\Workbench
Data Directory: C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE
Cairo Version: 1.8.8
OS: Microsoft Windows 7 Home Premium Edition Service Pack 1 (build 7601), 64-bit
CPU: 4x Intel(R) Core(TM) i3 CPU       M 370  @ 2.40GHz, 5.9 GiB RAM
Active video adapter (0): ATI Mobility Radeon HD 5470                         
Installed video RAM: 512 MB
Current video mode: 1366 x 768 x 4294967296 colors
Used bit depth: 32
Driver version: 8.741.0.0
Installed display drivers: aticfx64.dll,aticfx64.dll,aticfx64.dll,aticfx32,aticfx32,aticfx32,atiumd64.dll,atidxx64.dll,atidxx64.dll,atiumdag,atidxx32,atidxx32,atiumdva,atiumd6a.cap,atitmm64.dll
Current user language: Icelandic (Iceland)
=====

This is the trigger I've been using:

======
CREATE TRIGGER `test_table_before_insert`
BEFORE INSERT ON `test_table`
FOR EACH ROW BEGIN
	SET NEW.`invalid` = '';
END$$
=====

And, just for reference, this is the entire SQL block Worbench executes when I first click the "Apply" button:

=====
CREATE  TABLE `test`.`test_table` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `whatever` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) );
USE `test`;

DELIMITER $$

DROP TRIGGER IF EXISTS test.test_table_before_insert$$
USE `test`$$

CREATE TRIGGER `test_table_before_insert`
BEFORE INSERT ON `test_table`
FOR EACH ROW BEGIN
	SET NEW.`invalid` = '';
END$$
DELIMITER ;

=====

Please try version 5.2.42. Thanks.

I just tried 5.2.42 on both Win7 and Ubuntu. The problem is still there in both.

Hi Atli,
STEPS TO REPRODUCE:
1.- Open "Create Table..." Editor in MySQL Workbench.
2.- Create Table and trigger (trigger with invalid syntax,as shown in step 3)
3.- Click "APPLY" Button and the Query is showed:
-------------------------------
CREATE  TABLE `test`.`test_table` (
  `id` INT NOT NULL ,
  `whatever` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) );
USE `test`;

DELIMITER $$

DROP TRIGGER IF EXISTS test.test_table_before_insert$$
USE `test`$$

CREATE TRIGGER `test_table_before_insert`
BEFORE INSERT ON `test_table`
FOR EACH ROW BEGIN
	SET NEW.`invalid` = 'trigger info';
END$$
DELIMITER ;
-----------------
3.-Click "Apply" Button again this error will pop up:
------------------------
ERROR 1054: Unknown column 'invalid' in 'NEW'
SQL Statement:
CREATE TRIGGER `test_table_before_insert`
BEFORE INSERT ON `test_table`
FOR EACH ROW BEGIN
	SET NEW.`invalid` = 'trigger info';
END
-----------------------------
4.-CLICK "CANCEL" BUTTON
---------------------------
5.- modify TRIGGER to a valid Query and click "Apply" button, Next message will appear:
------------------
   "Selected name conflicts with existing table `<tablename>`."
------------------

This is a normal behavior due:
1- Was executed the Create Table Query(and it was succeed created)
2.-then , was executed the Create Trigger Query (and it fails).
3.- If you modify Trigger and click "APPLY" button ( re-run the create table and trigger), it will fail cause table already exist.

Solution:
Please, before, click "APPLY" button, delete "test_table" just created, and then  click on "APPLY" again and message error won't appear.

Please, let us know if this comment helps.
Thanks in advance.

Hi, Ruben.

Thanks for taking a look at this. 

I do realize why this happens, and that dropping the table manually works around that. 

The problem is, at least as I see it, that Workbench should, ideally, detect when something like this happens and either switch into the ALTER TABLE mode it uses when altering existing tables, or just DROP the table when the user clicks Cancel and start again from scratch the next time Apply is hit.