UBB.Developers Forums Announcements Announcements Anyone running 6.4

For anyone that is running 6.4. It seems that in some instances an important index isn't being created on the w3t_Last table. If the index isn't created your forum will start to slow down. You can check to make sure this index has been created by running the following sql command:

SHOW KEYS FROM w3t_Last

This should return 2 results something like this:

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Comment
w3t_Last 0 PRIMARY 1 L_Uid A
w3t_Last 0 PRIMARY 2 L_Board A 17259


If you don't see two PRIMARY keys, then this index didn't get created. In that case you will need to run the following 2 sql commands:

DELETE FROM w3t_Last

ALTER TABLE w3t_Last
ADD PRIMARY KEY Last_index(L_Uid,L_Board)

This will make it so all posts are unread for every user, but the index should be created at this point. You can verify this by running the SHOW KEYS FROM w3t_Last command up above and checking for the 2 returned results.

If you have a custom table prefix you will need to replace w3t_ with your prefix on all the commands.

For those that do need to create the index manually, if you can let me know that would be great so I can see how widespread of an issue this is.

I had both of those keys but I did an upgrade.. if they were in 6.3.2 they carried over if they are new to 6.4 they were created

I upgrade from 6.3.2 and the index still exist in 6.4. The altertable 6.4 to 6.4 script show the following:

Adding a new table to save database queries that are used frequently from the admin section
Adding a new field to the Groups table to allow for disabling of groups.
Grabbing all old poll data for conversion.
Dropping old poll tables and creating new ones for the updated poll system.
importing old poll data...
Adding a table for calendar events...
Altering the user's table to add a few new fields/preferences...
SQL ERROR: Mon, Dec 08 2003 12:13:09 +0100 Unable to do_query: ALTER TABLE w3t_Users ADD U_Birthday VARCHAR(10) NOT NULL DEFAULT '0', ADD U_ShowBday INT(1) NOT NULL DEFAULT '0', ADD U_TimeFormat VARCHAR(6), ADD U_Ignored TEXT, ADD U_OC_Userid VARCHAR(64), ADD INDEX birthday_ndx (U_Birthday,U_ShowBday)
Duplicate column name 'U_Birthday'Altering the online table to store info a bit differently...
Altering the board's table to allow admin's to associate an image with a forum...
Altering the posts table so posts can be linked to a calendar event...
Reworking some indexes for speed, this may take awhile. You will get a few errors about indexes not being able to be dropped. This is ok, index names might be a bit different depending on when you installed but they will be dropped properly...
SQL ERROR: Mon, Dec 08 2003 12:13:30 +0100 Unable to do_query: ALTER TABLE w3t_Last DROP INDEX Luid_ndx
Can't DROP 'Luid_ndx'. Check that column/key existsSQL ERROR: Mon, Dec 08 2003 12:14:13 +0100 Unable to do_query: ALTER TABLE w3t_Posts DROP INDEX w3t_Postsindex10
Can't DROP 'w3t_Postsindex10'. Check that column/key existsSQL ERROR: Mon, Dec 08 2003 12:14:13 +0100 Unable to do_query: ALTER TABLE w3t_Posts DROP INDEX topic_index
Can't DROP 'topic_index'. Check that column/key existsSQL ERROR: Mon, Dec 08 2003 12:14:13 +0100 Unable to do_query: ALTER TABLE w3t_Posts DROP INDEX topic_ndx
Can't DROP 'topic_ndx'. Check that column/key existsSQL ERROR: Mon, Dec 08 2003 12:14:13 +0100 Unable to do_query: ALTER TABLE w3t_Posts DROP INDEX w3t_Postsindex9
Can't DROP 'w3t_Postsindex9'. Check that column/key existsSQL ERROR: Mon, Dec 08 2003 12:14:13 +0100 Unable to do_query: ALTER TABLE w3t_Posts DROP INDEX w3t_Postsindex9
Can't DROP 'w3t_Postsindex9'. Check that column/key existsSQL ERROR: Mon, Dec 08 2003 12:14:31 +0100 Unable to do_query: ALTER TABLE w3t_Posts DROP INDEX w3t_Postsindex6
Can't DROP 'w3t_Postsindex6'. Check that column/key existsCreating a cache table for some things that don't change to often...
Creating a table to store version info to make future upgrades easier...
Done

The Birthday SQL is forced by an old birthday hack and dosn't matter.

it's possible an announcement like this might be better server at the official IP support forum:

http://community.infopop.net/eve/ubb.x?a=cfrm&s=729094322&f=8233088913

we're just a small, still important, but small piece of the threads community

I also posted this under the release announcement for 6.4.

I will also say this. If you have any custom links to recent threads, make sure that you update these to the new format for 6.4. If you do not, then when this is hit it will do a full table scan on your posts table whenever that link is hit. This will essentialy cripple your forums.

