| View previous topic :: View next topic |
| Author |
Message |
geki
Advocate
Joined: 13 May 2004
Posts: 2387
Location: Germania
|
 Posted: Sun Nov 16, 2014 8:15 pm Post subject: mysql: select ... into outfile - be a named pipe (fifo) |
 |
|
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! 
_________________
hear hear |
|
| Back to top |
|
 |
geki
Advocate
Joined: 13 May 2004
Posts: 2387
Location: Germania
|
| Posted: Thu Jan 29, 2015 7:43 am Post subject: |
|
|
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)) |
_________________
hear hear |
|
| Back to top |
|
|
steveL
Watchman
Joined: 13 Sep 2006
Posts: 5153
Location: The Peanut Gallery
|
| Posted: Thu Jan 29, 2015 4:17 pm Post subject: |
|
|
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 |
|
|
geki
Advocate
Joined: 13 May 2004
Posts: 2387
Location: Germania
|
| Posted: Thu Jan 29, 2015 10:27 pm Post subject: |
|
|
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.
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. 
_________________
hear hear |
|
| Back to top |
|
|
desultory
Bodhisattva
Joined: 04 Nov 2005
Posts: 9410
|
| Posted: Sat Jan 31, 2015 4:38 am Post subject: |
|
|
| geki wrote: | | little update to the patch - be nice and non-blocking. (whitespace foo in quote - apply manually to original patch) |
Two tips:
- 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.
- 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 |
|
|
geki
Advocate
Joined: 13 May 2004
Posts: 2387
Location: Germania
|
| Posted: Sat Jan 31, 2015 9:08 am Post subject: |
|
|
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.
_________________
hear hear |
|
| Back to top |
|
|
steveL
Watchman
Joined: 13 Sep 2006
Posts: 5153
Location: The Peanut Gallery
|
| Posted: Sat Jan 31, 2015 9:39 am Post subject: |
|
|
| 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 |
|
|
geki
Advocate
Joined: 13 May 2004
Posts: 2387
Location: Germania
|
| Posted: Tue Feb 03, 2015 7:36 am Post subject: |
|
|
| 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.
_________________
hear hear |
|
| Back to top |
|
|
steveL
Watchman
Joined: 13 Sep 2006
Posts: 5153
Location: The Peanut Gallery
|
| Posted: Fri Feb 13, 2015 7:16 am Post subject: |
|
|
| 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 |
|
|
|
|
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
|
|
Unsupported Software
