It’s time for another exciting installment of Log Buffer, the weekly review of database blogs.

On Prestidigitation of Oracle, Bradd Piontek gets it started with the second in a series on migration to 10g—Making the database a safer place, reviewing security-related changes in 10gR2. (Full disclosure: I should also mention that Bradd is now sitting just a few desks away from me.)

The ORACLE-BASE Blog has a dynamic duo of articles—one each on installing Oracle 11.1 and installing Fedora 10. (I’m a little late mentioning it, but this item is a fine complement to Augusto Bott’s latest article: Installing Oracle 11gR1 on Ubuntu 8.10 Intrepid Ibex).

Once you have your Ibex or Fedora up and running, check out Only Four Left, where John Piwowar has some tips for clearing the Oracle environment in a Unix session.

“I see that Tom Kyte has found a nasty little bug waiting to trap a few unlucky people as they patch to 10.2.0.4, or upgrade to 11g.” So writes Jonathan Lewis in his item on the SAS bug.

Asif Momen offered dynamic sampling myths dispelled, which he introduces thus: “There are couple of myths associated with Dynamic Sampling  . . .  i) Default number of blocks sampled are 32 in Oracle 10g and ii) Tables having less than 32 blocks are not considered by the optimizer for dynamic sampling.” Dispel away, Asif!

Cary Millsap has the latest in his ongoing series, Messed-Up App of the Day. This one comes courtesy a certain American airline. Hard to imagine something messed-up coming from that quarter, I know.

Messed-up apps and DBs. Writes Sam Hughes on Things Of Interest, “ . . . by far the least plainly bigoted [objections to gay marriage] . . . are the bureaucratic ones. To be blunt, the systems aren’t set up to handle it.” (more…)

This is the 124^th edition of Log Buffer, the weekly review of database blogs. Welcome.

Here’s what the Oracle blogosphere had to offer. On Oracle and more, Amardeep Sidhu announces the birth of the North India Oracle Users Group. Not too far away, on the AskDba.org Weblog gets the word out about a seminar by Tom Kyte on 18th December in Bangalore, arranged by the All India Oracle User Group.

Vivek Sharma examined Library Cache Latch Contention and Scalability to answer the question, “Why is Library Cache Latch Contention referred to as a ‘Severe Scalability Issue’?”

Marco Gralike discusses the value of setting an “impossible” password for SYS — even when you can use LOCK.

Greg Rahn of Structured Data explained the use of a preprocessor for external tables. He writes, “Before External Tables existed in the Oracle database, loading from flat files was done via SQL*Loader. One option that some used was to have a compressed text file and load it with SQL*Loader via a named pipe. This allowed one not to have to extract the file, which could be several times the size of the compressed file. As of 11.1.0.7, a similar feature is now available for External Tables . . . ”

On Oracle Corp’s Oracle Certification blog, Paul Sorensen debuts a new booklet called “Performing an Oracle DBA 1.0 to DBA 2.0 Upgrade”: “The knowledge required by the typical Oracle DBA has increased, requiring additional skills and in some cases job responsibilities. Oracle Certified Masters Joel Goodman and Harald van Breederode discuss this shift in DBA skills in this 10-page PDF booklet . . . ”

(more…)

This is the 123^rd edition of Log Buffer, the weekly review of database blogs. Welcome.

Let’s begin with some SQL Server, where it was a week of technical tips. Alexander Kuznetsov looks at defensive database programming. “In most cases LIKE conditions should by followed by ESCAPE clauses,” he asserts, continuing, “You have a choice: you can either have a CHECK constraint disallow special characters, or you can fix the procedure,” thus error-proofing your logic. Readers Alejandro Mesa and Adam Machanic suggest a couple refinements.

On In Recovery, Paul S. Randal shows how to move constraint indexes, taking as his cue a question he heard at SQL Connections Fall in Las Vegas: “Can CREATE INDEX … WITH DROP_EXISTING be used to move indexes that enforce constraints?” Short answer: yes. “This is really good, as it allows you to move these indexes without having to do DROP INDEX then CREATE INDEX.”

Piotr Rodak offers his example of the use of a numbers table, which “ . . . [implements] string split functionality for parameters passed as CSV arrays.” He asks for more examples, and Adam pops up there, too.

