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.
Patheos
Registered User
Posts: 1
Joined: Tue Jul 17, 2007 5:16 pm

Re: error 1104

Post by Patheos » Fri Mar 07, 2008 10:35 pm

Have you tried disabling the tags on peoples signatures? I looked through a number of your forum pages and there are tons of images there, maybe turn them off to see if it takes care of the problem and if so, maybe you can reduce the maximum allowed file sizes etc.?

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

Re: error 1104

Post by Jezzapandd » Sat Mar 08, 2008 7:04 pm

no offence, but I dont what ur trying to solve. if u read the post before yours in this topic, u will see that what u jsut posted was not relevant really.

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: error 1104

Post by drathbun » Mon Mar 10, 2008 11:48 am

I have been fighting that query for a while now. :) If you have a single forum with lots of topics in it, it is not very efficient.

What indexes do you have on phpbb_forums and phpbb_topics tables? If you have access to phpMyAdmin you should be able to run these queries:

Code: Select all

show indexes from phpbb_forums;
show indexes from phpbb_topics
I have created an index in my database that includes forum_id, topic_type, and topic_last_post_id that is now used on viewforum. The trick is, if you don't include those extra columns, the index that mysql picks is the index on forum_id. So if you have one forum with the bulk of your topics in it then mysql seems to want to read the entire table and get every topic by forum_id and then do additional processing to eliminate the rows. If you can do this (also in phpMyAdmin)

Code: Select all

explain 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
and post the results it might help.
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

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

Re: error 1104

Post by Jezzapandd » Mon Mar 10, 2008 4:42 pm

I ran the queries, but I don't know how to post the results, so can you tell me exactly what data you want?

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: error 1104

Post by drathbun » Tue Mar 11, 2008 2:47 pm

The explain should only generate a little bit of output, just post it here in "code" tags.
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

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

Re: error 1104

Post by Jezzapandd » Tue Mar 11, 2008 4:36 pm

Code: Select all

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	t 	ALL 	NULL 	NULL 	NULL 	NULL 	150 	Using where; Using temporary; Using filesort
1 	SIMPLE 	u 	eq_ref 	PRIMARY 	PRIMARY 	3 	theviola_phpb1.t.topic_poster 	1 	 
1 	SIMPLE 	p 	ALL 	NULL 	NULL 	NULL 	NULL 	2030 	Using where
1 	SIMPLE 	p2 	ALL 	NULL 	NULL 	NULL 	NULL 	2030 	Using where
1 	SIMPLE 	u2 	eq_ref 	PRIMARY 	PRIMARY 	3 	theviola_phpb1.p2.poster_id 	1 	 
bit hard to read, but I did what u said

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: error 1104

Post by drathbun » Tue Mar 11, 2008 11:50 pm

You don't have any indexes on your phpbb_topics table.

If you do show indexes from phpbb_topics; what do you see? According to the explain plan, there are no possible keys on the phpbb_topics table, and therefore you will scan every single row every time you need data from that table. You're also missing indexes that I would expect to be used on the post table. You're getting a primary key lookup on the users table, but that's it. Everything else is going to be slow and use lots of system resources.

Which, come to think of it, is what started this topic. :)

Normally you would not drop indexes from these tables. I have yet to see any sort of modification that would remove indexes, only add them. Did you do the install yourself? Or did your host? Because if they did the install, make them fix it.
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

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

Re: error 1104

Post by Jezzapandd » Wed Mar 12, 2008 7:09 am

it was fantastico

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: error 1104

Post by drathbun » Wed Mar 12, 2008 9:15 pm

I didn't see anything for this, which I also asked for earlier:
drathbun wrote:What indexes do you have on phpbb_forums and phpbb_topics tables? If you have access to phpMyAdmin you should be able to run these queries:

Code: Select all

show indexes from phpbb_forums;
show indexes from phpbb_topics
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

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

Re: error 1104

Post by Jezzapandd » Thu Mar 13, 2008 7:17 am

forums:

Code: Select all

Table 	Non_unique 	Key_name 	Seq_in_index 	Column_name 	Collation 	Cardinality 	Sub_part 	Packed 	Null 	Index_type 	Comment
phpbb_forums 	0 	PRIMARY 	1 	forum_id 	A 	19 	NULL 	NULL 	  	BTREE 	 
phpbb_forums 	1 	forums_order 	1 	forum_order 	A 	9 	NULL 	NULL 	  	BTREE 	 
phpbb_forums 	1 	cat_id 	1 	cat_id 	A 	9 	NULL 	NULL 	  	BTREE 	 
phpbb_forums 	1 	forum_last_post_id 	1 	forum_last_post_id 	A 	19 	NULL 	NULL 	  	BTREE 	 
topics:

Code: Select all

Table 	Non_unique 	Key_name 	Seq_in_index 	Column_name 	Collation 	Cardinality 	Sub_part 	Packed 	Null 	Index_type 	Comment
phpbb_topics 	0 	PRIMARY 	1 	topic_id 	A 	466 	NULL 	NULL 	  	BTREE 	 
phpbb_topics 	1 	forum_id 	1 	forum_id 	A 	12 	NULL 	NULL 	  	BTREE 	 
phpbb_topics 	1 	topic_moved_id 	1 	topic_moved_id 	A 	1 	NULL 	NULL 	  	BTREE 	 
phpbb_topics 	1 	topic_status 	1 	topic_status 	A 	2 	NULL 	NULL 	  	BTREE 	 
phpbb_topics 	1 	topic_type 	1 	topic_type 	A 	3 	NULL 	NULL 	  	BTREE 	 

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: error 1104

Post by drathbun » Thu Mar 13, 2008 5:22 pm

Oh. Sorry. My fault. In your query, you're not using phpbb_topics you are using phpbb2_topics. Can you do the index dump for those? Are you running queries that cross over two databases?

Try

Code: Select all

show indexes from phpbb2_topics

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

Re: error 1104

Post by Jezzapandd » Thu Mar 13, 2008 6:57 pm

grrrr... Im not using phpb2 anymore. I changed to a different system that works now. I said that in an earlier post. sorry. If thats what u were trying to fix :S

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: error 1104

Post by drathbun » Thu Mar 13, 2008 7:56 pm

I was basing my suggestions off of the sql you said you were having a problem with. If you will repost the query (or link to the specific post) I will be happy to take another look. :)
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

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

Re: error 1104

Post by Jezzapandd » Fri Mar 14, 2008 7:19 am

http://www.phpbb.com/community/viewtopi ... 5#p4578065
thanks :). completely different kind of thing now.

Post Reply

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