MySQL Bugs: #72220: LOAD DATA LOCAL INFILE not working in mysql

Bug #72220	LOAD DATA LOCAL INFILE not working in mysql
Submitted:	3 Apr 2014 13:54	Modified:	8 Apr 2014 10:52
Reporter:	vinu manikandan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S5 (Performance)
Version:	Ver 14.14 Distrib 5.5.35	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
LOAD DATA  LOCAL INFILE "INPUT.txt"  REPLACE INTO TABLE DOCTOR  FIELDS TERMINATED BY '\t';

ERROR 1148 (42000): The used command is not allowed with this MySQL version

My mySQL version is as follows
mysql  Ver 14.14 Distrib 5.5.35, for debian-linux-gnu (x86_64) 

do let me know how to fix it

How to repeat:
create a table and use the following command
LOAD DATA  LOCAL INFILE "INPUT.txt"  REPLACE INTO TABLE DOCTOR  FIELDS TERMINATED BY '\t';

The server variable 'local_infile' is set to FALSE|0. Refer documentation at https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_local_infile (I verified that this is the error you'll get when this variable is set to FALSE|0).

You can verify by executing "SHOW VARIABLES LIKE 'local_infile';")

(if you have SUPER privilege you can enable it (without restarting server with a new configuration) by executing "SET GLOBAL local_infile = 1;"). 

Peter
(not a MySQL/Oracle person)

I had executed the command 
SHOW VARIABLES LIKE 'local_infile';
result is as following
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+

even then the error exists;

Well .. it does not here (MySQL 5.5.37 Windows 64bit build from Oracle)! The variable works as expected.

I think your server is not an official MySQL build, but one bundled with a Linux distribution?

I had installed the mysql sql using following command

sudo apt-get install mysql-server mysql-client

set username password , created new database and tables;

Ran following commands

show variables like '%INFILE%';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

if i ran the following commands:
1) 
LOAD DATA  INFILE "/similarSequences.txt"  REPLACE INTO TABLE SimilarSequences  FIELDS TERMINATED BY '\t' lines terminated by '\n';

ERROR 29 (HY000): File '/similarSequences.txt' not found (Errcode: 13)

2) LOAD DATA  LOCAL INFILE "/similarSequences.txt"  REPLACE INTO TABLE SimilarSequences  FIELDS TERMINATED BY '\t';

ERROR 1148 (42000): The used command is not allowed with this MySQL version

Do let me know what to do

Hello Vinu,

Thank you for the report.
Imho if LOAD DATA LOCAL is disabled, either in the server or the client, a client that attempts to issue such a statement receives the following error message:

ERROR 1148: The used command is not allowed with this MySQL version

Please reference - http://dev.mysql.com/doc/refman/5.5/en/load-data.html
http://dev.mysql.com/doc/refman/5.5/en/load-data-local.html

Workaround(other than configuration option) is to use --local-infile=1

select version();
show variables like '%INFILE%';
use test;
create table if not exists t1(id int not null primary key, name varchar(100));
LOAD DATA LOCAL INFILE "/tmp/test.csv" REPLACE INTO TABLE t1 FIELDS TERMINATED BY '\t' lines terminated by '\n';
select * from t1;

# more /tmp/test.csv
1 mysql1
2 mysql2

mysql-5.5.38]# bin/mysql -u root -p test --local-infile=1
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.38-debug-log Source distribution

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> LOAD DATA LOCAL INFILE "/tmp/test.csv" REPLACE INTO TABLE t1 FIELDS TERMINATED BY '\t' lines terminated by '\n';
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

Dear Friends,

Thank you for all the help provided to me in fixing it.  I had followed the following things to fix the issue and it worked. 

1) Added few lines in /etc/mysql/my.cnf
 
    a) [client]
            loose-local-infile = 1 // added this
   b) [mysqld]
     local-infile = 1 // added this

2)  /etc/apparmor.d/usr.sbin.mysqld
     usr/sbin/mysqld {
...
/var/log/mysql/ r,
/var/log/mysql/* rw,
/var/run/mysqld/mysqld.pid w,
/var/run/mysqld/mysqld.sock w,
/data/ r,    // added this
/data/* rw, // added this
 }

3) sudo /etc/init.d/apparmor reload

4) sudo service mysql restart

http://bugs.mysql.com/bug.php?id=91872 marked as duplicate of this one.

http://bugs.mysql.com/bug.php?id=91891 marked as duplicate of this one.

Same issue with both router and workbench 8.0 ....
the config does not keep the local-infile set to true ...