Query Error on RSS Feed

Found a bug? Tell us!!
peaz
Regular
Posts: 18
Joined: Tue Apr 18, 2006 12:26 pm
Contact:

Query Error on RSS Feed

Post by peaz » Fri May 30, 2008 9:16 pm

I'm having this error when acessing my RSS feed (http://atpeaz.placidthoughts.com/index. ... index.rss2)
Any idea why?

Code: Select all

Query failed: <pre>SELECT 
                    e.isdraft AS orderkey,
ep_cache_extended.value AS ep_cache_extended,
ep_cache_body.value     AS ep_cache_body,

                    e.id,
                    e.title,
                    e.timestamp,
                    e.comments,
                    e.exflag,
                    e.authorid,
                    e.trackbacks,
                    e.isdraft,
                    e.allow_comments,
                    e.last_modified,

                    a.realname AS author,
                    a.username AS loginname,
                    a.email
                     
                     
                FROM
                    atpeaz_entries AS e
                    LEFT JOIN atpeaz_authors a
                        ON e.authorid = a.authorid
                    LEFT JOIN atpeaz_entrycat ec
                        ON e.id = ec.entryid
                    LEFT JOIN atpeaz_category c
                        ON ec.categoryid = c.categoryid
                     LEFT OUTER JOIN atpeaz_entryproperties ep_no_frontpage
                                                  ON (e.id = ep_no_frontpage.entryid AND ep_no_frontpage.property = 'ep_no_frontpage')
 LEFT OUTER JOIN atpeaz_entryproperties ep_cache_extended
                                                  ON (e.id = ep_cache_extended.entryid AND ep_cache_extended.property = 'ep_cache_extended')
 LEFT OUTER JOIN atpeaz_entryproperties ep_cache_body
                                                  ON (e.id = ep_cache_body.entryid AND ep_cache_body.property = 'ep_cache_body')
 LEFT OUTER JOIN atpeaz_entryproperties ep_access
                                              ON (e.id = ep_access.entryid AND ep_access.property = 'ep_access') LEFT JOIN atpeaz_authorgroups AS acl_a
                                   ON acl_a.authorid = 0
                            LEFT JOIN atpeaz_access AS acl_acc
                                   ON (    acl_acc.artifact_mode = 'read'
                                       AND acl_acc.artifact_type = 'category'
                                       AND acl_acc.artifact_id   = c.categoryid
                                      )
                    WHERE isdraft = 'false' AND e.timestamp <= 1212173400 AND (e.id NOT IN (SELECT e.id FROM serendipity_entries AS e
                            LEFT JOIN serendipity_entrycat AS ec ON ec.entryid = e.id
                            JOIN serendipity_categorytemplates AS t ON ec.categoryid = t.categoryid AND hide_rss = 1))  AND  (ep_access.property IS NULL OR ep_access.value = 'public') AND  (ep_no_frontpage.property IS NULL OR ep_no_frontpage.value != 'true')   AND     (
                                 c.categoryid IS NULL
                                 OR ( acl_acc.groupid = 0)
                                 OR ( acl_acc.artifact_id IS NULL
                                     
                                    )
                               )
                     GROUP BY e.id
            ORDER BY orderkey DESC, last_modified DESC
                      LIMIT 1</pre> / Table 'atpeaz.serendipity_entries' doesn't exist

peaz
Regular
Posts: 18
Joined: Tue Apr 18, 2006 12:26 pm
Contact:

Post by peaz » Sat May 31, 2008 5:01 am

btw, i'm already on the latest version 1.3.1. I've tried to find high and low for the culprit query but can't seem to find it anywhere in function_entries.inc.php nor can I seem to find any clue from rss.php. Man. this is really weird.

User avatar
garvinhicking
Core Developer
Posts: 30020
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Post by garvinhicking » Sat May 31, 2008 11:35 am

Hi!

Thanks for reporting this, this is a bug in the serendipity_event_categorytemplates plugin. It used a fixed database prefix of "serendipity_", but you use your own one. That's perfectly fine, and usually plugins honor this, but in this case it had been forgotten.

I fixed that for version 0.28 of this plugin, which should be available on spartacus soon. If you want to fix it yourself, here's a diff:

http://php-blog.cvs.sourceforge.net/php ... 25&r2=1.26

Best regards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/

peaz
Regular
Posts: 18
Joined: Tue Apr 18, 2006 12:26 pm
Contact:

Post by peaz » Sat May 31, 2008 6:59 pm

Thanks! Hmm no wonder i couldn't find the file as I search for all the files in the 1.3.1 release locally while my blog online had the plugin installed. :D haha.

But BTW i think it should be just

{$serendipity['dbPrefix']}entries instead of {$serendipity['dbPrefix']}_entries since the prefix settings usually includes the _ already right?

Also line 720, serendipity_categorytemplates needs to be replaced too right?

Code: Select all

                        $conds[] = ("(e.id NOT IN (SELECT e.id FROM {$serendipity['dbPrefix']}entries AS e
                            LEFT JOIN {$serendipity['dbPrefix']}entrycat AS ec ON ec.entryid = e.id
                            JOIN {$serendipity['dbPrefix']}categorytemplates AS t ON ec.categoryid = t.categoryid AND hide_rss = 1))");


Got mine working already :D

User avatar
garvinhicking
Core Developer
Posts: 30020
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Post by garvinhicking » Sun Jun 01, 2008 1:38 pm

Hi!

D'oh. Of course you're right. I just fixed it properly, thanks!

REgards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/

User avatar
Don Chambers
Regular
Posts: 3638
Joined: Mon Feb 13, 2006 2:40 am
Location: Chicago, IL, USA
Contact:

Post by Don Chambers » Mon Jun 02, 2008 5:02 pm

Hmmm... I too had a problem with the plugin and had it temporarily disabled. Just updated to the latest version and still have this error:

Code: Select all

Query failed: <pre>SELECT 
                    e.isdraft AS orderkey,
ep_cache_extended.value AS ep_cache_extended,
ep_cache_body.value     AS ep_cache_body,

                    e.id,
                    e.title,
                    e.timestamp,
                    e.comments,
                    e.exflag,
                    e.authorid,
                    e.trackbacks,
                    e.isdraft,
                    e.allow_comments,
                    e.last_modified,

                    a.realname AS author,
                    a.username AS loginname,
                    a.email
                     
                     
                FROM
                    serendipity_entries AS e
                    LEFT JOIN serendipity_authors a
                        ON e.authorid = a.authorid
                    LEFT JOIN serendipity_entrycat ec
                        ON e.id = ec.entryid
                    LEFT JOIN serendipity_category c
                        ON ec.categoryid = c.categoryid
                     LEFT OUTER JOIN serendipity_entryproperties ep_no_frontpage
                                                  ON (e.id = ep_no_frontpage.entryid AND ep_no_frontpage.property = 'ep_no_frontpage')
 LEFT OUTER JOIN serendipity_entryproperties ep_cache_extended
                                                  ON (e.id = ep_cache_extended.entryid AND ep_cache_extended.property = 'ep_cache_extended')
 LEFT OUTER JOIN serendipity_entryproperties ep_cache_body
                                                  ON (e.id = ep_cache_body.entryid AND ep_cache_body.property = 'ep_cache_body')
 LEFT OUTER JOIN serendipity_entryproperties ep_access
                                              ON (e.id = ep_access.entryid AND ep_access.property = 'ep_access') LEFT JOIN serendipity_authorgroups AS acl_a
                                   ON acl_a.authorid = 1
                            LEFT JOIN serendipity_access AS acl_acc
                                   ON (    acl_acc.artifact_mode = 'read'
                                       AND acl_acc.artifact_type = 'category'
                                       AND acl_acc.artifact_id   = c.categoryid
                                      )
                    WHERE isdraft = 'false' AND e.timestamp &lt;= 1212417900 AND (e.id NOT IN (SELECT e.id FROM serendipity_entries AS e
                            LEFT JOIN serendipity_entrycat AS ec ON ec.entryid = e.id
                            JOIN serendipity_categorytemplates AS t ON ec.categoryid = t.categoryid AND hide_rss = 1))  AND  (ep_access.property IS NULL OR ep_access.value = 'member' OR ep_access.value = 'public' OR (ep_access.value = 'private' AND e.authorid = 1))  AND  (ep_no_frontpage.property IS NULL OR ep_no_frontpage.value != 'true')   AND     (
                                 c.categoryid IS NULL
                                 OR ( acl_acc.groupid = acl_a.groupid OR acl_acc.groupid = 0)
                                 OR ( acl_acc.artifact_id IS NULL
                                     
                                    )
                               )
                     GROUP BY e.id
            ORDER BY orderkey DESC, last_modified DESC
                      LIMIT 1</pre> / You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT e.id FROM serendipity_entries AS e


Could it be the mySQL version (4.0.24)?

User avatar
garvinhicking
Core Developer
Posts: 30020
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Post by garvinhicking » Mon Jun 02, 2008 5:14 pm

Hi!

It could be; Judebert modified that SQL to use SQL subqueries. I think to remember that MySQL only supports that since 4.1+

Regards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/

User avatar
Don Chambers
Regular
Posts: 3638
Joined: Mon Feb 13, 2006 2:40 am
Location: Chicago, IL, USA
Contact:

Post by Don Chambers » Mon Jun 02, 2008 5:25 pm

I think so too - there is no problem on another server running 5.0.45. Time to upgrade!!!

User avatar
judebert
Regular
Posts: 2478
Joined: Sat Oct 15, 2005 6:57 am
Location: Orlando, FL
Contact:

Post by judebert » Mon Jun 02, 2008 8:50 pm

Garvin, thanks for taking care of the plugin. I'm completely overwhelmed, especially with the end of the school year approaching for my three kids.

Yes, I just checked the MySQL site, and subqueries were first supported in 4.1. Your 4.0.24 is too ancient to run the category template plugin.
Judebert
---
Website | Wishlist | PayPal

frontier psychiatrist
Regular
Posts: 10
Joined: Wed Jun 25, 2008 6:55 pm
Location: Wuppertal
Contact:

