0

Currently in my CI I'm completely wiping production db with development db. That was fine for testing but I'm coming to a stage where I need to start merging instead of completely wiping.

Right now the command I'm using to wipe and write is as follows:

mysqldump -u "$DB_USERNAME" -p"$DB_PASSWORD" -h "$DB_HOST" "development" > dump.sql
mysql -u "$DB_USERNAME" -p"$DB_PASSWORD" -h "$DB_HOST" "production" < dump.sql

I tried the following for merging:

mysqldump --no-create-db --no-create-info -u "$DB_USERNAME" -p"$DB_PASSWORD" -h "$DB_HOST" "development" > dump.sql

Which I thought would do the trick but I get the following error:

ERROR 1062 (23000) at line 23: Duplicate entry '1' for key 'PRIMARY'

Any ideas where I'm falling short?

  • 2
    There will always be headaches with that approach because of the keys, whether primary or unique. What we typically do is the other way around: dump a copy the production DB to use _as_ the dev database. – Paul T. May 15 '21 at 12:19
  • @PaulT.: where you say "a copy the production DB", you mean "a copy *of* the production DB", I suppose. –  May 15 '21 at 16:19
  • @PierreFrançois ... Yes, that is correct, missed that `of` – Paul T. May 15 '21 at 19:19

1 Answers1

0

Adding this to the mysqldump will probably work:

 --insert-ignore

The error message implies that you already have a row with primary key id=1 and are trying to add it again.

Caveat: If you have modified that row, this technique will not achieve the "merge" goal. Please specify the "merge" more thoroughly.

Rick James
  • 66,863
  • 4
  • 38
  • 92