Is there a way of detecting overflow in mysql?

Question

After some time trying to kill this bug, I was testing the sql query directly:

UPDATE noticias SET
    data_destaque_ini='2013-12-03', data_destaque_fim='',
    fotoDestaque_x=-3, fotoDestaque_y=-102,
    fotoDestaque_escala=154, destacar=1
WHERE idNoticia=3

But fotoDestaque_escala keeps the same value as before: 127. Hum, this is a "round" number, right? So even without any sql error message, I opened the structure and that's it: the column was set to TINYINT(-127 to 127), and changing it to SMALLINT solved the problem.

But thinking about the future, I'm alarmed with this behavior of MySQL: I passed too big a value for the column, MySQL saved what was possible, cut the rest, and didn't tell anything about - the client remains in the dark!

So, is there a way to configure or detect, somehow, an overflow? If not, I'm thinking about to make in my library a php level test...

Answer 1

Directly taken from the 11.2.6 Out-of-Range and Overflow Handling in the MySQL documentation:

When MySQL stores a value in a numeric column that is outside the permissible range of the column data type, the result depends on the SQL mode in effect at the time:

• If strict SQL mode is enabled, MySQL rejects the out-of-range value with an error, and the insert fails, in accordance with the SQL standard.
• If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.

So if you want an error, you need to put MySQL into strict SQL mode. How to change modes is described in 5.1.7 Server SQL Modes.

Answer 2

By default (as opposed to strict mode), MySQL raises a warning when such overflow / underflow takes place:

mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO t VALUES (128);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'i' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

Notice the "Query OK, 1 row affected, 1 warning" message at statement #2.

The SHOW WARNINGS; statement can be issued as a regular SQL query from your application, and the result can be parsed like any regular result set.

Checkout the manual page for this statement, there are a few interesting options.