Blame it on MyISAM

I reviewed most of the changes from the v4 Google patch today. My head hurts now. During this review I checked whether bugs fixed in the patch have also been fixed in recent releases of official MySQL. I am happy that most of them have been fixed. But some changes will never be accepted, such as the one that added support for INF for FLOAT/DOUBLE columns.

The default value of sql_mode is the empty string. You probably want to change that before your applications come to depend on it. When it is the empty string, invalid values are coerced to valid values on INSERT and UPDATE and a warning is returned. Applications usually ignore the warnings. The coercion includes:

• INT values that are too big are set to the maximum value of an INT. The same is done for BIGINT
  
• INF is changed to MAX_DOUBLE or MAX_FLOAT for a DOUBLE/FLOAT column
• varchar and LOB columns are truncated to not exceed the maximum length
• invalid DATE values are accepted
  

What needs this behavior? MyISAM. For a storage engine that doesn't do rollback, one way to handle invalid data during an INSERT or UPDATE statement is to coerce it to valid values and proceed with the statement. I am not fond of this approach. An alternative for data warehouse workloads is to use an exception table to log rows with invalid data and avoid corrupting non-exception tables.

MyISAM has also made replication semantics and internals much more complex. For example, what is written to the binlog in this case, and has this behavior changed between releases?

begin;
insert into Innodb_table values (1);
insert into Myisam_table values (1);
rollback;

I think that MyISAM has its place. It does fast table scans, but InnoDB is much faster on just about everything else. I am just not thrilled with the impact it has had on MySQL. It can be used for tasks where a table or partition is loaded once and then made readonly after the insert. This is a good fit for data warehouse tasks. Although it would be better were multi-core performance improved and the key cache expanded to include data blocks. MyISAM can also be used for scratch tables on a slave.

Drizzle avoided these problems by limiting MyISAM to temporary tables.