in Code Snippets, Hacking |

Rails mysql structure.sql dump contains AUTO_INCREMENT

When rails generates a structure.sql dump for MySQL it contains the AUTO_INCREMENT value. Which is anoying because this is not something you want to happen.

CREATE TABLE `active_storage_variant_records` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `blob_id` bigint NOT NULL,
  `variation_digest` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_active_storage_variant_records_uniqueness` (`blob_id`,`variation_digest`),
  CONSTRAINT `fk_rails_993965df05` FOREIGN KEY (`blob_id`) REFERENCES `active_storage_blobs` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=471 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Look at the part: AUTO_INCREMENT=471

After digging through the activerecord code, I saw I could sneak in a mysqldump option for the dump call. Yeah! 👍🏻

But there isn't such option 😕. It's an ancient BUG/Feature of mysql, which of course still isn't resolved in MySQL 8. https://bugs.mysql.com/bug.php?id=20786

Why would you like to dump the table structure (without data) with the AUTO_INCREMENT value!?

As a workaround it's possible to enhance the db:schema:dump task in a custom rake file (lib/tasks/remove_autoincrement_from_dump.rake).
So the AUTO_INCREMENT part is removed from it.

Rake::Task['db:schema:dump'].enhance do
  structure_sql_path = Rails.root.join("db/structure.sql")
  if File.exist?(structure_sql_path)
    sql = File.read(structure_sql_path)
    File.write(structure_sql_path, sql.gsub(/AUTO_INCREMENT=[0-9]+/, ""))
  end
end

References:

Published