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/

i?ve updated my patch for new-style mysql authentication for ruby/mysql, with a new test case for the change_user method (and support for same with new authentication).

i?ve even tested this against a 4.0 server, so i?m pretty sure i didn?t break anything.

I'm still working on the DELMITER issue. I have finished and committed my patch for BUG#11312, but also sent out a message to the developers asking for alternative suggestions:

Date: Fri, 4 Aug 2006 10:10:29 +0200
From: "Greg 'groggy' Lehey" <grog@mysql.com>
Subject: Thoughts on DELIMITER in mysqbinlog

BUG#11312 describes problems handling stored procedures in the output
of mysqlbinlog.  The DELIMITER kludge is handled entirely in the
client, so it never makes it to the binlog.  We have a patch, but we
don't like it: it involves connecting to the server to perform the
dump, and it's very intrusive.

I've done some examination and discussed with Lars, and we've
identified two solutions of varying ugliness:

1.  When processing a stored procedure or function, generate DELIMITER
    statements and log them to the binlog.

2.  When printing out log events, recognize stored procedure and …

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. I’ve been trying to learn more about MySQL locks and deadlocks, and have written several articles on the topic recently, so I’m keen to hear your feedback.