Issue lies somewhere in between Zabbix server and IBM DB2 database and is encoding related. It is possible to reproduce the error message "Value ... is too long" for any text field.

IBM DB2 look into LANG environment variable of the client (Zabbix server or proxy in our case) to find out what encoding it should expect from client. Zabbix server always works in UTF-8 and does not care about LANG variable.

Let's assume LANG is set to POSIX for Zabbix server/proxy and the latter wants to insert a text containing non-ASCII characters. Zabbix server/proxy prepares a valid and properly truncated to fit into field width UTF-8 string. IBM DB2 gets this string, assumes that it is some kind of one-byte-per-character encoding and starts to convert it to UTF-8.

ASCII characters are fine, they stay as they are. But non-ASCII characters take up more than one byte in UTF-8 and every one of them starts with binary '1', therefore corresponds to a non-ASCII character in one-byte-per-character encoding (character code > 127) and therefore grows to 2 to 4 bytes in UTF-8.

This way every non-ASCII character can expand from 2-4 bytes in Zabbix server string to 4-16 bytes in IBM DB2 string. This may cause a lot of problems, "Value ... is too long" is just one of them.

So this becomes a documentation issue.

There is a set of locale environment variables, and they influence and override each other in a very interesting manner:

• http://ps-2.kev009.com/tl/techlib/manuals/adoclib/aixbman/baseadmn/localeen.htm
• http://www-01.ibm.com/support/knowledgecenter/ssw_aix_53/com.ibm.aix.nls/doc/nlsgdrf/locale_env.htm%23a131c11b
• https://www.gnu.org/software/gettext/manual/html_node/Locale-Environment-Variables.html
• https://www.gnu.org/software/gettext/manual/html_node/The-LANGUAGE-variable.html#The-LANGUAGE-variable

The most important for us is LC_CTYPE which

determines character handling rules governing the interpretation of sequences of bytes of text data characters.

If LC_CTYPE is not set explicitly, its value is borrowed from LANG variable. However, regardless of whether LC_CTYPE is set explicitly or not its value is overridden by LC_ALL.

$ locale | grep -E "LC_CTYPE=|LANG=|LC_ALL="
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_ALL=

$ LC_CTYPE=POSIX locale | grep -E "LC_CTYPE=|LANG=|LC_ALL="
LANG=en_US.UTF-8
LC_CTYPE=POSIX
LC_ALL=

$ LANG=POSIX locale | grep -E "LC_CTYPE=|LANG=|LC_ALL="
LANG=POSIX
LC_CTYPE="POSIX"
LC_ALL=

$ LANG=POSIX LC_CTYPE=en_US.UTF-8 locale | grep -E "LC_CTYPE=|LANG=|LC_ALL="
LANG=POSIX
LC_CTYPE=en_US.UTF-8
LC_ALL=

$ LC_ALL=POSIX LC_CTYPE=en_US.UTF-8 locale | grep -E "LC_CTYPE=|LANG=|LC_ALL="
LANG=en_US.UTF-8
LC_CTYPE="POSIX"
LC_ALL=POSIX

Documentation must say that Zabbix users should provide that locale environment variables are properly set for Zabbix server/proxy:

• LC_ALL should be set to xx.UTF-8 or unset;
• if LC_ALL is unset either LC_CTYPE should be set to xx.UTF-8 explicitly or LANG should be set to xx.UTF-8.

This can be achieved by editing system configuration files for "zabbix" user or by simply setting necessary environment variables in commands to run Zabbix daemons:

$ LC_ALL=en_US.UTF-8 zabbix_server ...

As we see, it is very critical for IBM DB2 where we have the strictest limit of 2048 bytes for text fields, but can play a role for other DB engines as well resulting in more space taken by non-ASCII characters than essentially required (this has to be investigated in more detail though).

Zabbix server/proxy environment variables does not seem to affect how non-IBM DB2 database server interprets text Zabbix server/proxy sends to it. For example, PostreSQL documentation says that

To enable automatic character set conversion, you have to tell PostgreSQL the character set (encoding) you would like to use in the client.

Related reading on this topic:

• MySQL
• PostgreSQL

For Oracle we set NLS_LANG=.UTF8.

zalex_ua As for Oracle, see also ZBX-7015.
btw, would be nice to fix it too, please discuss it with sasha

If my words did not convince you, see Chapter 5 (p. 49) of IBM DB2 10.5 for Linux, UNIX, and Windows - Globalization Guide

Fixed for version 2.2 in development branch svn://svn.zabbix.com/branches/dev/ZBX-10076-22 revisions 57098 (server) and 57116 (frontend).

Server will now set SQL_ATTR_CLIENT_CODEPAGE connection attribute to 1208 (IBM notation of UTF-8).

