CODEWITHSUNDEEP
mysql
Robby75
Robby75

Reputation: 3455

Mysql: Generate error on overflow-warning

In MySQL, an overflow creates only a warning and MySQL destroys the data you feed it:

mysql> create table tmp(data tinyint primary key);
Query OK, 0 rows affected (7.83 sec)

mysql> insert into tmp set data = 200;
Query OK, 1 row affected, 1 warning (2.27 sec)

mysql> select * from tmp;
+------+
| data |
+------+
|  127 | 
+------+
1 row in set (0.00 sec)

This is a huge problem, especially when using MySQL via a programming language, where one (usually) does not check any MySQL-warnings. This may cause not only data-corruption, but data-corruption that is hidden and may remain undetected - one of the worst things that may happen in a database.

Is there a way to configure MySQL so that an overflow causes an ERROR (similar to a syntax error) and not only a warning?

Upvotes: 1

Views: 129

Answers (1)

Jens
Jens

Reputation: 69515

See Mysql documentation

you have to activate the strict SQL mode:

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.

Upvotes: 2

Related Questions