Re: Exporting to CSV. Error Code: 2013. Lost connection to MySQL server during query

[Fred G]

Hi Monty,

First of all, thanks so much for responding to my question! I am using
MySQLworkbench 5.2.37CE.  I'm pretty sure the issue has to do with
something on the administrator side of things. We managed to get it so that
I can click the icon to export the file, but he's still working on getting
it so that we can write an sql query to do this. So for all practical
purposes on my end, the question is being resolved.

But I don't understand how clicking the icon after running a select query
works for exporting, but the command to "outfile" in a sql query would not
work.

Thanks again

On Tue, Jul 31, 2012 at 5:36 AM, Michael Widenius wrote:

>
> Hi!
>
> > "Fred" == Fred G  writes:
>
> Fred> Thanks Dhaval.  Putting the join condition before INTO outfile
> doesn't seem
> Fred> to work, either.
>
> Fred> When I try to use the same outfile name 'test123.csv' I get Error
> Code:
> Fred> 1086 File 'test123.csv' already exists.  But then when I try to find
> the
> Fred> csv file on my computer, there is a folder with that name, but weird
> files
> Fred> in it, none of which are a csv-- and certainly not in the location
> that I
> Fred> thought it would be (the same directory that the .sql query is in).
>
> It's the mysqld server that is writing the .csv file. This means that
> the path is related to the mysql data directory and not to where your
> .sql file is.
>
> When using select into outfile it's always best to give a full path!
>
>
> Fred>  Additionally, when I try to identify a different path, such as
> 'C:\\' etc,
> Fred> I get an error.  This error is: Error Code: 1. Can't create/write to
> file
> Fred> "C:\test123.csv"(Errocde: 2).
>
> This probably means that you don't have write access to C:\
>
>
> Fred> I tried running the query outputting to a different named .csv file,
> but it
> Fred> is still just "running..." and seems like it was like yesterday
> where after
> Fred> 10 minutes I will get the Error that the MySQL connection was lost.
>
> The reason that your connection is lost are ether:
> - There is timeout in the client you are using
>   (The server never gives a timeout for running queries).
> - The mysqld server died (not likely but possible).
> - Some process in your system is killing quries that runs too long.
>
> One way to quickly check that things are working are by adding LIMIT 1
> to the query.
>
> Fred> Does anyone have an idea of what is going on?
>
> >>> The query without exporting the file works fine, in about 12 sec/77
> sec.
> >>> I
> >>> read online how to export MySQL queries into csv's, and I'm not sure
> what
> >>> I
> >>> am doing wrong.  I keep getting the error:
> >>> Error Code: 2013.  Lost connection to MySQL server during query, where
> the
> >>> duration/fetch values are 600.547 sec (~10 minutes).
>
> What is the exact error message?
> Which client are you using to do the query?
>
> It's strange that the query works fine when you are not using select
> into outfile.
>
> What MySQL version are you using
>
> Regards,
> Monty
> Creator of MySQL and MariaDB
>

Re: Exporting to CSV. Error Code: 2013. Lost connection to MySQL server during query

[Michael Widenius]

Hi!

> "Fred" == Fred G  writes:

Fred> Thanks Dhaval.  Putting the join condition before INTO outfile doesn't 
seem
Fred> to work, either.

Fred> When I try to use the same outfile name 'test123.csv' I get Error Code:
Fred> 1086 File 'test123.csv' already exists.  But then when I try to find the
Fred> csv file on my computer, there is a folder with that name, but weird files
Fred> in it, none of which are a csv-- and certainly not in the location that I
Fred> thought it would be (the same directory that the .sql query is in).

It's the mysqld server that is writing the .csv file. This means that
the path is related to the mysql data directory and not to where your
.sql file is.

When using select into outfile it's always best to give a full path!


Fred>  Additionally, when I try to identify a different path, such as 'C:\\' 
etc,
Fred> I get an error.  This error is: Error Code: 1. Can't create/write to file
Fred> "C:\test123.csv"(Errocde: 2).

This probably means that you don't have write access to C:\


Fred> I tried running the query outputting to a different named .csv file, but 
it
Fred> is still just "running..." and seems like it was like yesterday where 
after
Fred> 10 minutes I will get the Error that the MySQL connection was lost.

