Posted by The Natural Philosopher on 09/04/07 13:04

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++)
>> {
>> $index="new_file".$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
>> $filedescription=$_POST[$index];
>> copy($tmpname,"/tmp/foo"); //otherwise you get a null content
>
> // You should be using
> move_uploaded_file($tmpname,'/tmp/foo/'.basename($filename));
> // 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
>
>> continue;
>> // one supposes one has a file at this point..massage the name
>> into just the filename without the slashes
>> $filename=basename($filename);
>> $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') ",
>> $project_id,
>> date('Y-m-d'),
>> $employee_id,
>> $filesize,
>> $filedescription,
>> $filename,
>> "/tmp/foo");
>> mysql_query($query);
>> }
>> }
>
> // 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.

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.

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.

2/. use of
move_uploaded_file($tmpname,"/tmp/foo");
$query=.......LOAD_FILE("/tmp/foo");

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

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.

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.

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.

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

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.

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..

[Back to original message]