So I got a question from a developer today who was trying to SELECT NULL values from a table. As I have been asked about this many times in the past, I decided to write a little post about it.

mysql> SHOW CREATE TABLE testing_null \G
*************************** 1. row ***************************
Table: testing_null
Create Table: CREATE TABLE `testing_null` (
`id` int(11) unsigned NOT NULL auto_increment,
`mycolumn` varchar(12) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO testing_null (mycolumn) VALUES('ho'), (NULL), (NULL);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM testing_null;
+----+----------+
| id | mycolumn |
+----+----------+
| 1 | ho | …

Should you want to be on the bleeding edge, or in my case, don’t want to download 70MB each day in a daily snapshot (especially when I’m getting build errors), you can use Bit Keeper Free Bit Keeper Client that at least lets you download the MySQL Repository. This client doesn’t allow commits, which is a good thing for those non-gurus in mysql internals (which definitely includes me).

wget http://www.bitmover.com/bk-client.shar
/bin/sh bk-client.shar
cd bk_client-1.1
make

By placing sfioball in your path you can execute.

sfioball bk://mysql.bkbits.net/mysql-5.1 mysql-5.1

This took me about 4 mins, which seemed much quicker then getting a snapshot!

You can then get cracking with my instructions at Compiling MySQL Tutorial 1 - The Baseline.

A good reference …

I'm building a MySQL Cluster which you can see pictured on the right. Partly it is for serious learning and testing purposes, and partly because it is winter Down Under and this keeps the office warm. The smallest desktop box goes particularly well under the desk as a heated foot rest :-)

• Management Node: PIII 700 Dell Laptop mal (My old faithful laptop.)
• SQL Nodes: 2 x PIII 800 Dell Optiplexes inara, river (Optiplexii? Different form factor, doh! How messy. Had these two boxes laying around the office already.)
• Data Nodes: 4 x PIII 1GHz clone boxes w/ 512Mb RAM simon, jayne, book, wash (Found these boxes cheap at an ex-gov computer market. Another 1Gb RAM each on order - how rare SDRAM is becoming - for some DataMemory breathing space.)

I have several …

In an earlier article I explored the finer points of multi-table, cross-database deletes in MySQL, but I didn’t get it quite right. This article explains the subtleties I’ve discovered since then. This is more complex than the MySQL manual documents. In the earlier article, I wrote The solution is not to alias the tables. It’s less convenient, but it’s the only thing to do sometimes. Now I find it is possible to alias the tables, with certain restrictions.

I'm building a MySQL Cluster which you can see pictured on the right. Partly it is for serious learning purposes, and partly because it is winter Down Under and this keeps the office warm. The smallest desktop box goes particularly well under the desk as a heated foot rest :-)

• Management Node: mal - PIII 700 Dell Laptop (My old faithful laptop.)
• SQL Nodes: inara, river - 2 x PIII 800 Dell Optiplexes (Optiplexii? Different form factor, doh! How messy. Had these two boxes laying around the office already.)
• Data Nodes: simon, jayne, book, wash - 4 x PIII 1GHz clone boxes w/ 512Mb RAM (Another 1Gb each on order - how rare SDRAM is becoming - for some DataMemory breathing space.)

I have several other machines that participate sometimes, bringing the cluster …

Baron wrote nice article comparing locking hints in MySQL and SQL Server.

In MySQL/Innodb LOCK IN SHARE MODE and SELECT FOR UPDATE are more than hints. Behavior will be different from normal SELECT statements. Here is simple example:

PLAIN TEXT SQL:

1. SESSION1:
2. mysql> begin;
3. Query OK, 0 rows affected (0.00 sec)
4.  
5. mysql> INSERT INTO tst VALUES(1);
6. Query OK, 1 row affected (0.00 sec)
7.  
8. SESSION2:
9. mysql> begin ;
10. Query OK, 0 rows affected (0.00 sec)
11.  
12. mysql> SELECT * FROM tst;
13. Empty SET (0.01 sec)
14.  
15. #Session2 does not see any rows as transaction was not commited yet.
16.   …

Just to confirm my earlier confusion about verified snapshots at Compiling MySQL Tutorial 1 - The Baseline.

“Daily snapshot sources are only published, if they compiled successfully (using the BUILD/compile-pentium-debug-max script) and passed the test suite (using make test). If the source tree snapshot fails to compile or does not pass the test suite, the source tarball will not be published.”

Seems the fine print at MySQL Database Server 5.1: Beta snapshots also states this. Well, need to take my RTFM pill there.

Thanks to Lenz for putting the record straight, and helping with my Forum Post. Seems I did uncover a Bug, now recorded as …

I worked with Microsoft SQL Server 2000 at my previous employer, and was accustomed to explicitly defining the locking mode I wanted, by providing lock hints on queries. MySQL gives less control over locking, but there are ways to control the type of locks a query will acquire. This article explores those techniques.

In case people haven’t heard, Solid announced our new solidDB for MySQL Beta at OSCON last week! This is a fully transactional storage engine that supports full ACID compliance with transaction isolation levels, optimistic and pessimistic row-level locking, and multi-version concurrency control. It’s completely Free/Libre Open Source Software, released under GPLv2. It is available at http://dev.soliddb.com/download/