Mark all read button does not work under PostgreSQL :

    * warning: pg_query() [function.pg-query]: Query failed: ERREUR: erreur de syntaxe sur ou près de « h » LINE 1: DELETE h ^ in /home/html/manifestation-contre-hadopi/includes/database.pgsql.inc on line 139.
    * user warning: ERREUR: erreur de syntaxe sur ou près de « h » LINE 1: DELETE h ^ query: DELETE h FROM history AS h INNER JOIN term_node AS tn ON (h.nid = tn.nid) INNER JOIN term_data AS td ON (td.tid = tn.tid) WHERE h.uid = 2067 AND td.vid = 2 in /home/html/manifestation-contre-hadopi/sites/all/modules/advanced_forum/advanced_forum.module on line 1090.
    * warning: pg_query() [function.pg-query]: Query failed: ERREUR: la valeur d'une clé dupliquée rompt la contrainte unique « history_pkey » in /home/html/manifestation-contre-hadopi/includes/database.pgsql.inc on line 139.
    * user warning: ERREUR: la valeur d'une clé dupliquée rompt la contrainte unique « history_pkey » query: INSERT INTO history (uid, nid, timestamp) SELECT DISTINCT 2067, n.nid, 1242042956 FROM node AS n INNER JOIN term_node AS tn ON n.nid=tn.nid INNER JOIN node_comment_statistics AS ncs ON ncs.nid = n.nid INNER JOIN term_data AS td ON tn.tid = td.tid WHERE (n.created > 1239450956 OR ncs.last_comment_timestamp > 1239450956) AND td.vid = 2 in /home/html/manifestation-contre-hadopi/sites/all/modules/advanced_forum/advanced_forum.module on line 1103.

PostgreSQL does not allow nested delete queries.

Instead use this SQL92 variant :
DELETE FROM foo WHERE id IN (SELECT CLAUSE).

This will work on any database system.
Please apply this patch safely.

CommentFileSizeAuthor
#10 patch-adv-forum-pgsql-0.diff0 bytesgrub3
advanced_forum_pgsql.diff1.05 KBgrub3

Comments

grub3’s picture

grub3 commented
Status: Patch (to be ported) » Needs review
grub3’s picture

grub3 commented
Status: Needs review » Reviewed & tested by the community

Please apply this patch. Seeting status to Reviewed by community.

michelle’s picture

michelle
she/her
Primary language English
Location Wisconsin, USA
commented

@jmpoure: I will. I'm not working on AF right now.

Michelle

michelle’s picture

michelle
she/her
Primary language English
Location Wisconsin, USA
commented
Status: Reviewed & tested by the community » Needs work

This breaks the mark read functionality on MySQL. I get these errors when trying to use it after applying the patch:

* user warning: You can't specify target table 'history' for update in FROM clause query: DELETE FROM history WHERE nid IN ( SELECT h.nid FROM history AS h INNER JOIN term_node AS tn ON (h.nid = tn.nid) INNER JOIN term_data AS td ON (td.tid = tn.tid) WHERE h.uid = 1 AND td.vid = 1 ) in /path/sites/all/modules/advanced_forum/advanced_forum.module on line 1094.
    
* user warning: Duplicate entry '1-117' for key 1 query: INSERT INTO history (uid, nid, timestamp) SELECT DISTINCT 1, n.nid, 1243005358 FROM node AS n INNER JOIN term_node AS tn ON n.nid=tn.nid INNER JOIN node_comment_statistics AS ncs ON ncs.nid = n.nid INNER JOIN term_data AS td ON tn.tid = td.tid WHERE (n.created > 1240413358 OR ncs.last_comment_timestamp > 1240413358) AND td.vid = 1 in /path/sites/all/modules/advanced_forum/advanced_forum.module on line 1107.

Michelle

michelle’s picture

michelle
she/her
Primary language English
Location Wisconsin, USA
commented

Is anyone interested in working on this? Otherwise I'm going to won't fix it. I'm willing to support PG if I get patches that don't break MySQL but I don't have any set up to fix PG issues myself.

