"Compact" all instances of member_id field

[ashlar]

A feature/script where you can reclaim the "unused" (due to members deleting themselves or other reasons) member_id values.  Actually Ive posted this problem at the General English Support category already (below) but I'm getting a little desperate here.

http://www.simplemachines.org/community/index.php?topic=10345.0

[[Unknown]]

Do you have MySQL 4?

If you do:

Code Select Expand

CREATE TABLE prefix_new_members (
  ID_MEMBER mediumint(8) unsigned NOT NULL auto_increment,
  memberName varchar(80) NOT NULL default '',
  dateRegistered int(10) unsigned NOT NULL default '0',
  posts mediumint(8) unsigned NOT NULL default '0',
  ID_GROUP smallint(5) unsigned NOT NULL default '0',
  lngfile tinytext NOT NULL default '',
  lastLogin int(11) NOT NULL default '0',
  realName tinytext NOT NULL default '',
  instantMessages smallint(5) NOT NULL default 0,
  unreadMessages smallint(5) NOT NULL default 0,
  im_ignore_list tinytext NOT NULL default '',
  passwd varchar(64) NOT NULL default '',
  emailAddress tinytext NOT NULL default '',
  personalText tinytext NOT NULL default '',
  gender tinyint(4) unsigned NOT NULL default '0',
  birthdate date NOT NULL default '0000-00-00',
  websiteTitle tinytext NOT NULL default '',
  websiteUrl tinytext NOT NULL default '',
  location tinytext NOT NULL default '',
  ICQ tinytext NOT NULL default '',
  AIM varchar(16) NOT NULL default '',
  YIM varchar(32) NOT NULL default '',
  MSN tinytext NOT NULL default '',
  hideEmail tinyint(4) NOT NULL default '0',
  showOnline tinyint(4) NOT NULL default '1',
  timeFormat varchar(80) NOT NULL default '',
  signature text,
  timeOffset float NOT NULL default '0',
  avatar tinytext NOT NULL default '',
  im_email_notify tinyint(4) NOT NULL default '0',
  karmaBad smallint(5) unsigned NOT NULL default '0',
  karmaGood smallint(5) unsigned NOT NULL default '0',
  usertitle tinytext NOT NULL default '',
  notifyAnnouncements tinyint(4) NOT NULL default '1',
  notifyOnce tinyint(4) NOT NULL default '1',
  memberIP tinytext NOT NULL default '',
  secretQuestion tinytext NOT NULL default '',
  secretAnswer tinytext NOT NULL default '',
  ID_THEME tinyint(4) unsigned NOT NULL default '0',
  is_activated tinyint(3) unsigned NOT NULL default '1',
  validation_code varchar(10) NOT NULL default '',
  ID_MSG_LAST_VISIT int(10) unsigned NOT NULL default '0',
  additionalGroups tinytext NOT NULL default '',
  smileySet varchar(48) NOT NULL default '',
  ID_POST_GROUP smallint(5) unsigned NOT NULL default '0',
  totalTimeLoggedIn int(10) unsigned NOT NULL default '0',
  ID_OLD int(10) unsigned NOT NULL default 0,
  PRIMARY KEY (ID_MEMBER),
  KEY memberName (memberName(30)),
  KEY dateRegistered (dateRegistered),
  KEY ID_GROUP (ID_GROUP),
  KEY birthdate (birthdate),
  KEY posts (posts),
  KEY lastLogin (lastLogin),
  KEY lngfile (lngfile(30)),
  KEY ID_POST_GROUP (ID_POST_GROUP)
) TYPE=MyISAM;

# Note: doesn't do im_ignore_list!
INSERT INTO prefix_new_members
SELECT
NULL, memberName, dateRegistered, posts, ID_GROUP, lngfile, lastLogin, realName,
instantMessages, unreadMessages, '', passwd, emailAddress, personalText,
gender, birthdate, websiteTitle, websiteUrl, location, ICQ , AIM, YIM, MSN,
hideEmail, showOnline, timeFormat, signature, timeOffset, avatar, im_email_notify,
karmaBad, karmaGood, usertitle, notifyAnnouncements, notifyOnce, memberIP,
secretQuestion, secretAnswer, ID_THEME, is_activated, validation_code,
ID_MSG_LAST_VISIT, additionalGroups, smileySet, ID_POST_GROUP, totalTimeLoggedIn, ID_MEMBER
FROM prefix_members
ORDER BY dateRegistered;

