File sql: not found

[silmaril]

Hi,

I'm having trouble with the Mysql Storage ssytem.
I was able to set-up qloaderd.pl with no problem, the queue_log table schema is the default one,
except for the data1 field size which was too small to fetch our URLs:
alter table queue_log modify data1 varchar(255);

The Mysql Storage Info is correctly seeing the table with all datas:

Partition     Entries     N. calls     From:     To:     Days of data:     Last heartbeat:     
P000    11.140    773      2008-04-21 04:51      2008-05-08 05:59    16,9 days      2008-05-08 05:57

But i keep getting "File sql:P000|1 does not exists" in the custom report setup.

Also i wasn't able to find usefull info in the tomcat logs, no Security error, only:

Code: [Select]

8 mai 2008 20:04:06 org.apache.catalina.core.ApplicationContext log
INFO: [9F07C086390BBC4546A49292995AFA31] ===== Configurazione oggetto Analizzalog ===========
 File        : sql:P000|1
 Tst Iniziale: 1210182300
 Tst Finale  : 1210268700
 Tst corrente: 1210268700
 Agenti amm. : [agent/136, none]
 Code amm.   : [none, support-out, support]
 TZ Offset   : 0
 RewrLocal   : true
 RewrLocalQN : false
 ChiudiCB/QSt: false
 Ign QStart  : false
 Ending ch.na: false
 Min Anom Wai: 1000
 Min Anom Tal: 2000
 Server ID   :
====================================================
===-------------- MYSQL extension ---------------===
 Connection  : com.mysql.jdbc.Connection@773c550f
 Partition   : P000
====================================================

8 mai 2008 20:04:06 org.apache.catalina.core.ApplicationContext log
INFO: LowayTransactionController: [9F07C086390BBC4546A49292995AFA31] [ERR] The file sql:P000|1  does not exist
8 mai 2008 20:04:06 org.apache.catalina.core.ApplicationContext log
INFO: LowayTransactionController: [9F07C086390BBC4546A49292995AFA31] Classe 'it.loway.app.queuemetrics.caricamento.caricaCoda' eseguita in 14  ms
8 mai 2008 20:04:06 org.apache.catalina.core.ApplicationContext log
INFO: LowayTransactionController: [9F07C086390BBC4546A49292995AFA31] Tempo totale esecuzione verbo 'qm_mostra_coda': 28 ms

And even activating mysql query log didn't help, i don't see any queue_log query going thru the database (on the custom report page)

Here is the configuration.property file:

Code: [Select]

default.queue=1

# This is the default queue log file.
default.queue_log_file=sql:|2

#This key is used only as a placeholder for CVS versioning information
00_version=$Id: configuration.properties,v 1.23 2007/11/21 16:11:15 lenz Exp $

#When the realtime page for an agent is computed, the queue_log is NOT read in its entirety but only the last 'n' bytes.
realtime.max_bytes_agent=65000

#The top level directory where monitored calls are held.
#Do NOT forget to add the ending slash.
default.monitored_calls=/var/spool/asterisk/

#How many digits to consider as a default area code
default.areacode_digits=2

default.start_hour=9

default.end_hour=18

default.days=7

#if liveclock is enabled, the system clock is synchronized with asterisk server system clock.
liveclock.enable=true

#The max delay that will be shown in the TOS graph
sla.max_monitored_delay=120

#How big are time intervals for the TOS graph
sla.interval=10

#Your company logo (full or relative path) - shall be resized to be an image 200 x 72
layout.logo=$WEBAPP/img/sm_your_logo.gif

#HTML string displayed on the login page.
layout.splash=Please ask your system administrator for the correct credentials to access this instance of QueueMetrics.

# Preset 1: standard DB access. Edit to suit your DB needs.
sqlPreset.1.table=queue_log
sqlPreset.1.f_time_id=time_id
sqlPreset.1.f_call_id=call_id
sqlPreset.1.f_queue=queue
sqlPreset.1.f_agent=agent
sqlPreset.1.f_verb=verb
sqlPreset.1.f_partition=partition
sqlPreset.1.f_data1=data1
sqlPreset.1.f_data2=data2
sqlPreset.1.f_data3=data3
sqlPreset.1.f_data4=data4
sqlPreset.1.f_incr=unique_row_count

sqlPreset.2.table=queue_log
sqlPreset.2.f_time_id=time_id
sqlPreset.2.f_call_id=call_id
sqlPreset.2.f_queue=queue
sqlPreset.2.f_agent=agent
sqlPreset.2.f_verb=verb
sqlPreset.2.f_partition=
sqlPreset.2.f_data1=data1
sqlPreset.2.f_data2=data2
sqlPreset.2.f_data3=data3
sqlPreset.2.f_data4=data4
sqlPreset.2.f_incr=unique_row_count

#Is the calls panel in the realtime page invisible by default? 0 false, 1 true
realtime.calls_invisible=0

#Is the agents panel in the realtime page invisible by default? 0 false, 1 true   
realtime.agents_invisible=0

#Are not  the only agents to be shown on the realtime page those who are "known" for the queue? 0 false, 1 true
realtime.members_only=1

#In how many seconds is the realtime page to refresh?
realtime.refresh_time=18


# Do we consider callback agents permanent until logoff (default as Asterisk 1.2), or do they get disconnected at restart?
default.permanentCallBackAgents=true

# Do we take into consideration sessions and calls that are not complet eyet for the main analysis?
default.considerIncompletetEntities=true

#callFile spool dir, set to blank for no call files at all.
callfile.dir=/var/spool/asterisk/outgoing

callfile.monitoring.enabled=true
callfile.monitoring.channel=Local/$EM@from-internal/n
callfile.monitoring.extension=11
callfile.monitoring.context=queuemetrics

callfile.outmonitoring.enabled=true
callfile.outmonitoring.channel=Local/$EM@from-internal/n
callfile.outmonitoring.extension=14
callfile.outmonitoring.context=queuemetrics

callfile.agentdial.enabled=true
callfile.agentdial.channel=Local/13@queuemetrics/n
callfile.agentdial.extension=10
callfile.agentdial.context=queuemetrics

callfile.calloutcome.enabled=true
callfile.calloutcome.channel=Local/12@queuemetrics/n
callfile.calloutcome.extension=10
callfile.calloutcome.context=queuemetrics

callfile.agentlogin.enabled=true
callfile.agentlogin.channel=Local/20@queuemetrics/n
callfile.agentlogin.extension=10
callfile.agentlogin.context=queuemetrics

callfile.agentlogoff.enabled=true
callfile.agentlogoff.channel=Local/21@queuemetrics/n
callfile.agentlogoff.extension=10
callfile.agentlogoff.context=queuemetrics

callfile.agentpause.enabled=true
callfile.agentpause.channel=Local/22@queuemetrics/n
callfile.agentpause.extension=10
callfile.agentpause.context=queuemetrics

callfile.agentunpause.enabled=true
callfile.agentunpause.channel=Local/23@queuemetrics/n
callfile.agentunpause.extension=10
callfile.agentunpause.context=queuemetrics


# Rewrites Local/XXX@ext channels in the format Agent/XXX
default.rewriteLocalChannels=true

# DO NOT CHANGE
realtime.use_sql_now=false

#The default time zone offset. Valid values -24...24 hours
default.timeZoneOffset=0

#If an ongoing call has a wait time that exceeds this value, drop it. 0: ignore
default.maxOngoingWaitTime=1000

#If an ongoing call has a talk time that exceeds this value, drop it. 0: ignore
default.maxOngoingTalkTime=2000

#Ignore QUEUESTART statements.
default.ignoreQueueStarts=false


#The hour of the day to start realtime monitoring or sXX: sliding window of XX hours
realtime.startHour=0


# The default locale for QueueMetrics. DO NOT CHANGE unless you know what you're doing.
default.language=fr
default.country=FR

# Which channel name to show for a call: True: last one seen - False: first one found
default.useEndingChannelName=true

# How to create Excel files: True: Excel 2003 XML - False: CSV
default.useXmlExcel=false

# Show the queue composition or just the main queue name
default.showQueueComposition=true


# how long is an hour for hourly breakdown; default 60 minutes
default.hourly_slot=15

#Add the following key to enable default showing of all subqueues (default: no):
realtime.all_subqueues=0

# Join multi-stint calls as a default
default.joinMultiStintCalls=false

# Uses the starting or ending channel name in case they're different.
default.useEndingChannelName=false


# Decide whether to check for alarms on the wait time of ongoing conversations.
realtime.waitAlarmOnLiveCalls=true
sound.yellowAlarm=../img/sounds/dingdong.wav
sound.redAlarm=../img/sounds/ringer.wav



# Cluster to poll for RT data
# list of pipe-separated members of the cluster
# if callfilesdir = "", the commands will be sent through the manager API
cluster.servers=aleph|trix

cluster.aleph.manager=tcp:dial:12345@10.10.3.5
cluster.aleph.queuelog=sql:P001
cluster.aleph.monitored_calls=z:/qm_streamcall/server_aleph
cluster.aleph.callfilesdir=
cluster.aleph.audioRpcServer=
cluster.aleph.agentSecurityKey=

