VoIP Mailing List Archives :: View topic - [asterisk-users] Help Please - Asterisk MYSQL interface seems to be eating data

Sponsor: VoiceMeUp - Corporate & Wholesale VoIP Services

VoIP Mailing List Archives
Mailing list archives for the VoIP community

[asterisk-users] Help Please - Asterisk MYSQL interface seems to be eating data

Goto page 1, 2 Next

VoIP Mailing List Archives Forum Index -> Asterisk Users

View previous topic :: View next topic

Author

Message

stotaro at totarotechn...
Guest

Posted: Mon May 05, 2008 6:58 am Post subject: [asterisk-users] Help Please - Asterisk MYSQL interface seem

On Mon, May 5, 2008 at 4:21 AM, Al Baker <bwentdg at pipeline.com> wrote:

Quote:

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 Smile

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, Cool

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, Cool

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

bwentdg at pipeline.com
Guest

Posted: Mon May 05, 2008 8:29 am Post subject: [asterisk-users] Help Please - Asterisk MYSQL interface seem

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" Smile

Steve Totaro wrote:

Quote:

On Mon, May 5, 2008 at 4:21 AM, Al Baker <bwentdg at pipeline.com> wrote:

Quote:

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

stotaro at totarotechn...
Guest

Posted: Mon May 05, 2008 9:06 am Post subject: [asterisk-users] Help Please - Asterisk MYSQL interface seem

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:

Quote:

Steve Totaro wrote:

Quote:

On Mon, May 5, 2008 at 4:21 AM, Al Baker <bwentdg at pipeline.com> wrote:

Quote:

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

_______________________________________________
-- 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

stotaro at totarotechn...
Guest

Posted: Mon May 05, 2008 9:09 am Post subject: [asterisk-users] Help Please - Asterisk MYSQL interface seem

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."

Thanks,
Steve Totaro

On Mon, May 5, 2008 at 10:06 AM, Steve Totaro
<stotaro at totarotechnologies.com> wrote:

Quote:

Steve Totaro wrote:

Quote:

On Mon, May 5, 2008 at 4:21 AM, Al Baker <bwentdg at pipeline.com> wrote:

Quote:

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

oej at edvina.net
Guest

Posted: Mon May 05, 2008 9:45 am Post subject: [asterisk-users] Help Please - Asterisk MYSQL interface seem

Another issue that we need to fix with the MYSQL driver is that we're lacking a connection pool. Everything seems to be handled over one connection to Mysql, which causes issues. Any MySQL developers out there that can help us fix this? We need someone that has been developing towards the Mysql api. And please do not always refer to "Digium developers" when you have problems in Asterisk. There are developers that are not employees of Digium... Cheers, /Olle

raanders at acm.org
Guest

Posted: Mon May 05, 2008 9:57 am Post subject: [asterisk-users] Help Please - Asterisk MYSQL interface seem

Steve Totaro wrote:

Quote:

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
--

Quote:

Thanks,
Steve Totaro

On Mon, May 5, 2008 at 10:06 AM, Steve Totaro
<stotaro at totarotechnologies.com> wrote:

Quote:

Steve Totaro wrote:

Quote:

On Mon, May 5, 2008 at 4:21 AM, Al Baker <bwentdg at pipeline.com> wrote:

Quote:

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

bwentdg at pipeline.com
Guest

Posted: Mon May 05, 2008 10:16 am Post subject: [asterisk-users] Help Please - Asterisk MYSQL interface seem

I must be overlooking it, I pulled up the electronic version and
searched for and read every instance where ODBC was mentioned and I
could not find a single place where it said
ODBC was to be the only or even the best method. If so I would never
ever have gone down this road Sad

Quote "And according to the O'Reilly book ODBC is the way to go."

Roderick A. Anderson wrote:

Quote:

Steve Totaro wrote:

Quote:

tilghman at mail.jeffa...
Guest

Posted: Mon May 05, 2008 10:48 am Post subject: [asterisk-users] Help Please - Asterisk MYSQL interface seem

On Monday 05 May 2008 10:16, Al Baker wrote:

Quote:

While there are some things that you can do with the MYSQL app that you
can't do with func_odbc, most of what people want to do (single row queries,
updates, and inserts) can be done with func_odbc. Additionally, it's well
supported, the author (me) is still around, and we're still considering
enhancements. The MYSQL app is somewhat less certain. I don't know if
the author is still around, it is largely in maintenance mode, and, due to a
matter of licensing, the app is not in the Asterisk core distribution.

