Used by
MySQL 5.7 - x6.4
Open Server - the last

The folders with the database and tables were located first in FOLDER 1 . I reconfigured the server so that the folders with the database and tables are located in FOLDER 2 .

  1. When the server was working with folders in FOLDER 1 , I exported the "01tst" database with its tables.
  2. Switched the server to FOLDER 2 .
  3. I import database "01tst" with its tables.

# PROBLEM 1
MISTAKE
When importing multiple tables: SQL Error (1813): Tablespace ' 01tst . 00_tbl_00 'exists.

I read somewhere that the problem can be removed

DROP TABLE 01tst . 00_tbl_00
or
DROP TABLE IF EXISTS 01tst . 00_tbl_00

did not help

FOLDER 2 ("... MySQL-5.7-x64 \ 01tst \") contained the file "00_tbl_00.ibd" and didn’t seem to draw any attention ... Like "db.opt", but not * .frm. ..
I deleted the files "00_tbl_00.ibd" and "db.opt" (presumably).

PROBLEM 1. SOLUTION 1 (PARTIAL)
1. I import a DB.
2. The program creates a database folder with files: * .ibd and db.opt.
3. It produces an error Example "SQL error (1813): Tablespace ' 01tst . 00_tbl_00 ' exists." or something similar.
4. Delete the folder with the imported database files from the directory, which gives an error.
5. Reboot the server.
6. I carry out the import procedure.
All table, all table are imported, but in some tables not all rows. About 10-15% of the rows are not in the imported tables ... Well, okay ... I will suffer ..

PROBLEM 2
Using the solution PROBLEM 1. SOLUTION 1 (PARTIAL) so added three databases. Then the error began to appear again. see .screen Screen ** # PROBLEM 2 **

Code example

 -- -------------------------------------------------------- -- Хост: 127.0.0.1 -- Версия сервера: 5.7.16 - MySQL Community Server (GPL) -- Операционная система: Win64 -- HeidiSQL Версия: 9.4.0.5125 -- -------------------------------------------------------- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET NAMES utf8 */; /*!50503 SET NAMES utf8mb4 */; /*!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' */; -- Дамп структуры базы данных tst_rb_mn_02 CREATE DATABASE IF NOT EXISTS `tst_rb_mn_02` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `tst_rb_mn_02`; -- Дамп структуры для таблица tst_rb_mn_02.002_tst_01_vr_04_tbl_frm1_ssl CREATE TABLE IF NOT EXISTS `002_tst_01_vr_04_tbl_frm1_ssl` ( `id_tma_ssl` int(11) DEFAULT NULL, `name_ssl` text, `url_ssl` text ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- Дамп данных таблицы tst_rb_mn_02.002_tst_01_vr_04_tbl_frm1_ssl: ~3 rows (приблизительно) /*!40000 ALTER TABLE `002_tst_01_vr_04_tbl_frm1_ssl` DISABLE KEYS */; INSERT INTO `002_tst_01_vr_04_tbl_frm1_ssl` (`id_tma_ssl`, `name_ssl`, `url_ssl`) VALUES (1, 'name_ssl_04_tbl_zp_1 ', 'url_ssl_04_tbl_zp_1 '), (2, 'name_ssl_04_tbl_zp_2 ', 'url_ssl_04_tbl_zp_2 '), (3, 'name_ssl_04_tbl_zp_3 ', 'url_ssl_04_tbl_zp_3 '); /*!40000 ALTER TABLE `002_tst_01_vr_04_tbl_frm1_ssl` ENABLE KEYS */; -- Дамп структуры для таблица tst_rb_mn_02.002_tst_02_vr_07_tbl_frm1_ssl CREATE TABLE IF NOT EXISTS `002_tst_02_vr_07_tbl_frm1_ssl` ( `id_tma_ssl` text, `name_ssl` text, `url_ssl` text ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- Дамп данных таблицы tst_rb_mn_02.002_tst_02_vr_07_tbl_frm1_ssl: ~3 rows (приблизительно) /*!40000 ALTER TABLE `002_tst_02_vr_07_tbl_frm1_ssl` DISABLE KEYS */; INSERT INTO `002_tst_02_vr_07_tbl_frm1_ssl` (`id_tma_ssl`, `name_ssl`, `url_ssl`) VALUES ('frmA_A', 'name_ssl_07_tbl_zp_1 ', 'url_ssl_07_tbl_zp_1 '), ('frmA_B', 'name_ssl_07_tbl_zp_2 ', 'url_ssl_07_tbl_zp_2 '), ('frmA_C', 'name_ssl_07_tbl_zp_3 ', 'url_ssl_07_tbl_zp_3 '); /*!40000 ALTER TABLE `002_tst_02_vr_07_tbl_frm1_ssl` ENABLE KEYS */; -- Дамп структуры для таблица tst_rb_mn_02.002_tst_03_vr_10_tbl_frm1_ssl CREATE TABLE IF NOT EXISTS `002_tst_03_vr_10_tbl_frm1_ssl` ( `id_tma_ssl` text, `name_ssl` text, `url_ssl` text ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- Дамп данных таблицы tst_rb_mn_02.002_tst_03_vr_10_tbl_frm1_ssl: ~3 rows (приблизительно) /*!40000 ALTER TABLE `002_tst_03_vr_10_tbl_frm1_ssl` DISABLE KEYS */; INSERT INTO `002_tst_03_vr_10_tbl_frm1_ssl` (`id_tma_ssl`, `name_ssl`, `url_ssl`) VALUES ('frmA_01', 'name_ssl_10_tbl_zp_1 ', 'url_ssl_10_tbl_zp_1 '), ('frmA_02', 'name_ssl_10_tbl_zp_2 ', 'url_ssl_10_tbl_zp_2 '), ('frmA_03', 'name_ssl_10_tbl_zp_3 ', 'url_ssl_10_tbl_zp_3 '); /*!40000 ALTER TABLE `002_tst_03_vr_10_tbl_frm1_ssl` ENABLE KEYS */; /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */; /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; 

Question.
1. How to get rid of it errors - SQL Error (1813)?
2. How to fix # PROBLEM 2 ?

enter image description here

  • one
    view the imported files and remove the scripts for creating this table in all but the first file - teran
  • one
    @teran Ten against one, that in its database directory there is a file 00_tbl_00.ibd , and the file 00_tbl_00.frm . So remove the creation - will not help. And I would not recommend deleting 00_tbl_00.ibd either - you can get an error in the system namespace, and this is a lot worse than it is now. - Akina
  • However, you can try to drop the tablespace ... and if it drops (or it turns out that the server is unaware of its presence and considers it non-existent) - then deleting the file will be much less dangerous ... - Akina

1 answer 1

Delete the database before loading the dump. Or add to the beginning of the dump

 DROP DATABASE 01tst; 

In the future - if the dump is not done to replenish another database with data from the dumped one, add the --add-drop-database key to the mysqldump command line.

  • Completed the question. '-add-drop-database.' - how to use it? Did not find the syntax ... Just enter before the main code? - koverflow
  • '-add-drop-database.' - how to use it? Exactly as described in the documentation. dev.mysql.com/doc/refman/5.7/en/… - Akina