Page 1 of 1
LOAD DATA INFILE mysql permissions level
Posted: Wed Sep 29, 2004 6:33 pm
by hawleyjr
I have created a script to load a csv file to mysql. I'm getting an access denied message from the script.
Code: Select all
Access denied for user: 'username@localhost' (Using password: YES)
The server I'm using uses CPanel for their admin site. The database user I'm setting has: (Privileges: ALL PRIVILEGES)
Is there a specific privilege I will need to use LOAD DATA INFILE?
Query Code:
Code: Select all
<?php
$qry = "LOAD DATA INFILE 'test.txt'
INTO TABLE dbname.tablename
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES";
?>
Posted: Wed Sep 29, 2004 7:27 pm
by timvw
before performing the query, authenticate/connect with the credentials of that user (instead of using php default mysql user)
[php_man]mysql_connect[/php_man]
[php_man]mysql_select_db[/php_man]
Posted: Wed Sep 29, 2004 7:49 pm
by hawleyjr
I tried that. No luck.?
Posted: Wed Sep 29, 2004 7:52 pm
by Breckenridge
Here is a very simple way to do this:
Code: Select all
$db_conn = mysql_connect("localhost","db_user","password");
mysql_select_db("database_name",$db_conn);
Posted: Wed Sep 29, 2004 7:56 pm
by hawleyjr
I'm connected to the database fine. To make sure to make sure of that I ran a query before the LOAD DATA INFILE query.
Code: Select all
<?php
/* TEST CREATE JUST TO MAKE SURE CONNECTION IS THERE */
$qry = "CREATE TABLE `database`.`tablename` (
`Id` int(6) unsigned NOT NULL auto_increment,
PRIMARY KEY (`Id`)
) TYPE=MyISAM;";
$result = mysql_query($qry);
$qry = "LOAD DATA INFILE 'test.txt'
INTO TABLE dbname.tablename
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES";
mysql_query($qry);
?>
Posted: Wed Sep 29, 2004 8:01 pm
by Breckenridge
what platform are you running your code on unix or windows?
Posted: Wed Sep 29, 2004 8:03 pm
by hawleyjr
apache (1.3.31 (Unix))
mysql (4.0.20-standard)
Posted: Wed Sep 29, 2004 8:13 pm
by Breckenridge
try changing permissions to user:group mysql:mysql on your text file.
Posted: Wed Sep 29, 2004 8:15 pm
by hawleyjr
Breckenridge wrote:try changing permissions to user:group mysql:mysql on your text file.
Please explain.?
Posted: Wed Sep 29, 2004 8:23 pm
by hawleyjr
The FILE privilege gives you permission to read and write files on the server host using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements. A user who has the FILE privilege can read any file on the server host that is either world-readable or readable by the MySQL server. (This implies the user can read any file in any database directory, because the server can access any of those files.) The FILE privilege also allows the user to create new files in any directory where the MySQL server has write access. Existing files cannot be overwritten.
http://dev.mysql.com/doc/mysql/en/Privi ... vided.html
It looks like if I don't have FILE privelege I won't be able to use LOAD DATA INFILE. Since the server I'm using is a shared server, I'm sure I don't.
Posted: Wed Sep 29, 2004 8:27 pm
by Breckenridge
Most likely mysql server is running as a system user named mysql. When you try to read a file owned say by user apache or ftpuser I believe that mysql is failing to read the file.
Now, do you have ssh access to your server?
if yes do:
Code: Select all
chown mysql:mysql file.txt
or
chmod 777 file.txt << I don't like using this one!
if no:
try using your ftp client program change permissions on the text data file to 777 aka all can read/write/execute
Posted: Sun Oct 03, 2004 11:20 am
by hawleyjr
In case anyone else has this problem. By adding 'LOCAL' to the SQL I was able to get this to work:
What I was trying to do:
Code: Select all
<?php
$qry = "LOAD DATA INFILE 'test.txt'
INTO TABLE dbname.tablename
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES";
?>
How I got it to work:
Code: Select all
<?php
$qry = "LOAD DATA LOCAL INFILE 'test.txt'
INTO TABLE dbname.tablename
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES";
?>
Posted: Sun Oct 03, 2004 12:29 pm
by Breckenridge
Cool, so it was never a file perms issues.