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
Error 1175 aala ki khup students laghech SQL_SAFE_UPDATES = 0 karun visartat – aani nantar kharach sagla table udavtat. Error mhanje MySQL tumhala vachvtoy! Key column (student_id) var WHERE vapra. Band karaychach asel tar kaam zalyavar laghech parat = 1 kara.
Practice task
Turn on safe updates and trigger error 1175 on purpose. Then run a DELETE inside a transaction, check the count, and ROLLBACK.