CentOS Linux 版本 7.6.1810(核心)
内核:localhost.localdomain 5.0.7-1.el7.elrepo.x86_64
MariaDB:Ver 15.1 Distrib 10.4.4-MariaDB,适用于 Linux (x86_64),使用 readline 5.1
Asterisk:16.3.0 由 root 构建
unixODBC 2.3.1
mysql-连接器-odbc 8.0.15-1
我建立了 Asterisk 与 MariaDB 之间的连接,以存储 cdr 和 cel 数据。连接正常,Asterisk 可以写入数据库。我想使用西里尔文cid_name
,因此我尝试设置 UTF-8 字符集任何地方。
但是现在,如果我进行测试调用,Asterisk 会将数据写入cid_name
数据库,而不是 UTF-8 数据。为什么?我该如何解决这个问题?
命令行:
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 | |
正确的西里尔文视图如下:
+----+--------------+---------------------+--------------------------+---------+---------+-----------+----------+-------+---------+------------------+------+------+---------+------------+---------+-----------------+----------+-------------+---------------+---------------+------+-------------+------------+-----------+
| 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 | |
我认为这是由 Asterisk 引起的。可能是它默认写入 ISO-8859-1,而不管 DB 的字符集是什么?我不知道。Asterisk 内部没有字符集设置。
数据库设置:
一些 mysql 变量:
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 |
+--------------------------+--------------------+
猫/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
/etc/my.cnf.d 中没有冲突
ODBC 配置:
猫/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
猫/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
星号配置:
cat /etc/asterisk/cel_odbc.conf
[cel]
connection=asteriskcdrdb
loguniqueid=yes
table=cel
charset=utf8mb4
[general]
猫/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
答案1
通过将 mysql-connector-odbc 8.0.15-1 更改为 mariadb 3.1 连接器来修复。不知道 mysql 连接器有什么问题。