By “Good SQL Querying”, I am not referring to “how to make your queries more perfomant.” I am about to go on a mini-rant about how to make readable and self-documenting SQL queries.

One practice that will get me instantly going on a rant is using a comma join. There is NO reason to do the following:

-- uses the sakila sample database
SELECT first_name, last_name, address
FROM customer,address;

What kind of join did the original author intend? A CROSS JOIN? Or did they really want an INNER JOIN and forget the WHERE clause?

The answer: you do not know for sure; you can only guess. Had the query been

SELECT first_name,last_name,address
FROM customer INNER JOIN address;

you would know that the author intended an INNER JOIN; had the query been

SELECT first_name,last_name,address
FROM customer CROSS JOIN address;

you would know that the author intended a CROSS JOIN. I advocate using INNER JOIN instead of JOIN because like the comma, JOIN is ambiguous.

For full disclosure, the MySQL EXPLAIN plan is exact same for the above queries. The difference is purely semantic, but I think it a very important difference, because it makes the query author’s intentions clear. There are issues with the comma join not having the same precedence as INNER JOIN, but that is for more complex queries. To wit:

SELECT first_name,last_name,address
FROM customer, address INNER JOIN city;

will actually do address INNER JOIN city first, then join with customer. This was changed to be more like the SQL standard, so it’s only in MySQL 5.0.12 and higher that it acts this way.

My feeling is that a comma join should NEVER be used in MySQL. There is no such thing as a “comma join”, really what you mean is an “inner join” or a “left/right join” or “cross join” (or “natural join”).

I know that naysayers will say that

SELECT first_name,last_name,address
FROM customer INNER JOIN address;

actually does a CROSS JOIN (Cartesian product), so it is folly to rely only on the name of the join. And I agree. However, if you name your joins appropriately for what you want and intend them to be, it is much easier to catch mistakes.

And while we are on the topic of good SQL query techniques…..I would like to rant a bit about join criteria. (more…)

We hit an ORA-01450 error today trying to do online rebuild for an index in an unusable state. This was a non-unique index on a fairly large column — VARCHAR2(800 CHAR).

SQL> alter index i1 rebuild online;
alter index i1 rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

It rang a bell. I remembered that I encountered this issue a while ago, but I couldn’t recall the details. I know that it has nothing to do with the actual data size — it’s an error that can occur during index creation. A single index block must be able to fit at least two index entries, so the maximum index key size defined by the block size and overhead.

The key length is calculated as:

key length = sum of all column lengths
           + number of columns
           + 2 (key length)
           + 6 (restricted ROWID)
           + 1 (ROWID field length)

If I didn’t miss anything, the key length for my index should be 800 + 1 + 2 + 6 + 1 = 810. Hold on . . . we have character length semantic here — let’s check the database character set:

(more…)

If you have created a new Oracle 11g database using DBCA and opted to use by default 11g’s enhanced security settings or, at least, the audit setting, then you risk the unlimited growth of the SYSAUS tablespace that hosts the audit trail table SYS.AUD$. I realized that while reviewing the slides of my presentation on 11g’s new features, a few of which covered security enhancements.

During my presentation at the TOUG meeting later that day, I mentioned that concern, and Mohamed El-Shafie from Oracle quickly noticed that there is no auto-purge. I promised to have another look at the maintenance tasks in 11g to confirm that, and indeed, the audit trail is not purged automatically when auditing is enabled by default.

Here is a quick remedy — scheduling an audit trail maintenance job.

First create a PL/SQL procedure that will accept a number of days to keep. It rounds down to the beginning of the day. I like to dump a few diagnostic messages to alert.log when my maintenance procedures are running, so I included that here as well. (There was a typo in purge data calculation — thanks to Nial for catching it.

create or replace procedure purge_audit_trail (days in number) as
  purge_date date;
begin
  purge_date := trunc(sysdate-days);
  dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
                        purge_date || ' started');
  delete from aud$ where ntimestamp# < purge_date;
  commit;
  dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
                        purge_date || ' has completed');
