zabbix-server 2.2.11
MySQL 5.6

Attached UTF-8 message to reproduce the issue.
Reproduced on 2.2 UTF-8 messages limit is calculated incorrectly.

Actual:
More than 65,535 bytes are allowed for insert into database if there are UTF-8 characters.

Expected:
Limit is 65,535 bytes no matter if it's UTF-8 or not.

A TEXT column with a maximum length of 65,535 (216 − 1) characters. The effective maximum length is less if the value contains multibyte characters. Each TEXT value is stored using a 2-byte length prefix that indicates the number of bytes in the value.

I think ZBX-7822 is highly related.

FYI :

The default SQL mode in MySQL 5.6.6 and later is NO_ENGINE_SUBSTITUTION; in MySQL 5.6.5 and earlier, it was empty (no modes set).

and this,

As of MySQL 5.6.11, strict mode produces an error for attempts to create a key that exceeds the maximum key length. Previously, this resulted in a warning and truncation of the key to the maximum key length (the same as when strict mode is not enabled).

Source : https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html

This issue is very similar to https://support.zabbix.com/browse/ZBX-8328 but in this case it's mysql that needs the same fix. (bytes are limit, not characters)
Function was first added in r42385.

fixed in development branch:
svn://svn.zabbix.com/branches/dev/ZBX-11655

Successfully tested.
vso REOPENED

(1) These functions are identical:

char	*zbx_db_dyn_escape_string(const char *src);
char	*zbx_db_dyn_escape_string_size_len(const char *src, size_t max_bytes, size_t max_chars);

Need to merge into one.

vso RESOLVED in r65063, r65067

s.paskevics CLOSED

(2) Pointless macros:

#if defined(HAVE_MYSQL)
#	define ZBX_DB_ESC_CH	'\\'
#elif !defined(HAVE_POSTGRESQL)
#	define ZBX_DB_ESC_CH	'\''
#endif

Macros ZBX_DB_ESC_CH is not used for MYSQL version.

vso Sorry but isn't it used here ?

#if defined(HAVE_POSTGRESQL)
			*d++ = *s;
#else
			*d++ = ZBX_DB_ESC_CH;
#endif

s.paskevics Yes, it is used, but necessary to increase readability.
I propose to delete it and replace with:

#if defined(HAVE_POSTGRESQL)
			*d++ = *s;
#elif defined(HAVE_MYSQL)
			*d++ = '\\';
#else
			*d++ = '\'';
#endif

vso RESOLVED in r65100,65101

s.paskevics CLOSED

Successfully tested.
vso REOPENED, more improvements are requested.

(3) DBdyn_escape_string_len shall be replaced to function that takes table and field name as parameters and calculates length.

Currently developer need to hard code maximum field length but this can be error prone and does not give information about limit in bytes for mysql.

Expected:
Developer does not need to specify length, it is taken from database schema.

vso RESOLVED in 65285-65299

s.paskevics CLOSED

(4) There is similar problem on Oracle - ZBX-11478.

vso made it a sub issue.

vso RESOLVED in r65312-65328

s.paskevics Looks good. CLOSED

(5) Necessary to replace

dbschema_esc = zbx_db_dyn_escape_string(dbschema, ZBX_MAX_UINT, ZBX_MAX_UINT, ESCAPE_SEQUENCE_ON);

with

dbschema_esc = DBdyn_escape_string(dbschema);

in scr/libs/zbxdb/db.c

Bytes and characters limits should not be defined in different locations

vso RESOLVED in r65480, yes I agree thanks for noticing, it looks better now.

s.paskevics CLOSED.

(6) get_field_size function works only with string types, necessary rename this function to a more informative.

vso RESOLVED in r65480

s.paskevics CLOSED.

Successfully tested

Fixed conflicts in:
svn://svn.zabbix.com/branches/dev/ZBX-11655
svn://svn.zabbix.com/branches/dev/ZBX-11655-3.0
svn://svn.zabbix.com/branches/dev/ZBX-11655-3.2
svn://svn.zabbix.com/branches/dev/ZBX-11655-trunk

Successfully tested

Available in
pre-2.2.17rc1 r65571.
pre-3.0.8rc1 r65572.
pre-3.2.4rc1 r65573.
pre-3.3.0 (trunk) r65574.

(7) Trunk is broken for PostgreSQL:

db.c: In function ‘zbx_db_connect’:
db.c:549:18: error: too few arguments to function ‘zbx_db_dyn_escape_string’
   dbschema_esc = zbx_db_dyn_escape_string(dbschema);
                  ^
