db:import should not recreate/truncate all tables by default

[HenningerIBC]

During the db:import task, all tables are dropped and recreated unless the commandline option importTaskDoNotDropAllTablesBeforeImport is set:

deployer-extended-database/deployer/db/task/db_import.php

Lines 68 to 81 in 860cbe2

	if (empty($optionUtility->getOption('importTaskDoNotDropAllTablesBeforeImport'))) {
	runLocally(sprintf(
	'%s --defaults-file=%s %s %s --add-drop-table --no-data | ' .
	'grep -e \'^DROP \| FOREIGN_KEY_CHECKS\' | %s --defaults-file=%s %s -D%s',
	get('local/bin/mysqldump'),
	escapeshellarg($tmpMyCnfFile),
	DatabaseUtility::getSslCliOptions($databaseConfig),
	escapeshellarg($databaseConfig['dbname']),
	get('local/bin/mysql'),
	escapeshellarg($tmpMyCnfFile),
	DatabaseUtility::getSslCliOptions($databaseConfig),
	escapeshellarg($databaseConfig['dbname'])
	));
	}

I do not understand why this is done or necessary since the default configuration for both db_export_mysqldump_options_structure and db_export_mysqldump_options_data already do add a DROP TABLE IF EXISTS. So if both structure.sql and data.sql exists, a db:import call essentially does the following:

1. Export all table structures and then import it, thus dropping and the creating the tables again.
2. strucutre.sql drops each table and then recreates it
3. data.sql drops each table, recreates it and imports its data

So overall, during an import the tables are destroyed and recreated three times. I suggest removing the first subtask (export, drop and recreate) entirely, including the importTaskDoNotDropAllTablesBeforeImport flag, as it also does not follow the ignore_tables_out option. This means all tables are essentially truncated, even those defined by ignore_tables_out.

Additionally, the defaults for for db_export_mysqldump_options_data should include --no-create-info, as it really should only contain the data and not the structure, thus not trigger another drop&create table.

[kszymukowicz]

Thank you for raising this issue.

Why the Current Flow Exists

The first drop operation (lines 68-81) exists to ensure the target database becomes an exact replica of the source database. This is important for deployment scenarios where:

• The target database may have legacy tables not present in the source
• You want a clean, consistent state across environments
• Extra tables should be removed to match the source exactly

The ignore_tables_out setting

The ignore_tables_out setting is specifically for export, not import. By default when you export then:

• structure.sql
  Contains ALL tables and ignore_tables_out is not influencing structure.sql
• data.sql
  Excludes tables defined in ignore_tables_out

This makes sense because:

1. The first drop removes all tables (including cache tables)
2. Then all tables structures from imported database must be recreated. Remember that data.sql has no ignore_tables_out tables, so for example cache tables would not exist after import and the application would break.

What can be improved

While the current flow is internally consistent and serves its purpose, I agree that adding --no-create-info to the data export options would improve the flow as it eliminates the redundant second drop/create cycle (btw: there are not tree drops but two because after first drop the database is empty so the drops in structure.sql has nothing to drop).

Deleting empty tables is super fast, the same as creating them - so this is not a game changer for performance.
Anyway I will add it to next major version.

[HenningerIBC]

Understood! And you are right about that drop: it happens only twice, not thrice.
Somehow I assumed its reimporting the backup structure. whoops.

Still, it's weird that db:import does remove tables, especially by default. I would have expected that behavior more by db:copy...
The issue is my usecase:
Since both dev instance and staging instance are each connected to different ERP systems, I either need to leave some tables untouched or apply changes each time after running db:import.
For now, I have these tables defined in "ignores_tables_out" and do the import with dep db:pull production --options=exportTaskAddIgnoreTablesToStructureDump,importTaskDoNotDropAllTablesBeforeImport to make it work properly. A bit of a mouthful really...

Unfortunately I couldn't figure out how to set these options in my deployer.php config permanently, so I have to remeber to use them in the command line...

Anyway, if the goal is to replicate the database with db:pull perfectly, db_export_mysqldump_options_structure should include --routines

[kszymukowicz]

Still, it's weird that db:import does remove tables, especially by default. I would have expected that behavior more by db:copy...

Maybe yes, but anyway too late to change this, after 8 years :)

The issue is my usecase: [...] Unfortunately I couldn't figure out how to set these options in my deployer.php config permanently, so I have to remeber to use them in the command line...

Assuming you use db:pull, I would propose, that you overwrite whole db:pull task and add options there to db:import inside db:pull directly. Other solution, less intrusive, could be some bash alias, or if you use ddev maybe make some additional logic to "ddev dep" so you do not need to remember that for "this project" you need to use bash alias instead of just "ddev dep db:pull prod".

For adding --routines to db_export_mysqldump_options_structure. I am sure you know, that there are yet --triggers and --events. But the true is that none of common PHP CMS/shops use --triggers, --events, --routines as all try to be database implementation agnostic. Adding --triggers, --events, --routines by default would mean that for some hosters the db:export would stop working, because the permissions for using those are blocked. So while I agree, that it would be fine for clarity to add those options, I also need to say that from practical point of view that would be useless for 99,9% of users and be source of problems. If someone use --triggers, --events, --routines, as rare case, he can always modify db_export_mysqldump_options_structure - I would go into that direction.