title: Fragments of old tables left behind after DROP SCHEMA
body: <p>I'm attempting to drop and recreate a MySQL schema but it appears that fragments of the old schema reappear when I do this.</p>
  
  <p>I have a script to create the new schema thus...</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>This produces the error:</p>
  
  <pre><code>ERROR 1005 (HY000): Can't create table '.\scunthorpe\strand.frm' (errno: 150)
  </code></pre>
  
  <p>Perusing the <code>show innodb status</code> reveals the following:-</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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?</p>
  
  <p><strong>Edit 1</strong></p>
  
  <p>As suggested by devart, I tried <code>REPAIR TABLE</code> but this is apparently not supported for InnoDB. I inspected the files and found the schema directory was deleted after the <code>DROP SCHEMA</code> statement, and recreated on <code>CREATE SCHEMA</code>, as one would expect. Agent Ransack revealed the offending string <code>straightening_to_strand_fkey</code> was present in a file called <code>ibdata1</code>, whatever that is.</p>