MySQL Bugs: #34253: import sql workbech script generates errno 121

Bug #34253	import sql workbech script generates errno 121
Submitted:	2 Feb 2008 20:20	Modified:	3 Feb 2008 17:28
Reporter:	Pieter Claes	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.012.OSS Beta	OS:	Windows (XP )
Assigned to:		CPU Architecture:	Any
Tags:	errno 121, export script, foreign keys

Description:
Hello,

Workbench gives met the following script:
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';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb`;

-- -----------------------------------------------------
-- Table `mydb`.`country`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`country` (
  `idcountry` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `country` VARCHAR(45) CHARACTER SET 'latin1' NOT NULL ,
  PRIMARY KEY (`idcountry`) )
ENGINE = InnoDB
AUTO_INCREMENT = 142
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

-- -----------------------------------------------------
-- Table `mydb`.`language`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`language` (
  `idlanguage` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `language` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ,
  PRIMARY KEY (`idlanguage`) )
ENGINE = InnoDB
AUTO_INCREMENT = 32
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

-- -----------------------------------------------------
-- Table `mydb`.`user`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`user` (
  `iduser` MEDIUMINT(5) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `first_name` VARCHAR(45) NOT NULL ,
  `last_name` VARCHAR(45) NOT NULL ,
  `user_name` VARCHAR(10) NOT NULL ,
  `password` VARCHAR(20) NOT NULL ,
  `email` VARCHAR(45) NOT NULL ,
  `gender` VARCHAR(10) NULL DEFAULT NULL ,
  `birth_year` SMALLINT(4) UNSIGNED NULL DEFAULT NULL ,
  `account_activated` VARCHAR(4) NOT NULL DEFAULT 'no' ,
  `date_registered` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`iduser`) )
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1
COMMENT = 'Table containing data of the registered users';

-- -----------------------------------------------------
-- Table `mydb`.`link_usr_ctry`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`link_usr_ctry` (
  `idlink_usr_ctry` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `iduser` MEDIUMINT(5) UNSIGNED NOT NULL ,
  `idcountry` TINYINT(3) UNSIGNED NOT NULL ,
  PRIMARY KEY (`idlink_usr_ctry`) ,
  INDEX iduser (`iduser` ASC) ,
  INDEX idcountry (`idcountry` ASC) ,
  CONSTRAINT `iduser`
    FOREIGN KEY (`iduser` )
    REFERENCES `mydb`.`user` (`iduser` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `idcountry`
    FOREIGN KEY (`idcountry` )
    REFERENCES `mydb`.`country` (`idcountry` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1; 

When I import through phpMyAdmin I get the following error:

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';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb`;

-- -----------------------------------------------------
-- Table `mydb`.`country`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`country` (
  `idcountry` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `country` VARCHAR(45) CHARACTER SET 'latin1' NOT NULL ,
  PRIMARY KEY (`idcountry`) )
ENGINE = InnoDB
AUTO_INCREMENT = 142
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

-- -----------------------------------------------------
-- Table `mydb`.`language`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`language` (
  `idlanguage` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `language` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ,
  PRIMARY KEY (`idlanguage`) )
ENGINE = InnoDB
AUTO_INCREMENT = 32
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

-- -----------------------------------------------------
-- Table `mydb`.`user`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`user` (
  `iduser` MEDIUMINT(5) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `first_name` VARCHAR(45) NOT NULL ,
  `last_name` VARCHAR(45) NOT NULL ,
  `user_name` VARCHAR(10) NOT NULL ,
  `password` VARCHAR(20) NOT NULL ,
  `email` VARCHAR(45) NOT NULL ,
  `gender` VARCHAR(10) NULL DEFAULT NULL ,
  `birth_year` SMALLINT(4) UNSIGNED NULL DEFAULT NULL ,
  `account_activated` VARCHAR(4) NOT NULL DEFAULT 'no' ,
  `date_registered` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`iduser`) )
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1
COMMENT = 'Table containing data of the registered users';

-- -----------------------------------------------------
-- Table `mydb`.`link_usr_ctry`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`link_usr_ctry` (
  `idlink_usr_ctry` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `iduser` MEDIUMINT(5) UNSIGNED NOT NULL ,
  `idcountry` TINYINT(3) UNSIGNED NOT NULL ,
  PRIMARY KEY (`idlink_usr_ctry`) ,
  INDEX iduser (`iduser` ASC) ,
  INDEX idcountry (`idcountry` ASC) ,
  CONSTRAINT `iduser`
    FOREIGN KEY (`iduser` )
    REFERENCES `mydb`.`user` (`iduser` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `idcountry`
    FOREIGN KEY (`idcountry` )
    REFERENCES `mydb`.`country` (`idcountry` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;

All but one table are generated ; when I take the foreign key relationships out, the tables are properly imported.

version information:

localhost:
    * Server version: 5.0.45-community-nt
    * Protocol version: 10
phpMyAdmin - 2.10.1
    * MySQL client version: 5.0.45
    * Used PHP extensions: mysqli

How to repeat:
this is fairly consistent once you've set up one parent-child relationship and then try to set up a second one.

Suggested fix:
Set all the foreign key relationships up in phpMyAdmin instead of Workbench

It must be a bug with phpMyAdmin then (of which nobody else than the phpmyadmin communisty is responsible)

The script seems OK to me.  It can be imported to the server with Command Line as well as SQLyog (and try more yourself if you like!).

See Schema in SQLyog Schema Designer after import attached!

PhpMyAdmin is NOT an 'authoritative' client!  You should report this bug to the phpMyAdmin community, if it is not fixed in the most recent version!  And actually phpMyAdmin always had a weird way of handling of Foreign Keys in backup/restore!!

Peter
(not a MySQL person)

script imported OK

Attachment: fks.gif (image/gif, text), 25.32 KiB.