Страница 1 из 2

проблемы с настройкой на MySQL

Добавлено: 03 авг 2013, 13:40
nucleon
собственно проблема с настройкой на работу с базой MySQL
при попытки соедениться, с любым пользователем,
получаю:

Код: Выделить всё

[Aug  3 13:30:52] WARNING[28496]: res_config_mysql.c:326 realtime_mysql: MySQL RealTime: Invalid database specified: asterisk (check res_mysql.conf)
[Aug  3 13:30:52] NOTICE[28496]: chan_sip.c:22325 handle_request_register: Registration from '"neo"<sip:0000@x.x.x.x:5060>' failed for 'x.x.x.y' - No matching peer found
при этом вроде все настроенно:

PRIME_BBCODE_SPOILER_SHOW PRIME_BBCODE_SPOILER: res_mysql.conf:
[general]
dbhost = 127.0.0.1
dbname = asterisk
dbuser = asterisk
dbpass = <пароль>
dbport = 3306
PRIME_BBCODE_SPOILER_SHOW PRIME_BBCODE_SPOILER: extconfig.conf:
[settings]
sipusers => mysql,asterisk,sipusers
sippeers => mysql,asterisk,sipusers
voicemail => mysql,asterisk,voicemail
extensions => mysql,asterisk,extensions
PRIME_BBCODE_SPOILER_SHOW PRIME_BBCODE_SPOILER: extensions.conf:
[general]
static = no; параметр сохранения диалплана,
writeprotect = no;
autofallthrough = yes
clearglobalvars = no
priorityjumping = no
[globals]
; глобальные переменные
; у меня есть и статичные диалпланы и тут я объявил параметры подключения к бд,
; чтобы постоянно не писать их в диалпланах
DBHOST=127.0.0.1
DBUSER=asterisk
DBPASS=<пароль>
DBNAME=asterisk
[factory]
; диалплан "завод"
; а следующая строка указывает, что нужно смотреть в базу данных
switch => Realtime/@
PRIME_BBCODE_SPOILER_SHOW PRIME_BBCODE_SPOILER: sip.conf
[general]
context=factory
; контекст по умолчанию для входящих звонков
realm=hostname
; пишу hostname
bindport=5060
; порт UDP который "слушает" asterisk
bindaddr=0.0.0.0
srvlookup=yes
language=ru
disallow=all
allow=ulaw
allow=alaw
allow=gsm
type=friend
rtcachefriends = yes
rtcache=yes
PRIME_BBCODE_SPOILER_SHOW PRIME_BBCODE_SPOILER: asterisk.conf
[options]
; уровень вывода в консоль
verbose = 64
; уровень дебага
debug = 64
; параметры установлены по умолчанию и менять не желательно
[compat]
pbx_realtime=1.6
res_agi=1.6
app_set=1.6
подскажите, как победить?

Re: проблемы с настройкой на MySQL

Добавлено: 03 авг 2013, 13:42
awsswa
а саму базу то в mysql создали ?

mysql -u asterisk -p asterisk

Re: проблемы с настройкой на MySQL

Добавлено: 03 авг 2013, 13:57
Vlad1983
в
res_mysql.conf заменить [general] на [asterisk]

Re: проблемы с настройкой на MySQL

Добавлено: 03 авг 2013, 19:08
nucleon
база есть
PRIME_BBCODE_SPOILER_SHOW PRIME_BBCODE_SPOILER: заполнялась так
USE `asterisk`;
alter database character set utf8;
alter database collate utf8_general_ci;
-- ----------------------------
-- Table structure for extensions
-- ----------------------------
DROP TABLE IF EXISTS `extensions`;
CREATE TABLE `extensions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`context` varchar(20) NOT NULL DEFAULT '',
`exten` varchar(20) NOT NULL DEFAULT '',
`priority` tinyint(4) NOT NULL DEFAULT '0',
`app` varchar(20) NOT NULL DEFAULT '',
`appdata` varchar(128) NOT NULL DEFAULT '',
KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2;


-- ----------------------------
-- Table structure for sipusers
-- ----------------------------
DROP TABLE IF EXISTS `sipusers`;
CREATE TABLE `sipusers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`accountcode` varchar(20) DEFAULT NULL,
`disallow` varchar(100) DEFAULT 'all',
`allow` varchar(100) DEFAULT 'g729;ilbc;gsm;ulaw;alaw',
`allowoverlap` enum('yes','no') DEFAULT 'yes',
`allowsubscribe` enum('yes','no') DEFAULT 'yes',
`allowtransfer` varchar(3) DEFAULT NULL,
`amaflags` varchar(13) DEFAULT NULL,
`autoframing` varchar(3) DEFAULT NULL,
`auth` varchar(40) DEFAULT NULL,
`buggymwi` enum('yes','no') DEFAULT 'no',
`callgroup` varchar(10) DEFAULT NULL,
`callerid` varchar(80) DEFAULT NULL,
`cid_number` varchar(40) DEFAULT NULL,
`fullname` varchar(40) DEFAULT NULL,
`call-limit` int(8) DEFAULT '0',
`callingpres` varchar(80) DEFAULT NULL,
`canreinvite` char(6) DEFAULT 'yes',
`context` varchar(80) DEFAULT NULL,
`defaultip` varchar(15) DEFAULT NULL,
`dtmfmode` varchar(7) DEFAULT NULL,
`fromuser` varchar(80) DEFAULT NULL,
`fromdomain` varchar(80) DEFAULT NULL,
`fullcontact` varchar(80) DEFAULT NULL,
`g726nonstandard` enum('yes','no') DEFAULT 'no',
`host` varchar(31) NOT NULL DEFAULT 'dynamic',
`insecure` varchar(20) DEFAULT NULL,
`ipaddr` varchar(15) NOT NULL DEFAULT '',
`language` char(2) DEFAULT NULL,
`lastms` varchar(20) DEFAULT NULL,
`mailbox` varchar(50) DEFAULT NULL,
`maxcallbitrate` int(8) DEFAULT '384',
`mohsuggest` varchar(80) DEFAULT NULL,
`md5secret` varchar(80) DEFAULT NULL,
`musiconhold` varchar(100) DEFAULT NULL,
`name` varchar(80) NOT NULL DEFAULT '',
`nat` varchar(5) NOT NULL DEFAULT 'no',
`outboundproxy` varchar(80) DEFAULT NULL,
`deny` varchar(95) DEFAULT NULL,
`permit` varchar(95) DEFAULT NULL,
`pickupgroup` varchar(10) DEFAULT NULL,
`port` varchar(5) NOT NULL DEFAULT '',
`progressinband` enum('yes','no','never') DEFAULT 'no',
`promiscredir` enum('yes','no') DEFAULT 'no',
`qualify` char(3) DEFAULT NULL,
`regexten` varchar(80) NOT NULL DEFAULT '',
`regseconds` int(11) NOT NULL DEFAULT '0',
`rfc2833compensate` enum('yes','no') DEFAULT 'no',
`rtptimeout` char(3) DEFAULT NULL,
`rtpholdtimeout` char(3) DEFAULT NULL,
`secret` varchar(80) DEFAULT NULL,
`sendrpid` enum('yes','no') DEFAULT 'yes',
`setvar` varchar(100) NOT NULL DEFAULT '',
`subscribecontext` varchar(80) DEFAULT NULL,
`subscribemwi` varchar(3) DEFAULT NULL,
`t38pt_udptl` enum('yes','no') DEFAULT 'no',
`trustrpid` enum('yes','no') DEFAULT 'no',
`type` varchar(6) NOT NULL DEFAULT 'friend',
`useclientcode` enum('yes','no') DEFAULT 'no',
`username` varchar(80) NOT NULL DEFAULT '',
`usereqphone` varchar(3) NOT NULL DEFAULT 'no',
`videosupport` enum('yes','no') DEFAULT 'yes',
`vmexten` varchar(80) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `name_2` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=2 ROW_FORMAT=DYNAMIC;


