0

This is fresh mysql install from Debian apt.

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.47, for debian-linux-gnu (x86_64) using readline 6.3
..
Server version:         5.5.47-0+deb8u1-log (Debian)
Protocol version:       10
Connection:             Localhost via UNIX socket
..
Note that you are running in safe_update_mode:
UPDATEs and DELETEs that don't use a key in the WHERE clause are not allowed.

This is how it's set:

mysql> show global variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | OFF   |
+------------------+-------+

mysql> show variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | ON    |
+------------------+-------+

There's no sql_safe_updates setting on any config file (my.cnf or ~/.my.cnf etc.).

Why it's set to ON for my session (run mysql from root).

  • And no init_command = SET sql_safe_updates = ON ? – Rick James Feb 23 '16 at 17:47
2

From here, you obtain the command to change the setting for sql_safe_updates (from the mysql client command line). It's fairly simple - SET SQL_SAFE_UPDATES=0; (or 1), though as you point out, it can't be modified in my.ini or my.cnf.

However, from this post on StackOverflow, you have a method to run SQL (and server) commands on server startup.

Briefly, you create a file called (arbitrary) <my_file_name>.sql and put the command above setting the sql_safe_updates in that file.

Then, in the [mysqld] section of the my.ini (or my.cnf), you put the line

init-file="C:/xxx/yyy/<my_file_name>.sql"

To answer your specific question, there is a reason that the default is ON.

This is because many software developers are not very proficient in SQL. To compound this problem, many of them use frameworks which produce SQL which is (ahem...) not optimal. If one ran a command

DELETE FROM TABLE <no where clause>

on a production system, this could cause havoc (and has done, judging by the number of posts here pleading for help with a deleted table).

Note: I tested this on Windows (don't have Linux up and running at the moment).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.