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

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.

16Mbytes IIRC.

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

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.

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 ;-)

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

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?

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.

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.

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.

And

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

Thats nearly always true of anything.

Of

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.

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.