Log Buffer #96: A Carnival of the Vanities for DBAs

This is the 96^th edition of the weekly review of database blogs, Log Buffer.

Let’s start this one in SQL Server Land, with a question from Dennis Gobo — should SQL Server have the CREATE [OR REPLACE] PROCEDURE syntax? There are, he writes, advantages: “When scripting out a database you don’t have to generate if exists…..drop statements,” and disadvantages: “I can overwrite a proc without even knowing it.” Of course, the commenters have opinions of their own, and the piece becomes a straw poll for the desirability of that syntax as a feature.

Aaron Bertrand has one too: when was my database/table last accessed? Writes Aaron, “SQL Server does not track this information for you. SELECT triggers still do not exist. Third party tools are expensive and can incur unexpected overhead. And people continue to be reluctant or unable to constrain table access via stored procedures, which could otherwise perform simple logging.” He looks at 2008’s built-in auditing, and for those who can’t wait for that, illustrates a workaround for 2005.

Linchi Shea explores something else from 2008, Page Compression, focusing on how the number of processors affects the rebuilding a table with page compression.

Jamie Thomson, the SSIS Junkie writes that he has made a submission to Connect on the matter of absolute and relative paths in SSIS. “. . . I have always agreed that stipulating the use of absolute paths within SSIS was the right thing to do (and indeed I have championed it) however of late I have changed my mind. Support for relative paths would greatly simplify package deployment and package management . . . What do you think? Should SSIS support relative paths?” So far, it looks like a shoo-in.

Brian Knight also explains another little quirk, SSIS Case Sensitivity: “The case sensitivity can in some cases create behavior that is not expected and may give you bad results if you’re not careful.  . . . One such example is with the Lookup Transform, where comparisons against the cache are case sensitive. If you do not expect this, you may have a miss in a match that is actually a hit.”

In the MySQL ‘sphere this week, there is plenty of talk about the openness or otherwise of MySQL. Sun/MySQL’s Kaj Arno announces that MySQL Server is Open Source, even backup extensions. This looks like a reversal of MySQL’s at the recent conference of their closing some parts of upcoming releases. The reason? “The change comes from MySQL now being part of Sun Microsystems. Our initial plans were made for a company considering an IPO, but made less sense in the context of Sun . . .”

Matt Asay of c|net blogs’ The Open Road responds with, shall we say, a query: Will the real MySQL please stand up?. Writes Matt, “Kaj’s clarification clouded things more than it cleared them, such that wild throngs crowded the streets to celebrate their apparent success in browbeating MySQL into giving them all of its software for free, forever.  . . . Put simply, Kaj indicated that one announced closed extension would now be open source, but said nothing about other potential closed extensions.” So perhaps it isn’t a reversal? Or maybe there was no position to reverse in the first place, and this is simply the correction of a misstep?

Matthew Aslett of 451 CAOS Theory covers a little more of this in his MySQL licensing redux, and also mentions the problem of community involvement in MySQL development, or the lack of it.

What about those backup extensions? In the wake of Kaj’s news, Giuseppe Maxia, the Data Charmer, test drives the free and open online backup. He begins, “Politics aside, what is this online backup of which everyone has complained but few seem to have really tried? In short, it’s a feature that allows you to make a copy of the data from one or more database to a file.” Read through for his full investigation.

On the matter of community involvement, Robin Schumacher asks for your input on stored procedure enhancements: “. . . SIGNAL and RESIGNAL – which will help greatly in the area of error handling and management.” Jay Pipes asks if anyone is interested in a community-driven worklog.

On Ramblings of a web guy, Brian Moon suggests a MySQL Forge section for example my.cnf files.

Peter Zaitsev of the MySQL Performance Blog offers some hacks for quickly preloading Innodb tables in the buffer pool, slipping it in as a feature request as he goes.

Venu Anuganti links to his thorough article exploring new features in the InnoDB Plugin-1.0, which debuted at the recent MySQL User Conference & Expo.

