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.

5 comments:

Anonymous said...

I'm tempted to open an enhancement request for on connect/disconnect/commit triggers like those implemented in Firebird 2.1 ;-)

Anonymous said...

Hi db4free team.

First of all: THANKS! THANKS & THANKS!!

You're like an oasis in the sahara.

I'll be using my unique free db with you to develop a little web-app. That's max 5-8 queries at each refresh and with no more than 3 concurrent users.

I hope you can mantain this service for a long time, on my side, I'll be *helping* you with the ads (also my 2 comrades) daily. And when I have some money I'll send you some bucks for sure.

Great work and THANKS again.
PS. From México!!

Saludos

Anonymous said...

You know what would be great? A site that offers a great service like free MySQL testing that actually stays up and running. 1 of every 5 times I go to the site it's down.

Nilesh Jethwa said...

Hi,
I have been working on an MySQL DBA Dashboard. The Dashboard currently has basic information from the information schema.

Would like your opinion to improve and make it useful, for e.g to add DB performance metrics etc, but not sure where this information would be available.

Your opinion is highly regarded and will really appreciate if you could point me to some resources on this.
( njethwa @ gma!l . com)
Regards
Nilesh
MySQL Dashboards

Markus Popp said...

Sorry for the downtimes recently. It should be better again.