I am learning MySQL and I use it on pythonanywhere.com via the console they provide.
I was able to create some schema's but when i try to load data into the database I made I get an access denied error.
I used this code:
LOAD DATA
INFILE '/home/username/data.tsv'
INTO TABLE table
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';
And got this error:
ERROR 1045 (28000): Access denied for user 'username'@'%' (using password: YES)
while googling for solutions I got other (but similar) errors with commands like this:
SELECT user, host FROM mysql.user
with the folowing error:
ERROR 1142 (42000): SELECT command denied to user 'username'@'10.0.0.44' for table 'user'
Can anybody help me solve this/tell me what I did wrong?
There is a help page on pythonanywhere.com that deals with this problem specifically. There are two things to keep in mind.
The newer versions of MySQL client block load data by default. You need to pass it in as a command line switch. If you open up a Bash console you can connect to the database manually like so:
mysql -h myusername.mysql.pythonanywhere-services.com -u myusername 'myusername$default' -p --local-infile=1
The MySQL command LOAD DATA INFILE "foo.csv" tries to load the contents of the file foo.csv on the computer where the database is running. On PythonAnywhere, the MySQL server is on a different computer to the one where your code runs, so your files aren't available. Instead, you need to tell the database to load the file from the computer where the MySQL client is running, by adding the extra keyword LOCAL:
LOAD DATA LOCAL INFILE "foo.csv"