The reason that your connection is lost are ether:
- There is timeout in the client you are using
  (The server never gives a timeout for running queries).
- The mysqld server died (not likely but possible).
- Some process in your system is killing quries that runs too long.

One way to quickly check that things are working are by adding LIMIT 1
to the query.

Fred> Does anyone have an idea of what is going on?

>>> The query without exporting the file works fine, in about 12 sec/77 sec.
>>> I
>>> read online how to export MySQL queries into csv's, and I'm not sure what
>>> I
>>> am doing wrong.  I keep getting the error:
>>> Error Code: 2013.  Lost connection to MySQL server during query, where the
>>> duration/fetch values are 600.547 sec (~10 minutes).

What is the exact error message?
Which client are you using to do the query?

It's strange that the query works fine when you are not using select
into outfile.

What MySQL version are you using

Regards,
Monty
Creator of MySQL and MariaDB

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Exporting to CSV. Error Code: 2013. Lost connection to MySQL server during query

[Fred G]

Thanks!

On Thu, Jul 26, 2012 at 12:05 AM,  wrote:

>  2012/07/26 06:52 +0530, Dhaval Jaiswal 
> SELECT * FROM test INTO OUTFILE '/home/test.csv' FIELDS TERMINATED BY ','
> ENCLOSED BY '"' LINES TERMINATED BY '\n'
>
> as above give your join condition before INTO OUTFILE.
> 
> Right: MySQL server writes into some directory where it is, not where
> MySQL client is. If less than a full path name is given, almost certainly
> the server will attempt to write into a directory to which it has no
> permission, and almost certainly also not into one that you want it to
> write into. If server and client run on separate machines with separate
> disks, there is no means through OUTFILE of there setting the output where
> the client is, only through client s standard output, where you get no
> choice of field separator, line separator, or field-quote character (there
> is none), although you can keep or skip the column names (-N for skipping
> them), and suppress the one-character escape character (-r), same as FIELDS
> ESCAPED BY ''. There is no means of skipping "NULL" or "\N" for nulls,
> which is not CSV format.
>
> And if your MySQL is under Windows, be sure to read all instructions about
> entering full pathnames. It is best to avoid the backslash (\), because
> that is a C-escape introduced (along with much other C-stuff) into SQL s
> original PL1.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: Exporting to CSV. Error Code: 2013. Lost connection to MySQL server during query

[hsv]

 2012/07/26 06:52 +0530, Dhaval Jaiswal 
SELECT * FROM test INTO OUTFILE '/home/test.csv' FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' LINES TERMINATED BY '\n'

as above give your join condition before INTO OUTFILE.

Right: MySQL server writes into some directory where it is, not where MySQL 
client is. If less than a full path name is given, almost certainly the server 
will attempt to write into a directory to which it has no permission, and 
almost certainly also not into one that you want it to write into. If server 
and client run on separate machines with separate disks, there is no means 
through OUTFILE of there setting the output where the client is, only through 
client s standard output, where you get no choice of field separator, line 
separator, or field-quote character (there is none), although you can keep or 
skip the column names (-N for skipping them), and suppress the one-character 
escape character (-r), same as FIELDS ESCAPED BY ''. There is no means of 
skipping "NULL" or "\N" for nulls, which is not CSV format.

And if your MySQL is under Windows, be sure to read all instructions about 
entering full pathnames. It is best to avoid the backslash (\), because that is 
a C-escape introduced (along with much other C-stuff) into SQL s original PL1.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Exporting to CSV. Error Code: 2013. Lost connection to MySQL server during query

[Fred G]

Thanks Dhaval.  Putting the join condition before INTO outfile doesn't seem
to work, either.

When I try to use the same outfile name 'test123.csv' I get Error Code:
1086 File 'test123.csv' already exists.  But then when I try to find the
csv file on my computer, there is a folder with that name, but weird files
in it, none of which are a csv-- and certainly not in the location that I
thought it would be (the same directory that the .sql query is in).
 Additionally, when I try to identify a different path, such as 'C:\\' etc,
I get an error.  This error is: Error Code: 1. Can't create/write to file
"C:\test123.csv"(Errocde: 2).

