Forums

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.

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).

I have tried troubleshooting:

For SELECT user(); it returns alter@10.0.0.89

For SELECT current_user(); it returns alter@%

For SHOW GRANTS; it returns:

  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 '\r\n'
IGNORE 0 LINES;

You're probably bumping into this: http://help.pythonanywhere.com/pages/LoadDataInfile/

Thank you for your response. Unfortunately this has not fixed the problem and I continue to get the same warning as before - please see the image attached below. Any further assistance would be greatly appreciated. Thank you!

.

Could you try the same command, but add LOCAL before the INFILE? Let us know if that helps, and we'll add it to the help page if it does.

I'm pleased to report that using LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE whilst also enabling --local-infile=1 from the Bash console command line has allowed me to write the data to the MySQL databases at last!

I didn't manage to get it working with MySQL Workbench (I couldn't find a way to enable --local-infile=1 during SSH connection there) but at least I have this working alternative now. I think it might also have been key to have the .csv files of interest stored on my PythonAnywhere account too (which I transferred via FileZilla SFTP). If you update your documentation that would be excellent - I hope it will be useful to someone else in the future. Thank you for your great assistance throughout, you're the best.

Excellent, thanks for confirming! I've updated the help page, any comments appreciated :-)