A new feature to MySQL Version 5 was the introduction of SQL_MODE to support STRICT… or TRADITIONAL values.
This feature enabled a closer compatibility to other RDBMS products. MySQL by default performs a number of silent data changes which do not help in providing a level of data integrity if you come from a more traditional background. MySQL by default represents these as warnings, while with an appropriate SQL_MODE, these are in turn treated as errors.
How does Drizzle handle this? Very simple. There is no SQL_MODE. By default Drizzle handling a strict mode of producing errors for any invalid data. The following are some test case examples showing the varying conditions.
Test Case
select version();
create database if not exists test;
use test;
drop table if exists t1;
create table t1(i1 int, c1 char(10), d1 timestamp);
#Pass Tests
insert into t1(i1) values (500000000);
insert into t1(c1) values('1234567890');
insert into t1(i1) values (5000000000);
#Fail Tests
insert into t1(c1) values('12345678901');
insert into t1(d1) values(now());
insert into t1(d1) values(0);
Drizzle Output
drizzle> select version();
+-------------------------+
| version() |
+-------------------------+
| 2009.03.970-development |
+-------------------------+
1 row in set (0.00 sec)
drizzle> create database if not exists test;
Query OK, 1 row affected (0.01 sec)
drizzle> use test;
Database changed
drizzle> create table t1(i1 int, c1 char(10), d1 timestamp);
Query OK, 0 rows affected (0.17 sec)
#Pass Tests
drizzle> insert into t1(i1) values (500000000);
Query OK, 1 row affected (0.08 sec)
drizzle> insert into t1(c1) values('1234567890');
Query OK, 1 row affected (0.05 sec)
drizzle> insert into t1(d1) values(now());
Query OK, 1 row affected (0.02 sec)
#Fail Tests
drizzle> insert into t1(i1) values (5000000000);
ERROR 1264 (22003): Out of range value for column 'i1' at row 1
drizzle> insert into t1(c1) values('12345678901');
ERROR 1406 (22001): Data too long for column 'c1' at row 1
drizzle> insert into t1(d1) values(0);
ERROR 1685 (HY000): Received an invalid value '0' for a UNIX timestamp.
MySQL Output
mysql> create database if not exists test;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.05 sec)
mysql> create table t1(i1 int, c1 char(10), d1 timestamp);
Query OK, 0 rows affected (0.16 sec)
mysql> #Pass Tests
mysql> insert into t1(i1) values (500000000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(c1) values('1234567890');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(i1) values (5000000000);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> #Fail Tests
mysql> insert into t1(c1) values('12345678901');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into t1(d1) values(now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(d1) values(0);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------------+------------+---------------------+
| i1 | c1 | d1 |
+------------+------------+---------------------+
| 500000000 | NULL | 2009-04-06 12:14:21 |
| NULL | 1234567890 | 2009-04-06 12:14:21 |
| 2147483647 | NULL | 2009-04-06 12:14:21 |
| NULL | 1234567890 | 2009-04-06 12:14:21 |
| NULL | NULL | 2009-04-06 12:14:21 |
| NULL | NULL | 0000-00-00 00:00:00 |
+------------+------------+---------------------+
6 rows in set (0.00 sec)
MySQL SQL_MODE=STRICT_ALL_TABLES Output
mysql> set sql_mode = STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.32 |
+-----------+
1 row in set (0.00 sec)
mysql> create database if not exists test;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t1(i1 int, c1 char(10), d1 timestamp);
Query OK, 0 rows affected (0.12 sec)
mysql> #Pass Tests
mysql> insert into t1(i1) values (500000000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(c1) values('1234567890');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(i1) values (5000000000);
ERROR 1264 (22003): Out of range value for column 'i1' at row 1
mysql> #Fail Tests
mysql> insert into t1(c1) values('12345678901');
ERROR 1406 (22001): Data too long for column 'c1' at row 1
mysql> insert into t1(d1) values(now());
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(d1) values(0);
Query OK, 1 row affected (0.00 sec)
Update
Thanks to Robert Wultsch who highlighted to me that SQL_MODE has been around since 4.1
.
