The latest from the MySQL community

Back in April, I wrote On IPs, hostnames, and MySQL, which described the (sometimes surprising) ways in which MySQL deals with IP addresses, hostnames, and privileges, as well as some basics about the host cache itself. In a footnote to that post, I mentioned a patch I had written against MySQL 4.1 to give some more visibility into the host cache.

Over the past two days, I have worked on porting that patch to MySQL 5.01, 2, and making some fairly large improvements to it. The patch implements a few things:

1. Configurable Size — Without the patch, the size of the cache is fixed at 128 entries, and can only be changed by changing a #define and recompiling. You may now tune the size of the host cache using SET GLOBAL …

The domain registrars are making more money again, this time with .mobi. Naturally, people with a trademark or other (like .com) domain will be wanting to grab their .mobi so noone else can nick off with it. But what's the practical purpose of all these additional toplevel domains?

As far as I can tell, it's just a money making scheme just like .biz was. You may use one or the other to provide advance clarity to your users/customers, but you'll want/need to grab all the domains anyway to keep them safe.
Guh.

In MySQL 5, at the moment you can't write a recursive stored functions. It is forbidden.
Instead you can write a recursive stored procedure. That is not permitted by default but modifying a variable you can achieve such a recursion.
The variable is max_sp_recursion_depth.

SET GLOBAL max_sp_recursion_depth = 0.
A value of zero means: "no recursion" (the default value)

SET GLOBAL max_sp_recursion_depth=255
A value greater then zero means: the maximun number of nested recursion in a procedure. Max value is 255.

Pay attention to the variable named thread_stack, it is the amount of memory allocated for the stack of a thread. Writing a recursive routine the tipical error is to create an infinite recursion An infinite recursion leads very soon to fill up the stack. (the default value of the thread stack is less than 200K)
So, if you have …

Peter Zaitsev's blog entry on Duplicate indexes and redundant indexes certainly made a bit of a stir! It has already led to a new (mini) project by Baron Schwartz, the Duplicate index/foreign key finder which is publicly available on MySQLForge as a perl script. Daniel Schneller has entered the arena as well, devoting an entire blog to his own java implementation to tackle this and other index trouble.

I figured it would be fun …

I wonder how many people will attend a session at a tech conference where the start of the description reads as follows:

Hey You! Yes, You! Manager, marketeer, sales professional: are you tired of 98lb weaklings kicking silicon in your face?

I am thinking this way because my friends at MySQL AB are putting on another MySQL User Conference - this time from April 23 - 26 in Santa Clara, California.

The Call for Participation went live a few days ago and, as always, I am proposing a session. I don’t really need to go, but I definitely have a soft spot for the event, as I chaired the first two MySQL UCs. Also, I had a good deal of fun working on the …

Hello,

For those of you who are interested in learning more about MySQL Cluster, check out tomorrow's webinar presented by Vinay Joosery.

Info and registration:

http://www.mysql.com/news-and-events/web-seminars/mysql-ha-clustering.php

See you there,

Jimmy

I did a webinar for EMEA last week on tuning indexes and optimal SQL coding, and I had forgotten to publish the slides. Here they are in PDF format... Feel free to email me if you have any questions!

In other news, work on my new blog site is progressing. I should be done with this hideous beast of a website soon!

Zmanda has released their Recovery Manager for MySQL.  This is a great solution for DBAs seeking an easy-to-use backup and recovery solution.  Zmanda is based on the open source Amanda project started at the University of Maryland and the Zmanda team is a significant contributor to the Amanda project.  Zmanda offers support through their Zmanda Network subscription offering. 

Zmanda's has built on what was a rather generic backup project and now given it a much sharper focus by targeting the needs of DBAs.  The Zmanda Recovery Manager includes support for automatic backup scheduling, monitoring and reporting, backup analysis and granular recovery. Backup is a must-have capability for …

A few meetups ago, we had Jim Starkey come and speak. During that evening, he asked “Why is MVCC not serializable?” It was a “go and do your homework” kind of question.

So, in order to answer that question, let’s explore the concepts of isolation and concurrency, the “i” and “c” in ACID compliance. For this post, I will refer to “transactions”, by which I mean actual transactions, as well as statements where autocommit=0. In other words, every statement is its own transaction, unless it is within an explicit transaction, and for the purpose of this article, those statements are “transactions”. Of course, for many of the explanations below, if there’s only one statement in the transaction, there’s no problem. (As usual, if I make a mistake, please correct me. I will make sure all comments from all sources get to the article at …

Do you know the fine details of MySQL's transactional error handling with the InnoDB storage engine? If you're writing transactional SQL, you need to be prepared to handle errors appropriately, and to do that, you need to know how MySQL handles them. This article introduces you to the topic.