Basically, hover your mouse over the "New posts in past 24 hours" link and look at the variables that are passed to the dosearch.php script. You'll need to make sure that you update any custom links to the search engine with these variables.

Yes, very good point ! This was one og the first thing I noticed after update to 6.4. The search was very slow und I reviewd the link. Do a new search from the search menu and .. ahahhh.. it's fast. Seems, that this major update made a lot of little changes that no one noticed.

I fear a 6.4.1 will follow soon. I read the infopop forum daily and see a lot of little fixes.

[]Scream said:
I will also say this. If you have any custom links to recent threads, make sure that you update these to the new format for 6.4. If you do not, then when this is hit it will do a full table scan on your posts table whenever that link is hit. This will essentialy cripple your forums.

Basically, hover your mouse over the "New posts in past 24 hours" link and look at the variables that are passed to the dosearch.php script. You'll need to make sure that you update any custom links to the search engine with these variables. [/]

Ahhhhhhhhrggghhh !!! Finally ! After I updated my Forum to 6.4b1 my comunity were distructed ! The site's performance become very VERY poor and now I understand why ! I have a lot of custom search links ! Cool, I'm goning to update all this links !!

Anyone could help me converting this search string with the new format ?

%20[WORD2]&Match=&Old=allpo...e.com/ForumPath/dosear...ts&Limit=20

or again, hot to write a query to show all post in a certain forum with a word in the title ?

I use ProxyTrace from www.pocketsoap.com. You run the .exe file and it starts a little proxy tool. Enter localhost and port from proxytrace in your browser and then do a search from the search panel of UBB.threads. The string witch is submit by clicking the submit button in UBB can be reviewed in the proxy tool. There you can see all HTML action in plain text and nothing is hidden. Very useful.

You're right ! It's very useful tool !

My Past 24 Hour link is fast, but when trying to install the Yet Another Search Mod 2.0 the "display all last posts" function is slow -about 45 seconds to finish on my main site. On my dev site (that has a much smaller database) the search is very fast. The same mod produced fast results on my main site before I upgraded to 6.4 -although it was displaying every post and not just the last one.

I ran the check table MySQL command against every table, but it found no erros. I even ran repair table against the post and poll tables, but with no change (I got a few errors when running the altertable for 6.3 to 6.4 that were related to polls).

I'm at a loss as to why it won't work. I even optimised tables with no change

Since the dosearch.php script runs off of post variables you can't easily add debug=1 to the URL to get the full information on the query. So, try this. Open up dosearch.php and right after the calls to get_input() add this:

$debug=1;

Then run a search using the mod you're trying to create. You'll get a full EXPLAIN on the query which will help diagnose what rows the search is grabbing and if it's using any indexes, etc.

I posted this at http://community.infopop.net/eve/ubb.x?a...1034527&r=# as well, but I figured the more eyes, the merrier.

I have been experiencing problems similar to this, have tried every suggestion I have found, and it still rather annoyingly persists.

On postlist, I will have a long thread that has pagination links under the subject...

(1 2 3 4 ... 8 9 10 all)

If I click on a number, I am presented with...


quote:
--------------------------------------------------------------------------------

There was a problem looking up the post in our database.

Please use your back button to return to the previous page.

--------------------------------------------------------------------------------



Clicking "all" sometimes works. Other times it will show all except for the last page. You can tell there are supposed to be more posts from what you see on postlist's 'replies' and 'last post', but showflat tells a different story.

There are a few other nuances, but I think you get the drift. I am really starting to pull my hair out over here...

The page number links was my fault. Misplaced slash. I Fixed it.

You dirty rat!

[]Scream said:
Since the dosearch.php script runs off of post variables you can't easily add debug=1 to the URL to get the full information on the query. So, try this. Open up dosearch.php and right after the calls to get_input() add this:

$debug=1;

Then run a search using the mod you're trying to create. You'll get a full EXPLAIN on the query which will help diagnose what rows the search is grabbing and if it's using any indexes, etc. [/]

Here is the culprit:

