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

Error 1054 on DROP INDEX for tables having virtual column and view referencing it

    XMLWordPrintable

    Details

      Description

      To reproduce create an empty database with the following tables. The tables do not need to contain any data, empty tables are enough. It is important to create the view too, without this view its working.
      After tables and the view are created the `DROP INDEX` statement fails with the following error:

      SQL State  : 42S22
      Error Code : 1054
      Message    : (conn=63) Unknown column '`omega_label`.`du`.`sessionId`' in 'GENERATED ALWAYS'
      Statement  : DROP INDEX uttUser ON dialog_utterances
      

      minimum SQL statements to reproduce:

      CREATE TABLE IF NOT EXISTS `dialog_utterances` (
         `id` bigint(20) NOT NULL AUTO_INCREMENT,
         `sessionId` char(40) NOT NULL,
         `messageNo` smallint NOT NULL,
         `conversationUUID` varchar(50) AS (CONCAT(sessionId, '#', messageNo)) VIRTUAL,
         `uttUser` varchar(300) DEFAULT NULL,
         PRIMARY KEY (`id`),
         KEY (uttUser(191)),
         UNIQUE KEY (`sessionId`, `messageNo`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;
       
      CREATE TABLE IF NOT EXISTS `dialog_tags` (
           `tagId` bigint(20) NOT NULL,
           `dialogId` bigint(20) NOT NULL,
           PRIMARY KEY (`tagId`, `dialogId`),
           FOREIGN KEY fk_dialog_tags__dialog_utterances (dialogId) references `dialog_utterances` (id) ON DELETE CASCADE ON UPDATE NO ACTION
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;
       
      CREATE TABLE IF NOT EXISTS `tags` (
        `id` bigint(20) NOT NULL AUTO_INCREMENT,
        `tag` varchar(191) NOT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY (`tag`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci AUTO_INCREMENT=100;
       
      CREATE OR REPLACE VIEW `dialog_tags_view` AS
      select dt.*, du.uttUser, t.tag
      from dialog_tags dt
           join dialog_utterances du
                on dt.dialogId = du.id
           join tags t
                on dt.tagId = t.id;
       
      DROP INDEX uttUser ON dialog_utterances;
      

      This error happens only with latest versions from the component tags. All versions before (tested 10.2.37 / 10.3.28 / 10.4.18) are not affected.

      All versions tested with docker images from docker hub or local installation on Ubuntu linux.

      I stripped down our own sql migration statements to this 5 steatements. With them i am able to reproduce it on every run.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              stseide Stefan Seide
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration