mysqldump inserts "drop database" statement in the middle of the dump

mysql mysqldump

11,502

Solution 1

It's a bug in old versions of MySQL: http://bugs.mysql.com/bug.php?id=17201

Upgrading is recommended :)

Solution 2

The --add-drop-database option adds that. It is there to make sure you get a clean database with exactly the same content as when the dump was made.

Add a DROP DATABASE statement before each CREATE DATABASE statement

11,502

Author by

flypenguin

Digital Drifter.

Updated on June 05, 2022

Comments

flypenguin almost 2 years
I am dumping databases from a system using mysqldump. the problem is: Sometimes the SQL file contains a "delete database" in the MIDDLE of the file, Unfortunately the import of those files fails - quite logically in my eyes.

The mysqldump command line is as follows:
```
mysqldump -u$USER -p$PASS \
 --default-character-set=utf8 --single-transaction \
 --add-drop-database --add-drop-table \
 --databases $DB
```
When I try to re-import this SQL file, I get this error: ERROR 1146 (42S02) at line 3486: Table 'meta.tx_baauftragsdb_auft' doesn't exist.

Looking at the SQL dump file I see these lines:
```
3470 --
3471 -- Current Database: `meta`
3472 --
3473
3474 /*!40000 DROP DATABASE IF EXISTS `meta`*/;
3475
3476 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `meta` /*!40100 DEFAULT CHARACTER SET utf8 */;
3477
3478 USE `meta`;
3479
3480 --
3481 -- View structure for view `tx_baauftragsdb_view_ueber`
3482 --
3483
3484 /*!50001 DROP TABLE IF EXISTS `tx_baauftragsdb_view_ueber`*/;
3485 /*!50001 DROP VIEW IF EXISTS `tx_baauftragsdb_view_ueber`*/;
3486 /*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`meta`@`localhost` SQL SECURITY 
```
... and there is the DROP DATABSE statement. If I delete everything below this statement the db imports just fine, but ... well ... I'd like to know what's happening here.

Can anyone tell me what's going on here, why there is this extra DROP DATABASE statement, and maybe what I'm doing wrong?
flypenguin about 11 years

ah, I added the --add-drop-database statement on purpose - I want a clean import. But why is it there twice, that is the question. Because it is right on top of the SQL dump file, too, where it belongs in my eyes.
Emil Vikström about 11 years

It's added before each CREATE DATABASE statement.
rkosegi about 11 years

I think this is real answer.Mysqldump did not add drop statement twice.It add it x-times where x is number of create database statements.
Nikolai Prokoschenko about 11 years

@rkosegi Not if only one database is being dumped.