Sorting Gone Screwy

[127.0.0.1]

SMF Version: SMF 1.1.1
By default the topics in the message index are sorted by newest to oldest. However I just noticed that sorting is screwy for really old posts that were ported over from Yabb back in 2002 or 2003 when I upgraded to YabbSE I think it was.

It shows the oldest posts as listed:

Dec 9, 2002
Dec 10, 2002
Dec 11, 2002

Which is the wrong order! The oldest last post should be... last (at the bottom of the index).

And it contains to climb in the wrong order all the way to Sep 16, 2001

Where the next date is Dec 11, 2002, and again begins to list a batch of posts in the wrong order all the way to Oct 23, 2001

It does this again for another page of posts, until out of nowhere there's one from Aug 31, 2001. At that point it starts to list the posts correctly starting again from another batch at Dec 12, 2002 upwards to the present day.

[127.0.0.1]

I've been trying to figure this problem out by looking at the database with phpmyadmin. I cannot see anything peculiar with messages and topics of the really old posts as compared to the ones that are sorted properly.

Is there anything I should look for in particular? what tables and fields?

What could be causing this problem?

I did notice that the topics which are sorted out of whack are orderly in regards to the topic number.IE. topic=2, topic=3, topic=4, and so on. But that could just be relative to how they were put into the database when ported to smf? Hmm.

[KGIII]

(I am just clearing up older posts.)

Where you able to resolve this?

[127.0.0.1]

No.

[KGIII]

I can't think of any reason WHY it would do that UNLESS maybe the date format was different during that time and the date format is different for the current time. (The date format as stored in the database.)

Cycling it back to the top will, hopefully, get more people to look at it. The above is all I can think of and, to be honest, I regret that I can't think of a way to fix it.

[127.0.0.1]

Well, there is no date field in the topics table. I don't think it is actually sorting topics by "date," it sorts them by the id_first_msg and id_last_msg. It assumes they were made in chronological order. The only way to fix my problem is to look at each corresponding message, put them in chronological order and rewrite the id_first_msg/id_last_msg field. I THINK that's how it works, at least.

[KGIII]

That sounds a lot like work - I will see if there is someone who's more adept than I who can take a look at this for you.

[SleePy]

127.0.0.1,

Open your Settings.php add this in the database section:
$db_show_debug = true;

Then go your site. A new little information area is at the bottom. Click Show Queries.
Then go to one of your boards where this is messed up.
look for Sources/MessageIndex.php line 457 and paste last few lines starting with the WHERE from the query above the line you looked for.

[127.0.0.1]

Koodi [Valitse] Laajenna


WHERE t.ID_BOARD = 21
   AND ml.ID_MSG = t.ID_LAST_MSG
   AND mf.ID_MSG = t.ID_FIRST_MSG
ORDER BY isSticky DESC, ID_LAST_MSG DESC
LIMIT 0, 30
in c:\\web\\webroot\\backup\\forum\\Sources\\MessageIndex.php line 457, which took 0.07829881 seconds.


[SleePy]

in your phpmyadmin. Does your smf_topics have a ID_LAST_MSG column?

Does clicking any of the column titles on the SMF board change its sort order?

[127.0.0.1]

in your phpmyadmin. Does your smf_topics have a ID_LAST_MSG column?

Yup, it does...

ID_TOPIC
ID_BOARD
ID_MEMBER_STARTED
ID_MEMBER_UPDATED
ID_FIRST_MSG
ID_LAST_MSG
ID_POLL
numReplies
numViews
locked
isSticky

Does clicking any of the column titles on the SMF board change its sort order?

Yes, and each column sorts correctly. The exception being the Last Post column with old topics that were ported over from YaBB.

[SleePy]

any errors appear in the smf error log? apache error log? mysql error log?

could you include a phpinfo page? What is phpinfo.php?

Also does running Find and Repair errors in Forum Maintenance work?

[127.0.0.1]

No errors in the SMF error log, no errors in the Apache log, and there was no MySQL error log where the documentation said it should be.

