Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

unable to select into outfile from mysql stored procedure

Posted on 2008-11-14
10
Medium Priority
?
3,024 Views
Last Modified: 2012-05-05
Hi Im new to mysql and need to know how to write an output file from mysql stored procedure. I basically want to select some data from a table and write the result to an output file on the server. I tried the below procedure which seems to run without error, but I never see the file written to the server. Im using mysql 5.1 on Windows XP pro.


Delimiter $
create procedure output_file (p1 varchar(1))
begin
select col1, col2, col3  
  from table_t1
where col1= p1
  into outfile 'outfile1.txt';
END$
delimiter ;

When I call the above procedure it runs without error, but no file is generated.
However if I run the procedure a second time it gives an error:
ERROR 1086 (HY000): File 'outfile1.txt' already exists.

I tried running the select into file from the command line outside of the procedure and the same thing happens.

0
Comment
Question by:heliger
  • 6
  • 3
10 Comments
 
LVL 26

Expert Comment

by:Umesh
ID: 22965893
That means file has generated...and when you try next time it refuses to overwrite... Pls check on the server for file... also make sure you provide complete path to the out file..
0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 22965933
Yes, give complete path. Also, when you dont many a time file gets written to a default location in MySQL installation folder. make a search of this file on entire computer or the drive in which MySQL is installed. Since you are using windows, there is not much security in terms of file/folder permissions like in unix, linux etc. Hence, I do not understand why it is refusing to overwrite. Unless there is a setting that all newly created/saved files should be marked by default as read only which again is not the default behaviour of windows whether you are logged in as a normal local user/domain user/local administrator. Hence, once if you find the file check whether it is created as read only.
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22965939
Also... the syntax should be something.. if you don't provide the outfile path... then by default it will be created on server's data directory and into the database name directory...

Say you have this procedure in test schema.. then when you call without complete path to outfile... file will be created in mysql/data/test/outfile1.txt

SELECT col1,col2,col2 INTO OUTFILE 'outfile1.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 

Author Comment

by:heliger
ID: 22965995
I do not see the file created anywhere on the system. I looked in all the possible folders and I also searched by name and time created. I cannot find the file anywhere on the computer. Below shows eaxctly what happens:

mysql> select * from table_t1;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| A    | B    | C    |
| A    | B    | C    |
| A    | B    | C    |
| A    | B    | C    |
| A    | B    | C    |
| A    | B    | C    |
+------+------+------+
6 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> Delimiter $
mysql> create procedure output_file (p1 varchar(1))
    -> begin
    -> select col1, col2, col3
    ->   from table_t1
    -> where col1= p1
    ->   into outfile 'c:\outfile1.txt';
    -> END$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call output_file ('A');
Query OK, 0 rows affected (0.03 sec)

mysql> call output_file ('A');
ERROR 1086 (HY000): File 'c:outfile1.txt' already exists
mysql>

C:\>dir c:\out*
 Volume in drive C has no label.
 Volume Serial Number is C8AD-1A67

 Directory of c:\

File Not Found

C:\>dir c:\out*.*
 Volume in drive C has no label.
 Volume Serial Number is C8AD-1A67

 Directory of c:\

File Not Found

C:\>


As you can see the file is not there. I also searched the entire computer and the outfile1.txt  is not found. I've tried various directories and including the default but nothing seems to work.
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22966035
Is MySQL running on local host or remote? file should be created on the machine where MySQL is running...

I tried below code.. and just works fine for me.. I can see file created on C:/
Delimiter $
create procedure output_file (p1 varchar(100))
begin
 
SELECT col1,col2,col2 INTO OUTFILE 'c:/outfile1.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
   from table_t1
  where col1= p1;
 
END$
delimiter ;

Open in new window

0
 

Author Comment

by:heliger
ID: 22966100
Everything is running on a single computer Windows XP pro SP3. No other computers are involved. Also I am using version 5.1 community server that I just downloaded and installed. It seem very odd that this does not work.

From the command line I tried doing just this simple test:

mysql> select * from table_t1 into outfile 'c:\simple.out';
Query OK, 6 rows affected (0.05 sec)

However, there is no c:\simple.out. Or any other new file crated anywhere on the computer.
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22966113
Same configuration here at my end..it works.. bcoz  I'm doing what I have written in comments.. Pls don't use '\' on windows... you are just ignoring my comments..

Also can u post below command outputs.. file should be created on server so you need to have FILE privileges in order to do this.... try with the root user and see how things goes..

Change the user name in grant statement..
mysql>show grants for userName@localhost;
mysql>show grants for root@localhost;
 
 
mysql>select * from table_t1 into outfile 'c:/simple.out';
 
or
 
mysql>SELECT * INTO OUTFILE 'c:/simple.out'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
   from table_t1;
 
or
 
mysql>SELECT * INTO OUTFILE 'c:\\simple.out'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
   from table_t1;

Open in new window

0
 
LVL 26

Accepted Solution

by:
Umesh earned 2000 total points
ID: 22966189
Any success??? Pls update me
0
 

Author Comment

by:heliger
ID: 22968073
Thanks Ushastry:

Your comment Pls don't use '\' on windows...  solved my problem. All along I was setting the file up as c:\filename instead of c:/filename.   I dont know why on windows you should use slash instead of back slash (as normal in windows environment), but as long as it works I wont worry about it. Also, I cant explain why it didnt work when I just used file name without c:/ , but again I can write to a file now and that is all that counts.

Thanks
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22968095
Anytime...

Thanks GOD finally it worked!

As I commented in one of my post, when you don't specify the path to the out file, it actually creates the file in the schema directory.. say your table ''xyz' is in schema 'test'.. and you do outfile without specifying the complete path.. then it should be created in <MySQL_DATA_DIR>/test/outfile*

About the '/' and '\' .. I have suffered a lot during my initial period.. no matter you work on Windows/Unix..always use forward slash '/'
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question