Out of range value adjusted for column 'user_id' at row 1 ???????????

[lip9000]

Within the login script of my website, I have implemented the phpBB function that inserts a new forum user into the forum database if it does not exist. My website is integrated with my phpBB forum. I have recently updated my login page and have deleted a few users from the forum database manually, and since then, everytime a new user logs in that has not been registered in the forum gets the following error:

 

General Error

SQL ERROR [ mysql4 ]

 

Out of range value adjusted for column 'user_id' at row 1 [1264]

 

An sql error occurred while fetching this page. Please contact an administrator if this problem persists.

 

However users that have a forum id registerd previously do not get this error as the login function gets executed instead of the register function. If you are familiar with phpBB functions this is what I mean:

 

The new user register function is: bb3i_register(username,password,email);

The login function is: bb3i_login(forumid,postedpassword)

 

Is there a way to repair this database without mucking it up? Or is there a reason why I am getting that error?

 

Many thanks in advance!

[Ken2k7]

Can we see what the code you modified?

[lip9000]

I haven't modified any code as the login form points to auth.php which is where all the code for logging in, forum registration/login sits, and I haven't touched that code. What I think may have caused the problem is deleting a user manually out of the phpbb_users table, and now there is an id conflicting somewhere, here is a snippet of the login code:

 

if ($userinfo=mysql_fetch_array($qres)) {

			if ($userinfo['validated']==-1) {define('ERRMSG','Banned!');}

			if ($userinfo['validated']>0) {

				mysql_free_result($qres);

				$_SESSION['auth']=true;$_SESSION['user']=$_POST['lg_name'];$_SESSION['uid']=$userinfo['id'];

				user_setonlinestate(1);
				$GLOBALS['forumid']=$userinfo['forumid'];
				if ($GLOBALS['forumid']<=0) {
					$GLOBALS['forumid']=bb3i_register($userinfo['name'],$_POST['lg_pass'],$userinfo['email']);
					if ($GLOBALS['forumid']>0) {
						$qres=mysql_query("UPDATE lol_users SET forumid='".(int)$GLOBALS['forumid']."' WHERE `id`=".(int)$userinfo['id']);
					}
				}
				if ($GLOBALS['forumid']>0) {bb3i_login($GLOBALS['forumid'],$_POST['lg_pass']);}

				header('Location: '.(($_GET['redir']!='')?($_GET['redir']):'home.php'));die();

			} else {

				unset($_SESSION['uid']);unset($_SESSION['auth']);unset($_SESSION['user']);

				define('ERRMSG','Auth Failed!');

				include 'index.php';

			}

		}

 

You can see that if the forum id = 0, it will register a new one and insert that ID into my sites database, otherwise it will login the user into phpbb as well.

[PFMaBiSmAd]

The error means that the value being placed into the 'user_id' column exceeds the definition for that column. Either the value is incorrect or the column definition is not what you expect.

 

Identify which query is triggering the error. Find where the value that is being placed into the 'user_id' is coming from. What is the value and what is the definition of that column? If it is an autoincrement value, it could have been inadvertently set to the maximum possible value or you have simply used up all the available values and need to alter the column definition to hold a larger value.

[Ken2k7]

I don't understand this part -

if ($GLOBALS['forumid']<=0) {
     $GLOBALS['forumid']=bb3i_register($userinfo['name'],$_POST['lg_pass'],$userinfo['email']);	
     if ($GLOBALS['forumid']>0) {
          $qres=mysql_query("UPDATE lol_users SET forumid='".(int)$GLOBALS['forumid']."' WHERE `id`=".(int)$userinfo['id']);	
     }
}
if ($GLOBALS['forumid']>0) {bb3i_login($GLOBALS['forumid'],$_POST['lg_pass']);}

 

If $GLOBALS['forumid'] is <= 0, when will the if statement inside the first if statement ever be true? The answer - never!

 

I'm a bit confused at what it's doing.

[lip9000]

the function bb3i_register returns a forum id which gets assigned to $GLOBALS['forumid'],

 

apart from that I think i am getting somewhere, there are 35600 rows in the phpbb_users table, originally user_id was set to MEDIUMINT UNSIGNED, I tried changing it to INT UNSIGNED with 15 length and now instead of that error I get this error:

 

SQL ERROR [ mysql4 ]

 

