0

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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.