Friday, August 31, 2007

init_connect system variable

Even after working with MySQL for quite a long time, I sometimes stumble over great things that I didn't know. It happened to me again when I met Beat Vontobel (together with other MySQL folks like Giuseppe, Roland, Lenz, Susanne) at the FrosCon Conference.

What I learned about was the init_connect system variable. It allows to trigger a SQL statement (or Stored Procedure) whenever a new user that doesn't have SUPER privileges connects to the server. There's no need for me to explain this in large detail, since Beat already did this in one of his blog articles.

Why is it so useful for me? To keep db4free.net free from database accounts that aren't in use anymore, it's important to log the connects. So far I tried to do it with the general_log - but this of course also logs all the queries from the users ... which produces a huge amount of data that I don't really need for this purpose. Of course there are ways to filter that out, but since resources are limited, every way to avoid unnecessary data from the very beginning is of big help for me.

And since I never heard about it before - even though it is so useful - maybe there are others like me who could make use of it, but just haven't heard about it.