Xaprb

It’s New Year’s Eve, a date that should strike terror into the hearts of many, because tomorrow a bunch of their queries are going to fail.

Queries to “find all birthdays in the next week” and similar are always a nightmare to write. If you want to see a bunch of examples, go look at the user-contributed comments on the MySQL date and time function reference. This post is about a slightly saner way to do that. There’s still some nasty math involved, but a) a lot less of it, and b) at least the query will be able to use indexes[1].

So here’s my tip: instead of storing the user’s full birthdate, just store the month and day they were born. Try it. You’ll love it!

[1] Yes, I know Postgres can index a function. So this can be considered a jab at MySQL, which can’t.

If you’d like some improvements to the Cacti templates (MySQL, Apache, Memcached, and more) I’ve been maintaining, please make your voice heard — either write to the mailing list, or post a new issue (or comment on an existing one) on the issue list. I’ve got a bunch of work underway, including a test suite for ss_get_by_ssh.php.

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.