Author Topic: Yate + MySQL connection  (Read 3688 times)

iKenny

  • Newbie
  • *
  • Posts: 18
    • View Profile
Yate + MySQL connection
« on: February 18, 2013, 04:57:55 AM »
Hello.
I have a problem with connecting yate to mysql database. I want to store call history in to mysql database
My mysql server is on the same machine as yate.
OS Ubuntu 12.04
The output message in debug mode is:
Sniffed 'database' time=1361183648.475115
  thread=0x88a6cc0 'Engine Worker'
  data=(nil)
  retval='(null)'
  param['account'] = 'yate'
  param['query'] = 'UPDATE cdrs SET ended = 1 WHERE ended IS NULL OR ended = 0'
  param['results'] = 'false'
Returned false 'database' delay=0.001216
  thread=0x88a6cc0 'Engine Worker'
  data=(nil)
  retval='(null)'
  param['account'] = 'yate'
  param['query'] = 'UPDATE cdrs SET ended = 1 WHERE ended IS NULL OR ended = 0'
  param['results'] = 'false'

My register config

[call.cdr]
; Queries for the CDR updating message

; critical: boolean: Reject all registrations and routing if query fails
;critical=yes

;initquery=UPDATE cdr SET ended=true WHERE ended IS NULL OR NOT ended
;cdr_initialize=INSERT INTO cdr VALUES(TIMESTAMP 'EPOCH' + INTERVAL '${time} s','${chan}','${address}','${direction}','${billid}','${caller}','${called}',INTERVAL '${duration} s',INTERVAL '${billtime} s',INTERVAL '${ringtime} s','${status}','${reason}',false)
;cdr_update=UPDATE cdr SET address='${address}',direction='${direction}',billid='${billid}',caller='${caller}',called='${called}',duration=INTERVAL '${duration} s',billtime=INTERVAL '${billtime} s',ringtime=INTERVAL '${ringtime} s',status='${status}',reason='${reason}' WHERE chan='${chan}' AND time=TIMESTAMP 'EPOCH' + INTERVAL '${time} s'
;cdr_finalize=UPDATE cdr SET address='${address}',direction='${direction}',billid='${billid}',caller='${caller}',called='${called}',duration=INTERVAL '${duration} s',billtime=INTERVAL '${billtime} s',ringtime=INTERVAL '${ringtime} s',status='${status}',reason='${reason}',ended=true WHERE chan='${chan}' AND time=TIMESTAMP 'EPOCH' + INTERVAL '${time} s'
critical=no

initquery=UPDATE cdrs SET ended = 1 WHERE ended IS NULL OR ended = 0

cdr_initialize=INSERT INTO cdrs VALUES(NULL, '${time}', '${billid}', '${chan}', '${address}', '${caller}', '${called}', '${billtime}', '${ringtime}', '${duration}', '${direction}', '${status}', '${reason}', 0)

cdr_update=UPDATE cdrs SET address = '${address}', direction = '${direction}', billid = '${billid}', caller = '${caller}', called = '${called}', duration = '${duration}', billtime = '${billtime}', ringtime = '${ringtime}', status = '${status}', reason = '${reason}' WHERE chan = ''${chan}' AND time = CAST(${time} AS UNSIGNED)

cdr_finalize=UPDATE cdrs SET address = '${address}', direction = '${direction}', billid = '${billid}', caller = '${caller}', called = '${called}', duration = '${duration}', billtime = '${billtime}', ringtime = '${ringtime}', status = '${status}', reason = '${reason}', ended = 1 WHERE chan = '${chan}' AND time = CAST(${time} AS UNSIGNED)

accound=yate

My mysqldb.conf is

[yate]
host=127.0.0.1
port=3306
database=yate
user=root
password=password

In modules i entered

pgsqldb.yate=true
register.yate=true
mysqldb.yate=true

i vahe installed yate-mysql package and recompiled yate.
But no result.

Thanks for help.

asymetrixs

  • Administrator
  • Newbie
  • *****
  • Posts: 47
    • View Profile
Re: Yate + MySQL connection
« Reply #1 on: February 18, 2013, 06:58:52 AM »
Check the example at the bottom:

http://docs.yate.ro/wiki/Register_Database_Schema

also disable pgsqldb.yate if you only use mysql.

iKenny

  • Newbie
  • *
  • Posts: 18
    • View Profile
Re: Yate + MySQL connection
« Reply #2 on: February 18, 2013, 07:43:47 AM »
You mean that the problem is in MySQL configuration, not in yate?

asymetrixs

  • Administrator
  • Newbie
  • *****
  • Posts: 47
    • View Profile
Re: Yate + MySQL connection
« Reply #3 on: February 18, 2013, 07:56:48 AM »
What actually is your problem? Please describe it in more detail. I don't see any.

iKenny

  • Newbie
  • *
  • Posts: 18
    • View Profile
Re: Yate + MySQL connection
« Reply #4 on: February 18, 2013, 03:57:01 PM »
Returned false 'database'
And when i make call from one softphone to another databsase stays empty.
I can connet to database through localhost and i can make enterys. But yate doesn't write cdr data in database.

asymetrixs

  • Administrator
  • Newbie
  • *****
  • Posts: 47
    • View Profile
Re: Yate + MySQL connection
« Reply #5 on: February 18, 2013, 04:10:15 PM »
1) disable pgsqldb.yate if you do not need it
2) check that in register.conf in section [general] call.cdr=yes is set, default is call.cdr=no
3) check what the name was you configured in mysqldb.conf as database connection name (the thing in []), default is [default]
4) set this name in register.conf in section [default] on property account=  so in this case account=default - that tells yate to use the default-connection for all queries if not specified otherwise.

describe your problem in more detail if it still exists, maybe with debug output (start yate with yate -vvvvvvv  and show us what you got, the more -v the more detailed the output is)

iKenny

  • Newbie
  • *
  • Posts: 18
    • View Profile
Re: Yate + MySQL connection
« Reply #6 on: March 28, 2013, 04:13:43 AM »
Found the solution, on very x 10 verbose start i have found that the mysql module wasn't created. The problem was that was need to be installed package yate-mysql and after that yate must be compiled with yate-mysql.

asymetrixs

  • Administrator
  • Newbie
  • *****
  • Posts: 47
    • View Profile
Re: Yate + MySQL connection
« Reply #7 on: April 19, 2013, 05:43:36 AM »
Good.

Just curious:
Do you use the queries from the website I mentioned or do you use your own queries?
Did you enabled connection pooling?