[SleePy]

127.0.0.1,

Sorry for the delayed response (A few times from the looks of it).
Where you able to resolve this?
The only thing I can think of is the converter imported some posts before other posts on accident causing it to be out of order.

You could if it really matters edit the posts via phpmyadmin and change the time back to at least look like its correct.
If new posts are correct and its just the old ones you converted I wouldn't see much to worry about.

[127.0.0.1]

I believe you are right, the converter fouled up.

It's two years worth of threads so fixing it manually is out of the question. I think it would require a script to automate the process but it is not really that big of deal. I'm not going to lose sleep over it.

Thanks for the reply

[TosaInu]

I have a similar problem,

Probably more messed up too: Ezboard -> PHPBB and some Ikonboard --> Vbul --> Invision PB --> SMF 1.1.4.

Wouldn't it be solved by using posterTime to sort it?

[TosaInu]

Hello,

The thing was already messed up by importing into another forum product, but it was no issue there as topics were sorted by the database date field, not by ID.

It can be sorted if you have PHPMyAdmin, run a few queries and use SMF's ability to repair.

The basic is that you make use of posterTime stored in table smf_messages. It also works in case of duplicate stamps (copied, double posts).

Backup the database first.

My database was small, so I did not run into any max_exec problems with either MyAdmin or SMF. You could add limit in the SQL queries if this is an issue for you, I don't know how SMF does this?

Open the SMF database in PHPMyAdmin (SMF has no SQL/querie tool, has it?)

===run this query to set all ID_MSG to a range of very high values to ensure not getting unique value clashes========

SET @counter = 99999;
UPDATE `smf_messages`
SET `ID_MSG` = (SELECT @counter := @counter + 1) WHERE 1
ORDER BY `posterTime`

Then run

SET @counter = 0;
UPDATE `smf_messages`
SET `ID_MSG` = (SELECT @counter := @counter + 1) WHERE 1
ORDER BY `posterTime`

The result is a neat increasing column 1,2,3,4.. for ID_MSG, probably like it started to be, but now chronologically sorted.

Find the highest value of  ID_MSG by sorting it in the browse view and
go to Operations (top menu MyPHPAdmin), set the value in auto_increment 1 higher than the highest value and save.


The board is now even more messed up, as the last and first posts in the topics are wrong. This can be fixed however by using SMF.

Go to admin/Maintenance/Forum Maintenance - General Maintenance and select Find and repair any errors. Select Yes to fix them.

Error:

Duplicate entry '2163-6' for key 2
File: /home2/mizusco/public_html/bushi/board/Sources/RepairBoards.php
Line: 223


Not sure what's going on, I don't think 1st and last message are unique (PHP function?), but make them unique and high values to avoid trouble.

Run these extra queries in PHPMyAdmin (note check how many posts you have as we need unique values, you may have to update 99999 and 9999999 in the queries ):

SET @counter = 99999;
UPDATE `smf_topics`
SET `ID_FIRST_MSG` = (SELECT @counter := @counter + 1) WHERE 1

and SET @counter = 9999999;
UPDATE `smf_topics`
SET `ID_LAST_MSG` = (SELECT @counter := @counter + 1) WHERE 1


Go to admin/Maintenance/Forum Maintenance - General Maintenance select Find and repair any errors. Select Yes to fix them.


All errors fixed! Please check on any categories, boards, or topics created to decide what to do with them.


Board looks fixed.

I'm not a skilled coder or SQL expert, but it seems fine.

[LiveWire]

I merged two forums together by shifting the message IDs on the one forum higher than the other. The result was that the second forum's oldest posts were before the newest posts from the first forum.

I ran the above SQL then this last. This is probably what fixing the repair function does, but I wanted to run everything quickly one after the other.

update `smf_topics` as t, (select id_topic, max(`id_msg`) maxID, min(`id_msg`) minID from smf_messages group by id_topic) as m
set t.`id_first_msg`=m.minID, t.`id_last_msg`=m.maxID
where t.`id_topic`=m.`id_topic`