Looking for a hack: share data between MySQL sessions

20 thoughts on “Looking for a hack: share data between MySQL sessions”

1. 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. @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. 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. @Maciej,

   Thanks! Will check the feasibility of this.

5. 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;
   

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