1

My program that's running on Ubuntu 18.04 executes a select statement into outfile into my /home/tmp directory, but all files that are created by mysql are locked. I can manually unlock them but our program needs to read the file right after they are created, so I need them to be unlocked upon creation.

I've already set the folder permissions to 777, but still, any newly created files are locked. The files are owned by mysql server and have no access rights for "Others" users. I can't change these permissions as they are not owned by me.

the mysql server that's running is v8.0.18

In my.cnf, I have set secure_file_priv to this /home/tmp folder path.

We have the same program running with mysql server 5.7.27 on another computer and the files are not locked on there, so I'm not sure what the problem is.

Please help! Thanks.

EDIT: clarifications

  • by "locked" I mean the file has a padlock symbol on the icon when viewing it in files.
  • the error message I get when trying to open the file says "Access to "/home/tmp/filename" was denied.
  • my select statement is something like " SELECT * INTO OUTFILE '/home/tmp/file.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM device "
1

You've run in to this note from the MySQL documentation (see https://dev.mysql.com/doc/refman/8.0/en/select-into.html):

As of MySQL 8.0.17, the umask for file creation is 0640; you must have sufficient access privileges to manipulate the file contents. Prior to MySQL 8.0.17, the umask is 0666 and the file is writable by all users on the server host.

So the files are only readable to whichever group MySQL is running as. You could add your user to that group, but that might give access beyond what is desired (e.g., direct access to the database datafiles).

Another option, if your filesystem supports ACLs (most do) is to see if a default ACL will override the permissions MySQL is setting, try something like this (on the directory the dumps are being written to):

sudo setfacl -m 'd:u:«YOUR-USER»:r' /home/tmp

This sets the "default" ACL to automatically put an ACL on any files created in /home/tmp allowing «YOUR-USER» to read them. If you need to write to them as well, you'd use rw instead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.