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

Problem with modifing tables with STORED columns used in procedures

    XMLWordPrintable

    Details

      Description

      After starting to install 10.5 on some instances we've noticed a problem with inserting and upgrading old databases.
      Meaning the same procedure would work on 10.4 but it would throw and error Unknown column on 10.5.
      We've striped down a simple SQL to reproduce the problem:

      CREATE TABLE `A` (
        `AC1` int(11) PRIMARY KEY,
        `U_ID` int(11) NOT NULL,
        `U_N` int(11) GENERATED ALWAYS AS (`U_ID`) STORED
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      CREATE TABLE IF NOT EXISTS B(
        B_ID int(11) AUTO_INCREMENT PRIMARY KEY,
        BC1 int(11) NOT NULL
      );
       
      DELIMITER ;;
      CREATE OR REPLACE PROCEDURE proc_migrate()
      BEGIN
        DECLARE done INT DEFAULT 0;
        DECLARE p_AC1 int(11);
        DECLARE cur1 CURSOR FOR SELECT AC1 FROM A BU;
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
       
        OPEN cur1;
        REPEAT
          FETCH cur1 INTO p_AC1;
          IF NOT done THEN
            BEGIN
              INSERT INTO B (BC1) VALUES(p_AC1);
            END;
          END IF;
        UNTIL done END REPEAT;
        CLOSE cur1;
      END;;
      DELIMITER ;
       
      CALL proc_migrate();
       
      ALTER TABLE A MODIFY COLUMN AC1 int(11) NOT NULL;
      

      This would throw 'Unknown column '`MDEV_NEW`.`BU`.`U_ID`' in 'GENERATED ALWAYS' on 10.5, but will work fine on 10.4

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              lukav Anton Avramov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration