I’m working with a Rails 4 application that utilizes MySQL with the InnoDB storage engine. My schema is set to save in structure.sql format by using config.active_record.schema_format = :sql in the configurations of my app.
A challenge I’m encountering is that every time I execute a migration in my development environment, the structure.sql file is updated with the auto-increment values from the current database. This results in the file containing these specific AUTO_INCREMENT values that correspond to my local database state.
For instance, after running migrations, my structure.sql may include something like this:
CREATE TABLE `items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`cost` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=200 DEFAULT CHARSET=utf8;
This presents complications for team collaboration, as each developer’s structure.sql file will reflect different AUTO_INCREMENT values based on individual local data. Is there an effective solution to avoid saving these development-dependent auto increment values in the structure.sql?
We had this exact problem and solved it with a git hook that’s worked perfectly for over a year. Just set up a pre-commit hook that strips AUTO_INCREMENT values from structure.sql before each commit. The hook runs a simple sed command - removes the AUTO_INCREMENT part but keeps everything else intact. Best part? It’s completely transparent. Developers don’t have to remember anything, and those annoying merge conflicts disappear. MySQL starts auto-incrementing from 1 on empty tables anyway, so functionality stays the same. Make sure everyone gets the hook by putting it in your repo’s hooks directory.
This drove me nuts for months until I found something that actually works. You can modify your database.yml to include a custom dump command that strips out AUTO_INCREMENT values. Just add structure_dump_flags to your MySQL adapter with --no-data --single-transaction --routines --triggers. But honestly, I went with a cleaner approach - created a custom rake task that cleans up the structure.sql file after it’s generated. The task uses regex to find and kill all AUTO_INCREMENT=\d+ patterns. I’ve got it running automatically in our migration workflow and it keeps structure.sql clean for version control. The AUTO_INCREMENT values get set properly when you recreate the database anyway, so removing them doesn’t break anything.
We tried the squeel gem approach first, but Rails 4 has a simpler built-in fix. Just override the dump_schema_information method in an initializer to strip out AUTO_INCREMENT statements. I dropped this in config/initializers/schema_dump.rb and it hooks into the dump process before it writes structure.sql. Unlike other solutions, this works at the ActiveRecord level - no external deps or git hooks needed. We’ve run this setup for 18 months with 8 devs and it kills those annoying structure.sql merge conflicts. The database rebuilds AUTO_INCREMENT sequences automatically when you run db:setup or db:schema:load anyway.
I had the same headaches until I figured out you can configure mysqldump directly in your database config. Skip the Rails patches and hooks - just modify the structure dump process with custom flags in the database adapter. Add --skip-dump-date --no-autocommit and include a post-processing step that strips out AUTO_INCREMENT references. I wrote a simple script that runs after db:migrate with a one-liner to clean up structure.sql. Works perfectly within Rails, no extra git hooks needed, and team members don’t have to remember manual steps. Your structure.sql stays consistent across environments but keeps all the schema changes you actually need for deployment.
same issue here - fixed it with a custom initializer that overrides the dump method. I created config/initializers/mysql_structure_dump.rb and monkey-patched the mysql adapter to strip auto increment values during dump. works great since it happens at the rails level, no external scripts needed. yeah it’s hacky but it’s been rock solid for over 2 years.