Empty sent messages in web panel

neoscopio · March 8, 2021, 8:28am

Hi,
We’re testing the dev version (master) and successfully configured a Jasmin gateway. Messages are received, credit deducted, but no messages show in user_outgoing and all_outgoing (My sent messages, All messages).

The user report “app=main&inc=feature_report&route=user” shows the correct count of sent messages.

I’m guessing a problem with Jasmin gateway module, but no errors in log. Can you help?

* * 2021-03-07 23:11:47 PID60455db2e2786 - L3 jasmin_hook_sendsms # enter smsc:jasmin smslog_id:2 uid:1 to:XXXXXX
* * 2021-03-07 23:11:47 PID60455db2e2786 - L3 jasmin_hook_sendsms # send url:[http://XXXX:PPPP/send?username=xxx&password=xxx&to=xxx&from=xxx&content=XXXmeXX%21&dlr=yes&dlr-level=1&dlr-url=http%3A%2F%2Fxxx.xxx.com%2Fxxxsms%2Findex.php%3Fapp%3Dcall%26amp%3Bcat%3Dgateway%26amp%3Bplugin%3Djasmin%26amp%3Baccess%3Dcallback]
* * 2021-03-07 23:11:47 PID60455db2e2786 - L2 jasmin_hook_sendsms # sent smslog_id:2 message_id:2ba79985-812d-422d-b5a7-5a62c023152c smsc:jasmin
* * 2021-03-07 23:11:47 PID60455db2e2786 - L3 dlr # isdlrd:1 smslog_id:2 p_status:1 uid:1
* * 2021-03-07 23:11:47 PID60455db2e2786 - L2 simplerate_hook_rate_deduct # enter smslog_id:2
* * 2021-03-07 23:11:47 PID60455db2e2786 - L3 simplerate_hook_rate_getbyprefix # rate not found to:xxxxx default_rate:1 uid:1
* * 2021-03-07 23:11:47 PID60455db2e2786 - L3 simplerate_hook_rate_getcharges # uid:1 u:admin len:24 unicode:0 to:351966913513 enable_credit_unicode:0 count:1 rate:1 charge:1
* * 2021-03-07 23:11:47 PID60455db2e2786 - L3 simplebilling_hook_billing_post # saving smslog_id:2 parent_uid:0 uid:1 rate:1 count:1 charge:1
* * 2021-03-07 23:11:47 PID60455db2e2786 - L3 simplebilling_hook_billing_post # saved smslog_id:2 id:2
* * 2021-03-07 23:11:47 PID60455db2e2786 - L3 simplerate_hook_rate_deduct # deduct successful uid:1 parent_uid:0 smslog_id:2
* * 2021-03-07 23:11:47 PID60455db2e2786 - L2 sendsms_process # end
* * 2021-03-07 23:11:47 PID60455db2e2786 - L2 sendsms_daemon # result queue_code:e191cb71b60cf6df936804fa172f5be0 to:351966913513 flag:1 smslog_id:2
1.2.3.4 xx.xxxx.com 2021-03-07 23:11:47 PID60455db33b1ec - L2 init # WARNING: possible CSRF attack. sid:cbkn4os0cfm4dh27qg43bl94lb ip:11.22.33.44
* * 2021-03-07 23:11:47 PID60455db2e2786 - L2 sendsms_daemon # finish processing queue_code:e191cb71b60cf6df936804fa172f5be0 uid:1 sender_id:test queue_count:1 sms_count:1
* * 2021-03-07 23:11:47 PID60455db2e2786 - L3 playsmsd_once # finish command:sendqueue param:Q_e191cb71b60cf6df936804fa172f5be0_1
* * 2021-03-07 23:11:48 PID604559873a4ab - L3 dlr_update # smslog_id:2 p_status:1 uid:1
* * 2021-03-07 23:11:48 PID604559873a4ab - L2 simplebilling_hook_billing_finalize # saving smslog_id:2
* * 2021-03-07 23:11:48 PID604559873a4ab - L3 simplebilling_hook_billing_finalize # saved smslog_id:2

