[SOLVED] ODBC | MySQL | calldate empty

Asterisk Asterisk Support
1

Hi.
I have set up odbc (adaptive) with mysql and table cdr gets populated.
Nevertheless field calldate is not stored correctly. Every record is filled with default value like stated when table was created:
calldate datetime NOT NULL default ‘0000-00-00 00:00:00’

Do you know why this happens and what I have to do to get it filled with the date/time the system is using?
I found settings in cdr.conf but it looks to me that they are not relevant, am I wrong?

My cdr.conf is looking like this:
[odbc]
usegmtime=no ; log date/time in GMT. Default is “no”
loguniqueid=yes ; log uniqueid. Default is “no”
loguserfield=yes ; log user field. Default is “no”
accountlogs=yes ; create separate log file for each account code. Default is “yes”
newcdrcolumns=yes ; Enable logging of post-1.8 CDR columns (peeraccount, linkedid, sequence).
; Default is “no”.

Thanks,
_fuz

2

Okay just for the records in case this could help someone else:

calldate is no native field, so it is not populated. If you want to see the native supported fields, take a look in cdr_custom.conf and study the formats for Master.csv. If you add such a field to you table definition, it will get populated automatically. I added some fields, deleted calldate column and added a index over start column since I will use it in my WHERE clauses often. I am using myisam since there is no transaction handling needed and I guess in that case it would be faster than innodb. In the end my table looks like that:

cdr | CREATE TABLE cdr (
id int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
clid varchar(80) NOT NULL DEFAULT ‘’,
src varchar(80) NOT NULL DEFAULT ‘’,
dst varchar(80) NOT NULL DEFAULT ‘’,
dcontext varchar(80) NOT NULL DEFAULT ‘’,
channel varchar(80) NOT NULL DEFAULT ‘’,
dstchannel varchar(80) NOT NULL DEFAULT ‘’,
lastapp varchar(80) NOT NULL DEFAULT ‘’,
lastdata varchar(80) NOT NULL DEFAULT ‘’,
duration int(11) NOT NULL DEFAULT ‘0’,
billsec int(11) NOT NULL DEFAULT ‘0’,
disposition varchar(45) NOT NULL DEFAULT ‘’,
amaflags int(11) NOT NULL DEFAULT ‘0’,
accountcode varchar(20) NOT NULL DEFAULT ‘’,
uniqueid varchar(32) NOT NULL DEFAULT ‘’,
userfield varchar(255) NOT NULL DEFAULT ‘’,
peeraccount varchar(20) NOT NULL DEFAULT ‘’,
linkedid varchar(32) NOT NULL DEFAULT ‘’,
sequence int(11) NOT NULL DEFAULT ‘0’,
start datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
answer datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
end datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
PRIMARY KEY (id),
KEY datetime_start_ndx (start)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4

Hope that helps someone.
_fuz

3

Leave away AUTO_INCREMENT=6 if you use it.