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…)

“There are 10 types of people in the world — those who understand binary, and those who don’t.”

The term “binary” in MySQL has many different meanings. How many can you come up with? I have 6, but I am willing to believe there are more!

0) “Binary distribution” is the name for the package that contains a binary. Another use is “binary installation” but that’s pretty much the same usage pattern as “binary distribution”, so I won’t count “binary installation” as a separate usage.
1) “Server binary” or “client binary” is the actual program (mysqld, mysql).
2) “Binary format” is a compressed format. For example, DECIMAL is stored in a “binary format” — each group of nine digits is compressed into 4 bytes.
3) “Binary log” is the change log. You can argue that this is an extension of #3, because the binary log is a compressed log, but “binary log” is encountered ubiquitously in the MySQL world, and “binary format” is somewhat obscure knowledge.
4) “Binary CHARSET” - sets the collation to be case-insensitive. For instance, utf8_bin and latin1_bin are the binary collations for the utf8 and latin1 character sets, respectively.
5) “Binary string” - a byte string. This is also known as the BINARY data type. There is some kind of wit/pun in the fact that a number in binary is actually a “bit string”.

Any more I missed? There are over 1600 mentions of the word “binary” in the 5.0 manual!

Brian Aker delivers the keynote speech at OpenSQL Camp: State of the Open Source Databases. The presentation begins with a disclaimer:
“There is no way I’m going to tell you exactly where the future of databases go. We have way too many egos in the room to ever even begin a discussion…”
and ends with Aker saying,
“What the hell does that mean?”

View the video online at
http://technocation.org/node/649/play
or download the 42.6 MB Flash video file (.flv) directly at http://technocation.org/node/649/download.

My summary: (more…)

The MySQL 5.1 GA Release will be on or about Dec. 6th, 2008.

How do I know?
(more…)

(one note, I have updated a previous blog post by adding the video for the LISA presentation I gave entitled “How to Stop Hating MySQL”)

OpenSQL Camp is in full swing! Baron Schwartz has done an amazing job organizing this free unconference.

We are well into the 2nd session of the day, and the quality of the presentations is excellent (though I expected that!) and it is always great to see people.

Some pictures….
(more…)

So, I actually started my trip early, the taxi picked me up from my house in Malta at 5:15am, and after a short stop in the lounge for some breakfast, I boarded KM100 bound for Heathrow. After a layover there, I eventually arrived at Dulles International, and there was no queue for US customs and border patrol!

Picked up the rental car and drove the 101 miles to Charlottesville, VA — in dense fog.

Met with Sheeri and talked for a bit on Thursday, before just falling asleep, due to time difference and the long travel.

So on Friday, we had a few hours to run errands and pick up last minute stuff that we had forgot back home and so on, before the start at 6pm. Met up with everyone, had some nice interesting conversations and towards the end of the night, 14 of us decided that it was time for dinner, so we went over to a local restaurant. Lots of interesting talk, mainly MySQL related, and lots of food, just like it should be. There were some pictures taken, but I’ve yet to see them online, but keep watching Planet MySQL and I’m sure you’ll see them.

It was a nice start to the weekend, so I’m hoping that today (Saturday) will exceed my expectations as well! I’ll post updates and reviews of the talks, in a daily digest.

Until then,
Nick.

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…)

(Note: updated with the presentation video on 11/15/2008)

At LISA 2008, I gave a presentation entitled “How to Stop Hating MySQL: Fixing Common Mistakes and Myths”.

The presentation slides can be downloaded as a PDF at:

http://technocation.org/files/doc/stophatingmysql.pdf

View the video online at http://technocation.org/node/646/play or download the 202.5 MB Flash video file (.flv) directly at http://technocation.org/node/646/download.

Here are some notes and links I referred to:

Technocation, Inc containing free videos, a MySQL podcast (currently on hiatus) and blog posts about MySQL.

Why you want to switch to MySQL 5.1

How MySQL Uses Memory

Query Cache Configuration

EXPLAIN manual page

EXPLAIN cheatsheet

The maximum length of a VARCHAR is only restricted by the maximum row length. In most storage engines, the maximum row length is the maximum allowed by MySQL, which is 65,535 bytes. Only the NDB storage engine has a different maximum value.

The VARCHAR data type has some overhead. If the length of VARCHAR is less than 255, one byte per row is used to store the actual length of the string. If the length of VARCHAR is greater than 255, the overhead cost of storing the string length is two bytes per row. Thus, the maximum length of a VARCHAR should be 65,533 bytes.

However, that is not the case:

mysql> CREATE TABLE max_len_varchar(fld VARCHAR(65533) CHARSET latin1);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

mysql> CREATE TABLE max_len_varchar(fld VARCHAR(65532) CHARSET latin1);
Query OK, 0 rows affected (0.16 sec)

Why is that? I believe that is because there is also a 1-byte overhead per row, though I have not confirmed that. There is some allusion to that on the Forge Internals page……but I have not confirmed that it is the case.

(one of the reasons I believe this is that there’s a different error message when the character set is multi-byte:

mysql> CREATE TABLE max_len_varchar(fld VARCHAR(16384) CHARSET utf8);
ERROR 1074 (42000): Column length too big for column 'fld' (max = 16383); use BLOB or TEXT instead

This actually gives the maximum length you can use. Note that the maximum usable length still seems to be missing 1 byte from the theoretical max of 65,535:

mysql> SELECT 16383*4+2;
+-----------+
| 16383*4+2 |
+-----------+
|     65534 |
+-----------+
1 row in set (0.00 sec)

)

(and yes, you can use TEXT and BLOB fields if you actually want a variable-length string with more than 65,532 bytes, that’s not really the point of this article)

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…)