3 ms·
I had a narrow escape once doing something fancy with migrations. We had several MySQL string columns as long text type in our database but they should have be
by aszen 6y ago
I had a narrow escape once doing something fancy with migrations.
We had several MySQL string columns as long text type in our database but they should have been varchar(255) or so. So I was assigned to convert these columns to their appropriate size.
Being the good developer I was, I decided to download a snapshot of the prod database locally and checked the maximum string length we had for each column via a script. Using this script it made a migration query that would alter column types to match their maximum used length keeping the minimum length as varchar (255).
I tested that migration and everything looked good, it passed code review and was run on prod. Soon after we start getting complaints from users that their old email texts have been truncated. I then realize the stupidity of the whole thing, the local dump of production database always wiped out many columns clean for privacy like the email body column. So the script thought it had max length of 0 and decided to convert the column to varchar(255).
I realize the whole thing may look incredibly stupid, that's only because the naming for db columns was in a foreign european language so I didn't know even know the semantics of each column.
Thankfully my seniors managed to restore that column and took the responsibility themselves since they had passed the review.
We still did fix those unusually large columns but this time by simple duplicate alter queries for each of those columns instead of using fancy scripts.
I think a valuable lesson was learned that day to not rely on hacky scripts just to reduce some duplicate code.
I now prefer clarity and explicitness when writing such scripts instead of trying to be too clever and automating everything.
- heavenlyblue 6y agoAnd you didn’t even bother to do a query of the actual maximum length value of the columns you were mutating? Or at least query and see the text in there? Basically you just blindly ran the migration on the data and checked if it didn’t fail? The lesson here is not about cleverness unfortunately.
- detaro 6y agoThe comment clearly states that they did.
- heavenlyblue 6y agoIf they did, they would have noticed that the columns were empty (because they were wiped clean for PI data). The parent is either misrepresenting the situation or they didn’t do what they say they did. Also in any production setup, before the migration in the same transaction you would have something along the lines of “check if the column size is larger than and then abort”, because you never know when that can be added while working on the database.
- aszen 6y agoI agree we could have done it a lot differently and safely, I especially like the last point you mentioned that is what would have been the correct way to do it. But this happened as described, a local only script that generated a list of columns to modify then a migration to execute the alter queries for all of them.
- aszen 6y agoI did see some values and found them reasonable, problem of the whole thing was there were atleast 200 or so tables with dozens of columns each and only two or so tables were excluded from being dumped locally. So yes I could have noticed their length 0 if I had looked carefully amidst hundreds of rows but since my faulty logic of prod db = local db didn't even consider this possible I didn't bother. If it had been just 10 to 20 migrations queries that would have been a lot easier to validate but then I wouldn't even have attempted to write a script
- mr_toad 6y ago> my faulty logic of prod db = local db didn't It happens. “It worked in dev” is the database equivalent of “worked on my machine”.