can't load images into database

[acceleratebiz]

I'm trying to install the latest beta of Zabbix and can't install the images.sql rows. I keep getting errors with that load_file string function with mysql as it always returns null no matter what I do. Here's the description in mysql docs:
LOAD_FILE(file_name)

Reads the file and returns the file contents as a string. The file must be located 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 bytes.

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


zabbixdbuser (my mysql user) has File_priv = 'Y':
mysql> select * from user where User='zabbixdbuser';
+-----------+--------------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv |
+-----------+--------------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
| localhost | zabbixdbuser | 4b81fbf24265bc39 | N | N | N | N | N | N | N | N | N | Y | N | N | N | N |
+-----------+--------------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
1 row in set (0.00 sec)


I don't know how to check the size of "max_allowed_packet", but the default is supposed to be 1mbyte and I never changed that.


mysql> insert into images values(1,1,"Hub",load_file("/home/zabbix/zabbix/create/data/images/Hub.png"));
ERROR 1048: Column 'image' cannot be null
mysql> \q
Bye
[zabbix@manager data]$ ls -lh /home/zabbix/zabbix/create/data/images/Hub.png
-rw-r--r-- 1 zabbix zabbix 1.9K Jul 29 2004 /home/zabbix/zabbix/create/data/images/Hub.png


Oh and my mysql version is 3.23.58.

[James Wells]

Greetings,

Right off the bat, it appears that you did not edit the images.sql to correct the path to the image files. Please look at the images.sql and make sure the full path and file name is correct for where the files on your server.

[acceleratebiz]

I did try modifying the paths. I used relative paths. I tried copying some images to the /tmp directory and reference /tmp/Hub.png, for example. Take a look at the end of my post:
mysql> insert into images values(1,1,"Hub",load_file("/home/zabbix/zabbix/create/data/images/Hub.png"));
ERROR 1048: Column 'image' cannot be null
mysql> \q
Bye
[zabbix@manager data]$ ls -lh /home/zabbix/zabbix/create/data/images/Hub.png
-rw-r--r-- 1 zabbix zabbix 1.9K Jul 29 2004 /home/zabbix/zabbix/create/data/images/Hub.png

my directory structure is the same as the example entries in the images.sql file. You can see this by my "ls -lh" statement.

[James Wells]

Arrrggg. Sorry, teach me to try to answer posts without having my 4th cup of cofee... ;)

What version of Zabbix are you using please. It is looking like there is a schema mis-match here.

[acceleratebiz]

I'm using the latest 1.1beta, I just downloaded it yesterday

[James Wells]

Hmmm, okay, the schema is correct. Try this please, replace all instances of double quotes around the path/filename with single quotes and try to load it again.

Additionally, please check to make sure that the mysql user and the user you are running the mysql client as has the ability to read the image files.

[acceleratebiz]

hmm, good idea about the quotes, but didn't work :(
mysql> insert into images values(1,1,'Hub',load_file('/home/zabbix/zabbix/create/data/images/Hub.png'));
ERROR 1048: Column 'image' cannot be null
mysql>
From my first post on this thread, I copied the load_file() string function from mysql docs. I showed (I think?) that I have the file privileges for zabbixdbuser. Am I correct? I'm no expert in mysql...

[James Wells]

From my first post on this thread, I copied the load_file() string function from mysql docs. I showed (I think?) that I have the file privileges for zabbixdbuser. Am I correct? I'm no expert in mysql...
Sorry, who is the MySQL server running as, and who are you executing the MySQL client as? Not the database user, but the unix system users...

[Alexei]

Perhaps my notes in ZABBIX Manual will shed some light: http://www.zabbix.com/manual/v1.1/install_server.php

[acceleratebiz]

Hi James, the mysql server is run under root and the user is "zabbix", a new user I created just for this. I untarred the application under the /home/zabbix directory.

Alexei, thanks for chiming in. I followed these instructions originally. The only relevant note I see is the following
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.
I actually tried entering the commands directly into the mysql client with every kind of path imaginable including /tmp. As you can see from my 'ls -lh' command, the Hub.png file which I tried inserting has read permissions for everyone.

[elkor]

Ok, as we appear to firmly be in the "nobody knows why this is happening" camp I'll chime in with some stupid stuff to check:

can you verify the table structure for images in mysql with a "describe images". I expect it to be correct but it can't hurt to double check.

also.... in your path to the images file, are there any directories which the mysql or zabbix SYSTEM users do not have access to?

i.e. can the mysql system user execute that same ls command.

also, are there any symlinks in the path? if it doesn't like relative paths maybe it doesn't like symlinks either....


edit: oh meh, you said you tried tmp.
does it work adding them through the frontend?

[Alexei]

Just a guess, perhaps MySQL is running in some secured (chroot?) environment?

[acceleratebiz]

Hi guys, a coworker figured it out! the problem was the max_allowed_packets setting was in the /etc/my.cnf file and needed to be set to some high value. Thanks for trying to help though I appreciate it.

never said I was a mysql expert :o

[Alexei]

Thanks for the hint. ZABBIX Manual updated.

[Khach]

Hi acceleratebiz? I have the same problem with 1.1beta2. What is the value you changed it to? I can't seem to find the setting in /etc/my.cnf.

I am running CentOS 4.1 with mysql-4.1.12-3.RHEL4.1

Here is my /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[Khach]

I add the following line to /etc/my.cnf

set-variable = max_allowed_packet=16M

and restart mysqld (service mysqld restart)
copy all the images to /tmp
chmod 777 -R /tmp

but still have the following error

[root@wa05ids data]# cat images.sql |mysql -u root -p zabbix
Enter password:
ERROR 13 (HY000) at line 24: Can't get stat of '/tmp/Hub.png' (Errcode: 13)

[root@wa05ids data]# ls -al /tmp/Hub.png
-rwxrwxrwx 1 root root 1960 Oct 27 12:08 /tmp/Hub.png

[acceleratebiz]

Here is my /etc/my.cnf file:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable=max_allowed_packet=100M


[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[acceleratebiz]

[root@wa05ids data]# cat images.sql |mysql -u root -p zabbix
Enter password:
ERROR 13 (HY000) at line 24: Can't get stat of '/tmp/Hub.png' (Errcode: 13)

[root@wa05ids data]# ls -al /tmp/Hub.png
-rwxrwxrwx 1 root root 1960 Oct 27 12:08 /tmp/Hub.png
no idea what that error is all about, sorry. Not experienced with mysql myself.

[lofouk]

Hi,

I ve got the same trouble when i begin the installation of Zabbix

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

I create a user with the same privilege of the ROOT user

i am using Fedora Core 4.
Mysql 4.1.11

you know what is wrong ?

Regards

lofouk

[felipegeek]

I had exactly the same problem with the 'images.sql' part. I, like most users, put the Zabbix application in /home/zabbix. By default when the 'zabbix' user was created with 'useradd' the /home/zabbix folder was assigned '700' rights so no other user could read the contents of the '/home/zabbix' folder. After granting '755' rights it worked just fine. Another note: After extracting the current zabbix tar image make sure to rename the directory that was created which has the version number information on it to just 'zabbix'.

BTW: This happened to me on CentOS 4.2 (RHEL4 GPL variant)

-felipe