I tried running the query outputting to a different named .csv file, but it
is still just "running..." and seems like it was like yesterday where after
10 minutes I will get the Error that the MySQL connection was lost.

Does anyone have an idea of what is going on?

On Wed, Jul 25, 2012 at 9:22 PM, Dhaval Jaiswal wrote:

> SELECT * FROM test INTO OUTFILE '/home/test.csv' FIELDS TERMINATED BY ','
> ENCLOSED BY '"' LINES TERMINATED BY '\n'
>
> as above give your join condition before INTO OUTFILE.
>
>
>
> On Thu, Jul 26, 2012 at 1:32 AM, Fred G  wrote:
>
>> Hi--
>>
>> I'm trying to do the following:
>> SELECT db.emp.emp_fname, db.emp.emp_fname, db.sale.sale_date,
>> db.sale.sale_no, db.sale.sale_total_amt
>> into outfile 'test123.csv'
>> FIELDS terminated by ','
>> FROM db.emp
>> LEFT OUTER JOIN db.sale
>> ON db.sale.emp_id = db.emp.emp_id;
>>
>> The query without exporting the file works fine, in about 12 sec/77 sec.
>>  I
>> read online how to export MySQL queries into csv's, and I'm not sure what
>> I
>> am doing wrong.  I keep getting the error:
>> Error Code: 2013.  Lost connection to MySQL server during query, where the
>> duration/fetch values are 600.547 sec (~10 minutes).
>>
>> I'm wondering:
>> a) What is going on?
>> b) How do I fix it?
>>
>> Thanks so much!!
>>
>
>
>
> --
>
> [image: Inline image 2] 
>
> *Dhaval* | Database & System
>
> *E:* dhaval.jais...@via.com | *T:* 080 4043 3000 | *M:* +91
> - 8095 397 843
>
> [image: all-icon.jpg] 
>
>

Re: Exporting to CSV. Error Code: 2013. Lost connection to MySQL server during query

[Dhaval Jaiswal]

SELECT * FROM test INTO OUTFILE '/home/test.csv' FIELDS TERMINATED BY ','
ENCLOSED BY '"' LINES TERMINATED BY '\n'

as above give your join condition before INTO OUTFILE.


On Thu, Jul 26, 2012 at 1:32 AM, Fred G  wrote:

> Hi--
>
> I'm trying to do the following:
> SELECT db.emp.emp_fname, db.emp.emp_fname, db.sale.sale_date,
> db.sale.sale_no, db.sale.sale_total_amt
> into outfile 'test123.csv'
> FIELDS terminated by ','
> FROM db.emp
> LEFT OUTER JOIN db.sale
> ON db.sale.emp_id = db.emp.emp_id;
>
> The query without exporting the file works fine, in about 12 sec/77 sec.  I
> read online how to export MySQL queries into csv's, and I'm not sure what I
> am doing wrong.  I keep getting the error:
> Error Code: 2013.  Lost connection to MySQL server during query, where the
> duration/fetch values are 600.547 sec (~10 minutes).
>
> I'm wondering:
> a) What is going on?
> b) How do I fix it?
>
> Thanks so much!!
>



-- 

[image: Inline image 2] 

*Dhaval* | Database & System

*E:* dhaval.jais...@via.com | *T:* 080 4043 3000 | *M:* +91
- 8095 397 843

[image: all-icon.jpg] 

Exporting to CSV. Error Code: 2013. Lost connection to MySQL server during query

[Fred G]

Hi--

I'm trying to do the following:
SELECT db.emp.emp_fname, db.emp.emp_fname, db.sale.sale_date,
db.sale.sale_no, db.sale.sale_total_amt
into outfile 'test123.csv'
FIELDS terminated by ','
FROM db.emp
LEFT OUTER JOIN db.sale
ON db.sale.emp_id = db.emp.emp_id;

The query without exporting the file works fine, in about 12 sec/77 sec.  I
read online how to export MySQL queries into csv's, and I'm not sure what I
am doing wrong.  I keep getting the error:
Error Code: 2013.  Lost connection to MySQL server during query, where the
duration/fetch values are 600.547 sec (~10 minutes).

I'm wondering:
a) What is going on?
b) How do I fix it?

Thanks so much!!