Safe Mode in MySQL

1. Use the INSERT Statement to Insert Entries in a Table in MySQL
2. Turn the Safe Mode On in MySQL
3. Turn the Safe Mode Off in MySQL

This tutorial explores the concept of safe mode in the MySQL database.

Most businesses and organizations that use MySQL for data analysis or visualization need to sort or fetch different table values of their users based on the date of entry or expiry or something else altogether.

Another requirement is to fetch data, update data, alter or insert data based on a particular data in certain tables in MySQL. This operation is possible when the safe mode is deactivated.

However, sometimes, the data that needs to be updated or inserted in a particular table is not possible because the safe mode might be on. It ensures that the table is not altered under any situation.

This tutorial will extend this knowledge by turning our safe mode on and off and then inserting or updating information in a MySQL table.

First, let us switch off the safe mode in MySQL using the following query in MySQL Workbench.

SET SQL_SAFE_UPDATES = 0;

The query would not reflect any altercations in any rows but ensure that the safe mode is disabled.

However, before we begin, we create a dummy dataset to work with. Here we create a table, student_dates_3, along with a few rows.

-- create the table student_dates_3
CREATE TABLE student_dates_3(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_date date,
  primary key(stu_id)
);

Use the INSERT Statement to Insert Entries in a Table in MySQL

The above query creates a table with the name student_dates_3. Let us add data for a few students using the INSERT statement.

This operation can be done as follows.

-- insert rows to the table student_dates_3
INSERT INTO student_dates_3(stu_id,stu_firstName,stu_date)
 VALUES(1,"Preet",STR_TO_DATE('24-May-2005', '%d-%M-%Y')),
 (2,"Dhruv",STR_TO_DATE('14-June-2001', '%d-%M-%Y')),
 (3,"Mathew",STR_TO_DATE('13-December-2020', '%d-%M-%Y')),
 (4,"Jeet",STR_TO_DATE('14-May-2003', '%d-%M-%Y')),
 (5,"Steyn",STR_TO_DATE('19-July-2002', '%d-%M-%Y')),
 (6,"Rutvik",STR_TO_DATE('16-January-2001', '%d-%M-%Y'));

The code would enter the student data in the table student_dates_3. We can visualize this table with the following command.

SELECT * from student_dates_3;

The above code block would generate the following output.

stu_id	stu_firstName	stu_date
1		Preet			2005-05-24
2		Dhruv			2001-06-14
3		Mathew			2020-12-13
4		Jeet			2003-05-14
5		Steyn			2002-07-19
6 		Rutvik			2001-01-16

As shown in the above table, we have successfully entered dates in our table student_dates_3. Now let us delete this table by turning the safe mode on.

Turn the Safe Mode On in MySQL

We need to use the following query in MySQL to turn the safe mode on.

SET SQL_SAFE_UPDATES = 1;

Now let us get rid of some data in our table. Let us eliminate students with the first name set as Preet. We can do this using the following query.

DELETE FROM student_dates_3 WHERE stu_firstName like 'Preet';

However, we cannot perform this operation since we have turned the safe mode on. We will get the following error if we run the query on the MySQL server.

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

This message tells us that we first need to disable the safe mode in MySQL to make the operation successful. To disable safe mode, toggle the Preferences > SQL Editor option and reconnect.

Turn the Safe Mode Off in MySQL

Let us switch the safe mode off and then try this operation again. We can do this using the following query.

SET SQL_SAFE_UPDATES = 0;

This query would ensure that the safe mode has been disabled. Now, let us delete certain rows from our student_dates_3 table. We can do this using the following query.

DELETE FROM student_dates_3 WHERE stu_firstName like 'Rutvik';

As we can see from the query, we are trying to eliminate Rutvik. Now that we have turned the safe mode off, we should operate without the Error Code: 1175.

The query’s output can be visualized with the table visualization query described below.

SELECT * from student_dates_3;

The query’s output can be visualized using the following query.

stu_id	stu_firstName	stu_date
1		Preet			2005-05-24
2		Dhruv			2001-06-14
3		Mathew			2020-12-13
4		Jeet			2003-05-14
5		Steyn			2002-07-19

Thus, as we can see, the SAFE MODE plays a critical role when restricting access to our data in certain situations.

We can help restrict access to updates and deletions in our table using the safe mode. It is also really easy to toggle through this mode and make editions in our table.

Therefore, using the SET clause and the DELETE statement to make updates, we can efficiently understand the usage of the safe mode in MySQL.

Some other related topics that can help learn the concept better are below.

1. SET clause in MySQL.
2. DELETE statement in MySQL.