I spent the day at the Drizzle Developer Day at Sun's insane asylum campus. I'm not joking here, the campus was apparently a former insane asylum. First off I battled getting Drizzle to compile on Ubuntu 8.10, where the secret sauce appears to be to know about the drizzle-developer PPA. If you're using Ubuntu 8.10, add this to your sources.list and life will be a bit better:

deb http://ppa.launchpad.net/drizzle-developers/ppa/ubuntu intrepid main
deb-src http://ppa.launchpad.net/drizzle-developers/ppa/ubuntu intrepid main

After that compiling drizzle was pretty easy.

Tags for this post: mysql drizzle
Related posts: …

In an effort to speed up my database updates, I've been looking for ways to batch some of my updates. CASE seems like the way to go:

mysql> create table bar(a tinyint, b tinyint);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into bar(a) values(1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from bar;
+------+------+
| a    | b    |
+------+------+
|    1 | NULL | 
|    2 | NULL | 
|    3 | NULL | 
|    4 | NULL | 
|    5 | NULL | 
+------+------+
5 rows in set (0.00 sec)

mysql> update bar set b = case a
    ->   when 1 then 42
    ->   when 2 then 43
    ->   when 3 then 44
    ->   else 45
    ->   end;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from bar;
+------+------+
| a    | b    |
+------+------+
|    1 |   42 | 
|    2 |   43 | 
|    3 |   44 | 
|    4 |   45 | 
|    5 | …

I've been doing a lot of batch updates on one of my databases at home recently. show processlist says something like this:

mysql> show processlist;
+-------+------+---------------+--------------+---------+-------+----------+------------------------------------------+
| Id    | User | Host          | db           | Command | Time  | State    | Info                                     |
+-------+------+---------------+--------------+---------+-------+----------+------------------------------------------|
| 18354 | root | maui:37403    | smtp_servers | Query   | 57234 | Updating | update ips_218 set reverse_lookup = null |
| 22286 | root | maui:37348    | smtp_servers | Query   | 38103 | Updating | update ips_80 set reverse_lookup = null, |
| 22851 | root | maui:54982    | smtp_servers | Query   | 34091 | Updating | update ips_19 set reverse_lookup = null, | 
| 23351 | root | molokai:58232 | smtp_servers | Sleep   |    57 | …

Apart from sniping the queries, is there any other way to reduce the amount of time that a query will wait before giving up on getting access to a locked table?

Tags for this post: mysql query lock timeout
Related posts: MySQL scaling: query snipers; Estimating the progress of queries on MySQL; Managing MySQL the Slack Way: How Google Deploys New MySQL Servers; …

Hi. I am doing a select like this in MySQL 5:

select * from foo where bar rlike '(.*),(.*)';

The specific example here is made up. Anyway, I'd like to be able to get to the matched text from bar, like I can with various languages regexp libraries. Is this functionality exposed at all in MySQL? I've looked at the docs and can't see any indication that it is, so this might just be wishful thinking.

Tags for this post: mysql regexp rlike select
Related posts: Managing MySQL …

I've been wanting a query sniper for a while for MySQL (some people seem to call them "query killers"). They're basically programs which inspect the MySQL "show processlist" command and decide to kill some processes if they violate specified rules. I have a prototype of one in python, and couldn't find any other implementations until tonight when I came across Querybane, which is what Wikipedia use to do exactly the same thing.

Now, I need to think further about if querybane does what I want, especially given I can't find the source code. Perhaps it's hidden in the media wiki code somewhere? The only code for a project named "servmon" (it's parent project) that I can find is …

I was walking down Mountain View's Castro Street this afternoon, and noticed that meebo is advertising for developers and system admins. Interestingly, they seem to match the design pattern used by pretty much every web 2.0 company I have seen around here (except MySpace): linux, MySQL, and Ajax. So, there you go.

Tags for this post: mysql web 2.0
Related posts: Managing MySQL the Slack Way: How Google Deploys New MySQL Servers; I won a radio shark and headphones!; …

Kynan and I came along to the MySQL camp, and thru a quirk of fate pretty much ended up running it (the person who was meant to be running it got injured on the first day and had to go off to hospital). In return we wrote the Google Code blog post about the event. Pretty cool, huh?

Tags for this post: mysql user camp google code blog
Related posts: …

Greg Linden (Google watcher, search engine dude, ex-Amazon) saw Stewart Smith's talk on Google Video about MySQL cluster, and likes the idea. That's cool.

Time for more content on Google Video?

Tags for this post: mysql google video cluster
Related posts: MySQL cluster stores in RAM!; MySQL cluster stores in RAM!; …

Three intrepid MySQLers came to Google after the user conference to give internal tech talks. They were kind enough to agree to us hosting them for other people to see. The first two are up, so I'll mention those now, and put a link to the last one when it's available...

Click on the thumbnail to be taken to the video.