cluster.trix.manager=tcp:admin:amp111@127.0.0.1
cluster.trix.queuelog=sql:P002
cluster.trix.monitored_calls=z:/qm_streamcall/server_trix
cluster.trix.callfilesdir=
cluster.trix.audioRpcServer=
cluster.trix.agentSecurityKey=


# If true, hide export buttons on the Realtime page
realtime.hideExportButtons=false

# If true, show all agent sessions. If false, show only agent sessions with at least one call.
default.useRawAgentSessions=false

# If true, the agent cannot change their code in the login/logoff/pause popups.
default.lockedAgentPopupCode=false

#If true, the user will not be able to monitor without a given location.
realtime.assignedLocationsOnly=false



[QueueMetrics]

Have you tried using only sql:P000 instead of sql:P000|1 ?
Also, are you sure that all the fields in:

Code: [Select]

sqlPreset.1.table=queue_log
sqlPreset.1.f_time_id=time_id
sqlPreset.1.f_call_id=call_id
sqlPreset.1.f_queue=queue
sqlPreset.1.f_agent=agent
sqlPreset.1.f_verb=verb
sqlPreset.1.f_partition=partition
sqlPreset.1.f_data1=data1
sqlPreset.1.f_data2=data2
sqlPreset.1.f_data3=data3
sqlPreset.1.f_data4=data4
sqlPreset.1.f_incr=unique_row_count
have the same name as the table columns?

[silmaril]

Well yes, it's the standard queue_log table from your sql file:

mysql> describe queue_log;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| partition        | varchar(20)      | NO   | MUL |         |                |
| time_id          | int(11) unsigned | NO   |     | 0       |                |
| call_id          | varchar(30)      | NO   |     |         |                |
| queue            | varchar(30)      | NO   |     |         |                |
| agent            | varchar(30)      | NO   |     |         |                |
| verb             | varchar(30)      | NO   |     |         |                |
| data1            | varchar(255)     | NO   |     |         |                |
| data2            | varchar(30)      | NO   |     |         |                |
| data3            | varchar(30)      | NO   |     |         |                |
| data4            | varchar(30)      | NO   |     |         |                |
| serverid         | varchar(10)      | NO   |     |         |                |
| unique_row_count | int(10) unsigned | NO   |     | NULL    | auto_increment |
+------------------+------------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)


Also i tried

sql:
sql:P000
sql:P000|1
sql:|2

Edit:
Also what i don't understand is why is there no java exception, nor useful data in the log.
As i said queuemetrics isn't even accessing the database, except for fetching the agent list
before failing with that error

[QueueMetrics]

One stupid thing: are you sure you can run a select * on queue_log, i.e. the table is not ruined?

[silmaril]

Well yes the table is fine:

mysql> check table queue_log;
+------------------------+-------+----------+----------+
| Table                  | Op    | Msg_type | Msg_text |
+------------------------+-------+----------+----------+
| queuemetrics.queue_log | check | status   | OK       |
+------------------------+-------+----------+----------+
1 row in set (0.03 sec)


and i can do a select *

[silmaril]

Ok so it is most probably a SecurityPolicy issue.
When i start tomcat without security manager things works

Here are the rules i've added for queuemetrics so far:

grant codeBase "file:/usr/share/tomcat5.5-webapps/queuemetrics/-" {
 permission java.net.SocketPermission "localhost:3306", "connect,resolve";
 permission java.io.FilePermission "/var/log/asterisk/queue_log", "read";
 permission java.util.PropertyPermission "*", "read,write";
};

[QueueMetrics]

I really find this strange, as there is no different operation done when fetching data for the analysis versus loading, say, user prefrences from the database. Your suggested permissions are even excessive, as QM does not need write access to its own webapp. Still, if it's a security problem.... disable the security manager 

[silmaril]

Your suggested permissions are even excessive, as QM does not need write access to its own webapp.

Well my permission surely did not allow that.
I added read/write permission to java property for the license.jsp page that was requesting it.

Still, if it's a security problem.... disable the security manager  Grin

well, that's ... not an expected answer

[QueueMetrics]

We are tracking this issue as bug #365. Some of our users reported that:

grant {
  permission java.net.SocketPermission "localhost:3306", "connect,resolve";
  permission java.io.FilePermission "/var/log/asterisk/queue_log",    "read";
 permission java.security.AllPermission;
};

with the last permissin being used only to pass the DBTest.

[silmaril]

permission java.security.AllPermission;

Well, to me that's the same as removing the security manager.

[QueueMetrics]

We are working on this issue.

[QueueMetrics]

see the solution at http://forum.queuemetrics.com/index.php?topic=284.msg1273#msg1273