Today, while helping someone find some of the code that implements SHOW VARIABLES I was reminded at how insanely complex it all got with the information_schema implementation and the changes to the SHOW commands that came with it. Specifically, I was trying to find exactly how the SHOW VARIABLES output gets populated — what the source data is.

By way of example, I present the code to implement it in 5.1.51 (the new way), and in 4.1.21 (the old way). I, at least, have a much easier time sorting out the old way. They both start with the same basic entry point, in the parser, in show: within sql/sql_yacc.yy.

SHOW VARIABLES in MySQL 5.1.51 sql/sql_yacc.yy

The show token just does some initialization.

10062 show:
10063           SHOW
10064           {
10065             LEX *lex=Lex;
10066             lex->wild=0;
10067             lex->lock_option= TL_READ;
10068             mysql_init_select(lex);
10069 …

Computer World has an article about Telecommuting . So, I thought I’d chip in my inflated $0.02. Telecommuting, doesn’t mean working from home. This is one of the biggest myths, and truths people will realize over time. What makes me such an expert? I’ve been telecommuting for the past 3 years, straight, full time. I’m not talking about moonlighting, and doing part time work for companies who post on craigslist. I’m talking multi million dollar projects. I haven’t been fired, I haven’t left the company, and I’ve been rewarded with a raise, and more importantly, I haven’t missed deadlines.

You still have a job, act like it. (The Ugly)

Telecommuting, is not that hard. But, it’s not the holy grail for workers either. You’re not going to …

Apple announced, as expected, a release date for Mac OS 10.5 Leopard. The date is October 26th, 2007 in case you missed it. Since everyone on the internet is a guru, and therefore, so am I, I will give you plebes reasons to upgrade to Leopard. But, first let me qualify myself worthy of such preachery (gurus can make up words).

1. I’ve been using Leopard since January 2007, since I’m an ADC Member, and I have early access.
2. I’ve been using it on my primary work machine since September 2007.
3. This is the internet, and somehow you found this post to read, therefore you don’t know any better..

I’m going to assume that the build shipped to us ADC members, has been updated and will not be the build that will be released to the masses, there are still bugs in my release, but it’s leaps and bounds ahead of the August release, and very close to stable.

…

Matt has a good post on the recent push to make people use PHP 5. He makes several good points, and I’m just jumping on the same bandwagon.. forcing a version # change, just because the engine works better is pointless. Going into the political arena a little bit, making people switch to PHP 5 is like changing CAFE standards, it won’t do a thing for the billions of cars already out there, and it won’t change the basic dependence on crude oil. It’s just a lot of hot air to make the choir sing to themselves, again.

I’ve been running PHP 5 on my systems for the past 3 years, without any issues. Even some of the code I wrote in PHP 4, works flawlessly (with the same bugs) in PHP 5 as PHP4. Which brings me to the main point.. End Users don’t care or want to know what the engine is doing . If the application does something they want, they’ll use it. If …

So what happened? Well, two days ago I noticed connection issues while trying to create a blog post on MySQL?s new enterprise offering (more on that later). I sent in a support request to Tera-Byte, my colocation provider. After a while I checked and noticed that I could connect via SSH, but that the server was rejecting my private key and my password.This had me concerned so I called Tera-Byte and asked what was happening. Turns out that they had mistakenly deleted my virtual private server. But they were kind enough to not only increase my new virtual private server?s memory from 360 megs to 512, but also credit me one month?s fees. I was in shock at this news and thanked the tech for the upgrade and refund and hung up.After I got off the phone I skipped past the denial and bargaining stages of grief and arrived at anger. So, calling Tera-Byte back up to ask how exactly this …

I’m not the first person to run into this and I certainly won’t be the last. This is also already covered in: Bug #19093 I just want to say again how annoying it is that the default for show status is session instead of global. It bit me again yesterday. I ran show status and was confused because most of Com_* is 0 yet uptime was a few days. Then it hit like a brick to the face that I was working on 5.0 instead of 4.1 and the default for show status is session. I know the default has changed and it still bites me. This is going to cause people hours of confusion the first time they do what I just did.

If you want the default changed please go to Bug #19093 and add a comment.

Just a quick note while it is in my head:

When testing software, especially for performance, remember to test on the same hardware you intend to deploy on. One trend I have seen for some time is that testing is often done on whatever happens to be lying around and not otherwise assigned.

While you can certainly test MySQL or an email server on such hardware, you should at least be aware that by providing hardware that does not match what you use in production you will not get an accurate picture, and sometimes the differences can be dramatic. This is especially true of enterprise software with high demands for memory and storage such as databases and MTAs: running such software on an old desktop or five-year-old server will not provide an accurate picture of how the software will perform on a multicore server with several gigabytes of RAM and a RAID array.

My advice is this: if you know you will be deploying the solution …

Last month I blogged about the silly changes to the default for the SHOW STATUS command in MySQL 5.0 in Stop the madness: SHOW STATUS, and I filed MySQL Bug #19093: SHOW STATUS defaults to SESSION.

Well, it looks like Monty has spoken and this will not be fixed. That’s unfortunate, but oh well, I tried.

You can read the full text of Monty’s reply in the bug report itself, but his reasons for supporting the current behaviour boil down to:

• It’s similar to what SHOW VARIABLES does. — Yes, this is true, but neither commands’ output makes sense, really. If you do SHOW [SESSION] STATUS, you get a mix of session-scoped and global-scoped results back, and there’s no way to tell which is which. Same goes for SHOW [SESSION] VARIABLES. Ugh.
…

Are you a MySQL user? Have you tried 5.0? Did you notice that SHOW STATUS was giving you strange results?

Here’s the skinny: The SHOW STATUS command, which has been in MySQL, well forever, has had a long-standing feature request; to have the ability to report its metrics per-session. That functionality was added in MySQL 5.0, in the form of two new syntaxes: SHOW SESSION STATUS and SHOW GLOBAL STATUS to give the per-session and server-wide statistics, respectively. It’s great, and I’m happy to see it.

The problem comes in because of the default behaviour that was chosen for the basic SHOW STATUS with no SESSION or GLOBAL keyword—that is, the command that every MySQL DBA has been using for years—now defaults to per-session statistics.

Every tool, program, monitoring script, performance graph, etc., that uses SHOW STATUS (which is pretty much all of them) is broken in 5.0. Why? Well, for no reason, …

MySQL 5.0 has a ton of new features. In fact, several tons. Many people and companies were waiting endlessly for some of these features. Some of the new features include: Views, Stored Procedures and Stored Functions, Triggers, and some extra optimizations. Hoorah.

However, not all is peaches. In fact, there are few peaches to be found. Consider the rest of this post a bitch/complaint session and a call for a return to sanity. Let’s take a look at some of the major features of MySQL 5.0:

Views — Views are a …