[Freeswitch-users] Nibblebill database question

Wed Feb 13 22:57:46 MSK 2013

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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20130213/ee3b324f/attachment-0001.html 