UPDATE prefix_attachments AS a, prefix_new_members AS mem
SET a.ID_MEMBER = mem.ID_MEMBER
WHERE a.ID_MEMBER = mem.ID_OLD;

UPDATE prefix_banned AS b, prefix_new_members AS mem
SET b.ID_MEMBER = mem.ID_MEMBER
WHER Eb.ID_MEMBER = mem.ID_OLD;

UPDATE prefix_calendar AS c, prefix_new_members AS mem
SET c.ID_MEMBER = mem.ID_MEMBER
WHERE c.ID_MEMBER = mem.ID_OLD;

UPDATE prefix_collapsed_categories AS cc, prefix_new_members AS mem
SET cc.ID_MEMBER = mem.ID_MEMBER
WHERE cc.ID_MEMBER = mem.ID_OLD;

UPDATE prefix_instant_messages AS pm, prefix_new_members AS mem
SET pm.ID_MEMBER_FROM = mem.ID_MEMBER
WHERE pm.ID_MEMBER_FROM = mem.ID_OLD;

UPDATE prefix_im_recipients AS pmr, prefix_new_members AS mem
SET pmr.ID_MEMBER = mem.ID_MEMBER
WHERE pmr.ID_MEMBER = mem.ID_OLD;

UPDATE prefix_log_actions AS la, prefix_new_members AS mem
SET la.ID_MEMBER = mem.ID_MEMBER
WHERE la.ID_MEMBER = mem.ID_OLD;

UPDATE prefix_log_boards AS lb, prefix_new_members AS mem
SET lb.ID_MEMBER = mem.ID_MEMBER
WHERE lb.ID_MEMBER = mem.ID_OLD;

UPDATE prefix_log_errors AS le, prefix_new_members AS mem
SET le.ID_MEMBER = mem.ID_MEMBER
WHERE le.ID_MEMBER = mem.ID_OLD;

UPDATE prefix_log_mark_read AS lmr, prefix_new_members AS mem
SET lmr.ID_MEMBER = mem.ID_MEMBER
WHERE lmr.ID_MEMBER = mem.ID_OLD;

UPDATE prefix_log_notify AS ln, prefix_new_members AS mem
SET ln.ID_MEMBER = mem.ID_MEMBER
WHERE ln.ID_MEMBER = mem.ID_OLD;

UPDATE prefix_log_polls AS lp, prefix_new_members AS mem
SET lp.ID_MEMBER = mem.ID_MEMBER
WHERE lp.ID_MEMBER = mem.ID_OLD;

UPDATE prefix_log_topics AS lt, prefix_new_members AS mem
SET lt.ID_MEMBER = mem.ID_MEMBER
WHERE lt.ID_MEMBER = mem.ID_OLD;

UPDATE prefix_messages AS m, prefix_new_members AS mem
SET m.ID_MEMBER = mem.ID_MEMBER
WHERE m.ID_MEMBER = mem.ID_OLD;

UPDATE prefix_moderators AS mods, prefix_new_members AS mem
SET mods.ID_MEMBER = mem.ID_MEMBER
WHERE mods.ID_MEMBER = mem.ID_OLD;

UPDATE prefix_themes AS t, prefix_new_members AS mem
SET t.ID_MEMBER = mem.ID_MEMBER
WHERE t.ID_MEMBER = mem.ID_OLD;

UPDATE prefix_topics AS t, prefix_new_members AS mem
SET t.ID_MEMBER_STARTED = mem.ID_MEMBER
WHERE t.ID_MEMBER_STARTED = mem.ID_OLD;

UPDATE prefix_topics AS t, prefix_new_members AS mem
SET t.ID_MEMBER_UPDATED = mem.ID_MEMBER
WHERE t.ID_MEMBER_UPDATED = mem.ID_OLD;

