0

I can't get phpmyadmin and MySQL Workbench to allow me to update records in pretty much any view. Any view--even ones without an inner join (which is my ultimate objective)--cannot be edited/updated.

phpmyadmin shows: "Current selection does not contain a unique column. Grid edit, Edit, Copy and Delete features may result in undesired behavior." MySQL Workbench shows: "Read only" in the lower right corner.

(Yes, I know there have been lots of other posts on this topic, but they mostly just link to the MySQL 8 manual, which supposedly defines which views are updatable. But I can't figure out what I'm missing.)

STEPS TO REPRODUCE:

Create a simple table in MySQL 8:

CREATE TABLE `tbl_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Enter some data:

INSERT INTO `tobh_gis`.`tbl_test` (`name`) VALUES ('test');

Create a view based solely on this table, which does have a unique column (id)

CREATE VIEW `view_test` AS
   select 
     `tbl_test`.`id` AS `id`,
     `tbl_test`.`name` AS `name` 
   from `tbl_test`;

Try to edit the data (in phpmysqladmin): Head to "browse" and note the warning at the top of the screen

Try to edit the data (in MySQL Workbench): Select the view to edit--there will be a "Read Only" notice in the lower right-hand corner.

Related questions that are close but don't actually say how to create a view that's updatable in phpmysql or MySQL Workbench:

Unable to create an updatable MySql view

Updating MySQL view fails despite being updatable

MySQL Workbench Edit Table Data is read only (not relevant because my example (above) does have a primary key)

DSM
  • 1
  • 1
  • Does this answer your question? [MySQL Workbench Edit Table Data is read only](https://stackoverflow.com/questions/10815029/mysql-workbench-edit-table-data-is-read-only) – Punit Gajjar Mar 09 '23 at 03:07
  • *```PRIMARY KEY (`id`), UNIQUE KEY `id_UNIQUE` (`id`)```* - what is the reason for to create two index copies? PK provides its expression uniqueness already. – Akina Mar 09 '23 at 04:43
  • Please publish versin numbers for phpmyadmin, mysqlworkbench and mysql also have you established the view is updateable by updating via query window in mysqlworkbench? And what kind of edit are you trying to do? – P.Salmon Mar 09 '23 at 07:41
  • Punit: No, it doesn't answer my question. As you can see, I defined 'id' as a primary key for the table. And I've read elsewhere that one cannot identify a 'primary key' for a View. – DSM Mar 09 '23 at 10:45
  • Akina: I set both in order to forestall any complaints about there not being a unique key. I understand that a primary key needs to be unique, but the phpmyadmin error indicates 'no unique key' (or somesuch), so I added that index as well. The same (non-updateable) behaviour exists if you just set a primary key or if you just set 'id' to be UNIQUE. – DSM Mar 09 '23 at 10:47
  • P. Salmon: phpmyadmin: 5.1.1deb5ubuntu1 MySQL: 8.0.32-0ubuntu0.22.04.2 Workbench: 8.0.30 INSERT INTO view_test (name ) VALUES ('test3'); works fine so, yes, the view is actually updateable (thanks for making me test that). But neither phpmyadmin nor Workbench will let me do the same thing via their UI. (As you've just made me demonstrate, I could execute the underlying SQL statements to do it.) So I guess this is a problem with phpmyadmin and Workbench? If someone can confirm that newer releases fix this issue, I'd be happy to update. – DSM Mar 09 '23 at 10:56
  • Upgraded Workbench to 8.0.32. Same problematic behavior ('Read only' displayed in lower right-hand corner of the browse/edit panel). – DSM Mar 09 '23 at 11:03
  • @dsm if you want to get the attention of a commentator include the at symbol prior to their handle. – P.Salmon Mar 09 '23 at 13:43

0 Answers0