Bug #99026 Select Statement is editable, but View based on this statement is read-only
Submitted: 23 Mar 0:18 Modified: 23 Mar 7:58
Reporter: Jonathan Ornstein Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S2 (Serious)
Version: 8.0.19, 8.0.20 OS:Ubuntu (18.04)
Assigned to: CPU Architecture:x86

[23 Mar 0:18] Jonathan Ornstein
Description:
When I create select statement a View based on a single table which has a primary key, executing this select statement yields a perfectly editable recordset in MySQL workbench. However, when I create a View based on this exact same Select statement, the dataset is NOT editable anymore upon execution of the View. 

Strangely, when I create a query from an external application such as Ms Access on this table using ODBC, it IS possible to create an editable version of the query.

  

How to repeat:

  1) Create a database "testdb"
2) create a table with 3 columns: `ID` (=PK) , `FldDescription`  , `ForeignID` 
CREATE TABLE `testdb`.`new_table` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `FldDescription` VARCHAR(45) NULL,
  `ForeignID` INT NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE INDEX `ID_UNIQUE` (`ID` ASC) VISIBLE);
3) create and execute a select statement on the new table and add some data
a) SELECT * FROM testdb.new_table;
b) insert some dummy data:
INSERT INTO `testdb`.`new_table` (`FldDescription`, `ForeignID`) VALUES ('hello world', '12');
INSERT INTO `testdb`.`new_table` (`FldDescription`, `ForeignID`) VALUES ('hello again', '25');

4) now create a view based on th is table:
USE `testdb`;
CREATE  OR REPLACE VIEW `new_view` AS
SELECT * FROM testdb.new_table;

5) Save and open this view:
SELECT * FROM testdb.new_view;

6) the data is now read-only. I have tried this MANY times.

Suggested fix:
no idea. workaround using external dbms Access is a security nightmare.
[23 Mar 7:06] MySQL Verification Team
Hello Jonathan,

Thank you for the report.

regards,
Umesh
[23 Mar 7:58] Jonathan Ornstein
just to specify: identical installations of MySQL Server 8.0.19 on 3 VPS servers running Ubuntu 18.04 Bionic all show the same problem. I run Workbench on a Windows 10 Pro machine. regards!
[13 May 11:21] MySQL Verification Team
Bug #99549 marked as duplicate of this one