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