Detailed explanation of the use of the MySQL parameter sql_safe_updates in the production environment

Preface

In case of application bug or DBA misoperation, the entire table may be updated: update delete. MySQL provides sql_safe_updates to limit this operation.

set sql_safe_updates = 1;

After setting, the SQL execution without where conditions in update and delete will be restricted, which is more strict. It will have an adverse impact on the existing online environment. Strict audits of new systems and applications can ensure that full table updates do not occur.

CREATE TABLE working.test01 (id INT NOT NULL AUTO_INCREMENT,NAME VARCHAR(20),age INT,gmt_created DATETIME,PRIMARY KEY(id));

 insert into test01(name,age,gmt_created) values('xiaowang',2,now());
 insert into test01(name,age,gmt_created) values('huahua',5,now()); 
 insert into test01(name,age,gmt_created) values('gougou',9,now()); 
 insert into test01(name,age,gmt_created) values('heihei',12,now()); 
 insert into test01(name,age,gmt_created) values('baibai',134,now()); 

# No index update on the filter field
update test01 set name = 'xiaoxiao' where age = 2 ;
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 the entire table update test01 set name = 'xiaoxiao';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
# Add limit update update test01 set name = 'xia' limit 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

# Create a new index create index idx_age on test01(age);

update test01 set name = 'xiaoxiao' where age = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

update test01 set name = 'hhh' where age = 9 limit 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

alter table test01 drop index idx_age;
create index idx_age_name on test01(age,name);


update test01 set age = 100 where name = 'hhh';
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 test01 set age = 100 where name = 'hhh' limit 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Therefore, when updating, if there is no where condition or the where condition is not an index field, limit must be used; when there is a where condition, the index field

Recently, I found another problem at work. MySQL sql_safe_updates does not support subquery updates.

Considering that developers may accidentally update data sometimes, the MySQL instances of the online database are required to set sql_safe_updates=1 to avoid updates and deletes without indexes.

As a result, one day the developer found that the following SQL could not be executed correctly:

update t1 set col2=1 where key1 in (select col2 from t2 where key2='ABcD');

The error is as follows:

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

In other words, it is not possible to update the where condition that is not indexed. After searching, I found that it is indeed not possible. Even if key1 and key2 are the indexes of t1 and t2 respectively [I can't change them to primary keys]. Description: Subquery updates are not supported.

I googled and found that someone else has asked this question before. .

http://stackoverflow.com/questions/24314830/query-not-getting-executed-if-supplied-a-nested-sub-query

Final solution:

1) Modify the session-level parameters: set sql_safe_updates=0; and execute the update operation. Exit Terminal.

2) Program processing: first select col2 from t2 where key2='ABcD' to obtain data, then loop through the results, and use update t1 set col2=1 where key1=？ Updated in batches. It is recommended to use program processing. Temporary modification of variables is not a long-term solution.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.