I updated to 3.0-alpha1 and the DB update went without any problems.

However, at 'admin/content/simplenews/statistics' none of the stats of the newsletters already sent are displayed anymore.

What do I need to do to have them displayed again?

(I already reset the cache and ran cron.)

CommentFileSizeAuthor
#25 simplenews_statistics-6x30alpha3-query_fix-1370616-25.patch1.59 KBrmcom
#19 simplenews_statistics-6x30alpha2patched17-sort_names_revert-1370616-19.patch1.72 KBrmcom
#17 simplenews_statistics-6x30alpha2-query_fixes-1370616-17.patch4.21 KBrmcom

Comments

rmcom’s picture

rmcom commented

on a different instance, I get the following error message:

warning: pg_query(): Query failed: ERROR: syntax error at or near "LEFT" LINE 8: order by created desc LEFT JOIN support_ticket st ON n... ^ in /var/www/includes/database.pgsql.inc on line 138.
user warning: query: select n.nid, n.title, t.name, n.created, ss.send, ss.unique_opens, ss.user_unique_click_through from simplenews_statistics ss inner join node n on ss.nid=n.nid inner join term_node tn on n.vid=tn.vid inner join term_data t on tn.tid=t.tid where t.vid=1 order by created desc LEFT JOIN support_ticket st ON n.nid = st.nid WHERE ( (st.client IN (1,2) OR st.client IS NULL)) in /var/www/sites/all/modules/contrib/simplenews_statistics/simplenews_statistics.module on line 284.

weseze’s picture

weseze commented

Could you try the latest dev release and see if that fixes it? The dev is currently just as stable to use as the alpha.

rmcom’s picture

rmcom commented

I am afraid I can't at the moment as our testing instance is not available at the moment, but will do so asap.

weseze’s picture

weseze commented
Status: Active » Postponed (maintainer needs more info)
weseze’s picture

weseze commented

the new alpha2 version contains a few bugfixes related to these issues. Please update and report back. Thanks!

rmcom’s picture

rmcom commented
Version: 6.x-3.0-alpha1 » 6.x-3.0-alpha2
Status: Postponed (maintainer needs more info) » Needs work

Issue completely fixed: stats of old newsletters are now showing. Thank you, weseze!

However, after I updated to alpha2, I cannot see the opens and clicks anymore:

All the tables at admin/content/simplenews/statistics/NODE are empty and show "No statistics available"althought the numbers on the overview page at admin/content/simplenews/statistics seem correct.

Does this mean that the data is lost or only that it is not showing on the opens and clicks pages?

If the data was lost during the update, I would have to go back to my db backup asap. Please advise.

rmcom’s picture

rmcom commented

I can confirm thanks to the new Views integration (which is absolutely fantastic!! thank you!) that the data is there, but it does not show in the standard statistics tables.

Revised issue summary:

All the tables (opens and clicks) at
admin/content/simplenews/statistics/NID
are empty and show "No statistics available"

although the numbers on the overview page at admin/content/simplenews/statistics seem correct.

weseze’s picture

weseze commented

I can not think of a reason why it isn't working... could you attach an sql -dump of the 3 simplenews_statistics tables?

rmcom’s picture

rmcom commented

Here are the error messages I am getting:

at /admin/content/simplenews/statistics/NID (opens)

