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

Details

Type: Bug
Status: Closed (View Workflow)
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!

Attachments

Activity

Ascending order - Click to sort in descending order

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)

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)

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.

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.

People

Assignee:: Unassigned

Reporter:: Yhojann

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Due:: 2015-01-27

Created:: 2014-12-27 21:26

Updated:: 2015-02-03 19:21

Resolved:: 2015-02-03 19:21

Git Integration

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

MariaDB Server