Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "cmail" LINE 1: ...TINCT c.cid, u.init, c.uid, c.name, c.nid, c.mail cmail, u.m... ^ in /var/www/includes/database.pgsql.inc on line 138.
* user warning: query: SELECT DISTINCT c.cid, u.init, c.uid, c.name, c.nid, c.mail cmail, u.mail umail, u.init uinit, c.uid, c.name, cn.notify_hash mymd5 FROM comments c INNER JOIN comment_notify cn on c.cid = cn.cid LEFT OUTER JOIN users u ON c.uid = u.uid WHERE nid = 29 AND cn.notify = 1 AND c.status = 0 AND (u.status = 1 or u.status = '') in /var/www/sites/all/modules/comment_notify/comment_notify.module on line 302.
patches:
-c.mail cmail
+c.mail AS cmail
-u.mail umail
+u.mail AS umail
....
and
--(u.status OR u.status = '')
++(u.status OR u.status is null)
Comment | File | Size | Author |
---|---|---|---|
#9 | 308585_pgsql_round_2.patch | 1.23 KB | greggles |
#4 | 308585_comment_notify_pgsql_compliance_4.patch | 6.41 KB | greggles |
#1 | 308585_comment_notify_pgsql_compliance.patch | 1.32 KB | greggles |
Comments
Comment #1
gregglesHello Helg - I was curious if that worked so thanks for your feedback.
That last piece won't work for MySQL, so I've attached a patch which incorporates all three tests. It's not a perfect solution, but I can't think of a better one.
Can you test this out?
Comment #2
Helg CreditAttribution: Helg commentedno! this not valid code for PostgreSQL
@@ -296,9 +296,9 @@ function _comment_notify_mailalert($comm
$comment_mail = $comment->mail;
}
- $result = db_query("SELECT DISTINCT c.cid, u.init, c.uid, c.name, c.nid, c.mail cmail, u.mail umail, u.init uinit, c.uid, c.name, cn.notify_hash mymd5
+ $result = db_query("SELECT DISTINCT c.cid, u.init, c.uid, c.name, c.nid, c.mail AS cmail, u.mail AS umail, u.init AS uinit, c.uid, c.name, cn.notify_hash AS mymd5
FROM {comments} c INNER JOIN {comment_notify} cn on c.cid = cn.cid LEFT OUTER JOIN {users} u ON c.uid = u.uid
- WHERE nid = %d AND cn.notify = 1 AND c.status = 0 AND (u.status = 1 or u.status = '')", $nid
+ WHERE nid = %d AND cn.notify = 1 AND c.status = 0 AND (u.status = 1 OR
u.status = '' ORu.status IS NULL)", $nid);
PostrgreSQL not valid code u.status = ''
u.status => integer
'' => varchar
Comment #3
gregglesDoes pgsql give an error message when comparing an integer to a varchar?
Unfortunately mysql requires us to use
u.status = ''
for an equivalent test to the pgsqlu.status is null
.Do you have a proposal which will work on both MySQL and PostgreSQL?
Comment #4
gregglesHere's an alternate approach - we can do the filtering in php rather than the DB. It's probably slightly slower so I document the weakness in a code comment...
Comment #5
gregglesI committed the "AS" parts of this query since those seem reasonable to me.
I'm still waiting to hear from a pgsql person regarding the proper way to fix this that is cross-db.
Comment #6
Yoran CreditAttribution: Yoran commented@greggles Your last patch is working for me.
Drupal anonymous management is such a mess... I mean the all lot, your module helps a lot !!!
Comment #7
greggles@ulhume - thanks for the feedback, that's interesting. I don't like that patch as much for performance reasons. Can you tell me if the patch in #1 gives an error?
Comment #8
Yoran CreditAttribution: Yoran commented@greggles
I tried #1 :
1/ At the beginning of the query, there is 2 missing "AS" for uinit and mymd5:
2/ In postgresql u.status='' will always fail as u.status is an integer, not a varchar.
3/ I don't really understand why you have this " OR u.status = '' OR u.status IS NULL". When you first install drupal, you'll have 2 rows in {users} table. Admin (uid=1) and Anonymous (uid=0). The only thing you will catch with outer join and your test is users that has been deleted with {comment}.uid that is not anymore in {users} table. If your problem is that anonymous user has status=0, why no simply add "or uid=0)" ?
For me this is just enough :
* if c.uid=0 => OK
* if c.uid<>0 => (u.status = 1 ? OK : drop )
* if c.uid don't exists in {users} =>u.status=null => u.status<>1 AND u.uid<>0 => drop
I hope this is clear enough and that I'm not missing something :-)
Comment #9
greggles@ulhume - brilliant! I believe that will work perfectly. That was very creative to think of using uid = 0 for this csae.
Here's a patch against the latest 5.x code. Can you confirm it?
I fixed the " AS " problems earlier so now we just need to fix the u.status = '' problem.
Comment #10
Yoran CreditAttribution: Yoran commentedI confirm this is working at least on my test & production servers.
Happy I could help :)
Comment #11
gregglesFixed in both 5.x-2 and 6.x.
Thanks again, ulhume!
Comment #12
Anonymous (not verified) CreditAttribution: Anonymous commentedAutomatically closed -- issue fixed for two weeks with no activity.