QM 1.6 and realtime queue_log

[trymes]

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

[QueueMetrics]

Yes absolutely - see here: http://queuemetrics.com/manuals/QM_UserManual-chunked/ar01s16.html#_using_asterisk_realtime_queuelog_subsystem

[trymes]

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

[QueueMetrics]

It looks correct to me, but I have never actually tried.

[trymes]

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

[QueueMetrics]

You should have something on Asterisk's log, e.g. on the FULL log.

[trymes]

I did not see anything, but I'll go back and scour it.

[trymes]

Sure enough, it was there. Don't know how I missed it the first time....

Code: [Select]

[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:

Code: [Select]

;
; 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:

Code: [Select]

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

[trymes]

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:

Code: [Select]

[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:

Code: [Select]

[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:

Code: [Select]

;
; 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:

Code: [Select]

[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

[QueueMetrics]

You sure you have the mysql-dev modules and the mysql engine compiled in? does Elastix support it?

[QueueMetrics]

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

[trymes]

I'll give those options a try. I do know that MySQL is indeed loaded and functional, as Elastix uses it for CDRs.

Tom

[trymes]

OK, some more results:

Code: [Select]

vox*CLI> core show config mappings
Config Engine: pgsql
Config Engine: odbc
Config Engine: mysql
Config Engine: curl

Code: [Select]

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:

Code: [Select]

[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

[trymes]

OK, so after Googling some more I found this post, 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:

Code: [Select]

[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:

Code: [Select]

`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?):

Code: [Select]

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.

[trymes]

Any ideas out there?