Answers.Body: <p>After some research we found out that this is caused by other database tables which have relations to entries in the <code>sites</code> table, where these tables are not dropped before restoring the dump. And the reason, why they are not dropped is that they do not exist yet on the source <code>production</code> system, because they were created in a later development step which is not yet merged into the <code>production</code> branch.</p>
  <p>So actually it is at no chance possible for the <code>production</code> dump to contain SQL commands which drop the correct tables.</p>
  <p>Our solution (and please be aware that this contains risks if not properly used), is to automatically drop all tables in the target (<code>dev</code>) database before importing dumps. We do this by attaching to an event fired by the <code>craft db/restore</code> command.</p>
  <p>Please note that we are using Postgres, so maybe some minor changes to the SQL is necessary for MySQL/Maria.</p>
  <pre><code>    // Clear all database tables before doing a craft db/restore
      \Craft::$app-&gt;db-&gt;on(
          \Craft\db\Connection::EVENT_BEFORE_RESTORE_BACKUP,
          function (\craft\events\RestoreEvent $event) {
              $connection = \Craft::$app-&gt;db;
              $schema = $connection-&gt;schema;
              foreach ($schema-&gt;getTableNames() as $tableName) {
                  $connection-&gt;createCommand('DROP TABLE ' . $tableName . ' CASCADE')-&gt;execute();
              }
          }
      );
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-08-22T07:30:09.520Z
Answers.LastActivityDate: 2022-08-22T08:32:29.757Z
Answers.LastEditDate: 2022-08-22T08:32:29.757Z
Answers.UserDisplayName: Matthias Redl-Mann
Body: <p>I don't know how you work, but in our projects we have multiple staging zones and in regular intervals we take the <em>production</em> system and import them into the zones before to stay as close to production as possible. The early zones however are running on other branches (like <code>dev</code>) and its project configs are applied again immediately after the restore to get to the latest project structure again.</p>
  <p>In this context we now have the problem that taking a dump from <em>production</em> (using <code>craft db/backup</code> command) and trying to re-import it (using <code>craft db/restore</code>) into our <em>dev</em> zone causes a weird mixture of the state in <em>production</em> and the state of <em>dev</em> before the restore.</p>
  <p>Especially the <code>sites</code> table (we are using multisites) is kept in the state of the dev system before the restore, where more rows exist than on the source system. Also some other tables not existing in the source database are kept.</p>
  <p>This is problematic as project config would try to recreate the additional sites which is causing database errors if the corresponding rows already exist.</p>
  <p>Why does this happen?</p>
  
Tags: <console><staging>
Title: Craft's db/restore fails to import a dump
ThreadText: # Craft's db/restore fails to import a dump
  
  (Asked by: username_0 on 2022-08-22)
  
  I don't know how you work, but in our projects we have multiple staging zones and in regular intervals we take the *production* system and import them into the zones before to stay as close to production as possible. The early zones however are running on other branches (like `dev`) and its project configs are applied again immediately after the restore to get to the latest project structure again.
  
  In this context we now have the problem that taking a dump from *production* (using `craft db/backup` command) and trying to re-import it (using `craft db/restore`) into our *dev* zone causes a weird mixture of the state in *production* and the state of *dev* before the restore.
  
  Especially the `sites` table (we are using multisites) is kept in the state of the dev system before the restore, where more rows exist than on the source system. Also some other tables not existing in the source database are kept.
  
  This is problematic as project config would try to recreate the additional sites which is causing database errors if the corresponding rows already exist.
  
  Why does this happen?
  
  **Tags:** console, staging
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2022-08-22. Score: 1
  
  After some research we found out that this is caused by other database tables which have relations to entries in the `sites` table, where these tables are not dropped before restoring the dump. And the reason, why they are not dropped is that they do not exist yet on the source `production` system, because they were created in a later development step which is not yet merged into the `production` branch.
  
  So actually it is at no chance possible for the `production` dump to contain SQL commands which drop the correct tables.
  
  Our solution (and please be aware that this contains risks if not properly used), is to automatically drop all tables in the target (`dev`) database before importing dumps. We do this by attaching to an event fired by the `craft db/restore` command.
  
  Please note that we are using Postgres, so maybe some minor changes to the SQL is necessary for MySQL/Maria.
  
  ```
      // Clear all database tables before doing a craft db/restore
      \Craft::$app->db->on(
          \Craft\db\Connection::EVENT_BEFORE_RESTORE_BACKUP,
          function (\craft\events\RestoreEvent $event) {
              $connection = \Craft::$app->db;
              $schema = $connection->schema;
              foreach ($schema->getTableNames() as $tableName) {
                  $connection->createCommand('DROP TABLE ' . $tableName . ' CASCADE')->execute();
              }
          }
      );
  
  ```
  
  
  ---
  
  