Hi spameden,
I was thinking all the scenarios I used and/or helped in different similar
situations related to DLR and realized that the list may be long and may
not help you for your actual situation. In order to be more realistic and
more adaptive to your actual scenario, can you please explain your service
flow and the role of DLR there. I mean is it a kind of VAS platform, a bulk
sms platform, dlr need to be passed to an opensmpp client, or just need to
update the MT status on db, what is the maximum validity time of an MT, are
there additional actions to be performed when DLR arrives (like in case of
MT charging scenarios for a VAS etc...), what is the maximum number of DLR
waiting for a final status on bearerbox dlr table that you have usually,
etc... I hope you get my point.
That said, I've seen good performance in using sqlite3 for dlr storage, and
I use it frequently.
Br, Rinor
On Sat, Feb 16, 2013 at 6:52 PM, spameden <spame...@gmail.com> wrote:
> 2013/2/16 Rinor Hoxha <rinorho...@gmail.com>:
> > I'm not able to provide the patch right now since it is not compatible
> with
> > the last rev and is has also some other minor changes not related to
> > matching, but it is really simple.
> > I will paste a kind of diff here for now.
> >
> > sqlbox_mysql.c
> >
> > static Octstr *sqlbox_logtable;
> > static Octstr *sqlbox_insert_table;
> > +static int sqlbox_logtable_split = 0;
> > ......................................................
> > if (sqlbox_insert_table == NULL) {
> > panic(0, "No 'sql-insert-table' not configured.");
> > }
> > + cfg_get_bool(&sqlbox_logtable_split, grp,
> > octstr_imm("sql-log-table-split"));
> > +
> > + /* create send_sms && sent_sms MO MT DLR tables if they do not
> exist */
> > + if (sqlbox_logtable_split) {
> > + sql = octstr_format(SQLBOX_MYSQL_CREATE_LOG_TABLE,
> > octstr_cat(sqlbox_logtable, octstr_imm("MO")));
> > + sql_update(sql);
> > + octstr_destroy(sql);
> > + sql = octstr_format(SQLBOX_MYSQL_CREATE_LOG_TABLE,
> > octstr_cat(sqlbox_logtable, octstr_imm("MT")));
> > + sql_update(sql);
> > + octstr_destroy(sql);
> > + sql = octstr_format(SQLBOX_MYSQL_CREATE_LOG_TABLE,
> > octstr_cat(sqlbox_logtable, octstr_imm("DLR")));
> > + sql_update(sql);
> > + octstr_destroy(sql);
> > + }
> > + else {
> > + sql = octstr_format(SQLBOX_MYSQL_CREATE_LOG_TABLE,
> > sqlbox_logtable);
> > + sql_update(sql);
> > + octstr_destroy(sql);
> > + }
> >
> > - /* create send_sms && sent_sms tables if they do not exist */
> > - sql = octstr_format(SQLBOX_MYSQL_CREATE_LOG_TABLE, sqlbox_logtable);
> > - sql_update(sql);
> > - octstr_destroy(sql);
> > sql = octstr_format(SQLBOX_MYSQL_CREATE_INSERT_TABLE,
> > sqlbox_insert_table);
> > sql_update(sql);
> > octstr_destroy(sql);
> >
> ...........................................................................
> > Octstr *sql;
> > Octstr *stuffer[30];
> > int stuffcount = 0;
> >
> > - sql = octstr_format(SQLBOX_MYSQL_INSERT_QUERY, sqlbox_logtable,
> > st_str(momt), st_str(msg->sms.sender),
> > + if (sqlbox_logtable_split) {
> > + sqlbox_logtable_tmp = octstr_cat(sqlbox_logtable, momt);
> > + }
> > + else {
> > + sqlbox_logtable_tmp = octstr_duplicate(sqlbox_logtable);
> > + }
> > + sql = octstr_format(SQLBOX_MYSQL_INSERT_QUERY, sqlbox_logtable_tmp,
> > st_str(momt), st_str(msg->sms.sender),
> >
> >
> .....................................................................................
> > sql_update(sql);
> > while (stuffcount > 0) {
> > octstr_destroy(stuffer[--stuffcount]);
> > }
> > octstr_destroy(sql);
> > + octstr_destroy(sqlbox_logtable_tmp);
> >
> > ...................................................................
> >
> > hope you get it. I promise, if you are interested on it, i will provide a
> > proper applicable diff for the current version.
> > There are different ways we handled DLR when mysql went down (although it
> > should NOT happen normally).
>
> it usually happens if you try to upgrade MySQL for example or stop it
> while kannel having batch to send out.
>
> would be nice to hear how did you solved it
>
> thanks for the patch! currently i'm just deleting the DLR's (updating
> existing MT records with latest status from DLR and inserting into
> meta_data field DLR msgdata field) to keep tables clean, also running
> via cronjob a script to archive old MT records.
>
> > Solutions depend on case usage. and if opensmppbox is involved or not.
> > we can discuss it later since I'm in middle of an issue right now and
> have
> > to leave, sorry.
> >
> > Br, Rinor
> >
> >
> > On Fri, Feb 15, 2013 at 12:28 AM, spameden <spame...@gmail.com> wrote:
> >>
> >> Hey Rinor.
> >>
> >> Your idea about splitting MO, MT, DLR in separate tables is just great!
> >>
> >> How do you do matching against MT's btw? Do you invoke everytime
> >> script or just update table via unique dlr_url id in the MT table?
> >>
> >> Would love to see your patch against SQLBox.
> >>
> >> I'm also wondering if you solved the issue of DLR missing when DB goes
> >> down.
> >>
> >> Thanks.
> >>
> >> 2013/2/14 Rinor Hoxha <rinorho...@gmail.com>:
> >> > Sure Willy,
> >> > I'll post them in one hour, the time to remove the sensitive
> >> > information,
> >> > but I can tell you in advance there is nothing fancy on them.
> >> >
> >> > One scenario is the following:
> >> > 4xSMSbox -> 1xSQLbox -> 1xBEARERbox => 10xConnections to 4xMNOs.
> >> > (7xSMPPxTRANSCEIVER + 3 custom http connections to interface some smsc
> >> > proxy
> >> > that is not included on default kannel smscs)
> >> >
> >> > 1 SMSbox handles services for 1 MNO.
> >> > Don't remember why I did that, probably because of the retry delay
> queue
> >> > issue on smsbox, but not really sure.
> >> >
> >> > Each SMSbox has one specific config file that includes a common file,
> >> > for
> >> > all SMSboxes, that contains the users and the services.
> >> >
> >> > Kannel rev.5014
> >> > Kannel code has a small modification to activate smpp
> >> > relative_time_format
> >> > for validity (since some of MNOs refused to go with
> >> > absolute_time_format). I
> >> > agree with the list, in some previous debates, that relative time
> should
> >> > not
> >> > be used, but when you have to, kannel is there for you :)
> >> > relative-time-format = 1 is the customized directive for this.
> >> >
> >> > Sqlbox rev.49
> >> > Sqlbox has a small modification in mysql to insert MO,MT,DLR in
> separate
> >> > TABLES - that could be done also with triggers, but on high load I
> >> > didn't
> >> > wanted triggers so thought it was better to put it inside the code.
> >> > sql-log-table-split = 1 is the customized directive for this.
> >> >
> >> > This is in one production system, however the tests have been done
> also
> >> > with
> >> > unmodified versions of those revisions. (keep in mind that the small
> >> > changes
> >> > made to the code have nothing to do with the "mentioned issue"). Test
> >> > were
> >> > done both on debian and windows.
> >> >
> >> > I'll provide more info later if needed/interested.
> >> >
> >> > Br, Rinor
> >> >
> >> >
> >> > On Thu, Feb 14, 2013 at 2:11 AM, Willy Mularto <sangpr...@gmail.com>
> >> > wrote:
> >> >>
> >> >> May we know your Kannel and SQLBox version also please share your
> >> >> Kannel
> >> >> scenario configuration, including SQLBox position
> >> >> (Bearerbox-SQLBox-SMSBox
> >> >> or Bearbox-SMSBox-SQLBox or other?)
> >> >>
> >> >>
> >> >>
> >> >> Willy Mularto
> >> >> sangpr...@gmail.com
> >> >>
> >> >> On Feb 14, 2013, at 12:20 AM, Rinor Hoxha <rinorho...@gmail.com>
> wrote:
> >> >>
> >> >> I was concerned in the first time I read about this "issue",
> >> >> since I'm using sqlbox in different scenarios, with different load
> >> >> (1-20+
> >> >> sms/sec) with at least 10 smsc connections (smpp and http), with 4
> >> >> smsboxes,
> >> >> with delivery tracking, with/without sms services, with/without
> >> >> opensmppbox
> >> >> and the only times that sqlbox "failed" was because sql server
> reaching
> >> >> max connections (on very high load and bad programmed services) or
> >> >> services
> >> >> script issues or the MNO never sent dlr report or other issues not
> >> >> related
> >> >> to sqlbox itself.
> >> >> I tried myself on lab to make sqlbox "loose" sms, but it never
> >> >> happened. I
> >> >> went further and compiled a cygwin version on windows and it never
> >> >> happened.
> >> >> The last test was done on windows with rev.5014 with variable rate
> 6-30
> >> >> sms
> >> >> with approx 300.000 sms with dlr=3. And everything was there. No
> matter
> >> >> if
> >> >> one or more than one smsboxes, routing works fine.
> >> >> Yes, it may be some room for improvement on sqlbox (as with any
> >> >> software),
> >> >> but not sure if it has a "bug" causing missing sms.
> >> >> I may be wrong, but that my 2 cents, since I see this "issue" coming
> up
> >> >> from time to time. If others think that there is really a bug, lets
> >> >> help
> >> >> each other and the programmer to fix it.
> >> >>
> >> >> Br, Rinor
> >> >>
> >> >>
> >> >>
> >> >> On Tue, Feb 12, 2013 at 8:14 PM, Rene Kluwen <rene.klu...@chimit.nl>
> >> >> wrote:
> >> >>>
> >> >>> Please indicate if you are using sqlbox or not.
> >> >>>
> >> >>> I’ve tried to debug this problem in the past, but I could not
> >> >>> reproduce
> >> >>> this problem.
> >> >>>
> >> >>>
> >> >>>
> >> >>>
> >> >>>
> >> >>> From: Willy Mularto [mailto:sangpr...@gmail.com]
> >> >>> Sent: donderdag 7 februari 2013 14:05
> >> >>> To: michael osakede
> >> >>> Cc: Rene Kluwen; kannel users@kannel.org
> >> >>> Subject: Re: Issue with bearerbox
> >> >>>
> >> >>>
> >> >>>
> >> >>>
> >> >>> Do you use current version with SQLBox configured to service MO? If
> >> >>> yes
> >> >>> then it could be thee old bug still there.
> >> >>>
> >> >>> SlyDroid
> >> >>>
> >> >>> On Feb 7, 2013 7:44 PM, "michael osakede" <ire...@yahoo.com> wrote:
> >> >>>
> >> >>> Hi,
> >> >>>
> >> >>>
> >> >>>
> >> >>> I have setup an sms service
> >> >>>
> >> >>>
> >> >>>
> >> >>> group = sms-service
> >> >>>
> >> >>> keyword = default
> >> >>>
> >> >>> get-url = "http://localhost/insms/insms.php?sender=%p&text=%b&to=%P
> "
> >> >>>
> >> >>> accept-x-kannel-headers = true
> >> >>>
> >> >>> max-messages = 3
> >> >>>
> >> >>> concatenation = true
> >> >>>
> >> >>> catch-all = true
> >> >>>
> >> >>> accepted-smsc = "xxx;xxx1;xxx2;xxx3"
> >> >>>
> >> >>> omit-empty = true
> >> >>>
> >> >>>
> >> >>>
> >> >>> and corresponding SMPP connction with smsc-ids.
> >> >>>
> >> >>>
> >> >>>
> >> >>> I noticed that only about 40% of messages are routed to the sms
> >> >>> service,
> >> >>> any idea why this is a problem? and is there anyway to check and see
> >> >>> what
> >> >>> happened?
> >> >>>
> >> >>>
> >> >>>
> >> >>>
> >> >>>
> >> >>> Michael
> >> >>>
> >> >>>
> >> >>
> >> >>
> >> >>
> >> >
> >
> >
>
