File load data infile'file-name' fails

[John Gelm]

I am migrating from 7.04 to 8.04. The environment is Ubuntu 64bit desktop w/LAMP added.

Here is the error in 8.04:
mysql> load data infile '/var/www/tradesim/foliofn/MA46255006_open_tax_lot.csv' into table otl_csv;
ERROR 29 (HY000): File '/var/www/tradesim/foliofn/MA46255006_open_tax_lot.csv' not found (Errcode: 13)
mysql>

The message is a permission error, but I have 777'ed the entire /var/www to no avail.

root@voyager:/var# ls -lh /var/|grep www
drwxrwxrwx 54 root root 4.0K 2008-06-28 13:25 www

root@voyager:/var# ls -lh /var/www|grep tradesim
drwxrwxrwx 19 gelmjw gelmjw 4.0K 2008-06-14 10:48 tradesim

root@voyager:/var# ls -lh /var/www/tradesim | grep foliofn
drwxrwxrwx 5 gelmjw gelmjw 12K 2008-06-30 14:08 foliofn

root@voyager:/var# ls -lh /var/www/tradesim/foliofn/MA46255006_open_tax_lot.csv
-rwxrwxrwx 1 gelmjw gelmjw 14K 2008-06-30 14:08 /var/www/tradesim/foliofn/MA46255006_open_tax_lot.csv

[Mathias Gug (mathiaz)]

On Tue, Jul 01, 2008 at 01:44:48AM -0000, John Gelm wrote:
> Public bug reported:
>
> I am migrating from 7.04 to 8.04. The environment is Ubuntu 64bit
> desktop w/LAMP added.
>
> Here is the error in 8.04:
> mysql> load data infile '/var/www/tradesim/foliofn/MA46255006_open_tax_lot.csv' into table otl_csv;
> ERROR 29 (HY000): File '/var/www/tradesim/foliofn/MA46255006_open_tax_lot.csv' not found (Errcode: 13)
> mysql>
>
> The message is a permission error, but I have 777'ed the entire /var/www
> to no avail.

This is due to the apparmor profile for mysqld that disallows the mysqld
process to read files in /var/www.

You can emulate the load data infile SQL statement with the mysqlimport
utility and use the LOCAL option (so that the file is sent to the mysqld
rather then mysqld opening the file directly).

Another option is to update mysqld apparmor profile to allow read access
to your file. See https://wiki.ubuntu.com/DebuggingApparmor for more
information on this.

  status wontfix

--
Mathias Gug
Ubuntu Developer http://www.ubuntu.com

[John Gelm (jgelm)]

Mathais:

I would like to appeal your decision on this on the basis that mysqld already applies the necessary restriction and that the apparmor restriction is redundant, plus it is not granular per user. MySQL defaults to having LOAD DATA INFILE off due to the Files Privilege being off.

<This is due to the apparmor profile for mysqld that disallows the mysqld
process to read files in /var/www.>

On 7.04 all I needed was to GRANT FILES PRIVILEGES to use LOAD DATA INFILE. As it is now, apparmor thwarts my user-level GRANTS.

<See https://wiki.ubuntu.com/DebuggingApparmor for more
information on this.>

Here is what I did and it seems to work:
gelmjw@voyager:~$ sudo aa-complain /usr/sbin/mysqld
[sudo] password for gelmjw:
Setting /usr/sbin/mysqld to complain mode.
gelmjw@voyager:~$ sudo /etc/init.d/apparmor reload
Reloading AppArmor profiles : done.
gelmjw@voyager:~$

Thanks for the link. However I believe this is a workaround. Of course, I could be totally ignorant on this, so if there is a link to help understand why the apparmor restriction is there, I would be happy to be informed.

Thank You;
John

[Akash Yellappa (akashyk)]

Brilliant John, it worked :) Thank you!!

[Alex Beels (arbeels-ossf)]

A more complete solution:

1. Pick a directory from which mysqld should be allowed to load files. Perhaps somewhere writable only by your DBA account and readable only by members of group mysql?

2. sudo aa-complain /usr/sbin/mysqld

3. Try to load a file from your designated loading directory: 'load data infile '/var/opt/mysql-load/import.csv' into table ...'

4. sudo aa-logprof
aa-logprof will identify the access violation triggered by the 'load data infile ...' query, and interactively walk you through allowing access in the future. You probably want to choose Glob from the menu, so that you end up with read access to '/var/opt/mysql-load/*'. Once you have selected the right (glob) pattern, choose Allow from the menu to finish up. (N.B. Do *not* enable the repository when prompted to do so the first time you run aa-logprof, unless you really understand the whole apparmor process.)

5. sudo aa-enforce /usr/sbin/mysqld

6. Try to load your file again. It should work this time.

There are some advantages to this approach:

John's workaround keeps apparmor disabled for mysqld globally. That is overkill, and should be a security problem. (Everything in your LAMP stack should be tied down as securely as possible, because it is exposed to the outside world and will be attacked. This is surely the reason why mysqld is restricted via apparmor in the first place. Of course, MySQL has uses outside of LAMP, but we're talking about policy here, not individual installations.)

The alternative (that I was using before I found this page and learned about apparmor) is to always load from somewhere in /var/lib/mysql/, where mysql has read privileges. This is not ideal either, because it corrupts a directory that should be managed entirely by mysql and dpkg, and it requires root privileges to queue up a data file for loading. With the apparmor exception described above, your DBA does not need to be granted any special privileges.

Comments, anyone? I'm a newbie to both MySQL and apparmor, so corrections are welcome...

[Jamie Strandboge (jdstrand)]

Alex, your approach is totally correct. If you need to add additional access to files, updating the AppArmor profile is the correct way to do that.

For people who prefer to adjust the profile directly instead of using aa-logprof (which is totally legitimate), you can adjust /etc/apparmor.d/usr.sbin.mysqld directly, followed by:
$ sudo apparmor_parser -r -W -T /etc/apparmor.d/usr.sbin.mysqld

Or on Jaunty and earlier:
$ sudo apparmor_parser -r /etc/apparmor.d/usr.sbin.mysqld

[John Gelm (jgelm)]

I notice that my new 10.04 installation works fine with load data
infile; without having to use aa-complain.

[John Gelm (jgelm)]

>>I notice that my new 10.04 installation works fine with load data
>>infile; without having to use aa-complain.

Thank you to all.

I did change to load data LOCAL infile... per: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

 As previously stated, I am running a desktop+LAMP. The system is both client and server. The 'infile's are all stored under /var/www/ sub-directories via apache/php with owner.group www-data.www.data. It works.

 If I omit LOCAL, I would need the FILES privilege along with the apparmor adjustments.

There is no bug and all is well.