ODBC is not going to be the only supported method of accessing a database;
some native drivers exist, and they will continue to exist. The reason why
ODBC is so important to us is that it allows us to interface with many varied
databases out there for which the native API is not immediately available.
Additionally, it significantly lightens our maintenance workload not to have
to maintain drivers for some databases which our users may never use. And
most of our database integration has been for the specific purpose of
realtime. Our func_odbc module is about the most generic of database
interfaces, and its usage is completely user-defined, unlike realtime or the
other database interface (voicemail storage).

--
Tilghman

tilghman at mail.jeffa...
Guest

Posted: Mon May 05, 2008 10:51 am Post subject: [asterisk-users] Help Please - Asterisk MYSQL interface seem

On Monday 05 May 2008 09:45, Johansson Olle E wrote:

Quote:

Another issue that we need to fix with the MYSQL driver is that we're
lacking a connection pool. Everything seems to be handled over one
connection to Mysql, which causes issues.

That's not true. The MYSQL app generally uses multiple connections, one
for each channel. The only way one might use only a single connection is
by using a global variable to store a single connection id, but that method
is not documented anywhere, AFAIK.

Quote:

Any MySQL developers out there that can help us fix this? We need
someone that has been developing towards the Mysql api.

And please do not always refer to "Digium developers" when you have
problems in Asterisk. There are developers that are not employees of
Digium...

There used to be more outside developers, but Digium is a great place to
work... Wink

--
Tilghman

oej at edvina.net
Guest

Posted: Mon May 05, 2008 11:24 am Post subject: [asterisk-users] Help Please - Asterisk MYSQL interface seem

5 maj 2008 kl. 17.51 skrev Tilghman Lesher:

Quote:

On Monday 05 May 2008 09:45, Johansson Olle E wrote:

Quote:

Another issue that we need to fix with the MYSQL driver is that we're
lacking a connection pool. Everything seems to be handled over one
connection to Mysql, which causes issues.

That's not true. The MYSQL app generally uses multiple connections,
one
for each channel. The only way one might use only a single
connection is
by using a global variable to store a single connection id, but that
method
is not documented anywhere, AFAIK.

You talk about the Mysql APP, but is this the case with the Realtime
driver as well?

Quote:

There used to be more outside developers, but Digium is a great
place to
work... Wink

Hmm. Haven't found any Digium Stockholm office to discuss with Wink

tilghman at mail.jeffa...
Guest

Posted: Mon May 05, 2008 12:58 pm Post subject: [asterisk-users] Help Please - Asterisk MYSQL interface seem

On Monday 05 May 2008 11:24, Johansson Olle E wrote:

Quote:

5 maj 2008 kl. 17.51 skrev Tilghman Lesher:

Quote:

On Monday 05 May 2008 09:45, Johansson Olle E wrote:

Quote:

Another issue that we need to fix with the MYSQL driver is that we're
lacking a connection pool. Everything seems to be handled over one
connection to Mysql, which causes issues.

That's not true. The MYSQL app generally uses multiple connections,
one
for each channel. The only way one might use only a single
connection is
by using a global variable to store a single connection id, but that
method
is not documented anywhere, AFAIK.

You talk about the Mysql APP, but is this the case with the Realtime
driver as well?

No, the native Realtime driver uses a single connection. The ODBC Realtime
driver generally uses a single connection but can be configured to use a
separate connection for each query.

Quote:

There used to be more outside developers, but Digium is a great
place to work... Wink

Hmm. Haven't found any Digium Stockholm office to discuss with Wink

That hasn't stopped any of the Canadian employees. Smile

--
Tilghman

aklists at mixdown.ca
Guest

Posted: Mon May 05, 2008 1:25 pm Post subject: [asterisk-users] Help Please - Asterisk MYSQL interface seem

On May 5, 2008 01:58:42 pm Tilghman Lesher wrote:

Quote:

Hmm. Haven't found any Digium Stockholm office to discuss with Wink

That hasn't stopped any of the Canadian employees. Smile

That's because nothing stops Canadians, short of Hockey Night in Canada Smile

-A.

oej at edvina.net
Guest

Posted: Tue May 06, 2008 2:16 am Post subject: [asterisk-users] Help Please - Asterisk MYSQL interface seem

5 maj 2008 kl. 19.58 skrev Tilghman Lesher:

Quote:

On Monday 05 May 2008 11:24, Johansson Olle E wrote:

Quote:

5 maj 2008 kl. 17.51 skrev Tilghman Lesher:

Quote:

On Monday 05 May 2008 09:45, Johansson Olle E wrote:

Quote:

Another issue that we need to fix with the MYSQL driver is that
we're
lacking a connection pool. Everything seems to be handled over one
connection to Mysql, which causes issues.

That's not true. The MYSQL app generally uses multiple connections,
one
for each channel. The only way one might use only a single
connection is
by using a global variable to store a single connection id, but that
method
is not documented anywhere, AFAIK.

You talk about the Mysql APP, but is this the case with the Realtime
driver as well?

No, the native Realtime driver uses a single connection. The ODBC
Realtime
driver generally uses a single connection but can be configured to
use a
separate connection for each query.

So, we're back to where we started. A developer that can help us with
a connection
pool or a separate connection for each query would be a Nice Thing (TM).

/O

tilghman at mail.jeffa...
Guest

Posted: Tue May 06, 2008 8:25 am Post subject: [asterisk-users] Help Please - Asterisk MYSQL interface seem

On Tuesday 06 May 2008 02:16:47 Johansson Olle E wrote:

Quote:

5 maj 2008 kl. 19.58 skrev Tilghman Lesher:

Quote:

On Monday 05 May 2008 11:24, Johansson Olle E wrote:

Quote:

5 maj 2008 kl. 17.51 skrev Tilghman Lesher:

Quote:

On Monday 05 May 2008 09:45, Johansson Olle E wrote:

Quote:

Another issue that we need to fix with the MYSQL driver is that
we're
lacking a connection pool. Everything seems to be handled over one
connection to Mysql, which causes issues.

That's not true. The MYSQL app generally uses multiple connections,
one
for each channel. The only way one might use only a single
connection is
by using a global variable to store a single connection id, but that
method
is not documented anywhere, AFAIK.

You talk about the Mysql APP, but is this the case with the Realtime
driver as well?

No, the native Realtime driver uses a single connection. The ODBC
Realtime
driver generally uses a single connection but can be configured to
use a
separate connection for each query.

So, we're back to where we started. A developer that can help us with
a connection
pool or a separate connection for each query would be a Nice Thing (TM).

What issues are you specifically seeing that merit using multiple
connections?

--
Tilghman

anthonyf at rockynet.com
Guest

Posted: Tue May 06, 2008 10:42 am Post subject: [asterisk-users] Help Please - Asterisk MYSQL interface seem

Tilghman Lesher wrote:

Quote:

On Tuesday 06 May 2008 02:16:47 Johansson Olle E wrote:

Quote:

5 maj 2008 kl. 19.58 skrev Tilghman Lesher:

Quote:

On Monday 05 May 2008 11:24, Johansson Olle E wrote:

Quote:

5 maj 2008 kl. 17.51 skrev Tilghman Lesher:

Quote:

On Monday 05 May 2008 09:45, Johansson Olle E wrote:

Quote:

Another issue that we need to fix with the MYSQL driver is that
we're
lacking a connection pool. Everything seems to be handled over one
connection to Mysql, which causes issues.

That's not true. The MYSQL app generally uses multiple connections,
one
for each channel. The only way one might use only a single
connection is
by using a global variable to store a single connection id, but that
method
is not documented anywhere, AFAIK.

You talk about the Mysql APP, but is this the case with the Realtime
driver as well?

No, the native Realtime driver uses a single connection. The ODBC
Realtime
driver generally uses a single connection but can be configured to
use a
separate connection for each query.

So, we're back to where we started. A developer that can help us with
a connection
pool or a separate connection for each query would be a Nice Thing (TM).

What issues are you specifically seeing that merit using multiple
connections?

I can specify an issue that would merit multiple connections, if the
link to your db goes away Asterisk likes to freeze writing CDRs.
I have a few remote * servers that this happens to. My solution so far
has been to record CDR's to a local DB and then have a
perl script that attempts to move them over to my transaction DB. I
would suggest this solution to anyone who depends on their CDR records.

--
Thank you and have any kind of day you want,

Anthony Francis
Rockynet VOIP

Display posts from previous:

	VoIP Mailing List Archives Forum Index -> Asterisk Users	All times are GMT - 5 Hours Goto page 1, 2 Next
Page 1 of 2

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum