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

impossible to use schemas with points

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Incomplete
    • 10.1.2
    • N/A
    • None
    • 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

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.