(errno: 121) in MySQL

2

Problems compiling:

  

Query OK, 0 rows affected (0.00 sec)

     

Query OK, 0 rows affected (0.00 sec)

     

Query OK, 0 rows affected (0.00 sec)

     

Query OK, 1 row affected, 1 warning (0.00 sec)

     

Database changed

     

Query OK, 0 rows affected, 1 warning (0.00 sec)

     

Query OK, 0 rows affected, 1 warning (0.00 sec)

     

Query OK, 0 rows affected, 1 warning (0.00 sec)

     

ERROR 1005 (HY000): Can not create table 'dba1_s4a.pelicula' (errno:   121) ERROR 1005 (HY000): Can not create table 'dba1_s4a.ejemplar'   (errno: 121) Query OK, 0 rows affected, 1 warning (0.00 sec)

     

ERROR 1005 (HY000): Can not create table 'dba1_s4a.renta' (errno: 121)   ERROR 1005 (HY000): Can not create table 'dba1_s4a.reparto' (errno: 121)   Query OK, 0 rows affected (0.00 sec)

     

Query OK, 0 rows affected (0.00 sec)

     

Query OK, 0 rows affected (0.00 sec)

MySQL Code:

SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @[email protected]@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema dba1_s4a
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema dba1_s4a
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS 'dba1_s4a' DEFAULT CHARACTER SET latin1 ;
USE 'dba1_s4a' ;

