MySQL Bugs: #57145: CONSTRAINTs not recognized and not handled properly.

Bug #57145	CONSTRAINTs not recognized and not handled properly.
Submitted:	30 Sep 2010 14:33	Modified:	6 Feb 2013 21:24
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.28	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
I have an empty table defined like: 

CREATE TABLE `tab` (
  `num` INT(11) NOT NULL AUTO_INCREMENT,
  `txt` CHAR(10) NOT NULL,
  PRIMARY KEY (`txt`),
  UNIQUE KEY `num` (`num`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

Now from WB I enter data (image will be uploaded) and click 'Execute ...' (lightning icon)

Query editor displays "EDIT `wbtest`.`tab`;" (and it seems that some error message shortly 'flickers')

.. but from another client it is obvious that nothing was inserted.

Obviously WB does not recognize several CONSTRAINTS here and also the statement must be invalid and the server must have returned an error.  But no error appears in WB interface (except for the possible flicker what I cannot identify what is). 

How to repeat:
see above

Suggested fix:
I think WB's parser is supposed to handle this client side (unlike other clients)?

But at least the server error should be prompted to user and the editor could add something like (in a comment)

/*
"EDIT `wbtest`.`tab`;" failed

MySQL server returned the error "......."
*/

I am sorry that I cannot keep my promise not to spend more time with WB 5.2.8.  It is wonderful entertainment actually!

data entry

Attachment: data.jpg (image/jpeg, text), 8.75 KiB.

version string was incorrectly formatted - corrected!

sorry -- it is 5.2.28 and not 5.2.8

OK .. when I close Workbench I get some prompts if I want to 'review changes' and an 'apply' option. And then the server error prompts.

If SQL is only supposed to be sent to server at that time the 'tooltip' on 'Execute ' reading 'Execute SQL script in connected server' is misleading I think. But if that is how WB is supposed to work that is it.  The icon could be disabled where it does not apply for instance.

But very unfortunate if the first GRID operation by user generates an invalid statement! 

I realize that Workbench differs from most other clients in the respect that it has a 'model layer' and a 'parser' of its own.  WB does not send 'one row at a time'.  But then it should client side reject invalid INSERTS/UPDATES. Is it supposed to do that? - that is the question here.

I don't think I will need to point out that it gets even worse if the table is not empty.

You cannot 'accumulate' DML statements client side without severe risk that one will raise an error.  Each and every one will need to be executed and verified by the server as soon as entered.

You had to click on "Apply changes to data" button (those with green check mark) on top of the grid first...

Clicking on the execute button in the main toolbar just opened new tab for editing table data, exactly what EDIR command is supposed to do...

Thanks for teaching me how to navigate Workbench.  That is most needed.  I will probably never find out by myself.

But the GUI-implemenation is what this report is about.  It is about that WB does not recognize and does not handle simple CONSTRAINTs (like unique constraints violation). 

The image I uploaded shows plenty of such violations *even if table is empty*. I'd expect WB to refuse client side *foreach row* that this new row does does not violate constraints *as compared to rows already entered*.  That is a minimum. Because if not user may make a mistake in 2nd row and insertion of the next rows will fail. Actually with my example even the first row will fail in strict mode.  

It gets even worse of course if table is not empty as there may be a violation in relation to existing rows in the table.  I find no other way to validate INSERTS/UPDATES than executing against the server *foreach* row added/changed in GRID. Then user will be able to correct this without wasting a lot of time.

I am not sure WB should try to replicate all the logic for constraint validations that the server performs,
including UNIQUE keys, NULL values, foreign keys etc. This is bound to open a can of worms.

The reason is that WB does not send one row when entered by user.  In case a constraint violation occurs in the 3rd row of 200 this is effectively waste of people time (time used to enter 197 rows).

Other clients using a GRID interface will send one row when entered by user. So user discovers the violation at an early time.

In that case, the error should be displayed. But the data entered is not lost. Once you see the error you can correct it and commit it again. I'm going to verify it so that the error reporting can be fixed.
Thanks for the report.

Closing old, already handled bug