Xaprb

Stay curious!

Sanity-check features in MySQL

with 8 comments

MySQL has a couple of sanity-check features to help keep you from doing dumb things.

  • max_join_size is a configuration option for the mysqld server program. It throws an error if you write a query that the optimizer estimates will examine more than this number of rows.
  • –safe-updates is a command-line option to the mysql client program. It throws an error if you write an UPDATE or DELETE without a) a WHERE clause that refers to an indexed column or b) a LIMIT clause. It also sets the max_join_size and select_limit variables.

The –safe-updates mysql client option actually sets three variables server-side. Let’s see the effects. First, the defaults:

$ mysql -e 'select @@sql_safe_updates, @@sql_select_limit, @@sql_max_join_size\G'
*************************** 1. row ***************************
 @@sql_safe_updates: 0
 @@sql_select_limit: -1
@@sql_max_join_size: -1

With –safe-updates we get different results:

$ mysql --safe-updates -e 'select @@sql_safe_updates, @@sql_select_limit, @@sql_max_join_size\G'
*************************** 1. row ***************************
 @@sql_safe_updates: 1
 @@sql_select_limit: 1000
@@sql_max_join_size: 1000000

The following demonstrates what happens if you now try to do something silly that might be expensive or dangerous:

mysql> create table t(a int primary key, b int);
Query OK, 0 rows affected (0.01 sec)

mysql> delete from t;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
mysql> delete from t where b = 5;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
mysql> delete from t where a = 5;
Query OK, 0 rows affected (0.01 sec)

Alas, you can’t set sql_select_limit or sql_safe_updates through the server configuration file (my.cnf or my.ini) directly. But you can do that through the init_connect directive.

Written by Xaprb

December 25th, 2009 at 8:23 am

Posted in SQL

Tagged with

«
»

8 Responses to 'Sanity-check features in MySQL'

Subscribe to comments with RSS

  1. a little known, documented, gem that many folks forget, is you can restrict anybody from setting huge per-session buffers too. :)

    mysql> set session sort_buffer_size=2*1024*1024;
    Query OK, 0 rows affected, 1 warning (0.02 sec)

    mysql> select @@session.sort_buffer_size;
    +—————————-+
    | @@session.sort_buffer_size |
    +—————————-+
    | 1048576 |
    +—————————-+
    1 row in set (0.00 sec)

    shane bester

    25 Dec 09 at 4:01 pm

  2. Shane: I did not know that. Tell me how, lest I have to go find out myself :) What’s the warning in that command?

    Xaprb

    25 Dec 09 at 6:58 pm

  3. Xaprb: check bug #29340 for the details

    shane bester

    26 Dec 09 at 1:37 am

  4. Shane, did you really mean to say this is documented? Where is it documented, except in that bug report? I can’t find it with Google searches, and it doesn’t appear in “mysqld –help –verbose” output (on 5.0.75-0ubuntu10.2).

    The bug report is actually kind of silly — for some of these things you could conceive of a DoS attack with too-small settings, not just too-large.

    Xaprb

    26 Dec 09 at 2:11 pm

  5. “If you want to restrict the maximum value to which a variable can be set at runtime with SET, you can define this by using the –maximum-var_name=value command-line option. “

    http://dev.mysql.com/doc/refman/5.1/en/server-options.html

    Rob Wultsch

    26 Dec 09 at 5:54 pm

  6. Thanks Rob!

    Xaprb

    26 Dec 09 at 11:12 pm

  7. [...] Shared Sanity-check features in MySQL. [...]

    Weekly Digest for December 27th | William Stearns

    28 Dec 09 at 12:10 am

  8. You didn’t mention that there’s a synonym for –safe-updates: –i-am-a-dummy

    Gavin Towey

    28 Dec 09 at 5:22 pm

Leave a Reply