I haven't posted anything for a while, but that doesn't mean that nothing was happening around MySQL Optimizer. In fact, exactly the reverse was true - so many thing were happening that I didn't have time to blog.

I'll try to make up for the lost coverage. Let's start from smaller things. On Nov, 1 I've had a MySQL University session titled How MySQL Handles ORDER BY, GROUP BY, and DISTINCT.

The session page has slides and audio. If you're ready to spend 1 hour on MySQL's ways of doing ordering, you can listen to the audio. Otherwise, you can just scroll through the slides. They are reasonably self-contained and explain MySQL's ORDER BY and …

Niall Litchfield has published the 74th edition of Log Buffer, the weekly review of database blogs. Please step up and do a Log Buffer of your own! You’ll have some fun, bring new readers to your blog, and learn a few things in the process. Send a note to me, the Log Buffer coordinator, [...]

And just as I was saying I rarely get to fiddle with databases at work, this morning I resolved an issue with replication.

One of our database machines got rebooted yesterday without properly shutting down replication. The slave server had no idea this was happening. After looking around a bit it seems clear that MySQL on the master has moved to recording changes in a new log file, but the slave is still attempting to read from the old.

Which results in something like:

Slave_IO_Running: No
Slave_SQL_Running: Yes


After verifying the log positions in master and slave binary log files to confirm exactly where things stopped, I shut down the slave processes altogether and issue a command to point the slave to the new log file and reset the log position:


mysql> …

When I first arrived at MySQL more than 4 years ago, we didn't have much in the way of internal Quality Assurance. There were a couple of test suites that had been created and many engineers wrote unit tests. We supported a wide range of platforms, but formal testing was pretty spotty. As bugs were identified and fixed, the test suites were extended to help identify regressions. It was by no means a bullet-proof process; mostly the team depended on external testers to report issues. Sometimes a build would be created that would have some embarassing bug and we'd scramble... READ MORE

Have been thinking about my lack of postings . . . there are two reasons why posting about MySQL (and in general) has become rare in recent months.

First, and primarily, it is because of life changes. Where I used to have a moment here and there to write what was happening or on my mind, I'm barely able to post about anything these days. The whole time and season thing seems to be at play here.

Second, I just don't spend that much time fiddling with MySQL. It used to be that my work brought up lots of interesting things related to the database. I'm still the resident MySQL guy at my current job, but it's rare that something comes up that requires database expertise. And it's not that we're lightweights. We have a very high-volume system with a farm of database servers doing some pretty intense business/enterprise-level querying. We just don't find many issues that require attention down at the database level.

Some folks …

I've been a DBA for SQL Server databases since 2003, but always wanted to learn MySQL.
There is something cool in the fact that it's Open Source. Well, "cool" is actually a nice definition, and also is the all time favorite "warm fuzzy feeling", but if you think about it, it breaks down into the following (at least for me):

From the DBA/Developer perspective:

• You know you have nothing to worry about when you're installing another server.
  
• All the features are available - you don't need to make sure that you have that Visual Studio Ultra-Duper edition with Super-Amazing addon license.
  
• The bug-tracking system is ugly, but it's there out in the open, the way things work is transparent and you really feel that you can contribute to the product (my first critical bug in MySQL!).
• You grow with the …

What do you do when MySQL stopped working? Here are my 2 cents on what you should do.

1. Check if the database is running. Check the MySQL process.
   
   ps -aux | grep mysql
   
   
2. Check the error logs. Where is the location of the error logs again? The default location would be at /usr/local/mysql/data/ and look for a file with .err at the end of it. Sample errors would be:
   
   
   • Database got restarted - check with your sys admin why it restarted.
   • Replication failed - do a show slave status and check the error then restart the replication.
   
   
3. Check the MySQL process list and see if there are any read / write locking happening.
   
   show processlist
   
   
   

Planet MySQL dropped my blog for a while.  It came back, but not fully.  Several blogs are showing just an A as their content on Planet MySQL.  Maybe this will get their attention.

Planet PHP on the other hand has lost me and other completely.  They both happened about the same time.  I thought it was Wordpress.com, but it was not just Wordpress blogs and both the Phorum Planet and Chris Shiflett’s Planet have had no issues.

So, I wonder if the Planet PHP and Planet MySQL sites are running the same code for aggregating and something about is broken with some feed types.  Maybe this will get them looking.  The world is missing my wonderful content …

How do you teach a thinking pattern? In my previous job I worked with a data analyst, who was really good at thinking about how our data correlates among itself. He was good at the data analysis, good at thinking up relationships, and good at coming up with complex comparisons.

However, he wrote some pretty funky SQL. We had him take a course in basics of MySQL, and from time to time I’d take a gander in the slow query logs and pick a few queries and point out the good and bad things. Most of these are optimization tips, such as “Indexes don’t apply to columns when you’re applying a function to that column”.

But it’s hard to try to think outside of your own box, and even running EXPLAIN on every query won’t necessarily tell you how you can fix a query. The problem is that there’s no way to know if you have optimized the query as much as possible. Even experienced DBAs

I was often frustrated when repeating …

I seem to be getting a lot of requests on my webserver of the form: "GET /<directory-path>/pmapper-3.2-beta3/incphp/globals.php?__SESSION[PM_INCPHP]=<some-url>?" where replace the <directory-path> and the <some-url> with a path on your server and some random website respectively. It appears to be a probe to test for some vulnerability but I don't have the full request logged, only the request up