MySQL Bugs: #52705: Edit Table Data - Change doesn't get applied

Bug #52705	Edit Table Data - Change doesn't get applied
Submitted:	8 Apr 2010 23:51	Modified:	4 May 2010 14:25
Reporter:	Richard Creek	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	5.2.17, 5.2.18	OS:	Linux (Ubuntu 9.10 64bit)
Assigned to:	Alfredo Kojima	CPU Architecture:	Any

Description:
Using the SQL Editor to edit existing data in a table. The data can be altered in the results area, and to the right of the 'filter' field it says 'updated 1'.

Try to close the SQL Editor tab, it tells you there are uncommitted changes, and asks if you'd like to review or discard them. click 'review' and no indication of changes awaiting committment is shown.  Try clicking the tick icon to commit the changes and nothing happens. Try to close the SQL Editor tab again, still get the message about uncommitted changes.

How to repeat:
Right click a table
Select Edit Table Data
Double click on any field in the results displayed
Edit the value and hit return
It says updated 1, to the right of 'Fetched x records'
Click commit icon - nothing happens.
Click the close icon on the 'SQL Editor' tab
Warning of uncommitted changes pops up.

Suggested fix:
The changes made in the gui need updating to the database.

Thank you for the problem report. Please, check with a newer version, 5.2.18, and inform about the results.

Please, report any your findings. Maybe non-updatable tables are those without PRIMARY KEY defined?

It's a known limitation as far as I understand. 

We have to build a set of UPDATE statements based on changes in the grid, and for it to be correct we have to identify every changed row. This is what PRIMARY KEY is used for - to identify the row. No primary key - no correct identification, in general case.

Stopping user from editing table if it does NOT have a primary key looks like a great feature to have.

mac/win already display a readonly-grid in these cases, so we'd adjust linux version too.

Bug #53100 has been marked as duplicate of this one

I had this crop up in 5.2.19 (Ubuntu 9.10, 32 bit, MySQL Ubuntu package).  When editing the table, I had failed to realize that I had _not_, in fact, created a primary key for the table.  I had, however, created a two-column unique index for the table.  Setting these columns up as a multi-column primary key allowed the table data to be edited easily.

If you please, I suggest that the development team place an indicator of some sort on the dataset toolbar which unambiguously indicates that table data cannot be edited without the presence of a primary key.  Also related, though in a different segment, should be a warning which is thrown before committing a table create and/or change which results in the lack of a primary key for a table.

Fixed Linux editor so that RO recordsets dont allow editing

fix confirmed in repository

An entry has been added to the 5.2.21 changelog:

In the SQL Editor, if the table data was edited, and then an attempt made to apply the changes, the changes were not applied.