Unable to create an updatable MySql view

Question

I am trying to create an updatable view in MySql, but the result is not updatable. Attrepgen is a table where the first field (aidx = numauto) is the PK.

Any idea why attrepusr is not updatable ?

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`%` 
    SQL SECURITY DEFINER
VIEW `attrepusr` AS
    select 
        `attrepgen`.`Aidx` AS `Aidx`,
        `attrepgen`.`IsOffset` AS `IsOffset`,
        `attrepgen`.`Dt` AS `Dt`,
        `attrepgen`.`Owner` AS `Owner`,
        `attrepgen`.`P1` AS `P1`,
        `attrepgen`.`P2` AS `P2`,
        `attrepgen`.`P3` AS `P3`,
        `attrepgen`.`P4` AS `P4`,
        `attrepgen`.`P5` AS `P5`,
        `attrepgen`.`P6` AS `P6`,
        `attrepgen`.`Target` AS `Target`,
        `attrepgen`.`Attend` AS `Attend`,
        `attrepgen`.`Delta` AS `Delta`,
        `attrepgen`.`Remark` AS `Remark`,
        `attrepgen`.`Cat` AS `Cat`
    from
        `attrepgen`
    where
        (`attrepgen`.`Owner` = current_user())

for info, the table attrepgen :

CREATE TABLE `attrepgen` (
  `Aidx` int(11) NOT NULL AUTO_INCREMENT,
  `IsOffset` tinyint(1) NOT NULL,
  `Dt` date NOT NULL,
  `Owner` varchar(16) NOT NULL,
  `P1` time DEFAULT NULL,
  `P2` time DEFAULT NULL,
  `P3` time DEFAULT NULL,
  `P4` time DEFAULT NULL,
  `P5` time DEFAULT NULL,
  `P6` time DEFAULT NULL,
  `Target` decimal(7,3) NOT NULL,
  `Attend` decimal(7,3) NOT NULL,
  `Delta` decimal(7,3) NOT NULL,
  `Remark` varchar(32) DEFAULT NULL,
  `Cat` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`Aidx`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

I am a bit puzzled.

My guess is that `current_user()` is the problem. Try doing this with a constant value there instead. If that works, then you know that you can't use `current_user()`. — Austin, Jul 01 '15 at 08:25
This view seems to be updateable (http://www.sqlfiddle.com/#!9/dfd9d/2). Perhaps there is a phrasing issue. Could it be that the view is indeed updateable (as in there is no error), but the record is actually not inserted? — RandomSeed, Jul 01 '15 at 08:55
I am trying to update a sample line directly from Mysql workbench, changing only a single char in remark, and I receive a "Read Only" message with an exclamation mark. — Philippe Huysmans, Jul 01 '15 at 09:02

score 0 · Answer 1 · edited Jul 01 '15 at 09:24

Okay, got it. Must say that my base has in fact a front-end in Access. When trying to modify a field in the workbench, that idiot does not accept (keeps saying 'read only"). But when I re-linked my View in my front-end, this time, I could modify the line from there.

Probably that the workbench simply does not allow to modify in the views, but want you to modify directly in the source table.

Better to know it.

Thanks to all of you, it is good to know that we are not alone (in the dark) :p

Unable to create an updatable MySql view

1 Answers1

Linked