0

we have many questions about load_file() failures on stackoverflow already. alas, my wrinkle is different: I have full sudo control and privileges. how can I track what is going wrong?

background: on ubuntu 14.04 with mysql 5.5.46, I started a mysqld --verbose, then I turned myself into an su on another terminal, and did

 # mysql -u me
 use mydb;
 create table aha (fc blob);
 insert into aha values(load_file('/tmp/test.txt'));

which creates the seemingly inevitable NULL in the table. I only tried about 200 different variants on accomplishing this.

the failure is one issue. (an expert might take one look and see it. I strotz with privileges. I am su. I have given myself all privileges possible. the /tmp/test.txt file is world-readable. the mysql version is not that old.)

alas, I have a different issue: I have no idea how to go about tracing the problem. is the source of the failure the inability to slurp the file, the privileges, etc.? the /var/log/mysql/*log provides no useful info, despite the --verbose flag.

help appreciated.

ivo Welch
  • 2,427
  • 2
  • 23
  • 31
  • 1
    I believe it is the MySQL service user needing read permissions to that file, so you having started the client as su is not relevant. But if you can verify that the file is world-readable, I would expect the mysql user to be able to read it from /tmp. You are accessing the client from the same machine as the server, correct? `LOAD_FILE()` expects the file on the server, not the client if they differ. – Michael Berkowski Oct 29 '15 at 18:23
  • 1
    Please confirm you have met the other requirements - the `FILE` privilege for your mysql user, and size constraints: https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_load-file – Michael Berkowski Oct 29 '15 at 18:25
  • yes, I did check. `show grants` in mysql gives me `GRANT FILE ON *.* TO 'me'@'localhost'`; and `sudo -S -u mysql more /tmp/test.txt` shows me the file (and it is rw-rw-r--). but, more than a fish, I need to know how to fish---I need to learn how to track the problem. is this possible, or is this strictly a guessing game only? – ivo Welch Oct 30 '15 at 03:40
  • 1
    Sorry, I should have thought of this sooner, but your issue is likely due to AppArmor. I just tested this on an Ubuntu VM: http://stackoverflow.com/a/24461957/541091 – Michael Berkowski Oct 30 '15 at 12:00
  • 1
    With an issue like this, most of the time you won't get usable log output from MySQL. So to think through it I would visit anything which could get in the way of a file read (after verifying everything in the docs). You did all of those things, had world-readable perms etc. Then you get to more difficult territory, because the OS itself (via apparmor) is effectively blocking the file from use. I have var more experience in Red Hat/SELinux and would think of that quickly, but you just need to traverse downward toward the kernel if possible. – Michael Berkowski Oct 30 '15 at 12:56
  • I haven't checked and know too little of apparmor, but I wonder if MySQL would have been able to `LOAD_FILE()` on a text file inside its own data directory /var/lib/mysql. – Michael Berkowski Oct 30 '15 at 12:57
  • thank you, michael. the specific issue in my case was indeed apparmor. still, seeing a "cannot read file" message or something like it would have been helpful. but I now understand that we don't have much tracing :-( . this is probably the answer to accept --- mysql does not offer tracing of such errors. it's a guessing game, indeed. – ivo Welch Oct 30 '15 at 15:44

0 Answers0