This is an idea I came across at the MySQL UC last week: How many applications handle failing COMMITs correctly ? And how can we test it ?

COMMITs can fail. The most simple case are deadlocks. The sad side of deadlocks is that they only happen under real load when you application creates concurrency against the same rows. That's usually hard to create in test-setups.

With MySQL Proxy you can create deadlocks easily. Well, more or less.

You can at least fake them nicely and let the application and server think that we have a deadlock. The trick is:

To make it a bit more interesting you don't have to let all the COMMITs fail. Just let 50% of them fail.

--[[

Let a random number of transaction rollback

As part of the QA we have to verify that applications handle
rollbacks nicely. To simulate a deadlock we turn client-side COMMITs
into server-side ROLLBACKs and return a ERROR packet to the client
telling it a …

If you use a transactional storage engine in MySQL like InnoDB some of your transaction may be terminated by the storage engine because of deadlocks. Sadly it is a bit tricky to see what has led to the deadlock. SHOW ENGINE INNODB STATUS gives only a very minimal look into the state when it happened.

With the proxy and a little script we can track what happened in all open transactions before the deadlock happened. A classic example is the cross-over deadlock:

[36] received a ERR(1213, Deadlock found when trying to get lock; try restarting transaction), dumping all active transactions
  [35].1: select * from commit where id = 1 for update
  [35].2: select * from commit where id = 2 for update
  [36].1: select * from commit where id = 2 for update
  [36].2: select * from commit where id = 1 for update

35 and 36 are the two transactions we have open, the last statement in 36 triggered the deadlock.

In the script we stored …

Keith Murphy wrote about the open/closed source debacle and the first comment on that post was:

Monty makes all this money from the Sun acquisition, and pretends to be a free software advocate. How much did he make? How much is he giving back to the MySQL community?

Now, Keith rightfully met this with “grow up”. However, I want to point out that many people in the MySQL employee pool benefited from the sale, not just Monty. I also want to point out that Monty devoted years of his life to developing MySQL long before it was ever profitable.

According to Sun’s press release, “Sun will pay approximately $800 million in cash in exchange for all MySQL stock and …

So, I mentioned before that I found out about index_merge at the MySQL Conference. I was wondering why I had not heard more about it since it came out in 5.0.3. When talking with some MySQL people about it, I received mixed results. So, I decided to kind of run my own tests on some data and see what I could figure out.

I apologize for Wordpress' bad output. =(

The Data

I created a table with 5 million rows. Early tests with MySQL's Harrison Fisk (HarrisonF) over my shoulder with small data sets showed MySQL would optimize out the indexes in favor of table scans. I wanted to avoid that. This is my table schema:


CREATE TABLE `test2` (
`id1` int(10) unsigned NOT NULL default '0',
`id2` int(10) unsigned NOT NULL default '0',
`id3` int(10) unsigned …

Wow... I had a great time at the annual MySQL Users Conference in Santa Clara.Highlights:Received my CMDBA designation. Woohoo!Sun announced the possibility of an open source mobile phone;All day mysql proxy tutorial with Jan Kneschke and Giuseppe Maxia;Amazon's Power of Infrastructure as a Service - great premise; glitches remain;Great food and a nifty side trip to Santa Cruz;MySQL Workbench -

There was a lot of confusion at the recent MySQL Users Conference about the Associate exam. One recovering Oracle DBA took the test after a little persuasion (and two months of MySQL use) and was surprised how much they knew. The Associate or CMA is designed for those new to MySQL and have some hands-on experience.

The best way to prepare for the Certified MySQL Associate exams is to take part in the MySQL for Beginners training course offered by MySQL AB. It is also possible to prepare for the certified MySQL Associate exam through self-study. Candidates that choose to prepare themselves through self-study should consider purchasing the MySQL 5.0 Certification Study Guide.

The material on the exam is an overview of the basics. 25% of the exam is on basic data manipulation using SQL -- adding, updating and deleting data. Another 25% is …

Apache has a neat module called mod_dbd that allows your Apache modules to connect to a database. mod_dbd interfaces with apr_dbd, an Apache Portable Runtime (APR) abstraction layer around database specific drivers.

Back when Ubuntu 7.04 (fiesty) was released, a MySQL driver was not bundled with Apache for licensing concerns. So, in order to use mod_dbd to connect to a MySQL database, you need to get the MySQL driver source code from WebThing (apr_dbd_mysql.c) and manually re-compile apr-utils.

You also need the source code for Apache 2.2.3 (which includes apr-utils 1.2.7) from the Ubuntu 7.04 repositories, then copy the apr_dbd_mysql.c file into the Apache source …

The best storage engine for MySQL is by far InnoDB (and yes some applications can live with MyISAM only, but that's not the point). InnoDB (now part of Oracle) is also dual-licensed and has had an agreement with MySQL for several years now.

On the surface everything is looking  smooth:

• MySQL distributes proprietary MySQL+InnoDB to those who wants to embed them in their proprietary applications
• The open source community can work with  GPL MySQL and GPL InnoDB.

In practice it doesn't work quite that way:

Firstly because InnoDB hot-backup feature for instance has never been open source released. So contrary to the …

So, I mentioned before that I found out about index_merge at the MySQL Conference. I was wondering why I had not heard more about it since it came out in 5.0.3. When talking with some MySQL people about it, I received mixed results. So, I decided to kind of run my own tests on some data and see what I could figure out.

I apologize for WordPress’ bad output. =(

The Data

I created a table with 5 million rows. Early tests with MySQL’s Harrison Fisk (HarrisonF) over my shoulder with small data sets showed MySQL would optimize out the indexes in favor of table scans. I wanted to avoid that. This is my table schema:


CREATE TABLE `test2` (
`id1` int(10) unsigned NOT NULL default ‘0′,
`id2` int(10) unsigned NOT NULL default ‘0′,
`id3` int(10) …

All of my sites run a piece of software called Everything. Mine is a fork from the original Everydevel Corp's release. It shares some common traits with Slash but it has a more rapid development cycle that I like.

One of the things it shares with Slash, is that the "site" is the database. AKA everything that makes a site, a site, is the database. This means that a backup of the site can be tossed into an everything container and be up and running in seconds (I use this with virtual machines so that I can move stuff around on a whim).

I hate backups. I don't want to take snapshots with LVM, since I know I just need the content, and typically with dumps I end up with a huge set of these files just sitting around.

I cannot search them. …