Re: [phpOpenTracker-User] Duplicate key insertion (bug?) with postgreSQL

I found a way to avoid the errors using a single query. It workd in
postgres but unfortunately I cannot test to see if it works with MySQL.
The new query is standard SQL so hopefully MySQL can handle it.

The current syntax of the queries is like this:

    $this->db->query(
      sprintf(
        "INSERT
           INTO %s
                (client_id,   accesslog_id,
                 document_id, timestamp,
                 entry_document)
         VALUES (%d, %d,
                 %d, %d,
                 '%d')",

        $this->config['accesslog_table'],
        $this->container['client_id'],
        $this->container['accesslog_id'],
        $this->container['document_id'],
        $this->container['timestamp'],
        $this->container['first_request'] ? 1 : 0
      )
    );

For all the INSERT query that might try to insert duplicate primary
keys, the queries should be rewritten as:

    $this->db->query(
      sprintf(
        "INSERT
           INTO %s
                (client_id,   accesslog_id,
                 document_id, timestamp,
                 entry_document)
         SELETC %d, %d,
                 %d, %d,
                 '%d'
         WHERE NOT EXISTS (
           SELECT
              NULL
           FROM
             %s
           WHERE
             client_id=%d",

        $this->config['accesslog_table'],
        $this->container['client_id'],
        $this->container['accesslog_id'],
        $this->container['document_id'],
        $this->container['timestamp'],
        $this->container['first_request'] ? 1 : 0,
        $this->config['accesslog_table'],
        $this->container['client_id']
      )
    );

I am assuming here that client_id is the primary key.

This query is concurrency safe. If will not try and insert a new record
if there is already a record with the same primary key. The is a
possible race condition, where more than one client tries to insert, in
that case onlu one insert will succeed, all the others will fail. In
this particular case and error will be logged, so we don't completely
get around the problem of logging errors, but this race condition should
be rare.

I hope this helps!

Jean-Christian Imbeault



-------------------------------------------------------
This SF.Net email sponsored by: Free pre-built ASP.NET sites including
Data Reports, E-commerce, Portals, and Forums are available now.
Download today and enter to win an XBOX or Visual Studio .NET.
http://aspnet.click-url.com/go/psa00100006ave/direct;at.asp_061203_01/01