[MDEV-7386] impossible to use schemas with points Created: 2014-12-27  Updated: 2015-02-03  Due: 2015-01-27  Resolved: 2015-02-03

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 10.1.2
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Yhojann Assignee: Unassigned
Resolution: Incomplete Votes: 0
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!

 Comments   
Comment by Elena Stepanova [ 2014-12-27 ]

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)

Comment by Elena Stepanova [ 2015-02-03 ]

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

Generated at Thu Feb 08 07:19:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.