Board does not handle very high MSG Id Numbers

Finswimmer · January 31, 2010, 02:58:56 AM

Hi,

I have converted an old BB2 Board to SMF.
As of a massive spam attack we have very high message numbers: 3198416176 is the highest one at the moment.
Whenever I try to add a new topic I get:

Code Select

Duplicate entry '0-2' for key 2
Datei: /homepages/20/d204376042/htdocs/etcg/forum-smf-2jahre/Sources/Subs-Post.php
Zeile: 1557

All informations are stored correctly except ID_FIRST_MSG and ID_LAST_MSG.

Code Select

<db282652671>
  <!-- Tabelle smf_topics -->
    <smf_topics>
        <ID_TOPIC>383801</ID_TOPIC>
        <isSticky>0</isSticky>
        <ID_BOARD>16</ID_BOARD>
        <ID_FIRST_MSG>0</ID_FIRST_MSG>
        <ID_LAST_MSG>0</ID_LAST_MSG>
        <ID_MEMBER_STARTED>79898</ID_MEMBER_STARTED>
        <ID_MEMBER_UPDATED>79898</ID_MEMBER_UPDATED>
        <ID_POLL>0</ID_POLL>
        <numReplies>0</numReplies>
        <numViews>0</numViews>
        <locked>0</locked>
    </smf_topics>
</db282652671>

Here ID_TOPIC is the problem.

Code Select

 <smf_messages>
        <ID_MSG>3198416220</ID_MSG>
        <ID_TOPIC>0</ID_TOPIC>
        <ID_BOARD>2</ID_BOARD>
        <posterTime>1264924094</posterTime>
        <ID_MEMBER>79898</ID_MEMBER>
        <ID_MSG_MODIFIED>0</ID_MSG_MODIFIED>
        <subject>123 123 123 123 123</subject>
        <posterName>Finswimmer</posterName>
        <posterEmail>[email protected]</posterEmail>
        <posterIP>77.1.229.203</posterIP>
        <smileysEnabled>1</smileysEnabled>
        <modifiedTime>0</modifiedTime>
        <modifiedName></modifiedName>
        <body>123 123 123123 </body>
        <icon>xx</icon>
    </smf_messages>

If I adjust these three values everything is working fine.

On a local PC with Gentoo everything is working fine. With exactly the same data.
The very high msg numbers are just stored and it is working.

I adjust the Sub-Posts.php to output the $msgOptions[id] and in Post.php $_REQUEST['msg'].
Both the id was a very high negative value like : -10081454

What could be the problem?

Thank you
Tobi

Arantor · January 31, 2010, 05:40:22 AM

Upgrade to a 64-bit host.

32-bit PHP cannot really handle numbers greater than 2^31 properly.

rghb · January 31, 2010, 06:03:26 AM

Quote from: Arantor on January 31, 2010, 05:40:22 AM
Upgrade to a 64-bit host.

32-bit PHP cannot really handle numbers greater than 2^31 properly.

But message number 3.198.416.176 is smaller than 4.294.967.296 (2³²). So switching to 64bit host could not solve that problem, i think.

The questions is: Which data type is assigned to message id in the table definition? For such a high number like 3.198.416.176 this should be at least int (2³²). Data type int is defined as 0 - 4.294.967.295 .

Arantor · January 31, 2010, 06:06:05 AM

That's not the problem.

The table definition is unsigned int(10), which caps at 2^32 as you point out.

The problem is that PHP on 32-bit uses *signed* ints, meaning once it goes above 2^31-1, it wraps around to negative numbers which is the effect you're seeing.

rghb · January 31, 2010, 06:17:01 AM

You're right, i forgot the signed/unsigned thing.

Finswimmer · January 31, 2010, 10:16:20 AM

I guess you are right.
This is the server:
Linux infongd16264 2.6.28.8-20091106a-filemon-iscsi-pipe-grsec #1 SMP Fri Nov 6 15:17:10 CET 2009 i686 GNU/Linux

And my Desktop is a 64-bit system.

What can I do now?
Switching to 64-bit is not possible.

Any ideas?

Thanks

Arantor · January 31, 2010, 11:06:03 AM

The only answer will be to renumber the messages to use a lower id.

MrPhil · January 31, 2010, 11:32:39 AM