-- ----------------------------
-- Table structure for voicemail
-- ----------------------------
DROP TABLE IF EXISTS `voicemail`;
CREATE TABLE `voicemail` (
`uniqueid` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL DEFAULT '0',
`context` varchar(50) NOT NULL DEFAULT '',
`mailbox` int(5) NOT NULL DEFAULT '0',
`password` varchar(4) NOT NULL DEFAULT '0',
`fullname` varchar(50) NOT NULL DEFAULT '',
`email` varchar(50) NOT NULL DEFAULT '',
`pager` varchar(50) NOT NULL DEFAULT '',
`stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`attach` varchar(3) NOT NULL DEFAULT 'no',
`saycid` varchar(3) NOT NULL DEFAULT 'yes',
`hidefromdir` varchar(3) NOT NULL DEFAULT 'no',
PRIMARY KEY (`uniqueid`),
KEY `mailbox_context` (`mailbox`,`context`)
) ENGINE=MyISAM;


-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `extensions` VALUES ('1', 'factory', '_XXXXXXX', '1', 'NoOp', '');
INSERT INTO `sipusers` VALUES ('1', null, 'all', 'g729;ilbc;gsm;ulaw;alaw', 'yes', 'yes', null, null, null, null, 'no', null, null, null, null, '0', null, 'yes', null, null, null, null, null, null, 'no', 'dynamic', null, '', 'en', '0', null, '384', null, null, null, 'neo', 'no', null, null, null, null, '', 'no', 'no', null, '1000001', '0', 'no', null, null, 'password', 'yes', '', null, null, 'no', 'no', 'friend', 'no', '', 'no', 'yes', null);

CREATE TABLE `cdr` (
`calldate` datetime NOT NULL default '0000-00-00 00:00:00',
`clid` varchar(80) NOT NULL default '',
`src` varchar(80) NOT NULL default '',
`dst` varchar(80) NOT NULL default '',
`dcontext` varchar(80) NOT NULL default '',
`channel` varchar(80) NOT NULL default '',
`dstchannel` varchar(80) NOT NULL default '',
`lastapp` varchar(80) NOT NULL default '',
`lastdata` varchar(80) NOT NULL default '',
`duration` int(11) NOT NULL default '0',
`billsec` int(11) NOT NULL default '0',
`disposition` varchar(45) NOT NULL default '',
`amaflags` int(11) NOT NULL default '0',
`accountcode` varchar(20) NOT NULL default '',
`userfield` varchar(255) NOT NULL default ''
);
а на замену [general] на [asterisk] сервер отреагировал так:
[Aug 3 23:05:51] ERROR[2806]: res_config_mysql.c:1591 mysql_reconnect: MySQL RealTime: Failed to connect database server asterisk on 127.0.0.1 (err 1045). Check debug for more info.
да кстати астер 1.6.2.4

Re: проблемы с настройкой на MySQL

Добавлено: 03 авг 2013, 19:31
Vlad1983

Код: Выделить всё

mysql -uasterisk -pпароль -h 127.0.0.1 asterisk

Re: проблемы с настройкой на MySQL

Добавлено: 03 авг 2013, 19:40
nucleon
база есть,
в ответ на
Vlad1983 писал(а):mysql -uasterisk -pпароль -h 127.0.0.1 asterisk
стандартное приглашение:
PRIME_BBCODE_SPOILER_SHOW PRIME_BBCODE_SPOILER: ответ
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 663
Server version: 5.1.70-0ubuntu0.10.04.1-log (Ubuntu)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Re: проблемы с настройкой на MySQL

Добавлено: 03 авг 2013, 20:20
Vlad1983
Check debug for more info.

Re: проблемы с настройкой на MySQL

Добавлено: 03 авг 2013, 20:55
nucleon
это после замены general на asterisk
тут как-бы все сказано, если указан asterisk то настройки не применяются вообще
PRIME_BBCODE_SPOILER_SHOW PRIME_BBCODE_SPOILER: в messages:
[Aug 3 23:05:34] NOTICE[2771] loader.c: 3 modules will be loaded.
[Aug 3 23:05:34] WARNING[2771] res_config_mysql.c: MySQL realtime: unrecognized requirements setting 'warn or createclose or createchar', using 'warn'
[Aug 3 23:05:34] NOTICE[2771] config.c: Registered Config Engine mysql
[Aug 3 23:05:34] NOTICE[2771] manager.c: Invalid keyword <read> = <system,call,log,verbose,agent,user,config,dtmf,reporting,cdr,dialplan> in manager.conf [general]
[Aug 3 23:05:34] NOTICE[2771] manager.c: Invalid keyword <write> = <system,call,agent,user,config,command,reporting,originate> in manager.conf [general]
[Aug 3 23:05:34] NOTICE[2771] cdr.c: CDR simple logging enabled.
[Aug 3 23:05:34] NOTICE[2771] loader.c: 169 modules will be loaded.
[Aug 3 23:05:34] WARNING[2771] res_config_ldap.c: No directory user found, anonymous binding as default.
[Aug 3 23:05:34] ERROR[2771] res_config_ldap.c: No directory URL or host found.
[Aug 3 23:05:34] NOTICE[2771] res_config_ldap.c: Cannot load LDAP RealTime driver.
[Aug 3 23:05:34] NOTICE[2771] res_smdi.c: No SMDI interfaces are available to listen on, not starting SMDI listener.
[Aug 3 23:05:34] ERROR[2771] res_config_pgsql.c: PostgreSQL RealTime: Failed to connect database asterisk on 127.0.0.1:
[Aug 3 23:05:34] WARNING[2771] res_config_pgsql.c: PostgreSQL RealTime: Couldn't establish connection. Check debug.
[Aug 3 23:05:34] NOTICE[2771] config.c: Registered Config Engine pgsql
[Aug 3 23:05:34] NOTICE[2771] chan_skinny.c: Configuring skinny from skinny.conf
[Aug 3 23:05:34] WARNING[2771] utils.c: trying to reset empty pool
[Aug 3 23:05:34] WARNING[2771] utils.c: trying to reset empty pool
[Aug 3 23:05:34] WARNING[2771] utils.c: trying to reset empty pool
[Aug 3 23:05:34] NOTICE[2771] chan_ooh323.c: ---------------------------------------------------------------------------------
--- ******* IMPORTANT NOTE ***********
---
--- This module is currently unsupported. Use it at your own risk.
---
---------------------------------------------------------------------------------
[Aug 3 23:05:34] ERROR[2771] res_config_mysql.c: MySQL RealTime: Failed to connect database server asterisk on 127.0.0.1 (err 1045). Check debug for more info.
[Aug 3 23:05:34] WARNING[2771] res_config_mysql.c: Table sipusers not found in database. This table should exist if you're using realtime.
[Aug 3 23:05:34] ERROR[2771] res_config_mysql.c: MySQL RealTime: Failed to connect database server asterisk on 127.0.0.1 (err 1045). Check debug for more info.
[Aug 3 23:05:34] WARNING[2771] res_config_mysql.c: Table voicemail not found in database. This table should exist if you're using realtime.
[Aug 3 23:05:34] NOTICE[2771] pbx_ael.c: Starting AEL load process.
[Aug 3 23:05:34] NOTICE[2771] pbx_ael.c: AEL load process: parsed config file name '/etc/asterisk/extensions.ael'.
[Aug 3 23:05:34] NOTICE[2771] pbx_ael.c: AEL load process: checked config file name '/etc/asterisk/extensions.ael'.
[Aug 3 23:05:34] NOTICE[2771] pbx_ael.c: AEL load process: compiled config file name '/etc/asterisk/extensions.ael'.
[Aug 3 23:05:34] NOTICE[2771] pbx_ael.c: AEL load process: merged config file name '/etc/asterisk/extensions.ael'.
[Aug 3 23:05:34] NOTICE[2771] pbx_ael.c: AEL load process: verified config file name '/etc/asterisk/extensions.ael'.
[Aug 3 23:05:51] ERROR[2806] res_config_mysql.c: MySQL RealTime: Failed to connect database server asterisk on 127.0.0.1 (err 1045). Check debug for more info.
[Aug 3 23:05:51] ERROR[2806] res_config_mysql.c: MySQL RealTime: Failed to connect database server asterisk on 127.0.0.1 (err 1045). Check debug for more info.
[Aug 3 23:05:51] ERROR[2806] res_config_mysql.c: MySQL RealTime: Failed to connect database server asterisk on 127.0.0.1 (err 1045). Check debug for more info.
[Aug 3 23:05:51] ERROR[2806] res_config_mysql.c: MySQL RealTime: Failed to connect database server asterisk on 127.0.0.1 (err 1045). Check debug for more info.
если вернуться к general то этого уже не возникает:
PRIME_BBCODE_SPOILER_SHOW PRIME_BBCODE_SPOILER: general
[Aug 3 20:51:28] NOTICE[2999] loader.c: 3 modules will be loaded.
[Aug 3 20:51:28] WARNING[2999] res_config_mysql.c: MySQL realtime: no requirements setting found, using 'warn' as default.
[Aug 3 20:51:28] NOTICE[2999] config.c: Registered Config Engine mysql
[Aug 3 20:51:28] NOTICE[2999] manager.c: Invalid keyword <read> = <system,call,log,verbose,agent,user,config,dtmf,reporting,cdr,dialplan> in manager.conf [general]
[Aug 3 20:51:28] NOTICE[2999] manager.c: Invalid keyword <write> = <system,call,agent,user,config,command,reporting,originate> in manager.conf [general]
[Aug 3 20:51:28] NOTICE[2999] cdr.c: CDR simple logging enabled.
[Aug 3 20:51:28] NOTICE[2999] loader.c: 169 modules will be loaded.
[Aug 3 20:51:28] WARNING[2999] res_config_ldap.c: No directory user found, anonymous binding as default.
[Aug 3 20:51:28] ERROR[2999] res_config_ldap.c: No directory URL or host found.
[Aug 3 20:51:28] NOTICE[2999] res_config_ldap.c: Cannot load LDAP RealTime driver.
[Aug 3 20:51:28] NOTICE[2999] res_smdi.c: No SMDI interfaces are available to listen on, not starting SMDI listener.
[Aug 3 20:51:28] ERROR[2999] res_config_pgsql.c: PostgreSQL RealTime: Failed to connect database asterisk on 127.0.0.1:
[Aug 3 20:51:28] WARNING[2999] res_config_pgsql.c: PostgreSQL RealTime: Couldn't establish connection. Check debug.
[Aug 3 20:51:28] NOTICE[2999] config.c: Registered Config Engine pgsql
[Aug 3 20:51:28] NOTICE[2999] chan_skinny.c: Configuring skinny from skinny.conf
[Aug 3 20:51:28] WARNING[2999] utils.c: trying to reset empty pool
[Aug 3 20:51:28] WARNING[2999] utils.c: trying to reset empty pool
[Aug 3 20:51:28] WARNING[2999] utils.c: trying to reset empty pool
[Aug 3 20:51:28] NOTICE[2999] chan_ooh323.c: ---------------------------------------------------------------------------------
--- ******* IMPORTANT NOTE ***********
---
--- This module is currently unsupported. Use it at your own risk.
---
---------------------------------------------------------------------------------
[Aug 3 20:51:28] WARNING[2999] res_config_mysql.c: Table sipusers not found in database. This table should exist if you're using realtime.
[Aug 3 20:51:28] WARNING[2999] res_config_mysql.c: Table voicemail not found in database. This table should exist if you're using realtime.

[Aug 3 20:51:28] NOTICE[2999] pbx_ael.c: Starting AEL load process.
[Aug 3 20:51:28] NOTICE[2999] pbx_ael.c: AEL load process: parsed config file name '/etc/asterisk/extensions.ael'.
[Aug 3 20:51:28] NOTICE[2999] pbx_ael.c: AEL load process: checked config file name '/etc/asterisk/extensions.ael'.
[Aug 3 20:51:28] NOTICE[2999] pbx_ael.c: AEL load process: compiled config file name '/etc/asterisk/extensions.ael'.
[Aug 3 20:51:28] NOTICE[2999] pbx_ael.c: AEL load process: merged config file name '/etc/asterisk/extensions.ael'.
[Aug 3 20:51:28] NOTICE[2999] pbx_ael.c: AEL load process: verified config file name '/etc/asterisk/extensions.ael'.
т.е. насколько я понял базу он видит, а таблицы нет...

Re: проблемы с настройкой на MySQL

Добавлено: 03 авг 2013, 20:59
Vlad1983
extconfig.conf
sipusers => mysql,general,sipusers
sippeers => mysql,general,sipusers
voicemail => mysql,general,voicemail
extensions => mysql,general,extensions

Re: проблемы с настройкой на MySQL

Добавлено: 04 авг 2013, 02:10
nucleon
манипуляции с general не помогали, да и похоже что дело было не в них...
выяснилось, что почему-то не грузились модули - загрузил,
пересоздал базу и привилегии, теперь базу он находит и таблицы в ней тоже, но что-то оно ему не нравится:
[Aug 4 01:59:26] WARNING[4326] config.c: Realtime mapping for 'voicemail' found to engine 'mysql', but the engine is not available
[Aug 4 01:59:26] WARNING[4326] config.c: Realtime mapping for 'sippeers' found to engine 'mysql', but the engine is not available
а при подключении пишет:
[Aug 4 02:03:36] WARNING[4421]: res_config_mysql.c:326 realtime_mysql: MySQL RealTime: Invalid database specified: asterisk (check res_mysql.conf)