Stephen Forte has a method for using a TSQL Common Table Expression to find dupe records, which he was discussing at this week’s Tech Ed Europe. Nifty.

Speaking of duplicates. Mark, of Ajarn’s SQL Corner, wants us to know why he hates DISTINCT: (more…)

Welcome to the 122^nd edition of Log Buffer, the weekly review of database blogs.

Let’s start with MySQL. As you probably know, the Americans were at the polls this week. In keeping with this, Keith Murphy is polling for opinion on the question: MySQL on Debian or Ubuntu? The conversation is here; the poll is on Keith’s Diamond Notes.

A quick note by Sun CEO Jonathan Schwartz on how, with the outcome of those other polls, change has come to America, in the form of its new president and his choice of DBMS. Okay, it’s probably not his choice, but the association must be appealing.

Duleepa “Dups” Wijayawardhana also has a big new job. He’s taking over from Jay Pipes as MYSQL Community Manager, and his advice to himself is, Don’t Panic!. As a compatriot of Dups, I say: congratulations, eh!

Can we create a cross tab in MySQL? Yes we can! Arnold Daniels shows us how. Sheeri and Corey show some more.

On so many trails … so little time, Pabloj elucidates new syntax (the new WITH ROLLUP) for the old challenge of adding a “Total” row at the end of your tabular output.

Brian Aker was barking up a nearby tree . . .  or a nearby column, at any rate. Here’s his item on column stores in Drizzle. He writes, “I believe the second most important decision we will make long term for engines is going to be which column store we pick up on. I suspect we might even need two.” But which two? (more…)

Have you ever used Perfmon to collect performance statistics about a particular Windows Server? Do you hate manually picking the counters out of the list? Do you trace basically the same counters every time?

If you answered “yes” to any of these questions, here’s a tip that can save you some time. Once you’ve created and saved a trace, you can save/export it as an HTML document. When you need to trace a new server, with a few quick Find/Replaces from your favorite text editor, you can be up and tracing in minutes on the new host, without using the interface to pick and choose each of your counter objects.

Here’s how to do create a Perfmon trace template:

1. If you already have a saved trace somewhere, open perfmon on that server, expand Performance Logs and Alerts and click on Counter Logs. If you don’t already have a saved trace, you’ll have to create one before getting started.
2. From the Performance Logs and Alerts screen, right-click on your trace and click on Save Settings As…
3. Save the file somewhere and give it a meaningful name. (The default location is My Documents.)
4. Copy the file somewhere onto your machine for later use.

Congratulations. You’ve created and saved a template. It’s that easy. Now let’s say you’re ready to use it somewhere else.

(more…)

This week gives me a chance to get back into something I love to do—write. For those who don’t know, my name is Keith Murphy and I am a MySQL DBA at the Pythian Group. In addition, I have the privilege of being the editor of the MySQL Magazine, a quarterly  magazine for those who use MySQL on a daily basis, either as a DBA or a developer. The sixth issue was just released last week and is available for download now. But enough about me! Let’s see what you all had to say this week.

Beginning with the world of MySQL.

Monty Taylor kicks things off, bringing us news of the ability to use the innodb plugin with Drizzle. I just think its great how the Drizzle development is moving along so rapidly. My thanks to everyone involved.  Monty also puts out a call to help defray the cost of the purchase of the drizzle.org domain name.

There have been several posts this week on virtualization of MySQL Server. I point you to a colleague at Pythian, Sheeri Cabral, who wrote about some of the benefits our customers have already experienced while using virtualized servers. While virtualized servers are not a panacea, they definitely have a place in the DBA’s environment. If you don’t work with any currently, you will be doing so down the road.

The OpenSQL “unconference” is coming! The date is Nov 14 -16 in Charlottesville, Virginia, USA. Details and registration information are available here. Speaking of conferences, the MySQL Users Conference, which is in April in Santa Clara, CA, USA has extended its deadline for topic proposals.

Baron has a great post on naming conventions for your schema.

In the how-to department, Falko Timme has a good tutorial on how to set up GreenSQL to protect your databases from SQL Injection attacks. Finally, Johan Andersson has a good introductory blog post on optimizing queries for a NDB cluster, and a post on how you can get the Cluster Sandbox tool for testing the MySQL Cluster.

