Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
mysql: select ... into outfile - be a named pipe (fifo)
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Unsupported Software
View previous topic :: View next topic  
Author Message
geki
Advocate
Advocate


Joined: 13 May 2004
Posts: 2125
Location: Germania

PostPosted: Sun Nov 16, 2014 8:15 pm    Post subject: mysql: select ... into outfile - be a named pipe (fifo) Reply with quote

hi all!

I had a need for this feature. therefore I made a small patch from the original bigger patch which even has socket support.

well, just to let you know about this is possible! :o
_________________
improved boost|select libs to build|slotable|python-buildid

please do so ... idiocracy
Back to top
View user's profile Send private message
geki
Advocate
Advocate


Joined: 13 May 2004
Posts: 2125
Location: Germania

PostPosted: Thu Jan 29, 2015 7:43 am    Post subject: Reply with quote

little update to the patch - be nice and non-blocking. (whitespace foo in quote - apply manually to original patch)

Quote:
--- /tmp/mysql_select-into-fifo.diff 2014-11-16 09:16:51.301319127 +0100
+++ /etc/portage/patches/dev-db/mysql/mysql_select-into-fifo.patch 2015-01-04 16:01:25.381335238 +0100
@@ -19,7 +19,7 @@
+ if (my_stat(path, &stat, MYF(0)) && MY_S_ISFIFO(stat.st_mode))
+ {
+ if ((file= mysql_file_open(key_select_to_file,
-+ path, O_WRONLY, MYF(MY_WME))) < 0)
++ path, O_WRONLY | O_NONBLOCK, MYF(MY_WME))) < 0)
+ return file;
+ }
+ else if (!access(path, F_OK))

_________________
improved boost|select libs to build|slotable|python-buildid

please do so ... idiocracy
Back to top
View user's profile Send private message
steveL
Advocate
Advocate


Joined: 13 Sep 2006
Posts: 3436
Location: The Peanut Gallery

PostPosted: Thu Jan 29, 2015 4:17 pm    Post subject: Reply with quote

Nice work, geki.

Non-blocking is only useful when the app expects it; since you're using it, I presume it does. What does mySQL do when it's full; discard or buffer?
Back to top
View user's profile Send private message
geki
Advocate
Advocate


Joined: 13 May 2004
Posts: 2125
Location: Germania

PostPosted: Thu Jan 29, 2015 10:27 pm    Post subject: Reply with quote

I use that with a mysql trigger to notify on table updates.
if that open blocks, for whatever reason, mysql hangs.
having it non-blocking, mysql skips the notification and is therefore failsafe.

by default mysql expects the outfile to be non-existant.
managing the 'mysql: write > kernel notification > process: read > delete' before the next write from mysql trigger is just crazy.
it is not possible to have an outfile with timestamp in filename for triggers. therefore the patch.

I actually do not know how named pipes behave if their buffers are full. I hope the buffer is bigger than I ever need. :o
wrt mysql I hope it simply writes to the pipe without self destruction if open succeeds.

I have one process managing mysql triggers and listening on/managing the named pipe.
I know there are no more triggers than can be handled.

I hope my post is not too confusing. it is late. :P
_________________
improved boost|select libs to build|slotable|python-buildid

please do so ... idiocracy
Back to top
View user's profile Send private message
desultory
Administrator
Administrator


Joined: 04 Nov 2005
Posts: 8144

PostPosted: Sat Jan 31, 2015 4:38 am    Post subject: Reply with quote

geki wrote:
little update to the patch - be nice and non-blocking. (whitespace foo in quote - apply manually to original patch)
Two tips:
  1. Use code tags for code, they preserve formatting and present using a fixed width font (subject to the usual caveats for such things), which is generally preferable for code anyway.
  2. Either way, the formatting is preserved if you quote the post with the patch in it and copy from the resulting form.
Back to top
View user's profile Send private message
geki
Advocate
Advocate


Joined: 13 May 2004
Posts: 2125
Location: Germania

PostPosted: Sat Jan 31, 2015 9:08 am    Post subject: Reply with quote

I just have a hard time reading code tags(light green text on white(creme?) background). therefore I use quote tags.

code, which has more than 2~3 interesting/important lines, should be attached as a file or linked from a pastebin service.
_________________
improved boost|select libs to build|slotable|python-buildid

