CODEWITHSUNDEEP
mysqldatabaseinnodb
Sakura
Sakura

Reputation: 43

Cannot recreate innodb database after I deleted it from data because :"Tables already exist"

I know this has similarities with the shroedingers table problem, but it reaches father than that.

I'm using Mysql Server 5.6 with innodb and file per table enabled.

I recently deleted a few databases from the mysql/server/data folder. They where too big and the server went away when I tried to do it with a drop, so I thought that was the cleanest option.

Now I'm trying to recreate the database from a sql-file and I get the messages "table xy already exists" for all the tables in the database, even though I checked the data folder and it was empty before I started the file. As the file is running, the idb files for the databases are created, not the frm though, so I end up with tablespaces that I cannot fill.

Obviously the server still has some information about the deleted databases saved somewhere that I need to get rid of, I just have no idea where to look for it.

Here an excerpt from the scheme file

-- MySQL dump 10.13  Distrib 5.6.13, for Linux (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version   5.6.13-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,     FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `articles`
--

 DROP TABLE IF EXISTS `articles`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `articles` (
  `articlesID` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (`articlesID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;enter code here

Upvotes: 1

Views: 1247

Answers (2)

rhavendc
rhavendc

Reputation: 1013

It's a bad practice to delete file/s directly inside the data folder. Keep in mind that file/s has logs. Here's my suggestion:

  • Undo or Restore from the Recycle Bin the deleted files from the data folder

  • Make sure you are in the same server where you deleted them (ib_logfile, ibdata, etc should be there)

  • Try to DROP the table again

Please tell me if these thing works.

Upvotes: 0

Rick James
Rick James

Reputation: 142218

The system structures in the file ibdata1 contain information about all InnoDB tables. It did not get cleaned up when you deleted the database directories.

Plan A: (This has some risk.) Dump the rest of your tables using mysqldump (or some other took); stop mysqld; delete ibdata1; restart mysqld; reload the dump.

Plan B: (Low risk.) Abandon those database names.

Plan C: (Serious risk.) Hire an expert (not me) to fiddle with ibdata1. (Suggestions: Percona, MariaDB, Oracle.)

Plan D: (unknown risk.) Do some web searches. Someone has probably conquered this problem.

Upvotes: 1

Related Questions