MySQL No Active Call Rcds; CDRs

Discussions about using SIP Sorcery on your own computer/server
Aaron
Site Admin
Posts: 4652
Joined: Thu Jul 12, 2007 12:13 am

Re: MySQL No Active Call Rcds; CDRs

Post by Aaron » Thu Jul 01, 2010 12:00 pm


Trimline
Posts: 28
Joined: Sat May 15, 2010 7:59 pm

Re: MySQL No Active Call Rcds; CDRs

Post by Trimline » Thu Jul 01, 2010 1:10 pm

Yes, I tried that as well. There are not that many differences between 39246 and 47816 (see attached compare). I'm still wondering what the difference is between the XML version (works) and the SQL version. I tried to reverse engineer the XML to DDL, and that didn't work either. Is there something else that needs to be changed in the sipsorcery-appsvr.exe besides the path to the DB and password?

sqlcom-002.jpg
Compare
sqlcom-002.jpg (211.5 KiB) Viewed 3186 times

Aaron
Site Admin
Posts: 4652
Joined: Thu Jul 12, 2007 12:13 am

Re: MySQL No Active Call Rcds; CDRs

Post by Aaron » Thu Jul 01, 2010 1:37 pm

In some cases it will only take a single mising column to break the ability to use the database. If I recall correctly all the DDL updates I've done since v1.2 only add columns so the latest schema should work. I'll try and check it on the weekend.

The critical settings you need to update in your config file to use a MySQL database are:

<add key="PersistenceStorageType" value="SQLLinqMySQL" />
<add key="PersistenceConnStr" value="Database=sipsorcery;Data Source=localhost;User Id=root;Password=password" />

Two additional settings also need to be udpated if you want to use the DBRead and DBWrite functions in the dialplan:

<add key="UserDataDBType" value="MSSQL" />
<add key="UserDataDBConnStr" value="Database=sipsorcery;Data Source=localhost;User Id=root;Password=password" />

The differences between using XML and using an SQL database are confined to the persistence layer of sipsorcery but that layer is critical to the software's operation. Whether or not the persistence layer works smoothly with XML is largely irrelevant to whether it will work with an SQL database.

Trimline
Posts: 28
Joined: Sat May 15, 2010 7:59 pm

Re: MySQL No Active Call Rcds; CDRs

Post by Trimline » Thu Jul 01, 2010 4:34 pm

From the error log:

2010-07-01 12:28:15,176 [dialplanscript-1] ERROR sipsorcery [(null)] - Exception SQLAssetPersistor Add (for SIPDialogueAsset). Field 'direction' doesn't have a default value
2010-07-01 12:28:15,207 [dialplanscript-1] ERROR dialplan [(null)] - Exception DialPlanContext CallAnswered. Field 'direction' doesn't have a default value

I've reread the installation docs and notes and can only find reference to the DB in the config.exe - you mentioned adding the add key="UserDataDBType" and <add key="UserDataDBConnStr". I added under the current DB reference and still see the same results. I know we are getting close here....

Keep in mind, this is a "fresh" v1.2 install. I've never installed V.1.

Aaron
Site Admin
Posts: 4652
Joined: Thu Jul 12, 2007 12:13 am

Re: MySQL No Active Call Rcds; CDRs

Post by Aaron » Sat Jul 03, 2010 12:58 am

I've been able to use a sipsorcery version 1.2 with the latest database schema by making one modification which is setting a default value on the direction column in the sipdialgoues table. It doesn't matter what the default value is, I used a "?", as the sipsorcery 1.2 code does not know how to use that column anyway. After that change is made you should get your CDRs generated correctly and call hangups will work properly.

Edit: Actually you could also just leave the direction column out or set it as nullable.

Trimline
Posts: 28
Joined: Sat May 15, 2010 7:59 pm

Re: MySQL No Active Call Rcds; CDRs

Post by Trimline » Sat Jul 03, 2010 12:28 pm

