Looking for a hack: share data between MySQL sessions

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.

tags:
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

17 Comments to "Looking for a hack: share data between MySQL sessions"

  1. Daniël van Eeden wrote:

    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?

  2. shlomi wrote:

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

  3. Maciej Dobrzanski wrote:

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

  4. shlomi wrote:

    @Maciej,

    Thanks! Will check the feasibility of this.

  5. Justin Swanhart wrote:

    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 only one). However, with a single table there still needs to be a way to prevent sessions from reading each other's data.

    However, with fancy footwork you can implement security that prevents messages from being read by unintended recipients. You can also guarantee message processing order using row level locks and select for update.

    create schema private;

    grant all on private.* to root@'localhost';
    #nobody but root can read from the table
    #anybody can delete (but a trigger will enforce rules)
    
    create table messages (
      message_id              int auto_increment,
      message_from_username  varchar(255) not null,
      message_from_sid            bigint unsigned not null, 
      message_to_username      varchar(255) null,
      message_to_sid bigint default 0 not null,
      message text null,
      message_read tinyint(1) not null default 0,
      message_timestamp timestamp,
      primary key(message_to_sid, message_to_username,id),
      key(message_id)
    );
    
    /* PSEUDO CODE*/
    CREATE BEFORE DELETE TRIGGER ON MESSAGES FOR EACH ROW
    BEGIN
      IF (old.message_to_sid IS NOT NULL 
           and old.message_to_sid != CONNECTION_ID()
          )
      THEN
        SIGNAL('Invalid delete request');
      END IF;
       
      IF( old.message_to_username != USER() )
      THEN
         SIGNAL('Invalid delete request');
      END IF;
    END;;
    
    create  or replace 
    ALGORITHM=MERGE
    SECURITY=DEFINER 
    VIEW get_messages
    as
    select *
      from messages
    #get messages for a specific thread id
    #OR 
    #those that don't have a specific id but are
    #addressed to the current user
    #OR FINALLY 
    #those that don't specify a connection id or
    #destination user (this is a broadcast message)
    where ( message_to_sid = CONNECTION_ID()
                or
                (  message_to_sid = 0
                   and  
                   ( message_to_username = USER()
                     or
                     message_to_username IS NULL
                   )
                )
              )
    and message_read = 0
    ORDER BY message_id;
    
  6. Justin Swanhart wrote:

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

  7. Justin Swanhart wrote:

    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.

  8. Justin Swanhart wrote:

    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 :)

  9. Justin Swanhart wrote:

    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.

  10. Joro wrote:

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

  11. shlomi wrote:

    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.

  12. shlomi wrote:

    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.

  13. Justin Swanhart wrote:

    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.

  14. Maciej Dobrzanski wrote:

    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.

  15. combuilder wrote:

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

  16. shlomi wrote:

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

  17. MySQL get_lock – STRUCTURED DATA UNSTRUCTURED DATA wrote:

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

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org