vicidial.org

by **heinz** » Mon Feb 11, 2008 7:32 am

Hi,

we got a new database server so I made a backup of our production-Database and uploaded it to the new server. It was impossible to get the backup into the new MySQL-DB without changing the uniqueid-field in call_log and vicidial_log from FLOAT to VARCHAR (problems with primary key).

Since then everything is up and running again - except there are no entries in the vicidial_log-Table for users which are autodialing. I get entries with user=VDAD and if the user is doing manualdialing (comment=MANUAL).

I think the problem is the uniqueid because the VDAD entries have a proper uniqueid like 1202721136.6150 but the entries where the user-field is filled have entries like 1202721136.6150999 or 1202721136.6150001.

Anyone an Idea how to solve this problem???

Vicidial 2.0.3
Asterisk 1.2.21.1
MYSQL 5.0.45 (old and new DB-server)

Thanks,
heinz

by **heinz** » Mon Feb 11, 2008 1:44 pm

Last thouhgts befor I go to bed:

I changed the field_type from FLOAT to VARCHAR of uniqueid in tables

call_log
vicidial_log

but not in

park_log
vicidial_manager
vicidial_live_agents
vicidial_auto_calls

Is that causing my troubles ???

Thanks,
heinz

by **mflorell** » Tue Feb 12, 2008 2:02 am

This is an issue that a few MySQL 5.0.XX users have reported to me. Changing uniqueid to a VARCHAR(30) in all tables fixed the issue.

In a future version of astguiclient we will most likely make this change across the board and move to MySQL 5.0.XX as a recommended database version.

by **heinz** » Tue Feb 12, 2008 3:03 am

HI Matt,

I changed ALL the uniqueid -fields to varchar(30) and it's working again.

Thanks for listening...

heinz

by **mflorell** » Tue Feb 12, 2008 7:43 pm

I'm glad that worked for you.

Just as a note, that is something that the users of the BRIstuff edition of Asterisk have to do as well since they have hostname appended to their uniqueid string.

by **enjay** » Tue Feb 19, 2008 7:06 am

I too have been seen some wierdness in the database. For instance when looking up lead it shows the Status as QUEUE under "CALLS TO THIS LEAD" however the disposition was SALE.

2289 of the entries in vicidial_log have status set as QUEUE, is this normal?

It appears that I receive around 400 of these per day..

Just an UPDATE,

looking at some of these today it appears that for some reason the

UPDATE vicidial_log set status = "whateverdispo" is not run. I try searching for the entry in agiout and it just purely does not exist.

I found this wierd also..

Code: Select all: mysql> select * from vicidial_log where uniqueid="1203455971.4015911"; Empty set (0.01 sec) mysql> select * from vicidial_log where uniqueid LIKE "1203455971.4015911"; +--------------------+---------+---------+-------------+---------------------+-------------+-----------+---------------+--------+------------+--------------+------+----------+-----------+------------+ | uniqueid | lead_id | list_id | campaign_id | call_date | start_epoch | end_epoch | length_in_sec | status | phone_code | phone_number | user | comments | processed | user_group | +--------------------+---------+---------+-------------+---------------------+-------------+-----------+---------------+--------+------------+--------------+------+----------+-----------+------------+ | 1203455971.4015911 | 156796 | NULL | AOA | 2008-02-19 16:19:41 | 1203455981 | NULL | NULL | QUEUE | 1 | 3366254359 | VDAD | NULL | N | NULL | +--------------------+---------+---------+-------------+---------------------+-------------+-----------+---------------+--------+------------+--------------+------+----------+-----------+------------+ 1 row in set (0.41 sec) mysql> mysql> select * from vicidial_list where lead_id="156796"; +---------+---------------------+---------------------+--------+------+------------------+-----------+----------+----------------+-------------------------+------------+--------------+-------+----------------+----------------+-----------+-----------------------+----------+----------+----------+-------+----------+-------------+--------------+--------+---------------+-----------+-------+-----------------+----------+--------------+ | lead_id | entry_date | modify_date | status | user | vendor_lead_code | source_id | list_id | gmt_offset_now | called_since_last_reset | phone_code | phone_number | title | first_name | middle_initial | last_name | address1 | address2 | address3 | city | state | province | postal_code | country_code | gender | date_of_birth | alt_phone | email | security_phrase | comments | called_count | +---------+---------------------+---------------------+--------+------+------------------+-----------+----------+----------------+-------------------------+------------+--------------+-------+----------------+----------------+-----------+-----------------------+----------+----------+----------+-------+----------+-------------+--------------+--------+---------------+-----------+-------+-----------------+----------+--------------+ | 156796 | 2008-02-06 15:14:16 | 2008-02-19 16:20:30 | CBHOLD | 4335 | | | 20000004 | -5.00 | Y4 | 1 | 336XXXXXXX | Dr | XXX | | lastname | XXXE Dixie Dr | | | Asheboro | NC | | 27203 | | | 0000-00-00 | | | | | 8 | +---------+---------------------+---------------------+--------+------+------------------+-----------+----------+----------------+-------------------------+------------+--------------+-------+----------------+----------------+-----------+-----------------------+----------+----------+----------+-------+----------+-------------+--------------+--------+---------------+-----------+-------+-----------------+----------+--------------+ 1 row in set (0.00 sec)

