Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9629

Disappearing PRI from Key column after creating a trigger

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.11
    • Fix Version/s: 10.1.13
    • Component/s: Triggers
    • Environment:
      Ubuntu 15.10, Brfs filesystem, InnoDB (XtraDB), single database server, no replication, root access, all commands entered separately through the command line (bash) after logging through mysql command
    • Sprint:
      10.1.13

      Description

      I create a table on an empty database. Table is called Detail, has 4 columns with first one as a primary key:

      CREATE TABLE `Detail` ( `idDetail` INT(11) NOT NULL AUTO_INCREMENT , `price` FLOAT NOT NULL DEFAULT '0' , `shipping` FLOAT NOT NULL DEFAULT '0' , `wholePrice` FLOAT NOT NULL DEFAULT '0' , PRIMARY KEY (`idDetail`)) ENGINE = InnoDB CHARACTER SET utf8;

      I insert a single record and display column info:

      INSERT INTO `Detail` (`idDetail`, `price`, `shipping`, `wholePrice`) VALUES (NULL, '5', '3', '8');
      SHOW FULL COLUMNS FROM Detail;

      There is PRI in KEY column of idDetail (as it should be). I then create a trigger on INSERT and display column info again:

      CREATE TRIGGER `suma_I_Detail` BEFORE INSERT ON `Detail` FOR EACH ROW SET NEW.wholePrice = NEW.price + NEW.shipping;
      SHOW FULL COLUMNS FROM Detail;

      No change, PRI is still where it should be. I then execute an update on a single and only row. Update should neither activate the trigger, nor actually change anything (since the value in the row is the same as in the update).
      After update I display column info once again:

      UPDATE Detail SET price = 5 WHERE idDetail = 1;
      SHOW FULL COLUMNS FROM Detail;

      Suddenly PRI is missing from KEY column. I execute the very same update again and then show columns again.

      UPDATE  Detail SET price = 5 WHERE idDetail = 1;
      SHOW FULL COLUMNS FROM Detail;

      PRI is back in the KEY column. If I then again execute update, PRI is gone, and then after another one is back. Same behaviour when I execute INSERT.

      I then DROP the trigger, update the record and show columns again twice :

      DROP TRIGGER IF EXISTS `suma_I_Detail`;
      UPDATE Detail SET price = 5 WHERE idDetail = 1;
      SHOW FULL COLUMNS FROM Detail;
      UPDATE Detail SET price = 5 WHERE idDetail = 1;
      SHOW FULL COLUMNS FROM Detail;

      After the trigger is dropped, SHOW columns always correctly lists PRI in Key column for idDetail.

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            Danny_Fedor Lubomir Daniel Fedor
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration