bbPress support forums Tag: SQL

bbPress support forums Tag: SQL http://bbpress.org/forums/ bbPress support forums Tag: SQL en Tue, 02 Dec 2008 22:34:11 +0000 sambauers on "Slow Queries - 300 second query times" http://bbpress.org/forums/topic/slow-queries-300-second-query-times#post-19607 Thu, 02 Oct 2008 22:52:08 +0000 sambauers 19607@http://bbpress.org/forums/ I think that query is generated by the forum-last-poster plugin. If it is the one that is showing up over and over again, then get rid of it. chrishajer on "Slow Queries - 300 second query times" http://bbpress.org/forums/topic/slow-queries-300-second-query-times#post-19597 Thu, 02 Oct 2008 01:41:10 +0000 chrishajer 19597@http://bbpress.org/forums/ There have been some relevant discussions here: <a href="http://bbpress.org/forums/topic/bbpress-0901-gt-30-queries-per-page" rel="nofollow">http://bbpress.org/forums/topic/bbpress-0901-gt-30-queries-per-page</a> <a href="http://bbpress.org/forums/topic/server-overload" rel="nofollow">http://bbpress.org/forums/topic/server-overload</a> <a href="http://bbpress.org/forums/topic/fulltext-searching-very-slow" rel="nofollow">http://bbpress.org/forums/topic/fulltext-searching-very-slow</a> <a href="http://bbpress.org/forums/topic/how-to-evaluate-server-speed" rel="nofollow">http://bbpress.org/forums/topic/how-to-evaluate-server-speed</a> Looking at the front page of your forum, I see a total of 3074 threads and 22,121 posts. Is there more than meets the eye here to come up with 2.5 million rows of data? Or maybe there's not 2.5 millions rows in bb_topics, it's just because of the subquery that it looks at that many records (like it's something I don't understand about the way MySQL displays the number or rows searched)? Did the amount of data grow exponentially after the conversion from xdforums? Maybe there were 20K rows in xdforums, but something with the conversion didn't go well and it duplicated a bunch of data? How many rows were there in comparable tables before and after conversion? Something doesn't add up. Also, are all those queries the same, and if so, is that query just trying to determine the last poster, from the "Forum Last Poster" plugin? If so, have you tried just turning that plugin off? If that works, then maybe just optimizing the query for your installation would work and you could turn it back on after modifying the plugin. Of all the plugins, the only other one I could see affecting the queries like that would be the Post Count Plus. That would be the second one I turn off and check for a performance difference. clicknathan on "Slow Queries - 300 second query times" http://bbpress.org/forums/topic/slow-queries-300-second-query-times#post-19594 Wed, 01 Oct 2008 21:33:50 +0000 clicknathan 19594@http://bbpress.org/forums/ Hello, I've recently set up a BBPress forum for non-profit Bike Pittsburgh and their host, Pair Networks, shut down their database because of the load BBPress was putting on the server. Pair has since reinstated the db, but I was wondering if anyone could offer any insight as to how I might trim down the size of the queries that BBPress is putting through. A little background: <ol> <li>I used <a href="http://fastpipe.com/2008/06/23/converting-xdforum-to-bbpress">this method</a> to convert a WordPress XDForum into BBPress. I know the author of that post mentions that the method he uses might not be the most efficient, however at this point (after the conversion), I wouldn't think that BBPress is using any of this info any more, correct?</li> <li>We're running BBPress v. 0.9.0.2</li> <li>We're using the following plugins: Akismet, Use Display Name, BBPress Private Messaging, Post Count Plus, Forum Last Poster, and Images Allowed</li> </ol> The information from the host was this: <blockquote> With that said, we wanted to provide a follow up in terms of the nature of the problem itself. The problem is that the database is running some very inefficient queries, trying to sort through 2.5 million rows and are returning very few to no results. These queries take a long time to run and are called "slow queries". As you gain in traffic, the more of these slow queries that are run, the higher the load on the server, and therefore the more affect you have on other customers. I've included some examples of these queries below for your review: # Time: 080930 20:55:01 # <a href="mailto:User@Host:">User@Host:</a> bikepgh[bikepgh] @ [209.68.4.180] # Query_time: 54 Lock_time: 0 Rows_sent: 10 Rows_examined: 2469001 SELECT forum_id, topic_id FROM bb_topics WHERE topic_last_post_id IN (SELECT MAX (topic_last_post_id) FROM bb_topics WHERE topic_status=0 GROUP BY forum_id); # Time: 080930 20:56:05 # <a href="mailto:User@Host:">User@Host:</a> bikepgh[bikepgh] @ [209.68.4.180] # Query_time: 71 Lock_time: 0 Rows_sent: 10 Rows_examined: 2469001 SELECT forum_id, topic_id FROM bb_topics WHERE topic_last_post_id IN (SELECT MAX (topic_last_post_id) FROM bb_topics WHERE topic_status=0 GROUP BY forum_id); # Time: 080930 20:56:43 # <a href="mailto:User@Host:">User@Host:</a> bikepgh[bikepgh] @ [209.68.4.180] # Query_time: 67 Lock_time: 0 Rows_sent: 10 Rows_examined: 2469001 SELECT forum_id, topic_id FROM bb_topics WHERE topic_last_post_id IN (SELECT MAX (topic_last_post_id) FROM bb_topics WHERE topic_status=0 GROUP BY forum_id); # Time: 080930 20:57:38 # <a href="mailto:User@Host:">User@Host:</a> bikepgh[bikepgh] @ [209.68.4.180] # Query_time: 52 Lock_time: 0 Rows_sent: 10 Rows_examined: 2469001 SELECT forum_id, topic_id FROM bb_topics WHERE topic_last_post_id IN (SELECT MAX (topic_last_post_id) FROM bb_topics WHERE topic_status=0 GROUP BY forum_id); # Time: 080930 21:20:46 # <a href="mailto:User@Host:">User@Host:</a> bikepgh[bikepgh] @ [209.68.4.180] # Query_time: 263 Lock_time: 0 Rows_sent: 10 Rows_examined: 2469001 SELECT forum_id, topic_id FROM bb_topics WHERE topic_last_post_id IN (SELECT MAX (topic_last_post_id) FROM bb_topics WHERE topic_status=0 GROUP BY forum_id); # Time: 080930 21:22:35 # <a href="mailto:User@Host:">User@Host:</a> bikepgh[bikepgh] @ [209.68.4.180] # Query_time: 281 Lock_time: 0 Rows_sent: 0 Rows_examined: 1952752 SELECT forum_id, topic_id FROM bb_topics WHERE topic_last_post_id IN (SELECT MAX (topic_last_post_id) FROM bb_topics WHERE topic_status=0 GROUP BY forum_id); As you can see, these queries are taking almost 300 seconds to complete (query_time). This is an extremely high time, as a query on a shared server should take under 10 seconds to complete. I hope this helps provide some needed details for you. If we can be of any further assistance, please do not hesitate to ask.</blockquote> Any help as to what I might do in PHPMyAdmin or otherwise to help lower the load on the db would be VERY MUCH appreciated! holloway on "Quick SQL question" http://bbpress.org/forums/topic/quick-sql-question#post-18378 Sun, 17 Aug 2008 19:45:36 +0000 holloway 18378@http://bbpress.org/forums/ Nevermind, should someone encounter the same issue just run the query before you reinstall: ALTER wp_users DROP user_activation_key holloway on "Quick SQL question" http://bbpress.org/forums/topic/quick-sql-question#post-18377 Sun, 17 Aug 2008 19:13:35 +0000 holloway 18377@http://bbpress.org/forums/ I'm running Alpha 1.0 and recently had to ask for support. I get the feeling I did something wrong before I did the solution as I now have an issue where I APPEAR to have permission to access admin panel and to post, but clicking on the links just sends me to the board index. I've reinstalled the BBPress files and deleted the 'bb_' tables in the sql however this issue remains in this new installation. I'm integrated with WP, therefore I was wondering if someone could list the rows added to the Wordpress sql by the bbpress integration process so that I could try removing them. _ck_ on "bb_register_view - from SQL to array" http://bbpress.org/forums/topic/bb_register_view-from-sql-to-array#post-17583 Wed, 23 Jul 2008 16:15:49 +0000 _ck_ 17583@http://bbpress.org/forums/ I've never figured out how to do direct query setup for views, so what I do is just short-circuit the internal function and write my own. For example: <pre><code>bb_register_view("highest-rated","Topics with the highest rating",array('append_meta'=>false,'sticky'=>false)); add_action( 'bb_custom_view', 'highest_rated' ); function highest_rated( $view ) { if ($view=='highest-rated') { global $bbdb, $topics, $view_count, $page; $limit = bb_get_option('page_topics'); $offset = ($page-1)*$limit; $where = apply_filters('get_latest_topics_where',"WHERE topic_status=0 "); $query = " FROM $bbdb->topics $where "; $restrict = " ORDER BY cast(topic_posts as UNSIGNED) DESC LIMIT $limit OFFSET $offset"; $view_count = $bbdb->get_var("SELECT count(*) ".$query); $topics = $bbdb->get_results("SELECT * ".$query.$restrict); $topics = bb_append_meta( $topics, 'topic' ); } }</code></pre> . Obviously change the query to your own and that might work for you. Don't forget to use <code>$bbdb->bb_ratings</code> the $bbdb part is critical. poioioing on "bb_register_view - from SQL to array" http://bbpress.org/forums/topic/bb_register_view-from-sql-to-array#post-17568 Wed, 23 Jul 2008 13:33:40 +0000 poioioing 17568@http://bbpress.org/forums/ I work with bbPress 0.9. Been trying for hours to get this SQL into a view? How should the array for bb_register_view() look like? SELECT bbt.*, (SELECT COUNT(*) FROM bb_ratings WHERE topic_id=bbt.topic_id) AS counts FROM bb_topics bbt, bb_ratings bbr WHERE bbt.forum_id = 2 AND bbt.topic_status = 0 GROUP BY bbt.topic_id ORDER BY counts DESC bramruiter on "bbPress Database Error" http://bbpress.org/forums/topic/bbpress-database-error-1#post-16797 Wed, 18 Jun 2008 12:15:17 +0000 bramruiter 16797@http://bbpress.org/forums/ It indeed was aMySQL issue. It's been solved now. bramruiter on "bbPress Database Error" http://bbpress.org/forums/topic/bbpress-database-error-1#post-16794 Wed, 18 Jun 2008 10:12:49 +0000 bramruiter 16794@http://bbpress.org/forums/ Ah, I thought it was something like that. I've just ordered a new hosting, which works on MySQL 5. Thanks for the help. chrishajer on "bbPress Database Error" http://bbpress.org/forums/topic/bbpress-database-error-1#post-16762 Sat, 14 Jun 2008 18:13:48 +0000 chrishajer 16762@http://bbpress.org/forums/ bbPress requires MySQL 4.0 minimum. I think 3.23 is too low: <a href="http://bbpress.org/about/requirements/" rel="nofollow">http://bbpress.org/about/requirements/</a> bramruiter on "bbPress Database Error" http://bbpress.org/forums/topic/bbpress-database-error-1#post-16759 Sat, 14 Jun 2008 10:13:23 +0000 bramruiter 16759@http://bbpress.org/forums/ Sorry, I don't check this forum daily, so here's a late answer: 1. From what I know, it has always been like this. It's only when I perform a search. 2. The version is bbPress 0.9.0.2 and it was installed somewhere between june 26 and june 31. 3. I didn't. I only screwed around with the template files. 4. No, they did no update or change whatsoever. 5. Like I said, it was there before the plug-ins, but here's the list. Private Forums, BBPress Private Messaging (not working), Post Count Plus - Dynamic.Titles & More!, Akismet, Avatar, Last-Post, Limit Latest Discussion, Bozo Users, BBcode Lite, Move It, Quote, Simple Onlinelist, Allow Images, Human Test for bbPress & Front Page Topics. 6. The second error appears only when someone perform a search. The first error is only noted in the admin area, in the first tab, beneath 'User Registrations'. 7. Yes. It does happen. The MySQL version is 3.23.58. I'm not a big guy when it comes to coding/scripting, so I have no idea what these errors mean. I'm glad somebody wants to help. bobbyh on "bbPress Database Error" http://bbpress.org/forums/topic/bbpress-database-error-1#post-16671 Mon, 09 Jun 2008 19:32:12 +0000 bobbyh 16671@http://bbpress.org/forums/ Check your mysql version number. Is it really low? chrishajer on "bbPress Database Error" http://bbpress.org/forums/topic/bbpress-database-error-1#post-16667 Mon, 09 Jun 2008 15:52:37 +0000 chrishajer 16667@http://bbpress.org/forums/ Interestingly, I copied and pasted your SQL query from above, and used it on my installation unchanged, and it did not error out, it just return the expected results. <code>SELECT t.*, MIN(p.post_id) as post_id, p.post_text, 0 AS search_score FROM bb_topics AS t JOIN bb_posts as p ON ( t.topic_id = p.topic_id ) WHERE t.topic_status = '0' AND p.post_status = '0' AND ( (t.topic_title LIKE '%ja%') OR p.post_text LIKE '%ja%' ) GROUP BY t.topic_id ORDER BY t.topic_time DESC LIMIT 20</code> See the results <a href="http://www.chrishajer.com/bbpress/sqlerror.png" title="no SQL error here">here</a>. This is an old bbPress installation as well, 1.0-alpha from over a year ago. chrishajer on "bbPress Database Error" http://bbpress.org/forums/topic/bbpress-database-error-1#post-16666 Mon, 09 Jun 2008 15:25:17 +0000 chrishajer 16666@http://bbpress.org/forums/ 1. Has this installation always been like this, or did something change recently? 2. What version are you running and when was it installed? 3. Did you change hosting or make any other changes to the installation? 4. Did your host upgrade PHP or MySQL or your .htaccess file or something else that would have affected this? 5. What plugins are installed, and does disabling them help with the error? 6. Does the problem described in the first post happen only when searching, or is that from something else and you now have the problem described in the last post as well? 7. Does the problem happen when using the stock theme? You don't have a normal run-of-mill type problem that can be easily and simply solved, so people are going to need more info to help. bramruiter on "bbPress Database Error" http://bbpress.org/forums/topic/bbpress-database-error-1#post-16663 Mon, 09 Jun 2008 13:48:29 +0000 bramruiter 16663@http://bbpress.org/forums/ The problem got a bit bigger. When someone tries to find something through the search they get: bbPress database error: [You have an error in your SQL syntax near 'ON ( t.topic_id = p.topic_id ) WHERE p.post_text LIKE '%ja%' AND p.post_status =' at line 1] SELECT p.*, 0 AS search_score, MAX(post_time) AS post_time FROM bb_posts AS p JOIN bb_topics as t ON ( t.topic_id = p.topic_id ) WHERE p.post_text LIKE '%ja%' AND p.post_status = '0' AND t.topic_status = '0' GROUP BY t.topic_id ORDER BY p.post_time DESC LIMIT 5 bbPress database error: [You have an error in your SQL syntax near 'ON ( t.topic_id = p.topic_id ) WHERE t.topic_status = '0' AND p.post_status = '0' at line 1] SELECT t.*, MIN(p.post_id) as post_id, p.post_text, 0 AS search_score FROM bb_topics AS t JOIN bb_posts as p ON ( t.topic_id = p.topic_id ) WHERE t.topic_status = '0' AND p.post_status = '0' AND ( (t.topic_title LIKE '%ja%') OR p.post_text LIKE '%ja%' ) GROUP BY t.topic_id ORDER BY t.topic_time DESC LIMIT 20 chrishajer on "bbPress Database Error" http://bbpress.org/forums/topic/bbpress-database-error-1#post-16659 Sun, 08 Jun 2008 22:28:49 +0000 chrishajer 16659@http://bbpress.org/forums/ What do you have to do to get that error to show up? Installation, view profile, post a new topic, post a reply, add a tag, etc... bramruiter on "bbPress Database Error" http://bbpress.org/forums/topic/bbpress-database-error-1#post-16657 Sun, 08 Jun 2008 17:39:52 +0000 bramruiter 16657@http://bbpress.org/forums/ bbPress database error: [You have an error in your SQL syntax near 'ON ( t.topic_id = p.topic_id ) WHERE p.post_status != '0' AND t.topic_status = '' at line 1] SELECT p.* FROM bb_posts AS p JOIN bb_topics as t ON ( t.topic_id = p.topic_id ) WHERE p.post_status != '0' AND t.topic_status = '0' ORDER BY p.post_time DESC LIMIT 5 What does it mean? motaboy21 on "i cannot use "´" on my posts" http://bbpress.org/forums/topic/i-cannot-use-%c2%b4-on-my-posts#post-16612 Wed, 04 Jun 2008 20:57:00 +0000 motaboy21 16612@http://bbpress.org/forums/ well, when im writing a post and i write something like "it´s" i have an error of SQL (or Mysql) that says there was a problem with this character "´" try it hentaitube.org help please sambauers on "bbPress on a ms sql server" http://bbpress.org/forums/topic/bbpress-on-a-ms-sql-server#post-15762 Fri, 25 Apr 2008 15:20:15 +0000 sambauers 15762@http://bbpress.org/forums/ You would have to write a replacement for bb-includes/db.php to get bbPress to work with MSSQL. anjap on "bbPress on a ms sql server" http://bbpress.org/forums/topic/bbpress-on-a-ms-sql-server#post-15755 Fri, 25 Apr 2008 08:50:47 +0000 anjap 15755@http://bbpress.org/forums/ Has anyone tried to install bbpress on ms sql server ? I know mysql is required, but we already have a ms sql 2003 server up and runnig and would like to use bbpress on our intranet. Of cource we don't want to mess up the existing stuff, but on other hand we would like to avoid having both mssql and mysql runnig. So any experience, good advices or hints would be appreciated... fel64 on "How to get the id from the second row? (Mysql)" http://bbpress.org/forums/topic/how-to-get-the-id-from-the-second-row-mysql#post-11859 Thu, 08 Nov 2007 17:37:54 +0000 fel64 11859@http://bbpress.org/forums/ <pre><code>$data = $bbdb->query('stuff'); $what_you_want = $data[1]->column_name;</code></pre> If I recall correctly. <code>$data[1]</code> should be the second item (0-based arrays), which is an object. Access its property by the name of the column the ID is stored in. bbdb is based on ezSQL. Look for some documentation. It's actually pretty well documented for wpdb; did you not find anything there? Null on "How to get the id from the second row? (Mysql)" http://bbpress.org/forums/topic/how-to-get-the-id-from-the-second-row-mysql#post-11855 Thu, 08 Nov 2007 15:46:56 +0000 Null 11855@http://bbpress.org/forums/ I am at work so can't show the query :) The results are always different, but I need the id from the sec row no matter what the outcome is... So sometimes it's: Row 1 - id 23 - tekst Row 2 - id 12 - tekst Row 3 - id 44 - tekst etc Or sometimes it's: Row 1 - id 7863 - tekst Row 2 - id 56557 - tekst Row 3 - id 665 - tekst etc chrishajer on "How to get the id from the second row? (Mysql)" http://bbpress.org/forums/topic/how-to-get-the-id-from-the-second-row-mysql#post-11854 Thu, 08 Nov 2007 13:42:22 +0000 chrishajer 11854@http://bbpress.org/forums/ How are the results ordered now? How about showing the SQL? Null on "How to get the id from the second row? (Mysql)" http://bbpress.org/forums/topic/how-to-get-the-id-from-the-second-row-mysql#post-11851 Thu, 08 Nov 2007 12:51:24 +0000 Null 11851@http://bbpress.org/forums/ Hi, I have a query and it shows me 5 results Now, I always need the id from row 2 (result 2). How to get it? Greetz Xenuan on "proplem after installing" http://bbpress.org/forums/topic/proplem-after-installing#post-11358 Tue, 09 Oct 2007 22:57:50 +0000 Xenuan 11358@http://bbpress.org/forums/ ok, i did it (reason: different config file) thanks chrishajer on "proplem after installing" http://bbpress.org/forums/topic/proplem-after-installing#post-11356 Tue, 09 Oct 2007 20:31:05 +0000 chrishajer 11356@http://bbpress.org/forums/ Can you add this line to config.php: <code>$bb->debug = 1;</code> You can put it near the top after the database details. That should give you some useful information. Xenuan on "proplem after installing" http://bbpress.org/forums/topic/proplem-after-installing#post-11354 Tue, 09 Oct 2007 16:55:11 +0000 Xenuan 11354@http://bbpress.org/forums/ After installing the bbPress, i can't see my topics, forums, tags... (everything) i tried everything but i cant fixed :( <a href="http://www.bilgisinema.org/forum" rel="nofollow">http://www.bilgisinema.org/forum</a> chrishajer on "mysql.sock error ?" http://bbpress.org/forums/topic/mysqlsock-error-#post-11289 Fri, 05 Oct 2007 14:04:06 +0000 chrishajer 11289@http://bbpress.org/forums/ Sounds like your problem was discussed here: <a href="http://bbpress.org/forums/topic/db-access-problem-in-the-first-installation-step?replies=15" rel="nofollow">http://bbpress.org/forums/topic/db-access-problem-in-the-first-installation-step?replies=15</a> Check out the patch that is attached to this ticket: <a href="http://trac.bbpress.org/ticket/745" rel="nofollow">http://trac.bbpress.org/ticket/745</a> It worked for another user. Good luck. Herseycix on "mysql.sock error ?" http://bbpress.org/forums/topic/mysqlsock-error-#post-11287 Fri, 05 Oct 2007 13:53:44 +0000 Herseycix 11287@http://bbpress.org/forums/ Warning: mysql_get_server_info() [function.mysql-get-server-info]: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) in /vhosts/domain.com/http/1/bb-includes/db-mysqli.php on line 80 Warning: mysql_get_server_info() [function.mysql-get-server-info]: A link to the server could not be established in /vhosts/domain.com/http/1/bb-includes/db-mysqli.php on line 80 Warning: Cannot modify header information - headers already sent by (output started at /vhosts/domain.com/http/1/bb-includes/db-mysqli.php:80) in /vhosts/domain.com/http/1/bb-admin/install.php on line 10 Warning: Cannot modify header information - headers already sent by (output started at /vhosts/domain.com/http/1/bb-includes/db-mysqli.php:80) in /vhosts/domain.com/http/1/bb-includes/functions.php on line 1898 goebelmasse on "Wrong user count" http://bbpress.org/forums/topic/wrong-user-count#post-10315 Mon, 20 Aug 2007 14:22:35 +0000 goebelmasse 10315@http://bbpress.org/forums/ Oh, thank you SamBauers - this is the information missing on this site... But as I read at the trac site, the problem is already known. It is fixed by <a href="http://trac.bbpress.org/changeset/872/branches/0.8?old_path=%2F&format=zip">changeset 867</a> - at least in my test environment. I'll upload it to the real server, it seems to be okay and of course it will be better than my quick and dirty "solution"...