In file included from db.c:22:0:
../../../include/zbxdb.h:115:8: note: declared here
 char  *zbx_db_dyn_escape_string(const char *src, size_t max_bytes, size_t max_chars,
        ^

vso Thanks for noticing, RESOLVED in
pre-3.0.8rc1 r65641
pre-3.2.4rc1 r65642
pre-3.3.0 (trunk) r65643

Successfully tested

(8) [I] Database patch can't depend on the current database scheme. Such patches can be broken in future.
v2.2

Index: branches/2.2/src/libs/zbxdbupgrade/dbupgrade.c
===================================================================
--- branches/2.2/src/libs/zbxdbupgrade/dbupgrade.c      (revision 65570)
+++ branches/2.2/src/libs/zbxdbupgrade/dbupgrade.c      (revision 65571)
@@ -2103,7 +2103,7 @@
 
                if (0 != strcmp(name, row[1]))
                {
-                       name_esc = DBdyn_escape_string_len(name, 255);
+                       name_esc = DBdyn_escape_field("scripts", "name", name);
 
                        if (ZBX_DB_OK > DBexecute("update scripts set name='%s' where scriptid=%s", name_esc, row[0]))
                                ret = FAIL;

v3.0

@@ -535,7 +534,7 @@
                else
                        zbx_snprintf_alloc(&name, &name_alloc, &name_offset, "{$N%d_%s", nodeid, row[1] + 2);
 
-               name_esc = DBdyn_escape_string_len(name, field_length);
+               name_esc = DBdyn_escape_field(table_name, field_name, name);
 
                if (ZBX_DB_OK > DBexecute("update %s set %s='%s' where %s=" ZBX_FS_UI64,
                                table_name, field_name, name_esc, key_name, id))

vso RESOLVED in:
svn://svn.zabbix.com/branches/dev/ZBX-11655-2.2 r65668-65674
svn://svn.zabbix.com/branches/dev/ZBX-11655-3.0 r65669-65678
svn://svn.zabbix.com/branches/dev/ZBX-11655-3.2 r65670-65680
svn://svn.zabbix.com/branches/dev/ZBX-11655-trunk r65671-65681

s.paskevics CLOSED

(9) [PS] Extra lines

src/libs/zbxdbhigh/db.c

@@ -2461,6 +2483,8 @@
        zbx_vector_ptr_destroy(&fields);
 }
 
+
+
 /******************************************************************************
  *                                                                            *
  * Function: zbx_db_insert_add_values_dyn                                     *

vso RESOLVED in:
svn://svn.zabbix.com/branches/dev/ZBX-11655-2.2 r65668-65674
svn://svn.zabbix.com/branches/dev/ZBX-11655-3.0 r65669-65678
svn://svn.zabbix.com/branches/dev/ZBX-11655-3.2 r65670-65680
svn://svn.zabbix.com/branches/dev/ZBX-11655-trunk r65671-65681

s.paskevics CLOSED

(10) [PS] We can't include high-level into low-level library.

Index: branches/2.2/src/libs/zbxdb/db.c
===================================================================
--- branches/2.2/src/libs/zbxdb/db.c    (revision 65570)
+++ branches/2.2/src/libs/zbxdb/db.c    (revision 65571)
@@ -20,6 +20,7 @@
 #include "common.h"
 
 #include "zbxdb.h"
+#include "db.h"
 #include "dbschema.h"
 #include "log.h"
 #if defined(HAVE_SQLITE3)

vso RESOLVED in:
svn://svn.zabbix.com/branches/dev/ZBX-11655-2.2 r65668-65674
svn://svn.zabbix.com/branches/dev/ZBX-11655-3.0 r65669-65678
svn://svn.zabbix.com/branches/dev/ZBX-11655-3.2 r65670-65680
svn://svn.zabbix.com/branches/dev/ZBX-11655-trunk r65671-65681

s.paskevics CLOSED

Fixed in:
pre-2.2.17rc1 r65698
pre-3.0.8rc1 r65699
pre-3.2.4rc1 r65700
pre-3.3.0 (trunk) r65701

It caused a regression in 3.0, 3.2 and 3.3.0 (trunk)

It looks like carriage return skipping was removed in ZBX-8450 but only for 3.0 so when merging 2.2 we reused same function that skips carriage returns to calculate length.

So in the result carriage return is skipped when calculating length as it is expected to be skipped during escaping.
But in newer version carriage return is not skipped, this results in string being truncated by carriage return count.

Info: as you know, some problem remains after this revision. (only affects in 3.0.8, 3.2.4)
-> ZBX-11855 was fixed in Zabbix 3.0.9, 3.2.5