[Home]

Summary:ASTERISK-00572: [patch] cdr_unixodbc
Reporter:Brian West (bkw918)Labels:
Date Opened:2003-11-25 16:36:13.000-0600Date Closed:2004-09-25 02:20:08
Priority:MajorRegression?No
Status:Closed/CompleteComponents:Core/General
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:( 0) cdr_unixodbc.tar.gz
Description:Here is a new play toy.  Right now I have tested it with flatfile(pipe delimited) and mysql(via myodbc driver).  unixODBC is also LGPL.

****** ADDITIONAL INFORMATION ******

odbc.ini:
[ODBC Data Sources]
MySQL-asterisk = MySQL ODBC Driver asterisk DSN
Text-test = ODBC for Text Files

# see http://www.mysql.com/products/myodbc/faq_toc.html
# for details about the following entry
[MySQL-asterisk]
Description     = MySQL ODBC Driver asterisk DSN
Driver          = MySQL
Socket          = /var/run/mysqld/mysqld.sock
Server          = localhost
User            = username
Database        = asterisk
Option          = 3
#Port           =
Password        = password

[Text-asterisk]
Description     = ODBC for Text Files asterisk
Driver          = Text
Database        = /root/flatfile
Trace           = Yes
Tracefile       = /root/trace.log
Username        = username
Password        = password


odbcinst.ini
[MySQL]
Description     = MySQL ODBC MyODBC Driver
Driver          = /usr/lib/libmyodbc3.so

[Text]
Description     = ODBC for Text Files
Driver          = /usr/lib/libodbctxt.so
Setup           = /usr/lib/libodbctxtS.so
FileUsage       = 1
CPTimeout       =
CPReuse         =


cdr_unixodbc.conf
[global]
dsn=MySQL-asterisk|Text-asterisk
username=username
password=password
loguniqueid=yes


These all work.. anyone wish to test pgsql, oracle or even db2?
Comments:By: ben_au (ben_au) 2003-11-25 18:52:12.000-0600

Yep, works with Microsoft SQL Server 7.0 using FreeTDS
By: Brian West (bkw918) 2003-11-25 19:19:08.000-0600

Care to post odbc.ini and odbcinst.ini examples?
By: ben_au (ben_au) 2003-11-25 19:38:28.000-0600

Sure, here are the bits I've got for MS SQL Server 7.0:

Instructions for getting FreeTDS to work with UnixODBC are here:
http://www.unixodbc.org/doc/FreeTDS.html


freetds.conf
[demeter]
host = 10.0.0.2
port = 1433
tds version = 7.0


odbcinst.ini
[TDS]
Description = v0.61.2 with protocol v7.0
Driver = /usr/local/freetds/lib/libtdsodbc.so
FileUsage = 3


odbc.ini
[cdr]
Driver = TDS
Description = Asterisk Call Detail Records
Trace = /root/sqltrace
Servername = demeter
Database = asterisk
UID = asteriskuser


cdr_unixodbc.conf
[global]
dsn=cdr
username=asteriskuser
password=749zto249
loguniqueid=yes

edited on: 11-25-03 19:37
By: Brian West (bkw918) 2003-11-26 01:10:58.000-0600

Just did an insert test.. did 35,000 cdr inserts in under 40 min.  Note don't drop more than 1000 call files in the outgoing directory.
By: Brian West (bkw918) 2003-11-26 13:00:52.000-0600

odbcinst.ini
[PostgreSQL]
Description     = PostgreSQL driver for Linux & Win32
Driver          = /usr/lib/libodbcpsql.so
Setup           = /usr/lib/libodbcpsqlS.so
FileUsage       = 1

odbc.ini
[PostgreSQL-asterisk]
Description         = Test to Postgres
Driver              = PostgreSQL
Trace               = Yes
TraceFile           = sql.log
Database            = asterisk
Servername          = localhost
UserName            = username
Password            = password
Port                = 5432
Protocol            = 7.4
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No
ConnSettings        =


works perfect with pgsql.
By: ben_au (ben_au) 2003-11-26 17:06:33.000-0600

I get this flash past quite often:

      > cdr_unixodbc: Error in INSERT -1
      > cdr_unixodbc: INSERT FAILED Call not logged!
      > cdr_unixodbc: Connected to cdr
      > cdr_unixodbc: Reconnecting1 to dsn cdr
      > cdr_unixodbc: Trying to INSERT Record again!

I know I'm using a Microsoft product, but after it tries to insert the record again, it doesn't show a result...

Could it also show the result of the retry?
By: Brian West (bkw918) 2003-11-26 18:03:48.000-0600

Its accually helpful if we call the unixodbc_insert() again after we reconnect.  My Bad!  Fixed and attached.
By: Brian West (bkw918) 2003-11-26 18:12:01.000-0600

asterisk*CLI> set verbose 4
   -- Executing Answer("SIP/10-dcad", "") in new stack
   -- Executing MusicOnHold("SIP/10-dcad", "default") in new stack
   -- Started music on hold, class 'default', on SIP/10-dcad
   -- Stopped music on hold on SIP/10-dcad
 == Spawn extension (default, 999, 2) exited non-zero on 'SIP/10-dcad'
      > cdr_unixodbc: ODBC SQL INSERT Successful!
   -- Executing Answer("SIP/10-3799", "") in new stack
   -- Executing MusicOnHold("SIP/10-3799", "default") in new stack
   -- Started music on hold, class 'default', on SIP/10-3799
   -- Stopped music on hold on SIP/10-3799
 == Spawn extension (default, 999, 2) exited non-zero on 'SIP/10-3799'
      > cdr_unixodbc: Error in INSERT -1
      > cdr_unixodbc: INSERT FAILED Call not logged!
      > cdr_unixodbc: Error SQLConnect -1
      > cdr_unixodbc: Reconnecting1 to dsn MySQL-asterisk
      > cdr_unixodbc: MySQL-asterisk has gone away1!
   -- Executing Answer("SIP/10-afda", "") in new stack
   -- Executing MusicOnHold("SIP/10-afda", "default") in new stack
   -- Started music on hold, class 'default', on SIP/10-afda
   -- Stopped music on hold on SIP/10-afda
 == Spawn extension (default, 999, 2) exited non-zero on 'SIP/10-afda'
      > cdr_unixodbc: Reconnecting2 to dsn MySQL-asterisk
      > cdr_unixodbc: Connected to MySQL-asterisk
      > cdr_unixodbc: Trying to INSERT Record again!
      > cdr_unixodbc: ODBC SQL INSERT Successful!
By: Brian West (bkw918) 2003-11-26 18:13:33.000-0600

Thats more like it! :P
By: ben_au (ben_au) 2003-11-26 22:20:53.000-0600

Right!

 == Spawn extension (pbx, 0, 6) exited non-zero on 'Zap/9-1'
      > cdr_unixodbc: Reconnecting2 to dsn cdr
      > cdr_unixodbc: Connected to cdr
      > cdr_unixodbc: Trying to INSERT Record again!
      > cdr_unixodbc: Error in INSERT -1
      > cdr_unixodbc: INSERT FAILED Call not logged!
   -- Hungup 'Zap/9-1'
   -- Executing Answer("Zap/9-1", "") in new stack
   -- Accepting call from '' to '0' on channel 9, span 1
   -- Executing DigitTimeout("Zap/9-1", "5") in new stack
   -- Set Digit Timeout to 5
   -- Executing ResponseTimeout("Zap/9-1", "30") in new stack
   -- Set Response Timeout to 30
   -- Executing BackGround("Zap/9-1", "pbx-enterphonenumber") in new stack
   -- Playing 'pbx-enterphonenumber'
 == CDR updated on Zap/9-1
   -- Executing Dial("Zap/9-1", "Zap/g2/183114140407078178|90|T") in new stack
   -- Called g2/183114140407078178
   -- Zap/33-1 is ringing
   -- Channel 10, span 1 got hangup
   -- Zap/33-1 answered Zap/9-1
   -- Attempting native bridge of Zap/9-1 and Zap/33-1
   -- Hungup 'Zap/32-1'
 == Spawn extension (pbx, 0294974000, 1) exited non-zero on 'Zap/10-1'
      > cdr_unixodbc: Reconnecting2 to dsn cdr
      > cdr_unixodbc: Connected to cdr
      > cdr_unixodbc: Trying to INSERT Record again!
      > cdr_unixodbc: ODBC SQL INSERT Successful!



Does it continue to retry, or does it just retry once then give up?
By: Brian West (bkw918) 2003-11-26 23:13:19.000-0600

Can't really continue.  One idea I have is a secondary DSN(Using the Text driver) to store them ... but if you notice the channel isn't released until the record is either logged or is discarded.  So we could spawn off a thread but with no delay on retry inserts it would quickly hammer the box to death.

Also I just finished up the unixodbc-vm-routines.h and have began testing it.  I will go back to this issue and see if we can solve it with maybe a secondary DSN setup.
By: Brian West (bkw918) 2003-11-27 00:01:47.000-0600

http://bugs.digium.com/bug_view_page.php?bug_id=0000586

More unixODBC fun! :P
By: Brian West (bkw918) 2003-11-27 21:04:51.000-0600

Fixed a small issue in the odbc cdr module .. found out when doing the voicemail routines. :P
By: Brian West (bkw918) 2003-11-28 23:43:27.000-0600

changed unixodbc_insert() to unixodbc_do_query()
By: Brian West (bkw918) 2003-11-29 13:15:30.000-0600

all that reconnect code isn't needed unixODBC will do that for us.  I might remove it.
By: Brian West (bkw918) 2003-12-01 19:48:50.000-0600

Added to CVS