seems wierd to me that there is no output in any logs showing that it changed the status for this lead in vicidial_list to DC.

by **mflorell** » Tue Feb 19, 2008 6:21 pm

What is the field type of uniqueid in your system enjay?

I am thinking that I need to change all of the uniqueid fields to VARCHAR and see if that fixes ther problem.

by **enjay** » Tue Feb 19, 2008 6:45 pm

uniqueid | double(18,7) | NO | PRI | NULL

I will change them to varchar(30) does anything else have to take place once the ALTER is done?

Thanks,
Art

by **mflorell** » Wed Feb 20, 2008 3:26 am

You need to change uniqueid in all tables that have that field to keep the values consistent. That should be it, no programming or config changes needed.

by **enjay** » Wed Feb 20, 2008 10:42 am

Done deal we'll see how it goes!

by **enjay** » Fri Feb 22, 2008 11:26 am

Well that definitely solved the majority of this problem, thanks again for the help!

One other issue I noticed is that status isnt matching up for a few other types of calls..

the vicidial_log shows NA as the status yet vicidial_list shows DC

Code: Select all: +------------------+---------+---------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+------+----------+-----------+------------+ | uniqueid | lead_id | list_id | campaign_id | call_date | start_epoch | end_epoch | length_in_sec | status | phone_code | phone_number | user | comments | processed | user_group | +------------------+---------+---------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+------+----------+-----------+------------+ | 1203617441.34415 | 189309 | NULL | CL | 2008-02-21 13:10:44 | 1203617444 | 1203617445 | 1 | NA | 1 | 301XXXXXXX | VDAD | NULL | N | NULL | +------------------+---------+---------+-------------+---------------------+-------------+------------+---------------+--------+------------+--------------+------+----------+-----------+------------+ 1 row in set (0.00 sec) mysql> select * from vicidial_list where lead_id="189309"; +---------+---------------------+---------------------+--------+------+------------------+-----------+----------+----------------+-------------------------+------------+--------------+-------+------------+----------------+-----------+--------------------+----------+----------+-----------+-------+----------+-------------+--------------+--------+---------------+-----------+------------------+-----------------+----------+--------------+ | lead_id | entry_date | modify_date | status | user | vendor_lead_code | source_id | list_id | gmt_offset_now | called_since_last_reset | phone_code | phone_number | title | first_name | middle_initial | last_name | address1 | address2 | address3 | city | state | province | postal_code | country_code | gender | date_of_birth | alt_phone | email | security_phrase | comments | called_count | +---------+---------------------+---------------------+--------+------+------------------+-----------+----------+----------------+-------------------------+------------+--------------+-------+------------+----------------+-----------+--------------------+----------+----------+-----------+-------+----------+-------------+--------------+--------+---------------+-----------+------------------+-----------------+----------+--------------+ | 189309 | 2008-02-20 13:47:01 | 2008-02-21 13:10:52 | DC | VDAD | | | 10000002 | -5.00 | Y | 1 | 301XXXXXXX | | JIM | | IXXXX | 77 XX ST STE 205 | | | ANNAPOLIS | MD | | 21XXX | | | 0000-00-00 | | XXx@GXXXAN.COM | MD | | 1 | +---------+---------------------+---------------------+--------+------+------------------+-----------+----------+----------------+-------------------------+------------+--------------+-------+------------+----------------+-----------+--------------------+----------+----------+-----------+-------+----------+-------------+--------------+--------+---------------+-----------+------------------+-----------------+----------+--------------+