RSS Error

Post by frontier psychiatrist » Wed Jun 25, 2008 11:04 pm

Hello all,

first - thanks for the excellent sw. I'm running three sites for more than a year now, without having any trouble so far.

Yesterday I updated my plugins and fiddled around with alternative styles. Since then, for example on

http://www.drproll.de/index.php?/feeds/categories/13-time-off.rss

I get this cryptic message:

Code: Select all

XML-Verarbeitungsfehler: Syntax-Fehler
Adresse: http://www.drproll.de/index.php?/feeds/categories/13-time-off.rss
Zeile Nr. 1, Spalte 1:Query failed: <pre>SELECT
^


Trying to import the feed from another site leads to this:

Code: Select all

Error on line 149 of /bundled-libs/Onyx/RSS.php: File has an XML error (syntax error at line 1).


I upgraded to 1.3.1, but this didn't help. The other sites running on 1.2.1 - no problem.

Any ideas?

User avatar
garvinhicking
Core Developer
Posts: 30020
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Re: RSS Error

Post by garvinhicking » Thu Jun 26, 2008 10:28 am

Hi!

If you view your RSS feed code you will see htis full error:

Code: Select all

Query failed: <pre>SELECT 
                    e.isdraft AS orderkey,

                    e.id,
                    e.title,
                    e.timestamp,
                    e.comments,
                    e.exflag,
                    e.authorid,
                    e.trackbacks,
                    e.isdraft,
                    e.allow_comments,
                    e.last_modified,

                    a.realname AS author,
                    a.username AS loginname,
                    a.email
                     
                     
                FROM
                    serendipity_praxis_entries AS e
                    LEFT JOIN serendipity_praxis_authors a
                        ON e.authorid = a.authorid
                    LEFT JOIN serendipity_praxis_entrycat ec
                        ON e.id = ec.entryid
                    LEFT JOIN serendipity_praxis_category c
                        ON ec.categoryid = c.categoryid
                     LEFT OUTER JOIN serendipity_praxis_entryproperties ep_access
                                              ON (e.id = ep_access.entryid AND ep_access.property = 'ep_access')
                    WHERE isdraft = 'false' AND (( (c.category_left  BETWEEN 15 AND 16))) AND e.timestamp &lt;= 1214466900 AND (e.id NOT IN (SELECT e.id FROM serendipity_praxis_entries AS e
                            LEFT JOIN serendipity_praxis_entrycat AS ec ON ec.entryid = e.id
                            JOIN serendipity_categorytemplates AS t ON ec.categoryid = t.categoryid AND hide_rss = 1))  AND  (ep_access.property IS NULL OR ep_access.value = 'public')
                     GROUP BY e.id
            ORDER BY orderkey DESC, last_modified DESC
                      LIMIT 1</pre> / Table 'db58546_1.serendipity_categorytemplates' doesn't exist


It complains about a missing "serendipity_categorytemplates" DB table. Did you maybe delete this one?

Regards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/

frontier psychiatrist
Regular
Posts: 10
Joined: Wed Jun 25, 2008 6:55 pm
Location: Wuppertal
Contact:

Re: RSS Error

Post by frontier psychiatrist » Thu Jun 26, 2008 11:47 am

garvinhicking wrote:It complains about a missing "serendipity_categorytemplates" DB table. Did you maybe delete this one?

Regards,
Garvin


Hi Garvin,

actually I didn't. Good point looking into the source of the truncated error message: my DB tables come with the prefix serendipity_praxis_ , and the according table is still there.

I deactivated the categorytemplates plugin, and the feed is online again. Maybe while trying different templates somehow I messed it up. I'll stick to bulletproof, then. :D

Tanks a lot and see you next time.

Regards,
Ewald

User avatar
garvinhicking
Core Developer
Posts: 30020
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Re: RSS Error

Post by garvinhicking » Thu Jun 26, 2008 12:59 pm

Hi!

I believe the categorytemplates plugin had a fixed DB prefix in an earlier version. Maybe you can updated to the most recent one?

Regards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/

frontier psychiatrist
Regular
Posts: 10
Joined: Wed Jun 25, 2008 6:55 pm
Location: Wuppertal
Contact:

Re: RSS Error

Post by frontier psychiatrist » Thu Jun 26, 2008 7:31 pm

Hi Garvin,

the plugin is not vital for my pages at the time being, so I guess I'll leave it alone for a while. Thanks again and have a nice day!

Ewald

Freudi
Regular
Posts: 97
Joined: Thu Nov 23, 2006 6:29 am
Contact:

Post by Freudi » Mon Jul 21, 2008 6:11 pm

There's still a bug present in version 1.27 of the categorytemplates plugin, effecting users with non standard database prefixes:
Line 720 reeds

Code: Select all

JOIN serendipity_categorytemplates AS t ON ec.categoryid = t.categoryid AND hide_rss = 1))");

while it should reed

Code: Select all

JOIN {$serendipity['dbPrefix']}categorytemplates AS t ON ec.categoryid = t.categoryid AND hide_rss = 1))");

to fix the bug.

Bye,
Freudi

Post Reply