error 1104

This forum is now closed as part of retiring phpBB2.
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

This forum is now closed due to phpBB2.0 being retired.
Jezzapandd
Registered User
Posts: 71
Joined: Wed Oct 31, 2007 5:11 pm
Location: Bham, UK
Contact:

error 1104

Post by Jezzapandd » Sun Feb 24, 2008 10:13 am

This topic was moved from support, and the stuff you want to know is here:
http://www.phpbb.com/community/viewtopi ... 5#p4571245

Could not obtain topic information

DEBUG MODE

SQL Error : 1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time FROM phpbb2_topics t, users u, phpbb2_posts p, phpbb2_posts p2, users u2 WHERE t.forum_id = 24 AND t.topic_poster = u.user_id AND p.post_id = t.topic_first_post_id AND p2.post_id = t.topic_last_post_id AND u2.user_id = p2.poster_id AND t.topic_type <> 2 ORDER BY t.topic_type DESC, t.topic_last_post_id DESC LIMIT 0, 15

Line : 328
File : viewforum.php
the relevant code:

Code: Select all

$sql = "SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time 
	FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TABLE . " p2, " . USERS_TABLE . " u2
	WHERE t.forum_id = $forum_id
		AND t.topic_poster = u.user_id
		AND p.post_id = t.topic_first_post_id
		AND p2.post_id = t.topic_last_post_id
		AND u2.user_id = p2.poster_id 
		AND t.topic_type <> " . POST_ANNOUNCE . " 
		$limit_topics_time
	ORDER BY t.topic_type DESC, t.topic_last_post_id DESC 
	LIMIT $start, ".$board_config['topics_per_page'];
if ( !($result = $db->sql_query($sql)) )
{
   message_die(GENERAL_ERROR, 'Could not obtain topic information', '', __LINE__, __FILE__, $sql);
}
please help. i have not changed this code, so i think it might be something to do with the datatbase
Last edited by Jezzapandd on Tue Mar 04, 2008 7:22 am, edited 1 time in total.

User avatar
karlsemple
Former Team Member
Posts: 39802
Joined: Mon Nov 01, 2004 8:54 am
Location: Hereford, UK
Contact:

Re: error 1104

Post by karlsemple » Sun Feb 24, 2008 10:19 am

You are hitting a MySql limit set in its configuration, this is nothing to do with phpBB, you will need to contact your host about this and get them to raise this limit for you :)
Image

Jezzapandd
Registered User
Posts: 71
Joined: Wed Oct 31, 2007 5:11 pm
Location: Bham, UK
Contact:

Re: error 1104

Post by Jezzapandd » Sun Feb 24, 2008 2:01 pm

can you explain a bit more? i have other forums on the same server that work fine.

User avatar
karlsemple
Former Team Member
Posts: 39802
Joined: Mon Nov 01, 2004 8:54 am
Location: Hereford, UK
Contact:

Re: error 1104

Post by karlsemple » Sun Feb 24, 2008 2:05 pm

Jezzapandd wrote:can you explain a bit more? i have other forums on the same server that work fine.

Some of the queries use joins to pull information from multiple tables, Mysql has a configuration setting which limits the amount of rows which can be joined (pulled from multiple table using a common column id) in a query. You are hitting this limit on the board in question and thus the error. As I mentioned this is something you need to take up with the host as it is them who will control this setting :)
Image

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: error 1104

Post by espicom » Sun Feb 24, 2008 3:41 pm

Note that sometimes, this error can be generated because an index is not correct, and MySQL is fooled into thinking a query will be "too big". If you have phpmyadmin, spend a few minutes running repairs on your tables, and see if it eliminates the problem. I have only seen this a couple of times myself, but it is something you can try before trying to wake the host's tech support people from their slumber... :wink:
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

Jezzapandd
Registered User
Posts: 71
Joined: Wed Oct 31, 2007 5:11 pm
Location: Bham, UK
Contact:

Re: error 1104

Post by Jezzapandd » Sun Feb 24, 2008 7:12 pm

actually, I use bluehost and they're very good. not on sundays, though, it would appear. I will try repairing the relevant tables

EDIT: i ran a repair and an optimize on all the tables and still the same problemns

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: error 1104

Post by espicom » Mon Feb 25, 2008 3:40 am

If you run the query SHOW VARIABLES LIKE 'max%' in phpmyadmin, what does it show for the max_join_size? This is server-dependent, and normally defaults to around 2GB for a 32-bit server (my 64-bit test server reports the limit as 18,446,744,073,709,551,615 bytes). It can be a LOT smaller (I've seen hosts set it as low as 64K).
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

Jezzapandd
Registered User
Posts: 71
Joined: Wed Oct 31, 2007 5:11 pm
Location: Bham, UK
Contact:

Re: error 1104

Post by Jezzapandd » Mon Feb 25, 2008 7:02 am

max_allowed_packet 33553408
max_binlog_cache_size 18446744073709551615
max_binlog_size 1073741824
max_connect_errors 10
max_connections 600
max_delayed_threads 20
max_error_count 64
max_heap_table_size 268435456
max_insert_delayed_threads 20
max_join_size 100000000
max_length_for_sort_data 1024
max_prepared_stmt_count 16382
max_relay_log_size 0
max_seeks_for_key 18446744073709551615
max_sort_length 1024
max_sp_recursion_depth 0
max_tmp_tables 1024
max_user_connections 0
max_write_lock_count 18446744073709551615

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50859
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: error 1104

Post by stevemaury » Mon Feb 25, 2008 12:59 pm

So, 10 MB. Kinda small.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: error 1104

Post by espicom » Mon Feb 25, 2008 2:40 pm

Actually, 10MB should be a good number, unless the board is pretty big and the users in question have lots of posts. One place it could have a problem is if the board has one or more "very popular" topics, with thousands of replies; I have seen quite a few boards have to "clip" such topics (close the original and start a new one) when they hit 8-10,000 replies, for performance reasons in vBulletin, to keep the board responding quickly. It is possible this might be similar.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

Jezzapandd
Registered User
Posts: 71
Joined: Wed Oct 31, 2007 5:11 pm
Location: Bham, UK
Contact:

Re: error 1104

Post by Jezzapandd » Mon Feb 25, 2008 6:26 pm

It has happened to the whole forums, and that has a total of 2030 posts.
I don't know if this might be the reason, but the database has 4 forums on the same one. they share users and stuff, but individual posts. I suppose this problem has been around since i made the 3 extra forums, but i didn't notice it then. and the problem has happened on 2 of the 4 forums

have the problem:
http://www.theviolation.net/WET/
http://www.theviolation.net/ETQW/

don't:
http://www.theviolation.net/COD4/
http://www.theviolation.net/forums/

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: error 1104

Post by espicom » Mon Feb 25, 2008 6:42 pm

Jezzapandd wrote:I don't know if this might be the reason, but the database has 4 forums on the same one.
I can't speculate as to whether or not it's related, but the method used to "share" users has always struck me as being rather tenuous at best. There are a lot of things wrong with how it deals with permissions, user groups, and message counts that could cause problems.

But, I've never seen a report of THIS problem that was associated with it!
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

Jezzapandd
Registered User
Posts: 71
Joined: Wed Oct 31, 2007 5:11 pm
Location: Bham, UK
Contact:

Re: error 1104

Post by Jezzapandd » Mon Feb 25, 2008 7:47 pm

well thanks for the reply, but it doesnt really explain what I can do to solve this

Jezzapandd
Registered User
Posts: 71
Joined: Wed Oct 31, 2007 5:11 pm
Location: Bham, UK
Contact:

Re: error 1104

Post by Jezzapandd » Tue Feb 26, 2008 4:31 pm

So can I solve this? Please?

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: error 1104

Post by espicom » Tue Feb 26, 2008 4:38 pm

Well, what did your host say when you asked them about it? So far, we've discussed things to try without getting the host involved, but, since this is a setting THEY made, you can see if they will change it to make things work.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

Post Reply

Return to “[2.0.x] MOD Writers Discussion”