MySQL Bugs: #34966: Problems with LOAD

Bug #34966	Problems with LOAD_FILE
Submitted:	29 Feb 2008 22:45	Modified:	5 Mar 2008 20:39
Reporter:	Steven Lobbezoo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux (debian)
Assigned to:		CPU Architecture:	Any
Tags:	load_file

Description:
I use the LOAD_FILE as follows :

INSERT INTO Photos
SET sequence =0,
description = '',
thumb = LOAD_FILE( '/home/web/revimmo/intranet/photos/R 529/LOB999THUMB.jpg' ) ,
www = LOAD_FILE( '/home/web/revimmo/intranet/photos/R 529/LOB999WWW.jpg' ) ,
file_name = '/home/steven/R 529/vue3.jpg',
prop_num =2116

The files are existing, accessable and have good data in them.
Until yesterday this worked just fine. Since about 4 hours it does not anymore.
I changed NOTHING in my server. There is anough diskspace etc.
But I get NULL blobs, the rest of the record is ok.

So, I cannot figure it out anymore.
I tried to load these images with phpadmin into the blob fields, all is ok there.
It's just as if the LOAD_FILE stopped working for some reason.

My server was running for 127 days continuous. I tried a restart, same situation.

Regards,
Steven
 

How to repeat:
I just do the same again and again.
It does not change ;-)

Suggested fix:
Even when all the accès rights for my files are ok (xwrxwrxwr), Mysql seems not to accept them. I tried to move them elsewhere, same result. Some maybe we should be able to influance the accès rights ??????

Thank you for a problem report. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_load-file:

"LOAD_FILE(file_name)

Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full pathname to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes.

If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL."

So, please, send the results of:

show grants\G
show variables like 'max_allowed'_packet';

for the user you are connected as when use that LOAD_FILE() function. Sent also the results of:

ls -l "/home/web/revimmo/intranet/photos/R 529/"

The answers to your questions are in the connected file

Regards,
Steven

Answers to questions LOAD_FILE Problem

Attachment: answers_mysql-problem.txt (text/plain), 28.45 KiB.

Thank you for the feedback.

Could you also provide output of SHOW TABLE STATUS LIKE 'Photos' and error or warning message you get after issuing the query.

Sure, here it is :

mysql> SHOW TABLE STATUS LIKE 'Photos'
    -> \G
*************************** 1. row ***************************
           Name: Photos
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 11686
 Avg_row_length: 78543
    Data_length: 917862512
Max_data_length: 281474976710655
   Index_length: 242688
      Data_free: 284
 Auto_increment: 15864
    Create_time: 2007-10-22 21:24:16
    Update_time: 2008-02-29 23:47:16
     Check_time: 2008-02-29 22:05:34
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.14 sec)

mysql>

Hopes this helps,
Steven

Thank you for the feedback.

Do you get some error or warning when trying to insert these files? Please also provide output of SHOW CREATE TABLE Photos

Thank you for your questions :

No, I donnot get any error 
message (which in itself, I consider a little bug !

Sure here it is :
*************************** 1. row ***************************
       Table: Photos
Create Table: CREATE TABLE `Photos` (
  `id` int(11) NOT NULL auto_increment,
  `id_files` int(11) default NULL,
  `prop_num` int(11) NOT NULL default '0',
  `description` varchar(50) default NULL,
  `thumb` longblob,
  `www` longblob,
  `sequence` int(11) NOT NULL default '0',
  `file_name` varchar(100) default NULL,
  `sel_g` int(11) NOT NULL default '0',
  `sel_p` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `prop_num` (`prop_num`)
) ENGINE=MyISAM AUTO_INCREMENT=15864 DEFAULT CHARSET=latin1
1 row in set (0.05 sec)

Hi,

I don't know how to say this, but I have to confess, that the above bug was not existing.
As it happened, some junior at the company managing/hosting my server, thought it a good idea to change permissions on a directory in the intranet tree.
This directory is included in the path that leads up to photos !

My humble excuses for this stupid error. I'll probably will change pc-farm company. It caused our company almost a week of big problems with our work, so the last word is not spend about this.

Whatever, my sensere apologies,and thank you all a lot for your assistance.

Steven

Sorry forgot to change the status
Steven