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

Generated column DELETE with FOREIGN KEY crash InnoDB

    XMLWordPrintable

    Details

      Description

      Reported via HackerOne by Petr Gregor (gregy)

      I discovered a sequence of sql commands which will crash mariadb server upon execution. I have replicated the problem with mariadb 10.5.6, mysql 8 and mysql 5.7 both in docker and when using a full VM. I am able to trigger the crash as a remote user with full access to a single database.

      SQL to replicate the crash:

      create database ctest;
      use ctest;
       
      DROP TABLE IF EXISTS `email_stats`;
      DROP TABLE IF EXISTS `emails_metadata`;
      DROP TABLE IF EXISTS `emails`;
       
      CREATE TABLE `emails` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
       
      CREATE TABLE `email_stats` (
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        `email_id` int(10) unsigned DEFAULT NULL,
        `date_sent` datetime NOT NULL,
        `generated_sent_date` date GENERATED ALWAYS AS (concat(year(`date_sent`),'-',lpad(month(`date_sent`),2,'0'),'-',lpad(dayofmonth(`date_sent`),2,'0'))) VIRTUAL,
        PRIMARY KEY (`id`),
        KEY `IDX_D0F71136A832C1C9` (`email_id`),
        KEY `mautic_generated_sent_date_email_id` (`generated_sent_date`,`email_id`),
        CONSTRAINT `FK_D0F71136A832C1C9` FOREIGN KEY (`email_id`) REFERENCES `emails` (`id`) ON DELETE SET NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
       
      CREATE TABLE `emails_metadata` (
        `email_id` int(10) unsigned NOT NULL,
        PRIMARY KEY (`email_id`),
        CONSTRAINT `FK_C79476FDA832C1C9` FOREIGN KEY (`email_id`) REFERENCES `emails` (`id`) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
       
       
      INSERT INTO `emails` VALUES (1);
      INSERT INTO `email_stats` (`id`, `email_id`,  `date_sent`) VALUES (1,1,'2020-10-22 13:32:41');
      INSERT INTO `emails_metadata` VALUES (1);
       
      COMMIT;
       
      DELETE FROM emails;
      

      The easiest way to replicate the problem is using these docker commands and pasting the above script into mysql shell:

      docker run --rm  -e MYSQL_ALLOW_EMPTY_PASSWORD=yes --name mtest mariadb:latest &
      docker exec -it mtest mysql
      

      I do not have implementation level details about why this is happening. It is an accidental discovery.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              nikitamalyavin Nikita Malyavin
              Reporter:
              cvicentiu Vicențiu Ciorbaru
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration