Hello folks, it’s great to be back from hiatus. This is the 183rd edition of Log Buffer (arguably the best edition of Log Buffer yet!), the weekly review of database blogs.

The last time I wrote this was just under 2 years ago!!! WoW. Things have changed. Sun bought MySQL, Oracle bought Sun. Those were bombshell deals. At least you can rest assured that some things can be constant. I still eat my daily serving of broccoli (among other healthful “things”). I urge you all to go the fridge and grab some veggies prior to sitting down for this week’s… ahem… digest.

Starting with Oracle, Pythian’s own Alex Fatkulin illustrates a bug (?…likely) that could lead to logically corrupted data. Doug Burns provides an update on his Hotsos 2010 trip with some serious thumbs-up action pointing in Tanel Poder’s direction and his affinity toward SQL*Plus.

Over at Mark Rittman’s Oracle Weblog, Venkatakrishnan J talks about Oracle Warehouse Builder 11gR2 – Importing Essbase Cubes using ODI Knowledge Modules – Part 1. This should drive the MySQL folks bonkers (in a good way) since he points to an example by David Allen to import MySQL Metadata over and vice versa.

Oracle Virtualization Blog’s Adam Hawley let everyone know about a Best Practices around Oracle VM with RAC RAC SIG webcast. It was on March 18th, but it was recorded and should be available online. I’m interested in this stuff so I included it here. I hope you can enjoy it too.

I had the honour to recently provide a training session at a customer site on tuning methods and tools. A key topic of the discussion was related to Oracle statistics and the CBO. It was timely that the Oracle Scratchpad’s Jonathan Lewis posted a series created by Doug Burns all about stats. Nice.

Has anyone ever told you (or maybe you deduced it on your own) that leaving out where clauses is a bad thing. Well, leave it to Charles Hooper on his Oracle Notes to prove to us that sometimes, they are actually more efficient for solving certain types of problems. In another excellent post titled Physical Reads are Very Fast, Why is this SQL Statement Slow Charles also dives into why a slow query is “actually” slow. There’s an interesting discussion taking place. More performance tuning goodness was posted by Joel Goodman discussing some interesting behaviour with Oracle Index Leaf Blocks contention. Tell RAC to Leave Your Leaves Alone! Kerry Osborne illustrates the use of an interesting hidden parameter (_high_priority_processes) to resolve “log file sync” issues.

Over on the other side of the fence in the MySQL world, Jay Pipes @ Join-Fu gives us some background on the MySQL Transaction Log. Vadim Tkachenko with the MySQL Performance Blog has a number of posts on Percona 9.1 as well as a list of related sessions at the 2010 MySQL conference. Check it out.

On a more somber note (and by somber I mean legal, we all hate legalese don’t we?), Giuseppe Maxia over at The Data Charmer discusses Protocol, the GPL, and how Bazaar can help. He also has some good takeaways from the Linux MySQL distros meeting in Brussels. Baron Schwartz at xaprb has a new tool he’d like the MySQL folks to take for a spin. Try mk-query-advisor, a new Maatkit tool. It uses heuristics to find problems in SQL. Please use it and give feedback!

Are you interested in MySQL Clustering? I am. Andew Morgan has a new post introducing a tutorial to Build MySQL Cluster 7.1 from source – including MySQL Cluster Connector for Java. With Alex Fatkulin maybe running into an as yet possibly, sort of , kinda non-discovered bug, it’s only fair we also get some vision into the dark side on the MySQL front. Shlomi Noach states But I DO want MySQL to say “ERROR”!.

Brian Aker invites readers to participate at this year’s O’Reilly MySQL User’s Conference where they will be doing their first ever Ignite talk series.

Lastly, Stewart Smith has a very good set of posts illustrating Stored Procedures/Functions for Drizzle. Check this out (from Stewart’s post).

drizzle> select libtcc("#include <string.h>\n#include <stdlib.h>\nint foo(char* s) { char *a= 0x199c610; strcpy(s, a); return strlen(s); }") as result;

+--------------+
| result       |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.01 sec)

I dare you.

On to SQL Server….

Scary DBA SQL MVP Grant Fritchey discusses Undocumented Virtual Column: %%lockres% and also recaps the SNESSUG March Meeting.

Jamie Thomson gives keyboard junkies some tips to Kill your temp tables using keyboard shortcuts : SSMS. Anyone interested in distributed queries should read Buck Woody’s Using linked servers, OPENROWSET and OPENQUERY.

Finally, Aaron Bertrand says with conviction “Yes, you can benefit from both data and backup compression”.

Having now exhausted my supply of munchies, it is time for me to retire. I bid you all a fantastic week. Keep your data safe, folks.

Shakir

Good morning and welcome once again to the (usually) weekly round-up of news that matters to Sys Admins. We missed last week for reasons previously stated, client work always comes first. This week was yet another fast and furious week so let’s get started.

Operating Systems

In case anyone was wondering about SCO vs. Linux it is still going on. If anyone has a lot of free time on their hands and is interested in lost causes, check out SCO vs. Linux: The story so far at The H Online. Even more details can be found at the prolific GrokLaw in Summary of SCO v IBM.

If you are running Max OSX you may be vulnerable to at least 20 major security flaws in you system. Read the rest of this entry . . .

I’ve hit a bug in Oracle 11.2.0.1 when working with deferrable constraints which I think is worth sharing as it may have profound consequences under certain scenarios.

Let’s start by creating a simple table with a deferrable primary key:

SQL> create table def_bug(n number primary key deferrable initially deferred);

Table created

SQL> insert into def_bug values (1);

1 row inserted

SQL> insert into def_bug values (2);

1 row inserted

SQL> commit;

Commit complete

You can confirm that the primary key constraint is working fine by trying to insert a duplicate value:

SQL> insert into def_bug values (1);

1 row inserted

SQL> commit;

commit

ORA-02091: transaction rolled back
ORA-00001: unique constraint (SRC.SYS_C004070) violated

So far so good. Open a second session and execute the following update:

SQL> update def_bug set n=3 where n=2;

1 row updated

Do not commit yet and execute in your first session:

SQL> update def_bug set n=3 where n<=2;

The above update will block due to our second session holding a lock on the row where n=2. Now commit your second session…

SQL> update def_bug set n=3 where n=2;

1 row updated

SQL> commit;

Commit complete

…and then commit your first session:

SQL> update def_bug set n=3 where n<=2;

1 row updated

SQL> commit;

Commit complete

Take a look at the data now:

SQL> select * from def_bug;

         N
----------
         3
         3

Ouch! This was certainly unexpected. You can confirm that the primary key is still working by trying to insert a duplicate value again:

SQL> insert into def_bug values (3);

1 row inserted

SQL> commit;

commit

ORA-02091: transaction rolled back
ORA-00001: unique constraint (SRC.SYS_C004070) violated

It certainly looks like the update statement did not take into account deferrable constraint declared on the table during restart caused by the write consistency mechanism.

CC BY-NC-SA 2.0

There were two things I have wanted to do for some time now. The first was to come up with a way to quickly and easily set up a DarkPAN mirror so that we would have more control over our dependency chain at work. The second was to make a portable CPAN proxy service, so that I can always have access to my favorite modules, even if the machine I’m working on has no Internet access. Last week, I finally had a few ‘rount tuits’ to spend on this type of background itch, and the result is dpanneur (for dépanneur, French Canadian for convenience store).

Installation

As it stands, dpanneur is a very thin Catalyst application gluing together the goodiness of CPAN::Cache and MyCPAN::App::DPAN, and throwing in Git as the archive manager.

To get it running, first fetch it from Github

$ git clone git://github.com/yanick/dpanneur.git

then check that you have all the dependencies

$ perl Makefile.PL

and run the script that will create the module repository

$ ./script/create_repo

For now, the module repository is hard-coded to be in the subdirectory cpan of dpanneur. A branch called proxy is created and checked out. Eventually, I’ll use GitStore to push newly fetched modules to the repository, but for the time being if dpanneur is to be used as a proxy, that branch must remain as the one being checked out.

All that is left is to fire up the server in whichever mode you prefer (single-thread test server would do nicely for now)

$ ./script/dpanneur_server.pl

and there you are, running your first dpanneur. Congrats! :-)

Using it as a caching proxy

You can use the server as a caching proxy, either for its own sake, or to seed the DarkPAN branches. To do that, you just have to configure your CPAN client to use http://yourmachine:3000/proxy:

$ cpan
cpan[1]> o conf urllist = http://localhost:3000/proxy
cpan[2]> reload index
cpan[3]> install Acme::EyeDrops
Running install for module 'Acme::EyeDrops'
Running make for A/AS/ASAVIGE/Acme-EyeDrops-1.55.tar.gz
Fetching with LWP:
    http://localhost:3000/proxy/authors/id/A/AS/ASAVIGE/Acme-EyeDrops-1.55.tar.gz
etc..

As the modules are downloaded, they are also saved and committed within the repo

[dpanneur]$ cd cpan

[cpan (proxy)]$ git log -n 3
commit d065ad152f2204295334c5475104a3da517b6ae1
Author: Yanick Champoux <yanick@babyl.dyndns.org>
Date:   Wed Mar 10 20:32:52 2010 -0500

    authors/id/A/AS/ASAVIGE/Acme-EyeDrops-1.55.tar.gz

commit e8d2e83d1b16e2e0713d125f9a4bd2742681f859
Author: Yanick Champoux <yanick@babyl.dyndns.org>

Date:   Wed Mar 10 20:31:42 2010 -0500

    authors/id/D/DC/DCONWAY/Acme-Bleach-1.12.tar.gz

commit 7e0b4b600bac8424c519199ee96dc56ffbb177eb
Author: Yanick Champoux <yanick@babyl.dyndns.org>
Date:   Wed Mar 10 20:30:47 2010 -0500

    modules/03modlist.data.gz

Using it as a DarkPAN server

There is not much more involved to enabling DarkPAN repos. All we have to do is to create a branch with the modules we want and have the ‘dpan’ utility bundled with MyCPAN::App::DPAN generate the right files for us.

To continue with the example of the previous section, let’s say that we want a DarkPAN branch containing Acme::EyeDrops, but not Acme::Bleach. Then we’d do

                        # only necessary if you are running
                        # the server while you work on the branch
[dpanneur]$ git clone cpan cpan-work   

[dpanneur]$ cd cpan-work

                        # branch just before we imported Acme::Bleach
[cpan-work (proxy)]$ git branch pictoral 7e0b4b600bac8424c519199ee96dc56ffbb177eb

[cpan-work (proxy)]$ git checkout pictoral
Switched to branch 'pictoral'

                        # cherry-pick the Acme::EyeDrops commit
[cpan-work (pictoral)]$ git cherry-pick d065ad152f2204295334c5475104a3da517b6ae1

                        # rebuild the module list
[cpan-work (pictoral)]$ dpan

                        # commit the new 02packages.details.txt.gz
[cpan-work (pictoral)]$ git add .
[cpan-work (pictoral)]$ git commit -m "dpan processing"

                        # push back to the mothership
[cpan-work (pictoral)]$ git push origin pictoral

And that’s it. Now point the cpan client to http://yourmachine:3000/pictoral, and you’ll get the limited mirror.

cpan[1]> o conf urllist http://localhost:3000/pictoral
cpan[2]> reload index

cpan[3]> i Acme::EyeDrops
Strange distribution name [Acme::EyeDrops]
Module id = Acme::EyeDrops
    CPAN_USERID  ASAVIGE (Andrew J. Savige <asavige@cpan.org>)
    CPAN_VERSION 1.55
    CPAN_FILE    A/AS/ASAVIGE/Acme-EyeDrops-1.55.tar.gz
    UPLOAD_DATE  2008-12-02
    MANPAGE      Acme::EyeDrops - Visual Programming in Perl
    INST_FILE    /usr/local/share/perl/5.10.0/Acme/EyeDrops.pm
    INST_VERSION 1.55

