The latest from the MySQL community

Mark Atwood has published an interesting post with his wishlist for MySQL. One of his points is already (partly) covered with the LOAD XML command I made for MySQL 6.0 (or is it 5.2).

The main problem with it is perhaps that it works (like the LOAD DATA command) only with files, but in a web world you want it to be able to take input from other sources. You also need to be able to send an XML to several tables, with foreign keys coordinated etc.

Mark also has a wishlist for the opposite, the SELECT OUTFILE command. I have made a couple of stored procedures to help with this, but an extended SELECT OUTFILE would be mucke aesier to use and give much more possibilities. It would be fun to work on this, we'll see if I have the time, now that I am almost ready with my relocation..

There Ain't No Such Thing As A Free Lunch.
The price of a product, is only part of it's value. While not paying for the server itself, you end up paying more in development costs, as expected. It all makes perfect sense, and I'm certain everyone reading these words understands this without me explaining the obvious.

What I do intend to mention, are the specific shortcomings and limitations of MySQL, mostly compared to SQL Server - as this is the background I'm coming from.
Let's divide this to development tools, the database itself, and other stuff.

Development Tools
MySQL AB provides: MySQL Administrator, Query Browser and now the new MySQL Workbench.
There is also a myriad of 3rd party development tools for MySQL, but after getting used to the comforts of SQL Server's Management Studio, I can't say that any of them is truly …

Download Maatkit

Thanks again to all the great sponsors for my week of work on the kit!

This is the long-awaited "Baron worked on table sync" release. Hooray!

Please read the full blog post for important (very important!) information.

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