CodeIgniter Forums
Random DB Error - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Random DB Error (/showthread.php?tid=11074)

Random DB Error - El Forum - 08-25-2008

[eluser]Jay Logan[/eluser]
My site lets users add there school to a database. It works about 90% of the time but sometimes, users are given this error:

Code:
A Database Error Occurred

Error Number: 1062

Duplicate entry '2147483647' for key 1

INSERT INTO `schools` (`abbr`, `name`, `city`, `state`, `type`, `host_id`, `id`) VALUES ('RNE', 'Northeast High School', 'Columbia', 'SC', 'M', '4', 'd7d0e5348a4501a8b2f635d47276')

Apparently, the system sometimes gives schools the same ID. I wondering if you guys could take a look at the code I'm using and see any obvious errors.

My base model contains:

Code:
function unique_id()
    {
        $id = uniqid(dechex(rand()), true);
        $id = explode('.', $id);
        $id[1] = dechex($id[1]);
        $id = implode('', $id);
        return $id;
    }

My school model contains:

Code:
function insert($values)
    {
        $values['id'] = $this->unique_id();
        $this->db->insert('schools', $values);
        return $values['id'];
    }

Thanks for any help you can give me.

Random DB Error - El Forum - 08-25-2008

[eluser]steelaz[/eluser]
2147483647 is max for MySQL INT. I would suggest changing the field type to BIGINT.

Random DB Error - El Forum - 08-25-2008

[eluser]Jay Logan[/eluser]
Yes, it has always been set to BIGINT(11). Thanks for reply.

Random DB Error - El Forum - 08-25-2008

[eluser]steelaz[/eluser]
I don't think it's a coincidence that your error is showing exactly '2147483647'. Couple suggestions:

Double check all your field types.
If you're not using negative integer values, make them 'unsigned'.
Remove (11) next to BIGINT.

Random DB Error - El Forum - 08-25-2008

[eluser]Jay Logan[/eluser]
I have made your changes. Though I don't think it works properly. I tried to add another school and got this error:

Code:
A Database Error Occurred

Error Number: 1062

Duplicate entry '0' for key 1

INSERT INTO `schools` (`abbr`, `name`, `city`, `state`, `type`, `host_id`, `id`) VALUES ('WDSK', 'WOODSTOCK', 'WOODSTOCK', 'GA', 'H', 0, 'd6f406048b2ff09224875456449a')

I hit the refresh button, and got this error:

Code:
A Database Error Occurred

Error Number: 1062

Duplicate entry '60' for key 1

INSERT INTO `schools` (`abbr`, `name`, `city`, `state`, `type`, `host_id`, `id`) VALUES ('WDSK', 'WOODSTOCK', 'WOODSTOCK', 'GA', 'H', 0, 'd6f406048b2ff09224875456449a')


So it appears to be grabbing random ID numbers without checking to see if that ID already exists in the DB.

Random DB Error - El Forum - 08-25-2008

[eluser]steelaz[/eluser]
Do you currently have a function to check for existing IDs? If no, your insert function should be modified to something like this:

Code:
function insert($values)
{
    $exists = TRUE;
    while ($exists) {
        $values['id'] = $this->unique_id();
        $query = $this->db->get_where('schools', array('id' => $values['id']));
        $exists = ($query->num_rows() > 0) ? TRUE : FALSE;
    }
    $this->db->insert('schools', $values);
    return $values['id'];
}


Random DB Error - El Forum - 08-25-2008

[eluser]Jay Logan[/eluser]
I've implemented the code and added 4 schools without any problems. I will have users test it out for a while but it seems like it should work. I can't thank you enough for this.