Michelle

michelle’s picture

michelle
she/her
Primary language English
Location Wisconsin, USA
commented
Category: bug » task
Status: Needs work » Postponed (maintainer needs more info)

Going to get this off my active list for now. If anyone wants to work on it, set it active again. Otherwise I'll give it a while before closing it.

Michelle

adzio’s picture

adzio commented

This is bound to not work in MySQL, as documented at http://dev.mysql.com/doc/refman/5.1/en/delete.html:

Currently, you cannot delete from a table and select from the same table in a subquery.

michelle’s picture

michelle
she/her
Primary language English
Location Wisconsin, USA
commented
Status: Postponed (maintainer needs more info) » Closed (won't fix)

Ok, no one has stepped up for this and there's an issue to fix other problems with MAR anyway, which could make this moot, so just going to close this one out.

Michelle

grub3’s picture

grub3 commented
Assigned: Unassigned » grub3
Status: Closed (won't fix) » Active

Could we reopen this issue to find a suitable solution?

It seems that DELETE on JOINs require the using syntax: http://drupal.org/node/555562

I will propose a solution shortly.

grub3’s picture

grub3 commented
Category: task » bug
Status: Active » Needs review
StatusFileSize
new patch-adv-forum-pgsql-0.diff0 bytes

It seems that DELETE on JOINs require the using syntax: http://drupal.org/node/555562

The correct syntax line 455 is:

 $sql = "DELETE FROM {history} AS h
          USING {term_node} AS tn , {term_data} AS td  
          WHERE (h.nid = tn.nid) AND (td.tid = tn.tid) AND (h.uid = %d) AND (td.vid = %d)";

I tested successfully under PostgreSQL. Please test under MySQL and apply.

Thanks !

grub3’s picture

grub3 commented

Here is a second patch fixing line 425.

The correct syntax is:

 $sql = "DELETE FROM {history} AS h
            USING {term_node} as tn
            WHERE h.nid = tn.nid AND h.uid = %d AND tn.tid = %d";
michelle’s picture

michelle
she/her
Primary language English
Location Wisconsin, USA
commented

Thanks for the patch. I will try it out next time I work on AF.

Michelle

grub3’s picture

grub3 commented

Any progress, please apply, it is a safe patch.

michelle’s picture

michelle
she/her
Primary language English
Location Wisconsin, USA
commented

No, I haven't had time to work on AF and probably won't until the end of the month. The best I can do right now is triage the queue.

Michelle

michelle’s picture

michelle
she/her
Primary language English
Location Wisconsin, USA
commented
Status: Needs review » Postponed (maintainer needs more info)

Looking at this now and there isn't anything in the patch. Could you please repost?

Michelle

grub3’s picture

grub3 commented

Basically, you may use:

$sql = "DELETE FROM {history} AS h
          USING {term_node} AS tn , {term_data} AS td 
          WHERE (h.nid = tn.nid) AND (td.tid = tn.tid) AND (h.uid = %d) AND (td.vid = %d)";

and

$sql = "DELETE FROM {history} AS h
            USING {term_node} as tn
            WHERE h.nid = tn.nid AND h.uid = %d AND tn.tid = %d";

This is the correct syntax as explained in the developer doc:
http://drupal.org/node/555562

grub3’s picture

grub3 commented
Status: Postponed (maintainer needs more info) » Needs review
michelle’s picture

michelle
she/her
Primary language English
Location Wisconsin, USA
commented
Status: Needs review » Needs work

This gives me "user warning: Unknown table 'history' in MULTI DELETE query" which doesn't make any sense to me as history is the correct table name and the brackets are there.

Michelle

michelle’s picture

michelle
she/her
Primary language English
Location Wisconsin, USA
commented
Status: Needs work » Closed (won't fix)

If someone gives me a working patch that doesn't break MySQL, I'll add it but there's not likely to be another 1.x release of AF so I'm not putting any work into it.

Michelle