Use of MySQL safe update mode

Table data of the MySQL database may require to update or delete anytime based on the requirement. If the update or delete query executes without using the where clause, then all table records will be updated or deleted accidentally. If the safe update mode is enabled, the MySQL error code 1175 will be generated if the update or delete query is executed without using the where clause. But sometimes, it requires deleting all table records or updating all values of a particular column of a table. This problem can be solved by disabling the safe update mode or executing the query with a clause that will match all table rows. The way of enabling or disabling safe update mode to prevent accidental delete or update has been shown in this tutorial.

Pre-requisites:

You have to create a database table with the data in a MySQL database to check the use of Safe Update Mode in MySQL.

Open the terminal and connect with the MySQL server by executing the following command.

$ sudo mysql -u root

Run the following command to create a database named test_db.

CREATE DATABASE test_db;

Run the following command to select the database.

USE test_db;

Run the following query to create a table named customers with five fields.

CREATETABLE customers(
id INTNOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(50),
address TEXT,
contact_no VARCHAR(15));

Run the following INSERT query to insert four records into the customers table.

INSERT INTO `customers` (`id`, `name`, `email`, `address`, `contact_no`) VALUES

('4001', 'Laboni Sarkar', '[email protected]', '34, Dhanmondi 9/A, Dhaka.', '01844767234'),

('4002', 'Tahsin Ahmed', '[email protected]', '123/1, Jigatola, Dhaka.', '015993487812'),

('4003', 'Hasina Pervin', '[email protected]', '280, Shantibagh, Dhaka.', NULL),

('4004', 'Mehrab Ali', '[email protected]', '78, paltan, Dhaka..', '01727863459');

Checking the current value of the safe update mode:

The sql_safe_updates variable is used to enable or disable the safe update mode of MySQL. The safe update mode is disabled by default. Run the following command from the mysql prompt to check the current value of the sql_safe_updates variable.

SHOW VARIABLES LIKE "sql_safe_updates";

The following output shows that the safe update mode is disabled.

Execute update and delete command after enabling the safe update mode:

Run the following commands to enable the safe update mode and check the value of the sql_safe_updates variable.

SET SQL_SAFE_UPDATES = 1;

SHOW VARIABLES LIKE "sql_safe_updates";

The following output shows that the safe update mode is enabled now.

Execute UPDATE query:

Run the UPDATE command without the WHERE clause to check the safe update mode is working or not for the update operation. If the safe update mode is enabled, the error 1175 will be generated for the UPDATE query.

UPDATE customers SET contact_no = '02-458934679';

The following output shows an error generated for the UPDATE query without WHERE clause, and no UPDATE operation has been done.

Suppose the above UPDATE query will be executed from any graphical user interface like PHPMyAdmin where the safe update mode is disabled by default. In that case, the update operation will be done based on the user’s permission. If the user gives permission, then the contact_no field of all records of the customers table will be updated; otherwise, nothing will be done.

Execute DELETE query:

Run the DELETE command without WHERE clause to check the safe update mode is working or not for the delete operation. If the safe update mode is enabled, the error 1175 will be generated for the DELETE query like the UPDATE query.

DELETE FROM customers;

The following output shows an error generated for the DELETE query without WHERE clause, and no DELETE operation has been done.

Suppose the above DELETE query will be executed from any graphical user interface like PHPMyAdmin where the safe update mode is disabled by default. In that case, the delete operation will be done based on the user’s permission, like the update query. If the user gives permission, then all records of the customers table will be deleted; otherwise, nothing will be done.

Execute update and delete command after disabling the safe update mode:

Run the following commands to disable the safe update mode and check the value of the sql_safe_updates variable.

SET SQL_SAFE_UPDATES = 0;

SHOW VARIABLES LIKE "sql_safe_updates";

The following output shows that the safe update mode is disabled now.

Like the previous UPDATE command, run the following UPDATE command without WHERE clause to check the update operation works or not after disabling the safe update mode.

UPDATE customers SET address = 'Dhaka';

The following output shows that the update operation has been completed without any error, and four rows of the customers table have been affected.

Run the following SELECT query to check the current content of the customers table.

SELECT * FROM customers;

The following output shows that the string value has updated all address field values, ‘Dhaka’.

Like the previous DELETE command, run the following DELETE command without WHERE clause to check the delete operation works or not after disabling the safe update mode.

DELETE FROM customers;

The following output shows that the delete operation has been completed without error.

Run the following SELECT query to check the current content of the customers table.

SELECT * FROM customers;

The following output shows that all records of the customers table have been deleted.

Conclusion:

The safe update mode of MySQL is an essential feature to prevent unwanted updates or delete operations by mistake. The way of enabling and disabling this mode by setting the required variable has been shown in this tutorial. How the update and delete operations without the WHERE clause work based on the safe update mode have also been shown here by using a table.