Friday, February 12, 2010

Log Buffer #178, a Carnival of the Vanities for DBAs

Dave Edwards has offered me to write this week's Log Buffer, and I couldn't help but jump at the opportunity. I'll dive straight into it.


I'll start with Oracle, the dust of the Sun acquisition has settled, so maybe it's time to return our attention to the regular issues.

Lets start with Hemant Chitale's Common Error series and his Some Common Errors - 2 - NOLOGGING as a Hint explaining what to expect from NOLOGGING. Kamran Agayev offers us an insight into Hemant's personality with his Exclusive Interview with Hemant K Chitale. My favorite quote is:

Do you refer to the documentation? And how often does it happen?

Very frequently. Most often the SQL Reference (because I don’t — and do not intend to – memorise syntax. Syntax has to be understood rather than memorized). Also, the RMAN Reference (known as the “Backup and Recovery Reference”) and the Database Reference.
At least I'm not the only one forgetting the exact syntax of every command.

Chen Shapira offers us her thoughts on diagnostics in Automated Root Cause Analysis, and I have to agree with her that sometimes it is best to be offered good visualization tools rather than cut and dry solutions and recommendations.
Miladin Modrakovic explains how to avoid an Oracle 11g vulnerability: Oracle Exploit Published 11g R2. Gary Myers makes his own contribution about security issues with 10g and 11g in Exploits and revoking the risks of revoking PUBLIC.

As a MySQL DBA I've heard many times the question, "What is the right pronunciation?" and purists would say 'es-que-el' as the ANSI standard specifies. But before there were any standards, there was SEQUEL. I heard the real story many times. Iggy Fernandez's article does a pretty good job summarizing it in Not the SQL of My Kindergarten Days quoting some references for those who would like to dig into the details.

During the weeks leading to the final approval of Sun's acquisition by the EU, there was a lot of speculation about MySQL's destiny's under Oracle. I'm sure that many of the MySQL Community members that kept their cool, they did so because they knew that Ken Jacob would most likely have a say on it. So when the news of his resignation was published, I'm sure that those people (myself among them) starting scratching their heads and started wondering about MySQL's future as well. Matt Assay's news article on CNet, Oracle loses some MySQL mojo, offers a great insight on the issue including quotes of Pythian's own Sheeri Cabral. There are plenty of other articles on the issue in Planet MySQL's feed.


Continuing in the context of Oracle's acquisition and Ken's resignation, Bridget Bothelo's article MySQL users hope for the best, prep for the worst speculates about what is in the mind of those who run MySQL in production. If you are interested in the different releases and branches, you'll find plenty of blogs this week starting with Jay Jensen's question When should we expect the next stable MySQL release beyond 5.1? and Ronald Bradford's FOSDEM 2010 presentation Beyond MySQL GA: patches, storage engines, forks, and pre-releases – FOSDEM 2010.

Life goes on and in there is still plenty of action in the MySQL community. As Colin Charles reminds us in his MySQL Conference Update: Grid is up, go promote and register!, this should be an interesting year. In the storage engine and tools front, it's worth checking InfiniDB's impressive performance numbers in InfiniDB load 60 Billion SSB rows trended for storage engine developments and RE: HeidiSQL 5.0 Beta available in the tools segments.

Finally to end the MySQL section with some more mundane issues, here is a collection of articles with mysqldump related scripts and tools: Restore from mysqldump --all-databases in parallel and MyDumpSplitter-Extract tables from Mysql dump-shell script. No list of articles on backups would be complete without asking Can you trust your backup?.

Today we were talking at work about Perl vs Python for scripting. Me, I'm a Perl 'gansta' (see the PostgreSQL section). Traditionally MySQL has had a pretty bad Python driver, but Geert Vanderkelen is working on correcting that. If you're a Python fan check his FOSDEM 2010 presentation at FOSDEM: 'Connecting MySQL and Python', handout & wrap-up.

SQL Server

Aaron Bertrand published 2 really interesting articles that could be apply to other databases as well: When bad error messages happen to good people and Injection is not always about SQL with a funny (if it weren't for the Prius parked in my driveway) example at the end.

2010 MVP Summit is coming up and Thomas LaRock offers his 2010 MVP Summit Preview. His insight applies to other similar events (are you reading MySQL UC attendees?).

In my experience date and time representation and manipulation are tricky in databases, these 2 articles offer some tips: Convert FILETIME to SYSTEM time using T-SQL and Dan Guzman's Ad-Hoc Rollup by date/time Interval

I'm really bad judging the value of SQL server articles, so I'm going to choose the easy way and trust Adam Machanic's T-SQL Tuesday #002: The Roundup to provide with a few technical references.


Apparently the PostgreSQL community need their own "Geert" (see reference in the MySQL section) based on what I've read on Damn it feels good to be a (perl) gangsta and Josh Berkus' Postgres needs a new Python driver. Are you up to the challenge? In that case, step up to the plate, that's what Open Source is all about.

The PostGIS group had an important announcement: PostGIS 1.5.0 out and PLR working on Windows 8.3-8.4 installs which the author calls "Perhaps the best release ever", so make space on your disk and schedule and take it for a spin.

Baron Schwartz offers an interesting view on How PostgreSQL protects against partial page writes and data corruption. It offers great insight from a well known MySQL guru.

Last but not least End Point's people have determined with mathematical precision PostgreSQL version 9.0 release date prediction, make sure you read the article and get ready for it.

I hope I kept you reading up to this point and see you around in the blogosphere.

Wednesday, February 3, 2010

Using MariaDB with MySQL Sandbox

A few days back MariaDB announced their first GA release (see Released: MariaDB 5.1.42), so it is time to start testing it and there is not better way to test any MySQL version in a control environment other than MySQL Sandbox. However Sandbox relies on the fact that the tarball and tarball target directory are prefixed with mysql, which is not true with MariaDB. So here are the 2 tricks I had to use to make it work out of the box.

These steps are explained to create a single sandbox, the tips can be extrapolated to any other configuration. Also, I am trying to avoid renaming any files and/or directories as to leave the packages as close to the original as possible.

Step 1: Use A Symlink For The Tarball

The make_sandbox script will then think it's manipulating a MySQL tarball. Assuming that the default directory is where you have the tarball:
ln -sv mariadb-5.1.42-Linux-x86_64.tar.gz mysql-5.1.42-Linux-x86_64.tar.gz
make_sandbox /home/gnarvaja/Downloads/mysql-5.1.42-Linux-x86_64.tar.gz --sandbox_directory=maria_5.1.42

Make the adjustments needed to your own platform and version.

The make_sanbox run is going to fail since it expects a subdirectory named ./mysql-5.1.42-Linux-x86_64 which doesn't exist since we used a MariaDB tarball.

Step 2: Use A Symlink For The MariaDB Binaries Directory

For the same reason as above, now create a symlink for the directory to where the tarball was extracted and re-run make_sandbox:

ln -sv mariadb-5.1.42-Linux-x86_64 mysql-5.1.42-Linux-x86_64
make_sandbox /home/gnarvaja/Downloads/mysql-5.1.42-Linux-x86_64.tar.gz --sandbox_directory=maria_5.1.42

Remember to always include the --sandbox_directory option to avoid name conflicts in case you want to compare MariaDB with the corresponding MySQL release.

This time the installation will succeed and you'll be ready to start your testing.


I tried to install using the original tarball name using different options and the process failed with different error messages. I looked into the make_sandbox code and I saw some dependencies that would've taken me some time to figure out and fix. This method can be considered a hack, but it gets you up and running in no time.

Giusseppe, if you happen to see this blog, I'll be glad to test a patch when you have it.