The latest from the MySQL community

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 …

New features of InnoDB - compression format and fast index creation sound so promising so I spent some time to research time and sizes on data we have on our production. The schema of one of shards is

PLAIN TEXT SQL:

1. CREATE TABLE `article87` (
2.   `id` bigint(20) UNSIGNED NOT NULL,
3.   `ext_key` varchar(32) NOT NULL,
4.   `site_id` int(10) UNSIGNED NOT NULL,
5.   `forum_id` int(10) UNSIGNED NOT NULL,
6.   `thread_id` varchar(255) CHARACTER SET latin1 NOT NULL,
7.   `published` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
8.   `crawled` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
9.   `subject` varchar(255) NOT NULL,
10.   `title` varchar(255) NOT NULL,
11.   `url` varchar(255) NOT NULL,
12.   `num_links` smallint(6) NOT NULL,
…

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 …

In a unique display of mass hysteria, one blogger after the other and even slashdot (no, I’m not going to link) managed to take the completely innocent message that certain new enterprise features might get released as closed source only and turn it into an ongoing bad press onslaught about “MySQL closing down source code”.

Why don’t you all give MySQL a break here please?  The rule is always the same for everybody: the one that writes the code gets to pick the license.  Listen, I 100% believe in open source and I consider myself to be a big advocate, but commercial open source companies like MySQL (and Pentaho) are commercial entities.  At lease try to put yourself in their position for a second.  For example, if a customer asks you to NOT to release a piece of software they paid for, you don’t release it, it’s that simple.

In the end, what MySQL is doing is simple: they are experimenting with a …

TOTD #30 explained how to create CRUD application using Grails and hosted using in-built Jetty servlet engine and in-memory HSQLDB database. Jetty and HSQLDB are built into Grails and allows to start easily. You can also use GlassFish and MySQL for deploying your applications in production environment.

This blog entry walks you through the steps of deploying a Grails application on GlassFish and MySQL.

1. If MySQL is already installed, then download …

This past week was the annual MySQL Users’ Conference. It was my fourth time attending the conference, my second as a MySQL-er, and my first as a Sun employee.

The conference was very well attended with many more people than last year. Overall, I’d say it was a great success.

I work on the Backup project for MySQL and I must say it was a very strange experience being in the spotlight of so much controversy concerning the announcement made about making some backup features enterprise only releases.

While there was a lot of blogging expressing the general distaste for the suggestion of making some features of backup enterprise only (as opposed to free), most of these comments seemed to come from a relatively small number of people.

I presented the Backup session and led the Birds of a Feather session on Backup. I must say that the sentiments of the bloggers was not represented in the audience of these sessions. I …

This past week was the annual MySQL Users’ Conference. It was my fourth time attending the conference, my second as a MySQL-er, and my first as a Sun employee.

The conference was very well attended with many more people than last year. Overall, I’d say it was a great success.

I work on the Backup project for MySQL and I must say it was a very strange experience being in the spotlight of so much controversy concerning the announcement made about making some backup features enterprise only releases.

While there was a lot of blogging expressing the general distaste for the suggestion of making some features of backup enterprise only (as opposed to free), most of these comments seemed to come from a relatively small number of people.

I presented the Backup session and led the Birds of a Feather session on Backup. I must say that the sentiments of the bloggers was not represented in the audience of these sessions. I …

This year, we got fourteen Google Summer of Code projects accepted. Colin Charles has informed the students, and things can now get started!

The first step is what’s called the Community Bonding Period. That’s happening right now, and also being facilitated by Colin. Colin has written a summary of the Community Bonding period on the Forge Wiki, and there’s also a general description by Google.

We expect great things from the students. We want them to produce code that our userbase can use as features in MySQL.

Given our high …

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

This quiz on INFORMATION_SCHEMA is stolen (with permission!), from Roland:

• Specify a minimal set of columns of the information_schema.TABLE_CONSTRAINTS table that is sufficient to reliably identify a single row in the information_schema.TABLE_CONSTRAINTS table.
• Argue why these columns are necessary and sufficient to identify a row, and why a smaller set of columns does not exist

(more…)

I was not surprised when Jeremy raised his voice about the online backup offering.

Internally, I had expressed similar concerns about the plans, and I agree with much of Jeremy reasoning. However, there is something that has been miscommunicated about the online backup. From Jeremy's article, it looks like the whole online backup will be reserved for paying customers. That is not the case. The online backup is GPL. The community can use it right now. (But careful! it's alpha code, added in version 6.04 and newer)
The online backup, last time I tested it, works mostly like mysqldump, with three main differences:

• it's faster than mysqldump;
• the dump is much smaller;
• the backup …