Hot to force Asterisk to write date in UTF-8 charset?

Question

CentOS Linux release 7.6.1810 (Core)

Kernel: localhost.localdomain 5.0.7-1.el7.elrepo.x86_64

MariaDB: Ver 15.1 Distrib 10.4.4-MariaDB, for Linux (x86_64) using readline 5.1

Asterisk: 16.3.0 built by root

unixODBC 2.3.1

mysql-connector-odbc 8.0.15-1

---

I made a connection between my Asterisk and MariaDB to store cdr and cel data. Connection is ok and Asterisk can write into db. I want to use cyrillic cid_name, so I try to set UTF-8 charset anywhere.

But now, if I make test call, Asterisk writes cid_name to db as ISO-8859-1, not as UTF-8 data. Why? And how can I fix this?

CLI:

MariaDB [asteriskcdrdb]> select * from cel;
+----+--------------+---------------------+--------------------------+---------+---------+-----------+----------+-------+---------+------------------+------+------+---------+------------+---------+-----------------+----------+-------------+---------------+---------------+------+-------------+------------+-----------+
| id | eventtype    | eventtime           | cid_name                 | cid_num | cid_ani | cid_rdnis | cid_dnid | exten | context | channame         | src  | dst  | channel | dstchannel | appname | appdata         | amaflags | accountcode | uniqueid      | linkedid      | peer | userdeftype | eventextra | userfield |
+----+--------------+---------------------+--------------------------+---------+---------+-----------+----------+-------+---------+------------------+------+------+---------+------------+---------+-----------------+----------+-------------+---------------+---------------+------+-------------+------------+-----------+
|  1 | CHAN_START   | 2019-04-12 09:56:05 | �а�ана             | 123     |         |           |          | 123   | public  | SIP/123-00000000 | NULL | NULL | NULL    | NULL       |         |                 |        3 |             | 1555052165.0  | 1555052165.0  |      |             | NULL       |           |
|  2 | CHAN_START   | 2019-04-12 09:56:05 | �а�ана             |         |         |           |          | s     | public  | SIP/123-00000001 | NULL | NULL | NULL    | NULL       |         |                 |        3 |             | 1555052165.2  | 1555052165.0  |      |             | NULL       |           |

The correct cyrillic view will be like that:

+----+--------------+---------------------+--------------------------+---------+---------+-----------+----------+-------+---------+------------------+------+------+---------+------------+---------+-----------------+----------+-------------+---------------+---------------+------+-------------+------------+-----------+
| id | eventtype    | eventtime           | cid_name                 | cid_num | cid_ani | cid_rdnis | cid_dnid | exten | context | channame         | src  | dst  | channel | dstchannel | appname | appdata         | amaflags | accountcode | uniqueid      | linkedid      | peer | userdeftype | eventextra | userfield |
+----+--------------+---------------------+--------------------------+---------+---------+-----------+----------+-------+---------+------------------+------+------+---------+------------+---------+-----------------+----------+-------------+---------------+---------------+------+-------------+------------+-----------+
|  1 | CHAN_START   | 2019-04-12 09:56:05 | сатана                   | 123     |         |           |          | 123   | public  | SIP/123-00000000 | NULL | NULL | NULL    | NULL       |         |                 |        3 |             | 1555052165.0  | 1555052165.0  |      |             | NULL       |           |
|  2 | CHAN_START   | 2019-04-12 09:56:05 | сатана                   |         |         |           |          | s     | public  | SIP/123-00000001 | NULL | NULL | NULL    | NULL       |         |                 |        3 |             | 1555052165.2  | 1555052165.0  |      |             | NULL       |           |

I think this is caused by Asterisk. May be it writes ISO-8859-1 by default, despite of DB's charset? I dont' know. There are no charset settings inside Asterisk.

---

DB settings:

Some mysql vars:

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

cat /etc/my.cnf

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
collation-server = utf8mb4_unicode_ci
character-set-server = utf8mb4

init_connect = 'SET character_set_system = utf8mb4'
init_connect = 'SET character_set_connection = utf8mb4'
init_connect = 'SET character_set_database = utf8mb4'
init_connect = 'SET character_set_results = utf8mb4'
init_connect = 'SET collation_database = utf8mb4_unicode_ci'
init_connect = 'SET collation_connection = utf8mb4_unicode_ci'
init_connect = 'SET NAMES utf8mb4'

# This group is read both both by the client and the server
# use it for options that affect everything
[client-server]

# include all files from the config directory
!includedir /etc/my.cnf.d

There are no collisions in /etc/my.cnf.d

---

ODBC configs:

cat /etc/odbc.ini

[MySQL-asteriskcdrdb]
Description=MySQL connection to 'asteriskcdrdb' database
driver=MySQL
server=localhost
database=asteriskcdrdb
Port=3306
Socket=/var/lib/mysql/mysql.sock
Charset=utf8
FOUND_ROWS=1
BIG_PACKETS=1
UseUnicode=True

[MySQL-asterisk-conf]
Description=MySQL connection to 'asterisk' conf db
driver=MySQL
server=localhost
database=asterisk
Port=3306
Socket=/var/lib/mysql/mysql.sock
Charset=utf8
FOUND_ROWS=1
BIG_PACKETS=1
UseUnicode=True

cat /etc/odbcinst.ini

[MySQL]
Description=ODBC for MySQL
Driver64=/usr/lib64/libmyodbc8w.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1

[MySQL ODBC 8.0 Unicode Driver]
Driver=/usr/lib64/libmyodbc8w.so
SETUP=/usr/lib64/libmyodbc8S.so
UsageCount=1

[MySQL ODBC 8.0 ANSI Driver]
Driver=/usr/lib64/libmyodbc8a.so
SETUP=/usr/lib64/libmyodbc8S.so
UsageCount=1

---

Asterisk configs:

cat /etc/asterisk/cel_odbc.conf

[cel]
connection=asteriskcdrdb
loguniqueid=yes
table=cel
charset=utf8mb4

[general]

cat /etc/asterisk/cel.conf

[general]

enable=yes
apps=all
events=ALL
dateformat = %F %T

[manager]

[radius]

cat /etc/asterisk/res_odbc.conf

[asteriskcdrdb]
enabled=>yes
dsn=>MySQL-asteriskcdrdb
pooling=>no
limit=>1
pre-connect=>yes
username=>asteriskdb
password=>asteriskdb

[asterisk-conf]
enabled=>yes
dsn=>MySQL-asterisk-conf
pooling=>no
limit=>1
pre-connect=>yes
username=>asteriskdb
password=>asteriskdb

Answer 1

Fixed by changing mysql-connector-odbc 8.0.15-1 to mariadb 3.1 connector. Have no idea what's wrong with mysql connector.