MySQL - Few Observations

I have observed few things about MySQL, what it supports and how it supports.

MySQL supports different storage engines. Storage Engines not only describe how Indexing is done but also features supported. Primary Key is supported by MyISAM and InnoDB(These are widely used Stored Engines). But Foriegn Key Constraints are supported by InnoDB only.

MySQL works in different sql modes namely, STRICT_ALL_TABLES, ANSI, STRICT_TRANS_TABLES, TRADITIONAL, ALLOW_INVALID_DATES etc. SQL Mode tells, what kind of MySQL Syntax it supports and level of data validation checks it puts on Input Data. By Default, sql_mode is empty. sql_mode can be seen by
mysql > select @@session.sql_mode;

Importance of sql_mode:
My table definition is person(id int unsigned, name char(10), primary key(id));

Behaviour of this table under default sql_mode(which is empty).
mysql > Insert into person values(-1,'sarma');
Query OK, 1 row affected, 1 warning (0.07 sec)

Warning (Code 1264): Out of range value adjusted for column 'id' at row 1
Value is adjusted so that it will fit into id, if you see value of id, u find it to be Zero.

So, for first time, MySQL does not issue Error, but when you attempt to do it second time, inserting another -Ve value, it also adjusted to Zero again, causing Primary Key violation, so issues Error 1062(Duplicate Entry).

changing sql_mode to STRICT_ALL_TABLES will solve this problem.

mysql > SET SESSION sql_mode='STRICT_ALL_TABLES';
will change for this Session for this client.
If you want to change for all clients and for all sessions(globally), then better to change in /etc/my.cnf file.

It solves many problems, as MySQL issues warning when there is a mismatch between expected and actual values for a column. For Example, column Dt expects value to be in 'yyyy-mm-dd' and actual value is 'dd-mm-yyyy', it simply rejects rather than making it '0000-00-00'.


@Koteswara sarma