[#ZBX-12746] ERROR 1366 (HY000) at line 3390

Comment by dimir [ 2017 Sep 22 ]

Tried on latest Debian (stretch), cannot reproduce:

$ dpkg -l zabbix-server-mysql | grep ^ii
ii  zabbix-server-mysql 1:3.4.1-1+stretch amd64        Zabbix network monitoring solution - server (MySQL)
$ cd /usr/share/doc/zabbix-server-mysql
$ zcat create.sql.gz | mysql -u root zabbix
$ echo $?
0

For the information, line number 3390:

$ zcat create.sql.gz | sed -n '3390p'
INSERT INTO `items` (`itemid`,`type`,`snmp_community`,`snmp_oid`,`hostid`,`name`,`key_`,`delay`,`history`,`trends`,`status`,`value_type`,`trapper_hosts`,`units`,`snmpv3_securityname`,`snmpv3_securitylevel`,`snmpv3_authpassphrase`,`snmpv3_privpassphrase`,`formula`,`logtimefmt`,`templateid`,`valuemapid`,`params`,`ipmi_sensor`,`authtype`,`username`,`password`,`publickey`,`privatekey`,`flags`,`interfaceid`,`port`,`description`,`inventory_link`,`lifetime`,`snmpv3_authprotocol`,`snmpv3_privprotocol`,`snmpv3_contextname`,`evaltype`,`jmx_endpoint`,`master_itemid`) values ('27212','4','{$SNMP_COMMUNITY}','1.3.6.1.4.1.6527.3.1.2.2.1.8.1.18.{#SNMPINDEX}','10207','{#SNMPVALUE}: Temperature','sensor.temp.value[tmnxHwTemperature.{#SNMPINDEX}]','180','30d','365d','0','0','','°С','','0','','','','',NULL,NULL,'','','0','','','','','2',NULL,'','MIB: TIMETRA-SYSTEM-MIB\r\nThe current temperature reading in degrees celsius from this hardware component\'s temperature sensor.  If this component does not contain a temperature sensor, then the value -1 is returned.','0','30d','0','0','','0','',NULL);

Looks like UTF-8 cymbol °С is causing the issue. Are you sure you have created the database sa instructions suggest?

create database dbname character set utf8 collate utf8_bin;

Please provide the output of the following SQL command show variables like '%database'. It should be something like:

MariaDB [zabbix]> show variables like '%database';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| character_set_database | utf8     |
| collation_database     | utf8_bin |
| skip_show_database     | OFF      |
+------------------------+----------+
3 rows in set (0.00 sec)

Comment by dimir [ 2017 Sep 25 ]

vidas, please confirm if the above assumption is correct. We will close the issue assuming the above comment is correct if there will be no response in nearest future.

Comment by Glebs Ivanovskis (Inactive) [ 2017 Sep 28 ]

Looks like Cyrillic letter "С" (aka U+0421 aka \xd0\xa1) is causing the issue. And non-Unicode database character set, of course.

Comment by dimir [ 2017 Sep 28 ]

Thanks, glebs.ivanovskis. I'm closing the issue. vidas, feel free to re-open if you disagree.

Comment by Glebs Ivanovskis (Inactive) [ 2017 Sep 28 ]

I disagree. Latin letter "C" should be used for degrees Celsius. vzhuravlev, please fix templates.

Comment by dimir [ 2017 Sep 28 ]

Right, makes sense.

Comment by Vitaly Zhuravlev [ 2017 Sep 28 ]

updated xml files r73028

Comment by dimir [ 2017 Nov 13 ]

Templates will be fixed in ~~ZBXNEXT-4223~~ .

Comment by niaha [ 2017 Nov 30 ]

"Good news everyone" :

To temporary fix that just do :

1. gunzip /usr/share/doc/zabbix-server-mysql/create.sql.gz
2.sed "1727i\ALTER TABLE `items` CONVERT TO CHARACTER SET utf8;" /usr/share/doc/zabbix-server-mysql/create.sql (beware to escape backquotes... If you have some trouble, juste do it with Vim or (beurk)Nano )
3.echo "drop database zabbix;"|mysql -uzabbix -p zabbix
4.echo "create database zabbix;"|mysql -uzabbix -p zabbix
5.echo "grant all on zabbix.* to 'zabbix'@'localhost;'';"|mysql -uzabbix -p zabbix
6.cat /usr/share/doc/zabbix-server-mysql/create.sql |mysql -uzabbix -p zabbix

And it's done, give it a try. and post back.

See ya

[ZBX-12746] ERROR 1366 (HY000) at line 3390 Created: 2017 Sep 19 Updated: 2017 Nov 30 Resolved: 2017 Nov 21
Status:	Closed
Project:	ZABBIX BUGS AND ISSUES
Component/s:	Templates (T)
Affects Version/s:	3.4.1
Fix Version/s:	None

[ZBX-12746] ERROR 1366 (HY000) at line 3390 Created: 2017 Sep 19 Updated: 2017 Nov 30 Resolved: 2017 Nov 21