Bring back ability to insert zero value on primary key for fixtures (#31795)

Since #29504, mysql2 adapter lost ability to insert zero value on
primary key due to enforce `NO_AUTO_VALUE_ON_ZERO` disabled.
That is for using `DEFAULT` on auto increment column, but we can use
`NULL` instead in that case.

5 files changed, 22 insertions, 42 deletions

diff --git a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
index d663b59444..08f3e15a4b 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb
@@ -414,6 +414,9 @@ module ActiveRecord
       alias join_to_delete join_to_update
 
       private
+        def default_insert_value(column)
+          Arel.sql("DEFAULT")
+        end
 
         def build_fixture_sql(fixtures, table_name)
           columns = schema_cache.columns_hash(table_name)
@@ -432,7 +435,7 @@ module ActiveRecord
                 bind = Relation::QueryAttribute.new(name, fixture[name], type)
                 with_yaml_fallback(bind.value_for_database)
               else
-                Arel.sql("DEFAULT")
+                default_insert_value(column)
               end
             end
           end
diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
index d1a3b6de40..e4f31586d8 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
@@ -526,22 +526,15 @@ module ActiveRecord
         index.using == :btree || super
       end
 
-      def insert_fixtures(*)
-        without_sql_mode("NO_AUTO_VALUE_ON_ZERO") { super }
-      end
-
       def insert_fixtures_set(fixture_set, tables_to_delete = [])
         iterate_over_results = -> { while raw_connection.next_result; end; }
 
         with_multi_statements do
-          without_sql_mode("NO_AUTO_VALUE_ON_ZERO") do
-            super(fixture_set, tables_to_delete, &iterate_over_results)
-          end
+          super(fixture_set, tables_to_delete, &iterate_over_results)
         end
       end
 
       private
-
         def combine_multi_statements(total_sql)
           total_sql.each_with_object([]) do |sql, total_sql_chunks|
             previous_packet = total_sql_chunks.last
@@ -580,19 +573,6 @@ module ActiveRecord
           reconnect!
         end
 
-        def without_sql_mode(mode)
-          result = execute("SELECT @@SESSION.sql_mode")
-          current_mode = result.first[0]
-          return yield unless current_mode.include?(mode)
-
-          sql_mode = "REPLACE(@@sql_mode, '#{mode}', '')"
-          execute("SET @@SESSION.sql_mode = #{sql_mode}")
-          yield
-        ensure
-          sql_mode = "CONCAT(@@sql_mode, ',#{mode}')"
-          execute("SET @@SESSION.sql_mode = #{sql_mode}")
-        end
-
         def initialize_type_map(m = type_map)
           super
 
diff --git a/activerecord/lib/active_record/connection_adapters/mysql/database_statements.rb b/activerecord/lib/active_record/connection_adapters/mysql/database_statements.rb
index a058a72872..148a16328f 100644
--- a/activerecord/lib/active_record/connection_adapters/mysql/database_statements.rb
+++ b/activerecord/lib/active_record/connection_adapters/mysql/database_statements.rb
@@ -50,6 +50,9 @@ module ActiveRecord
         alias :exec_update :exec_delete
 
         private
+          def default_insert_value(column)
+            Arel.sql("DEFAULT") unless column.auto_increment?
+          end
 
           def last_inserted_id(result)
             @connection.last_id
diff --git a/activerecord/test/cases/fixtures_test.rb b/activerecord/test/cases/fixtures_test.rb
index baa5e5df34..34aaadcf7c 100644
--- a/activerecord/test/cases/fixtures_test.rb
+++ b/activerecord/test/cases/fixtures_test.rb
@@ -121,7 +121,7 @@ class FixturesTest < ActiveRecord::TestCase
       conn = ActiveRecord::Base.connection
       mysql_margin = 2
       packet_size = 1024
-      bytes_needed_to_have_a_1024_bytes_fixture = 855
+      bytes_needed_to_have_a_1024_bytes_fixture = 858
       fixtures = {
         "traffic_lights" => [
           { "location" => "US", "state" => ["NY"], "long_state" => ["a" * bytes_needed_to_have_a_1024_bytes_fixture] },
@@ -199,26 +199,17 @@ class FixturesTest < ActiveRecord::TestCase
     end
   end
 
-  def test_no_auto_value_on_zero_is_disabled
-    skip unless current_adapter?(:Mysql2Adapter)
-
-    begin
-      fixtures = [
-        { "name" => "first", "wheels_count" => 2 },
-        { "name" => "second", "wheels_count" => 3 }
-      ]
-      subscriber = InsertQuerySubscriber.new
-      subscription = ActiveSupport::Notifications.subscribe("sql.active_record", subscriber)
-
-      assert_nothing_raised do
-        ActiveRecord::Base.connection.insert_fixtures_set("aircraft" => fixtures)
-      end
-
-      expected_sql = "INSERT INTO `aircraft` (`id`, `name`, `wheels_count`) VALUES (DEFAULT, 'first', 2), (DEFAULT, 'second', 3);\n"
-      assert_equal expected_sql, subscriber.events.first
-    ensure
-      ActiveSupport::Notifications.unsubscribe(subscription)
+  def test_auto_value_on_primary_key
+    fixtures = [
+      { "name" => "first", "wheels_count" => 2 },
+      { "name" => "second", "wheels_count" => 3 }
+    ]
+    conn = ActiveRecord::Base.connection
+    assert_nothing_raised do
+      conn.insert_fixtures_set({ "aircraft" => fixtures }, ["aircraft"])
     end
+    result = conn.select_all("SELECT name, wheels_count FROM aircraft ORDER BY id")
+    assert_equal fixtures, result.to_a
   end
 
   def test_deprecated_insert_fixtures
diff --git a/activerecord/test/fixtures/minimalistics.yml b/activerecord/test/fixtures/minimalistics.yml
index c3ec546209..83df0551bc 100644
--- a/activerecord/test/fixtures/minimalistics.yml
+++ b/activerecord/test/fixtures/minimalistics.yml
@@ -1,2 +1,5 @@
+zero:
+  id: 0
+
 first:
   id: 1