I’m looking for a way to share data between two MySQL connections/sessions. Obviously tables are the trivial answer, but for reasons of security (possibly insufficient privileges) I wish to avoid that.
The type of data to be passed can vary. Ideally I would be able to pass multiple pieces of information (dates, texts, integers, etc.) around. If impossible, I would do with texts only, and if impossible yet, I could do with a single text (but reasonably long).
There is a way to do so: by writing to the file system (SELECT INTO OUTFILE + LOAD_FILE()). However I wish to avoid it, since writing to files from within MySQL requires creation of a new file each time; no overwrite and no purging; this litters the file system.
So: any other tricks? Is there some way to pass data via GET_LOCK()/RELEASE_LOCK() (none that I can see other than Morse code)?
Is there some global variable that is unused, can be changed dynamically, and has enough space? (I couldn’t find one)
I appreciate any input.
Justin, thanks.
As per Joro’s request, I’m elaborating:
I’m not too afraid of other people possibly reading the data interchanged between the two sessions.
I need this for my ongoing common_schema project, which is a sort of framework for MySQL. The need for inter-process communication is a shared part for several ideas. One of them would be the control of one “master” script or “dashboard” of some sorts, over scripts running in other connections (so, being able to gracefully stop them etc.).
Another idea involves controlling execution of stored routines (I won’t elaborate too much here, as I’ve only just started looking into this).
So basically, I will have:
One “master/dashboard/manager” connection, looking at “standard/worker” connections (one or more; if need be, I’m willing to settle for one).
The worker connections will send info about their state, and the manager connection will be able to send execution hints like “you should terminate now / you should resume now”.
There will be cooperation between the two; they just need to be able to pass info between themselves.
Like I said, I don’t care too much about eavesdropping at this moment.
If the solution is based on tables — which is the trivial way of sharing data between connections — then I already have all that I need. But this will of course require additional grants, which I prefer to avoid.
Justin,
The VIEW solution brings back some memories from a similar old solution I used. Good call: will look into it!
Though in terms of infrastructure it still requires tables and GRANTs, there’s a lightweight essence to it. Thanks — will check it out.
I know you said no UDF, but what you really want is gearman + Gearman MySQL UDF, but I know that they don’t have wide enough adoption for them to be usable by you. I figured I’d share this though, because you might think it is cool and it might let others know what you can do (anything you can image) with the UDFs.
It is how the Shard-Query MySQL proxy works, btw. The proxy intercepts the user’s original query, then runs a SQL call like this instead:
select gman_do(‘shard_query_worker’, v_orig_sql);
The gearman worker creates a Shard-Query object and executes the requested query. It then encodes the resultset in JSON in a representation friendly to the proxy so that it doesn’t have to reformulate it.
Since the results come back already properly formatted, the Lua script simply decodes the JSON and sends the array to the client as the result.
This makes the proxy very lightweight and unlikely to crash. Complex scripts are not very stable in my experience but simple ones are.
Justin,
The approach that uses process lists will not suffer from any permission-related problems as I assume both sessions would be using the same user, so they will be able to see each other in the process list even without the extra privilege.
have you tried the global using this: http://sourceforge.net/projects/mysql-sr-lib/ ?