How to automatically call a query for every new connection in MySQL?

Question

Is it possible to set or initialize a query in MySQL for every new connection ?

Whenever a connection is created, it will call a procedure or a select query automatically ?

Any script or config option for this?

Intoxicant

Exemplify your use case – Twohanded

I want to select some data and save values in variables for every session.. value is different for each session as data changes. – Intoxicant

@drew: yes. I need this. No latency. query should call on each new connection. – Intoxicant

@drew ok, if i can afford latency, whats the solution ? – Intoxicant

@drew, my use case is to pre calculate the result of select query and save in variable for each session .. – Intoxicant

A variable and session in what context? You have shell and linux tagged here. There is no session with mysql like PHP. It has connections. You can mimic some behavior or availability of info. But this question is all over the place. Rein it in bro. Enable someone to help you here by clearly defining what you want, not 5 word blurbs. Otherwise people read the vaguaries and gladly move on to other questions that are viable. – Euterpe

@drew: i want to store values in mysql connection variables.. – Intoxicant

I have idea what you want to do. But you will have do it in your application. MySQL has no support for connection events and registering queries on them. Other solution would be to create connector - and make database available over him only. Maybe some CLI exists with event registration etc. – Une

@emestas : thanks for help.. but i am looking something at database end.. – Intoxicant

Pretty sure there's nothing at the database end. You are probably gonna have to call the procedure yourself to set up the session_variables. You may be able to do this with some command line magic or, as @ErnestasStankevičius suggests, if it's an application code one entry point to the database (class or function) and add the procedure call there. – Warfourd

Answer 1

You may want to check init_connect variable:

A string to be executed by the server for each client that connects. The string consists of one or more SQL statements, separated by semicolon characters. For example, each client session begins by default with autocommit mode enabled.

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_init_connect

Apparatus

Caveat: init_connect is not executed for root. This is a strong reason for having separate user(s) for application code. – Gustation

There is no latency in this solution (other than how long it takes to run the queries). – Gustation

The point is that the query is run immediately after connecting; there is no delay (latency) before starting the query, as might happen with some polling mechanism. – Gustation

Answer 2

If you have not only mysql-client tool, but additional any application layer, there is more jedi-style :) You can create simple decorator for your connector's class that will execute some queries when instance created (in constructor)/connect method invoked.

Hope it helps

Haematocryal