CODEWITHSUNDEEP
mysql
Brian Hooper
Brian Hooper

Reputation: 22074

Fragments of old tables left behind after DROP SCHEMA

I'm attempting to drop and recreate a MySQL schema but it appears that fragments of the old schema reappear when I do this.

I have a script to create the new schema thus...

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

DROP SCHEMA IF EXISTS `scunthorpe` ;

CREATE SCHEMA IF NOT EXISTS `scunthorpe` DEFAULT CHARACTER SET latin1 ;
USE `scunthorpe` ;

DROP TABLE IF EXISTS `location` ;

CREATE  TABLE IF NOT EXISTS `location` (
      `id_location` INT NOT NULL AUTO_INCREMENT ,
      `location` VARCHAR(45) NOT NULL ,
    PRIMARY KEY (`id_location`) )
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = latin1;

DROP TABLE IF EXISTS `strand` ;

CREATE  TABLE IF NOT EXISTS `strand` (
    `id_location` INT NOT NULL ,
    `id_strand` INT NOT NULL ,
    `notes` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id_location`, `id_strand`) ,
  INDEX `strand_location_fkey` (`id_location` ASC) ,
  CONSTRAINT `strand_location_fkey`
  FOREIGN KEY (`id_location` )
      REFERENCES `location` (`id_location` )
      ON DELETE CASCADE
      ON UPDATE CASCADE)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = latin1;

This produces the error:

ERROR 1005 (HY000): Can't create table '.\scunthorpe\strand.frm' (errno: 150)

Perusing the show innodb status reveals the following:-

111021 10:46:17 Error in foreign key constraint of table scunthorpe/straightenin
g_temperature:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match to the ones in table. Constraint:
,
  CONSTRAINT straightening_to_strand_fkey FOREIGN KEY (id_location, id_sequence,
 id_strand) REFERENCES strand (id_location, id_sequence, id_strand)

Now there was, at about the time of the Norman conquest, such a table in the schema. But the drop schema should get rid of it, no? So does anyone know why this is reappearing and what I should do about it?

Edit 1

As suggested by devart, I tried REPAIR TABLE but this is apparently not supported for InnoDB. I inspected the files and found the schema directory was deleted after the DROP SCHEMA statement, and recreated on CREATE SCHEMA, as one would expect. Agent Ransack revealed the offending string straightening_to_strand_fkey was present in a file called ibdata1, whatever that is.

Upvotes: 1

Views: 321

Answers (1)

Devart
Devart

Reputation: 122002

Maybe this recommendations will help to resolve the problem:

Upvotes: 2

Related Questions