QueueMetrics forum
QueueMetrics => QueueMetrics installation => Topic started by: trymes on September 30, 2010, 21:23:10
-
Woo-Hoo!!!
Now to figure out how to enable and tell QM to use the realtime Queue_log in Asterisk 1.6....
Anyone got a pointer?
Tom
-
Yes absolutely - see here: http://queuemetrics.com/manuals/QM_UserManual-chunked/ar01s16.html#_using_asterisk_realtime_queuelog_subsystem
-
OK, before I mess something up, I need some hand holding on the MySQL portion of this setup, if anyone is willing to provide it. Specifically, the portion between the command prompt and entering the command in MYSQL. Here's my guess:
1.) Log into server
2.) "mysql -p"
3.) Enter MySQL password
4.) "use asterisk;"
5.) Enter the command from the voip-info/bug-tracker to create the new "queue_log" table.
6.) Add the line specified to the extconfig.conf file.
7.) Reload asterisk.
Is that right?
Tom
-
It looks correct to me, but I have never actually tried.
-
Does asterisk need to be restarted after making this change? I did as above and the queue_log file stopped receiving new entries, but nothing showed up in MySQL. Maybe a permissions issue?
Tom
-
You should have something on Asterisk's log, e.g. on the FULL log.
-
I did not see anything, but I'll go back and scour it.
-
Sure enough, it was there. Don't know how I missed it the first time....
[Oct 25 14:48:43] VERBOSE[28356] config.c: == Binding queue_log to mysql/asterisk/queue_log
[Oct 25 14:48:43] WARNING[28356] res_config_mysql.c: MySQL RealTime: Invalid database specified: 'asterisk' (check res_mysql.conf)
[Oct 25 14:48:43] VERBOSE[28356] logger.c: Asterisk Queue Logger restarted
[
So, I checked my res_mysql.conf and found:
;
; Sample configuration for res_config_mysql.c
;
; The value of dbhost may be either a hostname or an IP address.
; If dbhost is commented out or the string "localhost", a connection
; to the local host is assumed and dbsock is used instead of TCP/IP
; to connect to the server.
;
[general]
dbhost = 127.0.0.1
dbname = asteriskrealtime
dbuser = asteriskuser
dbpass = eLaStIx.asteriskuser.2oo7
;dbport = 3306
;dbsock = /tmp/mysql.sock
I checked MySQL and found this:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| asterisk |
| asteriskcdrdb |
| meetme |
| mya2billing |
| mysql |
| queuemetrics |
| roundcubedb |
| test |
| vtigercrm510 |
+--------------------+
10 rows in set (0.01 sec)
You'll notice that there is no "asteriskrealtime" database as specified in the res_mysql.conf file. Before I just modify the file, does anyone have some pointers? Keep in mind that this is an Elastix 2.0 box running FreePBX.
Tom
-
OK, I got impatient and just changed /etc/asterisk/res_mysql.conf, given that I could see no harm. After doing that and restarting asterisk, I now get this in the full log:
[Oct 26 17:07:50] WARNING[25195] config.c: Realtime mapping for 'queue_log' found to engine 'mysql', but the engine is not available
It seems that the logger is starting before the mysql engine is loaded. If I reload asterisk, I then get this error again:
[Oct 26 17:47:41] VERBOSE[12854] config.c: == Binding queue_log to mysql/asterisk/queue_log
[Oct 26 17:47:41] WARNING[12854] res_config_mysql.c: MySQL RealTime: Invalid database specified: 'asterisk' (check res_mysql.conf)
This is my res_mysql.conf file:
;
; Sample configuration for res_config_mysql.c
;
; The value of dbhost may be either a hostname or an IP address.
; If dbhost is commented out or the string "localhost", a connection
; to the local host is assumed and dbsock is used instead of TCP/IP
; to connect to the server.
;
; Modified "dbname" to "asterisk" from "asteriskrealtime" as part of enabling
; realtime storage of queue_log. - Tom Rymes 10-26-10
[general]
dbhost = 127.0.0.1
dbname = asterisk
dbuser = asteriskuser
dbpass = eLaStIx.asteriskuser.2oo7
;dbport = 3306
;dbsock = /var/lib/mysql/mysql.sock
I don't think that permissions issues are the problem here, as I can connect to the database from the command line:
[root@vox ~]# mysql -u asteriskuser -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2293
Server version: 5.0.77 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use asterisk;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from queue_log;
Empty set (0.00 sec)
mysql>
So, I am stumped here. Going to switch back to the old method until someone can point me in the right direction....
Tom
-
You sure you have the mysql-dev modules and the mysql engine compiled in? does Elastix support it?
-
I did a bit of experimentation. Try to module reload res_config_mysql.so and see if it works.
Vigor106*CLI> core show config mappings
Config Engine: mysql
Config Engine: curl
Config Engine: odbc
Vigor106*CLI> module show like mysql
Module Description Use Count
app_cbmysql.so CBMysql conference scheduling 0
cdr_addon_mysql.so MySQL CDR Backend 0
res_config_mysql.so MySQL RealTime Configuration Driver 0
app_addon_sql_mysql.so Simple Mysql Interface 0
-
I'll give those options a try. I do know that MySQL is indeed loaded and functional, as Elastix uses it for CDRs.
Tom
-
OK, some more results:
vox*CLI> core show config mappings
Config Engine: pgsql
Config Engine: odbc
Config Engine: mysql
Config Engine: curl
vox*CLI> module show like mysql
Module Description Use Count
app_cbmysql.so CBMysql conference scheduling 0
res_config_mysql.so MySQL RealTime Configuration Driver 0
app_addon_sql_mysql.so Simple Mysql Interface 0
cdr_addon_mysql.so MySQL CDR Backend 0
4 modules loaded
But this is what the problem is in my mind:
[Oct 29 10:17:22] VERBOSE[9785] config.c: == Binding queue_log to mysql/asterisk/queue_log
[Oct 29 10:17:22] WARNING[9785] res_config_mysql.c: MySQL RealTime: Invalid database specified: 'asterisk' (check res_mysql.conf
Something is wrong with res_mysql.conf such that Asterisk cannot find the MySQL database named "asterisk", even though it is there, and the user "asteriskuser" as defined in res_mysql.conf can access it.
Tom
-
OK, so after Googling some more I found this post (http://forums.digium.com/viewtopic.php?f=1&t=73715), and it was something stupid. The format of the extconfig.conf file has changed in asterisk 1.6, such that you reference the context in that file ("general" in my example) instead of the database name. I modified my extconfig.conf to point there, and it seemed to load up.
However, I now get this message:
[Oct 29 10:25:34] WARNING[9853] res_config_mysql.c: Realtime table general@queue_log: column 'time' cannot be type 'int(10) unsigned' (need char)
I double checked the table creation instructions from voip-info, and it shows this relevant piece:
`time` int(10) unsigned default NULL,
So, any ideas on how I need to modify my queue_log table?
Tom
EDIT: The only thing I found on google was this IRC log: http://ibot.rikers.org/%23asterisk/20090728.html.gz
The relevant snippet (it looks like he solved his own problem?):
16:23.41 cusco__ hi
16:23.43 cusco__ "require_mysql: Realtime table general@csr_queue: column 'time' cannot be type 'int(10) unsigned' (need char)"
16:23.48 cusco__ what type must it be?
16:25.44 cusco__ type char
16:25.45 cusco__ ok
16:25.46 cusco__ lol
Before I go messing about with MySQL, can someone confirm, maybe provide a little handholding on how to modify my existing table and/or delete the existing table and properly create a new one?
Loway, maybe y'all could test this out and provide a working config, considering this is a published feature of the software? I am, by no means, an experience MySQL user.
-
Any ideas out there?
-
You could use something like
ALTER TABLE `queueu_log` CHANGE `time` `time` VARCHAR( 30 ) NOT NULL
Old data may be deleted....
I would like to see which data you write to it.
-
OK, I haven't tried firing up the queue_log via MySQL again, but here is what happened when modifying my table. It seems as if no data was lost (not that I had much real data...).
mysql> use asterisk;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from queue_log;
+----+------------+------------------+-----------+------------------------+----------------+----------+
| id | time | callid | queuename | agent | event | data |
+----+------------+------------------+-----------+------------------------+----------------+----------+
| 1 | 1288362313 | NONE | NONE | NONE | CONFIGRELOAD | |
| 2 | 1288362334 | 1288362186.10990 | 400 | Local/127@from-queue/n | COMPLETECALLER | 11|132|2 |
+----+------------+------------------+-----------+------------------------+----------------+----------+
2 rows in set (0.01 sec)
mysql> ALTER TABLE `queue_log` CHANGE `time` `time` VARCHAR( 30 ) NOT NULL;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from queue_log;
+----+------------+------------------+-----------+------------------------+----------------+----------+
| id | time | callid | queuename | agent | event | data |
+----+------------+------------------+-----------+------------------------+----------------+----------+
| 1 | 1288362313 | NONE | NONE | NONE | CONFIGRELOAD | |
| 2 | 1288362334 | 1288362186.10990 | 400 | Local/127@from-queue/n | COMPLETECALLER | 11|132|2 |
+----+------------+------------------+-----------+------------------------+----------------+----------+
2 rows in set (0.00 sec)
I will test this once call volume is down.
Tom
-
OK, so to clear things up, I tried the ALTER statement, and I get this error still. How, I do not know.
/var/log/asterisk/full:[Nov 3 13:37:57] VERBOSE[25801] config.c: == Binding queue_log to mysql/general/queue_log
/var/log/asterisk/full:[Nov 3 13:37:57] WARNING[25801] res_config_mysql.c: Realtime table general@queue_log: column 'time' cannot be type 'int(10) unsigned' (need char)
So, I thought, let's just delete the whole table and start again. Which I did. I used this to create the table:
CREATE TABLE `queue_log` (
`id` int(10) unsigned NOT NULL auto_increment,
`time` varchar(30) NOT NULL default '',
`callid` varchar(32) NOT NULL default '',
`queuename` varchar(32) NOT NULL default '',
`agent` varchar(32) NOT NULL default '',
`event` varchar(32) NOT NULL default '',
`data` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
);
I still get the same error. Any ideas? I mean, I can only presume that you guys had this working at some point in order to implement the functionality on QM? Can you look at how the development machine was set up and tell me if it differs from what I am doing?
Tom
-
No news is bad news, I presume?
Come on, this is a published feature of the program, I can only presume that the devs had it working at some point?
Tom
-
Yes, but they accessed a client's system during development.
We plan to address this with a tutorial for 1.8.
-
I'm sure you have, but it's always good to check: After altering the database table, did you do a module reload within the asterisk CLI? From my experience, it seems asterisk does some sort of cacheing of the database column types and layouts.
Here is the describe on my queue_log:
mysql> describe queue_log;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| time | char(10) | YES | | NULL | |
| callid | varchar(32) | NO | | | |
| queuename | varchar(32) | NO | | | |
| agent | varchar(32) | NO | | | |
| event | varchar(32) | NO | | | |
| data | varchar(255) | NO | | | |
+-----------+------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
And here are my relevant config file entries:
res_mysql.conf:
[general]
dbhost=127.0.0.1
dbname=ast_db
dbuser=asterisk
dbpass=XXXXXX
dbport=3306
sock=/var/run/mysqld/mysqld.sock
extconfig.conf:
[settings]
queue_log => mysql,general,queue_log
The main difference I have noticed here is that my column type is "char" not "varchar". I'm no database expert but if asterisk is picky about column types this could cause it to fail.
I hope this helps!
-
The main difference I have noticed here is that my column type is "char" not "varchar". I'm no database expert but if asterisk is picky about column types this could cause it to fail.
I think this is completely undetectable at the client level. The only difference is if you want to waste space but have faster access (CHAR) or save disk space at a light penalty (VARCHAR).
-
Can either of you confirm how the default and NULL items ought to be set? In the original bug report and Voip-Info report, IIRC, they had it set to NOT NULL and default '', while moa has his time clumn set to NULL = YES and defauly NULL.
I doubt it will make a difference, but....
Tom
-
OK, I have it up and working, I think. No more errors. I chose to set the time column as char(10), NOT NULL and default ''.
Now, though, I get an error in QM because the queuemetrics@localhost is denied permission by MySQL. What is the best method for granting permission to QM to read the queue_log table?
I am not confident in MySQL enough to feel comfortable with granting the proper rights, etc without a little handholding.
Thank you,
Tom
-
Try
grant all privileges on databasename.* to 'queuemetrics'@'localhost' identified by 'javadude';
You have to set your database name for databasename.
-
Excellent. All seems to be working well now. Once I get a chance to set the rest up and play with it I will do my best to update the instructions, as it isn't really as it ought to be.
Thanks for the help, guys!
Tom
-
But what was the issue that stopped you last week?
-
My issues were as follows (starting from the beginning):
1.) Asterisk 1.6 uses the context name, not the database name in extconfig.conf
2.) The instructions say to use the wrong column format. Should be char(10), NOT int(10).
3.) queuemetrics user needs permission to read the asterisk database.
I think it would have worked last week when it was set to varchar, but I was getting the error about the wrong column format and I didn't want to turn it loose until I had some more info. Chances are that a module reload would have cleared it up, as moa said, but who's to say?
Tom
-
Thanks that's great.
-
As an update, I registered at voip-info and modified the wiki page to reflect the lessons I learned here.
http://www.voip-info.org/wiki/index.php?page_id=2767
I presume that the folks at QM are aware of the changes that have been made to realtime queue_log storage for Asterisk 1.8? I posted a link to the bug report if you are interested. I presume that QM will require changes to support those modifications.
Tom
-
We have a bug open for that - #1215.
-
This bug has now been resolved - please refer to the next QM release, due to be made available within the next few days.