How to resolve 'Duplicate Entry' error in MySQLi

Question

I am trying to do a basic MySQL query. But when I run my code, it doesn't properly check if a record with that name already exists.

Here is my code:

            $loggedinUserID = $_SESSION['user'];
            $loggedinUserName = $_SESSION['name'];

            //check if user is logged in
            if(empty($loggedinUserID)) {header('Location: ../');}

            $servername = "localhost";
            $username = "{hidden}";
            $password = "{hidden}";

            // Create connection
            $conn = mysqli_connect($servername, $username, $password, '{hidden}');

            // Check connection
            if (!$conn) {
               die("Connection failed: " . mysqli_connect_error());
            }

            $sql = "SELECT accessId FROM users WHERE userId=$loggedinUserID";
            $final = mysqli_query($conn, $sql);
            $result = mysqli_fetch_assoc($final);
            echo $result;

            if(empty($result)) {
                $sql2 = "INSERT INTO users (`userId`, `userName`, `accessId`) VALUES ('$loggedinUserID', '$loggedinUserName', '0')";
                if(mysqli_query($conn, $sql2)) {
                    echo "Your account was created successfully, however you have not been permitted access. Please contact system admins to be granted access.";
                }
                else {
                    echo "An error occured when creating your account. --->" . mysqli_error($conn);
                }
            }

When doing this it returns the error An error occured when creating your account. --->Duplicate entry '2147483647' for key 'userId'.

I know I have a record for this user already... But I do not know why it isn't detecting that record. Please help.

INSERT IGNORE INTO users (`userId`, `userName`, `accessId`) VALUES ('$loggedinUserID', '$loggedinUserName', '0') — Antonio Abrantes, Apr 17 '20 at 19:34
where are you setting them session vars? it looks a bit backwards, user is logged in but then your adding a new user row as if its sign up. — Lawrence Cherone, Apr 17 '20 at 19:35
How is the user already logged in, with an id in the session, if you're still trying to insert it in the table? Something is way off here. — Aioros, Apr 17 '20 at 19:35
@AntonioAbrantes, think the issue is more that it should not even get there if the user exists. — Nigel Ren, Apr 17 '20 at 19:35
This uses Discord OAuth, which is how I already have an ID and username. But I want to check if I have records for them or not. — Dude2093350, Apr 17 '20 at 19:37
are you sure that there is no result ? echo $result is not properly to show an array, it would be better if print_r($result) — Antonio Abrantes, Apr 17 '20 at 19:44
Please read: [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) — Dharman, Apr 18 '20 at 11:42
It is a very bad idea to use `mysqli_error($conn);` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Apr 18 '20 at 11:42

score 2 · Answer 1 · answered Apr 17 '20 at 19:52

The reported duplicate value 2147483647 can also be expressed as 2^31-1, which also happens to be the maximum value that can be stored by a 32-bit signed integer INT type in MySQL.

I suspect the datatype of userid column is INT (just guessing), and I suspect that the INSERT statement is attempting to provide a value larger than can be stored in INT datatype. MySQL behavior is to issue a warning and truncate the value down to the largest value that can be stored, and allow the statement to proceed.

I expect we would get different error with different setting of sql_mode to return a data truncation error rather than a data truncation warning.

Note that the SELECT won't generate a warning or error, checking for the existence of a row. It's just not going to return a row.

For debugging, consider echo/printf/vardump of the value being incorporated into the SQL text. (Also consider using prepared statement with bind placeholders as a way to mitigate SQL Injection vulnerabilities.)

As a demonstration of the behavior we observe in MySQL with values larger than can be stored in INT, consider:

USE test ;
CREATE TABLE foo (id INT PRIMARY KEY) ;
-- # 0 row(s) affected

INSERT INTO foo (id) VALUES (2147483648) ;
-- # 1 row(s) affected, 1 warning(s)
-- # Warning Code : 1264
-- # Out of range value for column 'id' at row 1

SELECT id FROM foo ;
-- #          id
-- # -----------
-- #  2147483647

SELECT id FROM foo WHERE id = 2147483649 ;
-- # 0 row(s)

INSERT INTO foo (id) VALUES (2147483649) ;
-- # Error Code: 1062
-- # Duplicate entry '2147483647' for key 'PRIMARY'

DROP TABLE foo ;
-- # 0 row(s) affected

score 0 · Answer 2 · answered Apr 17 '20 at 19:50

0

'2147483647' is the maximum value for int. You need change your column userId on bigint.

answered Apr 17 '20 at 19:50

Seldo97

611
1
8
17

Changing the datatype of the column could get involved if there are foreign key references. Changing the datatype may fix the immediate problem, but that's really only kicking the can down the road til someone decides to POST a couple of forms with $_SESSION['user'] value greater than 2^63-1. Seems to me like the code could be changed so it detects values larger than can be stored in the userid column. – spencer7593 Apr 17 '20 at 20:04

How to resolve 'Duplicate Entry' error in MySQLi

2 Answers2