11. MySQL Part 2: UPDATE, ALTER, DELETE, Keys, Constraints and Users
11.2 Safe Update Mode, Error 1175 and Transactions
MySQL madhe ek "seat belt" aahe – safe update mode. To chalu asla ki key column var WHERE (kiwa LIMIT) nasleli UPDATE/DELETE MySQL chalvu det nahi. MySQL Workbench madhe to default chalu asto.
SET SQL_SAFE_UPDATES = 1; -- turn on for this session
SELECT @@SQL_SAFE_UPDATES; -- 1 = on
UPDATE students SET fees_paid = 0; -- no WHERE → blocked
-- ERROR 1175 (HY000): You are using safe update mode and you tried to update
-- a table without a WHERE that uses a KEY column.
UPDATE students SET fees_paid = 0 WHERE city = 'Pune'; -- city is not a key → also blocked
UPDATE students SET fees_paid = 0 WHERE student_id = 3; -- key column → allowed
| Question | Answer |
|---|---|
| What does it block? | UPDATE and DELETE without a WHERE on a key (indexed) column, or without LIMIT |
| Why is it useful? | Stops accidental full-table changes from a missing or wrong WHERE |
| Where is it on by default? | MySQL Workbench (Edit › Preferences › SQL Editor › "Safe Updates"); the mysql client with --safe-updates / --i-am-a-dummy |
| How to do a planned bulk change? | Use a key in WHERE, or SET SQL_SAFE_UPDATES = 0; for that session only, then set it back to 1 |
Transactions – your undo button (InnoDB tables):
START TRANSACTION;
DELETE FROM enrollments WHERE score < 50;
SELECT COUNT(*) FROM enrollments; -- check the result
ROLLBACK; -- undo! (or COMMIT; to keep)
Why this matters for security
Integrity (अखंडता) is one pillar of the CIA triad. Safe updates, transactions and backups protect integrity against both mistakes and attackers. Many real data-loss incidents are a single unreviewed UPDATE or DELETE.
Ravindra Bagale's Tip
When Error 1175 appears, many students immediately set SQL_SAFE_UPDATES = 0 and forget about it – and later really do wipe the whole table. The error means MySQL is saving you! Use a WHERE on a key column (student_id). If you must switch it off, set it back to = 1 as soon as the work is done.
Ravindra Bagale's Tip – मराठी
Error 1175 आला की बरेच students लगेच SQL_SAFE_UPDATES = 0 करून विसरतात – आणि नंतर खरंच सगळा table उडवतात. Error म्हणजे MySQL तुम्हाला वाचवतोय! Key column (student_id) वर WHERE वापरा. बंद करायचंच असेल तर काम झाल्यावर लगेच परत = 1 करा.
Ravindra Bagale's Tip – हिंदी
Error 1175 आते ही बहुत से students तुरंत SQL_SAFE_UPDATES = 0 करके भूल जाते हैं – और बाद में सच में पूरी table उड़ा देते हैं. Error का मतलब है MySQL तुम्हें बचा रहा है! Key column (student_id) पर WHERE इस्तेमाल करो. बंद करना ही हो तो काम होते ही वापस = 1 कर दो.
Practice task
Turn on safe updates and trigger error 1175 on purpose. Then run a DELETE inside a transaction, check the count, and ROLLBACK.