Sqlbox shouldn't remove any entries from send_sms in case of it doesn't get an ack from bearerbox. So if shutting down bearerbox first means you lose messages, file a bug.
== Rene -----Original Message----- From: users-boun...@kannel.org [mailto:users-boun...@kannel.org] On Behalf Of spameden Sent: zondag 17 februari 2013 16:13 To: Rinor Hoxha Cc: users@kannel.org Subject: Re: Issue with bearerbox 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 >> >> >>> >> >> >>> >> >> >> >> >> >> >> >> >> >> >> > >> > >> > > >
