Is there a way of detecting overflow in mysql?
mysql, php, sql
Solution
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.
Problem
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...