4 ms·

I would argue you shouldn't have been able to do that in your organization without bypassing (several) significant safeguards. Did you forget a where clause wh

I would argue you shouldn't have been able to do that in your organization without bypassing (several) significant safeguards. Did you forget a where clause while deleting data on a table, or were you actually on the production server hosting the database? Any code you write that interacts with a database (or really any production code at all...) should be reviewed before being merged. And developers shouldn't be writing raw SQL commands on a production server. It's hard for me to see this as anything other than an organizational failure rather than your own. EDIT: Based on the number of downvotes this has received, I can only imagine we have a lot of devs on HN who cowboy SQL in production...holy hell how can any of what I said be controversial.

deleted 8y ago
[deleted]
penagwin 8y ago
While I mostly agree, many companies have a tech department of half a dozen people, and implementing and enforcing every good practice devops isn't always realistic. That said, I'd expect at least a backup of production, then again he said he lost 1 hr of data so it was likely between backups.
- Cthulhu_ 8y ago
  If you haven't been able to invest the time to do database maintenance tasks in a safe way, at the very least enforce a 4-eyes principle and write up a checklist / script before hacking away in the production database. I mean I get it, I've made mistakes like this as well knowing I shouldn't have (we had test and prod running on the same server, about 40K people received a test push notification). But the bigger your product gets, the less you can afford to risk losing data.
  - penagwin 8y ago
    I totally agree, if feasible those steps should be done! I was just trying to explain that many business like the one I'm at don't do business in tech (mine sells wholesale clothing), with 6 people in the tech dep, so understandably there's certain limitations on how far best practices can go. While I would usually consider it a mistake, if you thought you were just making a quick, what should be read only query, and it happens to hit some random edgecase-bug and crash a db... Edit, continuing - Sure you should have tested that on the test DB first, but I'd be kinda understanding of how that happened. Depends on the business too, if you're a startup-tech company then yea, get your -stuff- together! It's just a lot of business only need their website and some order management, their focus isn't on the tech side of things.
- HenryBemis 8y ago
  Any IT dept with less than 5-10 DBAs will have to throw out the window any Segregation of Duties plans (keeping apart access on Prod-Dev-QA) or separating/dedicating DBAs to the three different environments. But the backup, hell yeah, you NEED mitigating controls (preventive/corrective) for when you allow people to make changes in Prod that haven't been gone through all the testing phases.
  - hotsauceror 8y ago
    That's the problem with DevOps / CI/CD. The DBA team, and separation of duties / least privilege more generally, are seen as old-fashioned impediments to business velocity. The foundations of DevOps are supposed to be trust, tools, and testing, but in my experience once the dev team gets their hands on the tools, that's all she wrote.
    - penagwin 8y ago
      To be fair, when you have a tech department of 6 people, who still have to respond to internal support tickets, manage the businesses intranet, and continue development of current projects, not to mention only 2 of those 6 have any clue how to setup/administrate a database... You can see the issue. You can't just say "Hire more people" because the current setup is "working" and isn't considered critical to the rest of the business when it isn't tech related.
chronolitus 8y ago
Even cowboys don't herd cattle by themselves...
neals 8y ago
No I totally agree, looking back on my actions, rm -rf'ing the production database was not a productive use of my time.
ams6110 8y ago
SQL fixes in production don't have to be that risky. Just run a SELECT first with the same predicates and be sure that's what you want. In some cases this can be better than trying to develop a fix using a test system which may not have the same data. Same with "rm -r" --- run an "ls" first. Sure it's not ideal but life isn't ideal. When you have a production problem, you need to fix production.
stickfigure 8y ago
I think you're making a number of assumptions about the size and maturity of the parent's organization; it could be just one person. With the right tools and processes, it's possible to build very successful companies with tiny engineering teams. Developers run queries against prod because there's nobody else to do so. The risk of mistakes is mitigated by the increased situational awareness and the developer quality and communication in 3-teams vs 30-teams. Neither approach is inherently 'wrong', although running a 30-person team the same way as a 3-person team (or vice versa) can have nasty consequences.
perl4ever 8y ago
I worked in a place for years where we had access to production databases, to do updates, deletes and so on. We had a certain stylized way of doing things to prevent irreversible errors. If you were going to delete data for instance, you always created a backup table first with 100% of the columns for the rows of interest, then selected from it to verify it looked reasonable, and then deleted using the backup table to correlate rows to the production table using the primary key. And then another analyst reviewed your work (originally two levels of review, later reduced to one). The backup tables and comments in a ticket including a copy of the SQL run were kept indefinitely, so even if the review missed something it generally was correctable. The DBAs in the company did think SQL should be reviewed in advance, but that's not how our department did it. I think it's arguable that, given that reviewers before or after doing something dangerous may miss things, it's better to establish safe practices and if you do that, then you don't really need a review in advance.

