0

I think my problem might be caused by this old MySQL bug (and this one: /bug.php?id=6295 ), in which they were checking the column constraints before running triggers. But, according to that bug, they fixed it years ago.

I was originally running on MariaDB v10.1.19; but according to this MariaDB ticket: jira.mariadb .org/browse/MDEV-10002, they also fixed this in v10.1.6.

I have tried it on:

[FAILS] -> MariaDB v10.1.19
[FAILS] -> MySQL v5.6
[WORKS] -> MySQL v5.7

Is there perhaps a config problem with my setup that’s causing this to not work in MariaDB? Or is it more likely that the bug got broken again since its original fix? I don’t want to bother the MariaDB devs if it turns out to be my fault.

The Problem

I have a restricted view which filters on a base table using a discriminator column. The view does not contain the discriminator column. In the base table, this column does not allow NULL values.

I created a BEFORE INSERT trigger to populate this column when inserting data through the view, but as far as I can tell, the trigger is not firing. When inserting a record into the view, the DB throws the error:

Field 'groupId' doesn't have a default value

I’ve tried doing several insert tests on the base table, and it looks like the trigger is just not firing at all. I searched around online for other people with this problem, but they all seemed to just be syntax errors. I don’t have much experience with this kind of “deep” SQL, but I think mine’s correct.

Here’s an example implementation, I also made a SQL Fiddle:

P.S. Sorry about the 2 links with spaces in them. StackExchange won't let me post more than 2 links; apparently, I'm 4 reputation short.

CREATE TABLE privateNotes (
  id int(11) NOT NULL,
  userId int(11) NOT NULL,
  text varchar(500) NOT NULL,
  groupId int(11) NOT NULL
);

INSERT INTO privateNotes (id, userId, text, groupId) VALUES
(30, 1, '[John] A Secret Message',         101),
(32, 2, '[George] A Secret Message',       101),
(34, 3, '[Sarah] A Secret Message',        202),
(36, 4, '[Mary] A Secret Message',         202);

-- --------------------------------------------------------

-- Get
CREATE FUNCTION getCurrentGroupId()
    RETURNS int(11)
    NO SQL
    BEGIN
      RETURN @currentGroupId;
    END //

-- Set
CREATE FUNCTION setCurrentGroupId(groupId_in int(11))
    RETURNS int(11)
    NO SQL
    BEGIN
      SET @currentGroupId = groupId_in;
      RETURN @currentGroupId;
    END //

-- --------------------------------------------------------

CREATE VIEW myPrivateNotes AS
SELECT
  privateNotes.id AS id,
  privateNotes.userId AS userId,
  privateNotes.text AS text
FROM
  privateNotes
WHERE
  (
    privateNotes.groupId = getCurrentGroupId() /* 101, 202, etc. */
  );

-- --------------------------------------------------------
-- Trigger

CREATE TRIGGER before_insert_privateNotes
    BEFORE INSERT ON privateNotes
    FOR EACH ROW BEGIN
        IF NEW.groupId IS NULL THEN
            SET NEW.groupId = getCurrentGroupId();
        END IF;
    END //

-- --------------------------------------------------------
-- ------- --
--  TESTS  --
-- ------- --

-- View Insert Test
SELECT setCurrentGroupId(999);
//
-- (Uncomment) Error: Field 'groupId' doesn't have a default value
-- INSERT INTO privateNotes (id, userId, text) VALUES (50, 51, 'a cool message');
//

-- View Select Tests
-- 
-- Copy the full chunk bellow to the Sql box over there ->
-- Don't forget to change the delimeter to: [//]
SELECT setCurrentGroupId(NULL);
//
SELECT * from myPrivateNotes; -- Returns Zero Results [Correct]
//

SELECT setCurrentGroupId(101);
//
SELECT * from myPrivateNotes; -- Returns Valid Results [Correct]
//
0

I opened a ticket with MariaDB. They have confirmed that it's still broken, and it has been put in the backlog to fix it.

Update:

The ticket has been fixed in MariaDB versions: 10.1.21 & 10.2.4

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.