anton · March 8, 2021, 10:33pm

Check your MySQL table playsms_tblSMSOutgoing, see if the test message saved.

Also, route to SMSC dev and send some test message.

anton

neoscopio · March 8, 2021, 11:29pm

Message is in the table:

mysql> select * from playsms_tblSMSOutgoing;
+-------------+----+-----------+--------------+-----+------------+-----------+--------+-----------+--------------+----------+--------------------------------------+---------------------+---------------------+----------+--------+----------+------------+---------+----------------------------------+
| c_timestamp | id | smslog_id | flag_deleted | uid | parent_uid | p_gateway | p_smsc | p_src     | p_dst        | p_footer | p_msg                                | p_datetime          | p_update            | p_status | p_gpid | p_credit | p_sms_type | unicode | queue_code                       |
+-------------+----+-----------+--------------+-----+------------+-----------+--------+-----------+--------------+----------+--------------------------------------+---------------------+---------------------+----------+--------+----------+------------+---------+----------------------------------+
|  1615245631 |  1 |         1 |            0 |   1 |          0 | jasmin    | jasmin | neoscopio | 321123321123 |          | Olá cá estou eu, o brise continuo.   | 2021-03-08 23:20:29 | 2021-03-08 16:20:31 |        1 |      0 |    0.000 | text       |       1 | d7707d96cc6dccfecbb2132531d42944 |
+-------------+----+-----------+--------------+-----+------------+-----------+--------+-----------+--------------+----------+--------------------------------------+---------------------+---------------------+----------+--------+----------+------------+---------+----------------------------------+

Also Routing a second SMS via DEV got the same results, no messages shown.

PS: I’ve just reinstalled from scratch.

All reports

User	Pending	Sent	Delivered	Failed	Billing	Credit
admin	0	1	1	0	0.00	1000.00
Total: 2	0	1	1	0	0.00	1000.00
⇤←1 to 1 of 1 rows

→⇥|

But “My sent messages” is empty.

anton · March 8, 2021, 11:32pm

So it was saved in DB table, but no show in report page ?

anton

neoscopio · March 8, 2021, 11:33pm

Adding log of DEV route send:

- - 2021-03-08 23:24:07 PID6046afcee19ff - L2 sendsms_hook_playsmsd_loop # prepare chunks queue:4f8b6a5f2fca42fe6528432dd9e28bdd id:2 num:1 chunk:1
- - 2021-03-08 23:24:07 PID6046afcee19ff - L2 sendsms_hook_playsmsd_loop # destination found chunk prepared queue:4f8b6a5f2fca42fe6528432dd9e28bdd
- - 2021-03-08 23:24:07 PID6046afcee19ff - L2 sendsms_hook_playsmsd_loop # job ready queue:4f8b6a5f2fca42fe6528432dd9e28bdd queue_id:2 chunk:1
- - 2021-03-08 23:24:07 PID6046afcee19ff - L3 playsmsd_run # run type:once command:sendqueue param:Q_4f8b6a5f2fca42fe6528432dd9e28bdd_1 pid:10747
- - 2021-03-08 23:24:07 PID6046afcee19ff - L2 sendsms_hook_playsmsd_loop # sendqueue job:Q_4f8b6a5f2fca42fe6528432dd9e28bdd_1 pid:10747
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 playsmsd_once # start command:sendqueue param:Q_4f8b6a5f2fca42fe6528432dd9e28bdd_1
- - 2021-03-08 23:24:07 PID6046b217799f1 - L2 sendsms_hook_playsmsd_once # running once command:sendqueue param:Q_4f8b6a5f2fca42fe6528432dd9e28bdd_1
- - 2021-03-08 23:24:07 PID6046b217799f1 - L2 sendsms_daemon # start processing queue_code:4f8b6a5f2fca42fe6528432dd9e28bdd chunk:1 queue_count:1 sms_count:1 scheduled:2021-03-08 23:24:06 uid:1 gpid:0 sender_id:neoscopio
- - 2021-03-08 23:24:07 PID6046b217799f1 - L2 sendsms_daemon # sending queue_code:4f8b6a5f2fca42fe6528432dd9e28bdd smslog_id:2 to:999321321321 sms_count:1 counter:1
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 sendsms_intercept # msgtemplate modified sms_sender:[neoscopio] sms_footer:[] sms_to:[999321321321] sms_msg:[Hi u there, good morning!!] uid:[1] gpid:[0] sms_type:[text] unicode:[0] queue_code:[4f8b6a5f2fca42fe6528432dd9e28bdd] smsc:[]
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 outgoing_hook_sendsms_intercept # found SMSCs:[dev]
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 outgoing_hook_sendsms_intercept # using prefix based smsc smsc:[dev] uid:1 parent_uid:0 from:neoscopio to:999321321321
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 sendsms_intercept # outgoing modified sms_sender:[neoscopio] sms_footer:[] sms_to:[999321321321] sms_msg:[Hi u there, good morning!!] uid:[1] gpid:[0] sms_type:[text] unicode:[0] queue_code:[4f8b6a5f2fca42fe6528432dd9e28bdd] smsc:[dev]
- - 2021-03-08 23:24:07 PID6046b217799f1 - L2 sendsms_process # start
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 simplerate_hook_rate_getbyprefix # rate not found to:999321321321 default_rate:0 uid:1
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 simplerate_hook_rate_getcharges # uid:1 u:admin len:26 unicode:0 to:999321321321 enable_credit_unicode:0 count:1 rate: charge:0
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 rate_getcharges # uid:1 u:admin len:26 unicode:0 to:999321321321 enable_credit_unicode:0 count:1 rate:0 charge:0
- - 2021-03-08 23:24:07 PID6046b217799f1 - L2 simplerate_hook_rate_cansend # allowed user uid:1 sms_to:999321321321 adhoc_credit:1000 count:1 rate:0 charge:0 adhoc_balance:1000
- - 2021-03-08 23:24:07 PID6046b217799f1 - L2 sendsms # saving smslog_id:2 u:1 parent_uid:0 g:0 gw:dev smsc:dev s:neoscopio d:999321321321 type:text unicode:0 status:0
- - 2021-03-08 23:24:07 PID6046b217799f1 - L2 sendsms_process # saved smslog_id:2 id:2
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 sendsms # final smslog_id:2 gw:dev smsc:dev message:Hi u there, good morning!! len:26
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 dev_hook_sendsms # enter smsc:dev smslog_id:2 uid:1 to:999321321321
- - 2021-03-08 23:24:07 PID6046b217799f1 - L2 simplerate_hook_rate_deduct # enter smslog_id:2
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 simplerate_hook_rate_getbyprefix # rate not found to:999321321321 default_rate:0 uid:1
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 simplerate_hook_rate_getcharges # uid:1 u:admin len:26 unicode:0 to:999321321321 enable_credit_unicode:0 count:1 rate: charge:0
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 rate_getcharges # uid:1 u:admin len:26 unicode:0 to:999321321321 enable_credit_unicode:0 count:1 rate:0 charge:0
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 simplebilling_hook_billing_post # saving smslog_id:2 parent_uid:0 uid:1 rate:0 count:1 charge:0
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 simplebilling_hook_billing_post # saved smslog_id:2 id:2
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 simplerate_hook_rate_deduct # deduct successful uid:1 parent_uid:0 smslog_id:2
- - 2021-03-08 23:24:07 PID6046b217799f1 - L2 sendsms_process # end
- - 2021-03-08 23:24:07 PID6046b217799f1 - L2 sendsms_daemon # result queue_code:4f8b6a5f2fca42fe6528432dd9e28bdd to:999321321321 flag:1 smslog_id:2
- - 2021-03-08 23:24:07 PID6046b217799f1 - L2 sendsms_daemon # finish processing queue_code:4f8b6a5f2fca42fe6528432dd9e28bdd uid:1 sender_id:neoscopio queue_count:1 sms_count:1
- - 2021-03-08 23:24:07 PID6046b217799f1 - L3 playsmsd_once # finish command:sendqueue param:Q_4f8b6a5f2fca42fe6528432dd9e28bdd_1
- - 2021-03-08 23:24:59 PID6046afcedf2a2 - L3 dlr # isdlrd:1 smslog_id:2 p_status:3 uid:1
- - 2021-03-08 23:24:59 PID6046afcee3446 - L3 dlr_update # smslog_id:2 p_status:3 uid:1
- - 2021-03-08 23:24:59 PID6046afcee3446 - L2 simplebilling_hook_billing_finalize # saving smslog_id:2
- - 2021-03-08 23:24:59 PID6046afcee3446 - L3 simplebilling_hook_billing_finalize # saved smslog_id:2

neoscopio · March 8, 2021, 11:34pm

Yes, report page shows no SMS in the list.

anton · March 8, 2021, 11:45pm

Above log shown that submitted SMS saved in tblSMSOutgoing. Related codes:

github.com

playsms/playsms/blob/master/storage/application/plugin/core/sendsms/fn.php#L506-L514


      
          	// thus the message length counts is inaccurate
          	$sms_msg = str_replace("\r\n", "\n", $sms_msg);
          
          	// just to make sure its length, we need to stripslashes message before enter other procedures
          	$sms_sender = stripslashes($sms_sender);
          	$sms_msg = stripslashes($sms_msg);
          	$sms_footer = stripslashes($sms_footer);
          
          	// fixme anton - mobile number can be anything, screened by gateway

After that, plugin report will read the table and show it. If theres issue it should be in plugin report. Related codes:

github.com

playsms/playsms/blob/master/storage/application/plugin/feature/report/user_outgoing.php#L43-L65


      
          		$table = _DB_PREF_ . "_tblSMSOutgoing AS A";
          		$fields = "B.username, A.p_gateway, A.p_smsc, A.smslog_id, A.p_dst, A.p_sms_type, A.p_msg, A.p_footer, A.p_datetime, A.p_update, A.p_status, B.uid, A.queue_code";
          		$conditions = [
          			'B.uid' => $_SESSION['uid'],
          			'A.flag_deleted' => 0,
          		];
          		$extras = [];
          
          		if ($queue_code = trim($_REQUEST['queue_code'])) {
          			$conditions['A.queue_code'] = $queue_code;
          			$queue_label = "<p class=lead>" . sprintf(_('List of queue %s'), $queue_code) . "</p>";
          			$queue_home_link = _back($base_url);
          			$base_url .= '&queue_code=' . $queue_code;
          		} else {
          			$fields .= ", COUNT(A.queue_code) AS queue_count";
          			$extras['GROUP BY'] = "A.queue_code";
          		}
          
          		$search = themes_search($search_category, $base_url);
          		$keywords = $search['dba_keywords'];

This file has been truncated. show original

SQL used by plugin report (example with uid=1):

SELECT B.username, A.p_gateway, A.p_smsc, A.smslog_id, A.p_dst, A.p_sms_type, A.p_msg, A.p_footer, A.p_datetime, A.p_update, A.p_status, B.uid, A.queue_code, COUNT(A.queue_code) AS queue_count
FROM `playsms_tblSMSOutgoing` AS A
INNER JOIN `playsms_tblUser` AS B ON A.uid=B.uid AND A.flag_deleted=B.flag_deleted
WHERE B.uid=1 AND A.flag_deleted=0
GROUP BY A.queue_code
ORDER BY A.smslog_id DESC