I dropped the column and that caused the following error:

2010-07-03 06:39:21,595 [dialplanscript-2] ERROR sipsorcery [(null)] - Exception SQLAssetPersistor Add (for SIPDialogueAsset). Field 'dialogueid' doesn't have a default value
2010-07-03 06:39:21,595 [dialplanscript-2] ERROR dialplan [(null)] - Exception DialPlanContext CallAnswered. Field 'dialogueid' doesn't have a default value

So, I dropped the entire table and created with the following - worked like a charm!

create table sipdialogues
(
id varchar(36) null,
owner varchar(32) null,
adminmemberid varchar(32),
localtag varchar(64) null,
remotetag varchar(64) null,
callid varchar(128) null,
cseq int null,
bridgeid varchar(36) null,
remotetarget varchar(256) null,
localuserfield varchar(512) null,
remoteuserfield varchar(512) null,
proxysipsocket varchar(64),
routeset varchar(512),
cdrid varchar(36) null,
calldurationlimit int,
inserted varchar(33) null,
hangupat varchar(33) null default null,
transfermode varchar(16),
direction varchar(3) null default null, -- In or Out with respect to the proxy.
sdp varchar(2048),
remotesdp varchar(2048),
switchboarddescription varchar(1024),
switchboardcallerdescription varchar(1024),
switchboardowner varchar(32),
Primary Key(id),
Foreign Key(owner) references Customers(customerusername) on delete cascade on update cascade
);


Thanks for all of your help!

old-ocker
Posts: 17
Joined: Sun Dec 27, 2009 1:23 pm

Re: MySQL No Active Call Rcds; CDRs

Post by old-ocker » Sun Jul 31, 2011 3:41 am

I have the same problem.
Error file in logs directory shows:-

2011-07-31 10:58:13,758 [dialplanscript-1] ERROR sipsorcery [(null)] - Exception SQLAssetPersistor Add (for SIPDialogueAsset). Field 'direction' doesn't have a default value
2011-07-31 10:58:13,759 [dialplanscript-1] ERROR dialplan [(null)] - Exception DialPlanContext CallAnswered. Field 'direction' doesn't have a default value

After a call the console shows:-

No dialogue matched for BYE

I edited sipdialogue table as per previous post by Trimline and set default value of direction column to ?
This made no difference - still get same error.

The root.log file in logs directory shows:-

2011-07-31 10:50:56,334 [3] DEBUG sipsorcery [(null)] - SIPMonitorDaemon starting...
2011-07-31 10:50:56,337 [3] DEBUG sipsorcery [(null)] - The SIP Monitor Server was successfully started, loopback port 10001.
2011-07-31 10:50:56,341 [3] WARN sipsorcery [(null)] - Exception starting internal notifications TCP hosted service. Service 'SIPSorcery.Web.Services.SIPMonitorClientManagerHost' has zero application (non-infrastructure) endpoints. This might be because no configuration file was found for your application, or because no service element matching the service name could be found in the configuration file, or because no endpoints were defined in the service element.

Anyone help please.

Aaron
Site Admin
Posts: 4652
Joined: Thu Jul 12, 2007 12:13 am

Re: MySQL No Active Call Rcds; CDRs

Post by Aaron » Sun Jul 31, 2011 10:09 pm

Did you drop the sipdialogues table as per Trimlines's post? The error message you are getting when recording a CDR indicates there is still an issue with the direction column which is what the new sipdialogues schema in Trimline's post fixes.

old-ocker
Posts: 17
Joined: Sun Dec 27, 2009 1:23 pm

Re: MySQL No Active Call Rcds; CDRs

Post by old-ocker » Mon Aug 01, 2011 4:46 am

No I edited the sipdialogues columns to Trimlines script.

So next I deleted sipdialogues table and ran Trimlines script - got same error.
Changed the direction column default to "?" - got same error.
Deleted direction column - got same error.
Gave up.

Post Reply