Loading map images

[rogerp]

Hi!

Ive been trying forever to get the map images loaded into my mysql-3.23.58 database. The error "image column cannot be null" is driving me crazy here.

I guess this means that the LOAD_FILE function did not work and that it doesnt load anything into the image column.

Ive tried everything i could find on the forums, including copying the images to /var/lib/mysql. Ive changed images.sql, added the full path to the images. Ive changed the permissions on the images to 777 to make them readable to everyone. ive tried manually adding a line to the images table and then doing

UPDATE images SET image = LOAD_FILE("/tmp/Hub.png") WHERE imageid = 1;

This returns OK, 0 rows updated, 1 warnings.

Im totally lost here.

Any ideas?

[Alexei]

Please, do a mysqldump of structure of table 'images'. I believe the structure is not correct.

[rogerp]

Mysql Dump

Thanks,

Here is the requested info:

-- MySQL dump 8.23
--
-- Host: localhost Database: zabbix
---------------------------------------------------------
-- Server version 3.23.58

--
-- Table structure for table `images`
--

CREATE TABLE images (
imageid int(4) NOT NULL default '0',
imagetype int(4) NOT NULL default '0',
name varchar(64) NOT NULL default '0',
image longblob NOT NULL,
PRIMARY KEY (imageid),
UNIQUE KEY imagetype (imagetype,name)
) TYPE=MyISAM;

--
-- Dumping data for table `images`
--


INSERT INTO images VALUES (1,1,'Hub','');

[Alexei]

Please, check that all conditions are met:

From MySQL docs:

LOAD_FILE(file_name) Reads the file and returns the file contents as a string.
The file must be on the server, you must specify the full pathname to the file,
and you must have the FILE privilege. The file must be readable by all and be
smaller than max_allowed_packet. If the file doesn't exist or can't be read due
to one of the above reasons, the function returns NULL:

mysql> UPDATE tbl_name SET blob_column=LOAD_FILE("/tmp/picture") WHERE id=1;

[rogerp]

Solved: Thanks alot!

Thanks for your help!

I did a "GRANT ALL PRIVILEGES on zabbix.* to zabbix@localhost identified by '****';" and thought that included FILE privileges (which i still find logical that it should).

Specifying "GRANT FILE ON zabbix.* to etc etc" solved the problem.

Thanks again, now its only the SNMP too long issue left, ill take a look at that now.

[sojeshj]

Hi,

I'm facing the same kinda issue. The table images is empty. When i'm trying to import the images, i get the error as follows:

ERROR 1048 at line 24: Column 'image' cannot be null

The structure of images table is as follows:

mysql> desc images;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| imageid | int(4) | | PRI | NULL | auto_increment |
| imagetype | int(4) | | MUL | 0 | |
| name | varchar(64) | | | 0 | |
| image | longblob | | | | |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

I am using the command as follows to import the images:

cat images.sql | mysql -uzabbixuser -pMypass zabbix

(I get the error ERROR 1048 at line 24: Column 'image' cannot be null)

If i issue the following command:

mysql zabbix < images.sql

I get the following error:
ERROR 1045: Access denied for user: 'zabbixuser@localhost' (Using password: NO)

Please let me know why I am not able import images to the table "images"?
----------
Thank you,
Soj

[chaag]

GRANT syntax

For the record, what worked for me was:
mysql> grant file on *.* to 'zabbix'@'localhost';

using:
mysql Ver 12.22 Distrib 4.0.24,

On Debian

[tifrere]

For the record, what worked for me was:
mysql> grant file on *.* to 'zabbix'@'localhost';

using:
mysql Ver 12.22 Distrib 4.0.24,

On Debian

I got de same problems and searched for a while before understanding it was this issue I had (thanks google to drive me to this post).

As I create a dedicated user for zabbix utilization and I allow him to do queries only on zabbix database, I did not have the right to do ant FILE request (like load_file).

My advise is to add this notice in your installation note :

Note: Unfortunately MySQL does not support relative path in statement load_file(). You may want to edit images.sql to replace locations of image files. Also make sure that MySQL super-user (usually mysql) has read access to these files. If you still have any problems, copy images to directory /tmp, assign read permissions, change load_file() in images.sql, and execute images.sql. Additionally you may need to increase max_allowed_packets in MySQL configuration file.

Something like : "Also check that user as the FILE option granted in the mysql general privilege"


Thanks you ;-)

--
Pierre-Yves

[gpearson]

I am getting the following error after granting the user file_priv. The error is

ERROR 13 (HY000) at line 24: Can't get stat of '/root/zabbix/create/data/images/Hub.png' (Errcode: 13)

If I goto the path listed above, all of the png files are in this directory and have the owner of mysql. If I try to import this table as root I get the same error message.

[tifrere]

just for testing purpose, try to chmod 777 the images files to see if the error still occur.

[gpearson]

I changed the mod of the .png files to 777 and upon insert of the images.sql file I get the same error message about Can't get stat.

[gpearson]

I was able to solve this by going to the configuration images section and manually enter each of the images to the database.

[cameronsto]

Just for future reference as this issue seems to come up a lot, I have added what gpearson did to the wiki FAQ here.

Hopefully this will prevent some headaches down the line...

-cameron