please do so ... idiocracy
Back to top
View user's profile Send private message
steveL
Advocate
Advocate


Joined: 13 Sep 2006
Posts: 3436
Location: The Peanut Gallery

PostPosted: Sat Jan 31, 2015 9:39 am    Post subject: Reply with quote

geki wrote:
I use that with a mysql trigger to notify on table updates.
if that open blocks, for whatever reason, mysql hangs.
having it non-blocking, mysql skips the notification and is therefore failsafe.

by default mysql expects the outfile to be non-existent.

I agree it does without the patch, but the patched version only opens the fifo (or socket) if they already exist. For a fifo, opening it in write-mode with blocking (the default) does mean you'll hang waiting for a reader; but in non-blocking mode, if there is no reader you get an ENXIO, which I imagine is going to be treated as failure (since it also indicates a non-existent special file.)

Certainly the tests are careful to make the fifo before starting; I just think it'd be odd to give the db an exisiting fifo which you're not about to read from (so you really want it to block, should the vagaries of scheduling mean it runs before you.) Still, it's your setup; you know what works.
Quote:
I actually do not know how named pipes behave if their buffers are full. I hope the buffer is bigger than I ever need. :o
wrt mysql I hope it simply writes to the pipe without self destruction if open succeeds.

With non-blocking mode it's going to get EAGAIN on its writes in that instance (for writes less than PIPE_BUF), instead of the thread being put to sleep waiting for your reader to catch up.

As I said, mySQL is likely written with non-blocking i/o in mind; just going over the ground so we're on the same page.
I take it this is strictly at the admin level, as you're the one starting up mySQL and giving it an OUTFILE in the query. The latter feels a bit of a hole in SQL terms, though I've never used it.
Back to top
View user's profile Send private message
geki
Advocate
Advocate


Joined: 13 May 2004
Posts: 2125
Location: Germania

PostPosted: Tue Feb 03, 2015 7:36 am    Post subject: Reply with quote

steveL wrote:
Certainly the tests are careful to make the fifo before starting; I just think it'd be odd to give the db an exisiting fifo which you're not about to read from (so you really want it to block, should the vagaries of scheduling mean it runs before you.) Still, it's your setup; you know what works.
steveL wrote:
As I said, mySQL is likely written with non-blocking i/o in mind; just going over the ground so we're on the same page.
I take it this is strictly at the admin level, as you're the one starting up mySQL and giving it an OUTFILE in the query. The latter feels a bit of a hole in SQL terms, though I've never used it.
of course, this non-blocking/fifo mode is quite dangerous. for a single user accessing the mysql server, it suffices.
AFAIS the non-blocking mode helps in case of fifo still there but fifo reader being defunct.

I wonder if there are feasable ways to catch other error cases.

the case where there are, for whatever reasons, mysql triggers left active but the fifo is missing - reader process being defunct.
then the first mysql trigger event creates the outfile. the second event hangs the server. :o
_________________
improved boost|select libs to build|slotable|python-buildid

please do so ... idiocracy
Back to top
View user's profile Send private message
steveL
Advocate
Advocate


Joined: 13 Sep 2006
Posts: 3436
Location: The Peanut Gallery

PostPosted: Fri Feb 13, 2015 7:16 am    Post subject: Reply with quote

geki wrote:
AFAIS the non-blocking mode helps in case of fifo still there but fifo reader being defunct.

I wonder if there are feasable ways to catch other error cases.

the case where there are, for whatever reasons, mysql triggers left active but the fifo is missing - reader process being defunct.
then the first mysql trigger event creates the outfile. the second event hangs the server.

Sure there are, though we're talking C; you need to keep reading past EOF. ie treat 0 as meaning "the writer has quit, let's wait for the next one." I'm a bit concerned that mySQL is hanging, but that would mean there's always a reader available (until you kill it.)

Negative return you check for signal and continue in that case, or exit 0 otherwise (in test code: it means the other end errored out, usually, though as ever you have to be aware of other error conditions that might arise.)

I can give you some test code for that to play with if you want; are you ok doing it in C? You could just leave a minimal reader waiting around, if nothing else, so that mySQL doesn't hang.

edit: <desultory> you know... a reader that will eat everything you'd ever care to throw at it is just a dd away...

/o\
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Unsupported Software All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum