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

impossible to use schemas with points

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Incomplete
    • Affects Version/s: 10.1.2
    • Fix Version/s: N/A
    • Labels:
      None
    • Environment:
      Ubuntu 14.04.1

      Description

      Proof of concept:

      SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
      SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
      SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

      -- -----------------------------------------------------
      -- Table `shoes.tetsing.domain.com`.`shoes`
      -- -----------------------------------------------------
      CREATE TABLE IF NOT EXISTS `shoes.tetsing.domain.com`.`shoes` (
        `id` INT NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(45) NULL,
        PRIMARY KEY (`id`))
      ENGINE = InnoDB;

      -- -----------------------------------------------------
      -- Table `shoes.tetsing.domain.com`.`colors`
      -- -----------------------------------------------------
      CREATE TABLE IF NOT EXISTS `shoes.tetsing.domain.com`.`colors` (
        `id` INT NOT NULL AUTO_INCREMENT,
        `color` VARCHAR(45) NULL,
        PRIMARY KEY (`id`))
      ENGINE = InnoDB;

      -- -----------------------------------------------------
      -- Table `shoes.tetsing.domain.com`.`shoes_colors`
      -- -----------------------------------------------------
      CREATE TABLE IF NOT EXISTS `shoes.tetsing.domain.com`.`shoes_colors` (
        `id` INT NOT NULL AUTO_INCREMENT,
        `shoe_id` INT NOT NULL,
        `color_id` INT NOT NULL,
        PRIMARY KEY (`id`),
        INDEX `fk_shoes_colors_shoes_idx` (`shoe_id` ASC),
        INDEX `fk_shoes_colors_colors1_idx` (`color_id` ASC),
        CONSTRAINT `fk_shoes_colors_shoes`
          FOREIGN KEY (`shoe_id`)
          REFERENCES `shoes.tetsing.domain.com`.`shoes` (`id`)
          ON DELETE CASCADE
          ON UPDATE CASCADE,
        CONSTRAINT `fk_shoes_colors_colors1`
          FOREIGN KEY (`color_id`)
          REFERENCES `shoes.tetsing.domain.com`.`colors` (`id`)
          ON DELETE CASCADE
          ON UPDATE CASCADE)
      ENGINE = InnoDB;

      SET SQL_MODE=@OLD_SQL_MODE;
      SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
      SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

      INSERT INTO `shoes.tetsing.domain.com`.`shoes` (`name`) VALUES ('shoe 1');
      INSERT INTO `shoes.tetsing.domain.com`.`colors` (`color`) VALUES ('black');
      INSERT INTO `shoes.tetsing.domain.com`.`shoes_colors` (`shoe_id`, `color_id`) VALUES (1, 1);

      Error in CONSTRAINT REFERENCES ... Can not insert.

      The problem is:
      REFERENCES `shoes.tetsing.domain.com`.`shoes` (`id`)

      replace by:
      REFERENCES `shoes` (`id`)

      And it works!

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              WHK Yhojann
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Due:
                Created:
                Updated:
                Resolved: