Re: I am totally stumped..with this on..LOAD_FILE Mysql+PHP= FSCK!!

The Natural Philosopher wrote:
Jerry Stuckle wrote:
The Natural Philosopher wrote:
Jerry Stuckle wrote:
The Natural Philosopher wrote:
Jerry Stuckle wrote:
The Natural Philosopher wrote:
The Natural Philosopher wrote:
This is so weird.

What I am trying to do is to upload files and stuff them in a mysql database.

Everything works except the file content is zero.

using the load_file command from mysql command line as 'root' works and i can download the inserted file ok.

using the load_file command from mysql as 'web-user' (my default web user?) didn't work until I gave that user every permission I could find, and *restarted mysqld*. (why? *shrug*)

using the load_file command from php/Mysql to load the temporary files fails miserably all the time. Not one success. Everything else is fine..I get the name and the size coming through,and I've 'echo'ed the command string that goes to mysql_query() and tried it in the command line stuff, and that works provided I give it a file that actually exists.

If the file does NOT exist then I get no error, just no data..

So that is a possibility..

Ah. I copied the temporary file to somewhere else (/tmp/foo), and then handed it to MySQL..THAT WORKED..

So it's something about how the temporary file is - or isn't - being written to disk maybe.

Is there a way to force a close on the file..maybe that's the problem Mysql is opening a file that is not flushed to disk maybe?

Mmm I tried move_uploaded_file() and THAT didn't work either.

Something is badly broken/misconfigured in PHP I think.
I gew the feeling its maintaining its own picture of file objects, and doesn't actually flush to the disk unless you do a copy or close php..

No, PHP doesn't maintain it's own copy. However, it's possible the OS hasn't flushed a file to disk. Unlikely, though.

This sounds very much like a permissions problem - does MySQL have read access to the directory the file is in, also?

Of course, with no code, everything's a guess...

// Yawn bugger, Files. File data should be stored in the $_FILES[] array, so let's start with the the new ones..
for ($i=0;$i<10;$i++)
$filename= $_FILES[$index]["name"]; //orig filename
$filesize= $_FILES[$index]["size"]; // the size in bytes of the uploaded file
$tmpname=$_FILES[$index]["tmp_name"]; // the name of the temporary copy of the file stored on the server
$index="new_description".$i; // where new file decscriptors are stored
copy($tmpname,"/tmp/foo"); //otherwise you get a null content

// You should be using
// If it doesn't work, look for the error message!

if ($filename=="" || $filesize==0) // skip emptiness.

// You should do this before trying to move the file

// one supposes one has a file at this point..massage the name into just the filename without the slashes
$query=sprintf("insert into project_files set project_id='%s',current='yes', date='%s' ,user='%d', size='%d', description='%s', name='%s', content=LOAD_FILE('%s') ",

// Does the webserver user have FILE privileges? And how big is the file? What's max_allowed_packet set to in your mysql configuration?

I see you are having your usual comprehension problems Jerry.

No, I'm not.

It DOES work so all the above are OK. Its the exceptions that are relevant. Files were in general small, for test purposes. <100KB.

Post data size and file size are set to 16Mbyte.

But I asked about max_packet_size in MySQL's configuration.

16Mbytes IIRC.

That shouldn't be a problem, then.

The program is *capable* of inserting correctly, even using LOAD_FILE, so its not a permissions or MySQL issues.

The only difference between working an not working is that :-

1/. use of $tmpame as an argument to LOAD_FILE does NOT work. Empty file.

As I said - (

I don't remember you saying that..

you should first use move_uploaded_file() to move the
uploaded file to another directory before you do anything else with it.

I tried., It doesn't work

"It doesn't work" isn't very clear. Exactly what doesn't work? Does the file not get created? Is it empty? Is the results of the LOAD_FILE() a zero length file?

2/. use of

hangs the client? server?...and/or leaves an empty file. I forget which. It was late.

Try moving it out of the /tmp directory. I don't leave them there any longer than necessary. Things there have a tendency to get deleted.

Wel if I copy it to a new file there, it doesn't.

Depending on how the OS is set up, it could get deleted there, also. I just don't trust /tmp for anything other than very short requests. When I used shared hosting I saw files "disappear" on me.

Since going to VPS's, I haven't had that problem. But that's because I know how everything's configured :-)

3/. copy ($tmpname,"/tmp/foo") ; LOAD_FILE("/tmp/foo") works perfectly.

My conjecture is that either PHP is exiting and deleting the file before SQL has has a chance to grab it..but that doesn't account for the failure of move_uploaded_file(), or what seems most likely is that PHP gets the file in the form of a POST stream, holds it as such internally, creates the filename, but does NOT actually write the data to disk. Unless its a very large file.

PHP shouldn't be exiting before the mysql_query() call completes. And I believe the file isn't written to /tmp by PHP; it's done by the server.

No. Its php. It has the name php embeded in its filename ;-)

That could be true - I haven't paid attention the the tmpname in so long - I just know it's there and I move it.

I can see you actually understand this less han I do.

Hey, I'm just trying to help. If you want to be insulting about it, I'll stop.

What it does is hold it until..

1/. It gets a move_uploaded_file() when it simply deletes the dir. entry and makes a new one, BUT DOES NOT FLUSH THE DATA TILL THE PROGRAM CLOSES.

Yes, once you call move_uploaded_file(), PHP will delete the temporary file. But the file should be copied immediately; many times I've used files after moving them. But as I said, I don't leave them in /tmp; I move them to a working directory then delete them when I'm done if necessary.

But did you access them from an external program WHILE THE PHP PROGRAM WAS STILL RUNNING?

As I said - I don't remember offhand. I'd have to go back and look at some of that code.

2/. Get's a call to do something physical with the file, like copy() it, in which case it actually creates the new file from te existing stream.

That's possible, but I don't think so. I've inserted files into MySQL before. But I'd have to go back and check if I used LOAD_FILE() or just inserted it from a PHP string. I don't recall off hand, but it would be different processing.

It works fine from the string, but i didn't want to addslashes to a 16Mbyte file.

OK, that's something then. And I understand why you don't want to do it to a 16MB file (BTW - you should use mysql_real_escape_string() instead - but same point). But it helps with the diagnosis.

3/. Exits, in which case it flushes its caches to disk, and deletes $tmpname.

That would also be true if it were the OS buffering the stream.

But if it was the OS, that would arbitrate the call to the file by Mysqld. And all would work. My contention is that PHP is holding a buffer above the OS layer - its acting like a little OS in its own right. So that any file actions WITHING PHP work as normal, but unil its finished, the external file is indeterminate.

You would the OS would catch that, and in a perfect world, it would. You're right, PHP might be caching it - but I wouldn't expect it to cache huge files. More likely it would cache part of it but not close the file; the OS, seeing this, might not allow mysql to read the file.

I.e. the actual temporary file only exists as a 'holding place' and its data is not guaranteed during the PHP execution AS FAR AS ANOTHER PROGRAM is concerned. From PHP's point of view it exists, and can be accessed correctly by any PHP function.

But it would take a lot of memory, especially if it's a large file.

Virtual memory, swapped out if necessary. Heck Ive got 512Myte on this and this is all that running. PHP's buffers are about 8Mbytes so its unlikely to be worried by that.

It doesn't do a whole lot of good to have buffers if you're swapping them to disk all the time, would it?

if the system crashed before you exited, you would lose data.

Thats nearly always true of anything.

course, the same would be true if it were left in /tmp.

It doesn;t get left in /tmp..well the last one does, but who cares? its in Mysql by that time.

And if it did do it this way, I would consider it a bug. It's not at all unusual to have other programs access the file before exiting. Not just MySQL, but things like exec'ing a program to do something with it.

I thik in MOST cases php is fine, becasue MOST file actions are not handled this way.

But in the case of a POST opeartion with a file, the stream of the file is uploaded to apache , which pipes it via stdin probably to the php stuff. So it isn't actually IN a file. Its in an input stream

Let's say you have a '$filecontents' type variable. Full of wahtever.
You make a temporary name, $tmpname, and serve it up to the user in POST variable.

Your internal libray Fopen() isused to open a stream to the correct temporary file (or not: You may simply delay that)

you EXPECT the user to do a move_uploaded_file(), at which point you simply change your internal file name. You don't bother to actually write the stream, because it takes time to write it, and rename it, possibly copying it, if its across partition boundaries, as well. No, its better to simply register the fact hat the user wants it over there==>> and wait till you have finished the program, then open the file for real, dump the contents in it and exit. Or if he file hasn't been move_uploaded_file()ed simply discard the memory (string) it's in.

That could be, as long as it's on the same physical disk.

This is actually clever, and probably makes PHP blindingly fast.but screws up in this instance..what I was hoping for as a call like 'flush all caches to disk' which would have meant there was good data there to hand over to SQL.

I suspect I will have to go with copy() and use a randomly generated own temporary filename to avid possible session collisions.

What a hack..

I wonder what would happen if you tried to just fopen() and fclose() the file before calling MySQL.

Now that is the first really sensible thing you have said. and fflush it too.

I will if I get a chance, try that.

However IF PhP IS maintaining its own 'meta-OS' that still won't work. Only by creating a true copy, do I force an actual disk write..and even that is a bit surprising. If PHP is delaying disk writes, i'd expect it to do it for all of them, but maybe they decided that a copy operation was one where they didn't want to use buffers for stuff that by implication, would not be needed later.

Who would be the person to contact at the php maintainers site?

To me it looks like a bug. I looked at the bugs list back to '04 and didn't see anything on it, but I also admit I didn't do a real thorough search.

I'd recommend reporting it in the bugs database. Include a short script to show the failure.

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.