[asterisk-users] Help Please - Asterisk MYSQL interface seems to be eating data
Roderick A. Anderson
raanders at acm.org
Mon May 5 09:57:33 CDT 2008
Steve Totaro wrote:
> A quote from Tilghman Lesher from a previous post.
>
> "That's fine, but I have had the most horrid results using any distribution-
> supplied ODBC drivers. The best results are obtained by source-compiling
> the latest ODBC drivers, whether they be the MySQL ODBC Connector 3.51 or
> PsqlODBC. UnixODBC is fairly safe to use from distribution channels, however."
And according to the O'Reilly book ODBC is the way to go.
Though they use PostgreSQL for their examples and Asterisk is installed
on a CentOS system the instructions are really good. Getting it to work
with MySQL should be pretty simple and I'm sure on-line resources for
doing this are be out there.
<soapbox>
Personally I never use MySQL except in cases where I am under extreme
duress. Therefore I tried and tossed trixbox, AsteriskNOW, and
freeePBX. Yes I know I can get around the database engine issue but
that is what a distribution should be for: no hacking (or at least
not-too-much) required.
It is now CentOS 5, Asterisk from source, PostgreSQL (on another system)
and hand edited (for now anyway) *.conf files. Maybe AsteriskGUI later.
</soapbox>
Rod
--
>
> Thanks,
> Steve Totaro
>
> On Mon, May 5, 2008 at 10:06 AM, Steve Totaro
> <stotaro at totarotechnologies.com> wrote:
>> User intervention is required Al. You need to open a bug report on
>> mantis and anyone can edit the wiki (this is the nature of a wiki).
>> http://bugs.digium.com/main_page.php
>>
>> Anyways, I am fairly certain that the UnixODBC connector is the
>> "preferred" way of connecting to MySQL.
>>
>> Thanks,
>> Steve Totaro
>>
>>
>>
>> On Mon, May 5, 2008 at 9:29 AM, Al Baker <bwentdg at pipeline.com> wrote:
>> > I looked all over VOIP-INFO and ATFOT and could not find anything that
>> > said or even suggested not using the mysql driver.(except NOT to have
>> > BOTH drivers loaded at the same time). I could easily be missing
>> > something. But the apparent BUG I am seeing is at such a Basic and
>> > Simple Level of functionality that either DIGIUM ought to fix it ASAP or
>> > update VOIP-INFO pages and their own documentation to say "Broke - No
>> > Workie and We Are No Gonna Fixie" :)
>> >
>> >
>> >
>> > Steve Totaro wrote:
>> > > On Mon, May 5, 2008 at 4:21 AM, Al Baker <bwentdg at pipeline.com> wrote:
>> > >
>> > >> I would appreciate any and all advice on what appears to be a BUG (or a
>> > >> brainfart on my part) with the MySQL add-on for Asterisk this is of FEDORA 8
>> > >> fully patched with Asterisk Addons 1-4-6 with the Asterisk 1.4.18.1
>> > >>
>> > >> It appears that the interface "eats" the first field requested from a
>> > >> table. If only One Field is Requested from the Table , that field is eaten
>> > >> ENTIRELY by Asterisk. If several fields are requested, the First Field Is
>> > >> Eaten and the remaining filed are returned, but place in the WRONG Variable
>> > >> since the 1tst fileld data was eaten. In the DIALPLAN below I have tried 3
>> > >> Different ways to approach this.
>> > >>
>> > >> Extension 9999 – Get only ONE (1) field from Table
>> > >>
>> > >> Extension 8888 – Get THREE(3) fields from the Table and Quote Them.
>> > >>
>> > >> Extension 7777 - Get THREE(3) fields from the Table
>> > >>
>> > >> I have show the Output from the Asterisk CL for each, which clearly show
>> > >> that SOMETHING is not
>> > >> right. Maybe the Software, maybe the person using the software :)
>> > >>
>> > >> Here is the Table in the Database.
>> > >>
>> > >> mysql> select * from agent;
>> > >>
>> > >> +----------+---------+--------+------------+---------------------+
>> > >>
>> > >> | id | cust_id | status | phone | tlce |
>> > >>
>> > >> +----------+---------+--------+------------+---------------------+
>> > >> | 00000001 | NAMB | free | 1234567890 | 2008-04-17 02:32:02 |
>> > >>
>> > >> | 00000002 | NAMB | free | 2234567890 | 2008-04-17 02:32:02 |
>> > >>
>> > >> | 00000003 | NAMB | free | 3234567890 | 2008-04-17 02:32:02 |
>> > >>
>> > >> | 00000004 | NAMB | free | 4234567890 | 2008-04-17 02:32:02 |
>> > >> +----------+---------+--------+------------+---------------------+
>> > >>
>> > >> 4 rows in set (0.00 sec)
>> > >>
>> > >>
>> > >> Here is the DIALPLAN
>> > >>
>> > >> exten => 7777,1,MYSQL(Connect connid localhost ivr ivrxxx dtc)
>> > >>
>> > >> exten => 7777,n,MYSQL(Query resultid ${connid} SELECT\ cust_id\, \
>> > >> status\,\ tlce\ from\ agent\ where\ phone=\'1234567890\')
>> > >>
>> > >> exten => 7777,n,MYSQL(Fetch fetchid ${resultid} custid mystatus mytlce)
>> > >>
>> > >> exten => 7777,n,NoOp(CUSTID is ${custid} MYSTATUS is ${mystatus} MYTLCE is
>> > >> ${mytlce})
>> > >>
>> > >> exten => 7777,n,NoOp(FETCHID is ${fetchid} RESULUT ID is .. ${resultid}
>> > >> CONNID is ${connid})
>> > >>
>> > >> exten => 7777,n,MYSQL(Clear ${resultid})
>> > >>
>> > >> exten => 7777,n,MYSQL(Disconnect ${connid})
>> > >>
>> > >> exten => 7777,n,HANGUP
>> > >>
>> > >>
>> > >>
>> > >> exten => 8888,1,MYSQL(Query resultid ${connid} SELECT\ 'cust_id'\, \
>> > >> 'status'\,\ 'tlce'\ from\ agent\ where\ phone=\'1234567890\')
>> > >>
>> > >> exten => 8888,n,MYSQL(Fetch fetchid ${resultid} custid mystatus mytlce)
>> > >>
>> > >> exten => 8888,n,NoOp(CUSTID is ${custid} MYSTATUS is ${mystatus} MYTLCE is
>> > >> ${mytlce})
>> > >>
>> > >> exten => 8888,n,NoOp(FETCHID is ${fetchid} RESULUT ID is .. ${resultid}
>> > >> CONNID is ${connid})
>> > >>
>> > >> exten => 8888,n,MYSQL(Clear ${resultid})
>> > >>
>> > >> exten => 8888,n,MYSQL(Disconnect ${connid})
>> > >>
>> > >> exten => 8888,n,HANGUP
>> > >>
>> > >>
>> > >> exten => 9999,1,MYSQL(Connect connid localhost ivr ivrxxx dtc)
>> > >>
>> > >> exten => 9999,n,MYSQL(Query resultid ${connid} SELECT\ 'cust_id'\ from\
>> > >> agent\ where\ phone=\'1234567890\')
>> > >>
>> > >> exten => 9999,n,MYSQL(Fetch fetchid ${resultid} custid)
>> > >>
>> > >> exten => 9999,n,NoOp(CUSTID is ${custid})
>> > >>
>> > >> exten => 9999,n,NoOp(FETCHID is ${fetchid} RESULUT ID is .. ${resultid}
>> > >> CONNID is ${connid})
>> > >>
>> > >> exten => 9999,n,MYSQL(Clear ${resultid})
>> > >>
>> > >> exten => 9999,n,MYSQL(Disconnect ${connid})
>> > >>
>> > >> exten => 9999,n,HANGUP
>> > >>
>> > >>
>> > >>
>> > >>
>> > >> Here is the Asterisk CLI Output
>> > >>
>> > >> dial 9999
>> > >>
>> > >> == Console is full duplex
>> > >>
>> > >> *CLI> -- Executing [9999 at default:1] MYSQL("OSS/dsp", "Connect connid
>> > >> localhost ivr ivrxxx dtc") in new stack
>> > >>
>> > >> -- Executing [9999 at default:2] MYSQL("OSS/dsp", "Query resultid 5 SELECT
>> > >> cust_id from agent where phone='1234567890'") in new stack
>> > >>
>> > >> -- Executing [9999 at default:3] MYSQL("OSS/dsp", "Fetch fetchid 6 custid") in
>> > >> new stack
>> > >>
>> > >> -- Executing [9999 at default:4] NoOp("OSS/dsp", "CUSTID is ") in new stack
>> > >>
>> > >> -- Executing [9999 at default:5] NoOp("OSS/dsp", "FETCHID is 1 RESULUT ID is
>> > >> .. 6 CONNID is 5") in new stack
>> > >>
>> > >> -- Executing [9999 at default:6] MYSQL("OSS/dsp", "Clear 6") in new stack
>> > >>
>> > >> -- Executing [9999 at default:7] MYSQL("OSS/dsp", "Disconnect 5") in new stack
>> > >>
>> > >> -- Executing [9999 at default:8] Hangup("OSS/dsp", "") in new stack
>> > >>
>> > >> == Spawn extension (default, 9999, 8) exited non-zero on 'OSS/dsp'
>> > >>
>> > >> << Hangup on console
>> > >>
>> > >> *CLI> dial 8888
>> > >>
>> > >> == Console is full duplex
>> > >>
>> > >> *CLI> -- Executing [8888 at default:1] MYSQL("OSS/dsp", "Connect connid
>> > >> localhost ivr ivrxxx dtc") in new stack
>> > >>
>> > >> -- Executing [8888 at default:2] MYSQL("OSS/dsp", "Query resultid 5 SELECT
>> > >> cust_id, status, tlce from agent where phone='1234567890'") in new stack
>> > >>
>> > >> -- Executing [8888 at default:3] MYSQL("OSS/dsp", "Fetch fetchid 6 custid
>> > >> mystatus mytlce") in new stack
>> > >>
>> > >> -- Executing [8888 at default:4] NoOp("OSS/dsp", "CUSTID is free MYSTATUS is
>> > >> 2008-04-17 02:32:02 MYTLCE is ") in new stack
>> > >>
>> > >> -- Executing [8888 at default:5] NoOp("OSS/dsp", "FETCHID is 1 RESULUT ID is
>> > >> .. 6 CONNID is 5") in new stack
>> > >>
>> > >> -- Executing [8888 at default:6] MYSQL("OSS/dsp", "Clear 6") in new stack
>> > >>
>> > >> -- Executing [8888 at default:7] MYSQL("OSS/dsp", "Disconnect 5") in new stack
>> > >>
>> > >> -- Executing [8888 at default:8] Hangup("OSS/dsp", "") in new stack
>> > >>
>> > >> == Spawn extension (default, 8888, 8) exited non-zero on 'OSS/dsp'
>> > >>
>> > >> << Hangup on console >
>> > >>
>> > >> OK – Have I done something really dumb or is this a BUG ?
>> > >> Thank VERY MUCH for any and ALL Help
>> > >>
>> > >
>> > > While it may be a bug, I am under the impression that UnixODBC is the
>> > > preferred way of connecting to to MySQL. Maybe you should look into
>> > > that and see if you come the the same conclusion about being
>> > > "preferred" and see if the "bug" still exists. That should help in a
>> > > couple different ways.
>> > >
>> > > Thanks,
>> > > Steve Totaro
>> > >
>> > > _______________________________________________
>> > > -- Bandwidth and Colocation Provided by http://www.api-digital.com --
>> > >
>> > > asterisk-users mailing list
>> > > To UNSUBSCRIBE or update options visit:
>> > > http://lists.digium.com/mailman/listinfo/asterisk-users
>> > >
>> > >
>> > >
>> >
>> > _______________________________________________
>> > -- Bandwidth and Colocation Provided by http://www.api-digital.com --
>> >
>> > asterisk-users mailing list
>> > To UNSUBSCRIBE or update options visit:
>> > http://lists.digium.com/mailman/listinfo/asterisk-users
>> >
>>
>
> _______________________________________________
> -- Bandwidth and Colocation Provided by http://www.api-digital.com --
>
> asterisk-users mailing list
> To UNSUBSCRIBE or update options visit:
> http://lists.digium.com/mailman/listinfo/asterisk-users
More information about the asterisk-users
mailing list