MySQL Troubleshooting

Variables That Are Supposed to Change the Server Behavior

Another set of variables affects how the MySQL server handles user input.

I will show a trivial example that clearly shows the effect of setting such a variable. In this case, we will set SQL Mode to STRICT_TRANS_TABLES so that attempts to insert invalid data into transactional tables will be rejected instead of being smoothed over. However, we expect the server to fix the statements, if possible, for nontransactional tables instead of rejecting the statements:

mysql> SET @@sql_mode = 'strict_trans_tables';
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE `myisam` (
    ->           `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->           `a` varchar(50) NOT NULL,
    ->           `b` varchar(50) NOT NULL,
    ->           PRIMARY KEY (`id`)
    ->         ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO `myisam` (id,a) VALUES (1,'a');
ERROR 1364 (HY000): Field 'b' doesn't have a default value

I deliberately issued an erroneous INSERT, omitting a value for the b column. I expect the server to insert an empty string for b. But even though this table uses the MyISAM storage engine, the insert fails with an error message.

The MySQL Reference Manual explains the behavior (see http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html):

STRICT_TRANS_TABLES: If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a single-row statement ...