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

[The Natural Philosopher]

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?

[The Natural Philosopher]

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

[Jerry Stuckle]

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

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

[The Natural Philosopher]

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
if ($filename=="" || $filesize==0) // skip emptiness.
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);
}
}

[Jerry Stuckle]

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?

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

[The Natural Philosopher]

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?

It cannot be permissions as simply COPYING the file somewhere else first
works.,

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

[The Natural Philosopher]

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

[Jerry Stuckle]

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.
js*******@attglobal.net
==================

[The Natural Philosopher]

Jerry Stuckle wrote:

The Natural Philosopher wrote:

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

I did see a sort of hint of this on the php.net site under the
move_uploaded bit..one person said 'but PHP isn't actually writing the
file till it exits' and another said 'wrong: I watched a 110Mbye fie
grow during upload.'

If php caches about 8 Mbytes they would both be correct.

I'll see if I can file a query.