MySQL syntax error reported with ccache_cleanup() code

Development-related discussion, including bundled plugins
meyercr
Bear Rating Trainee
Bear Rating Trainee
Posts: 9
Joined: 30 Jul 2011, 03:03

MySQL syntax error reported with ccache_cleanup() code

Postby meyercr » 10 Aug 2011, 18:56

Hi Andrew,

After applying this git patch: 3261ca582066ea7f88e1

I get a Syntax error reported back from MySQL from ccache_cleanup():

"Query DELETE FROM ttrss_counters_cache AS c1 WHERE (SELECT count(*) FROM ttrss_counters_cache AS c2 WHERE c1.feed_id = c2.feed_id AND c2.owner_uid = c1.owner_uid) > 1 AND owner_uid = '1' failed: 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 'WHERE (SELECT count(*) FROM ttrss_counters_cache AS c2 WHERE c1.feed_id =' at line 1"

I haven't yet debugged it, just wanted to let you know.

Craig

lippoliv
Bear Rating Trainee
Bear Rating Trainee
Posts: 3
Joined: 11 Nov 2010, 11:08

Re: MySQL syntax error reported with ccache_cleanup() code

Postby lippoliv » 16 Aug 2011, 19:57

Me to... got the same issue on rss.mixedhost.de

Please can you give some support, would like to host your app for free while evaluation-phase to some users :)

meyercr
Bear Rating Trainee
Bear Rating Trainee
Posts: 9
Joined: 30 Jul 2011, 03:03

Re: MySQL syntax error reported with ccache_cleanup() code

Postby meyercr » 17 Aug 2011, 06:31

I looked at the query again file: functions.php at function ccache_cleanup($link, $owner_uid)

It seems to be deleting duplicate rows from ttrss_counters_cache (WHERE feed_id, owner_uid, are duplicated)

After reading this (search down for "-Deleting Duplicate Entries -")
The suggested code is:

Code: Select all

                db_query($link, "DELETE
                                     t1
                                 FROM
                                     ttrss_counters_cache t1,
                                     ttrss_counters_cache t2
                                 WHERE
                                     t1.owner_uid = $owner_uid AND
                                     t1.owner_uid = t2.owner_uid AND
                                     t1.feed_id   = t2.feed_id ");


From my tests, it seems to delete all the duplicated rows. It does not leave one of the duplicates (as the comment suggests).

The original code has the query duplicated, not sure if it needs to run twice???

The other solution is to comment out the only call to ccache_cleanup() in functions.php. ;)

User avatar
joshp
Bear Rating Disaster
Bear Rating Disaster
Posts: 50
Joined: 31 Mar 2011, 11:31

MySQL Error after pulling from git

Postby joshp » 18 Aug 2011, 19:35

The log in screen loads, but after log in all I get is a white page displaying this notice


Query DELETE FROM ttrss_counters_cache AS c1 WHERE (SELECT count(*) FROM ttrss_counters_cache AS c2 WHERE c1.feed_id = c2.feed_id AND c2.owner_uid = c1.owner_uid) > 1 AND owner_uid = '2' failed: 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 'AS c1 WHERE (SELECT count(*) FROM ttrss_counters_cache AS c2 WHERE c1.fee' at line 1


Totally at a loss

User avatar
joshp
Bear Rating Disaster
Bear Rating Disaster
Posts: 50
Joined: 31 Mar 2011, 11:31

Re: MySQL Error after pulling from git

Postby joshp » 19 Aug 2011, 09:24

found the issue here

viewtopic.php?f=10&t=863

and trying to remove the duplication of the request, if that fails I will try the suggested code in the comment here

viewtopic.php?f=10&t=863#p3900

User avatar
joshp
Bear Rating Disaster
Bear Rating Disaster
Posts: 50
Joined: 31 Mar 2011, 11:31

Re: MySQL Error after pulling from git

Postby joshp » 19 Aug 2011, 10:56

this fixed it

Code: Select all

function ccache_cleanup($link, $owner_uid) {

      //db_query($link, "DELETE FROM ttrss_counters_cache AS c1 WHERE
      //   (SELECT count(*) FROM ttrss_counters_cache AS c2
      //      WHERE c1.feed_id = c2.feed_id AND c2.owner_uid = c1.owner_uid) > 1
      //      AND owner_uid = '$owner_uid'");

      //db_query($link, "DELETE FROM ttrss_cat_counters_cache AS c1 WHERE
      //   (SELECT count(*) FROM ttrss_cat_counters_cache AS c2
      //      WHERE c1.feed_id = c2.feed_id AND c2.owner_uid = c1.owner_uid) > 1
      //   AND owner_uid = '$owner_uid'");
                    db_query($link, "DELETE
                                         t1
                                     FROM
                                         ttrss_counters_cache t1,
                                         ttrss_counters_cache t2
                                     WHERE
                                         t1.owner_uid = $owner_uid AND
                                         t1.owner_uid = t2.owner_uid AND
                                         t1.feed_id   = t2.feed_id ");
   }

User avatar
joshp
Bear Rating Disaster
Bear Rating Disaster
Posts: 50
Joined: 31 Mar 2011, 11:31

Re: MySQL syntax error reported with ccache_cleanup() code

Postby joshp » 19 Aug 2011, 10:57

I also had the same problem, your change to functions.php seems to have solved the problem for me.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: MySQL Error after pulling from git

Postby fox » 21 Aug 2011, 16:26

Ugh. That looks cleaner but is apparently incompatible with postgresql.

Edit: thanks for the fix, though. It seems to work. :)

https://github.com/gothfox/Tiny-Tiny-RS ... 89410aed51


Return to “Development”

Who is online

Users browsing this forum: No registered users and 2 guests