Lost connection to MySQL server during query

Question

Here is a part of my query

SELECT * INTO OUTFILE 'Target_File_Name' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '' FROM (SELECT 'Start time','Calling no','Called no','Calltype','Cust. name','Total Dur.','Call stat' UNION (SELECT CDR_StartDateTime,CDR_A_Number ,(CASE CDR_B_Number WHEN 0 THEN '' ELSE (CASE CDR_CallTypeWHEN 1 THEN CONCAT('011',CDR_B_Number) ELSE CONCAT('1',CDR_B_Number) END) END ),CDR_CallType_Name ,CDR_CustomerName ,(CASE WHEN CDR_TotalDuration>0 THEN ROUND(CDR_TotalDuration/60,1) ELSE 0.00 END),CDR_CallStat  FROM   tlb_cdrdetails_4   FORCE INDEX(Index_CallStatus) WHERE CDR_StartDateTime >= CAST('2012-04-01 00:00' AS DATETIME) AND CDR_StartDateTime <= CAST('2012-04-30 23:59:59' AS DATETIME) AND CDR_CallStat IN (42, 52)  AND CDR_CustomerID IN (123,1000,1001) AND CDR_CarrierID IN (114,1039,115))) tbl

it fetches some 80 lac records.Its causing Lost connection to Mysql during query error.Is the network speed an issue while downloading huge amount of data

Answer 1

8 million rows? - network speed will determine how long it takes to download the rows, but as to why you are losing connection, you'd have to dig deeper to see if you have a timeout setting?

Answer 2

As you are facing issue in generating the CSV.

SELECT * INTO OUTFILE 'D:/CrossStatV3.3/Download/TempFiles//Call-Details-Report-04-01-2012_06-01-12-09-23-22.csv'........

Once i also faced the same issue i was generating a csv whose size was approximately supposed to 17GB.I found a work around for that

why not use mysqldump which is more powerful and fast.

for generating csv using mysqldump you can do like this

mysqldump -uuser -ppassword --tab="Path where we need to create the CSV" --fields-terminated-by="\t"  --fields-enclosed-by="'" --lines-terminated-by="\n" DB_Name TableName 

this will create the TableName.txt file and TableName.sql files at the location as you specified with --tab option where TableName.txt is your CSV you can rename it as TableName.csv.

Note : use the (--tab="PATH") path where the mysql has write permissions.

for various other options of mysqldump.

please see..

mysqldump --help