end;
/

Then let’s schedule a new maintenance job using Job Scheduler, which appeared first in 10g. Oracle 11g includes the default maintenance windows group, MAINTENANCE_WINDOW_GROUP, and we will use that to run the purge.

(more…)

I was contacted by the folks at MONyog and asked if I would review MONyog. Since using MONyog is something I have been wanting to do for a while, I jumped at the chance. Of course, “jumped” is relative; Rohit asked me at the MySQL User Conference back in April, and here it is two months later, in June. My apologies to folks for being slow.

This review is an overall review of MONyog as well as specifically reviewing the newest features released in the recent beta (Version 2.5 Beta 2). Feature requests are easily delineated with (feature request). This review is quite long, feel free to bookmark it and read it at your leisure. If you have comments please add them, even if it takes a while for you to read this entire article.

While the webyog website gives some information about what MONyog can do, it is a bit vague about what MONyog is, although there is a link to a PDF whitepaper on What is MONyog? which does answer much of these questions.

The screenshots available from the website are accurate, so I will not reproduce them here. I will note that I have not shared this feedback with the webyog team yet, so I may be upset that a feature is lacking, and the feature may be implemented but I missed it. I will post a follow-up in that case, even though they will likely comment here too.

My reference points — I have used other monitoring and graphing tools such as Nagios, Cacti, and Intermapper as well as MySQL’s Enterprise Monitor.

Overall

As an overall review — MONyog is the best out-of-the-box GUI monitoring tool for MySQL that I have seen. It “just works.” As promised, getting up and running quickly is easy, and having a centralized location for monitoring is very useful. The graphs are beautiful and the statistics that are graphed are useful time-savers.
(more…)

This is what I found in the APEX documentation that comes with Oracle 11g, in the chapter describing building a very simple application:

In Region Source, add the following at the end of the existing code:
WHERE nvl(DEPARTMENT_ID,’-1′) = nvl(:P3_DEPARTMENT_ID,’-1′)

This WHERE clause changes the query to display only those employees that belong to the selected department accounting for empty DEPARTMENT_ID as well.

Making sure the database instance couldn’t potentially use an index in DEPARTMENT_ID column? Why on earth would you teach novice APEX developers such a horrible practice?

To me, it’s one more confirmation that Oracle can do an excellent RDBMS, but when it comes to database applications development . . .

Small local user group meetings have their own beauty because of the special intimate atmosphere. We expected somewhat higher attendance but it seems that people were already in the holiday mode — it’s been a somewhat hot day and school kids had their last classes yesterday — so we’ve got about half of expected 50 attendees.

I should say that it’s been a nice and relaxing day for me. I started the morning reviewing my slides and then continued at the airport and on the flight. My flight to Toronto arrived just a bit late but I had plenty of time so I arrived quarter to one at the Oracle office building in Toronto (technically, Mississauga).

Djordje Jankovic and Carol Legros, who invited me there in the first place, were already there and, being a nice host, Djordje took me downstairs for a quick lunch. When we were back, the audience was almost ready so I just few 5 minutes to hook my laptops up which is more than enough — I know my Mac well enough now to avoid any surprises. Kenotes even managed to display few PowerPoint slides that Djordje used starting the meeting. By the way, he admitted that Keynotes is way cooler than PowerPoint. Well, that’s no brainer!
(more…)

Today I’m doing a presentation at the Toronto Oracle User Group meeting. It’ll be my first time there and I’m really looking forward to it.

My session is called Oracle 11g New Features Out of the Box. It went pretty well at Collaborate 08 and I think it will be the right audience at the TOUG meeting. I’m reviewing the slides at the moment — usual last minute tweaks but it’s time to leave to the airport — my flight is in 1.5 hour.

I have already checked-in on both the flight to Toronto and return. I didn’t know that you can check-in online on the return flight without completing one-way first. Well, I like technology advancing. :)

