staying humble

I learn something new every day at work. Some of the stuff I learn is pretty big – but the real humbling ones are the small. A couple of little things that I learned recently:

1) Did you know that NULL and empty string “” can be valid elements of an ENUM column – even if they are not explicitly named!

From the ENUM manual page:

The value may also be the empty string (”) or NULL under certain circumstances:
* If you insert an invalid value into an ENUM (that is, a string not present in the list of allowed values), the empty string is inserted instead as a special error value. This string can be distinguished from a “normal” empty string by the fact that this string has the numerical value 0. More about this later.

If strict SQL mode is enabled, attempts to insert invalid ENUM values result in an error.

* If an ENUM column is declared to allow NULL, the NULL value is a legal value for the column, and the default value is NULL. If an ENUM column is declared NOT NULL, its default value is the first element of the list of allowed values.

2) The mysql command line utility can ignore statements except those for the default database named on the command line using the –one-datebase option.

This is can be extremely useful for skipping updates to other databases in the binary log or a backup mysqldump file. Wish I knew about this years ago.

Ex:
shell> mysql -uroot -ppwd --one-database db1 < /path/backup.sql

3) In 5.1 with InnoDB you can reduce DML locking by using the READ_COMMITTED isolation level. Harrison, one of my senior colleagues, even blogged about it.

Until then, I thought that the isolation level dealt only with the reading of data and had nothing to do with the locking of it. My ignorance was exposed to the light and I am still wrapping my brain around it, but I think I have finally gotten used to the feeling now.

Just when I think I have the basics - something little comes along to knock me back into my place. Anyway - back to work!