MySQL and not null is not good!
On a daily basis I continue to be unimpressed with MySQL when using MyISAM tables. Today I discovered that if you set a field to be non-nullable and then do an insert without specifying the field, MySQL happily completes the insert when I should be getting an error. Here’s an example:
create table test_state (
id bigint not null auto_increment,
date_created datetime not null,
state varchar(2) not null,
primary key (id)
);
insert into test_state (date_created) values (now());
insert into test_state (state) values ('WY');
select * from test_state
+----+---------------------+-------+
| id | date_created | state |
+----+---------------------+-------+
| 1 | 2005-02-17 09:25:28 | |
| 2 | 0000-00-00 00:00:00 | WY |
+----+---------------------+-------+
You’ve got to be kidding me! It would be better if “not null” had no meaning and were simply ignored, whereas this confuses the issue even more. Now, I don’t profess to being a MySQL expert by any stretch and I know this is probably solved with InnoDB, but IMHO this shouldn’t work in MyISAM either! Oh how I miss the days of working with PostgreSQL, Oracle, and DB2.
That said I do enjoy the low administrative overhead of MySQL, it’s low memory footprint, and the excellent gnu readline and pipe support built into the client.
January 13th, 2006 at 3:37 pm
[...] We’ve finally hit the point at work where we’re ready to upgrade from MySQL 4.0 to 5. Data integrity has been a problem since I started at this job. Part of the reason is that MySQL 4.0 has an odd (to put it lightly) interpretation of not null and we still have a fair amount of legacy Perl code running the administrative portion of the site that lets bad data through. The lack of subselects for ad-hoc querying, aggregation functions, stored procedures, and triggers has also grown more irritating knowing that MySQL 5 has those features. [...]
May 28th, 2006 at 11:51 am
[...] As I’ve described before, MySQL has some appalling out of the box settings which will thwart your attempts at good data integrity! They’ve clearly seen the light though and at least give you an option to achieve good data integrity through foreign keys in InnoDB and proper handling of non-nullable fields and invalid values through the servers sql-mode setting. What compounds the problem is that it seems MySQL may have different settings per platform. For example a friends 5.0.21 install on Windows XP had the sql-mode set with relatively strict settings whereas 5.0.18 on my Mac did not. If you’re configuring MySQL for a new greenfield project there are 2 things that you should absolutely always do in your my.cnf (or my.ini in Windows): [...]