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.

mySQL DBA

Looks to be a really nice blog on using MySQL in real world applications.

One of very interesting techniques Innodb uses is technique called "doublewrite" It means Innodb will write data twice when it performs table space writes - writes to log files are done only once.

So why doublewrite is needed ? It is needed to archive data safety in case of partial page writes. Innodb does not log full pages to the log files, but uses what is called "physiological" logging which means log records contain page number for the operation as well as operation data (ie update the row) and log sequence information. Such logging structure is geat as it require less data to be written to the log, however it requires pages to be internally consistent. It does not matter which page version it is - it could be "current" version in which case Innodb will skip page upate operation or "former" in which case Innodb will perform update. If page is inconsistent recovery can't proceed.

Now …

The first beta version of XAMPP for Linux is available for public download in our XAMPP BETA area. New in this beta version are: Apache 2.2.3, MySQL 5.0.22, PHP 4.4.3 and phpMyAdmin 2.8.2.

Beta releases are only for testing purposes. We would very much appreciate it if you could test this release and let us know if you run into any problems.

Follow me to the XAMPP BETA area

I'm working on an article with Peter G. about MySQL partitioning that should be on dev.mysql.com next week sometime (assuming that I pull my finger out and get my bits finished soon). I started working on an example that partitions based on years and quarters and very quickly realised that if you use QUARTER() as part of of the partitioning expression, then that table is going to have all the high-speed performance of the average garden slug.I won't give away everything here (otherwise you'd have no reason to go read the article), but I will say that using TO_DAYS() instead of QUARTER() is part of the solution, and that I came up with a utility of sorts to help in creating such tables. Here's the stored procedure: DELIMITER |CREATE PROCEDURE qdays ( IN y1 INT, IN q1 INT, IN y2 INT, IN q2 INT ) BEGIN DECLARE y INT DEFAULT 2005; DECLARE q INT DEFAULT 0; DECLARE mn INT DEFAULT 1; DECLARE ms CHAR(2) DEFAULT ''; DECLARE s VARCHAR(50) DEFAULT ''; DROP …