[MDEV-7386] impossible to use schemas with points - JIRA

Details

Type: Bug
Status: Closed
Priority: Critical
Resolution: Incomplete
Affects Version/s: 10.1.2
Fix Version/s: N/A
Component/s: Data Manipulation - Update
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!

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2014-12-27 23:19

Works fine for me (see below).
Please paste the exact output of the failing statement and attach your SHOW VARIABLES results from the session where the error occurs. Thanks.

MariaDB [test]> select @@version;
+----------------------+
| @@version            |
+----------------------+
| 10.1.2-MariaDB-wsrep |
+----------------------+
1 row in set (0.00 sec)

MariaDB [test]> create database `shoes.tetsing.domain.com`;
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> 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;
Query OK, 0 rows affected (0.51 sec)

MariaDB [test]> 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;
Query OK, 0 rows affected (0.06 sec)

MariaDB [test]> 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;
Query OK, 0 rows affected (0.26 sec)

MariaDB [test]> SET SQL_MODE=@OLD_SQL_MODE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO `shoes.tetsing.domain.com`.`shoes` (`name`) VALUES ('shoe 1');
Query OK, 1 row affected (0.25 sec)

MariaDB [test]> INSERT INTO `shoes.tetsing.domain.com`.`colors` (`color`) VALUES ('black');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> INSERT INTO `shoes.tetsing.domain.com`.`shoes_colors` (`shoe_id`, `color_id`) VALUES (1, 1);
Query OK, 1 row affected (0.04 sec)

Show

Elena Stepanova added a comment - 2014-12-27 23:19 Works fine for me (see below). Please paste the exact output of the failing statement and attach your SHOW VARIABLES results from the session where the error occurs. Thanks. MariaDB [test]> select @@version; +----------------------+ | @@version | +----------------------+ | 10.1.2-MariaDB-wsrep | +----------------------+ 1 row in set (0.00 sec) MariaDB [test]> create database `shoes.tetsing.domain.com`; Query OK, 1 row affected (0.00 sec) MariaDB [test]> SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> 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; Query OK, 0 rows affected (0.51 sec) MariaDB [test]> 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; Query OK, 0 rows affected (0.06 sec) MariaDB [test]> 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; Query OK, 0 rows affected (0.26 sec) MariaDB [test]> SET SQL_MODE=@OLD_SQL_MODE; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> INSERT INTO `shoes.tetsing.domain.com`.`shoes` (`name`) VALUES ('shoe 1'); Query OK, 1 row affected (0.25 sec) MariaDB [test]> INSERT INTO `shoes.tetsing.domain.com`.`colors` (`color`) VALUES ('black'); Query OK, 1 row affected (0.00 sec) MariaDB [test]> INSERT INTO `shoes.tetsing.domain.com`.`shoes_colors` (`shoe_id`, `color_id`) VALUES (1, 1); Query OK, 1 row affected (0.04 sec)

Hide

Permalink

Elena Stepanova added a comment - 2015-02-03 19:21

Closing as 'Incomplete' for now. Please comment to re-open if you have additional information.

Show

Elena Stepanova added a comment - 2015-02-03 19:21 Closing as 'Incomplete' for now. Please comment to re-open if you have additional information.

impossible to use schemas with points

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile