You didn't use sqlbox. What you've used is basic kannel + dlr reports backend in MySQL.
As mentioned before dlr table is only used for temporary track of received DLRs. Once DLR is received corresponing record from dlr table will be deleted. So to store permanently DLRs as well as MT/MO messages in DB you need to use sqlbox... Check kannel.org site it's there. 2016-01-19 12:01 GMT+03:00 Denis <notabenaaba...@gmail.com>: > 18.01.2016 21:43, spameden пишет: > > You can use sqlbox to get reports in the MySQL DB. > > dlr table is only for temporary tracking of DLR reports. > > 2016-01-18 16:44 GMT+03:00 Achyut Raj <de...@bongolive.co.tz>: > >> Hi Deins, >> >> It is the normal behaviour of Kannel /SQLBOX. >> Actually the dlr table is used to hold the dlr for receiving the dlr's. >> Record on this table will be removed once dlr is received. For your >> convenience see the highlighted text below : >> >> DLR: received 33250, sent 0 >> DLR: inbound (0.00,0.01,0.13) msg/sec, outbound (0.00,0.00,0.00) msg/sec >> *DLR: 80412 queued*, using mysql storage >> >> >> To store the dlr status permanently you can >> 1) make create new table and use of dlr-mask and dlr-url parameters >> while sending message and develop the php script or the script of any >> language of your choice to receive and store dlr. >> Ref : <http://www.kannel.org/pipermail/users/2008-June/005035.html> >> http://www.kannel.org/pipermail/users/2008-June/005035.html >> >> 2) make use of some mysql triggers to store the dlr >> Ref : <http://www.blogalex.com/archives/322> >> http://www.blogalex.com/archives/322 >> >> >> On Mon, Jan 18, 2016 at 4:11 PM, Deins < <notabenaaba...@gmail.com> >> notabenaaba...@gmail.com> wrote: >> >>> Hi! >>> Sorry for my english. >>> I'm try to set up mySQL dlr storage. It works, but not the way I want >>> it. When delievery-sm comes to kannel it does two sql transactions. >>> >>> 1. It writes dlr record >>> DEBUG: sql: INSERT INTO dlr (smsc, ts, source, destination, service, >>> url, boxc, status) VALUES ('myprovider-smsc', '569CC8480000', 'MYNUM', >>> 'myservice', '', '1', '', '0') >>> 2. Then it delete record just written >>> DEBUG: sql: DELETE FROM dlr WHERE smsc='myprovider-smsc' AND >>> ts='569CC8480000' LIMIT 1 >>> >>> As a result I have empty dlr table. Is it normal behavior of kannel and >>> how to stop it, because I need to store delivery records? >>> >>> >> > Thank for your advices. I used mysql triggers and it's done. > But now I have another problem. I always have 0 in "status" field. Which > field in delivery_sm answers for message status? > > I turn off my phone and send message with parameter validity=5. Here is > log: > > /* Sending PDU */ > > 2016-01-19 02:45:22 [4029] [6] DEBUG: SMPP[my-smsc]: Sending PDU: > 2016-01-19 02:45:22 [4029] [6] DEBUG: SMPP PDU 0xb2a00888 dump: > 2016-01-19 02:45:22 [4029] [6] DEBUG: type_name: submit_sm > 2016-01-19 02:45:22 [4029] [6] DEBUG: command_id: 4 = 0x00000004 > 2016-01-19 02:45:22 [4029] [6] DEBUG: command_status: 0 = 0x00000000 > 2016-01-19 02:45:22 [4029] [6] DEBUG: sequence_number: 82 = 0x00000052 > 2016-01-19 02:45:22 [4029] [6] DEBUG: service_type: "" > 2016-01-19 02:45:22 [4029] [6] DEBUG: source_addr_ton: 5 = 0x00000005 > 2016-01-19 02:45:22 [4029] [6] DEBUG: source_addr_npi: 0 = 0x00000000 > 2016-01-19 02:45:22 [4029] [6] DEBUG: source_addr: "SOURCE" > 2016-01-19 02:45:22 [4029] [6] DEBUG: dest_addr_ton: 1 = 0x00000001 > 2016-01-19 02:45:22 [4029] [6] DEBUG: dest_addr_npi: 1 = 0x00000001 > 2016-01-19 02:45:22 [4029] [6] DEBUG: destination_addr: "7xxxxxxxxxx" > 2016-01-19 02:45:22 [4029] [6] DEBUG: esm_class: 3 = 0x00000003 > 2016-01-19 02:45:22 [4029] [6] DEBUG: protocol_id: 0 = 0x00000000 > 2016-01-19 02:45:22 [4029] [6] DEBUG: priority_flag: 0 = 0x00000000 > 2016-01-19 02:45:22 [4029] [6] DEBUG: schedule_delivery_time: NULL > 2016-01-19 02:45:22 [4029] [6] DEBUG: validity_period: "160119075022000+" > 2016-01-19 02:45:22 [4029] [6] DEBUG: registered_delivery: 1 = 0x00000001 > 2016-01-19 02:45:22 [4029] [6] DEBUG: replace_if_present_flag: 0 = > 0x00000000 > 2016-01-19 02:45:22 [4029] [6] DEBUG: data_coding: 0 = 0x00000000 > 2016-01-19 02:45:22 [4029] [6] DEBUG: sm_default_msg_id: 0 = 0x00000000 > 2016-01-19 02:45:22 [4029] [6] DEBUG: sm_length: 23 = 0x00000017 > 2016-01-19 02:45:22 [4029] [6] DEBUG: short_message: > 2016-01-19 02:45:22 [4029] [6] DEBUG: Octet string at 0xb2a011b0: > 2016-01-19 02:45:22 [4029] [6] DEBUG: len: 23 > 2016-01-19 02:45:22 [4029] [6] DEBUG: size: 24 > 2016-01-19 02:45:22 [4029] [6] DEBUG: immutable: 0 > 2016-01-19 02:45:22 [4029] [6] DEBUG: data: 54 65 73 74 20 6d 65 73 > 73 61 67 65 20 76 61 6c Test message val > 2016-01-19 02:45:22 [4029] [6] DEBUG: data: 69 64 69 74 79 20 > 35 idity 5 > 2016-01-19 02:45:22 [4029] [6] DEBUG: Octet string dump ends. > 2016-01-19 02:45:22 [4029] [6] DEBUG: SMPP PDU dump ends. > 2016-01-19 02:45:22 [4029] [6] DEBUG: SMPP[megafon-smsc]: Got PDU: > 2016-01-19 02:45:22 [4029] [6] DEBUG: SMPP PDU 0xb2a00888 dump: > 2016-01-19 02:45:22 [4029] [6] DEBUG: type_name: submit_sm_resp > 2016-01-19 02:45:22 [4029] [6] DEBUG: command_id: 2147483652 = 0x80000004 > 2016-01-19 02:45:22 [4029] [6] DEBUG: command_status: 0 = 0x00000000 > 2016-01-19 02:45:22 [4029] [6] DEBUG: sequence_number: 82 = 0x00000052 > 2016-01-19 02:45:22 [4029] [6] DEBUG: message_id: "569DE9CF0000" > 2016-01-19 02:45:22 [4029] [6] DEBUG: SMPP PDU dump ends. > 2016-01-19 02:45:22 [4029] [6] DEBUG: DLR[mysql]: Adding DLR smsc=my-smsc, > ts=569DE9CF0000, src=SOURCE, dst=7xxxxxxxxxx, mask=31, boxc=my-smsbox > 2016-01-19 02:45:22 [4029] [6] DEBUG: sql: INSERT INTO dlr (smsc, ts, > source, destination, service, url, mask, boxc, stat) VALUES > ('megafon-smsc', '569DE9CF0000', 'SOURCE', '7xxxxxxxxxx', 'dest', ' > http://example.com', '31', 'my-smsbox', '0'); > 2016-01-19 02:45:22 [4029] [6] DEBUG: SMSC[my-smsc]: creating DLR message > 2016-01-19 02:45:22 [4029] [6] DEBUG: SMSC[my-smsc]: DLR = > <http://example.com>http://example.com > > /* log end*/ > > > After this I have delivery_sm. Log: > > /* Delivering PDU */ > > 2016-01-19 02:49:19 [4029] [6] DEBUG: SMPP[my-smsc]: Got PDU: > 2016-01-19 02:49:19 [4029] [6] DEBUG: SMPP PDU 0xb2a01470 dump: > 2016-01-19 02:49:19 [4029] [6] DEBUG: type_name: deliver_sm > 2016-01-19 02:49:19 [4029] [6] DEBUG: command_id: 5 = 0x00000005 > 2016-01-19 02:49:19 [4029] [6] DEBUG: command_status: 0 = 0x00000000 > 2016-01-19 02:49:19 [4029] [6] DEBUG: sequence_number: 10 = 0x0000000a > 2016-01-19 02:49:19 [4029] [6] DEBUG: service_type: NULL > 2016-01-19 02:49:19 [4029] [6] DEBUG: source_addr_ton: 1 = 0x00000001 > 2016-01-19 02:49:19 [4029] [6] DEBUG: source_addr_npi: 1 = 0x00000001 > 2016-01-19 02:49:19 [4029] [6] DEBUG: source_addr: "7xxxxxxxxxx" > 2016-01-19 02:49:19 [4029] [6] DEBUG: dest_addr_ton: 5 = 0x00000005 > 2016-01-19 02:49:19 [4029] [6] DEBUG: dest_addr_npi: 0 = 0x00000000 > 2016-01-19 02:49:19 [4029] [6] DEBUG: destination_addr: "DEST" > 2016-01-19 02:49:19 [4029] [6] DEBUG: esm_class: 4 = 0x00000004 > 2016-01-19 02:49:19 [4029] [6] DEBUG: protocol_id: 0 = 0x00000000 > 2016-01-19 02:49:19 [4029] [6] DEBUG: priority_flag: 0 = 0x00000000 > 2016-01-19 02:49:19 [4029] [6] DEBUG: schedule_delivery_time: NULL > 2016-01-19 02:49:19 [4029] [6] DEBUG: validity_period: NULL > 2016-01-19 02:49:19 [4029] [6] DEBUG: registered_delivery: 0 = 0x00000000 > 2016-01-19 02:49:19 [4029] [6] DEBUG: replace_if_present_flag: 0 = > 0x00000000 > 2016-01-19 02:49:19 [4029] [6] DEBUG: data_coding: 0 = 0x00000000 > 2016-01-19 02:49:19 [4029] [6] DEBUG: sm_default_msg_id: 0 = 0x00000000 > 2016-01-19 02:49:19 [4029] [6] DEBUG: sm_length: 96 = 0x00000060 > 2016-01-19 02:49:19 [4029] [6] DEBUG: short_message: > 2016-01-19 02:49:19 [4029] [6] DEBUG: Octet string at 0xb2a01548: > 2016-01-19 02:49:19 [4029] [6] DEBUG: len: 96 > 2016-01-19 02:49:19 [4029] [6] DEBUG: size: 97 > 2016-01-19 02:49:19 [4029] [6] DEBUG: immutable: 0 > 2016-01-19 02:49:19 [4029] [6] DEBUG: data: 69 64 3a 33 33 36 37 33 > 38 31 39 37 32 20 73 75 id:3367381972 su > 2016-01-19 02:49:19 [4029] [6] DEBUG: data: 62 3a 30 30 31 20 64 6c > 76 72 64 3a 30 30 31 20 b:001 dlvrd:001 > 2016-01-19 02:49:19 [4029] [6] DEBUG: data: 73 75 62 6d 69 74 20 64 > 61 74 65 3a 31 36 30 31 submit date:1601 > 2016-01-19 02:49:19 [4029] [6] DEBUG: data: 31 39 31 33 34 36 20 64 > 6f 6e 65 20 64 61 74 65 191346 done date > 2016-01-19 02:49:19 [4029] [6] DEBUG: data: 3a 31 36 30 31 31 39 31 > 33 35 30 20 73 74 61 74 :1601191350 stat > 2016-01-19 02:49:19 [4029] [6] DEBUG: data: 3a 45 58 50 49 52 45 44 > 20 65 72 72 3a 32 35 34 :EXPIRED err:254 > 2016-01-19 02:49:19 [4029] [6] DEBUG: Octet string dump ends. > 2016-01-19 02:49:19 [4029] [6] DEBUG: message_state: 3 = 0x00000003 > 2016-01-19 02:49:19 [4029] [6] DEBUG: receipted_message_id: > "569DE9CF0000" > 2016-01-19 02:49:19 [4029] [6] DEBUG: SMPP PDU dump ends. > 2016-01-19 02:49:19 [4029] [6] DEBUG: SMPP[my-smsc] handle_pdu, got DLR > 2016-01-19 02:49:19 [4029] [6] DEBUG: DLR[mysql]: Looking for DLR > smsc=my-smsc, ts=569DE9CF0000, dst=79235922029, type=2 > 2016-01-19 02:49:19 [4029] [6] DEBUG: sql: SELECT mask, service, url, > source, destination, boxc FROM dlr WHERE smsc='megafon-smsc' AND > ts='569DE9CF0000'; > 2016-01-19 02:49:19 [4029] [6] DEBUG: Found entry, row[0]=31, > row[1]=service, row[2]=http://example.com, row[3]=SOURCE, > row[4]=7xxxxxxxxxx row[5]=my-smsbox > 2016-01-19 02:49:19 [4029] [6] DEBUG: DLR[mysql]: created DLR message for > URL <http://example.com> <http://example.com> > 2016-01-19 02:49:19 [4029] [6] DEBUG: removing DLR from database > 2016-01-19 02:49:19 [4029] [6] DEBUG: sql: DELETE FROM dlr WHERE > smsc=my-smsc' AND ts='569DE9CF0000' LIMIT 1; > > /* log end */ > > > How can I save this > data: 3a 45 58 50 49 52 45 44 20 65 72 72 3a 32 35 34 :EXPIRED err:254 > to the database? >
