MySQL Bugs: #43788: autoincrement not handled in standard inserts editor

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:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.1, 5.2	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
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:
obvious!

id column is defined auto-increment

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

inserts created by workbench

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

Thank you for the bug report.

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.

@Mike 

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!

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.

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*.

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.

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.

"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

<empty>,'a'
88,'b'
<empty>,'c'

.. 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!

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.