In case you are attending Oracle Open World 2008, the biggest Oracle conference in the world, and interested in either (or both) MySQL or Oracle Enterprise Manager Extensibility — I posted a proposal for a new presentation:

Extending Oracle Enterprise Manager by Example — Creating MySQL Management Plug-In

I’ve started looking into Oracle extensibility several years ago and since then I’ve seen lots of improvements in Extensibility Guide and many new plug-ins have seen the light of the day. However, creating a new plug-in is still considered to be something special and not available to mere mortals.
In this presentation we will see how easy it is to create a new plug-in. What are the steps and the method to follow. As an example we will work with MySQL Management Plug-in that I have recently released to public.
This session includes a live demo.

If you are interested, you may as well vote for it. If there are enough interested people, Oracle might select it for the conference.

If neither this nor my previous submission make it, well, I’ll go there anyway to have some fun and meet good old and, hopefully, new friends.

Please note: This plug-in has been created and is now available for download:

Usenix 2008 - Automated System Management, by Æleen Frisch of Exponential Consulting (and numerous books)

What is automation?

generic [perl|shell] scripts with cron,at

Problem: overlap of effort

So folks developed automation systems. General automation tools are around:

cfengine, puppet, cfg2

These are general — files, directories, etc. Don’t need to use chmod and chown and underlying commands.

However, they don’t really survive reboots well. For that, we tend to use tools more towards jumpstart, kickstart.

Monitoring with Nagios, related tools are rrd-tools such as cacti, cricket, munin, “or any of 8,000 others.” Automating ideas like iostat.

Nessus is a security testing tool.

homegrown, general, performance related, also automated backups — bakula, amanda, legato.

What do you want automated?

“Coffee machines”.

A lot of unsolved problems are human interaction.

Other problems solved — using remote power management.

Inventory management is another issue. HP OpenView is one, but Frisch says folks are not happy with it. You can pay for high-end monitoring systems.

A question came up about an inventory of users on systems. LDAP or NIS or Active Directory is the traditional solution where there are no local accounts. There’s authentication and then authorization, and the automated tools usually have authentication information but not authorization information. (You can handle it, but making groups on these tools is usually painful.) Authorization is usually handled either locally or as “if you’re authenticated you’re authorized”.

We talked about how to power down 500 machines when the air conditioning goes out, or when the power is going down. Combinations of temperature probes, “wake-on-lan”, remote power on and off were discussed.

What do people use to automate installs and configuration on Windows? For installation, the Windows native tools are great. It was noted that efs works better on Windows.

Anyone using Splunk with Windows? One answer — it works OK, there are some daemon tools to convert Windows Event Log to syslog.

Splunk came up as a topic of discussion, how it’s a great log management software and solves a problem we’ve had for decades — how to deal with logs. Frisch says, “Splunk is the most promising thing out there.”

Record keeping of time was brought up, as well as time management. Basically what we do at Pythian, so I explained how we do things. Other folks brought up ticketing systems as well. Jira and RT (Request Tracker) and OTRS (Open Ticket Request System) were brought up as well.

Also for change management, some folks use ClearCase (not open source), and others use rancid, others use Trac or bugzilla + change management system like subversion. Jira was recommended as a product that does both (with an add-on).

Use DHCP to help automate IP assigning. rsync is your friend too.

(it occurs to me that a dishwasher is an interesting problem; why do we have a dishwasher instead of just having a sink/dishwasher hybrid? Similarly, a hamper that does laundry for you when it’s full.)

From 7:30 - 8:30 pm tonight, Wed. June 25th, in the Berkeley room of the Sheraton Boston, I will be hosting a Birds of a Feather conversation entitled “Pros and Cons of Managed Services”. This will go beyond MySQL and even beyond remote database management, and just deal with the overall pros and cons.

Come, share your good and bad experiences, and discuss why managed services may or may not be appropriate for your situation. I will try to take notes at the BoF.

(Note: I have no idea if they check badges for Birds of a Feather sessions or not)