Duplicate entry 'testing12' for key 2 [1062]

 

An sql error occurred while fetching this page. Please contact an administrator if this problem persists.

 

 

 

testing12 is the user I am trying to login with whom does not have a forumid yet.

[lip9000]

Ok I did a search for the user "testing12" and found an old row with that in there, so I deleted that one to solve the duplicate entry error, now when I try to login again I get the original error again:

 

SQL ERROR [ mysql4 ]

 

Out of range value adjusted for column 'user_id' at row 1 [1264]

 

An sql error occurred while fetching this page. Please contact an administrator if this problem persists.

 

UPDATE:

 

After this error I checked the phpbb_users table and saw that it inserted that user, but it did not update lol_users and set the forumid.

[Ken2k7]

Regarding this line -

$GLOBALS['forumid']=bb3i_register($userinfo['name'],$_POST['lg_pass'],$userinfo['email']);

 

1. What does bb3i_register function return?

2. Are the parameters correct? Meaning $userinfo['name'], $_POST['lg_pass'] and $userinfo['email'].

3. If you were to check the DB, does the user get registered?

[lip9000]

1. The bb3i_register function returns the id of the user that it just inserted into the forum database.

2. Parameters are correct, they all appear if i echo them out on the next page.

3. If I look at the forum database, the user does indeed get inserted with a user_id of 16777217, but the next step in the script is to insert that value into the lol_users table under forum_id.

 

I'm still getting this error though:

 

SQL ERROR [ mysql4 ]

 

Out of range value adjusted for column 'user_id' at row 1 [1264]

 

 

what does out of range usually mean?

[lip9000]

I have done a search for all fields called 'user_id' and have changed them all from MEDIUMINT ( to INT (15) and now when I login i get no error and it inserts it. The only only problem now is that it's not logging me into the forum, when I go to the forum it still says to login which is weird because the bb3i_login function is being run

[Ken2k7]

Perhaps my phpBB 3 version is out-of-date because I never saw any of those functions names before and I have worked my way around phpBB 3 a lot. Can you check how phpBB 3 calls the bb3i_login function? Maybe you need to set a SESSION or COOKIE yourself?

[lip9000]

bb3i_login function gets called in the login script for my site, and that function sits in functions.php in the phpbb folder, so within that function the session and cookie get's set. i've totally stuffed things up, because before i had this problem, users that had signed up before could login to my site and it would also log them into the phpbb forum, now it doesn't even work for them.

[Ken2k7]

Ouch! One of the reasons why you should use SVN (if you have that). Best now is to try to undo anything you did to get it so members can log in and we'll attempt from there. I'll also have to take a look at the new phpBB 3 version.

[lip9000]

I've gone way too far to undo what I've done, I don't know why it's not logging in. Here is the function that logs the user in, I'm not sure if this is part of phpBB as it is in a script outside of the phpBB folder, I think the previous owner of the site made it:

 

function bb3i_login($userid,$pass) {

	bb3i_logout();

	global $auth,$db;

	if ($userid<1) {return false;}

	$sql = 'SELECT username FROM '.USERS_TABLE." WHERE user_id = '".(int)$userid."' ";

	$result = $db->sql_query($sql);

	$row = $db->sql_fetchrow($result);

	$db->sql_freeresult($result);

	// If authentication is successful we redirect user to previous page

	//die($pass);

	$result = $auth->login($row['username'], $pass, 1, 1, 0);

	// The result parameter is always an array, holding the relevant information...

	//die(print_r($result));

	return $result['status'];

	return false;

}

[lip9000]

If anyone is an expert with this stuff and has good knowledge of phpBB's coding, I will give you $50 USD if you can fix this problem for me! I'm desperate, been pulling my hair out for a week now. Just can't see what's wrong.

[Ken2k7]

Why does that function return $result['status'] and false? return false will never run. What do you get if you var_dump($row)?

[kanenas.net]

The man has pointed you the solution from the very beginning !

The error means that the value being placed into the 'user_id' column exceeds the definition for that column. Either the value is incorrect or the column definition is not what you expect.

 

Identify which query is triggering the error. Find where the value that is being placed into the 'user_id' is coming from. What is the value and what is the definition of that column? If it is an autoincrement value, it could have been inadvertently set to the maximum possible value or you have simply used up all the available values and need to alter the column definition to hold a larger value.