ALTER TABLE prefix_members
DROP ID_OLD;

RENAME TABLE prefix_members TO prefix_old_members;
RENAME TABLE prefix_new_members TO prefix_members;

Replace all the prefix_ with your prefix.

-[Unknown]

[ashlar]

you mean command line access to mysql??? yes!!!
whohooo thank you unknown!!! this really makes my weekend!!! 
cant wait to try it!  will post the results of this fix after I applied it here.

[dschwab9]

Why would this be neccessary?  Is there some kind of limit to the member id field or something?

[[Unknown]]

Yeah, once you get past 16 million you'll run into problems, actually. (luckily, that's a small country so most people will never know or care.)

-[Unknown]

[ashlar]

The script ran into some problems:

Code Select Expand

mysql> INSERT INTO prefix_new_members
    -> SELECT NULL, memberName, dateRegistered, posts, ID_GROUP, lngfile, lastLogin, realName,instantMessages, unreadMessages, '', passwd, emailAddress, personalText,gender, birthdate, websiteTitle, websiteUrl, location, ICQ , AIM, YIM, MSN,hideEmail, showOnline, timeFormat, signature, timeOffset, avatar, im_email_notify,karmaBad, karmaGood, usertitle, notifyAnnouncements, notifyOnce, memberIP,secretQuestion, secretAnswer, ID_THEME, is_activated, validation_code,
    -> ID_MSG_LAST_VISIT, additionalGroups, smileySet, ID_POST_GROUP, totalTimeLoggedIn, ID_OLD
    -> FROM prefix_members
    -> ORDER BY dateRegistered;
ERROR 1054: Unknown column 'ID_OLD' in 'field list'


Anyone know what the problem is?  I basically ran the entire script as is (except for the prefix changes) and encountered the error in that section. 

[[Unknown]]

Replace only that ID_OLD with ID_MEMBER.  I updated my post.

-[Unknown]

[ashlar]

Solved!  Thanks again [Unknown]!

[TodaysXt]

Hi [Unknown],
I'm not sure what had happened after I had upgraded I from yabbse to smf I got over 5000 members, but they were only the same 300 or so members over and over so I just went through and deleted the rest, so now I had about 10 more members join and they were getting member numbers like 5696,5697,5698,5699 I had edited them, but I thought I better clean this up so, I was trying to run your script, I got the first error, ok ..

UPDATE prefix_banned AS b, prefix_new_members AS mem
SET b.ID_MEMBER = mem.ID_MEMBER
WHER Eb.ID_MEMBER = mem.ID_OLD;

but can't figure this one out, any suggestions...

UPDATE yabbse_log_boards AS lb, yabbse_new_members AS mem
SET lb.ID_MEMBER = mem.ID_MEMBER
WHERE lb.ID_MEMBER = mem.ID_OLD
MySQL said:
#1062 - Duplicate entry '15-2' for key 1

I tried to move it to the bottom of the script and ran into a couple of more so I put all of these at the bottom of the script and it runs until it hits these last three at the bottom of the script.
SQL-query : 
UPDATE yabbse_log_mark_read AS lmr, yabbse_new_members AS mem
SET lmr.ID_MEMBER = mem.ID_MEMBER
WHERE lmr.ID_MEMBER = mem.ID_OLD
MySQL said:
#1062 - Duplicate entry '3-7' for key 1

SQL-query : 
UPDATE yabbse_log_topics AS lt, yabbse_new_members AS mem
SET lt.ID_MEMBER = mem.ID_MEMBER
WHERE lt.ID_MEMBER = mem.ID_OLD
MySQL said:
#1062 - Duplicate entry '33-8' for key 1


Thanks
Xt.

[[Unknown]]

That should be WHERE b. not WHER Eb.....

But, this is from May.  I'm not sure it'd still work with Beta 6.

-[Unknown]

[madfiddler]

I'd me interested to know whether this works in RC1

[Cixi]

Hello.
I'm new into PHP and SMF.
I have to reset the counters of my members.
What does it mean I have to change the prefix_ with my prefix?
what I exactly have to do?
I can't understand.
Thank You
Regards