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

Disappearing PRI from Key column after creating a trigger

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1.11
    • 10.1.13
    • Triggers
    • 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
    • 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

          steris Risato Stefano added a comment - - edited

          I confirm this bug with my installation. I was creating a new bug, before finding this one. Here the text I was writing, if it can help...

          After migration from windows (2003 server 32 bit - MariaDB 10.0.24) to linux (Ubuntu server 15.10 64 bit - MariaDB 10.1.12), I had a problem saving records from web pages. The client uses the .Net connector. After investigating, I found that a table with a trigger doesn't report correctly the information about the primary key to client after an insert or update of a record. To reproduce the bug, try this steps (it is not dependant on the db engine used):

          CREATE TABLE test (
          		 
          	a INT NOT NULL,
          		 
          	PRIMARY KEY (a)
          		 
          );
          		 
           
          		 
          INSERT INTO test (a) VALUES (1);

          perform SELECT * FROM test, the informations about the primary key are correct

          INSERT INTO test (a) VALUES (2);

          perform SELECT * FROM test, the informations about the primary key are correct

          CREATE TRIGGER test_trigger BEFORE UPDATE ON test FOR EACH ROW 
          BEGIN
          		 
          END;
          		 
           
          		 
          INSERT INTO test (a) VALUES (3);

          perform SELECT * FROM test, the informations about the primary key are NOT correct, client reports no primary key.

          Doing an OPTIMIZE TABLE test, informations are then correct, but only for one INSERT. After this the problem reappear.

          steris Risato Stefano added a comment - - edited I confirm this bug with my installation. I was creating a new bug, before finding this one. Here the text I was writing, if it can help... After migration from windows (2003 server 32 bit - MariaDB 10.0.24) to linux (Ubuntu server 15.10 64 bit - MariaDB 10.1.12), I had a problem saving records from web pages. The client uses the .Net connector. After investigating, I found that a table with a trigger doesn't report correctly the information about the primary key to client after an insert or update of a record. To reproduce the bug, try this steps (it is not dependant on the db engine used): CREATE TABLE test ( a INT NOT NULL , PRIMARY KEY (a) );   INSERT INTO test (a) VALUES (1); perform SELECT * FROM test , the informations about the primary key are correct INSERT INTO test (a) VALUES (2); perform SELECT * FROM test , the informations about the primary key are correct CREATE TRIGGER test_trigger BEFORE UPDATE ON test FOR EACH ROW BEGIN END ;   INSERT INTO test (a) VALUES (3); perform SELECT * FROM test , the informations about the primary key are NOT correct, client reports no primary key. Doing an OPTIMIZE TABLE test , informations are then correct, but only for one INSERT. After this the problem reappear.

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.