Query: SELECT t1.B_Number , t1.B_Main , t3.U_Username , t1.B_Subject , t1.B_Posted , t1.B_Board , t3.U_Color , t1.B_Reged , t1.B_Icon , t2.Bo_Title , t2.Bo_Read_Perm , t2.Bo_Cat , t1.B_Posterid , t1.B_Status , t1.B_AnonName , t1.B_Body FROM w3t_Posts AS t1 , w3t_Boards AS t2 , w3t_Users AS t3 LEFT JOIN w3t_Posts as t4 ON t1.B_Main = t4.B_Main AND t1.B_Number < t4.B_Number WHERE (t4.B_Main IS NULL) AND t1.B_Approved = 'yes' AND t1.B_Board = t2.Bo_Keyword AND t1.B_Posterid = t3.U_Number AND t1.B_Status <> 'M' AND t1.B_Board IN ('UBB1' , 'UBB2' , 'UBB4' , 'UBB5' , 'UBB6' , 'networking' , 'danews' , 'construction' , 'soho' , 'reviews' , 'security' , 'win2k' , 'unix' , 'test' , 'ports' , 'info' , 'mobile' , 'feedback' , 'photos' , 'unreg' , 'swap' , 'change' , 'help' , 'deals' , 'article' , 'software') AND ( (t1.B_Subject LIKE '%%' OR t1.B_Body LIKE '%%')) ORDER BY t1.B_Last_Post DESC LIMIT 26
table type possible_keys key key_len ref rows Extra
t2 ALL indx1 27 Using temporary; Using filesort
t1 ref w3t_Postsindex7,ID_ndx,board_topic_ndx w3t_Postsindex7 103 const,t2.Bo_Keyword 2086 where used
t3 eq_ref PRIMARY,indx3 PRIMARY 4 t1.B_PosterId 1
t4 ref PRIMARY,w3t_Postsindex2,w3t_Postsindex3 w3t_Postsindex3 4 t1.B_Main 7 where used; Not exists
Query took a total of 46.043 seconds.

Not sure how this post is gonna look -formatting may be ugly...

Ok, that left join is probably causing some issues. There is a full table scan being done in the search by noting where it says ALL on t2, and the left join is compounding that.

With the latest search engine changes, this mod would probably be better served to run a different type of search query instead of the default one. Maybe one query to grab the latest topics and another to just loop through those and grab the info from the latest posts in those topics. This is probably not the best place to post this, probably be should be in another forum but I can give some tips here that can be continued elsewhere

This basic query would need to be modified to only select from the proper forums, but this would give you the last post and posterid for every topic ordering it so the most recent topics get returned first:

SELECT B_LastPostNum,B_LastPosterId
FROM w3t_Posts
WHERE B_Topic='1'
ORDER BY B_Last_Post DESC

Then you'd just need to loop through the results and grab the actual information for those particular posts. Would make for a quite complex modification I'm sure but on large forums with alot of posts the regular search engine would pretty much die as it's doing now on your forums.

Actually if you don't mind giving the subject of the topic itself instead of the last post in the topic, this query should do what you are looking for...

SELECT t1.B_LastPostNum, t1.B_Main , t3.U_Username , t1.B_Subject , t1.B_Posted , t1.B_Board , t3.U_Color , t1.B_Reged , t1.B_Icon , t2.Bo_Title , t2.Bo_Read_Perm , t2.Bo_Cat , t1.B_Posterid , t1.B_Status , t1.B_AnonName , t1.B_Body FROM w3t_Posts AS t1 , w3t_Boards AS t2 , w3t_Users AS t3 WHERE t1.B_Topic='1' AND t1.B_Approved = 'yes' AND t1.B_Board = t2.Bo_Keyword AND t1.B_LastPosterId = t3.U_Number AND t1.B_Status <> 'M' AND t1.B_Board IN ('UBB1' , 'UBB2' , 'UBB4' , 'UBB5' , 'UBB6' , 'networking' , 'danews' , 'construction' , 'soho' , 'reviews' , 'security' , 'win2k' , 'unix' , 'test' , 'ports' , 'info' , 'mobile' , 'feedback' , 'photos' , 'unreg' , 'swap' , 'change' , 'help' , 'deals' , 'article' , 'software') AND ( (t1.B_Subject LIKE '%%' OR t1.B_Body LIKE '%%')) ORDER BY t1.B_Last_Post DESC LIMIT 26

That eliminates the last join and only returns one entry per topic. Ok, like I said, that's something to go on

Now my head is spinning

LOL. Ok, that last query I gave is pretty much like the default search query. I bolded the only changes that were made to it.

I will do some testing on it

[]Scream said:
LOL. Ok, that last query I gave is pretty much like the default search query. I bolded the only changes that were made to it. [/]

You are Da Man! Thanks Scream!

Works Great Dal.

Thanks for all the hard work you put into YASM 2.0!

I have just changed server and think I have this problem on my 6.2.3 board.

This is what w3t_Last; looks like.



No keys. So I tried to ad them:


What do I do wrong?

The above keys where for 6.4. I would not attempt making changes to tables based on functionality you do not have yet. The Index's changed in 6.4 thus the change.

Oki... so this keys is not anything to worry about on 6.2.3?

Exactly thats why the topic of the discussion says Anyone running 6.4

There was an issue for some people who ran the altertable-6.3-6.4.php that the new INDEX's did not get created as they changed in the new version.

Just upgraded to 6.4 and the index's have been created etc. but my forum are slow then OK slow then OK, mostly on the main index page.

I think I just found the problem.

Never mind, only lasted for a few minutes back to being slow again.

Yeah... i read the topic. but I´ve got an error similar to this, so i thought...