[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MYSQL Can't create/write to file ...

Posted on 2012-09-21
30
Medium Priority
?
1,863 Views
Last Modified: 2014-01-09
Hello. I'm new here. Having never known anything about SQL until just a few days ago I have been doing some heavy research on ways to build a text report of inventory for my online store. This file is used for things like google shopping. I've figured out how to get text files "into" my database in a new table with no problems and I update my inventory regularly that way, but I need to export a new report often throughout the day. I made a query that gives me the following error when I try to put the file where I actually want it to be:

#1 - Can't create/write to file '/home/somedude/public_html/somefolder/somefile.txt' (Errcode: 13)

This is the query I am running:
SELECT `id`, `title`, `description`, `condition`, `price`, `availability`, `link`, `image_link`, `gtin`, `mpn`, `brand`, `google_product_category`, `shipping`, `shipping_weight`, `sale_price`, `sale_price_effective_date`, `additional_image_link`, `product_type` 
FROM slickfeed
INTO OUTFILE '/home/somedude/public_html/somefolder/somefile.txt'
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n';

Open in new window


Now, when I run it with the following query (notice the location change) it works just fine but I need it to be in the above location so that it can be downloaded regularly by others.

SELECT `id`, `title`, `description`, `condition`, `price`, `availability`, `link`, `image_link`, `gtin`, `mpn`, `brand`, `google_product_category`, `shipping`, `shipping_weight`, `sale_price`, `sale_price_effective_date`, `additional_image_link`, `product_type` 
FROM slickfeed
INTO OUTFILE '/tmp/somefile.txt'
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n';

Open in new window


Any help you guys and gals can offer would be greatly appreciated. I've been pulling my hair out trying to get this solved.

If it matters or helps, I have a VPS with hostgator.

Also, I ultimately want this to be coded into a php script so that I can run it from anywhere just like I do with my inventory update. If we can't "fix" the problem, I'm open to any work-arounds such as somehow to copy the file to the specified location after creating it in the "wrong" directory.
0
Comment
Question by:lanbosarmory
  • 12
  • 12
  • 2
  • +2
28 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 38424077
This is a permissions problem.  You need to make sure /home/somedude/public_html/somefolder/ is writable by the mysql user (or whatever user is running the mysql daemon)
0
 

Author Comment

by:lanbosarmory
ID: 38424078
That was my first guess since it would work in the tmp directory but I don't know how to go about that and the hostgator tech couldn't help me.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38424081
Assuming there's a mysql group, you can do a:

sudo chgrp mysql /home/somedude/public_html/somefolder/
sudo chmod g+w /home/somedude/public_html/somefolder/

That changes the group ownership of the folder to the mysql group, then allows all members of the group (notably the mysql user) to write on the folder
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:lanbosarmory
ID: 38424085
I'm also new to Unix so bear with me. I don't know if there is a mysql group but I input the commands you posted after logging in with root access and then tried the query again and got the same error.
0
 
LVL 81

Expert Comment

by:arnold
ID: 38424089
You would need to use the extended access control list using getfacl and setfacl
To extend rights.
 http://linuxcommand.org/man_pages/setfacl1.html
0
 

Author Comment

by:lanbosarmory
ID: 38424114
Says command not found when I try to use setfacl.
0
 
LVL 81

Expert Comment

by:arnold
ID: 38424172
Which Linux do you have?
uname -a
more /etc/*release
Depending on you. Linux version, Ubuntu/Debian apt-get install acl
Centos/redhat yum install acl
0
 

Author Comment

by:lanbosarmory
ID: 38424215
So it's all showing as rwx on the folder. Isn't that what it should be?
0
 
LVL 81

Expert Comment

by:arnold
ID: 38424409
Permissions are made up of three groups
rwx owner/user
       rwx  group
              rwx world/everyone else.

Using the acl commands, you can add additional rights for users groups.
0
 
LVL 2

Expert Comment

by:brijesh_chauhan
ID: 38424502
Hi,

This Is The Most Common Error You Often See While Performing Such Operations In MySQL.

Its The Error Of File Permissons. In Linux To Read Or Write To Any File You Must have The Desired Permisson To Do That.

When You Write Any Data From MySQL To Your Local File System It Must Have The User And Group Permission Of MySQL Itself.

In Your Case You Don't Have It That's Why Its Throwing You An Error.

There Are Two Ways To Resolve This Problem.

1. You Can Write Anything Into The /tmp Directory. You Don't Need Any Special Permisson For That. So Simply Write Into The /tmp Directory First And Copy That File To The Desired Location Afterwards.

You Can Simply Copy A File From Tmp To Your Directory As Below.
Suppose That You Have Written Your O/P In /tmp/a.txt And You Want To Copy
It To '/home/somedude/public_html/' Then Do Following.

cp /tmp/a.txt /home/somedude/public_html/a.txt

2. Second Option Is Change The Ownership Of The Location Where You Want To Store Your O/P File And Change It To mysql.

You Can Do That As Follows.

chown -R mysql:mysql /home/somedude/public_html/

Now Anything Written Under the public_html Directory Will Have The mysql Permisson.
So You Can Simply Write In That Directory.

Hope This Would Help.
0
 
LVL 81

Accepted Solution

by:
arnold earned 2000 total points
ID: 38424728
The other option might be to run the query without the into output line

echo "select query " | MySQL -u username --password=" password" > filename.data
0
 
LVL 27

Expert Comment

by:tliotta
ID: 38427550
Is "somedude" always the same? Or will that change as different users run the process?

Tom
0
 

Author Comment

by:lanbosarmory
ID: 38434159
@arnold:
Okay, so that works perfectly from the command line and I've figured out how to make the headers like I want them to be in the select query. However, how do I have that run from a PHP file? Currently the way I've been doing it is saving the queries as a script file and then using php to invoke those queries. But I have to admit I "cheated" and found someone else's php file and just edited it for my own file and database. If I can run that via PHP somehow, that will solve all my problems and do exactly what I want it to do.

@tliotta
"somedude" (while not actaully my username) will always be the same, yes.

@everyone
I've tried all the permissions stuff to the best of my abilities and can't get it to allow the outfile to write anywhere else. But if I can run the query arnold suggested, that will work perfectly.
0
 
LVL 81

Expert Comment

by:arnold
ID: 38437259
You can use the php script to execute the query excluding the into filename option. The results are then accessible within the php code.

Using the results within the php and the open file function fopen http://php.net/manual/en/function.fopen.php
Into which you will then format and output the results as you see fit.
The issue is that the limitation of file creation in various locations will be based on the credentials under which the php script runs.
0
 

Author Comment

by:lanbosarmory
ID: 38437314
I know even less about PHP, so is there no way to directly run this command in PHP?

echo "SELECT * FROM slickfeed_txt" | mysql -u user --password="password" dbase > /home/username/www/dump.txt

Open in new window


Because it works perfectly exactly the way I want it to when I run it from the command line via SSH.

Basically I want to be able to just navigate to the php page from any computer anywhere to run it and that's it. As it stands now I'm running my old php page and it builds the files properly and then I login via SSH and move them where needed.
0
 
LVL 81

Expert Comment

by:arnold
ID: 38437715
If you are unfamiliar with php, why use it?
You can use the command in a shell script
#!/bin/bash

echo "select....." | mysql -username --password=password > file location

The script can be modular where it could get the select from the files you mentioned as well as when the file could be save

I.e. ./querymysql.sh script_number username_folder_where_the_result_should_be_saved
0
 

Author Comment

by:lanbosarmory
ID: 38437777
Arnold, thank you, but I know even less about shell scripts. Can that be run from a web browser, for example from my iPhone? That's why I had been using PHP since I knew I could just navigate to it in any web browser and it would do what I needed.

EDIT: also, when creating the .sh file when I run it is says permission denied.

EDIT2: did more research and realized i needed to type "sh file.sh" still not sure how I can do that from a web browser though.
0
 
LVL 81

Expert Comment

by:arnold
ID: 38437861
Web server based apps, are restricted such that they have even less access on the system than a MySQL user.
Configuring a web server in such a way that would allow a direct access to user folders would open up your setup and make it insecure.

Depending on the responsiveness you need from the point to enter a command using a php page to when the results are in the user's folder.
I.e. one updates requests in the MySQL database.
On the system, you would have a process that will monitor changes in the request table of MySQL and will act based on validating the request (has a defined set of requests that are valid, anything else will be ignored/email notification sent) will perform the requested task.
0
 

Author Comment

by:lanbosarmory
ID: 38437962
I really appreciate the help and don't take this the wrong way but I think what you're not understanding is that I'm not understanding you, LOL.

I understand not wanting to create security risks and if that's inevitable then I will abandon this and continue the process I am currently using.

However, what I'm looking to do is create these text files from my database in the specified location. I don't care how it gets there I would just like to be able to do it from anywhere in the world on any type of device from a web browser. At present I can create the files by simply visiting the appropriate php page, but since it won't let me write them to the proper location I connect via SSH and move them. Your method above allows me to create them in the correct location but I don't know how to have that command run via php. If this is hopeless that is fine, just tell me so I don't keep trying. Thanks again for your help.
0
 
LVL 81

Expert Comment

by:arnold
ID: 38438620
It is possible.
Any reason you do not let the users into whose directory you want to export a report get the report using the web interface?
0
 

Author Comment

by:lanbosarmory
ID: 38439516
It's google, bing, and slickguns. They need a URL for the specific file to automatically download.
0
 
LVL 81

Expert Comment

by:arnold
ID: 38439543
I have no idea what you are trying to say.
0
 

Author Comment

by:lanbosarmory
ID: 38439557
I'll put it this way: the file must be accessible as-is via http. It must be accessible with no user input from a standard URL.
0
 
LVL 81

Expert Comment

by:arnold
ID: 38439574
You could have your php function to output data to the browser
Http://yourdomain.com/yourphpscript.php?selectscriptnumber

Within php $_SERVER{'QUERY_STRING'} will be set to selectscriptnumber.
Using this value you would change the query and the results can be output to the browser for display or if you prefer you can define the content type such that the set will be prompted to download the data.
This is controlled via content-type designation of the data being sent back to the browser.

All you would need is to add script files to match the queries you want the users to view/access.
0
 

Author Comment

by:lanbosarmory
ID: 38439580
The URL has to point directly to a tab delimited text file, for example:

http://www.lanbosarmory.com/tabdelimited.txt

Otherwise it won't work and they won't be able to download it.
0
 
LVL 81

Expert Comment

by:arnold
ID: 38439592
That is not true.
You can as part of running the script issue a directive to the browser that a file is coming
http://richardlynch.blogspot.com/2006/06/php-downloads-content-disposition.html

I'd like to suggest you look at the available options of php.
0
 

Author Comment

by:lanbosarmory
ID: 38439597
No, my bad, I was not clear. What I mean is google, bing, and slickguns require it to be a URL directly to a tab delimited text file.
0
 
LVL 81

Assisted Solution

by:arnold
arnold earned 2000 total points
ID: 38439622
The URL can output content-type: text/plain data.

If you insist, you can configure a directory within your webserver with write permission by the apache user. Chown apache:apache /var/www/HTML/data_folder
Http://yourdomain.com/data_folder/filedata.txt

The issue you have to be aware of if you are using Selinux, is to configure this directory to auto approve access to the created files. Etc.
Php select query and use fopen to create the file in /var/www/HTML/data_folder/
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
Suggested Courses
Course of the Month19 days, 1 hour left to enroll

834 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