Bug 10744

Summary:	mariadb 5.5.28 error if using "load_file" or "load file into"
Product:	Mageia	Reporter:	bozonius <bozonius>
Component:	New RPM package request	Assignee:	Mageia Bug Squad <bugsquad>
Status:	RESOLVED INVALID	QA Contact:
Severity:	major
Priority:	Normal	CC:	bozonius
Version:	3
Target Milestone:	---
Hardware:	i586
OS:	Linux
Whiteboard:
Source RPM:		CVE:
Status comment:

Description bozonius 2013-07-10 05:53:36 CEST

Description of problem:
MariaDB/MySQL 5.5.28 do not permit the use of the LOAD_FILE or "INTO OUTFILE" constructs, and possibly others related to secure-file-priv, which is supposed to specify which directory external files may be read from or written to from an SQL command.  

This version seems to parse the syntax, but always results in a failure.  The errors seem to vary, and not consistently.

I have experimented setting the secure-file-priv= and --secure-file-priv options to my.cnf and mysqld, respectively, but they do not seem to have any effect.

Anecdotal reports found by googling reveal that this is a common issue.

Anyone trying to export data tables to CSV format, or trying to load a binary file into a table field will be frustrated by this issue.  There is not really any alternative or simple workaround; for this reason, I am giving this Major severity.  

However, version 5.6.12 (used on Debian 6.0.4) does work correctly with LOAD_FILE and INTO OUTFILE constructs, and I am guessing the others restricted by secure-file-priv would be as well.

I also used mysql 5.5.31 on a CentOS 6.4 system and both LOAD_FILE and INTO OUTFILE worked correctly there as well.

Version-Release number of selected component (if applicable):
MySQL/MariaDB 5.5.28

How reproducible:
Very

Steps to Reproduce:
1. Execute any SQL statement using the LOAD_FILE() or "INTO OUTFILE" construct


Reproducible: 

Steps to Reproduce:

bozonius 2013-07-10 05:54:17 CEST

CC: (none) => bozonius
Summary: mariadb 5.5.28 does not allow "load_file" or "load file into" => mariadb 5.5.28 error if using "load_file" or "load file into"

Comment 1 bozonius 2013-07-10 05:56:48 CEST

Also see bug 9878 as these may be related to the same (buggy?) release.

Comment 2 bozonius 2013-07-10 07:27:40 CEST

Never mind.  Turns out that the MySQL "user" trying to use these operations must have file privileges.  Those are not listed or available at the database privileges level, only at the user privileges level.  I missed those, which led me to think this was the same problem other users were seeing.

Another thing to be aware of is that the directory where the files are to be written to must be writable by the Unix user "mysql".

These issues and advisos are "documented" but it takes a lot of hunt-and-peck to find out.  Another problem is that the error messages from mysql are not too specific.  The secure-file-priv variable was introduced since MySQL 5.0, and the documentation on it seems less than thorough to me.

I guess close this, unless we want to make a request for better documentation.

Comment 3 bozonius 2013-07-10 07:28:39 CEST

See previous comment.

Status: NEW => RESOLVED
Resolution: (none) => INVALID