Posted by Jerry Stuckle on 09/04/07 14:46

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++)
>>> {
>>> $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.
>

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.

> 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 - you should first use move_uploaded_file() to move the
uploaded file to another directory before you do anything else with it.

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

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.

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

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

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

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

> 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. And
if the system crashed before you exited, you would lose data. Of
course, the same would be true if it were left in /tmp.

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.

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

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]