Try query that in your DB.

I’ll try to install fresh master version and test, perhaps later today.

anton

neoscopio · March 9, 2021, 12:11am

The query fails:

mysql> SELECT B.username, A.p_gateway, A.p_smsc, A.smslog_id, A.p_dst, A.p_sms_type, A.p_msg, A.p_footer, A.p_datetime, A.p_update, A.p_status, B.uid, A.queue_code, COUNT(A.queue_code) AS queue_count
    -> FROM `playsms_tblSMSOutgoing` AS A
    -> INNER JOIN `playsms_tblUser` AS B ON A.uid=B.uid AND A.flag_deleted=B.flag_deleted
    -> WHERE B.uid=1 AND A.flag_deleted=0
    -> GROUP BY A.queue_code
    -> ORDER BY A.smslog_id DESC;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'playsms.A.p_gateway' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

anton · March 9, 2021, 1:03am

Something like this may help:

Anton

neoscopio · March 9, 2021, 9:00am

It didn’t. This is a standart ubuntu server 20.04 installation, that might mean this will happen to everybody.
But even as a temporary fix, it isn’t working:

mysql> use playsms;
Database changed
mysql> SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT B.username, A.p_gateway, A.p_smsc, A.smslog_id, A.p_dst, A.p_sms_type, A.p_msg, A.p_footer, A.p_datetime, A.p_update, A.p_status, B.uid, A.queue_code, COUNT(A.queue_code) AS queue_count FROM
`playsms_tblSMSOutgoing` AS A INNER JOIN `playsms_tblUser` AS B ON A.uid=B.uid AND A.flag_deleted=B.flag_deleted WHERE B.uid=1 AND A.flag_deleted=0 GROUP BY A.queue_code ORDER BY A.smslog_id DESC;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'playsms.A.p_gateway' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> 
[1]+  Stopped                 mysql
here:~# cat /etc/issue
Ubuntu 20.04.2 LTS \n \l
here:~# uname -a
Linux neosms 5.4.44-2-pve #1 SMP PVE 5.4.44-2 (Wed, 01 Jul 2020 16:37:57 +0200) x86_64 x86_64 x86_64 GNU/Linux

neoscopio · March 9, 2021, 9:08am

I was able to bypass this after all, trying other stuff. It’s a fix.

What seems to have worked is:

SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

My current sql mode:

mysql> SELECT @@GLOBAL.sql_mode;
+----------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                  |
+----------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

anton · March 9, 2021, 9:24am

Do you use MySQL or Mariadb ?

anton

neoscopio · March 9, 2021, 10:14am

Mysql

here:~# mysql --version         
mysql  Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

anton · March 9, 2021, 11:54am

Ok. Nothing I can do right now but to reference this thread for a temporary fix.

The solution is to rewrite the SQL without group by, and add another one with a group by to get message counts. Or perhaps insert an SQL for setting sql_mode to a session, not global, before the group by query.

This is with group by:

And this is withoup group by:

anton

neoscopio · March 9, 2021, 11:58am

Well, at least we have a fix.
Does it work on vanilla mariadb?

I have another issue with DLRs from jasmin, I’m still testing, I will open another issue when I have more tests.

anton · March 9, 2021, 12:00pm

I installed fresh Ubuntu 18 or 20 previously with MariaDB, seems like the sql_mode off for this, so I didn’t encounter the issue.

anton

system · May 8, 2021, 12:00pm

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Jasmin as a Gateway Help	15	3431	August 17, 2023
Sent Messages showing empty Help	2	211	May 5, 2023
Playsms 1.4 gamu reports My sent messages empty Help	10	1847	August 28, 2018
Jasmin Gateway and Playsms Help	1	441	September 26, 2020
PlaySMS and Smstools 3 Help	20	7013	February 22, 2016

Empty sent messages in web panel

All reports

Related Topics