|
PRoblem: New user gets userid=16777215
|
| Zidane55 |
Posted on 09-03-2012 09:49
|
Member
Posts: 93
Joined: 20/07/2011
|
Hello. When a new user registres on my site, he becomes the userid = 16777215 every time.
Does anyone know why this happens?
When a new user activates his account he get this message "your account is activated ... Duplicate entry '16777215' for key 'PRIMARY'
Can anyone help? |
| |
|
|
| Korcsii |
Posted on 09-03-2012 10:49
|
Member
Posts: 132
Joined: 22/01/2006
|
Huh, you have a big site! 
You should go to phpmyadmin, to the users table, and change user_id column's type from MEDIUMINT to INT (length might be 10 or so).
And well, you have to go trough all tables, and check, if there is any field where user's id is used. Those should be also changed. For example post_author in the posts table.
|
| |
|
|
| PolarFox |
Posted on 09-03-2012 12:36
|
Admin
Posts: 1503
Joined: 26/08/2008
|
Do you have over 16b users?
|
| |
|
|
| smokeman |
Posted on 09-03-2012 12:38
|
Veteran Member
Posts: 960
Joined: 23/06/2006
|
No no - he have 15 users. He asked for this on my site too.
I too thought it was because the limit of the type mediumint(8) whish has limit 16777215.
But there's 15 users - so I don't know what causes this.
|
| |
|
|
| mawe4585 |
Posted on 09-03-2012 12:47
|
Junior Member
Posts: 46
Joined: 01/02/2008
|
maybe a defect of the mysql table so that the auto-inc counter is corrupt? |
| |
|
|
| smokeman |
Posted on 09-03-2012 12:52
|
Veteran Member
Posts: 960
Joined: 23/06/2006
|
I told him to repair the table too - that did not fix it.
|
| |
|
|
| Moregelen |
Posted on 09-03-2012 15:53
|
Junior Member
Posts: 37
Joined: 25/06/2007
|
Has he tried clearing the table completely? So that it is completely remade?
dbquery('TRUNCATE TABLE ' . DB_USERS);
(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.
Oooooh.. is that what Deep Corruption does? Ooopsie...
.. so, how do I heal without Holy Radiance spam anyway? I forgot...
|
| |
|
|
| Zidane55 |
Posted on 09-03-2012 15:57
|
Member
Posts: 93
Joined: 20/07/2011
|
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 |
| |
|
|
| Korcsii |
Posted on 09-03-2012 22:33
|
Member
Posts: 132
Joined: 22/01/2006
|
If you have only 15 users... set all user_id to 1,2,3...15, and then try to repair the table.
|
| |
|
|
| PolarFox |
Posted on 10-03-2012 08:37
|
Admin
Posts: 1503
Joined: 26/08/2008
|
maybe a defect of the mysql table so that the auto-inc counter is corrupt?
agreed, you must fix counter, something like this
just an example, not for real use:
ALTER TABLE fusion_users AUTO_INCREMENT = _YOUR_MAXIMUM_USER_ID_
_YOUR_MAXIMUM_USER_ID_ - YOUR MAXIMUM real USER ID.
|
| |
|
|
| Zidane55 |
Posted on 10-03-2012 12:19
|
Member
Posts: 93
Joined: 20/07/2011
|
Polarfox. How should I do this exactly? |
| |
|
|
| PolarFox |
Posted on 10-03-2012 13:38
|
Admin
Posts: 1503
Joined: 26/08/2008
|
Try to use (make some backup first)
replace 100 to your max user ID
<?php
dbquery("ALTER TABLE ".DB_USERS." AUTO_INCREMENT = 100");
?>
|
| |
|
|
| Moregelen |
Posted on 10-03-2012 14:09
|
Junior Member
Posts: 37
Joined: 25/06/2007
|
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
Oooooh.. is that what Deep Corruption does? Ooopsie...
.. so, how do I heal without Holy Radiance spam anyway? I forgot...
|
| |
|
|
| PolarFox |
Posted on 10-03-2012 14:53
|
Admin
Posts: 1503
Joined: 26/08/2008
|
Right.
|
| |
|
|
| Zidane55 |
Posted on 10-03-2012 15:31
|
Member
Posts: 93
Joined: 20/07/2011
|
Okay. I have 15 user now. So I change the auto_increment field to 16 ? |
| |
|
|
| mawe4585 |
Posted on 10-03-2012 16:31
|
Junior Member
Posts: 46
Joined: 01/02/2008
|
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. |
| |
|
|
| Zidane55 |
Posted on 10-03-2012 17:15
|
Member
Posts: 93
Joined: 20/07/2011
|
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  |
| |
|
|
| JoiNNN |
Posted on 10-03-2012 20:45
|
Admin
Posts: 319
Joined: 05/02/2011
|
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.
 Code<?php
$result = dbquery("SELECT MAX(user_id) FROM ".DB_USERS);
$id = dbarray($result);
$maxid = $id['MAX(user_id)'] + 1;
$result = dbquery("ALTER TABLE ".DB_USERS." AUTO_INCREMENT = ".$maxid);
if ($result) {
echo "Success :)<br />New AUTO_INCREMENT = ".$maxid;
} else {
echo "Something went wrong :(<br />Please try again";
}
?
Edited by JoiNNN on 10-03-2012 20:50
|
| |
|
|
| Korcsii |
Posted on 10-03-2012 21:51
|
Member
Posts: 132
Joined: 22/01/2006
|
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.
|
| |
|
|
| Moregelen |
Posted on 10-03-2012 22:46
|
Junior Member
Posts: 37
Joined: 25/06/2007
|
just stick this in a custom page
<?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>";
?>
and then preview the page. Should list all the user names and their new ids.
Edited by Moregelen on 10-03-2012 23:54
Oooooh.. is that what Deep Corruption does? Ooopsie...
.. so, how do I heal without Holy Radiance spam anyway? I forgot...
|
| |
|
