MySQL – Trigger Not Firing: ‘Field ‘__’ Doesn’t Have a Default Value’

functionsMySQLstored-procedurestriggerview

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]
//

Best Answer

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

Related Question