Once you get rid of the spam accounts and messages, is your highest ID comfortably under 2.1E+09? MySQL maintains an internal counter of the last value used for "auto_increment" counters. This value only increases, even if you delete messages. I believe there is a way to reset that value to something reasonable with an SQL command in phpMyAdmin. It's something like

Code Select

ALTER TABLE smf_messages AUTO_INCREMENT = new_value;
where new_value is where the next ID should be.

Of course, back up your database before making any such changes! You'll probably want to put your forum in maintenance mode while fooling with the database.

If you now have legitimate message IDs that are too high, you'll have to renumber IDs until they're reasonably small. You should be able to do this with SQL queries in phpMyAdmin: get the largest ID, pick a new value (not currently used), change smf_messages, find that ID value in all the other tables (one at a time), change them to the new value, repeat until all IDs are OK.

Finswimmer · February 05, 2010, 09:50:02 AM

This is a script, that I wrote:

This one copies all msg_ids to a new table with "_stripped" without gaps (from 1, 30, 219 to 1,2,3).

Code Select

#!/bin/bash

function queryDB {
mysql -N -S /tmp/mysql5.sock -utest123 -ptest123 -A -Dtest -e "$1"
}


### Drop Tables

queryDB "DROP TABLE smf_messages_stripped"
queryDB "DROP TABLE smf_attachments_stripped"
queryDB "DROP TABLE smf_boards_stripped"
queryDB "DROP TABLE smf_topics_stripped"

### Erzeuge Tabellen

queryDB "CREATE TABLE smf_messages_stripped LIKE smf_messages;"
queryDB "CREATE TABLE smf_attachments_stripped LIKE smf_attachments;"
queryDB "CREATE TABLE smf_boards_stripped LIKE smf_boards;"
queryDB "CREATE TABLE smf_topics_stripped LIKE smf_topics;"

###     Schleife
# Startwert
curr_ID_msg=0

max_posts=$(queryDB "SELECT COUNT(ID_MSG) FROM smf_messages;")
#echo $max_posts

#for (( i=1; $i <=20; i++ ))
for ((i=1; $i <= $max_posts; i++))
do

        ###     Kleinste ID_MSG in smf_messages
        next_ID_msg=$(queryDB "SELECT ID_MSG FROM smf_messages WHERE ID_MSG > $curr_ID_msg order by ID_MSG asc limit 1;")


        ### Kopiere smf_messages
        queryDB "INSERT INTO smf_messages_stripped SELECT $i,ID_TOPIC, ID_BOARD, posterTime, ID_MEMBER, ID_MSG_MODIFIED, subject, posterName, posterEmail, posterIP, smileysEnabled, modifiedTime, modifiedName, body, icon FROM smf_messages WHERE ID_MSG=$next_ID_msg"

        ### Kopiere smf_attachments
        queryDB "INSERT INTO smf_attachments_stripped SELECT ID_ATTACH, ID_THUMB, $i, ID_MEMBER, attachmentType, filename, file_hash, size, downloads, width, height FROM smf_attachments WHERE ID_MSG=$next_ID_msg"

        ### Kopiere smf_boards
        queryDB "INSERT INTO smf_boards_stripped SELECT ID_BOARD, ID_CAT, childLevel, ID_PARENT, boardOrder, $i, $i, memberGroups, name, description, numTopics, numPosts, countPosts, ID_THEME, permission_mode, override_theme FROM smf_boards WHERE ID_LAST_MSG=$next_ID_msg"

        ### Kopiere smf_topics
        queryDB "INSERT INTO smf_topics_stripped SELECT ID_TOPIC, isSticky, ID_BOARD, $i, ID_LAST_MSG, ID_MEMBER_STARTED, ID_MEMBER_UPDATED, ID_POLL, numReplies, numViews, locked  FROM smf_topics WHERE ID_FIRST_MSG=$next_ID_msg"
        queryDB "INSERT INTO smf_topics_stripped SELECT ID_TOPIC, isSticky, ID_BOARD, ID_FIRST_MSG, $i, ID_MEMBER_STARTED, ID_MEMBER_UPDATED, ID_POLL, numReplies, numViews, locked  FROM smf_topics WHERE ID_LAST_MSG=$next_ID_msg"

        ### Setze currMSG auf next
        curr_ID_msg=$next_ID_msg

done


###     Debug

#echo $next_ID_msg
#echo $curr_ID_msg

Tobi

News:

Board does not handle very high MSG Id Numbers

MrPhil