Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Incomplete
-
10.1.2
-
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
Field | Original Value | New Value |
---|---|---|
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! |
Proof of concept: {code:sql} 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'; {code} {code:sql} -- ----------------------------------------------------- -- 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; {code} {code:sql} -- ----------------------------------------------------- -- 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; {code} {code:sql} -- ----------------------------------------------------- -- 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; {code} {code:sql} SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; {code} {code:sql} 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); {code} 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! |
Description |
Proof of concept: {code:sql} 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'; {code} {code:sql} -- ----------------------------------------------------- -- 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; {code} {code:sql} -- ----------------------------------------------------- -- 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; {code} {code:sql} -- ----------------------------------------------------- -- 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; {code} {code:sql} SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; {code} {code:sql} 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); {code} 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! |
Proof of concept: {code:sql} 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'; {code} {code:sql} -- ----------------------------------------------------- -- 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; {code} {code:sql} -- ----------------------------------------------------- -- 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; {code} {code:sql} -- ----------------------------------------------------- -- 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; {code} {code:sql} SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; {code} {code:sql} 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); {code} 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! |
Due Date | 2015-01-27 |
Fix Version/s | N/A [ 14700 ] | |
Resolution | Incomplete [ 4 ] | |
Status | Open [ 1 ] | Closed [ 6 ] |
Workflow | MariaDB v2 [ 59084 ] | MariaDB v3 [ 65103 ] |
Workflow | MariaDB v3 [ 65103 ] | MariaDB v4 [ 148665 ] |
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.
| @@version |
| 10.1.2-MariaDB-wsrep |
Query OK, 1 row affected (0.00 sec)
-> ENGINE = InnoDB;
-> ENGINE = InnoDB;
-> ENGINE = InnoDB;
Query OK, 1 row affected (0.25 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.04 sec)