deleted 8y ago

[deleted]

penagwin 8y ago

While I mostly agree, many companies have a tech department of half a dozen people, and implementing and enforcing every good practice devops isn't always realistic. That said, I'd expect at least a backup of production, then again he said he lost 1 hr of data so it was likely between backups.

Cthulhu_ 8y ago
If you haven't been able to invest the time to do database maintenance tasks in a safe way, at the very least enforce a 4-eyes principle and write up a checklist / script before hacking away in the production database. I mean I get it, I've made mistakes like this as well knowing I shouldn't have (we had test and prod running on the same server, about 40K people received a test push notification). But the bigger your product gets, the less you can afford to risk losing data.
- penagwin 8y ago
  I totally agree, if feasible those steps should be done! I was just trying to explain that many business like the one I'm at don't do business in tech (mine sells wholesale clothing), with 6 people in the tech dep, so understandably there's certain limitations on how far best practices can go. While I would usually consider it a mistake, if you thought you were just making a quick, what should be read only query, and it happens to hit some random edgecase-bug and crash a db... Edit, continuing - Sure you should have tested that on the test DB first, but I'd be kinda understanding of how that happened. Depends on the business too, if you're a startup-tech company then yea, get your -stuff- together! It's just a lot of business only need their website and some order management, their focus isn't on the tech side of things.
HenryBemis 8y ago
Any IT dept with less than 5-10 DBAs will have to throw out the window any Segregation of Duties plans (keeping apart access on Prod-Dev-QA) or separating/dedicating DBAs to the three different environments. But the backup, hell yeah, you NEED mitigating controls (preventive/corrective) for when you allow people to make changes in Prod that haven't been gone through all the testing phases.
- hotsauceror 8y ago
  That's the problem with DevOps / CI/CD. The DBA team, and separation of duties / least privilege more generally, are seen as old-fashioned impediments to business velocity. The foundations of DevOps are supposed to be trust, tools, and testing, but in my experience once the dev team gets their hands on the tools, that's all she wrote.
  - penagwin 8y ago
    To be fair, when you have a tech department of 6 people, who still have to respond to internal support tickets, manage the businesses intranet, and continue development of current projects, not to mention only 2 of those 6 have any clue how to setup/administrate a database... You can see the issue. You can't just say "Hire more people" because the current setup is "working" and isn't considered critical to the rest of the business when it isn't tech related.

chronolitus 8y ago

Even cowboys don't herd cattle by themselves...

neals 8y ago

No I totally agree, looking back on my actions, rm -rf'ing the production database was not a productive use of my time.

ams6110 8y ago

SQL fixes in production don't have to be that risky. Just run a SELECT first with the same predicates and be sure that's what you want. In some cases this can be better than trying to develop a fix using a test system which may not have the same data. Same with "rm -r" --- run an "ls" first. Sure it's not ideal but life isn't ideal. When you have a production problem, you need to fix production.

stickfigure 8y ago

I think you're making a number of assumptions about the size and maturity of the parent's organization; it could be just one person. With the right tools and processes, it's possible to build very successful companies with tiny engineering teams. Developers run queries against prod because there's nobody else to do so. The risk of mistakes is mitigated by the increased situational awareness and the developer quality and communication in 3-teams vs 30-teams. Neither approach is inherently 'wrong', although running a 30-person team the same way as a 3-person team (or vice versa) can have nasty consequences.

perl4ever 8y ago

I worked in a place for years where we had access to production databases, to do updates, deletes and so on. We had a certain stylized way of doing things to prevent irreversible errors. If you were going to delete data for instance, you always created a backup table first with 100% of the columns for the rows of interest, then selected from it to verify it looked reasonable, and then deleted using the backup table to correlate rows to the production table using the primary key. And then another analyst reviewed your work (originally two levels of review, later reduced to one). The backup tables and comments in a ticket including a copy of the SQL run were kept indefinitely, so even if the review missed something it generally was correctable. The DBAs in the company did think SQL should be reviewed in advance, but that's not how our department did it. I think it's arguable that, given that reviewers before or after doing something dangerous may miss things, it's better to establish safe practices and if you do that, then you don't really need a review in advance.