The Oracle world had sad news this week. Carl Backstrom, who was an Oracle employee and APEX developer, was killed early Sunday morning in a car accident in Nevada. (more…)

Previously on Log Buffer: Log Buffer #119.

And now.

Welcome to Log Buffer #120. My name is Warner, and I’m a SQL Server DBA at The Pythian Group. This is my first time on Log Buffer duties ever, so here’s hoping I can give everyone a fair and unbiased look at this week in the database blogging world (and related).

I admit I had no idea of the community or state of the PostgreSQL RDBMS, and so I definitely learned some new stuff this week. First off, over on “The Scale-out Blog” Robert Hodges invites us all to get our shoephone and get smart about the new world of PostgreSQL replication.

Moving over to “ad’s corner”, Andreas Scherbaum gives us a glimpse of the glitz and glam of PGDay opening. Then he entices us with the title, “Party in the evening” just to horrify us by revealing that—you’d better sit down—by the end of the event half of the beer was still untouched. Next time Andreas, who you gonna call?

“Everything is a DNS problem,” is my new voicemail message, and also the title for Kris Buytaert’s blog, where we go deep into uncovering once again the 10 month-old enigma of why did Sun buy MySQL.

Next up, I will speak to you, Mr. (or Ms.) Cross-Platform DBA. You think you know all of Oracle’s exp command-line switches? All of SQL Server’s bcp formats? And all of the things you can do with . . .  whatever you use with DB2? (more…)

Logon triggers were introduced in SQL server 2005 SP2. They fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server.

Logon triggers can be used to audit and control server sessions, in such usage scenarios as:

• Tracking login activity, you can create a user table to audit user sessions.
• Restricting logins to SQL Server, you can prevent a specific login from establishing a user session.
• limiting the number of sessions for a specific login, you can limit a specific login from establishing more than a specific number of user sessions to the instance.

You can extract useful XML data about Logon events inside Logon by using the EVENTDATA function. I will demonstrate a little later how we can use this function to audit log-ins to a user table.

How do Logon triggers work?

The life cycle of a Logon trigger is simple: (more…)

SQL Server 2008 is out of the bag and—luckily for my team—at Pythian we are already seeing customer interest in upgrading, even from SQL Server 2000 and 2005. There are many new features and there will definitely be more blog posts coming from the team regarding them, but for now, I was just browsing around the new 2008 Dynamic Management Views (DMVs), and did a quick overview of some that spiked my attention:

select * from sys.dm_db_mirroring_auto_page_repair

It looks like grouping by db or file id would be useful to zoom in on possible disk issues causing the page errors. Beware—only 100 rows are kept for any database, so if you want to keep a full history you should move those records on a scheduled basis.

select * from sys.dm_db_mirroring_past_actions

This one could be useful for monitoring when there is no witness on a mirroring setup; or for creating a mirroring history report.

select * from sys.dm_db_persisted_sku_features

This one should be used as a warning sign before moving databases between editions, specifically between Enterprise and Standard. Paul Randal has a great post on it: SQL Server 2008: Does my database contain Enterprise-only features?.

(more…)

Welcome to the 119^th edition of Log Buffer, the weekly review of database blogs.

We start in the MySQL world with some engine news. On Brian “Krow” Aker’s Idle Thoughts, Brian explains the state of engines in Drizzle, the pared-down MySQL. He begins, “So many engines, and so little to choose from. This is one of our two major decision points in Drizzle right now.” Maria, Falcon, PBXT, and InnoDB are in the dock.

Arjen Lentz asks a simple question: Would you prefer InnoDB to be the default storage engine?, also the subject of a quick poll he created.

While we’re on the subject of defaults, Giuseppe Maxia, the Data Charmer reports on STATEMENT-based replication as the default in MySQL 5.1: “MySQL 5.1.29, the next (and last) release candidate, will revert the default replication mode to STATEMENT based  . . . MIXED mode won’t be the default anymore.” The commenters are not totally happy with that.

Well, one of the good things about MySQL is that, if you’re not pleased with it as it comes, you can soup it up yourself. (more…)