Unfortunately, it is not possible to set client codepage attributes on the connection level in PHP. That's why we utilize web server environment variables for this purpose. We try to setlocale() to one of the most common UTF-8 locales and issue an error if we fail.

(1) If environment variables were not set for Zabbix server and web server properly, non-ASCII text information is stored in the database incorrectly. This may cause problems after the fix and therefore has to be documented in Upgrade notes.

I'd suggest to fix this for 3.0 only as it involves upgrade notes documentation, db creation scripts documentation.
Also, I'd suggest to include ZBX-7015 fix to 3.0 too, as required documentation changes are very similar for both issues and would be good to do them together and unified.

(2) According to the documentation (not examples) it seems to be more correct using SQL_IS_UINTEGER rather than SQL_IS_POINTER when setting codepage attribute.

Please review, r57840, r57841.

glebs.ivanovskis Following documentation rigorously will definitely not hurt. Thanks, accepted.
CLOSED

Server side tested

(3) [F] Minor coding style improvements in r58270 and r58271

iivs CLOSED

(4) Success message "Application added", but there is an error in details:

SQL error [INSERT INTO applications (name,hostid,applicationid) VALUES ('āāāāāāāāāāāāāāāāāāāāāāēēēēēēēēēēēēēēēēēēēēēēēēasdioasdio asud 909sd8 āāāāāāāāāāāāāāāāāāāāāāēēēēēēēēēēēēēēēēēēēēēēēēāāāāāāāāāāāāāāāāāāāāāāēēēēēēēēēēēēēēēēēēēēēēēēāāāāāāāāāāāāāāāāāāāāāāēēēēēēēēēēēēēēēēēēēēēēēēāāāāāāāāāāāāāāāāāāāāāāēēēēēēēēēēēēēēēēēēēēēēē!!','10105','477')] in [[IBM][CLI Driver][DB2/LINUXX8664] SQL0433N Value "āāāāāāāāāāāāāāāāāāāāāāēēēēēēēē" is too long. SQLSTATE=22001]

And error in footer:

SQL error [INSERT INTO auditlog (userid,clock,ip,action,resourcetype,details,auditid) VALUES ('1','1454595567','192.168.3.48','0','12','Application [āāāāāāāāāāāāāāāāāāāāāāēēēēēēēēēēēēēēēēēēēēēēēēasdioasdio asud 909sd8 āāāāāāāāāāāāāāāāāāāāāāēēēēēēēēēēēēēēēēēēēē...','38')] in [[IBM][CLI Driver][DB2/LINUXX8664] SQL0433N Value "Application [āāāāāāāāāāāāāāāāāāāāāāē�" is too long. SQLSTATE=22001]

And items...

SQL error [INSERT INTO items (name,description,key_,hostid,interfaceid,delay,history,status,type,snmp_community,snmp_oid,value_type,trapper_hosts,port,units,multiplier,delta,snmpv3_contextname,snmpv3_securityname,snmpv3_securitylevel,snmpv3_authprotocol,snmpv3_authpassphrase,snmpv3_privprotocol,snmpv3_privpassphrase,formula,trends,logtimefmt,valuemapid,delay_flex,authtype,username,password,publickey,privatekey,params,ipmi_sensor,data_type,inventory_link,flags,itemid) VALUES ('ksjdhhashd*@#!#*()SADQWEQWEQWAAAAAAAAAAAAAššššššššššššššššššššššēēēēēēēēēēēēēļļļļļļļļļļļļ!@!@()*$#ļļļļķšāšāāšāšēāšõīāīīõõšīāõīā–õģī9890adjuiahewķļļāšīūāāūēā!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!1231231ļūšāīūõāīšksjdhhashd*@#!#*()S','','asd','10105','2','30','90','0','0','','','3','','','','0','0','','','0','0','','0','','1','365','',NULL,'','0','','','','','','','0','0','0','23660')] in [[IBM][CLI Driver][DB2/LINUXX8664] SQL0433N Value "ksjdhhashd*@#!#*()SADQWEQWEQWAAAAAAAAAAAAAššššššššš" is too long. SQLSTATE=22001]

oleg.egorov MOVED to ZBX-10351

iivs CLOSED

Frontend side tested

(5) Since it was decided to fix the issue for trunk only, I've ported patch to new development branch svn://svn.zabbix.com/branches/dev/ZBX-10076 revision 58556. There was a conflict on frontend side. oleg.egorov, please review.

oleg.egorov CLOSED

Fixed in pre-3.1.0 (trunk) revision 58585.

Documented in:

• Upgrade notes for 3.2
• Database creation scripts for 3.0
• Database creation scripts for 2.4
• Database creation scripts for 2.2

sasha CLOSED