Member Number in Database Incorrect

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Ideas Centre
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
User avatar
Tux_Rules
Registered User
Posts: 958
Joined: Sat Apr 24, 2004 10:48 pm
Location: Afton, Michigan
Contact:

Member Number in Database Incorrect

Post by Tux_Rules »

Hello all and good day. The issue I am having is that I have had a lot of "spam" users register, which I delete, and the member count is not correct. I have the Admin account, Anonymous account, plus a Mod user, as well as the 50 standard BOTs, but the next user to sign up will be numbered 166 (see attached picture). So, the question is:

How do I reset the SQL database numbering to show that the next member will be number 55 (or however many users there actually are)?

I have run the "resynchronise statistics" as well as the other "Resynchronise or reset statistics", but the member number is not in line with the actual number of users. I have looked in the database for a spot the might have user ID =165 as I could then change it to the proper number, but I have not found it...
Attachments
member.JPG
member.JPG (32.84 KiB) Viewed 596 times
User avatar
Mick
Support Team Member
Support Team Member
Posts: 22631
Joined: Fri Aug 29, 2008 9:49 am
Location: Cardiff

Re: Member Number in Database Incorrect

Post by Mick »

Support for phpBB2 has ended. Any support requests regarding phpBB2 are limited to help with conversion to phpBB3.

Information on how to convert from phpBB2 to phpBB3 is here.

Basically, you must have both phpBB2 and phpBB3 installed on the same server. After you have finished installing phpBB3 you will have the option to convert your phpBB2 board using the built in converter. The information it asks you for is related to the phpBB2 installation and can be found in its config.php file on the server. All of your users, posts, forums, PMs etc are converted. However, styles and MODs are not (with the exception of the birthday and attachment MODs which are supported by the converter). The phpBB2 database is not converted per se, it is merely read and converted/moved to the new phpBB3 database, so if it goes wrong your old board will not be affected at all. At the end of the conversion, once you’re happy it’s all working, your phpBB2 database and files can be backed up and removed if you so wish. They are not connected to the new installation in any way.
"The more connected we get the more alone we become" - Kyle Broflovski©
User avatar
Tux_Rules
Registered User
Posts: 958
Joined: Sat Apr 24, 2004 10:48 pm
Location: Afton, Michigan
Contact:

Re: Member Number in Database Incorrect

Post by Tux_Rules »

Thanks for the reply, but this is a new install. I apologize for not stating that...it is a clean install of phpBB 3.0.10, not a conversion of upgrade...I have found this in the SQL structure....phpbb_users called "NextAutoindex":
member_2.JPG
member_2.JPG (69.43 KiB) Viewed 585 times
Now the question is how to reset it to the actual number of users, and is there a way to do it automatically when users are deleted?
User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: Member Number in Database Incorrect

Post by Oyabun1 »

If you delete users using the built in functions using the ACP member numbers are automatically synchronised.

Did you delete users directly from the database?
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests
User avatar
Tux_Rules
Registered User
Posts: 958
Joined: Sat Apr 24, 2004 10:48 pm
Location: Afton, Michigan
Contact:

Re: Member Number in Database Incorrect

Post by Tux_Rules »

Oyabun1 wrote:If you delete users using the built in functions using the ACP member numbers are automatically synchronised.

Did you delete users directly from the database?
No, not through the database. I am doing it through the ACP....and it does NOT automatically sync them...as evident in both pictures. It may show proper in the member list, but in the database the number jumps from the last user (my moderator, number 54) to 166.
User avatar
Brf
Support Team Member
Support Team Member
Posts: 52052
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Member Number in Database Incorrect

Post by Brf »

RUn this SQL in phpmyAdmin:

Code: Select all

ALTER TABLE phpbb_users AUTO_INCREMENT = 60;
Where 60 is the next number you want.
User avatar
Tux_Rules
Registered User
Posts: 958
Joined: Sat Apr 24, 2004 10:48 pm
Location: Afton, Michigan
Contact:

Re: Member Number in Database Incorrect

Post by Tux_Rules »

Brf wrote:RUn this SQL in phpmyAdmin:

Code: Select all

ALTER TABLE phpbb_users AUTO_INCREMENT = 60;
Where 60 is the next number you want.
Awesome, Thank You very much...that did it. :P

Any way to add this to the base code so that when a spammer is deleted or any other user is deleted that it reads the last user id number and automatically corrects for the next new user?
User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: Member Number in Database Incorrect

Post by Oyabun1 »

You could start a RFC (request for comment) topic on the Development board, [3.x] RFCs. If you can get some support for it then it will be considered for a future version.

In order to gain support you will need to clearly show what benefit this change would bring, so good luck with that. :roll:
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests
User avatar
Tux_Rules
Registered User
Posts: 958
Joined: Sat Apr 24, 2004 10:48 pm
Location: Afton, Michigan
Contact:

Re: Member Number in Database Incorrect

Post by Tux_Rules »

Oyabun1 wrote:In order to gain support you will need to clearly show what benefit this change would bring, so good luck with that. :roll:
I do realize that a major benefit would not be readily shown, and most likely is not a long term issue that would cause problems, but for nit-pickers like me, the accuracy in the member count would be the benefit...take this site for example...the memberlist page shows total members is 429,970, but the newest member (at this time/posting), whos username is 22football, is showing as user number 1,347,482..BIG discrepancy...so even though the usercount is showing proper, the database user count is wayyyy off. Not sure how many other people would be concerned with it.
User avatar
Mick
Support Team Member
Support Team Member
Posts: 22631
Joined: Fri Aug 29, 2008 9:49 am
Location: Cardiff

Re: Member Number in Database Incorrect

Post by Mick »

The problem is, if every conceivable option/hack/MOD or whatever was introduced (for nit-pickers :P ) to the core code, before long you would need a terabyte of webspace before you got started. I'm not arguing one way or the other whether or not this would be beneficial to a lot of users but the path Oyabun1 mentioned, [3.x] RFCs, is the way to go. If it's accepted it will be added.
"The more connected we get the more alone we become" - Kyle Broflovski©
User avatar
Tux_Rules
Registered User
Posts: 958
Joined: Sat Apr 24, 2004 10:48 pm
Location: Afton, Michigan
Contact:

Re: Member Number in Database Incorrect

Post by Tux_Rules »

Mick wrote:The problem is, if every conceivable option/hack/MOD or whatever was introduced (for nit-pickers :P ) to the core code, before long you would need a terabyte of webspace before you got started. I'm not arguing one way or the other whether or not this would be beneficial to a lot of users but the path Oyabun1 mentioned, [3.x] RFCs, is the way to go. If it's accepted it will be added.
That I do understand! :mrgreen: I may submit it...but I might first dig through the code to see if I can make the MOD myself, then submit it as such...that way others can pick and choose...I just needed the "bump" as to where to begin...which I received.. 8-)

Thanks again for the help! :)
User avatar
HGN
Former Team Member
Posts: 4706
Joined: Wed Dec 03, 2008 1:53 pm
Location: The Netherlands
Name: Alfred
Contact:

Re: Member Number in Database Incorrect

Post by HGN »

Tux_Rules wrote:Any way to add this to the base code so that when a spammer is deleted or any other user is deleted that it reads the last user id number and automatically corrects for the next new user?
Suppose not the latest member is deleted but a member that has newer members after him/her.
That will make a hole in the database numbering (userid) as well. It will not do to only read the last used number, but requires renumbering of all users after that deleted member.
However synchronising the user table will give problems with many other tables, synce they are related through the user_id. So it requires updating userid in many other tables, e.g. posts, topics, groups, permissions, etc.
User avatar
Tux_Rules
Registered User
Posts: 958
Joined: Sat Apr 24, 2004 10:48 pm
Location: Afton, Michigan
Contact:

Re: Member Number in Database Incorrect

Post by Tux_Rules »

HGN wrote:
Tux_Rules wrote:Any way to add this to the base code so that when a spammer is deleted or any other user is deleted that it reads the last user id number and automatically corrects for the next new user?
Suppose not the latest member is deleted but a member that has newer members after him/her.
That will make a hole in the database numbering (userid) as well. It will not do to only read the last used number, but requires renumbering of all users after that deleted member.
However synchronising the user table will give problems with many other tables, synce they are related through the user_id. So it requires updating userid in many other tables, e.g. posts, topics, groups, permissions, etc.
Figured that one out about an hour after posting my last comment and had time to really contemplate what all it would take. It is beyond what I would want to try and tackle for my level of experience, though I am sure it could be done.
User avatar
Lumpy Burgertushie
Registered User
Posts: 67985
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: Member Number in Database Incorrect

Post by Lumpy Burgertushie »

well anything can be done. however something like this is probably not going to be worth anyone's time.

I have never seen a request for something like this before. people just dont usually care about the ID numbers staying in order. It has no effect on how the board runs etc.


robert
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.3 Styles by PlanetStyles.net

If nobody is in the forest, does a tree really fall?
User avatar
Tux_Rules
Registered User
Posts: 958
Joined: Sat Apr 24, 2004 10:48 pm
Location: Afton, Michigan
Contact:

Re: Member Number in Database Incorrect

Post by Tux_Rules »

Lumpy Burgertushie wrote:well anything can be done. however something like this is probably not going to be worth anyone's time.

I have never seen a request for something like this before. people just dont usually care about the ID numbers staying in order. It has no effect on how the board runs etc.


robert
I agree that it won't be worth the time to do (made my brain hurt just thinking of what it would entail to make the code to update and keep things in order :cry: ). I had asked for the help for my own nit-pickyness :mrgreen: and I do realize that it should have no adverse affects on how the board runs. ;)

And I again want to thank everyone for their help.
Locked

Return to “[3.0.x] Support Forum”