FastAGIout

Code: Select all: 2008-02-21 13:10:41|TEST_VDfastAGI|begin|Perl Environment Dump: 2008-02-21 13:10:41|TEST_VDfastAGI|begin|0|--debug 2008-02-21 13:10:41|TEST_VDfastAGI|begin|URL HVcauses: |PRI|NODEBUG|0|||)| 2008-02-21 13:10:41|TEST_VDfastAGI|begin|AGI Environment Dump: 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- accountcode = 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- callerid = 7578211087 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- calleridname = V0221131041000189309 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- callingani2 = 0 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- callingpres = 0 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- callingtns = 0 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- callington = 0 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- channel = Local/913018506020@default-bed1,2 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- context = default 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- dnid = unknown 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- enhanced = 0.0 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- extension = 913018506020 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- language = en 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- network = yes 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- network_script = call_log 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- priority = 1 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- rdnis = unknown 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- request = agi://127.0.0.1:4577/call_log 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- type = Local 2008-02-21 13:10:41|TEST_VDfastAGI|begin| -- uniqueid = 1203617441.34415 2008-02-21 13:10:41|TEST_VDfastAGI|begin|AGI Variables: |1203617441.34415|Local/91301XXXXXXX@default-bed1,2|913018506020|Local|V0221131041000189309| 2008-02-21 13:10:41|TEST_VDfastAGI|call_log|Process to run: |agi://127.0.0.1:4577/call_log|call_log|START| 2008-02-21 13:10:41|TEST_VDfastAGI|call_log|+++++ CALL LOG START : 2008-02-21 13:10:41 2008-02-21 13:10:41|TEST_VDfastAGI|call_log||INSERT INTO call_log (uniqueid,channel,channel_group,type,server_ip,extension,number_dialed,start_time,start_epo ch,end_time,end_epoch,length_in_sec,length_in_min,caller_code) values('1203617441.34415','Local/91301XXXXXXX@default-bed1,2','','Local','192.168.10.71','9130 18506020','915419540440','2008-02-21 13:10:41','1203617441','','','','','V0221131041000189309')| 2008-02-21 13:10:43|TEST_VDfastAGI|begin|+++++++++++++++++ FastAGI Start ++++++++++++++++++++++++++++++++++++++++ 2008-02-21 13:10:43|TEST_VDfastAGI|begin|Perl Environment Dump: 2008-02-21 13:10:43|TEST_VDfastAGI|begin|0|--debug 2008-02-21 13:10:43|TEST_VDfastAGI|begin|URL HVcauses: |PRI|NODEBUG|0|||)| 2008-02-21 13:10:43|TEST_VDfastAGI|begin|AGI Environment Dump: 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- accountcode = 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- callerid = 7578211087 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- calleridname = V0221131041000189309 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- callingani2 = 0 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- callingpres = 0 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- callingtns = 0 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- callington = 0 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- channel = Local/913018506020@default-bed1,2 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- context = default 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- dnid = unknown 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- enhanced = 0.0 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- extension = h 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- language = en 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- network = yes 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- network_script = call_log--HVcauses--PRI-----NODEBUG-----1-----CHANUNAVAIL----------) 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- priority = 1 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- rdnis = unknown 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- request = agi://127.0.0.1:4577/call_log--HVcauses--PRI-----NODEBUG-----1-----CHANUNAVAIL----------) 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- type = Local 2008-02-21 13:10:43|TEST_VDfastAGI|begin| -- uniqueid = 1203617441.34415 2008-02-21 13:10:43|TEST_VDfastAGI|begin|AGI Variables: |1203617441.34415|Local/91301XXXXXXX@default-bed1,2|h|Local|V0221131041000189309| 2008-02-21 13:10:43|TEST_VDfastAGI|call_log|Process to run: |agi://127.0.0.1:4577/call_log--HVcauses--PRI-----NODEBUG-----1-----CHANUNAVAIL----------)|call_l og|END| 2008-02-21 13:10:43|TEST_VDfastAGI|call_log||CALL HUNG UP| 2008-02-21 13:10:43|TEST_VDfastAGI|call_log||1203617441.34415|1203617441| 2008-02-21 13:10:43|TEST_VDfastAGI|call_log|QUERY done: start time = 1203617441 | sec: 2 | min: 0.03 | 2008-02-21 13:10:43|TEST_VDfastAGI|call_log||UPDATE call_log set end_time='2008-02-21 13:10:43',end_epoch='1203617443',length_in_sec=2,length_in_min=' 0.0 3',channel='Local/91301XXXXXXX@default-bed1,2' where uniqueid='1203617441.34415'| 2008-02-21 13:10:43|TEST_VDfastAGI|call_log||DELETE from live_inbound where uniqueid='1203617441.34415' and server_ip='192.168.10.71'| 2008-02-21 13:10:43|TEST_VDfastAGI|call_log|+++++ CALL LOG HUNGUP: |1203617441.34415|Local/91301XXXXXXX@default-bed1,2|h|2008-02-21 13:10:43|min: 0.03| 2008-02-21 13:10:43|TEST_VDfastAGI|call_log|DEBUG: NODEBUG 2008-02-21 13:10:43|TEST_VDfastAGI|call_log|VD_hangup : V0221131041000189309 Local/91301XXXXXXX@default-bed1,2 1 189309 2008-02-21 13:10:43|TEST_VDfastAGI|call_log|-- VD_hangup Local DEBUG: |PRI|V0221131041000189309||0| 2008-02-21 13:10:43|TEST_VDfastAGI|call_log|+++++ VDAD START LOCAL CHANNEL: EXITING- 1

