lanbosarmory
asked on
MYSQL Can't create/write to file ...
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:
This is the query I am running:
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.
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.
#1 - Can't create/write to file '/home/somedude/public_html/somefold er/somefil e.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';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';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.
johanntagle
This is a permissions problem. You need to make sure /home/somedude/public_html
ASKER
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.
Assuming there's a mysql group, you can do a:
sudo chgrp mysql /home/somedude/public_html
sudo chmod g+w /home/somedude/public_html
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
sudo chgrp mysql /home/somedude/public_html
sudo chmod g+w /home/somedude/public_html
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
ASKER
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.
You would need to use the extended access control list using getfacl and setfacl
To extend rights.
http://linuxcommand.org/man_pages/setfacl1.html
To extend rights.
http://linuxcommand.org/man_pages/setfacl1.html
ASKER
Says command not found when I try to use setfacl.
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
uname -a
more /etc/*release
Depending on you. Linux version, Ubuntu/Debian apt-get install acl
Centos/redhat yum install acl
ASKER
So it's all showing as rwx on the folder. Isn't that what it should be?
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.
rwx owner/user
rwx group
rwx world/everyone else.
Using the acl commands, you can add additional rights for users groups.
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_htm
cp /tmp/a.txt /home/somedude/public_html
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.
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_htm
cp /tmp/a.txt /home/somedude/public_html
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is "somedude" always the same? Or will that change as different users run the process?
Tom
Tom
ASKER
@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.
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.
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.
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.
ASKER
I know even less about PHP, so is there no way to directly run this command in PHP?
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.
echo "SELECT * FROM slickfeed_txt" | mysql -u user --password="password" dbase > /home/username/www/dump.txtBecause 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.
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_
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_
ASKER
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.
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.
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.
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.
ASKER
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.
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.
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?
Any reason you do not let the users into whose directory you want to export a report get the report using the web interface?
ASKER
It's google, bing, and slickguns. They need a URL for the specific file to automatically download.
I have no idea what you are trying to say.
ASKER
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.
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.
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.
ASKER
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.
http://www.lanbosarmory.com/tabdelimited.txt
Otherwise it won't work and they won't be able to download it.
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.
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.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.