Failing to write out a file from MySQL — a tale of systems behavior

Sometimes you get into a situation where two separate issues conspire to drive you to question your sanity. For me this recently happened when I was trying to do a pretty simple operation: I just wanted to write out a result set to a .csv file from MySQL into the local file system. The problem (initially, anyway) was that I was logged into MySQL, ran the command, which worked without complain.. but when I went to look in the /tmp directory for my file… nada.

So, tried the command again just to see what would happen. Now MySQL complained (quite rightly) that the file already existed, yet I didn’t see it.

At this point the problem was that this particular machine I was on had a newer Fedora Linux install on it and one of the changes made was to add private tmp directories. It looks like that feature was added in Fedora 16 and they expanded the list of services that by default use private tmp directories in Fedora 17. Basically a subdirectory under /tmp will get created with some random string, and that’s where, in this case, my file was getting written to. There’s definitely a logic to that, but if you weren’t aware of that and/or didn’t have that in mind it is an easy thing to miss. And truth be told other folks have written about this in various places (such as this discussion post and this blog post I found later.) At least it wasn’t just me…

Initially I didn’t realize the problem of the private tmp directories though. My next step was just to try to write out my .csv file to a couple of other directories on the system. I set up a /mytmp directory and /usr/local/mytmp with appropriate permissions, but MySQL complained that it couldn’t write to the system. Huh? At this point I was confused… what was going on?

So, problem number two was that I didn’t realize SE Linux was enforcing limitations on where services, such as the MySQL daemon, could write to. This message gets logged to /var/log/messages as well, which is how I identified what was going on here. (This too has been commented on in this discussion post and elsewhere.)

In this particular case, the solution for me was to put SELinux into permissive mode, which then allowed MySQL to write out the my file somewhere I could have another script pick it up.

At the end it was clear that these two problems, although separate, did converge in my particular use case. This is an example of Apparently pathological behavior in systems is the result of different components doing exactly what they are supposed to do. Once you understand each component and its behaviors the system makes more sense.