Forums

Hey Guys! I was trying to import a file to a new mysql table with "LOAD DATA LOCAL INFILE", but it keeps giving me this "ERROR 1148 (42000): The used command is not allowed with this MySQL version " I googled it up a bit and found out it is some config when mysql is loaded, but I failed to solve it out by myself (and I really gave it a shot!) . It should be something about "local_infile = 1". Can you guys give me some help here? Tks

You need to start the mysql client with the local_infile setting. Like this:

mysql -h mysql.server -u <usernme> '<dbname>' -p --local-infile=1

Then you should be able to use LOAD DATA LOCAL INFILE

PostgreSQL is a better solution....Oh, sorry I keep promising myself to stop that...☺

I'm having this same issue, and the above solution doesn't seem to work for me. Getting:

ERROR 1045 (28000): Access denied for user '<myusername>'@'%' (using password: YES)

Any help?

What command did you type to get that error?

I launched mysql from bash using :

mysql -h alk3ckwd.mysql.pythonanywhere-services.com -u alk3ckwd 'alk3ckwd$sports_analysis' -p --local-infile=1

Then:

mysql> LOAD DATA INFILE "/home/alk3ckwd/mysite/data/stats2.csv"
    -> INTO TABLE sports_analysis_stats
    -> COLUMNS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> ESCAPED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 LINES;

Which gives me:

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

Are you sure you're using your database password and not your account password?

I think the problem here is that you're using LOAD DATA INFILE without the extra LOCAL keyword. When you use it without local, it tried to load a file from the machine where the MySQL server is running, which doesn't have access to your files. Try again, replacing the first line with LOAD DATA LOCAL INFILE "/home/alk3ckwd/mysite/data/stats2.csv"

LOAD DATA LOCAL INFILE

did it! Thanks!

Great! Thanks for confirming :-)

For anyone else coming across this thread, the updated command-line for launching mysql with local-infile switched on is:

mysql -h harry.mysql.pythonanywhere-services.com -u harry harry$my-db-name --local-infile=1

You'll need to replace "harry" and "my-db-name" as appropriate with values for your database hostname, database username, and database name, all of which you can find on the Databases tab...