1

I recently switched from a single MySQL installation to MySQL Galera, running on 3 nodes addressed via HAProxy. Ever since switching I do get randomly "duplicate key" errors reported which theoretically should not be possible as I do check if the dataset is already available.

e.g.:

MySQL Error executing stmt:

        INSERT INTO user_ips
            (user_id,ip) 
        VALUES
            ('444079','41.205.7.198, 176.9.19.101')


 Error No:  1062
 Error:         Duplicate entry '444079-41.205.7.198, 1' for key 'user_id'

My code looks like this:

function save_user_ip($user_id){
    global $T62, $link;
    $ip = $_SERVER['REMOTE_ADDR'];
    // do we already know that user by this ip?
    $stmt="
        SELECT ID AS ip_id
        FROM $T62
        WHERE user_id = '$user_id' AND ip = '$ip'
    ";
    $row = db_get_row2($stmt);
    $ip_id = isset($row->ip_id) ? $row->ip_id : "";

    if (!$ip_id){ // ip unknown for that user, save it
        $stmt="
            INSERT INTO $T62
                (user_id,ip) 
            VALUES
                ('$user_id','$ip')
        ";
        db_query($stmt);
        $ip_id = db_insertid($link);
    }       
    return $ip_id;
}

Running this on a test page it works as intended. On the production server under load it however does not always work. I thought about INSERT IGNORE but would rather not run an insert on all actions as writes are slower and it also triggers a warning.

Running SHOW CREATE TABLE tablename creates following output: 1146 - Table 'tablename' doesn't exist

One possible solution that seems to work at least on development cluster:

// save ip. Will ignore if already known ip
$stmt="
    INSERT IGNORE INTO $T62
        (user_id,ip) 
    VALUES
        ('$user_id','$ip')
";
#echo $stmt; exit;
db_query($stmt);
$ip_id = db_insertid($link);
// if ip already known, retrieve it
if (!$ip_id){ 
    $stmt="
        SELECT ID AS ip_id
        FROM $T62
        WHERE user_id = '$user_id' AND ip = '$ip'
    ";
    $row = db_get_row2($stmt);
    $ip_id = $row->ip_id;
}   
return $ip_id;

Not sure how this improves the situation on prod or if it makes things even worse. There might be a more elegant solution.

Thank you in advance for any help on this.

  • Run SHOW CREATE TABLE tablename; and add the output in the question. Have you tried using INSERT ... ON DUPLICATE KEY instead of running 2 queries (one to check and another to insert)? – ypercubeᵀᴹ Jan 6 '16 at 13:26
  • If the unique or primary key is (user_id,ip), you could also just use only the INSERT and simply change your application to ignore duplicate key errors (no need to do that first SELECT at all!) – ypercubeᵀᴹ Jan 6 '16 at 13:27
  • "writes are slower". True but there won't be any write when there is a duplicate error. Did you test if that route is slower or faster? – ypercubeᵀᴹ Jan 6 '16 at 13:31
  • I tried the IGNORE solution (altered question). That works, just not sure if this is better or worse. – merlin Jan 6 '16 at 16:36
2

User error.

Since when does an ip look like 41.205.7.198, 176.9.19.101?

444079-41.205.7.198, 1 implies that 41.205.7.198, 1 was what was used as the ip. That's the first 15 characters, so I guess you have VARCHAR(15). (Note: this is why the first comment was asking for SHOW CREATE TABLE.)

So, your funny IP got truncated, leading to a user-caused DUP KEY.

| improve this answer | |
  • IPv6 is upon us; switch to VARCHAR(39) CHARACTER SET ascii. – Rick James Jan 6 '16 at 16:44
  • You are right! I did not even notice this in the error message. Thank you. Do you believe that this is due to IPv6 and Varchar(39) will solve this? I am using $ip = $_SERVER['REMOTE_ADDR'] and in my case it shows ipv4 as a result. – merlin Jan 6 '16 at 16:51
  • Actually it was not an IPv6 problem, but rather a problem with added IPv4s from proxy servers and load balancers. Your solution did point me to the right direction though. Thank you. Here is a link to the related problem and solution: stackoverflow.com/questions/34639205/… – merlin Jan 6 '16 at 18:04
  • IPv6 is not the solution; it is another problem. Just giving you another head's up. – Rick James Jan 6 '16 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.