[MDEV-16224] [mysqldump] Support INSERT ... ON DUPLICATE KEY UPDATE for data dumps Created: 2018-05-21  Updated: 2018-05-21

Status: Open
Project: MariaDB Server
Component/s: Backup
Fix Version/s: None

Type: Task Priority: Major
Reporter: whois Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Please consider implementing support for the following mysql feature request.

https://bugs.mysql.com/bug.php?id=73190

quoted here for safe keeping:
-------------------------------------
" [3 Jul 2014 21:23] Christopher Schultz

Description:
mysqldump currently supports two different options for dealing with PK collisions:

--insert-ignore (uses INSERT IGNORE)
--replace (uses REPLACE INTO instead of INSERT INTO)

I'd like to request an additional option that uses INSERT ... ON DUPLICATE KEY UPDATE ... when used.

This will allow one database to be used as a source to update another database without the following problems:

Using INSERT IGNORE will ignore any updates to existing rows that are coming from the file being loaded "on top of" an existing database.

Using REPLACE ends up churning the primary index, failing when foreign keys point to the record being updated (REPLACED), or ON DELETE CASCADE wiping-out records in other tables as the records in the target table are "updated" (using REPLACE).

There are two significant downsides to using ON DUPLICATE KEY UPDATE, of course:

1. The dump file will get much bigger, because the bulk-loading syntax for INSERT can no longer be used.
2. The dump file will get much longer, because ON DUPLICATE KEY UPDATE requires that all values be specified twice: once in the VALUES() section and then a second time in the field=value, field=value part after "ON DUPLICATE KEY UPDATE" phrase.

Bug 11422 http://bugs.mysql.com/bug.php?id=11422 requests the ability to simply say "ON DUPLICATE KEY UPDATE" and allow the engine to use the VALUES to update all fields in the table. Fixing bug 11422 and using the solution here would mitigate both downsides because then extended syntax could (possibly?) be used and the values would not have to be mentioned twice in the dump file.

I have found many posts online about how to do something like this and the responses always steer the person toward --insert-ignore or --replace but there are many scenarios where the above request would be preferable.
"
----------------------------
if mariadb's mysqldump had this feature it would be an easy way to backup/restore particular data without disrupting the existing data on restore.

thanks


Generated at Thu Feb 08 08:27:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.