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