QM 1.6 and realtime queue_log

[QueueMetrics]

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.

[trymes]

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...).

Code: [Select]

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

[trymes]

OK, so to clear things up, I tried the ALTER statement, and I get this error still. How, I do not know.

Code: [Select]

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

Code: [Select]

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

[trymes]

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

[QueueMetrics]

Yes, but they accessed a client's system during development.
We plan to address this with a tutorial for 1.8.

[moa]

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:

Code: [Select]

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:

Code: [Select]

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!

[QueueMetrics]

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).

[trymes]

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

[trymes]

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

[QueueMetrics]

Try

Code: [Select]

grant all privileges on databasename.* to 'queuemetrics'@'localhost' identified by 'javadude';
You have to set your database name for databasename.

[trymes]

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

[QueueMetrics]

But what was the issue that stopped you last week?

[trymes]

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

[QueueMetrics]

Thanks that's great.

[trymes]

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