Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a MySQL database on Ubuntu 10.04. Every time I work on it I'm worried that I am accidentally going to drop a table or the entire database.

Is there anyway to configure MySQL to give a "warning" before going forward with executing the command in question?

share|improve this question

3 Answers 3

Start a transaction before running your commands, and commit it when you're satisfied.

This requires A. InnoDB tables, and B. disabling implicit transactions.

share|improve this answer
    
And backups are always welcome –  Janis Veinbergs Jul 20 '12 at 11:13
    
Are table-level commands (alter, and so on) now transaction compliant? –  Tom O'Connor Jul 20 '12 at 11:16
    
Thanks! InnoDB tables I have. But how do I disable implicit transactions? In the config file? –  user1488723 Jul 20 '12 at 11:19

One of the developers I used work with got into a habit of always writing the WHERE clause first and only then going back and writing the UPDATE or DELETE part of the query.

It's also a good habit to always write a SELECT query and verify that it matches the rows you want before changing the SELECT part to UPDATE.

Alternatively, you can use the --i-am-a-dummy setting on your MySQL client. This stops you (and everyone else) from doing UPDATE or DELETE queries without a WHERE clause. It also has some other effects that can prevent long running queries and large result sets.

None of this helps if you are running DROP queries. For that, you are better off creating a separate user and making sure your normal user doesn't have the DROP privilege. You will most likely get a permission denied error and have to log out and then log back in as the more privileged user before trying the query again, giving you plenty of time to re-think the query.

share|improve this answer
    
Thank you! Now I know:) –  user1488723 Jul 20 '12 at 12:30

The short answer to the question you asked is no. As others have already described, there are ways too mitigate risks somewhat (I like the use of transactions but they're not always an option) but there's a fundamental assumption that only those who know what they're doing will use the command line.

You don't accidentally drop a table or database simply because the "drop" command is not valid for other types of queries. If you were to type "drop" when you meant something else you can't seriously expect software to protect you from yourself. This is a human issue, not a technological one, and you simply have to learn to be a lot more careful.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.