How to import data via MySQL Workbench with SSH?

I'm trying to upload data to my MySQL database on the PythonAnywhere server hosting via SSH (I have a paid account). I have three databases (alter$default, alter$ip_data and alter$visitor_data) from within the PythonAnywhere interface.

alter$default

alter$ip_data

alter$visitor_data

Using the online instructions (here) I can successfully connect to the server via SSH using MySQL Workbench, and see the three databases in the schemas window (Click here to view image of schemas list).

I can successfully add tables to the database of my choice, but when I try to add data I get Error Code: 1045. Access denied for user 'alter'@'%' (using password: YES).

Error Code: 1045. Access denied for user 'alter'@'%' (using password: YES)

I have tried troubleshooting:

For SELECT user(); it returns alter@10.0.0.89

SELECT user();

alter@10.0.0.89

For SELECT current_user(); it returns alter@%

SELECT current_user();

alter@%

For SHOW GRANTS; it returns:

SHOW GRANTS;

GRANT USAGE ON *.* TO 'alter'@'%' IDENTIFIED BY PASSWORD <secret> WITH MAX_USER_CONNECTIONS 6
GRANT ALL PRIVILEGES ON 'alter$default' .* TO 'alter'@'%'
GRANT ALL PRIVILEGES ON 'alter$visitor_data' .* TO 'alter'@'%'
GRANT ALL PRIVILEGES ON 'alter$ip_data' .* TO 'alter'@'%'


It appears that I have the necessary privileges granted and am correctly connected to the MySQL server - so why will it not let me write to the database? Is the .csv file in the wrong place? I can't use the 'root' user as that is surely the PythonAnywhere administrator account?

Extra details:
The SQL query for writing to the database - that returns "access denied" - is:

LOAD DATA INFILE 'VALUES.CSV'
INTO TABLE ip_data_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
IGNORE 0 LINES;


1 Answer
1

It's the infile that is causing the permission denied. You need to start the mysql client with the --local-infile=1 flag.

LOAD DATA LOCAL INFILE

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.