0

I am trying to load a CSV file from a remote server into an AWS mySQL table.

I get an Error 1045 (Access denied) when running mysql script to LOAD DATA INFILE from the remote server.

If I paste the URL of the csv file into a browser it downloads as a CSV. So there doesn't seem to be an authentication issue with it.

If I store the csv locally and use LOAD DATA LOCAL INFILE it uploads to the mySQL table - Successful.

The command I am using in mySQL Workbench is this:

LOAD DATA INFILE 'http://geoserver-rls.imas.utas.edu.au/geoserver/RLS/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=RLS:SpeciesSurvey&outputFormat=csv'  
INTO TABLE staging.locations 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

This SQL will build the locations table 

CREATE TABLE `locations` (
  `FID` text,
  `SpeciesID` int(11) DEFAULT NULL,
  `SurveyID` int(11) DEFAULT NULL,
  `SiteLongitude` double DEFAULT NULL,
  `SiteLatitude` double DEFAULT NULL,
  `Realm` text,
  `Country` text,
  `State` text,
  `geom` text,
  `key` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The exact error message is here: Error Code: 1045. Access denied for user ''@'%' (using password: YES) 0.188 sec

My question is: 1) what can I do to get around this access denied error when I'm running this from within the Workbench environment - which is allowed read/write access to the database?

Thank you.

John.

John Anderson
  • 35
  • 8
  • Possible duplicate of: http://stackoverflow.com/questions/7271502/file-privilege-to-mysql-session-user – user2182349 Oct 21 '15 at 23:41
  • Hi user2182349, Thank you for adding this reference. I've tried following what they say and its interesting that using the example of a 'nonexistantfile' produces the same error. So maybe this is not an error on the remote location where the file sits? and maybe it IS a mysql side error. Although I can reference a file on my desktop using the above command and LOAD DATA LOCAL INFILE. this works fine. – John Anderson Oct 22 '15 at 03:08
  • Load data infile cannot load data from an url. The file has to be either on the server (or on a shared drive mapped to the server), or on the client. – Shadow Oct 22 '15 at 06:54
  • Ah.. that explains it then. Where I thought this was a security error or syntactical. it turns out that its a limitation. I didn't see that there was really a difference between Loading it from a folder on one remote server as opposed to loading it from a dynamic web server (also remote). Now I know this won't work. I will review other options. Thank you for this. – John Anderson Oct 22 '15 at 11:00

0 Answers0