Using the --safe-updates
Option
For beginners, a useful startup option is --safe-updates
(or --i-am-a-dummy
, which has the same effect). It is helpful for cases when you might have issued a DELETE FROM
statement but forgotten the tbl_name
WHERE
clause. Normally, such a statement deletes all rows from the table. With --safe-updates
, you can delete rows only by specifying the key values that identify them. This helps prevent accidents.
When you use the --safe-updates
option, mysql issues the following statement when it connects to the MariaDB server:
SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;
See , "Server System Variables".
The SET
statement has the following effects:
- You are not permitted to execute an
UPDATE
orDELETE
statement unless you specify a key constraint in theWHERE
clause or provide aLIMIT
clause (or both). For example:UPDATE
tbl_name
SETnot_key_column
=val
WHEREkey_column
=val
; UPDATEtbl_name
SETnot_key_column
=val
LIMIT 1; - The server limits all large
SELECT
results to 1,000 rows unless the statement includes aLIMIT
clause. - The server aborts multiple-table
SELECT
statements that probably need to examine more than 1,000,000 row combinations.
To specify limits different from 1,000 and 1,000,000, you can override the defaults by using the --select_limit
and --max_join_size
options: