Hi spameden,
Both your situations are "easily" manageable.
I will split things based on components.
1) SQLbox - In order to control SQLbox you have at least 2 options
a) Don't send your data directly to sql-insert-table but send them into
another table here called as bulk_table for reference. After calculating
approx your max throughput from the providers, fill sql-insert-table with
that amount of data each X seconds or minutes, changing the status of those
data as DONE to the bulk_table. This can be done with a daemon or cron or
whatever. This way you are in control of what data gets in
sql-insert-table, so when you want to stop, just stop feeding
sql-insert-table and wait for SQLbox to finish what has already in and stop
SQLbox safely.
b) Into sql-insert-table create a new field here refered as bulk_active for
reference with default value =1. Modify the SQLbox mysql query on source
code adding "WHERE bulk_active=1". So if you want to stop SQLbox from
getting data when you want to restart, just update bulk_active=0 and stop
SQLbox safely.
2) BEARERbox - DLR related.
This is a little bit more complicated and depends on your providers and the
way they treat DLR.
If the provider retries to deliver you the DLR in case your system is
disconnected (after reconnecting of course :)):
Before stopping BEARERbox (once you have handled SQLbox without stopping it
yet) you can wait for BEARERbox to finish sending the messages it has in
queue and set the status "Isolated" or "Shutdown".
In this case you are done sending for the moment and kannel should not
receive/ACK the DLR (I have to retest this one since not sure/ don't
remember).
At this point you can stop kannel, do whatever you have to do with mysql
and restart everything without loosing a single thing.
- However I've seen providers discarding DLR if they don't find you
connected ate the time of the delivery. (some allowed query_sm or alternate
command if not smpp, some not)
Also in a similar situation like yours, since you don't have MO (providers
return to you only DLR), long time ago we used to create a client that
would connect as reciever only to the same accounts as kannel did, so when
kannel goes down the DLR are redirected to this client connection wich
would queue the DLR and once MYSQL is up it performed the needed actions.
(a bit extreme but at that time was the only solution)
In extreme cases some created also some scripts to recover the DLR from log
files ( only for match failed DLR due to mysql down )
For those providers that allowed query_sm ( very few ) we used a smpp perl
lib to automate the querying of pending DLR.
I hope I gave you some useful ideas how to deal with your scenarios.
If not, well, sorry :)
BR, Rinor
On Sun, Feb 17, 2013 at 4:12 PM, spameden <spame...@gmail.com> wrote:
> My service is just a bulk SMS service to send out MTs, no OpenSMPPBox
> is involved at all.
>
> The flow is bearerbox<->sqlbox<->MySQL DB
>
> When i'm trying to stop kannel (or restart), first instance goes down
> is sqlbox. whilst this is happening bearerbox still servicing requests
> from remote SMSCs, and thus accepting DLRs, but bearerbox cant process
> them, because sqlbox is already down.
>
> If I stop bearerbox first that means sqlbox is still getting queue
> from DB, thus removing entries from DB but not sending any MTs.
>
> Other scenario is:
>
> if i try to upgrade database when there is an active queue going on ->
> sqlbox failing to connect to DB or bearerbox so its missing DLRs once
> again.
>
> 2013/2/17 Rinor Hoxha <rinorho...@gmail.com>:
> > 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
> >> >> >>>
> >> >> >>>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >
> >> >
> >> >
> >
> >
>