vdautodial

Code: Select all: 2008-02-21 13:10:44|LIVE AGENTS LOGGED IN: 4 ACTIVE CALLS: 4| 2008-02-21 13:10:44|CL 192.168.10.71: agents: 2 dial_level: 1.1| 2008-02-21 13:10:44|CL 192.168.10.71: Calls to place: 0 (4 - 4) 4 | 2008-02-21 13:10:44|CAMPAIGN DIFFERENTIAL: 4 1.15 (1.15 - 0)| 2008-02-21 13:10:44|LOCAL TRUNK SHORTAGE: 0|0 (4 - 96)| 2008-02-21 13:10:44|CL 192.168.10.71: CALLING| 2008-02-21 13:10:44|| dead call vac deleted|134977|189309|V0221131041000189309|1203617443|1||| 2008-02-21 13:10:44|| dead NA call added to log 1203617441.34415|189309|3018506020|SENT|NA|1|| 2008-02-21 13:10:44|| dead call vac lead marked NA|189309|3018506020|SENT|| 2008-02-21 13:10:44|| dead call vla agent PAUSED 0E0|189309|3018506020|SENT|| 2008-02-21 13:10:44|| lagged call vla agent PAUSED 0E0|20080221131014|20080221131034|20080221131044|| 2008-02-21 13:10:44|| lagged call vac agent DELETED 0E0|2008-02-21 13:08:44|| 2008-02-21 13:10:44|| logindate UPDATED 1|'CL'||

UPDATE: actually it appears that it did try to update vicidial_log to set the status as DC however the unique did not match up exactly was off by 2 how does this happen and is there a solution for this kind of issue?

Code: Select all: 2008-02-21 13:10:51|TEST_VDfastAGI|call_log||UPDATE vicidial_log set status='DC' where uniqueid = '1203617431.34413';|

the actual uniqueid was 1203617431.34415

by **mflorell** » Fri Feb 22, 2008 12:23 pm

That can happen because a call can have as many as 4 uniqueids. This is because of how Local/ channels are treated in Asterisk. Can you see if there are any entries in the agiout logfile for the right uniqueid?

by **enjay** » Fri Feb 22, 2008 12:30 pm

That makes sense..

There are absolutely no entries in agiout for the correct uniqueid just in FASTagiout

by **mflorell** » Sat Feb 23, 2008 1:47 pm

Maybe I should change that code to work with CallerIDname instead, that would involve quite a few changes, but may be better for the long run.

by **mflorell** » Sun Feb 24, 2008 3:52 am

I have made several changes and fixes to the FastAGIlog.pl script in SVN trunk. Please try it out and let me know if you still have this problem.

by **enjay** » Sun Feb 24, 2008 2:05 pm

I'll give it a shot!

vicidial.org

Vicidial_log entries not working

Vicidial_log entries not working

Who is online