Pythian Group

I recently granted ALTER access in MySQL so a user could run the ALTER TABLE command . However after I granted the necessary privileges, the user was still not able to perform the tasks needed. Reproducing the issue using a test instance, I granted a test user the required privileges and MySQL reported no errors or warnings when the ALTER TABLE was run:
Read the rest of this entry . . .

I think many people truly realized how much they take the MySQL documentation for granted during the recent multi-hour outage from mysql.com’s data center. Apparently there is a lot of FUD floating around about the legality of mirroring the documentation, as presented by Justin Swanhart and asked by Mark Callaghan.

The manual page at http://dev.mysql.com/doc/refman/5.1/en/copyright-mysql.html says:

You shall not publish or distribute this documentation in any form or on any media, except if you distribute the documentation in a manner similar to how Sun disseminates it (that is, electronically for download on a Web site with the software) or on a CD-ROM or similar medium, provided however that the documentation is disseminated together with the software on the same medium.

Now, I am not a lawyer, however, to me this means that you can indeed mirror the documentation, so long as you mirror the binaries as well. Giuseppe commented on Mark’s post (linked above) saying “There is no license restriction to mirror the docs.”

Note that I played a part in unknowingly spreading the FUD — I thought special permission was needed to mirror the documentation (and binaries) and indeed, it is not.

As a postscript, what are everyone’s favorite site mirror programs? Searching http://www.ohloh.net for an open-source website mirror did not reveal anything very popular, though I am sure there are a few “standard” mirroring tools that folks use. (Perhaps I should have searched for spiders, and seen which spiders have sync/download capabilities?)

By now many folks know that MySQL documentation is not changing its license. This is an issue with many sides, but before I go through them, I want to address a comment made by Masood Mortazavi:

People who are interested in forking the server — and potentially interested in creating what is in effect separate communities of their own — should probably develop their own docs for their own forks.

(There is a cost involved here, I know. However, it should be a cost worth paying if developers of forks really believe in their work. MySQL AB certainly paid that cost in developing the docs while it had already made the code itself freely available under GPL. So, the playing ground among all forks, etc., and including MySQL itself, is actually quite level.)

MySQL AB paid the cost in developing the *software* as well. Why is it that the cost of writing documentation from scratch is acceptable, but the cost of writing the *software* from scratch isn’t?

I totally understand the concern that if people have the same rights to fork the documentation as they do to fork the code, confusion may arise. Many do not agree that the risk is high enough to warrant keeping the documentation “closed”. However, even if that is the case, Section 2 of GPLv2 states:
Read the rest of this entry . . .

Martin Brown’s blog shows a pretty good way of navigating the MySQL Reference Manual. It’s worth noting, however, that finding the different topics has been a lot easier since mysql.com started using a Google appliance for its search.

I use the documentation all the time and have been doing so for years (I won’t claim that I can remember +2000 pages worth of ever-changing content). A few years back, I stopped using the search box on dev.mysql.com because the result sets were enormous, with lots of unrelated references. My technique was to do a Google site search:

For replication use the expression: replication site:http://dev.mysql.com/doc/refman/5.0/en/index.html

The result set was smaller and I would find what I was looking for relatively easily, usually within the first page.

Since the documentation team implemented the Alphabetical Index, it has succeeded the Google search as my favorite way to get the information I needed. Things are easy to find and never more than a couple of URLs away.

Read the rest of this entry . . .

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 . . .

While doing a standard audit for a new client, I recommended a few changes to get better performance. Because I had several changes, I used the documentation at

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

and found that innodb_flush_log_at_trx_commit is a dynamic variable. I was surprised, because most operations dealing with file sizes and operations are not dynamic. But the client proceeded with:

set global innodb_flush_log_at_trx_commit = 2;

and got:

ERROR 1193 (HY000): Unknown system variable 'innodb_flush_log_at_trx_commit'

So I searched for others who may have had the same error, and ended up getting an unintentional googlewhack. Well, it’s not a real Googlewhack, because it has more than 2 words and I used quotes to surround it — but I was pretty surprised that there were not a lot more people like me who trusted the manual. Granted, it’s “intuitive” that the variable is one that would need a restart….but because I was looking up others, I just looked up all of them, and thus fell into the trap.

The search I did was “Unknown system variable innodb_flush_log_at_trx_commit”

And the bug I filed is http://bugs.mysql.com/bug.php?id=35575.

I am an ex-tea tottler. Several months ago, I started to once again drink that elixir of the Database Gods. Now that I am beginning to drink coffee again, I can really appreciate Jon Emmons’s humor in naming his blog site www.lifeaftercoffee.com. Truly, a DBAs work life does NOT begin until after coffee.

Unfortunately, it had been years since I actually brewed a pot of coffee. What was I going to do? Like many people using a new technology, I wanted to make some but had no idea of where to begin. Luckily, our company had a BIG sign posted next to the coffee machine, “Are you tired of weak, crappy coffee?”, with detailed instructions.

I don’t think I am giving away too many Pythian secrets here, but not only did it explain to only put a portion of the potential water reserve so the coffee would be stronger. It also gave warnings of potential problems. One problem was that putting the coffee grinds and water in before turning the unit off would cause a burst of steam to cause the grinds to be blown all over and ruin the pot of coffee. Pure Genius!

The instructions were so humorous because I see them as exemplifying the work ethic at Pythian. And that is, documentation and sharing of information. When we do work for a client, we carefully document all the steps taken, and any problems encountered and workarounds we discovered. The information is then put into a shared repository for all the DBAs to access and use in similar situations.

Most recently markings were added to the actual coffee pot to indicate MINIMUM and MAXIMUM levels. As a DBA, I am very familiar with thresholds. If something like free space reaches a MININUM, then I react by adding additional space. When the coffee pot reaches the minimum, there is an indication that it is time to make a new pot. The MAXIMUM indicator assists us to ensure that we do not go over our limits (for water).

So sharing the secret to making a perfect cup of coffee or cloning a database with RMAN are handled the same way around here. Freely, in the spirit of teamwork and making each others’ lives easier for both us and our clients.

How often do you read the reference guide for something you already know ? When I find little things like the one to follow, I wonder why Oracle doesn’t broadcast these small improvements more clearly.

I needed to change the undo_retention parameter in a 10gR2 database. Unfortunately I forgot whether the parameter was in seconds or minutes, and I wanted to see what the default was. So I open up the docs, go into the Reference guide and get the info I need from the top few lines.

I wanted to set undo_retention to some large value in order to use flashback. So I decided to read if they were any notes about setting this too large. I started reading the notes. I’ve read this in the past, so didn’t expect anything new. Then I read the following:

“For fixed-size undo tablespaces, the system automatically tunes for the maximum possible undo”

What? Fixed size undo vs autoextend undo ? That’s new!

So I clicked the link to the administrator guide and kept reading.

Apparently, when your undo tablespace is NOT autoextensible, then Oracle will switch from time based undo retention, to size based retention. Same as the good old days, where you set X amount of space for rollback segments and if there were more changes in the database … well simply you could not use flashback that far into the past. As opposed to your undo tablespace growing to something you didn’t want.

I checked back, and this feature doesn’t seem to be in 10.1, so it’s brand new. According to the notes however, it’s still time based, but based on the amount of undo you are generating and the fixed size of your undo tablespace.

Stay tuned: I will be testing this “new” behaviour sometime in the near future!