I wrote an article a while back about how to order updates in MySQL so you don't violate a unique index. I said I'd write another article on how to swap numbers in a sequence with a unique index. This is that article, but I'm going to make it a little more generic: how to reverse a (possibly ordered) sequence.

I've been thinking about this for a while, wondering if there's a way I can do it in-place in one statement (I like to pile challenge upon difficulty). I've thought of a number of techniques, some using one statement, some using more, some that won't work on MySQL, some that will.

A couple of days ago, I was thinking about what we need to implement inside MySQL to support easier replication fail-over (of an old master to a new master).

I came up with some preliminary ideas that I scetch below. If you have any suggestions on how to solve these problems in a smarter way, please write a comment to this blog entry.

PROBLEM 1: The fail-over infinite looping problem

The scenario is that four servers A, B, C, and D (with server ids 1, 2, 3, and 4 respectively) replicate in a topology { A->B, B->C, C->D, D->A }. Server A crashes. The DBA wants slave B to connect to master D instead and continue with the circular replication.

The problem is that events generated on server A that has been replicated to B but not to C and D will replicate in the circle forever.
The normal loop detection that MySQL has discards events that are generated on the same server, but …

This week LinuxWorld Expo will convene in San Francisco.  MySQL has a booth there where we'll be showing off the latest new technology around the forthcoming MySQL 5.1 and MySQL Network.  We also have a reception Wednesday evening next to Moscone conference center at Jillian's.  It's a chance to meet MySQL developers, execs, customers, partners in a nice social context with beer, pizza and pool.  Hey, what more could you ask for?  But space is limited, so be sure to RSVP.

• LinuxWorld Expo: Overview, …

For those who have been emailing me about the engine I displayed in my blog this weekend:
http://tangent.org/index.pl?node_id=503

Directly to the source:
http://download.tangent.org/table_functions_engine-0.1.tar.gz

This engine allows you to plug C style functions into the storage engine interface with minimal hassle. This is incredibly alpha, and I have found several bugs in 5.1 release related to the loadable engine interface in the process. You must be using the very latest MySQL source tree from http://mysql.bkbits.com/ if you hope to make this work.

BTW I don't expect many people to make this work at this point. You must read the README.

The interface …

Having gotten my daughter a new laptop to take off to college where she starts as a freshman in a couple of weeks, I now have her old laptop, still quite serviceable albeit older, larger, heavier, etc. So I set about setting it up as a system where I can do BK pulls of various MySQL versions, be continuously running and testing new pulls from the 5.1 and 5.2 trees.

The first thing I did was to take the laptop, a Toshiba Satellite with a 30GB hard drive, Celeron processor and 256MB RAM, reformat it and install Slackware 10.2. This is my preferred operating system except for a couple of systems that are rather low on my priority to convert, where it's vital to keep them running as Windows because of a number of applications. I set up the following partitions:

hda1  6.6GB /
hda2  6.6GB /usr
hda3  512MB (swap)
hda4 13.3GB /usr/local

The swap partititon is my standard formula, double the size of the RAM, …

I’m headed to Thailand in September (along with my friend and MySQL colleague Morgan) for an extended working holiday. It’ll be my third time there, and each time I visit, I fall more in love with the place. The culture is fascinating, the food is marvelous, and the people are some of the nicest I’ve met anywhere.

Since we’re planning to be there for a while, we figure that we can take a little time out from work and play to do some Open Source evangelising and networking. We’ve already contacted Open Source Thailand about the possibility of participating in any events they’ve got planned for Software Freedom Day. (Actually, Morgan got the ball rolling on that.)

However, we’re not limited to that particular event. Either one of …

The answer to the question posed in the title used to be, “Linux, Solaris, and Mac OS X - anywhere else, and you’re on your own, mate. And forget about running it on Windows.”

The answer is now more like, “If it’s one of the Unices, chances are that we support running Cluster on it. Windows still need not apply.”

In fact, it might be easier now just to list the platforms we definitely don’t support for using Cluster, which as far as I know are these:

• Minix
• Plan 9
• BeOS
• Windows

(To see what’s officially supported by MySQL for Cluster - and to what extent - check out this page that’s maintained by our illustrious Support Department: Supported Platforms for MySQL Cluster.)

However, if you can get a MySQL client running on one …

The MySQL open source database has become the database-of-choice for the new generation of highly-popular Internet companies pioneering new "Web 2.0" innovation. Leading Web 2.0 sites such as YouTube, Flickr, Habbo Hotel, Linden Labs, CyWorld, Technorati, Facebook, FeedBurner, Feedster, Wikipedia, Digg, LiveJournal, Mixi.jp, SimpleStar, PhotoBucket, 37signals, del.icio.us, Trulia, Neopets, and Zimbra have all selected MySQL to power their explosive growth - due to the database's speed and ability to easily "scale-out" on low-cost hardware.

I wrote an article a while back about how to order updates in MySQL so you don’t violate a unique index. I said I’d write another article on how to swap numbers in a sequence with a unique index. This is that article, but I’m going to make it a little more generic: how to reverse a (possibly ordered) sequence. I’ve been thinking about this for a while, wondering if there’s a way I can do it in-place in one statement (I like to pile challenge upon difficulty).

Few days ago I wrote an article about using UNION to implement loose index scan.

First I should mention double IN also works same way so you do not have to use the union. So changing query to:

PLAIN TEXT SQL:

1. mysql> SELECT sql_no_cache name FROM people WHERE age IN(18,19,20) AND zip IN (12345,12346, 12347);
2. +----------------------------------+
3. | name                             |
4. +----------------------------------+
5. | ed4481336eb9adca222fd404fa15658e |
6. | 888ba838661aff00bbbce114a2a22423 |
7. +----------------------------------+
8. 2 rows IN SET (0.00 sec)
9.  
10. mysql> EXPLAIN SELECT …