Thursday, November 30, 2006

The hidden risks of SQL MODE

MySQL 5.0 introduces improved SQL modes, which can fine tune the way your server behaves. If you are a long term MySQL user, you may be familiar with the speed for accuracy trade-off. MySQL has a default for each field, and guesses a value when you don't provide an appropriate one when inserting or updating. If this behavior is not acceptable to you, you can now tell the server to be less permissive. Check out an article by Robin Schumacher, where this concept is explained thoroughly.

If you look at the manual, though, you will see that the SQL modes are quite a few, and you may be tempted to combine some of them to control every tiny part of the server input.
Beware, though. There are some pitfalls that you should be aware of. Let's walk through an example.

SET SQL_MODE='';
select @@sql_mode, cast(1 as unsigned) - cast(2 as unsigned);
+------------+-------------------------------------------+
| @@sql_mode | cast(1 as unsigned) - cast(2 as unsigned) |
+------------+-------------------------------------------+
| | 18446744073709551615 |
+------------+-------------------------------------------+
What is this? It is a subtraction between two unsigned values. There is an overflow in the result, and then the result of 1-2 becomes the highest BIGINT value minus one.
We can control this behavior, and use a specific SQL_MODE, NO_UNSIGNED_SUBTRACTION, to tell the server that it should not allow a subtraction between unsigned values, and treat them as signed instead.
SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
select @@sql_mode, cast(1 as unsigned) - cast(2 as unsigned);
+-------------------------+-------------------------------------------+
| @@sql_mode | cast(1 as unsigned) - cast(2 as unsigned) |
+-------------------------+-------------------------------------------+
| NO_UNSIGNED_SUBTRACTION | -1 |
+-------------------------+-------------------------------------------+
Fine. Now we know that we can take control of subtractions. But there is something more to know. The SQL MODE sticks to each procedure, function, or trigger, meaning that each routine is executed using the SQL mode that was active at creation time. This could lead to surprising results.
set sql_mode='';
drop function if exists subtraction;
create function subtraction(x int unsigned, y int unsigned)
returns int
deterministic
return x - y;

SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';

select @@sql_mode, subtraction(1,2), cast(1 as unsigned) - cast(2 as unsigned);
+-------------------------+------------------+-------------------------------------------+
| @@sql_mode | subtraction(1,2) | cast(1 as unsigned) - cast(2 as unsigned) |
+-------------------------+------------------+-------------------------------------------+
| NO_UNSIGNED_SUBTRACTION | 2147483647 | -1 |
+-------------------------+------------------+-------------------------------------------+
Look here. We set the SQL_MODE to NO_UNSIGNED_SUBTRACTION, because we want to avoid that unpleasant effect, but the subtraction function was created with a different SQL_MODE.
Therefore, the operations inside such function will be affected by the stored SQL_MODE, regardless of the one that is active at the moment.

Whenever your result depends on a specific SQL_MODE, always check which mode is associated with the stored routines or triggers that you are using.
SELECT
routine_name,. sql_mode
FROM
information_schema.routines
WHERE routine_schema='test'
AND routine_name='subtraction'
AND routine_type='function';
+--------------+----------+
| ROUTINE_NAME | SQL_MODE |
+--------------+----------+
| subtraction | |
+--------------+----------+

There is also a more complex example involving triggers.

2 comments:

Mike Kruckenberg said...

This is good to know.

Roland Bouman said...

Yes I got bitten too.

you can run into big trouble when you've had a server and relied for ages on the sql_mode set in the my.cnf

All of a sudden, you want to run a new application, with it's own schema, next to the others - turns out the sql_mode is too restrictive for that app.

So, what can you do? Install a separate server for that one application? No, I want to be able to share data easily. Maybe modify all the other applications and have them set the sql_mode at the session level? ouch...this begins to defeat it's purpose...

It would be nice to be able to bind the DDL-time sql_mode not only to procedures, but also to databases and even tables (yes, you need that - try timestamp columns with the NO_ZERO_IN_DATE and NO_ZERO_DATE modes on, but the ALLOW_INVALID_DATES off.

Come to think of it, as some sql modes alter the way MySQL behaves in providing default values, the natural grain for sql mode is the column level.

Vote on Planet MySQL