5

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?

Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81
  • Exemplify your use case – 1000111 Jun 28 '16 at 06:24
  • I want to select some data and save values in variables for every session.. value is different for each session as data changes. – Aman Aggarwal Jun 28 '16 at 06:26
  • @drew: yes. I need this. No latency. query should call on each new connection. – Aman Aggarwal Jun 28 '16 at 06:27
  • @drew ok, if i can afford latency, whats the solution ? – Aman Aggarwal Jun 28 '16 at 06:50
  • @drew, my use case is to pre calculate the result of select query and save in variable for each session .. – Aman Aggarwal Jun 28 '16 at 07:06
  • 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. – Drew Jun 28 '16 at 15:43
  • @drew: i want to store values in mysql connection variables.. – Aman Aggarwal Jun 29 '16 at 05:42
  • 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. – Ernestas Stankevičius Jun 29 '16 at 05:58
  • @emestas : thanks for help.. but i am looking something at database end.. – Aman Aggarwal Jun 29 '16 at 06:11
  • 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. – Arth Jun 30 '16 at 08:38

2 Answers2

10

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

Pavel Katiushyn
  • 795
  • 4
  • 9
  • Caveat: `init_connect` is not executed for `root`. This is a strong reason for having separate user(s) for application code. – Rick James Jul 01 '16 at 01:32
  • There is no latency in this solution (other than how long it takes to run the queries). – Rick James Jul 01 '16 at 01:34
  • 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. – Rick James Jul 02 '16 at 18:41
3

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

D.Samchuk
  • 1,219
  • 9
  • 9