[Freeswitch-users] Nibblebill database question

Wed Feb 13 23:13:19 MSK 2013

It's probably not a very well known page. I created it when the odbc-dsn
change broke my own system on an upgrade (I too store user/password in
odbc.ini not the DSN). It lacks any earlier breaking changes, but seemed a
good idea to add now we have a stable branch.e

It's linked to on the Wiki front page and the download & install guide, but
if people are used to upgrading from the CLI they probably never even read
the Wiki each time they update. :o)

There's also http://wiki.freeswitch.org/wiki/Changelog_Summary, but the
Release Notes page is a nice place to highlight changes that might actually
break stuff. The odsn-dsn change is rather innocuously mentioned in the
changelog as merely 'core: Add Postgres core db support (r:0c1180d5)'.

-Steve

On 13 February 2013 19:57, David Villasmil
<david.villasmil.work at gmail.com>wrote:

> It worked! finally! That happend when one doesn't read the release notes :(
>
> it updated the balance, BUT I still got and error, balance was 100:
>
> 2013-02-13 19:55:09.903784 [DEBUG] mod_nibblebill.c:351 Doing update query
> [UPDATE sip_users SET balance=balance-0.022267 WHERE id='1']
> 2013-02-13 19:55:09.903784 [CRIT] mod_nibblebill.c:535 Failed to log to
> database!      <----- error?
> 2013-02-13 19:55:09.903784 [DEBUG] mod_nibblebill.c:378 Doing lookup query
> [SELECT balance AS nibble_balance FROM sip_users WHERE id='1']
> 2013-02-13 19:55:09.923320 [DEBUG] mod_nibblebill.c:386 Retrieved current
> balance for account 1 (balance = 99.977733)
>
> So it did the update fine, but stil got errors somehow...
>
>
> On Wed, Feb 13, 2013 at 8:49 PM, David Villasmil <
> david.villasmil.work at gmail.com> wrote:
>
>> Aha!
>>
>> That must it, i'll test and let you know...
>>
>> David
>>
>> ---
>>
>> David Villasmil
>>
>>
>> On Feb 13, 2013, at 20:35, Steven Ayre <steveayre at gmail.com> wrote:
>>
>> Is "viking" the actual DSN you're using? If so that format won't work any
>> longer since the generalized interface was implemented. name:user:pass
>> still works but plain name no longer works*.*
>>
>> See http://wiki.freeswitch.org/wiki/Release_Notes#odbc-dsn
>>
>> You're best off updating it to odbc://viking
>>
>> -Steve
>>
>>
>>
>>
>> On 13 February 2013 19:07, David Villasmil <
>> david.villasmil.work at gmail.com> wrote:
>>
>>> Yeah, everything works within isql:
>>>
>>> root at lamp ~# isql viking
>>> +---------------------------------------+
>>> | Connected!                            |
>>> |                                       |
>>> | sql-statement                         |
>>> | help [tablename]                      |
>>> | quit                                  |
>>> |                                       |
>>> +---------------------------------------+
>>> SQL> SELECT balance AS nibble_balance FROM sip_users WHERE id='1'
>>> +---------------+
>>> | nibble_balance|
>>> +---------------+
>>> | 100.000000    |
>>> +---------------+
>>> SQLRowCount returns 1
>>> 1 rows fetched
>>> SQL> UPDATE sip_users SET balance=balance-0.044817 WHERE id='1'
>>> SQLRowCount returns 1
>>> SQL> SELECT balance AS nibble_balance FROM sip_users WHERE id='1'
>>> +---------------+
>>> | nibble_balance|
>>> +---------------+
>>> | 99.955183     |
>>> +---------------+
>>> SQLRowCount returns 1
>>> 1 rows fetched
>>> SQL>
>>>
>>> and freeswitch core is also using odbc (although a differen dsn)
>>>
>>> David
>>>
>>>
>>> On Wed, Feb 13, 2013 at 7:07 PM, Steven Ayre <steveayre at gmail.com>wrote:
>>>
>>>> Do the SQL queries work ok within isql?
>>>>
>>>>
>>>>
>>>> On 13 February 2013 17:51, David Villasmil <
>>>> david.villasmil.work at gmail.com> wrote:
>>>>
>>>>> And i can connect from the cli:
>>>>>
>>>>> root at lamp ~# isql viking
>>>>> +---------------------------------------+
>>>>> | Connected!                            |
>>>>> |                                       |
>>>>> | sql-statement                         |
>>>>> | help [tablename]                      |
>>>>> | quit                                  |
>>>>> |                                       |
>>>>> +---------------------------------------+
>>>>> SQL>
>>>>>
>>>>> strange...
>>>>>
>>>>>
>>>>> On Wed, Feb 13, 2013 at 6:46 PM, Steven Ayre <steveayre at gmail.com>wrote:
>>>>>
>>>>>> http://jira.freeswitch.org/browse/FS-5100
>>>>>>
>>>>>>
>>>>>> On 13 February 2013 17:40, Steven Ayre <steveayre at gmail.com> wrote:
>>>>>>
>>>>>>> Specifically it's introduced by commit 5c176e8 which was resolving
>>>>>>> FS-4810, which is indeed adding support for the the generalised DSN
>>>>>>> interface.
>>>>>>>
>>>>>>>
>>>>>>> http://fisheye.freeswitch.org/changelog/freeswitch.git?showid=5c176e889d8cc2da7efb18a89782407ee5524a74
>>>>>>> http://jira.freeswitch.org/browse/FS-4810
>>>>>>>
>>>>>>> -Steve
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On 13 February 2013 17:37, Steven Ayre <steveayre at gmail.com> wrote:
>>>>>>>
>>>>>>>> Looks like this difference is brought in by the modifications to
>>>>>>>> support the generalised DSN interface. odbc-dsn would be consistent with
>>>>>>>> all other modules, so I guess that's why it's happened.
>>>>>>>>
>>>>>>>> But...
>>>>>>>> The HEAD of v1.2.stable understands db_dsn but not odbc-dsn.
>>>>>>>> The HEAD of master understands odbc-dsn but not db_dsn.
>>>>>>>>
>>>>>>>> This is a configuration breaking behaviour, which doesn't seem to
>>>>>>>> have been documented!
>>>>>>>>
>>>>>>>> At the very least it'd make sense for the master branch to
>>>>>>>> understand both odbc-dsn and db_dsn for backwards compatibility. I'll file
>>>>>>>> a Jira with a patch (it's a trivial fix) in a moment.
>>>>>>>>
>>>>>>>> -Steve
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> On 13 February 2013 17:33, Steven Ayre <steveayre at gmail.com> wrote:
>>>>>>>>
>>>>>>>>> How are you setting the DSN?
>>>>>>>>>
>>>>>>>>> The param name mentioned earlier is wrong. Reading the source the
>>>>>>>>> correct parameter is "odbc-dsn". Looks like the wiki documentation is
>>>>>>>>> incorrect.
>>>>>>>>>
>>>>>>>>> -Steve
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On 13 February 2013 15:33, David Villasmil <
>>>>>>>>> david.villasmil.work at gmail.com> wrote:
>>>>>>>>>
>>>>>>>>>> hello Avi,
>>>>>>>>>>
>>>>>>>>>> that's right, but nevermind that, that's the output when I was
>>>>>>>>>> trying something else:
>>>>>>>>>>
>>>>>>>>>> 2013-02-12 19:32:07.784156 [INFO] mod_nibblebill.c:498 Beginning
>>>>>>>>>> new billing on 58e6762b-c049-470e-84f7-519a815ab6f0
>>>>>>>>>> 2013-02-12 19:32:07.784156 [CRIT] mod_nibblebill.c:535 Failed to
>>>>>>>>>> log to database!
>>>>>>>>>> 2013-02-12 19:32:07.784156 [ERR] mod_nibblebill.c:380 Error
>>>>>>>>>> running this query: [SELECT balance AS nibble_balance FROM sip_users WHERE
>>>>>>>>>> id='1']
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> On Wed, Feb 13, 2013 at 12:58 PM, Avi Marcus <avi at avimarcus.net>wrote:
>>>>>>>>>>
>>>>>>>>>>> Your logs showed the queries run were:
>>>>>>>>>>>
>>>>>>>>>>> UPDATE sip_users SET balance=balance-0.018983 WHERE ='1'
>>>>>>>>>>> SELECT balance AS nibble_balance FROM sip_users WHERE ='1'
>>>>>>>>>>>
>>>>>>>>>>>  You just tried now.. "WHERE *id*='1' " -- they are not the
>>>>>>>>>>> same query...
>>>>>>>>>>>
>>>>>>>>>>> -Avi
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> On Wed, Feb 13, 2013 at 12:48 PM, David Villasmil <
>>>>>>>>>>> david.villasmil.work at gmail.com> wrote:
>>>>>>>>>>>
>>>>>>>>>>>> Hello Rupa,
>>>>>>>>>>>>
>>>>>>>>>>>> the query if ok, the user is there:
>>>>>>>>>>>>
>>>>>>>>>>>> mysql> select * from sip_users;
>>>>>>>>>>>>
>>>>>>>>>>>> +----+----------+----------+----------------+------------+--------------+--------------+
>>>>>>>>>>>> | id | username | password | rateplan       | balance    |
>>>>>>>>>>>> domain       | max_channels |
>>>>>>>>>>>>
>>>>>>>>>>>> +----+----------+----------+----------------+------------+--------------+--------------+
>>>>>>>>>>>> |  1 | 1002     | 1234     | retail_default | 100.000000 |
>>>>>>>>>>>> 192.168.1.44 |            1 |
>>>>>>>>>>>>
>>>>>>>>>>>> +----+----------+----------+----------------+------------+--------------+--------------+
>>>>>>>>>>>> 1 row in set (0.00 sec)
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> and the actual sql executed by nibblebill:
>>>>>>>>>>>>
>>>>>>>>>>>> mysql> SELECT balance AS nibble_balance FROM sip_users WHERE
>>>>>>>>>>>> id='1';
>>>>>>>>>>>> +----------------+
>>>>>>>>>>>> | nibble_balance |
>>>>>>>>>>>> +----------------+
>>>>>>>>>>>> |     100.000000 |
>>>>>>>>>>>> +----------------+
>>>>>>>>>>>> 1 row in set (0.00 sec)
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> so that's ok..
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> any othe ideas?
>>>>>>>>>>>>
>>>>>>>>>>>> Thanks a lot for your help!
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> David
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> On Wed, Feb 13, 2013 at 3:35 AM, Rupa Schomaker <rupa at rupa.com>wrote:
>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> On Tue, Feb 12, 2013 at 5:26 PM, David Villasmil <
>>>>>>>>>>>>> david.villasmil.work at gmail.com> wrote:
>>>>>>>>>>>>>
>>>>>>>>>>>>>> call goes out fine... no billing though:
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> 2013-02-12 19:21:49.844277 [DEBUG] mod_nibblebill.c:504 22
>>>>>>>>>>>>>> seconds passed since last bill time of 2013-02-12 19:21:27
>>>>>>>>>>>>>> 2013-02-12 19:21:49.844277 [DEBUG] mod_nibblebill.c:521
>>>>>>>>>>>>>> Billing $0.018983 to 1 (Call: d442dc6b-a3a7-423a-8450-598d17ca43b3 /
>>>>>>>>>>>>>> 0.000000 so far)
>>>>>>>>>>>>>> 2013-02-12 19:21:49.844277 [DEBUG] mod_nibblebill.c:351 Doing
>>>>>>>>>>>>>> update query
>>>>>>>>>>>>>> [UPDATE sip_users SET balance=balance-0.018983 WHERE ='1']
>>>>>>>>>>>>>> 2013-02-12 19:21:49.844277 [CRIT] mod_nibblebill.c:535 Failed
>>>>>>>>>>>>>> to log to database!
>>>>>>>>>>>>>> 2013-02-12 19:21:49.844277 [DEBUG] mod_nibblebill.c:378 Doing
>>>>>>>>>>>>>> lookup query
>>>>>>>>>>>>>> [SELECT balance AS nibble_balance FROM sip_users WHERE ='1']
>>>>>>>>>>>>>> 2013-02-12 19:21:49.844277 [ERR] mod_nibblebill.c:380 Error
>>>>>>>>>>>>>> running this query: [SELECT balance AS nibble_balance FROM sip_users WHERE
>>>>>>>>>>>>>> ='1']
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> the select go fine if executed "by hand"
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> None of those SQL statements are valid.  (hint: WHERE = '1' is
>>>>>>>>>>>>> no good).    Been a while since I hacked/worked with nibblebill, but I'm
>>>>>>>>>>>>> pretty sure the 'id' field isn't not being filled in the sql for some
>>>>>>>>>>>>> reason.
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> --
>>>>>>>>>>>>> -Rupa
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> _________________________________________________________________________
>>>>>>>>>>>>> Professional FreeSWITCH Consulting Services:
>>>>>>>>>>>>> consulting at freeswitch.org
>>>>>>>>>>>>> http://www.freeswitchsolutions.com
>>>>>>>>>>>>>
>>>>>>>>>>>>> 
>>>>>>>>>>>>> 
>>>>>>>>>>>>>
>>>>>>>>>>>>> Official FreeSWITCH Sites
>>>>>>>>>>>>> http://www.freeswitch.org
>>>>>>>>>>>>> http://wiki.freeswitch.org
>>>>>>>>>>>>> http://www.cluecon.com
>>>>>>>>>>>>>
>>>>>>>>>>>>> FreeSWITCH-users mailing list
>>>>>>>>>>>>> FreeSWITCH-users at lists.freeswitch.org
>>>>>>>>>>>>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>>>>>>>>>>>> UNSUBSCRIBE:
>>>>>>>>>>>>> http://lists.freeswitch.org/mailman/options/freeswitch-users
>>>>>>>>>>>>> http://www.freeswitch.org
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> _________________________________________________________________________
>>>>>>>>>>>> Professional FreeSWITCH Consulting Services:
>>>>>>>>>>>> consulting at freeswitch.org
>>>>>>>>>>>> http://www.freeswitchsolutions.com
>>>>>>>>>>>>
>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>>
>>>>>>>>>>>> Official FreeSWITCH Sites
>>>>>>>>>>>> http://www.freeswitch.org
>>>>>>>>>>>> http://wiki.freeswitch.org
>>>>>>>>>>>> http://www.cluecon.com
>>>>>>>>>>>>
>>>>>>>>>>>> FreeSWITCH-users mailing list
>>>>>>>>>>>> FreeSWITCH-users at lists.freeswitch.org
>>>>>>>>>>>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>>>>>>>>>>> UNSUBSCRIBE:
>>>>>>>>>>>> http://lists.freeswitch.org/mailman/options/freeswitch-users
>>>>>>>>>>>> http://www.freeswitch.org
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> _________________________________________________________________________
>>>>>>>>>>> Professional FreeSWITCH Consulting Services:
>>>>>>>>>>> consulting at freeswitch.org
>>>>>>>>>>> http://www.freeswitchsolutions.com
>>>>>>>>>>>
>>>>>>>>>>> 
>>>>>>>>>>> 
>>>>>>>>>>>
>>>>>>>>>>> Official FreeSWITCH Sites
>>>>>>>>>>> http://www.freeswitch.org
>>>>>>>>>>> http://wiki.freeswitch.org
>>>>>>>>>>> http://www.cluecon.com
>>>>>>>>>>>
>>>>>>>>>>> FreeSWITCH-users mailing list
>>>>>>>>>>> FreeSWITCH-users at lists.freeswitch.org
>>>>>>>>>>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>>>>>>>>>> UNSUBSCRIBE:
>>>>>>>>>>> http://lists.freeswitch.org/mailman/options/freeswitch-users
>>>>>>>>>>> http://www.freeswitch.org
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> _________________________________________________________________________
>>>>>>>>>> Professional FreeSWITCH Consulting Services:
>>>>>>>>>> consulting at freeswitch.org
>>>>>>>>>> http://www.freeswitchsolutions.com
>>>>>>>>>>
>>>>>>>>>> 
>>>>>>>>>> 
>>>>>>>>>>
>>>>>>>>>> Official FreeSWITCH Sites
>>>>>>>>>> http://www.freeswitch.org
>>>>>>>>>> http://wiki.freeswitch.org
>>>>>>>>>> http://www.cluecon.com
>>>>>>>>>>
>>>>>>>>>> FreeSWITCH-users mailing list
>>>>>>>>>> FreeSWITCH-users at lists.freeswitch.org
>>>>>>>>>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>>>>>>>>> UNSUBSCRIBE:
>>>>>>>>>> http://lists.freeswitch.org/mailman/options/freeswitch-users
>>>>>>>>>> http://www.freeswitch.org
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> _________________________________________________________________________
>>>>>> Professional FreeSWITCH Consulting Services:
>>>>>> consulting at freeswitch.org
>>>>>> http://www.freeswitchsolutions.com
>>>>>>
>>>>>> 
>>>>>> 
>>>>>>
>>>>>> Official FreeSWITCH Sites
>>>>>> http://www.freeswitch.org
>>>>>> http://wiki.freeswitch.org
>>>>>> http://www.cluecon.com
>>>>>>
>>>>>> FreeSWITCH-users mailing list
>>>>>> FreeSWITCH-users at lists.freeswitch.org
>>>>>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>>>>> UNSUBSCRIBE:
>>>>>> http://lists.freeswitch.org/mailman/options/freeswitch-users
>>>>>> http://www.freeswitch.org
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> _________________________________________________________________________
>>>>> Professional FreeSWITCH Consulting Services:
>>>>> consulting at freeswitch.org
>>>>> http://www.freeswitchsolutions.com
>>>>>
>>>>> 
>>>>> 
>>>>>
>>>>> Official FreeSWITCH Sites
>>>>> http://www.freeswitch.org
>>>>> http://wiki.freeswitch.org
>>>>> http://www.cluecon.com
>>>>>
>>>>> FreeSWITCH-users mailing list
>>>>> FreeSWITCH-users at lists.freeswitch.org
>>>>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>>>> UNSUBSCRIBE:
>>>>> http://lists.freeswitch.org/mailman/options/freeswitch-users
>>>>> http://www.freeswitch.org
>>>>>
>>>>>
>>>>
>>>>
>>>> _________________________________________________________________________
>>>> Professional FreeSWITCH Consulting Services:
>>>> consulting at freeswitch.org
>>>> http://www.freeswitchsolutions.com
>>>>
>>>> 
>>>> 
>>>>
>>>> Official FreeSWITCH Sites
>>>> http://www.freeswitch.org
>>>> http://wiki.freeswitch.org
>>>> http://www.cluecon.com
>>>>
>>>> FreeSWITCH-users mailing list
>>>> FreeSWITCH-users at lists.freeswitch.org
>>>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>>> UNSUBSCRIBE:
>>>> http://lists.freeswitch.org/mailman/options/freeswitch-users
>>>> http://www.freeswitch.org
>>>>
>>>>
>>>
>>> _________________________________________________________________________
>>> Professional FreeSWITCH Consulting Services:
>>> consulting at freeswitch.org
>>> http://www.freeswitchsolutions.com
>>>
>>> 
>>> 
>>>
>>> Official FreeSWITCH Sites
>>> http://www.freeswitch.org
>>> http://wiki.freeswitch.org
>>> http://www.cluecon.com
>>>
>>> FreeSWITCH-users mailing list
>>> FreeSWITCH-users at lists.freeswitch.org
>>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>>> http://www.freeswitch.org
>>>
>>>
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org
>> http://www.freeswitchsolutions.com
>>
>> 
>> 
>>
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://wiki.freeswitch.org
>> http://www.cluecon.com
>>
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at lists.freeswitch.org
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> http://www.freeswitch.org
>>
>>
>
> _________________________________________________________________________
> Professional FreeSWITCH Consulting Services:
> consulting at freeswitch.org
> http://www.freeswitchsolutions.com
>
> 
> 
>
> Official FreeSWITCH Sites
> http://www.freeswitch.org
> http://wiki.freeswitch.org
> http://www.cluecon.com
>
> FreeSWITCH-users mailing list
> FreeSWITCH-users at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> http://www.freeswitch.org
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20130213/1e8ea610/attachment-0001.html 