How to reset member_id's auto_increment number

[ashlar]

HELP!  I need a quick fix script for this fast bacause my forum doesn't seem to be able to add new users.  I add users from an external script which automatically inserts them as members in my forum (I have a separate database of members with extra fields).  Anyway, lately it always displays "Duplicate entry 16777215 for key 1"  when I try to insert into the members table of SMF.  I'm assuming the auto_increment value has reached its limit for the medium int variable.  I've never tried inserting members with the member_id specified so I'm wondering how the value got so blown up since I only have 20k plus members.  Perhaps the jump in the member_id value happened during one of my upgrades? (was using YabbSE before and already upgraded many times through YabbSE 1.3...  and SMF 1.0....).  Anyway I really REALLY need a script where all the instances of each member_id can be synchronized and reset to at least follow the actual number of users and which also resets the current auto_increment value to 20k+.  Was thinking of creating one but am afraid of messing things up even more.   

[Oldiesmann]

I did this when I went from YaBB SE to a fresh install of LSP Enigma Portal, and it can be done, but it is a pain in the rear - it was for me and I only had about 100 members. Basically, you'd have to re-number all the member IDs according to the gaps (ie the range if IDs for which there is no corresponding member), then you'd have to update all the topics, posts, and even the polls. If you have enough time and patience, you can do it with a ton of MySQL queries, but it really is not easy to do at all. As far as the numbers getting "blown up" - that's caused by members joining your board and then deleting their accounts. The way the auto_increment function works, it doesn't check to see if any previous values exist - it just continues incrementing. That's how you can end up with a member ID that's much higher than the actual number of members on your board.

So, just change everything dealing with ID_MEMBER to a Bigint like so:

Code Select Expand

ALTER TABLE smf_members CHANGE ID_MEMBER ID_MEMBER BIGINT(8) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE smf_collapsed_categories CHANGE ID_MEMBER ID_MEMBER BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_im_recipients CHANGE ID_MEMBER ID_MEMBER BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_instant_messages CHANGE ID_MEMBER_FROM ID_MEMBER_FROM BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_log_actions CHANGE ID_MEMBER ID_MEMBER BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_log_boards CHANGE ID_MEMBER ID_MEMBER BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_log_karma CHANGE ID_TARGET ID_TARGET BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_log_karma CHANGE ID_EXECUTOR ID_EXECUTOR BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_log_mark_read CHANGE ID_MEMBER ID_MEMBER BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_log_notify CHANGE ID_MEMBER ID_MEMBER BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_log_online CHANGE ID_MEMBER ID_MEMBER BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_log_polls CHANGE ID_MEMBER ID_MEMBER BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_log_topics CHANGE ID_MEMBER ID_MEMBER BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_members CHANGE ID_MEMBER ID_MEMBER BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_messages CHANGE ID_MEMBER ID_MEMBER BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_moderators CHANGE ID_MEMBER ID_MEMBER BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_polls CHANGE ID_MEMBER ID_MEMBER BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_themes CHANGE ID_MEMBER ID_MEMBER BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_topics CHANGE ID_MEMBER_STARTED ID_MEMBER_STARTED BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;
ALTER TABLE smf_topics CHANGE ID_MEMBER_UPDATED ID_MEMBER_UPDATED BIGINT(8) UNSIGNED DEFAULT 0 NOT NULL;

[ashlar]

arghh I knew this was gonna take a lot of work... it's funny that would happen though since I disabled registration in my board (had a centralized script for adding members) so I'm pretty sure it wasn't from members deleting their own accounts (my main members db is around 20+k but it still is greater in number than my smf members db). anyway thanks for the script oldiesmann!    but before I implement it will it affect my succeeding upgrades of smf?  I'm really wishing hard *hint hint*  that someone could come up or teach me (in theory) a "compact member_id" sort of script.  thanks again!

[Oldiesmann]

Yeah, come to think of it that probably would affect future upgrades. It wasn't really meant to be a permanent fix anyway - just a temporary one until someone could write a script to update everything (I tried that when I was doing it and the php script didn't work right).

[ashlar]

Unknown has posted a script to fix this:
http://www.simplemachines.org/community/index.php?topic=10400.0

Keeping my fingers crossed that it will finally fix everything. 
Thanks for all your help though Oldies!