-- -----------------------------------------------------
-- Table 'dba1_s4a'.'actor'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'dba1_s4a'.'actor' (
'actor' INT(3) NOT NULL COMMENT '',
'nombre' VARCHAR(30) NULL DEFAULT NULL COMMENT '',
'nacionalidad' VARCHAR(15) NULL DEFAULT NULL COMMENT '',
PRIMARY KEY ('actor') COMMENT '')
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table 'dba1_s4a'.'director'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'dba1_s4a'.'director' (
'director' INT(3) NOT NULL COMMENT '',
'nombre' VARCHAR(30) NULL DEFAULT NULL COMMENT '',
'nacionalidad' VARCHAR(15) NULL DEFAULT NULL COMMENT '',
PRIMARY KEY ('director') COMMENT '')
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table 'dba1_s4a'.'genero'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'dba1_s4a'.'genero' (
'genero' INT NOT NULL COMMENT '',
'nombre' VARCHAR(45) NULL COMMENT '',
PRIMARY KEY ('genero') COMMENT '')
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'dba1_s4a'.'pelicula'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'dba1_s4a'.'pelicula' (
'pelicula' INT(3) NOT NULL COMMENT '',
'titulo' VARCHAR(40) NULL DEFAULT NULL COMMENT '',
'nacionalidad' VARCHAR(15) NULL DEFAULT NULL COMMENT '',
'fecha' DATE NULL DEFAULT NULL COMMENT '',
'sinopsis' MEDIUMTEXT NULL DEFAULT NULL COMMENT '',
'director' INT(3) NULL DEFAULT NULL COMMENT '',
'genero' INT NOT NULL COMMENT '',
PRIMARY KEY ('pelicula') COMMENT '',
INDEX 'dirige' ('director' ASC) COMMENT '',
INDEX 'genero1_idx' ('genero' ASC) COMMENT '',
CONSTRAINT 'dirige'
FOREIGN KEY ('director')
REFERENCES 'dba1_s4a'.'director' ('director'),
CONSTRAINT 'genero1'
FOREIGN KEY ('genero')
REFERENCES 'dba1_s4a'.'genero' ('genero')
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table 'dba1_s4a'.'ejemplar'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'dba1_s4a'.'ejemplar' (
'noejemplar' INT(4) NOT NULL COMMENT '',
'estado' VARCHAR(15) NULL DEFAULT NULL COMMENT '',
'pelicula' INT(3) NULL DEFAULT NULL COMMENT '',
PRIMARY KEY ('noejemplar') COMMENT '',
INDEX 'dispone' ('pelicula' ASC) COMMENT '',
CONSTRAINT 'dispone'
FOREIGN KEY ('pelicula')
REFERENCES 'dba1_s4a'.'pelicula' ('pelicula'))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table 'dba1_s4a'.'socio'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'dba1_s4a'.'socio' (
'nosocio' INT(4) NOT NULL COMMENT '',
'nombre' VARCHAR(30) NULL DEFAULT NULL COMMENT '',
'direccion' VARCHAR(80) NULL DEFAULT NULL COMMENT '',
'telefono' VARCHAR(15) NULL DEFAULT NULL COMMENT '',
PRIMARY KEY ('nosocio') COMMENT '')
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table 'dba1_s4a'.'renta'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'dba1_s4a'.'renta' (
'noejemplar' INT(4) NOT NULL DEFAULT '0' COMMENT '',
'nosocio' INT(4) NOT NULL DEFAULT '0' COMMENT '',
'frenta' DATE NULL DEFAULT NULL COMMENT '',
'fdev' DATE NULL DEFAULT NULL COMMENT '',
'precio' FLOAT NULL DEFAULT NULL COMMENT '',
PRIMARY KEY ('noejemplar', 'nosocio') COMMENT '',
INDEX 'alquila' ('nosocio' ASC) COMMENT '',
CONSTRAINT 'alquila'
FOREIGN KEY ('nosocio')
REFERENCES 'dba1_s4a'.'socio' ('nosocio'),
CONSTRAINT 'es_alquilado'
FOREIGN KEY ('noejemplar')
REFERENCES 'dba1_s4a'.'ejemplar' ('noejemplar'))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table 'dba1_s4a'.'reparto'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'dba1_s4a'.'reparto' (
'pelicula' INT(3) NOT NULL DEFAULT '0' COMMENT '',
'actor' INT(3) NOT NULL DEFAULT '0' COMMENT '',
'papel' VARCHAR(20) NULL DEFAULT NULL COMMENT '',
PRIMARY KEY ('pelicula', 'actor') COMMENT '',
INDEX 'actua' ('actor' ASC) COMMENT '',
CONSTRAINT 'actua'
FOREIGN KEY ('actor')
REFERENCES 'dba1_s4a'.'actor' ('actor'),
CONSTRAINT 'actuan'
FOREIGN KEY ('pelicula')
REFERENCES 'dba1_s4a'.'pelicula' ('pelicula'))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


SET [email protected]_SQL_MODE;
SET [email protected]_FOREIGN_KEY_CHECKS;
SET [email protected]_UNIQUE_CHECKS;
    
asked by Luis EC 04.11.2016 в 04:18
source

2 answers

1

What version of MySQL do you use? I can not reproduce the problem in MySQL 5.7.

I executed the script indicated in the question with the following result:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.11    |
+-----------+
1 row in set (0,00 sec)

mysql> \. script.sql
Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 1 row affected (0,00 sec)

Database changed
Query OK, 0 rows affected (0,01 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,01 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

Query OK, 0 rows affected (0,00 sec)

mysql> SHOW TABLES;
+--------------------+
| Tables_in_dba1_s4a |
+--------------------+
| actor              |
| director           |
| ejemplar           |
| genero             |
| pelicula           |
| renta              |
| reparto            |
| socio              |
+--------------------+
8 rows in set (0,00 sec)
    
answered by 05.11.2016 в 15:00
0

Like @wchiquito, I can not reproduce your error. So it may be a problem that only manifests itself in your specific version of MySQL, or in your specific configuration.

However, when looking more carefully at the tables that gave you the errors, I suspect that this is a conflict between the names of the indexes and the names of the foreign keys.

For example, in the pelicula table, you have an index and a foreign key that you both define with the name dirige . I notice similar conflicts with the other tables ejemplar , renta and reparto .

I suggest you change the names to avoid these conflicts. For example, try changing the 2 dirige to dirige_idx and dirige_fk respectively. Then do the same with the other tables to correct the other conflicts in the names. That may solve your problem.

    
answered by 05.11.2016 в 22:33