"accessed Denied For User" When Loading Data From A File

[MrProsser]

I created a mySQL database in cPanel called tstewart_tsunami, as well as a user tstewart_root, which I then gave access to the database and gave it full privileges. Additionally I used the '%' wildcard to allow remote access to any host.

 

I log into the database using the command line:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -h johnny.heliohost.org -u tstewart_root -p tstewart_tsunami

 

and created a table in tstewart_tsunami called sourceEvents, and I want to load the data from a tab delimited file into this table. I tried to do this using this statement:

 

mysql> LOAD DATA INFILE 'C:\Documents and Settings\tstewart\WebSites\ExamplesSite\tsevent.txt' INTO TABLE sourceEvents;

 

but I get the error:

ERROR 1045 (28000): Access denied for user 'tstewart_root'@'%' (using password:

YES)

 

I have not really done this before so perhaps I am missing something, but I thought that by adding the user to the database and allowing remote connections this should work. I should note that after connecting to my database in Visual Studio I could see the new table, and when I check the DB in cPanel I can see that the table was created. So apparently I can remotely create tables but for some reason I cannot load data into them.

[Ice IT Support]

Try

LOAD DATA LOCAL INFILE 'C:\Documents and Settings\tstewart\WebSites\ExamplesSite\tsevent.txt' INTO TABLE sourceEvents;

If that doesn't work, then upload your file through FTP or cPanel's File Manager, and use

LOAD DATA INFILE '/home/tstewart/path/to/tsevent.txt' INTO TABLE sourceEvents;

[MrProsser]

Hi,

I tried both but had problems. Trying to the local version I kept getting errors

ERROR 22 (HY000): File 'C:Documents and Settings	    stewartMy DocumentsVisual Studio 2010WebSitesExamplesSiteApp_Data	   sevent.txt' not found (Errcode:
22 - Invalid argument)

 

and when I uploaded it to /home/tstewart/tsevent.txt, I ended up getting the same error as in the original post.

[Ice IT Support]

Silly me for not noticing that database users (those created in cPanel using the MySQL utilities) don't have permissions to use the LOAD command. Try logging into MySQL using your cPanel username and password. Not sure if it will work, but it's worth a shot.

 

Also, being a tab delimited file, you should use this command:

LOAD DATA LOCAL INFILE 'C:\Documents and Settings\tstewart\WebSites\ExamplesSite\tsevent.txt' INTO TABLE sourceEvents FIELDS terminated by '\t';

 

If you still have no luck after the above, the next thing I can think of is to import your data through phpMyAdmin. If you go that route, you will have to export your file as a CSV or SQL file.