MySQL Enable LOAD DATA LOCAL INFILE

Question

I m running Mysql 5 5 on Ubuntu 12 LTS  How should I enable LOAD DATA LOCAL INFILE in my cnf     I ve tried adding local-infile in my config at various places but I m still getting the  The used command is not allowed with this MySQL version

User · Answer

Replace the driver php5-mysql by the native driver  On debian   apt-get install php5-mysqlnd

User · Answer

in case your flavor of mysql on ubuntu does NOT under any circumstances work and you still get the 1148 error  you can run the load data infile command via command line  open a terminal window  run mysql -u YOURUSERNAME -p --local-infile YOURDBNAME  you will be requested to insert mysqluser password  you will be running MySQLMonitor and your command prompt will be mysql gt   run your load data infile command  dont forget to end with a semicolon       like this   load data local infile   home tony Desktop 2013Mini csv  into table Reading Table FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   n

User · Answer

The my cnf file you should edit is the  etc mysql my cnf file  Just   sudo nano  etc mysql my cnf   Then add    mysqld  local-infile    mysql  local-infile    The headers  mysqld  and  mysql  are already given  just locate them in the file and add local-infile underneath each of them   It works for me on MySQL 5 5 on Ubuntu 12 04 LTS

User · Answer

From the MySQL 5 5 manual page      LOCAL works only if your server and your client both have been   configured to permit it  For example  if mysqld was started with   --local-infile 0  LOCAL does not work  See Section 6 1 6     Security Issues with LOAD DATA LOCAL       You should set the option   local-infile 1   into your  mysql  entry of my cnf file or call mysql client with the --local-infile option   mysql --local-infile -uroot -pyourpwd yourdbname   You have to be sure that the same parameter is defined into your  mysqld  section too to enable the  local infile  feature server side   It s a security restriction

User · Answer

In case if Mysql 5 7 you can use   show global variables like  local infile     which will give the local infile status  You can turn it on using   set global local infile ON

User · Answer

I used below method  which doesn t require any change in config  tested on mysql-5 5 51-winx64 and 5 5 50-MariaDB   put  load data     in  sql file  ex  LoadTableName sql   LOAD DATA INFILE  D   Work  TableRecords csv  INTO TABLE tbl1 FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   r n  IGNORE 1 LINES  col1 col2     then   mysql -uroot -pStr0ngP ss -Ddatabasename -e  source D  Work LoadTableName sql

User · Answer

if your csv file located same with db  you need to remove LOCAL in LOAD DATA INFILE  or you will get the error     The used command is not allowed with this MySQL version

User · Answer

Another way is to use the mysqlimport client program   You invoke it as follows   mysqlimport -uTheUsername -pThePassword --local yourDatabaseName tableName txt   This generates a LOAD DATA statement which loads tableName txt into the tableName table   Keep in mind the following   mysqlimport determines the table name from the file you provide  using all text from the start of the file name up to the first period as the table name  So  if you wish to load several files to the same table you could distinguish them like tableName 1 txt  tableName 2 txt      etc  for example

User · Answer

Ok  something odd is happening here  To make this work  do NOT need to make any configuration changes in  etc mysql my cnf   All you need to do is to restart the current mysql service in terminal   sudo service mysql restart   Then if I want to  recreate  the bug  I simply restart the apache service   sudo service apache2 restart   Which can then be fixed again by entering the following command   sudo service mysql restart   So  it appears that the apache2 is doing something to not allow this feature when it starts up  which is then reversed corrected if restart the mysql service    Valid in Debian based distributions   service mysqld restart service httpd restart   Valid in RedHat based distributions

User · Answer

You have to take care how you establish your mysqli connection  Full credit for this solution goes to Jorge Albarenque  source  In order to fix it I had to    Add local-infile 1 to the  mysqld  and  mysql  sections of my cnf  as explained in the comments above  Use mysqli real connect function  PHP  documentation     The catch is that with that function you can explicitly enable the support for LOAD DATA LOCAL INFILE  For example  procedural style     link   mysqli init    mysqli options  link  MYSQLI OPT LOCAL INFILE  true   mysqli real connect  link   host   username   password   database     or object oriented   mysqli   mysqli init     mysqli- gt options MYSQLI OPT LOCAL INFILE  true    mysqli- gt real connect  host   username   password   database

User · Answer

Add local infile in both client and mysqld section    client  local infile 1       mysqld  local infile 1       Tested in MySQL 8 x both in Windows and Linux

User · Answer

This went a little weird for me  from one day to the next one the script that have been working since days just stop working  There wasn  t a newer version of mysql or any kind of upgrade but I was getting the same error  so I give a last try to the CSV file and notice that the end of lines were using  n instead of the expected   per my script    r n so I save it with the right EOL and run the script again without any trouble   I think is kind of odd for mysql to tell me The used command is not allowed with this MySQL version since the reason was completely different   My working command looks like this   LOAD DATA LOCAL INFILE  file-name  IGNORE INTO TABLE table-name CHARACTER SET latin1 FIELDS TERMINATED BY     OPTIONALLY ENCLOSED BY      LINES TERMINATED BY   r n  IGNORE 1 LINES

User · Answer

I am using xampp v3 2 4 and mysql server 8 0 20   I added local-infile 1 to  mysql  and  mysqld  in the file  my ini   The file is located at  C  xampp mysql bin my ini    Then I inserted the data from csv file using the following code LOAD DATA INFILE      It is important to move LOCAL  Otherwise it won t work    Thanks for all suggestions above  A combination finally worked out for me

User · Answer

See below image     I ve added --local-infile 1 to normal mysql command  mysql -u root -p  So total line would be        mysql --local-infile 1  -u root -p

User · Answer

All  Evidently this is working as designed  Please see new ref man dated 2019-7-23  Section 6 1 6  Security Issues with LOAD DATA LOCAL

User · Answer

For those of you looking for answers to make LOAD DATA LOCAL INFILE work like me  this might probably work  Well it worked for me  so here it goes  Install percona as your mysql server and client by following the steps from the link  A password will be prompted for during the installation  so provide one that you ll remember and use it later  One the installation is done  reboot your system and test if the server is up and running by going to the terminal and typing mysql -u root -p and then the password  Try running the command LOAD DATA LOCAL INFILE now   Hope it works     BTW I was working on Rails 2 3 with Ruby 1 9 3 on Ubuntu 12 04

User · Answer

I solved this problem on MySQL 8 0 11 with the mysql terminal command   SET GLOBAL local infile   true    I mean I logged in first with the usual   mysql -u user -p    After that you can see the status with the command   SHOW GLOBAL VARIABLES LIKE  local infile     It should be ON  I will not be writing about security issued with loading local files into database here

User · Answer

Also  for other readers  if you are trying to do this in Django AND your server allows local infile  you can check by typing SHOW VARIABLES via a mysql client  then you can add this to your settings py file  since python MySQLdb doesn t by default read the  my cnf file    DATABASES          default              ENGINE    django db backends mysql            NAME    mydb            USER    myname            PASSWORD    mypass            HOST    myserver            PORT    3306            OPTIONS                   local infile  1

[mysql] MySQL: Enable LOAD DATA LOCAL INFILE

Examples related to mysql

Examples related to mysql-loadfile