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

Update Table Fails with Trigger and Stored Function

    Details

      Description

      In upgrading my MariaDB Server from version 10.2.23 to 10.2.24 I ran into a problem that resulted in me having to revert my environment back to 10.2.23.

      I have a table with a before update trigger that references a stored function. Prior to MariaDB 10.2.24, the following code ran successfully:

      -- BUG TEST CASE
      -- 1. test schema
      DROP SCHEMA IF EXISTS testdata;
      CREATE SCHEMA IF NOT EXISTS testdata;
      USE testdata;
       
      -- 2. test table with a column that is set by a trigger value
      DROP TABLE IF EXISTS test_table;
      CREATE TABLE test_table (
      	`ID` int(11) not null AUTO_INCREMENT,
          `Name` varchar(150) not null,
          `TriggerSet` varchar(75),
      	PRIMARY KEY (`ID`)
      ) ENGINE = InnoDB;
       
      -- 3. stored function that does something
      DELIMITER $$
      DROP FUNCTION IF EXISTS test$$
      CREATE DEFINER = `root`@`localhost` FUNCTION `test`()
      RETURNS VARCHAR(50)
      READS SQL DATA
      BEGIN
      	RETURN 'Result';
      END$$
      DELIMITER ;
       
      -- 4. the trigger that calls the stored function and sets the result to a column value 
      DELIMITER $$
      DROP TRIGGER IF EXISTS testdata.test_table_update_before$$
      CREATE TRIGGER testdata.test_table_update_before BEFORE UPDATE ON testdata.test_table
      FOR EACH ROW
      BEGIN
      	SET NEW.TriggerSet = (SELECT testdata.test());
      END$$
       
      DELIMITER ;
       
      -- 5. test data
      INSERT INTO testdata.test_table (Name)
      VALUES
      ('1'), ('2'), ('3'), ('4'), ('5');
       
      -- 6. a query to form another table that has modifications
      DROP TABLE IF EXISTS scratch.`modify`;
      CREATE TABLE scratch.modify (PRIMARY KEY (`ID`))
      SELECT ID, CONCAT(Name,'_') Name FROM testdata.test_table WHERE ID BETWEEN 2 AND 4;
       
      -- 7. this update FAILS in MariaDB 10.2.24 but not 10.2.23
      UPDATE testdata.test_table t 
      INNER JOIN scratch.modify m USING (ID) 
      SET t.Name = m.Name;
       
      -- 8. the resulting table that should have the modifications, but in 10.2.24, the update statement above fails
      SELECT * FROM testdata.test_table;
       
      -- 9. this is the same function that is called in the trigger, it succeeds when called on its own
      SELECT testdata.test();
      

      In MariaDB 10.2.24, the update statement in step 7 does not execute, you get the error "FUNCTION testdata.test does not exist". However, if you call the exact same function (what is done in step 9) it succeeds.

      A couple of things that might be worth knowing. First, updating the table in a non-joined update statement works fine:

      -- this functions in 10.2.24 and the triggerset column is populated with the result of the stored -- function
      UPDATE testdata.test_table SET Name = '1_1' WHERE ID = 1;
      

      Also potentially worth noting, if you run the non-joined update statement, the immediately execute the bug test case, everything works fine. However if you restart the MariaDB daemon and you re-run the test case it fails. This leads me to believe that the non-joined update statement is pulling the table into cache which allows the joined statement to succeed. However it only succeeds temporarily.

      Please let me know if I can provide any additional information.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                alice Alice Sherepa
                Reporter:
                brycejlowe Bryce Lowe
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: