Greetings from Wisconsin! Welcome to the 106th edition of the Log Buffer. Mr. Edwards is on a brief holiday and kindly asked me to fill in for him. So join me as we take a tour of some of this week’s database blogging activity.

I’d like to start by sharing the story of MySQL engineer Andrii Nikitin’s young son, Ivan. The short story is that Ivan is in need of a bone marrow transplant and that would also require travelling outside of their native Ukraine for the procedure. The family is asking for donations to cover the cost of the operation and trip, so please consider donating via the previous link.

Now, moving on to the database topics, we begin with my own area of (relative) expertise, Oracle. The big news this week is the July 2008 CPU, or Critical Patch Update, which Oracle releases quarterly. The Integrity blog has a nice summary of what’s included in this latest patchset. I’m sure plenty of you will be heading to Oracle OpenWorld 2008 as well, and OTN’s Justin Kestelyn announced a community preview page with some sneak peeks of what to expect (spoiler: beer!).

In a quick round-up of Oracle 11g topics, Amit Bansal at AskDBA.org has a nice guide for installing Oracle 11g on Solaris 10. Meanwhile, Oracle performance guru (and fellow Wisconsin native) Greg Rahn makes a great case for using incremental global statistics on partitioned tables. I see that Tim Hall has taken a break from reviewing summer blockbusters to write up a nice article on AWR baseline enhancements in Oracle 11gR1, and Alex Nuijten at AMIS has a nice presentation on using Oracle 11g’s virtual columns to clean up your data model.
Read the rest of this entry . . .

The next issue of MySQL Magazine is now available for download. Get it while it is hot! At forty-two great pages it is our biggest and best issue yet.  This issue is anchored by the first annual MySQL Usage Survey results.

Downloads at the MySQL Magazine homepage: http://www.mysqlzine.net

Thanks to everyone who contributed.  I couldn’t have done it without you all!!

Building High-Performance Drivers for Oracle Database 11g: OCI Tips and Tricks (PDF). I had the opportunity to have an early look at this white paper by Luxi Chidamdaram.

The paper goes over what needs to be done to use OCI effectively, especially in a web environment. The step-by-step approach taken in this document is very readable and the well explained code snippets make it a very good reference — great for comparing your code to the “proper” way to do it.

The novice OCI programmer will find the initial sections of great benefit, as it takes a lot of mystery out of OCI programming. For the more advanced OCI-head, the document is full of good examples of how to manage connections, sessions, and pooling–with an examination of what type of pooling is needed in a given situation.

One highlight for me was the section on database events, which are covered very effectively with some practical examples.

In short, this white paper is a must-read for both the OCI master and the novice, with some great pointers on how to use what is already in OCI and what is new in 11.

The latest quarterly update came out this morning. There are oh-my-god smoking guns this time, but several medium-important patches:

CVE-2008-2607: Vulnerability in DBMS_AQELM (Advanced queueing package for e-mail and HTTP notifications)
CVE-2008-2613: Vulnerability in DBMS_SCHEDULER, requiring access to a local user in the oinstall group for exploitation
CVE-2007-1359: Remotely-exploitable vulnerability in Oracle App Server. This is an issue in the ModSecurity application firewall that was originally reported in March 2007 that allows some security checks to be bypassed given a specially-formatted string. The original advisory is here.
CVE-2008-2589: PL/SQL injection flaw in Oracle Portal. Details were posted to the full disclosure list in conjunction with the patch
CVE-2008-2594 and CVE-2008-2609: These look like two more injection flaws in Portal.

If you’re running Oracle Collaboration Suite, note that the patch blows away the login and logout pages (oops!). MetaLink note 445172.1 has info on how to restore the pages post-patch.

Is your database in archive log mode?

Is your database in flashback mode?

Have you defaulted your archive location to the flash recovery area?

Is your ORACLE_SID lowercase?

Do you use SQL BackTrack?

If your answer is yes to all these questions then beware!

There is a bug in SQL BackTrack (at least in version 6.8) that prevents your backing up of the archive logs from the flash recovery area. This will result in the inability to recover from any online backups that you have taken if you somehow lose those archive logs.

You do not receive any obvious errors when doing the backup but, if you are observant, you may spot the fact that the backup report does not show that it backed up any archive logs. To really check whether you are suffering from this problem then you need to check the $DTBASE/obacktrack/log/datatools.log. This is the log that gets written whenever any SQL BackTrack command is run. You will see an error such as

17:29:16.61 obacktrack[839826](2835/oramisc.cpp): Unable to open directory: '/home/oracle/data/test/test/archivelog'

You will know if you have been caught by this bug when you try to restore and you get messages that look something like this.

BMCBKO4802051E: The following Oracle error occurred during
BMCBKO4802051E: SQL-BackTrack processing : 'ORA-01195: online backup of file 1 needs more recovery to be consistent'
BMCBKO4802051E: The following Oracle error occurred during
BMCBKO4802051E: SQL-BackTrack processing : 'ORA-01110: data file 1: '/home/oracle/data/test/system01.dbf''
BMCBKO4805906E: Unable to perform recovery.

The problem arises when Oracle creates a directory in the flash recovery area that correspond to the database name. This directory is in uppercase. So, in my case, my ORACLE_SID is test and the init.ora parameter db_recovery_file_dest='/home/oracle/data/test'.

Oracle creates the directory
/home/oracle/data/test/TEST.

However when SQL BackTrack sees that the archive location is defaulted to the flash recovery area it tries to look in the directory
/home/oracle/data/test/test.

This fails … silently!

There are 2 workarounds to this issue that I have tested.

1. Create a symbolic(soft) link to point to the real directory using ln -s
2. Explicitly set the archive location using the init.ora parameter log_archive_dest_n

The workaround suggested by BMC (the supplier of SQL BackTrack) is to change the database name to uppercase. This will work too, but I haven’t tested it.

Oracle R12 is certified to install on 64-bit Linux for X86_64 platforms. Yeah, I know that’s old news. The interesting part here is it’s not “pure” 64-bit software like the Oracle Database on Linux 64-bit: R12 is taking advantage of the OS’s capability to run 32-bit executables.

If we look at metalink note 416305.1, there is a big list of 32-bit RPMs in the pre-req packages list for R12 on 64-bit Linux. This is not something new — 11i on Solaris, HP-UX, and AIX also had the same behaviour.

11i doesn’t take advantage of 64-bit capabilities of the OS at full scale. It runs in 32-bit mode even on a 64-bit OS, as all its executables are 32-bit. One place where 11i does use the 64-bit feature is the “Memory Based Planner” program, which is part of the Advanced Supply Chain Planning (ASCP) module. Oracle shipped a 64-bit version of the program only for the Solaris, AIX, and HP-UX platforms, while the Linux version had just the 32-bit version. I have seen customers who added a Solaris Middle Tier to their Linux 11i instances just to run this 64-bit Planner program, spending all the dollars they saved with Linux middle tiers thus far, in consulting fees to add that extra Solaris middle tier node.

The good news for R12 customers is that, we don’t need a Solaris, AIX or HP-UX middle tier any more to run the 64-bit Planner program. Oracle has started shipping 64-bit Planner (MSONWL64.exe) for Linux 64-bit from version 12.0.3 onwards. Check Metalink note 254810.1 for details.

If you are a early adopter of R12 with a version earlier than 12.0.3, its time to patch up to 12.0.3 or 12.0.4. and migrate to the Linux 64-bit platform. This migration to Linux 64-bit should not be a big hassle, as it is binary-compatible with Linux 32-bit. I expect it be as simple as 1) copy, 2) relink, and 3) startup. I will, however, find out the exact method, and post here in the blog.

I just put together on “meetup.com” the first Pensacola MUG meeting.  The first meeting is scheduled for August the 5th at 7:00 pm at the Panera Bread right outside the entrance to Cordova Mall.

I have thoroughly enjoyed the previous user group meetings I have attended and wanted to see if we can get a group growing here in Pensacola.  No formal agenda for the first meeting, just want to meet everyone and find out what people want to hear about in the future. I can be reached @ bmurphy AT paragon-cs.com.

The information and signup is at: http://mysql.meetup.com/300/.

It’s time to continue our series on the transactional storage engines for MySQL. Some might question why I even include Falcon because it is very much beta at this time. MySQL, however, has made quite an investment into Falcon, and while it is currently beta, the code is improving and it looks like that it will be production-worthy when MySQL server 6.0 hits GA.

If this is the case, it is important to begin to understand what Falcon was designed for and how it differs from other transactional engines such as InnoDB. I am going to concentrate quite a bit on the Falcon/InnoDB comparison as that is what everyone wants to talk about. This is despite my having heard MySQL employees repeatedly make statements to the effect of, “Falcon is not going to replace InnoDB,” or “Falcon is not competing with InnoDB.” Well, take that with a grain of salt. It certainly seems to me that they are competing for the same spot.

Warning

As I said, Falcon is beta. First off, don’t even try to use it in production. Using it in production means you will also be using MySQL Server 6.0, which itself is considered alpha. Your data will explode, be corrupted, or eaten by jackals. It won’t be pretty. It will cause great pain.

In addition, the features of Falcon are still changing. What I say here might or might not be accurate in the future.

End of Warning

So, why was Falcon even created?

Read the rest of this entry . . .

This week, database blogs seemed to talk about conforming to stereotypes as well as breaking them.

Fulfilling the stereotype of Microsoft software being unsecure, Microsoft released a very important SQL Server update that Aaron Bertrand notes patches “four elevation of privilege vulnerabilities.” That’s right, not one, not two, but four!!! At least there is a patch now……go forth and patch! Usually it is MySQL that throws ambiguous warnings or errors which are not an accurate representation of the actual error, but Bertrand also notes that there is A Little Management Studio Oops.

Contrary to stereotype, Microsoft is giving away content with NO DRM! Kalen Delaney asks, Did You Know the History of SQL Server? and shares a link to a chapter from a book on SQL Server 2000 in the Inside SQL Server series.

The PSS SQL Escalation Services team has fought many times about SQL Server I/O Bottleneck, I don’t have one, YES YOU DO! The team gives some reminders about how to interpet averages properly.

Allen White asks about Tools for the Reluctant DBA — that is, a programmer or administrator so good at databases that they were promoted to DBA, but may not actually want the job. Check out the comments and add your own.

Tibor Karaszi shares his stored procedure to find physical index details. Now you can use one stored procedure does what a stored procedure plus 3-4 tables ordinarily does.

But wait! There are some more updates!

Read the rest of this entry . . .

That’s right. MySQL now has a user group in Paradise.

I am always looking into connecting with other MySQL professionals, to share the laughs and tears, and to enjoy what we love working with every day, MySQL. I have always wanted to bring us all together, and I thought that this would have a good chance of doing so. Since I live in Malta, this made for the perfect location for it. If you live in Malta, or perhaps in Sicily or Tunisia, and want to take a trip, please do join us at our first meeting.

We will be having our first meeting in Mellieha, and please RSVP to me personally via email, westerlund (at) pythian.com if you want to attend. The date is set for Thursday, July 31^st at 6pm. We will discuss the current use of MySQL, its future, and whatever else comes into mind. I myself would love to hear usage stories for our first meeting, so we all get an understanding of how MySQL is used in Malta and environs.

I will make sure there are some refreshments to be had.

Let’s keep ourself educated and aware of how other people solve problems that we all sometimes encounter, as well as their interesting technical solutions. And let’s have some fun doing so!