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.
You could write an UDF which has ‘usable’ dynamic variables.
An other option is the processlist, but that seems hard and impractical.
Comments on tables is also not a great solution.
You could write something to the binlog and try find a way to read it back.
What about creating a function in SQL with the communication packet?
@Daniël,
Thanks. I should have mentioned this should work on any vanilla MySQL so:
– No UDF
– Comments on tables require table access; this can be an issue of privileges
– How do you read from the binlog from within a session?
– No problem writing a stored function, but how will it pass the data around?
If the two sessions were connected to MySQL at the same time, you could try issuing a long running query to pass the information through the process list:
SELECT ‘@message: This is my awsome message!’ FROM DUAL WHERE SLEEP(10);
Another session could browse the process list looking for ‘@message’.
Or…
sess1> SELECT GET_LOCK(‘WAITER’, 0);
sess1> keep looking at the process list…
sess2> SELECT ‘@message: This is my awsome message!’ FROM DUAL WHERE GET_LOCK(‘WAITER’, 10);
sess1> …found @message
sess1> SELECT RELEASE_LOCK(‘WAITER’);
@Maciej,
Thanks! Will check the feasibility of this.
Show processlist requires the PROCESS privilege so permission issues are pretty likely. At the minimum you need on grant per user, and users can see what every other user’s messages are. Also you can’t wait on the creation of a get_lock without using a loop and a sleep. If you want to start a listener before a writer that is very difficult, or if you want a listener running all the time, pretty much impossible. MySQL needs a NOTIFY mechanism. I think a single table is the best of a bunch of sub-optimal solutions. It still requires one grant (but… Read more »
Oh, I forgot the one grant you need to give the other users:
grant delete on private.messages
And UPDATE if you want to mark messages as read instead of reading and deleting. This can be used with SELECT FOR UPDATE on the view to serialize the processing of requests, otherwise multiple people might pick them up due to repeatable read.
Of course processlist will work as long as all the message passing happens between clients using the same username, but the other issues are still problematic. Sorry for polluting your comments so much 🙂
Actually, I think DELETE and UPDATE through the view should be possible. I think it follows all the rules of updateable views. Just make sure you add CHECK OPTION CASCADE to the definition.
Then you don’t need any grants.
Care to elaborate more on why you need it ? Maybe there are alternative solutions …
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… Read more »
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… Read more »
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/ ?
@combuilder,
So the Stored Routines lib can dynamically get and set global variables — but that doesn’t help too much because those global variables have some important value, that I should not be tampering with.
[…] 最近看到Shlomi Noach的blog()，分布式系统如何来做并发控制，一般情况，我们往往会使用一个表的一个记录来表示资源是否可用，但有个评论提到用mysql的get_lock函数，以前一直没有关注这个函数，今天小试一把。 […]
@shlomi Did you find a solution to this problem?
I did not pursue this much further, so no…
Well, securty concerns about IPC are even harder, since the data are less defined than the tables.
So I don’t think you’ll be saving anything with avoiding the tables.
But if that must be done your option is to write a daemon plugin that has (any number of) global variables that processes can use to pass data around.
If you couple that with GET_LOCK() it may work.
But this will require super priv to set the global.