MySQL Bugs: #56357: SELECT .. INTO OUTFILE documentation should list better ways to export to client

Bug #56357	SELECT .. INTO OUTFILE documentation should list better ways to export to client
Submitted:	30 Aug 2010 8:44	Modified:	17 Sep 2010 15:41
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	ALL	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
"The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as mysql -e "SELECT ..." > file_name to generate the file on the client host."

How to repeat:
http://dev.mysql.com/doc/refman/5.1/en/select.html

Suggested fix:
"The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some client host other than the server host, you can pass a network-mapped path to SELECT ... INTO OUTFILE or use mysqldump with the --tab option. In both cases you can reload the data using LOAD DATA INFILE."

This may need some "beefing out", here is what I wrote in another bug about this:

---------
For people who need to move large chunks of data, want to avoid mysqldump and/or want to obtain client side data in a reusable format:

1. To dump, use SELECT INTO OUTFILE to a network-mapped path (i.e. SELECT * FROM <table> INTO OUTFILE "\\\\somehost\\somepath\\somefile.txt";), then re-import using LOAD DATA INFILE. Note the need for double escaping.

2. mysqldump --tab, then import using LOAD DATA INFILE or mysqlimport.
http://dev.mysql.com/doc/refman/5.1/en/mysqldump-delimited-text.html
http://dev.mysql.com/doc/refman/5.1/en/reloading-delimited-text-dumps.html
---------

I'll take this one. 

Set priority/target.

See also BUG#56476.

Correction. mysqldump --tab is not an option. SELECT ... INTO OUTFILE using a network-mapped path is however.

C:\mysql\bin>mysqldump --tab=test -uroot -h192.168.0.241 -P5150 roelt
mysqldump: Got error: 1: Can't create/write to file '/mysql/mysql-5.1.50-linux-i686-glibc23/data/C:/mysql/bin/test/test1.txt' (Errcode: 2) when executing 'SELECT INTO OUTFILE'

Suggested text (not 100% on the section between []):

"The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some client host other than the server host, you can pass a network-mapped path to SELECT ... INTO OUTFILE and afterward reload the data using LOAD DATA INFILE. You can also use mysql -e "SELECT ... FROM ...;", possibly combined with the --silent (or --batch) [and --raw options?]. One disadvantage of this latter option is that this will not output a uniform data stream which means it cannot be re-imported easily via LOAD DATA INFILE."

I've hidden the off-topic comments made in this bug report.

I have to protest!  My comments were not *off-topic*.  I proposed a better and more consistent solution.

Peter,

This is a Documentation bug. It is not a Server bug.

This bug was filed in order to improve the documentation for the software as it actually works now. 

Suggestions for changes in the software's behaviour -- regardless to their quality -- are completely outside the scope of this *Documentation* bug and therefore off-topic.

Thanks!

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.