4

I have searched and found this post (http://stackoverflow.com/questions/1814297/cant-load-file-data-in-the-mysql-directory) but it is not working for me.

i am un Ubuntu 12.04 and MySQL version is 5.5.22-0ubuntu1

I have logged into MySQL as root and so grants should all be okay:

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

I am trying to insert some data from a text file into a MySQL database and the LOAD_FILE function doesn't seem to work properly

I created a test file, permissions of 777 and copied to root of the install (I tried changing owner/group to root:root and mysql:mysql and still no good):

mysql> select load_file('/test.txt');
+------------------------+
| load_file('/test.txt') |
+------------------------+
| NULL                   |
+------------------------+
1 row in set (0.00 sec)

But if I try this:

mysql> select load_file('/etc/hosts');

It works fine. If I copy the test file into /etc it still fails.

has anyone seen this before or can perhaps point me to another way to load into the database?

Firedragon
  • 3,685
  • 3
  • 35
  • 75

4 Answers4

1

To use load_file, the following conditions must be met (from the documentation):

  1. The file must be located on the server host
  2. You must specify the full path name to the file, and you must have the FILE privilege.
  3. The file must be readable by all and its size less than max_allowed_packet bytes.
  4. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

If the file contains SQL statements that you want to execute, an easier approach might be to pipe it in:

mysql -u foo -p dbname < filename.sql

Burhan Khalid
  • 169,990
  • 18
  • 245
  • 284
  • `secure_file_priv` is empty. I am running locally and using full path and it is readable. The test file is smaller in size thabn `/etc/hosts` so all of the above should be okay. It isn't SQL statements so that won't work either. It is, basically, a large list of numbers in a text file that are sent and similar data is received as part of a test step. – Firedragon Jun 13 '12 at 10:08
  • Are you sure about the path here? `select load_file('/test.txt');` This isn't full path, unless the file is actually sitting at `/test.txt` – Burhan Khalid Jun 13 '12 at 10:10
  • The file was copied there. I have tried in various paths like `/home/me/test.txt` as well and it doesn't work. – Firedragon Jun 13 '12 at 10:26
  • The only thing remaining is to check the permissions. – Burhan Khalid Jun 13 '12 at 10:27
0

Im not an expert on MySQL, but ive observed that MySQL version 5.5 has a problem with UBUNTU OS.

Even after following the documentation in mysql docs LOAD_FILE() didnt work. There is a service called apparmour, preventing the function LOAD_FILE() from executing, i tried stopping that service but still it persisted.....

I know this doesnt solve your problem, but at least it'll help u find where the problem is......

user2681045
  • 380
  • 1
  • 4
0

Consider this one-liner (note, I'm on Ubuntu):

printf "$(cat update_xml.sql)" "$(cat my.xml | sed s/"'"/"\\\'"/g)" | mysql -h myRemoteHost -u me -p***

In update_xml.sql there is:

UPDATE
   myTable
SET
   myXmlColumn = '%s'
WHERE
   ...
user151851
  • 198
  • 1
  • 2
  • 8
0

Adding this for future reference. Probably won't help the OP.

As noted before, AppArmor is to blame. You need to whitelist the paths needed for load_file into the provided profile which can be found here: /etc/apparmor.d/usr.sbin.mysqld. The apparmor.d documentation can be found here. This is the recommended way as AppArmor has its reasons to be there.

Alternatives:

  • This is the unrecommended method. Disable the usr.sbin.mysqld profile so you won't expose all the services. Simply link the profile to /etc/apparmor.d/disable with ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/usr.sbin.mysqld. Reload the profiles with /etc/init.d/apparmor restart. It probably makes sense for a development machine.
  • This is the highly unrecommended method, if you don't actually need AppArmor. The profiles can be unloaded with /etc/init.d/apparmor teardown. Disable the init script with update-rc.d -f apparmor remove.

All the above stuff requires root privileges, but I skipped the ever repetitive sudo in front of all the commands.

SaltwaterC
  • 372
  • 4
  • 11