From c24efdbd2720bd4d936a3ffab0c22af0a718d2f1 Mon Sep 17 00:00:00 2001 From: Bob Lail Date: Thu, 4 Apr 2019 07:35:04 -0500 Subject: When skipping duplicates in bulk insert on MySQL, avoid assigning id when not specified If `id` is an `AUTONUMBER` column, then my former strategy here of assigning `no_op_column` to an arbitrary column would fail in this specific scenario: 1. `model.columns.first` is an AUTONUMBER column 2. `model.columns.first` is not assigned in the insert attributes I added three tests: the first test covers the actual error; the second test documents that this _isn't_ a problem when a value is given for the AUTONUMBER column and the third test ensures that this no-op strategy isn't secretly doing an UPSERT. --- activerecord/test/cases/insert_all_test.rb | 38 ++++++++++++++++++++++++++++++ 1 file changed, 38 insertions(+) (limited to 'activerecord/test') diff --git a/activerecord/test/cases/insert_all_test.rb b/activerecord/test/cases/insert_all_test.rb index fc25701c80..61fbda1f0f 100644 --- a/activerecord/test/cases/insert_all_test.rb +++ b/activerecord/test/cases/insert_all_test.rb @@ -104,6 +104,44 @@ class InsertAllTest < ActiveRecord::TestCase end end + def test_insert_all_with_skip_duplicates_and_autonumber_id_not_given + skip unless supports_insert_on_duplicate_skip? + + assert_difference "Book.count", 1 do + # These two books are duplicates according to an index on %i[author_id name] + # but their IDs are not specified so they will be assigned different IDs + # by autonumber. We will get an exception from MySQL if we attempt to skip + # one of these records by assigning its ID. + Book.insert_all [ + { author_id: 8, name: "Refactoring" }, + { author_id: 8, name: "Refactoring" } + ] + end + end + + def test_insert_all_with_skip_duplicates_and_autonumber_id_given + skip unless supports_insert_on_duplicate_skip? + + assert_difference "Book.count", 1 do + Book.insert_all [ + { id: 200, author_id: 8, name: "Refactoring" }, + { id: 201, author_id: 8, name: "Refactoring" } + ] + end + end + + def test_skip_duplicates_strategy_does_not_secretly_upsert + skip unless supports_insert_on_duplicate_skip? + + book = Book.create!(author_id: 8, name: "Refactoring", format: "EXPECTED") + + assert_no_difference "Book.count" do + Book.insert(author_id: 8, name: "Refactoring", format: "UNEXPECTED") + end + + assert_equal "EXPECTED", book.reload.format + end + def test_insert_all_will_raise_if_duplicates_are_skipped_only_for_a_certain_conflict_target skip unless supports_insert_on_duplicate_skip? && supports_insert_conflict_target? -- cgit v1.2.3