What's happening?

Today, I'm assigned to do an automation task for inserting a CSV file into the MySQL database. The CSV format is a mess; I need to reorganize it into usable data. I'm not familiar with such a job, so I coded it in a hacky and unsafe way with Javascript.

The problem

It's ugly, but it works anyway. After I've tested the script on my local DB and local machine, I deployed the script to the production server, set up the database connection and ran it without a second thought. And guess what? It worked on my local machine but it didn't on the server, the local and remote database aren't fully synchronized causing an unexpected condition where it threw an error. back then I didn't fully realize how valuable the transaction was so I didn't prepare for it. the progress now is stalled and left the partial data residing in the database.

The solution

I've to clean up the problem I've created on my own. Fortunately, I printed the id to stdout each time a new record was inserted to the table, so I managed to filter the id with grep, construct a series of DELETE operation on the SQL file with awk. and finally execute the SQL file with foreign constraint check disabled.

The partial data problem solved, and I found the culprit that caused the error, Happy ending. From this tragedy, I've learn that proceed anything with caution can benefit your time even when it's seems take more time. Trust me, it will cost more time to clean up a mess than to proceed things with caution.

The conclusion

as a note for myself, in the next occasion, please do:

apply more robust unit test
ensure your code will work properly as expected
proper error handling is necessary for mitigation to prevent unwanted things
use transaction, commit, rollback
the following option are actually optional and might be considered on advance scenario/level: remote and local database synchronization, multiple production databases (cloned) that are synchronized in real-time

It's not a worst cases scenario where I broke everything, it cost my time more than it should though, but now I've learned something new.