[MDEV-9629] Disappearing PRI from Key column after creating a trigger Created: 2016-02-25  Updated: 2016-03-22  Resolved: 2016-03-22

Status: Closed
Project: MariaDB Server
Component/s: Triggers
Affects Version/s: 10.1.11
Fix Version/s: 10.1.13

Type: Bug Priority: Major
Reporter: Lubomir Daniel Fedor Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: mariadb, primary_key, trigger
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.

 Comments   
Comment by Risato Stefano [ 2016-03-09 ]

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.

Generated at Thu Feb 08 07:36:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.