MySQL Bugs: #51563: MySQL Workbench Editor - Edit Table fails without explanation

Bug #51563	MySQL Workbench Editor - Edit Table fails without explanation
Submitted:	26 Feb 2010 18:44	Modified:	16 Nov 2011 6:25
Reporter:	Lon Amick	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.16 OSS Beta rev 5249	OS:	Windows (XP SP3)
Assigned to:		CPU Architecture:	Any
Tags:	Edit Table

Description:
EDIT database.table fails to initialize edit capability for some tables without explanation, returning a result set instead.

How to repeat:
1. In the MySQL Workbench editor select the example menagerie.pet table as created in the tutorial at section 3.3.2 of MySQL 5.1 Reference 
--
CREATE DATABASE menagerie;
USE menagerie;
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
INSERT INTO `menagerie`.`pet`
(`name`,`owner`,`species`,`sex`,`birth`,`death`)
VALUES
('Fluffy', 'Harold', 'cat', 'f', '1993-02-04', \N),
('Claws', 'Gwen', 'cat', 'm', '1994-03-17', \N),
('Buffy', 'Harold', 'dog', 'f', '1989-05-13', \N),
('Fang', 'Benny', 'dog', 'm', '1990-08-27', \N),
('Bowser', 'Diane', 'dog', 'm', '1979-08-31', '1995-07-29'),
('Chirpy', 'Gwen', 'bird', 'f', '1998-09-11', \N),
('Whistler', 'Gwen', 'bird', '\N', \N, '1997-12-09'),
('Slim', 'Benny', 'snake', 'm', '1996-04-29', \N),
('Puffball', 'Diane', 'hamster', 'f', '1999-03-30', \N)
;
--
2. issue the command: EDIT menagerie.pet;
3. At this point, a result table is populated, but the edit feature fails to initialize.
4.issue the command: ALTER TABLE `menagerie`.`pet` ADD COLUMN `idPet` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`idPet`) ;
5. issue the command: EDIT menagerie.pet;
6. Now, EDIT works as expected.

Suggested fix:
EDIT table should either allow users to edit all existing tables or explain why it refuses to do so. In this case, it appears that EDIT would not edit my table because it expected a key. I'm not sure that's the real reason, but adding a key made the difference. There may be other reasons a table can't be edited such as a privilege insufficiency, but whatever the reason, knowing why a feature fails helps the user identify the appropriate actions to take.

Table should have PRIMARY KEY for you to be able to edit data in Workbench (I'd even say that every table in RDBMS must have a PRIMARY KEY defined). But this is not necessary to just view data...

So, where is the bug, from your point of view? Workbench should NOT allow to review data if they are not editable? Tutorial should be changed? Something else?

Thank you for your comment.

I agree with you that it is usually a good idea to have a primary key, but it is allowed to create a table without one and there are situations where that is not an unreasonable design decision.

The contents of a table without a key can be edited with the UPDATE and INSERT statements, but there are challenges to making a general purpose EDIT tool to deal with tables lacking PRIMARY KEY. The design team has probably considered the obvious solutions and rejected them.

You ask, "So, where is the bug, from your point of view?" 1. The behavior of disallowing edits of tables lacking PRIMARY KEY appears to be undocumented, and to some extent runs counter to what is documented.

Section 5 TOC page says:
"... a SQL Editor tab is launched which displays a data grid that allows you to interactively edit table data as required."

Section 5.2.4.5. says:

"It is possible to edit data in real time using the Live Editing tabsheets. In the Overview tab, if a table is double-clicked, a live editing tab will be launched, allowing you to edit the data maintained in that table. Field data can be edited by clicking on a field and entering the required data, or editing existing data."

In neither case is there a mention of any limitation to the type or format of table that is allowed other than a note on entering functions when editing fields at 5.2.4.5.

2. The failure of EDIT to initialize the editing facility occurs silently.

When one has been led to believe that the EDIT function's intent is to "allow you to interactively edit table data as required" and it fails to do so and that failure occurs without warning or explanation, it appears that EDIT is not performing as it should.

You make some suggestions in your reply:
"Workbench should NOT allow to review data if they are not editable?"
No, I think that the current fallback behavior is correct. My only objection is that it falls back to display mode without notice.

"Tutorial should be changed?"
Changing the tutorial won't make much difference because tables lacking a PRIMARY KEY will still be created, even by people who never see the tutorial.

The Workbench documentation identified above should be modified to specify the minimum requirements of EDIT. You have identified the need for a PRIMARY KEY. The documentation should enumerate this along with any other requirements that may exist. Unfortunately, not everyone reads the manual, so while this is probably necessary, it isn't enough.

"Something else?"
Yes! A check is already being performed to determine whether to allow editing or to fall back to displaying the results table. There should be a notification to the user explaining why EDIT refuses to allow editing. A simple notification saying "EDIT refuses to edit this table because it lacks a PRIMARY KEY - use the sql UPDATE statement instead" or a similar appropriate message would be most helpful. As a result of being notified of the problem, users will be able to take the necessary steps to remedy it.

Of course it would be ideal if all legitimate tables were editable with the tool, but I understand some of the reasons why this may be more difficult than it first appears.

Thanks,this resolved my problem on a XP SP3 PC.

It's very difficult to find this bug report, because, due to the lack of any warning, and because is the first time to me using MySQL Workbench, I was thinking I was doing something wrong.

Now I know the "live editing table" needs a primary key, but anyway it's a good idea to put some warning in the next compilation of MySQL Workbench.

Question:
         Why I can't see the year in the field "Submitted" of this bug?

Agreed, I just blew 10 minutes trying to figure this out. Some notification as to why edit doesn't work would be great, makes sense that a primary key is needed. A lot of users probably run into this and just work around it assuming mysql workbench is just buggy, not understanding the rational.

I have same problem, EDIT data does not work. All my tables have a primary key. A screen flash seems to indicate that an edit screen may be loading but it disappears immediately, just leaving the resultset displayed. Another clue may be that the tables dispalyed in the object browser do not display their columns. They did when I first created them and they display all properties when I select "Alter Table". I am using V5.2.25 on WIN7 64bit.

5.2.26 now shows "Error: `db`.`table`: table data is not editable because there is no primary key defined for the table" 

However, InnoDB internally maintains a RowID anyways, so why would it not be possible to use this internal key even in the absence of a defined PK?

More information on InnoDB rowid/clustered index:
http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.html

Also, if WorkBench is able to fetch all rows of a table (for instance: limit is set to 1000, but only 10 rows in a table), then it does not matter if there is a PK or not. Even if we "show the data" or "write the data back" in a different order, it does not matter (row order is not guaranteed in MySQL).

So it's quite possible for WorkBench to show data more times than it does currently, based on the suggestions above.

I have one table where I cannot have a primary key for design reasons (its a kind of backup table).  Workbench behaves in strange ways when accessing tables with no primary key, please correct, this is defenetely a buggy behavior. Rgds

Fixed as of 5.2.36:

+        If a table cannot be editied (e.g., no Primary key), then
+        &workbench; now notifies users of the reason, when before the
+        edit option was simply not available.

The problem still happening on 5.2.37 CE

Seems fine/fixed to me. Workbench says it's Read Only, and hovering over the (i)nformation icon refers to the missing primary key as the cause. I tested 5.2.40 on Mac.