Bug #43788 autoincrement not handled in standard inserts editor
Submitted: 22 Mar 2009 10:27 Modified: 27 Oct 2009 9:36
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
Category:MySQL Workbench Severity:S2 (Serious)
Version:5.1, 5.2 OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[22 Mar 2009 10:27] Peter Laursen
I have a simple table defined with 1) an integer PK auto_inc column and a varchar column.

When I enter text in the varchar column WB does not set values for the integer column properly

How to repeat:
I will upload an image

Suggested fix:
[22 Mar 2009 10:29] Peter Laursen
id column is defined auto-increment

Attachment: idpk.jpg (image/jpeg, text), 12.74 KiB.

[22 Mar 2009 10:30] Peter Laursen
inserts created by workbench

Attachment: zeroinsert.jpg (image/jpeg, text), 46.00 KiB.

[22 Mar 2009 14:57] MySQL Verification Team
Thank you for the bug report.
[2 Apr 2009 13:46] Mike Lischke
Lowering prio to feature request because automatically adding values by the editor is surely something that would be nice to have, but auto inc values are usually filled by the server. Nonetheless something we should keep an eye on.
[2 Apr 2009 14:10] Peter Laursen

I simply do not understand your reply.  Why then have a data editor at all in the program? THE INSERTS WILL FAIL of course! So you find it OK to create a non-valid model?

Obviously you should create INSERTS like

INSERT INTO table (txt) values ('a') 

-- simply ignoring autoincrement columns. And you could leave the respective cells in the GRID empty as well or just print a 'placeholder' of some kind!
[19 Jul 2009 0:56] Alfredo Kojima
The Standard Inserts editor is just a nicer frontend to editing INSERT statements than a plain textbox.
It is not intended to mimic the server logic and the user is responsible for the correctness of the data entered (we can't 100% validate the data entered by the user for auto_increment, UNIQUE constraints, FOREIGN KEY constraints, NOT NULL, triggers etc)

That said, it would be a nice feature to automatically fill auto_inc columns.
[19 Jul 2009 8:06] Peter Laursen
There is nothing to be validated in this case.  I leave the autoincrement column empty in the GRID. So the client should simple let the server auto-increment that column. Do not include autoincrement columns in the columns-list of INSERT-statements if cell is empty.  That is how simple it is.

We can discuss Foreign Keys and all the other stuff mentioned elsewhere - but not in this report, because that is not what this report is about. I consider the last reply irrelevant and arrogant.

If you *will not* understand what I am writing then of course you also *can not*.
[27 Oct 2009 9:12] Susanne Ebrecht
This is still true for Workbench 5.2.

You can't let autoinc column blanc nor will be able to set NULL or DEFAULT here.
[27 Oct 2009 9:22] Susanne Ebrecht
INSERT INTO t(i, txt) VALUES (0,'a'),(0,'b'),(0,'c');

Here the 0 will be replaced with actual auto increment value.

The syntax above is doing the same as the following syntaxes:

INSERT INTO t(txt) VALUES ('a'),('b'),('c');
INSERT INTO t(i, txt) VALUES (NULL,'a'),(NULL,'b'),(NULL,'c');

So this is just a small design problem.

And I agree with you here. I prefere syntax INSERT INTO t(txt) VALUES ('a'),('b'),('c'); too but I think most MySQL users are using NULL or 0 here instead of nothing.
[27 Oct 2009 9:36] Peter Laursen
"INSERT INTO t(i, txt) VALUES (0,'a'),(0,'b'),(0,'c');
Here the 0 will be replaced with actual auto increment value."

That depends on the SQL_mode! There is a "no_auto_on_zero" or similar mode

The only correct solution is :
INSERT INTO t(txt) VALUES ('a'),('b'),('c');

But if I have 3 rows in the grid an enter


.. it will have to execute three statements.  
INSERT INTO t(txt) VALUES ('a');
INSERT INTO t(id, txt) VALUES (88,'b');
INSERT INTO t(txt) VALUES ('c');

Auto_increment values will need to be handled by the server.  A client simply cannot!
[21 Nov 2010 18:24] Lindolfo Junior
Some one has fixed this bug? I'm tring to use for model a db and in this, have to much tables with insert values where i dont place values in defaul values.