cpan[4]> i Acme::Bleach
Strange distribution name [Acme::Bleach]
No objects found of any type for argument Acme::Bleach

This is the 182nd edition of Log Buffer, the weekly review of database blogs. Make sure to read the whole edition so you do not miss where to submit your SQL limerick!

This week started out with me posting about International Women’s Day, and has me personally attending Confoo (Montreal) which is an excellent conference I hope to return to next year. I learned a lot from confoo, especially the blending nosql and sql session I attended.

This week was also the Hotsos Symposium. Doug’s Oracle Blog has a series of posts about Hotsos. If all this talk about conferences has gotten you excited, Joshua Drake notes that 14 days and the hotel is almost full for postgresql conference east which is March 25th-28th in Philadelphia. And the Oracle database insider notes that the Oracle OpenWorld call for papers is now open.

According to Susan Visser this week (ending tomorrow) is also read an e-book week. So if you have not already done so, read an e-book! She links a coupon for an e-book in the post.
Read the rest of this entry . . .

Persistence Smoothie: Blending NoSQL and SQL – see user feedback and comments at http://joind.in/talk/view/1332.

Michael Bleigh from Intridea, high-end Ruby and Ruby on Rails consultants, build apps from start to finish, making it scalable. He’s written a lot of stuff, available at http://github.com/intridea. @mbleigh on twitter

NoSQL is a new way to think about persistence. Most NoSQL systems are not ACID compliant (Atomicity, Consistency, Isolation, Durability).

Generally, most NoSQL systems have:
Read the rest of this entry . . .

Most of this stuff is not PHP specific, and Python or Ruby or Java or .NET developers can use the tools in this talk.

The session on joind.in, with user comments/feedback, is at http://joind.in/talk/view/1320.

Slides are at http://talks.php.net/show/confoo10

“My name is Rasmus, I’ve been around for a long time. I’ve been doing this web stuff since 1992/1993.”

“Generally performance is not a PHP problem.” Webservers not config’d, no expire headers on images, no favicon.

Tools: Firefox/Firebug extension called YSlow (developed by yahoo) gives you a grade on your site.
Read the rest of this entry . . .

subtitle: Monetizing Social Media

Why is social media and social networking essential to you and your business? (because it will drive sales, but there’s very few analytics for ROI on social networking and social media)

Relying on advertising is no longer working for print newspapers and television. So why do we think it will work on internet media?
Read the rest of this entry . . .

What is confoo? It is the sequel to the PHP Quebéc Conference (2003 – 2009). This year PHP Quebec decided to team up with Montreal-Python, W3Quebéc and OWASP Montréal to produce confoo.

And now, on to Mark Pilgrim of Google speaking on HTML5.
Read the rest of this entry . . .

Video fragments of my session posted at the end — read on.

I arrived at Omni Mandalay Hotel on Sunday evening with Dan Norris. I was flying through Chicago and it turned out that Dan was on the same flight and only few rows behind me. Small world.

Preparations for the conference were very chaotic on my part and, of course, I didn’t have either of my presentations ready. I was very stressed and getting sick as well — it looked like a complete disaster waiting to happen. I’d like to say that I was feeling like Doug Burns as he often managed to get sick just before a conference. Of course, I worked on my slides for the last few days as well as on the flight and presentation was slowly getting there but boy was I tired!

I quickly said hello to the crowd in the bar on the way to my room and rushed away to do some more damage to my slides. And then I had a brilliant idea — I could still see one of my best mates and do something good about my presentation! I asked Doug if he was interested in the preview (he probably wasn’t interested but he couldn’t say it to me) especially that my session wasn’t on his original agenda. Of course, that would mean that he had to leave a bunch of other good friends and spend some time tete-a-tete. Knowing Doug, this is some of the hardest thing to ask from him but it shows how good of a friend he is! (Plus, everyone thinks that he is anti-social anyway. Shhhh!)
Read the rest of this entry . . .