PRoblem: New user gets userid=16777215

maybe a defect of the mysql table so that the auto-inc counter is corrupt?

I told him to repair the table too - that did not fix it.

Has he tried clearing the table completely? So that it is completely remade?

[syntaxhighlighter brush=php,first-line=1,highlight=0,collapse=false,html-script=false]dbquery('TRUNCATE TABLE ' . DB_USERS);[/syntaxhighlighter]

(truncate drops the table and then remakes it)

Then you can register a new account and manually set it as the super admin. Hopefully that will reset whatever happened to your increment key.

I tried doing what he told me in post number 2. But now the userid changes to 16777216 and when I make a new user 16777217 and a new 16777218..

hmm

If you have only 15 users... set all user_id to 1,2,3...15, and then try to repair the table.

agreed, you must fix counter, something like this

just an example, not for real use:

_YOUR_MAXIMUM_USER_ID_ - YOUR MAXIMUM real USER ID.

Polarfox. How should I do this exactly?

Try to use (make some backup first)

replace 100 to your max user ID

[syntaxhighlighter brush=php,first-line=1,highlight=0,collapse=false,html-script=false]
<?php
dbquery("ALTER TABLE ".DB_USERS." AUTO_INCREMENT = 100";
?>[/syntaxhighlighter]

Your webhost most likely has phpMyAdmin installed; you might want to just go in there and alter the structure of the table rather than running code. That way you have an interface to verify it worked as well. Just go to the OPERATIONS tab for the table and set the auto_increment field to the number of rows you have +1

Right.

Okay. I have 15 user now. So I change the auto_increment field to 16 ?

change it to the MAX_CURRENT_USER_ID + 1
could be there was a user more who was deleted, so look for the user_id field.

hm, how do I do that?

I went to operation inside the user_table and changed the auto_increment to 16. But how do I change it to MAX_CURRENT_USER_ID + 1 ?

Sorry, but I am a bit new in php and database :(

LOL.
MAX_CURRENT_USER_ID is the user_id with the highest value, in your case might be 15, highest user_id is not equal to the number of your registered users if you deleted a user for example.


If you done it already there's not need to change anything else. Do the new registered users have proper IDs?

Here is a complete script that will change the AUTO_INCREMENT based on user_id with the highest value, paste it in a custom page and preview it then delete it, don't save the page.

But the problem is that, his highest user id is about 16777215.

He have to delete all users with high user_id, or change the id to lower.

just stick this in a custom page

[syntaxhighlighter brush=php,first-line=1,highlight=0,collapse=false,html-script=false]<?php
$users = dbquery("SELECT user_name FROM " . DB_USERS . " ORDER BY user_id";
$count = 1;
while ($user = dbarray($users)) {
dbquery("UPDATE " . DB_USERS . " SET user_id = " . $count . " WHERE user_name = '" . $user['user_name'] . "'";
$count++;
}

dbquery("ALTER TABLE " . DB_USERS . " AUTO_INCREMENT = " . $count);

$users = dbquery('SELECT user_id,user_name FROM ' . DB_USERS . ' ORDER BY user_id';
print "<table>";
while ($user = dbarray($users)) {
print "<tr><td>" . $user['user_name'] . "</td><td>" . $user['user_id'] . "</td></tr>";
}
print "</table>";
?>[/syntaxhighlighter]

and then preview the page. Should list all the user names and their new ids.

Oh LOL :D, I started writing the code got a little busy and when i came back didn't checked for new posts.
Well, deleted mine yours looks cleaner :).

To be honest, he should have a bit more to the code to make sure that he isn't trying to double insert the primary key... buuut there shouldn't be a problem since it should be sorting by user_id, ascending by default, so there shouldn't be any overlap. But making assumptions and all that isn't really a good idea when coding, I've learned =D

In fact, added the order by clause which should take care of that =D

problem would be if these users posted threads etc, with this mod the connection between user and thread is gone

You're right, I didn't even think of that.

This should help with cleaning up the orphans, but I don't know if I missed any tables. But at least you can see how to easily add a table and column that needs to be updated. This is assuming of course he hadn't gotten around to running the code yet, 'cause if he did the links are well and truly gone and he will have to do it manually =(

[syntaxhighlighter brush=php,first-line=1,highlight=0,collapse=false,html-script=false]<?php
$users = dbquery("SELECT user_name,user_id FROM " . DB_USERS . " ORDER BY user_id";
$count = 1;
$tables = array(
array("table" => DB_DOWNLOADS, "column" => "download_user",
array("table" => DB_FORUM_POLL_VOTERS, "column" => "forum_vote_user_id",
array("table" => DB_MESSAGES, "column" => "message_to",
array("table" => DB_MESSAGES, "column" => "message_from",
array("table" => DB_PHOTOS, "column" => "photo_user",
array("table" => DB_PHOTO_ALBUMS, "column" => "album_user",
array("table" => DB_POLL_VOTES, "column" => "vote_user",
array("table" => DB_POSTS, "column" => "post_author",
array("table" => DB_RATINGS, "column" => "rating_user",
array("table" => DB_SUBMISSIONS, "column" => "submit_user",
array("table" => DB_SUSPENDS, "column" => "suspended_user",
array("table" => DB_THREADS, "column" => "thread_author",
array("table" => DB_THREAD_NOTIFY, "column" => "notify_user"
);
while ($user = dbarray($users)) {
dbquery("UPDATE " . DB_USERS . " SET user_id = " . $count . " WHERE user_name = '" . $user['user_name'] . "'";
foreach ($tables as $table) {
dbquery("UPDATE " . $table['table'] . " SET " . $table['column'] . " = " . $count . " WHERE " . $table['column'] . " = " . $user['user_id']);
}
$count++;
}

dbquery("ALTER TABLE " . DB_USERS . " AUTO_INCREMENT = " . $count);

$users = dbquery('SELECT user_id,user_name FROM ' . DB_USERS . ' ORDER BY user_id';
print "<table>";
while ($user = dbarray($users)) {
print "<tr><td>" . $user['user_name'] . "</td><td>" . $user['user_id'] . "</td></tr>";
}
print "</table>";
?>[/syntaxhighlighter]