MySQL Bugs: #44835: INTO OUTFILE should support writing to named pipes

Bug #44835	INTO OUTFILE should support writing to named pipes
Submitted:	12 May 2009 21:37	Modified:	13 May 2009 6:24
Reporter:	Justin Swanhart	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:	ANY	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
There should be an option which allows INTO OUTFILE to write into a named pipe.  Currently if a file exists the database will return an error, even if the file is a named pipe and the pipe is writable by the database.

This feature could be protected by a my.cnf parameter.

This will allow me to use LOAD DATA LOCAL INFILE in combination with SELECT INTO OUTFILE to quickly load multiple partitions and quickly load it across the network, without having to use an intermediate file which wastes space and I/O.

How to repeat:
mkfifo /tmp/myfifo
mysql -e 'select * from mysql.user into outfile "/tmp/myfifo"'

Suggested fix:
Add a my.cnf parameter which allows INTO OUTFILE to send its contents into a named pipe.

Thank you for the feature request.

Why not just add a LOCAL option to SELECT INTO OUTFILE just like LOAD DATA has already.  This is a very old request actually.

The LOCAL option would be nice to have, but I also want a named pipe. The LOCAL option is more overhead when the client is on the same server as mysqld. I want support for a named pipe to read the data, process it and then send it elsewhere without ever having it on disk.

I'd like to add a vote for this feature (an option to support named pipes as a target of INTO OUTFILE).  Lack of this feature makes it very difficult to move huge data sets, and can be hugely consumptive of disk resources.  We spend a fair amount of time working around this.

I would like to add a +1 vote for this as well. Not sure if anything recently has changed to support this.  Moving large amounts of data around is time consuming...especially on EC2/EBS

For security reasons i.e. (don't destroy "/etc/passwd")  would create a specific directory in my.cnf for use R/W to OUTFILE.

Would be fantastic to use:
mkfifo /tmp/FIFO
gzip < /tmp/FIFO > table.gz&
mysql ..... INTO OUTFILE "/tmp/FIFO" ....

I have a large amount of data to export and can't compress until the output is complete, using a named pipe will allow the data to be split into chunks and compressed on the fly so there will be no space issues.

I understand concerns about overwriting system files but these aren't FIFO files and you can restrict them to specific directories or declare them in the MySQL configuration.

A definite +1 from me