Depending on the amount of experience you have with databases in general it's likely that as an Oracle user you would use the Oracle sytle join syntax. From version 9i Oracle has supported the ANSI join syntax but many users and many examples of Oracle SQL on the web still use the Oracle syntax.

Essentially the Oracle syntax uses the where clause to specifiy the join condition simply by comparing a column in one table with the other.

SQL> select e.emp_id, d.description from emp e, dept d
  2  where e.dept_id = d.dept_id;


    EMP_ID DESCRIPTION
---------- ------------------------------
         1 HR
         2 HR
         3 IT
         4 ADMIN

The good news is that this also works in MySQL. MySQL fully supports ANSI join conditions but it also allows (in part) the joining of tables via the where clause.

mysql> select …

Patrick came over the house last week and for some reason around 6:00 o'clock on a Friday I got it in my head to write up connection caching for Federated.
Its pretty simple, handler items are created as before but when they need a resource, aka a connection to a database, they just grab a connection from the cache. The cache is a hash with a key of the connection string. This returns a linked list of connections which you can then walk to find a free connection. At the moment I have implemented a new variable and support for the minimum number of connections that should be kept. I also added a variable for max connections too, but it will involve writing some sort of new loop with a timer to implement. Though we could just toss an error if too many connections are tried... that might be the best way to handle the situation.

Not bad for two hours worth of work aye?

The cool thing is that this is the first step to …

Ok.  Finally took the fifteen minutes required to upgrade to dasBlog 1.8.  No problems.  It sure is a joy to use a well-written piece of software.

We released 1.6.1 last friday with these changes:

- Fixed the installation procedure to add the INDEX privilege to the MySQL user (João)
- Fixed bug with handling HTML characters in Internal FAQ entries (Bryan)
- Fixed bug displaying priority in current filters (Bryan)
- Added feature to set X-Eventum-Type header in new assignment email (Bryan)
- Fixed bug that allowed users to access attachments, custom fields, phone calls and time tracking from issues they did not have access too (Bryan)
- Added new workflow method to check if an address should be emailed (Bryan)
- Fixed the issue searching routine to properly handle disabled fulltext search and customer integration features (João)
- Improved the IRC Bot script to be easier to configure (João)
- Added feature to update issue assignment, status and release for multiple issues at the same (Bryan)
- Fixed labels on …

The OSDC CFP site is the most annoying CFP in the entire world. So bloody complicated to go through - really makes you examine if you really wanted to do a talk in the first place.

I have been exposed to a couple of really great database books recently, both of which I highly recommend.

The first, Richard T. Watson’s Data Management - Databases and Organizations is a great book for anyone that needs to learn how to manage data. If you’re going to manage large amounts of data and are fairly new to the whole idea of databases, then this is for you. If you’re a DBA who feels that your boss has no idea what is going on in the DBA corner of your organizations, this may be the christmas gift for him or her. More about that here

The other, Jon Stephens’ and Chad Russell’s MySQL Database Design and Optimization is (as the title says) specifically aimed at MySQL users. Not a complete starters guide, you’re probably better off finding something else if you’ve never touched MySQL befoe. But if you have a few months …

Have you used your tag browser yet?

http://cjcollier.livejournal.com/tag/

It's neat. But there are a couple of things I'd like to see put into it. I'd like to see a redirect to /tag in the case of a single tag argument, and I'd like to see multiple tag arguments handled.

Whee. Should only be a modification to a WHERE statement in conjunction with splitting the cgi query string up in a manageable way.

So. I talked to Junior, and he pointed me at the right places to twiddle in order to get the change into the codebase.



Probably the best plan of action:

cgi-bin/ljfeed.pl
Pass "tagids" arrayref of tag ids to LJ::get_recent_items call

cgi-bin/Apache/LiveJournal.pm
Whatever code path does the call to generate feeds, make it respect a "?tag=foo" GET …

MySQL's User-Defined Functions (UDFs) allow you to link code libraries into the database server, and I was excited to learn this week that Jan Kneschke has published a UDF which uses Sean Chittenden's libmemcache library to connect to memcached. My next post will be all about using the memcache UDFs along with triggers in MySQL 5. First, though, I wanted to clarify a little bit about how to compile UDFs on Mac OS X.

OS X is based on the Mach executable format, and Apple's compiler is a slightly different version of gcc than anyone else's. The MySQL manual tells you to use gcc -shared to compile a UDF, like this:

gcc -shared -o udf_example.so udf_example.cc

However, Apple's gcc doesn't support the …

At OSCON, Don showed me a demonstration of smugMaps, a new service that he has created to "combine the power of Google Maps with over 32,000,000 smugmug photos." What makes Don's implementation really work is that he has made it easy for those of us without geo data in our photos to add it using the Google Maps interface.

Here are some of the highlights, in Don's own words:

• All smugmug RSS/Atom feeds support geo data, so other apps/services/etc can use this anyway they'd like.
• Google Earth KML is a supported format for all feeds, too, so we can toss photos across the surface of the earth from anywhere on smugmug. Very cool.
• Search for photos by address & various different types of …

About a year ago, I worked for a few months prototyping a MySQL Cluster system to try to replace an Oracle RAQ database containing ~90 million user account records and handling ~4,000 transactions per second. Even though the cluster's speed and performance actually exceeded what our Oracle servers were doing, I could not come up with a way to fit 90 mil records into main memory in a cost-efficient way.

Here's a diagram of the cluster towards the end of my time testing it. (Edit: Original picture stated that RRDNS was used between the application and cluster layers; a load balancer would be more appropriate.)

Every computer in my test cluster was a single processor Intel Xeon 3.4Ghz computer with 2G RAM, running Mandrake 10.1, and MySQL 4.1.7 custom compiled. I used the super-smack utility, running on the API nodes, to …