Skip to content

# Looking for a hack: share data between MySQL sessions

MySQL Hack January 28, 2013

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
20
Leave a Reply

18 Comment threads
2 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
8 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Daniël van Eeden

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?

Author
shlomi

@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?

Guest
Maciej Dobrzanski

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’);

Author
shlomi

@Maciej,

Thanks! Will check the feasibility of this.

Guest
Justin Swanhart

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 »

Guest
Justin Swanhart

Oh, I forgot the one grant you need to give the other users:
grant delete on private.messages

Guest
Justin Swanhart

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.

Guest
Justin Swanhart

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 🙂

Guest
Justin Swanhart

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.

Guest
Joro

Care to elaborate more on why you need it ? Maybe there are alternative solutions …

Author
shlomi

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 »

Author
shlomi

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.

Guest
Justin Swanhart

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 »

Guest
Maciej Dobrzanski

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.

Guest
combuilder

have you tried the global using this: http://sourceforge.net/projects/mysql-sr-lib/ ?

Author
shlomi

@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.

MySQL get_lock – STRUCTURED DATA UNSTRUCTURED DATA

[…] 最近看到Shlomi Noach的blog()，分布式系统如何来做并发控制，一般情况，我们往往会使用一个表的一个记录来表示资源是否可用，但有个评论提到用mysql的get_lock函数，以前一直没有关注这个函数，今天小试一把。 […]

Guest
Kushal

@shlomi Did you find a solution to this problem?

Author
shlomi

I did not pursue this much further, so no…

Guest
Joro

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.

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.