Outfile syntax and out of memory
Hi, I need to extract some data to a textfile from a big database. If I try to do like this: mysql queryfile.sql outfile.txt outfile.txt it looks something like: OrderID, Quant, OrdrDate, code1, code2... 10021, 12, 20060412, 23, 95... 10022, 5, 20060412, , 75... But, I never get a complete file. I get a out of memory error after a hour or 2!! If I instead insert the following code in queryfile.sql: INTO OUTFILE 'outfile.txt' Now my outfile.txt don't get the first row with the column names, and any NULL values are exported as \N. This is a big problem, cause the import function that exist where I send the data only accept the format I get using mysql queryfile.sql outfile.txt. Any help??! Ideas?? Can I in any way format my output to print the column names and print NULL values as 'nothing'?? Regards, /Johan Lundqvist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outfile syntax and out of memory
Hi, Increase max_allowed packet to 1.5 gb and then try to import your data . eg ; In mysql prompt run the file as *use database \. /tmp/filename.txt * Johan Lundqvist wrote: Hi, I need to extract some data to a textfile from a big database. If I try to do like this: mysql queryfile.sql outfile.txt outfile.txt it looks something like: OrderID, Quant, OrdrDate, code1, code2... 10021, 12, 20060412, 23, 95... 10022, 5, 20060412, , 75... But, I never get a complete file. I get a out of memory error after a hour or 2!! If I instead insert the following code in queryfile.sql: INTO OUTFILE 'outfile.txt' Now my outfile.txt don't get the first row with the column names, and any NULL values are exported as \N. This is a big problem, cause the import function that exist where I send the data only accept the format I get using mysql queryfile.sql outfile.txt. Any help??! Ideas?? Can I in any way format my output to print the column names and print NULL values as 'nothing'?? Regards, /Johan Lundqvist -- Thanks Regards, Dilipkumar DBA Support ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch India vs. England LIVE, Hot videos and more only on Sify Max! Click Here. www.sifymax.com Get to see what's happening in your favourite City on Bangalore Live! www.bangalorelive.in
RE: Outfile syntax and out of memory
Johan, have you thought about doing this incrementally? ie - 25% at a time x 4 to show something for NULL, you can use the COALESCE function. ie - COALESCE(column,'nothing') -- George Law VoIP Network Developer 864-678-3161 [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] -Original Message- From: Johan Lundqvist [mailto:[EMAIL PROTECTED] Sent: Monday, May 08, 2006 10:16 AM To: mysql@lists.mysql.com Subject: Outfile syntax and out of memory Hi, I need to extract some data to a textfile from a big database. If I try to do like this: mysql queryfile.sql outfile.txt outfile.txt it looks something like: OrderID, Quant, OrdrDate, code1, code2... 10021, 12, 20060412, 23, 95... 10022, 5, 20060412, , 75... But, I never get a complete file. I get a out of memory error after a hour or 2!! If I instead insert the following code in queryfile.sql: INTO OUTFILE 'outfile.txt' Now my outfile.txt don't get the first row with the column names, and any NULL values are exported as \N. This is a big problem, cause the import function that exist where I send the data only accept the format I get using mysql queryfile.sql outfile.txt. Any help??! Ideas?? Can I in any way format my output to print the column names and print NULL values as 'nothing'?? Regards, /Johan Lundqvist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outfile syntax and out of memory
Hi George, To do it incrementally is not really an option, since i have to run it as a script during a short time-frame every night, and theres simply not time to process the files. The outfile is about 2 - 10 Gb every time. The tables have about 100 - 180 columns, and to do a COALESCE would create humongous sql-statements. I might also have wrote it a bit ambigous in my question; I don't want the word nothing, I really want the field to contain nothing - as in ''. Regards, /Johan - Ua, Sweden George Law wrote: Johan, have you thought about doing this incrementally? ie - 25% at a time x 4 to show something for NULL, you can use the COALESCE function. ie - COALESCE(column,'nothing') -- George Law VoIP Network Developer 864-678-3161 [EMAIL PROTECTED] MSN: [EMAIL PROTECTED] -Original Message- From: Johan Lundqvist [mailto:[EMAIL PROTECTED] Sent: Monday, May 08, 2006 10:16 AM To: mysql@lists.mysql.com Subject: Outfile syntax and out of memory Hi, I need to extract some data to a textfile from a big database. If I try to do like this: mysql queryfile.sql outfile.txt outfile.txt it looks something like: OrderID, Quant, OrdrDate, code1, code2... 10021, 12, 20060412, 23, 95... 10022, 5, 20060412, , 75... But, I never get a complete file. I get a out of memory error after a hour or 2!! If I instead insert the following code in queryfile.sql: INTO OUTFILE 'outfile.txt' Now my outfile.txt don't get the first row with the column names, and any NULL values are exported as \N. This is a big problem, cause the import function that exist where I send the data only accept the format I get using mysql queryfile.sql outfile.txt. Any help??! Ideas?? Can I in any way format my output to print the column names and print NULL values as 'nothing'?? Regards, /Johan Lundqvist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outfile syntax and out of memory
Hi, Where should I increase max_allowed packet?? I get a error from Windows (yes, I know... it's running on a M$-os, not my bad - not my desicion). The results is about 2 - 10 Gb of data. Regards, /Johan Dilipkumar wrote: Hi, Increase max_allowed packet to 1.5 gb and then try to import your data . eg ; In mysql prompt run the file as *use database \. /tmp/filename.txt * Johan Lundqvist wrote: Hi, I need to extract some data to a textfile from a big database. If I try to do like this: mysql queryfile.sql outfile.txt outfile.txt it looks something like: OrderID, Quant, OrdrDate, code1, code2... 10021, 12, 20060412, 23, 95... 10022, 5, 20060412, , 75... But, I never get a complete file. I get a out of memory error after a hour or 2!! If I instead insert the following code in queryfile.sql: INTO OUTFILE 'outfile.txt' Now my outfile.txt don't get the first row with the column names, and any NULL values are exported as \N. This is a big problem, cause the import function that exist where I send the data only accept the format I get using mysql queryfile.sql outfile.txt. Any help??! Ideas?? Can I in any way format my output to print the column names and print NULL values as 'nothing'?? Regards, /Johan Lundqvist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outfile syntax and out of memory
Hi Johan, I need to extract some data to a textfile from a big database. If I try to do like this: mysql queryfile.sql outfile.txt outfile.txt it looks something like: OrderID, Quant, OrdrDate, code1, code2... 10021, 12, 20060412, 23, 95... 10022, 5, 20060412, , 75... But, I never get a complete file. I get a out of memory error after a hour or 2!! This is because the mysql client program is trying to read the entire result into its own memory. Try adding the '-q' option to mysql: mysql -q queryfile.sql outfile.txt Regards, Jeremy -- Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
