70
coderme
7y

For some idiotic reason, I ran
UPDATE users
SET email="myname@mycompany.com"; in production.

No where clause. Oh drat.

Comments
  • 5
    @senzory As many times as it happens, I guess...
  • 4
    Always write where clause first!
  • 6
    On production, don't execute queries as fast as you can. Read it at least 10 times, add where clause as well as limit at the end of your query then read it again. Always have a back up of the production database. Always read instruction to be executed in production dB.
  • 12
    We are required to ALWAYS use BEGIN TRAN when running queries on prod databases.
  • 6
    SQL Service with Ctrl + Z plz
  • 2
    Yeah, I tend to write queries as comments first, if I don’t need Intellisense. Or start with WHERE 1 = 0.
  • 1
    Always use transactions with roll back just to make sure.
  • 1
    how...? lmao
  • 0
    That's why I write transactional statements or write the where clause first...
  • 1
    Did that at my first programming job during like my second or third week...but with the users' passwords. Thank god we had frequent backups for that database. Lesson learned, auto-commit is off, using transactions for any updates, and I ALWAYS write the statement as a select first, and then just change everything in front of the where clause...
  • 0
    Best approach I have found is to just hire a DBA part time to run your PROD queries for you. This has stopped countless issues for me.
  • 0
    @rpcaldeira sounds like it wasn't a transaction. So yes. :(
  • 0
    @rpcaldeira nope, I had to restore from the daily backup. Not cool, but I don't think anyone actually noticed other than me.
  • 1
    @riekena What? I should have known this. I had no idea MySQL supported transactions!
  • 0
    @BeardedFists Must be nice to be able to hire a guy just to write SQL. We don't have that luxury at our small web agency.
  • 1
    @coderme You would have to find a DBA that can contract for about 3-5 hours per week. DBAs seem to love small contracts.
  • 2
    My manager taught me to always use transactions after a coworker had done something similar.. 3 lines to save your life:

    begin transaction
    Sql code here
    --commit transaction
    Rollback transaction

    Most helpful advice ever 😊
  • 1
    @BeardedFists That is a neat/weird pro tip! Thanks! That would be helpful in setting up Flyway / MySQL CI.
  • 2
    @MrCSharp Between you and @riekena I owe you guys. I took a SQL class at school (PLSQL to be exact) and the professor didn't discuss the actual usefulness of transactions. Not even sure he covered them at all.
  • 1
    Change the email to someone you don't like and rerun the SQL and blame it in him. Easy solution
  • 0
    @Alpham0use Lol. Didn't think of that.
Add Comment