warning: pg_query(): Query failed: ERROR: column "simplenews_statistics_opens.nid" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select count(*) from (select nid, email, timestamp, count(em... ^ in /var/www/includes/database.pgsql.inc on line 138.
user warning: query: select count(*) from (select nid, email, timestamp, count(email) as opens from simplenews_statistics_opens where nid=43 group by email) as results group by nid in /var/www/sites/all/modules/contrib/simplenews_statistics/simplenews_statistics.module on line 346.
warning: pg_query(): Query failed: ERROR: column "simplenews_statistics_opens.nid" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select nid, email, timestamp, count(email) as opens from sim... ^ in /var/www/includes/database.pgsql.inc on line 138.
user warning: query: select nid, email, timestamp, count(email) as opens from simplenews_statistics_opens where nid=43 group by email ORDER BY timestamp DESC LIMIT 50 OFFSET 0 in /var/www/sites/all/modules/contrib/simplenews_statistics/simplenews_statistics.module on line 346.

and at /admin/content/simplenews/statistics/NID/clicks (clicks)

warning: pg_query(): Query failed: ERROR: column "simplenews_statistics_clicks.nid" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select count(*) from (select nid, url, email, count(url) as ... ^ in /var/www/includes/database.pgsql.inc on line 138.
user warning: query: select count(*) from (select nid, url, email, count(url) as clicks from simplenews_statistics_clicks where nid=43 group by email, url) as results group by nid in /var/www/sites/all/modules/contrib/simplenews_statistics/simplenews_statistics.module on line 376.
warning: pg_query(): Query failed: ERROR: column "simplenews_statistics_clicks.nid" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select nid, url, email, count(url) as clicks from simplenews... ^ in /var/www/includes/database.pgsql.inc on line 138.
user warning: query: select nid, url, email, count(url) as clicks from simplenews_statistics_clicks where nid=43 group by email, url ORDER BY email DESC LIMIT 50 OFFSET 0 in /var/www/sites/all/modules/contrib/simplenews_statistics/simplenews_statistics.module on line 376.

weseze’s picture

weseze commented

Ok, I'm not used to writing sql for postgre and I haven't got a test environment... So could you try something?
In the simplenews_statistics.module, find:
$count_query = 'select count(*) from ('.$main_query.') as results group by nid';
And change it to:
$count_query = 'select count(nid) from ('.$main_query.') as results group by nid';
There should be 2 occurences of this line of code, both need to be changed.

Please do let me know if that works, so I can commit it.

rmcom’s picture

rmcom commented

I made the two changes (changing '*' to 'nid' twice) in simplenews_statistics.module

I am still getting the following error:

warning: pg_query(): Query failed: ERROR: column "simplenews_statistics_opens.nid" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select count(nid) from (select nid, email, timestamp, count(... ^ in /var/www/includes/database.pgsql.inc on line 138.
user warning: query: select count(nid) from (select nid, email, timestamp, count(email) as opens from simplenews_statistics_opens where nid=31 group by email) as results group by nid in /var/www/sites/all/modules/contrib/simplenews_statistics/simplenews_statistics.module on line 346.
warning: pg_query(): Query failed: ERROR: column "simplenews_statistics_opens.nid" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select nid, email, timestamp, count(email) as opens from sim... ^ in /var/www/includes/database.pgsql.inc on line 138.
user warning: query: select nid, email, timestamp, count(email) as opens from simplenews_statistics_opens where nid=31 group by email ORDER BY timestamp DESC LIMIT 50 OFFSET 0 in /var/www/sites/all/modules/contrib/simplenews_statistics/simplenews_statistics.module on line 346.

Could this comment provide a solution? http://drupal.org/node/280742#comment-1075735

rmcom’s picture

rmcom commented

I have also installed the module on a new instance and am getting these new error messages at /admin/content/simplenews/statistics when I send a newsletter:

warning: pg_query(): Query failed: ERROR: column "xxx@xxx.com" does not exist LINE 1: ...nid from simplenews_statistics_clicks where email="xxx@xx... ^ in /var/www/includes/database.pgsql.inc on line 138.
user warning: query: select nid from simplenews_statistics_clicks where email="xxx@xxx.com" and nid=92 limit 1 in /var/www/sites/all/modules/contrib/simplenews_statistics/simplenews_statistics.module on line 492.
warning: pg_query(): Query failed: ERROR: column "xxx@xxx.com" does not exist LINE 1: ...stics_clicks (email, nid, url, timestamp) values ("xxx@xx... ^ in /var/www/includes/database.pgsql.inc on line 138.
user warning: query: insert into simplenews_statistics_clicks (email, nid, url, timestamp) values ("xxx@xxx.com", 92, "http://www.xxx.com/link", 1327345100) in /var/www/sites/all/modules/contrib/simplenews_statistics/simplenews_statistics.module on line 503.

Are those related?
From what you can see, are these errors related to me using postgresql?

Please let me know what else I can do to help.

weseze’s picture

weseze commented

I don't think it has got anything to do with postgresql. It's much more likely something to do with me writing bad query's... My plan to get this fixed is working on the views integration and getting rid of all the custom query's.
I'll try and get it working as soon as possible. (have been a bit busy with another project ;))

rmcom’s picture

rmcom commented

Greatly appreciated! I am standing by to help in any way I can.

rmcom’s picture

rmcom commented

I am also getting this error at /admin/content:

warning: pg_query(): Query failed: ERROR: column "xxx@xxx.com" does not exist LINE 1: ...nid from simplenews_statistics_clicks where email="xxx@xx... ^ in /var/www/includes/database.pgsql.inc on line 138.
user warning: query: select nid from simplenews_statistics_clicks where email="xxx@xxx.com" and nid=94 limit 1 in /var/www/sites/all/modules/contrib/simplenews_statistics/simplenews_statistics.module on line 492.
warning: pg_query(): Query failed: ERROR: column "xxx@xxx.com" does not exist LINE 1: ...stics_clicks (email, nid, url, timestamp) values ("xxx@xx... ^ in /var/www/includes/database.pgsql.inc on line 138.
user warning: query: insert into simplenews_statistics_clicks (email, nid, url, timestamp) values ("xxx@xxx.com", 94, "http://www.xxx.com", 1327378561) in /var/www/sites/all/modules/contrib/simplenews_statistics/simplenews_statistics.module on line 503.

rmcom’s picture

rmcom commented

And at the send, the dblog registers for some, not all, outgoing emails for each the following succession of 4 php error messages:

Message 1:

pg_query(): Query failed: ERROR: column "abc@def.com" does not exist LINE 1: ...nid from simplenews_statistics_clicks where email="abc@de... ^ in /var/www/includes/database.pgsql.inc on line 138.

Message 2:

query: select nid from simplenews_statistics_clicks where email="abc@def.com" and nid=94 limit 1 in /var/www/sites/all/modules/contrib/simplenews_statistics/simplenews_statistics.module on line 492.

Message 3:

pg_query(): Query failed: ERROR: column "abc@def.com" does not exist LINE 1: ...stics_clicks (email, nid, url, timestamp) values ("abc@de... ^ in /var/www/includes/database.pgsql.inc on line 138.

Message 4:

query: insert into simplenews_statistics_clicks (email, nid, url, timestamp) values ("abc@def.com", 94, "http://www.abc.com/link", 1327442843) in /var/www/sites/all/modules/contrib/simplenews_statistics/simplenews_statistics.module on line 503.

Hope this is of value!

rmcom’s picture

rmcom commented
Title: after update, the stats of old newsletters are not showing » after update, the stats of old newsletters are not showing and queries produce error messages
Component: User interface » Code
Status: Needs work » Patch (to be ported)
StatusFileSize
new simplenews_statistics-6x30alpha2-query_fixes-1370616-17.patch4.21 KB

weseze,

A friend of mine was kind enough to take a look and figured out what was going on. Thanks Mr. 3T!

This patch takes care of both query issues described above: (a) the opens/clicks display issue and (b) the query issue (the 4 messages) that I posted twice. (b) also made it impossible for the db to record some open and click data.

Attached the patch, already tested on a production server this evening.

Should be be ready to roll! alpha3 ?

weseze’s picture

weseze commented

Thanks for sharing the patch!
It looks very good, but I have a question. Could you ask Mr. 3T for me and post back?

Why has he:

  $header = array(
    array('data' => t('Url'), 'field' => 'url', 'sort' => 'desc'),
    array('data' => t('Email'), 'field' => 'email', 'sort' => 'desc'),
    array('data' => t('Click(s)'), 'field' => 'clicks'));

changed to:

  $header = array(
    array('data' => t('Url'), 'field' => 1, 'sort' => 'desc'),
    array('data' => t('Email'), 'field' => 2, 'sort' => 'desc'),
    array('data' => t('Click(s)'), 'field' => 3));
rmcom’s picture

rmcom commented
StatusFileSize
new simplenews_statistics-6x30alpha2patched17-sort_names_revert-1370616-19.patch1.72 KB

Mr. 3T writes:

Here is a patch to revert back to column names for sorting.

Again, sometimes some databases can have difficulty sorting using the
column alias in the sort clause. This is more often the case with
complex queries and with aliases to columns containing aggregate
functions (e.g. min, max, avg, ...).

I changed it from field/alias name sorting to number based as part of
the process of fixing the other SQL problems. Having fixed those,
sorting by column/alias name works again. The included patch reverts
from column number back to column name.

Mr. 3T

weseze, another question:

I just looked at the table, and I would also need the date/time stamp per click in the CLICKS table and do the primary sort by that:

Could you add date/time column with sort preference to clicks table before you roll the patch?

weseze’s picture

weseze commented

Patch comitted (thanks for the work!)
Also added the timestamp for the clicks to the table and made it the default sort.
3.0-alpha3 should be online shortly ;)

weseze’s picture

weseze commented
Status: Patch (to be ported) » Closed (fixed)
rmcom’s picture

rmcom commented

oops, opening the issue again, as the timestamp query produces the following error:

warning: pg_query(): Query failed: ERROR: column "simplenews_statistics_clicks.timestamp" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT COUNT(*) FROM (SELECT url, timestamp, email, COUNT(ur... ^ in /var/www/includes/database.pgsql.inc on line 138.
user warning: query: SELECT COUNT(*) FROM (SELECT url, timestamp, email, COUNT(url) AS clicks FROM simplenews_statistics_clicks WHERE nid=94 GROUP BY email, url) AS results in /var/www/sites/all/modules/contrib/simplenews_statistics/simplenews_statistics.module on line 314.
warning: pg_query(): Query failed: ERROR: column "simplenews_statistics_clicks.timestamp" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT url, timestamp, email, COUNT(url) AS clicks FROM simp... ^ in /var/www/includes/database.pgsql.inc on line 138.
user warning: query: SELECT url, timestamp, email, COUNT(url) AS clicks FROM simplenews_statistics_clicks WHERE nid=94 GROUP BY email, url ORDER BY timestamp DESC LIMIT 50 OFFSET 0 in /var/www/sites/all/modules/contrib/simplenews_statistics/simplenews_statistics.module on line 314.

I will ask Mr. 3T if he can take a look for a patch.

rmcom’s picture

rmcom commented
Status: Closed (fixed) » Needs work
rmcom’s picture

rmcom commented
Version: 6.x-3.0-alpha2 » 6.x-3.0-alpha3
rmcom’s picture

rmcom commented
Status: Needs work » Patch (to be ported)
StatusFileSize
new simplenews_statistics-6x30alpha3-query_fix-1370616-25.patch1.59 KB

Fixed, thanks to Mr. 3T, patch enclosed.

weseze’s picture

weseze commented
Status: Patch (to be ported) » Fixed

comitted to dev.

weseze’s picture

weseze commented
Status: Fixed » Closed (fixed)

It's better you open a new issue if you find bugs like these. I can create a better log and better commit message if there are seperate issues for seperate bugs ;)