image-2020-08-25-16-48-32-114.png
[ZBX-18295] Unsupported charset or collation for tables Created: 2020 Aug 25 Updated: 2020 Aug 28 Resolved: 2020 Aug 28 |
|
| Status: | Closed |
| Project: | ZABBIX BUGS AND ISSUES |
| Component/s: | Frontend (F) |
| Affects Version/s: | 5.0.3 |
| Fix Version/s: | None |
| Type: | Incident report | Priority: | Trivial |
| Reporter: | Tomasz Kłoczko | Assignee: | Zabbix Support Team |
| Resolution: | Won't fix | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
php 7.4.9. Linux, MySQL 8.0.21 |
||
| Attachments: |
image-2020-08-25-16-48-32-114.png
|
| Description |
|
Steps to reproduce:
Result: |
| Comments |
| Comment by Tomasz Kłoczko [ 2020 Aug 25 ] |
|
Just checked what is in last listed table as definition:
mysql> SHOW CREATE TABLE widget_field\G;
*************************** 1. row ***************************
Table: widget_field
Create Table: CREATE TABLE `widget_field` (
`widget_fieldid` bigint unsigned NOT NULL,
`widgetid` bigint unsigned NOT NULL,
`type` int NOT NULL DEFAULT '0',
`name` varchar(255) NOT NULL DEFAULT '',
`value_int` int NOT NULL DEFAULT '0',
`value_str` varchar(255) NOT NULL DEFAULT '',
`value_groupid` bigint unsigned DEFAULT NULL,
`value_hostid` bigint unsigned DEFAULT NULL,
`value_itemid` bigint unsigned DEFAULT NULL,
`value_graphid` bigint unsigned DEFAULT NULL,
`value_sysmapid` bigint unsigned DEFAULT NULL,
PRIMARY KEY (`widget_fieldid`),
KEY `widget_field_1` (`widgetid`),
KEY `widget_field_2` (`value_groupid`),
KEY `widget_field_3` (`value_hostid`),
KEY `widget_field_4` (`value_itemid`),
KEY `widget_field_5` (`value_graphid`),
KEY `widget_field_6` (`value_sysmapid`),
CONSTRAINT `c_widget_field_1` FOREIGN KEY (`widgetid`) REFERENCES `widget` (`widgetid`) ON DELETE CASCADE ON UPDATE RESTRICT,
CONSTRAINT `c_widget_field_2` FOREIGN KEY (`value_groupid`) REFERENCES `hstgrp` (`groupid`) ON DELETE CASCADE ON UPDATE RESTRICT,
CONSTRAINT `c_widget_field_3` FOREIGN KEY (`value_hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE ON UPDATE RESTRICT,
CONSTRAINT `c_widget_field_4` FOREIGN KEY (`value_itemid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE ON UPDATE RESTRICT,
CONSTRAINT `c_widget_field_5` FOREIGN KEY (`value_graphid`) REFERENCES `graphs` (`graphid`) ON DELETE CASCADE ON UPDATE RESTRICT,
CONSTRAINT `c_widget_field_6` FOREIGN KEY (`value_sysmapid`) REFERENCES `sysmaps` (`sysmapid`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Which seems legit. In zabbix server log I see: 2911334:20200825:165043.891 current database version (mandatory/optional): 05000000/05000002 2911334:20200825:165043.891 required mandatory version: 05000000 2911334:20200825:165043.910 character set name or collation name that is not supported by Zabbix found in 285 column(s) of database "zabbix" 2911334:20200825:165043.910 only character set "utf8" and collation "utf8_bin" should be used in database 2911334:20200825:165043.914 database is not upgraded to use double precision values |
| Comment by Dmitrijs Lamberts [ 2020 Aug 26 ] |
|
Hello Tomasz, From your output looks like utf8_bin collation is not present. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin | |
| Comment by Tomasz Kłoczko [ 2020 Aug 26 ] |
|
So here we have bug in zabbix. mysql> SHOW CHARACTER SET; +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | binary | Binary pseudo charset | binary | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | cp866 | DOS Russian | cp866_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.00 sec)mysql> SHOW VARIABLES LIKE "%version%"; +--------------------------+-------------------------------+ | Variable_name | Value | +--------------------------+-------------------------------+ | admin_tls_version | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 | | immediate_server_version | 999999 | | innodb_version | 8.0.21 | | original_server_version | 999999 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 | | version | 8.0.21 | | version_comment | Source distribution | | version_compile_machine | x86_64 | | version_compile_os | Linux | | version_compile_zlib | 1.2.11 | +--------------------------+-------------------------------+ 12 rows in set (0.03 sec) As you see MySQL 8.0.21 does not have on list of suppoorted collations utf8_bin I've changed default collation of the zabbix database to utf8_general_ci ALTER DATABASE zabbix CHARACTER SET utf8 COLLATE utf8_general_ci; And now server in logs shows: 3187986:20200826:130521.422 Zabbix supports only "utf8_bin" collation. Database "zabbix" has default collation "utf8_general_ci" 3187986:20200826:130521.438 character set name or collation name that is not supported by Zabbix found in 285 column(s) of database "zabbix" |
| Comment by Kristians Pavars [ 2020 Aug 28 ] |
|
Hi kloczek SHOW CHARACTER SET clearly shows that you have utf8 charset which by default is using utf8_general_ci To see the available collations run the command below which will show you available collations for utf8 charset and utf8_bin should be in the list, if it's missing then please consult mysql documentation. Alternatively utfmb4 with utfmb4_bin can be used, as this has more bytes reserved (3 instead of 4)
mysql> show collation WHERE charset LIKE "utf8%";
+--------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |
| utf8_german2_ci | utf8 | 212 | | Yes | 8 |
| utf8_croatian_ci | utf8 | 213 | | Yes | 8 |
| utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 |
| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 |
| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 |
| utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 |
| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 |
| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 |
| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 |
| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 |
| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 |
| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 |
| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 |
| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 |
| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 |
| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 |
| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 |
| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 |
| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 |
| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 |
| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 |
| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 |
| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 |
| utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 |
| utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 |
| utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 |
| utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 |
+--------------------------+---------+-----+---------+----------+---------+
53 rows in set (0.00 sec)
Regards, |
| Comment by Tomasz Kłoczko [ 2020 Aug 28 ] |
|
> SHOW CHARACTER SET clearly shows that you have utf8 charset which by default is using utf8_general_ci
Yeah I found it today morning that everything is OK and "**utf8_bin" is on the list. however I'm stuck with switching to right collation :/ mysql> SELECT table_schema, table_name, column_name, COLLATION_NAME, COLUMN_TYPE FROM information_schema.columns WHERE collation_name != 'utf8_bin' AND table_schema not in ('information_schema','mysql', 'performance_schema','sys'); +--------------+----------------------------+-------------------------+-----------------+---------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME | COLUMN_TYPE | +--------------+----------------------------+-------------------------+-----------------+---------------+ | zabbix | acknowledges | message | utf8_general_ci | varchar(2048) | | zabbix | actions | name | utf8_general_ci | varchar(255) | | zabbix | actions | esc_period | utf8_general_ci | varchar(255) | | zabbix | actions | formula | utf8_general_ci | varchar(255) | | zabbix | alerts | sendto | utf8_general_ci | varchar(1024) | | zabbix | alerts | subject | utf8_general_ci | varchar(255) | | zabbix | alerts | message | utf8_general_ci | mediumtext | | zabbix | alerts | error | utf8_general_ci | varchar(2048) | | zabbix | alerts | parameters | utf8_general_ci | text | | zabbix | application_discovery | name | utf8_general_ci | varchar(255) | | zabbix | application_prototype | name | utf8_general_ci | varchar(255) | | zabbix | applications | name | utf8_general_ci | varchar(255) | | zabbix | auditlog | note | utf8_general_ci | varchar(128) | | zabbix | auditlog | ip | utf8_general_ci | varchar(39) | | zabbix | auditlog | resourcename | utf8_general_ci | varchar(255) | | zabbix | auditlog_details | table_name | utf8_general_ci | varchar(64) | | zabbix | auditlog_details | field_name | utf8_general_ci | varchar(64) | | zabbix | auditlog_details | oldvalue | utf8_general_ci | text | | zabbix | auditlog_details | newvalue | utf8_general_ci | text | [..] | zabbix | widget | type | utf8_general_ci | varchar(255) | | zabbix | widget | name | utf8_general_ci | varchar(255) | | zabbix | widget_field | name | utf8_general_ci | varchar(255) | | zabbix | widget_field | value_str | utf8_general_ci | varchar(255) | +--------------+----------------------------+-------------------------+-----------------+---------------+ 304 rows in set (0.05 sec) So that provides the list of columns which I must correct however When I'm trying go correct that something is wrong. mysql> ALTER TABLE widget_field CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; Query OK, 0 rows affected, 2 warnings (0.03 sec) Records: 0 Duplicates: 0 Warnings: 2mysql> ALTER TABLE widget_field MODIFY value_str varchar(255) SET COLLATE utf8_bin; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET COLLATE utf8_bin' at line 1 I'm not sure but probably only way will be do full text dump -> create empty database with proper default charset and collation and import all data with removed from text dump collation.
Nevertheless it is not bug in zabbix so fill free to close that ticket If someone can help on sorting that issue without dump -> import I'll be really appreciated.
|
