LOAD_FILE doesn't work

[rneve]

Hello,

I try to load a file into the SQL DB, using load_file() function. But this doesn't work.

Database definition:
Field Type Attributes Null Default
ID int(8) No
server_id int(4) Yes NULL
date date Yes NULL
inventtext longblob BINARY Yes NULL

SQLcommand:
mysql> insert into inventory values('', '858', '2008-01-16', LOAD_FILE("/import/tv.20080116"));

The file has the following security settings:
-rwxrwxrwx tv.20080116 (116 KB)

When I execute the command I don't get an error, but are filled like this:
ID server_id date inventtext
242 858 2008-01-16 [BLOB - NULL]

Can anybody tell me what I'm doing wrong.

Thanks in advance

 

[elsenorjose]

From the docs:

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.

mysql> UPDATE t
SET blob_col=LOAD_FILE('/tmp/picture')
WHERE id=1;

Before MySQL 3.23, you must read the file inside your application and create an INSERT statement to update the database with the file contents. If you are using the MySQL++ library, one way to do this can be found in the MySQL++ manual, available at

http://tangentsoft.net/mysql++/doc/.

Have you verified all that?

 

[rneve]

Hello,

It is not a permission issue (at least not a file persmission).

I have all privileges on the database:
mysql> show grants;
GRANT USAGE ON *.* TO 'nl35553'@'localhost' IDENTIFIED BY PASSWORD '*A5A2767F4772A6A699116F409DC950FA70F58FB7'
GRANT ALL PRIVILEGES ON `apar`.* TO 'nl35553'@'localhost'
GRANT ALL PRIVILEGES ON `backups`.* TO 'nl35553'@'localhost'
GRANT ALL PRIVILEGES ON `cmdb`.* TO 'nl35553'@'localhost'
GRANT ALL PRIVILEGES ON `monitor`.* TO 'nl35553'@'localhost'
GRANT ALL PRIVILEGES ON `cmdb`.`cmdb` TO 'nl35553'@'localhost'

I want to load the file into database cmdb

My variabele max_allowed_packet is set to 1048576.
So this should be able to load a file with the size of 116KB.

Please advise

 

[LucL]

Hi,

So you're not getting an error but the LOAD_FILE always returns a NULL/blank?

I've had this problem before. In my situation it was caused by two things, one, the file needs to be on the MYSQL SERVER and mySQL server must have rights to it. If you're running the command from a client that is not on the server it won't work.

Additionally, try changing the owner of the file to "mysql".

Finally, try this in your mysql client:

SELECT LOAD_FILE("YOURFILE");

And see what it outputs, see if there are any warnings with "SHOW WARNINGS";

Good luck,
Luc L.