Summary of problem – I created a table, then removed it and now I want it back. I can’t do mix ecto.rollback on the “remove” migration (it failed) and it won’t let me now create a new table with the same name.
Can I just delete all three of these migrations AND remove them from the schema_migrations table? That way, I can start from scratch and create the table all over again.
Or am I missing something that will haunt me down the line?
Thank you @Schultzer! Can I specify which migration to drop? could I do this:
mix ecto.drop -v 20220430220042
That way, it would just drop the migration where I removed the table. Then presumably I could mix.ecto.migrate and get the table back. Would that work?
Ohhhh … I think I misunderstood you. I have 50+ other migrations and about 30 tables in my repo. So mix ecto.drop would cause me to flush the entire database which I don’t want to do. I just want to delete the migrations that relate to this one table so that i can start over.
Then I would recommend to use the former where you delete the specific entry in the schema_migrations and file. But make sure you have deleted the table, indice or any constraint that are associated.
If not you might run into some issues when you write your new migration files.
The table is deleted, but how could I check for indices or constraints? I don’t remember having those in my original “create” migration, but is there a way for me to double-check that there are no outstanding indices or constraints?
Isn’t mix ecto.rollback --to 20220430220042 easier, safer and just better? schema_migrations is not accesible from app, so I would think about it like about a private code (@doc false).
I tried that but I got an error that it “cannot reverse migration.” I think it can’t reverse a migration where a table was deleted. It doesn’t know how to go back to and recreate a table based on a previous migration.
I also tried just deleting that “table remove” migration file and the entry from the schema_migrations table (using Navicat) hoping that the earlier migrations would be detected and the table would be re-created when I did mix.ecto.migrate. Unfortunately, it did not detect the missing table. It just said “Migrations already up.” I also couldn’t find a way to tell it to rerun specific migrations (i.e. mix ecto.migrate -v 20220430220040.
I think the only way to get the first migrations to work again would be to drop all of the tables, rerun ALL migrations from scratch … but then I’d lose all of the test data that I manually entered.
So the easiest solution was to delete all migration files that reference that particular table, remove them from schema_migrations (via Navicat) and start over. So far, that has worked. I was able to create the table again without it kicking back and telling me that the table already existed.
I agree that this is a TOTALLY dicey approach, but I couldn’t find another option that didn’t require dropping the entire repo. I followed @Schultzer’s advice and went back through all migrations and made sure I had not added any indices or constraints. It was a pretty basic table with only two fields.
LESSON: Don’t delete tables until you are really, really sure you don’t want them.
You can write reversible migrations or up and down migrations (depending on use case). However if you delete table and you create it back then all data stored in said table would be deleted. https://hexdocs.pm/ecto_sql/Ecto.Migration.html
No idea why people expect something to work when they touch undocumented parts.
If some rollback needs to drop table anyway then you would have deleted data in those tables anyway. I got that you have more migrations, but you should follow documentation and write proper migrations. In theory you can fix that by hand, but it could take long time (depending on how much things you changed). If you do not have a production deployment yet then dropping the database would be definitely easier.
In future for dev environment think about creating seeds for you database, so that you would less worry about dropping database (again in dev environment).
So then every change in database you forgot to manually sync between migrations and your database would be lost. Most probably you would have problems with another migrate/rollback calls and in the very best case those changes would be out of control.
All you need to do is to revert all your manual changes to database, write proper migrations and do rollback as mentioned above. It’s hard to advice you what exactly you would need to do as we do not know and most probably you would not remember every database entry you have manually changed/removed. In future think about making database backup, so even if you mess with manual changes then you would always be able to restore the backup.
If you need it and you don’t care about deleted rows in said table it’s fine, but to have a proper migration/rollback control you need to do it in migration files.
Andrea Leopardi wrote a blog post about the idea of periodically deleting old migrations and doing exactly this - just loading in the schema from an SQL file.
ecto.dump only dumps the schema by default though, and it wouldn’t really make sense to update it to dump the data too, because that’s not something you’d want to commit to the respository, however you could probably do it as a one-off locally. Creating seeds so the test data can be recreated as Eiji suggested helps mitigate this, but you would still lose any changes you made since initializing the DB.
Lesson learned! Thank you for all of the advice. I’m still in development and have not deployed, so I’ll be careful going forward.
One of the reasons I was asking about deleting migrations is because my migrations are a mess. I started developing this app with no experience in Phoenix, Elixir and Ecto. I didn’t come from Rails so I had no point of reference.
So in the beginning, I edited migrations without realizing I had to create new migrations to modify tables. I know … scary. I think I fixed all of that, but I have tons of migrations that fix mistakes. For example, now I need to create a bunch of migrations to fix the fact that I used naive_datetime instead of utc_datetime.
So one of the reasons I asked about deleting migrations is that I wanted to clean up my migrations so that each create_table migration was clean (now that I know what should and should not be in each table). I was then going to delete the bazillion alter_migrations. But after reading that Andrea Leopardi post, I’m wondering if maybe his approach is better.
No matter what, I’ll back up the database and create better seed data. I just saw that I can export my tables to csv files using Navicat. I can then just write a script to import the data back in.
That’s your best bet indeed: export the data you care about in a more schema-neutral format (CSV, JSON) and then play with the schema to your heart’s content.
Fearless development is an enabler of ideas and creativity.
I strongly advise you that Andrea’s approach is much better. What you’re talking about doing is almost like rewriting your git history so if someone cares to look at history it’ll seem like you added each feature atomically and without bugs. Who cares? What matters is that you have the db tables you need now. Dump them to a structure.sql to serve as a starting point for future migrations.
Short answer, yes. But it’s not really advisable unless you really understand how the migration system works.
To put it as simply as possible, the migration system is all about “how do I ensure a script (aka some code) is run once and only once?” It does this by recording the script name in a database table after it has been successfully run.
There is a bit more too it, like how to undo the results of some code being run (aka “rollback”), but that’s essentially it.
Once you really grok this, then ecto.migrate, ecto.rollback, ecto.load, and structure.sql all kinda make sense and you’ll know if deleting things out of schema_migrations will mess you up…
… and you’ll start abusing the migration system to run code that has nothing to do with database schema changes…
