From Stack Overflow:

I have a logfile which logs the insert/delete/updates from all kinds of tables.

I would like to get an overview of for example the last 20 people which records where updated, ordered by the last update (datetime DESC)

A common solution for such a task would be writing an aggregate query with ORDER BY and LIMIT:

SELECT  person, MAX(ts) AS last_update
FROM    logfile
GROUP BY
person
ORDER BY
last_update DESC
LIMIT 20

What's bad in this solution? Performance, as usual.

Since last_update is an aggregate, it cannot be indexed. And ORDER BY on unindexed fields results in our good old friend, filesort.

Note that even in this case the indexes can …

Pentaho’s Spoon is an open source ETL or Extract, Transform and Load tool that makes loading data from various formats into a MySQL server easy. Spoon also lets you check the data for problems and correct them before it gets inserted. It does a lot of things very well to make life easier for a DBA. So if people are entering their state of residence as ‘CA’, ‘Cal’, ‘Cal.’ and ‘California’ , Spoon can clean up the data to what you need.

What follows is an example of transforming a CSV file into a series on INSERT statements. It is a very simple transformation but is a good initial exposure to how Spoon functions. It then covers how to use Spoon with a bulk loading program.

There have been questions on the Calpont InfiniDB forums (http://www.infinifb.org/forums) on using Spoon to drive the cpimport bulk loader program. LOAD DATA INFILE can be slow (10,000 …

The future of open source licensing. OpenSolaris governing board quits. And more.

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca
“Tracking the open source news wires, so you don’t have to.”

# Glyn Moody asked which open source software licensing is best for the future?

# The OpenSolaris Governing Board has collectively and expectedly resigned.

# OpenBravo has updated its rapid implementation ERP offering for small and mid-sized businesses, Openbravo QuickStart. …

We are pleased to announce the release of SQLyog – MySQL GUI 8.6 GA. This release adds functionalities requested by users for some time, and fixes a number of bugs. Most important enhancements:

User management has been updated with a brand-new interface, has been completely rewritten and also now fully communicates with the MySQL server the recommended way using GRANT and REVOKE syntax. We believe that with this release we have provided the best available ever GUI for MySQL User Management.

For users that prefer to work in a spreadsheet-like interface when filtering and sorting data the options to do this have been enhanced: There is now a ‘custom filter’ option to be used when filtering on a value that does not exist in the result set displayed – or even is not stored in the table at all. Additionally you may now define the substring to be filtered on in more flexible …

As I mentioned in a past article I got my IPv6 connectivity working so started working on setting up various IPv6 services. One of these was to setup my name server so it also worked on IPv6. This worked fine, but recently I lost my IPv6 connectivity but thought no more about it. I’m trying [...]

I am very happy to announce that I have secured a great rate at a hotel for OpenSQLCamp (a free weekend conference for open source databases such as MySQL, Postgres, SQLite, and NoSQL databases). We have a room block at the Doubletree Club Hotel Boston Bayside at 24 Mt. Vernon Street in Boston at a rate of $149 per night, for single or double occupancy*, for both Friday night, October 15 and Saturday night, October 16. Wireless internet access, which is usually $9.95 per night, is included in the room fee, so there's no hidden extra there. There is also a free shuttle from Boston's Logan Airport to the hotel**. The subway is steps away from both the hotel and the venue (MIT Stata Center on the corner of Main and Vassar Streets in Cambridge). Subway fare is $2.00 each way, so if you stay at the hotel you'd have $8 extra in transportation fee.

You can reserve your room by …

Neither is ext3. Nor ext4. Nor btrfs. And thus, none of these will work on dual-Primary DRBD. Nor active-active shared storage. Nor any synchronously replicated active-active SAN. And we’re telling you very clearly.

So if you choose to ignore all warnings and put ReiserFS on dual-Primary DRBD, and mount it from two nodes, you’ve just signed up for wrecking your data. And when that happens, don’t come whining. And don’t blame DRBD or any other of the technologies you may be choosing to employ while ignoring the documentation.

…

EMT is a monitoring tool that I’ve been developing over the past few years. It’s goal is to serve as a hub for performance metrics on a single server. I’ve tried to talk about what EMT is before but I’m not a very good writer so I thought it would be best to just show people. This tutorial is going to be a quick overview of installing EMT from the rpm and a basic tutorial of it’s usage. Some of this is covered in the manual and some has changed in newer released.

Installation
The easiest way to install EMT is to grab the latest rpm from the Google Code downloads page. After installing the rpm you will see a notice about correcting some details in the default view.

Bug reports can be fun. They can also be terrible. Either way they can be entertaining. On the Drizzle IRC channel today I saw a couple references to MySQL bug reports: it is stop working and Does not make toast (which reminds me of the Mozilla bug report about the kitchen sink). Got any other favourites1?

1 This one’s for Jay.

Related posts:

1. What are your favorite MySQL replication filtering rules?
2. My new favorite comic: The Adventures of …

Inspired by some recent posts about scripts for parsing mysqldump files, I thought I’d put my own little dump program out there for those looking for alternatives to mysqldump. It’s written in perl and only actually uses the mysqldump utility to dump the data portion of its file dumps. The rest is done via mysql’s internal show commands. Each database is dumped to it’s own directory, where each component in that database is dumped into five subdirs: schemas, routines, views, triggers & data. Inside these subdirs is a file per table and file per proc, etc. The data section is slightly different: as I mentioned it uses mysqldump to dump bulk insert statements into a sql file, one per table. So at any time, any or all components (including data) can be reloaded with a simple redirect or pipe back to mysql client: …