LOAD DATA INFILE mysql permissions level

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

LOAD DATA INFILE mysql permissions level

Post 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";

?>
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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]
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

I tried that. No luck.?
Breckenridge
Forum Commoner
Posts: 62
Joined: Thu Sep 09, 2004 11:10 pm
Location: Breckenridge, Colorado

Post 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);
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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); 

?>
Breckenridge
Forum Commoner
Posts: 62
Joined: Thu Sep 09, 2004 11:10 pm
Location: Breckenridge, Colorado

Post by Breckenridge »

what platform are you running your code on unix or windows?
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

apache (1.3.31 (Unix))
mysql (4.0.20-standard)
Breckenridge
Forum Commoner
Posts: 62
Joined: Thu Sep 09, 2004 11:10 pm
Location: Breckenridge, Colorado

Post by Breckenridge »

try changing permissions to user:group mysql:mysql on your text file.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Breckenridge wrote:try changing permissions to user:group mysql:mysql on your text file.
Please explain.?
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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.
Breckenridge
Forum Commoner
Posts: 62
Joined: Thu Sep 09, 2004 11:10 pm
Location: Breckenridge, Colorado

Post 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
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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";

?>
Breckenridge
Forum Commoner
Posts: 62
Joined: Thu Sep 09, 2004 11:10 pm
Location: Breckenridge, Colorado

Post by Breckenridge »

Cool, so it was never a file perms issues.
Post Reply