And speaking of segues, here are a couple photos of a dolphin and an elephant expressing their love for each other under the Sun. Sorta brings a tear to the eye, doesn’t it?

Actually, there was no shortage of MySQL-to-PostgreSQL transitional material anyway. I could have used this item from the Postgres OnLine Journal looking into what can PostgreSQL learn from MySQL. “A lot of staunch advocates of PostgreSQL wonder what exactly is it that MySQLers see in that beast of a database . . . Why do people choose MySQL time and time again over PostgreSQL and why is PostgreSQL sometimes a hard sell?” Good question! Some good answers, too, and a lot of interesting comments.

Had that item not existed, I could have used this item on Hubert Lubaciewski’s select * from depesz showing how to simulate MySQL’s timestamp in PostgreSQL. Hubert begins, “MySQL has this nifty/annoying feature/bug of special data type ‘TIMESTAMP’. It is like a DATETIME, but it gets automatically updated whenever you modify the row.  . . . I’ll try to add the same feature to PostgreSQL.”

As if there weren’t already an embarrassment of segue-worthy blogs, this week, on An Expert’s Guide to Oracle Technology, Lewis Cunningham offers a tutorial on DBMS_PIPE & DBMS_ALERT In EnterpriseDB. What’s this all about? Lewis writes, “The latest release of the EnterpriseDB database, Postgres Plus Advanced Server (AS) 8.3, includes additional Oracle compatibility features. Two of those new features are DBMS_PIPE and DBMS_ALERT. Old Oracle hands will recognize those two packages that are used for interprocess communication.”

Elsewhere in the Oracle world, Radio Free Tooting’s Andrew Clarke reveals things he wished SQL supported, writing, “. . . it is always interesting to see what shortcuts people would like to take. It’s a bit like peeking inside the medicine cabinet in other people’s bathrooms (not that I would ever do that).”

Have you ever done testing a no-statistics environment? David Aldridge, the Oracle Sponge, writes: “I am absolutely sick and tired of dealing with issues where misleading partition statistics (for example) cause ETL queries to go crazy and try to sort-merge tables with tens of millions of rows each.  . . .  Since we’re about to run a test load on production data I’m going to drop all table and index statistics and lock them. OK, actually I’m going to back the statistics up first, and then I’m going to drop and lock them.”

On his Oracle Scratchpad, Jonathan Lewis compiles his rules for hinting.

Don Seiler must have read them, since he has on offer a brief but helpful hint for RMAN recovery on die Seilerwerks.

Jason Arneil has an worthwhile look into ASM and disk “hot spots”. “I’ve seen repeated in various locations that ASM somehow has the ability to move data around in response to how much I/O is ocurring on each of the disks . . . Sounds great? Trouble is, it just is not true. This idea has gained a bit of traction in the community, and I’m sure many people think ASM is perhaps more clever than it actually is.” Jason gives his succinct explanation of what ASM actually does.

Here on the Pythian Blog, Augusto Bott published the latest in his popular series about Oracle on Ubuntu Linux: Installing Oracle 11g on Ubuntu 8.04 LTS (Hardy Heron).

Firebird News likewise linked an article on guide for installing the Firebird 2.1 DBMS on Hardy Heron.

Last, Oracle ACE Director Dan Norris shows what it takes if you want to be an Oracle ACE, using a MySQL community leader, Sheeri, as his model. “I think it’s a good time to note that community involvement is becoming a bigger factor in the job market. Those that are engaged in the community are more likely to get my attention and those that lead parts of the community receive and deserve a special place near the front of the line in my book.  . . . Right or wrong, those involved with the community have typically been more resourceful, harder working, and easier to work with in my experiences.”

Editing and publishing an edition of Log Buffer on your own blog is an excellent way to make yourself better known in your DBA community. If you think you’d like to, email me, the Log Buffer coordinator, and we’ll get started.

That’s all! See you in a week’s time. Breaking news: Brian “Krow” Aker is doing next Friday’s Log Buffer #97.