About (Web) Programming, Linux, Database and Open Source

MySQL doesn't allow us to set default value for field with TEXT data type. It caused MySQL often return error message "Field '' doesn't have a default value". And it occur because we do not set value for that field when execute SQL statement, while that field has no default value.

Initially this made me confused, because MySQL on my Ubuntu allow me to not set value for that field and not return any messages. While I use MySQL on Windows, it always refuse and give error message above. This caused by different of sql-mode parameter.

What is sql-mode
MySQL can run for several mode also can be implemented on different client. These modes are used to support and do validate SQL statement. Here lists of mode that can be used for sql-mode.

On Ubuntu, sql-node parameter is not set by default, while on Windows default value for sql-node is "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION". Cause of MySQL return error message above is STRICT_TRANS_TABLES

STRICT_TRANS_TABLES

It make MySQL change the invalid value to valid value and insert the adjusted value. If the value from that field doesn't be set on SQL statement, then MySQL will fill the field with default value of this field. In other case MySQL give warning than error message and continue process the SQL statement. Because we not set value of that field, and the field doesn't have default value because we cannot define default value for TEXT data type, then MySQL return error message.

Value of sql-node can be checked with this command on MySQL prompt:

SELECT @@GLOBAL.sql_mode;

Or

SELECT @@SESSION.sql_mode;

To change value of sql-node on MySQL runtime:

SET SESSION sql_mode = 'modes';

Or

SET GLOBAL sql_mode = 'modes';

To change value of sql-node permanently, just change this parameter

sql-node = "mode"

on configuration file (my.ini for Windows and my.cnf for Unix, or add parameter --sql-mode="mode" when run MySQL daemon.