Tjyylli

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

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