Databases
StackOverflow2013
Postsdbo
PODuplicate entries in MySQL after performing a check (PHP)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODuplicate entries in MySQL after performing a check (PHP)
    primarykey
    data
    text
    <p>Lately, I have been getting some strange duplicate entries in my MySQL database. Entries in this table are <strong>inserted during a PUT request to a PHP page</strong>. The table contains 3 fields with no external references:</p> <ul> <li>manga_id (primary key; auto increment) - bigint(20);</li> <li>name - varchar(255);</li> <li>manga_cid - varchar(255).</li> </ul> <p>The PHP code is the following:</p> <pre><code>class MangaHandler { private function getMangaFromName($name) { $id = $this-&gt;generateId($name); $mangas = new Query("SELECT * FROM tbl_manga WHERE manga_cid = '" . $this-&gt;conn-&gt;escapeString($id) . "'", $this-&gt;conn); if(!$mangas || $mangas-&gt;hasError()) { logError("getMangaFromName($name): " . $this-&gt;conn-&gt;getError()); return null; } if($mangas-&gt;moveNext()) { return $mangas-&gt;getRow(); } return null; } private function addManga($name) { $manga_row = null; $error = false; $cid = $this-&gt;generateId($name); $sql = sprintf("INSERT INTO tbl_manga(name, manga_cid) VALUES ('%s', '%s')", $this-&gt;conn-&gt;escapeString($name), $this-&gt;conn-&gt;escapeString($cid)); if(!$this-&gt;conn-&gt;execute($sql)) $error = true; // some more code ... if($error) { logError("addManga($name): " . $this-&gt;conn-&gt;getError()); } return $manga_row; } public function addMangaSourceAndFollow($name, $url, $source_id, $user_id, $stick_source = false, $stick_lang = 'English') { // validate url $manga = $this-&gt;getMangaFromUrl($url, $source_id); if(!$manga) { $manga = $this-&gt;getMangaFromName($name); if(!$manga) $manga = $this-&gt;addManga($name); // ... } // ... return true; } } class MangaRestService extends CommonRestService { public function performPut($url, $arguments, $accept, $raw) { header('Content-type: application/json'); header("Cache-Control: no-cache, must-revalidate"); $json = json_decode($raw, true); // some input validation and auth $ms = new MangaHandler(); try { $ret = $ms-&gt;addMangaSourceAndFollow(null, $json['url'], $source['source_id'], $user['user_id'], $enforce == 1); // throw exception if some ret is invalid // return proper json response } catch(Exception $e) { $conn-&gt;rollback(); logError("MangaRestService.performPut($url, [" . implode("; ", $arguments) . "], $accept, $raw): " . $e-&gt;getMessage()); echo RestResponse::getSomeErrorResponse()-&gt;toJSON(); } } } $serv = new MangaRestService(); // performs some checks and invokes the appropriate GET, POST, PUT or DELETE method // in this case it's the performPut method above $serv-&gt;handleRawRequest(); </code></pre> <p>The manga name gets filtered (only alphanumeric chars, underscore and some more characters are allowed) and becomes <strong>the manga_cid (which should be unique in the table)</strong>.</p> <p><strong>The code basically checks if a manga with a specific manga_cid exists. Only if it doesn't, a new entry is inserted.</strong> I have tested the code and it works, however, after deploying the app., <strong>I have been getting entries with duplicate manga_cid (sometimes more than 2). This is a rare occurrence, as most entries remain unique.</strong> Also, I have no errors in my logs.</p> <p>Could it be that for some reason, multiple HTTP PUT requests are being executed concurrently and, since there is no synchronization, INSERT gets called multiple times? I find it very unlikely, specially because the app only lets you press the button that performs this request once before it disappears.</p> <p>I have tried using MySQL transactions but it didn't solve the problem. I know that setting the field as unique will probably allow me to avoid these duplicate entries, however I would have to perform some heavy maintenance on the database in order to remove the duplicate entries first. Although this table has a simple structure, the manga_id is referenced in several other tables. Also, I am curious as to why this is happening :-)</p> <p>Just in case, here's the Query class:</p> <pre><code>class Query extends QueryBase { function Query($query, &amp;$conn) { $this-&gt;recordset = array(); $this-&gt;has_error=0; $regs = mysqli_query($conn-&gt;getConnection(), $query); if(!$regs) { $this-&gt;has_error=1; return; } $index = 0; $this-&gt;current_index=-1; while(($row = mysqli_fetch_array($regs, MYSQL_ASSOC))) { $this-&gt;recordset[$index]=$row; $index++; } mysqli_free_result($regs); } public function moveNext() { if($this-&gt;current_index&lt;(sizeof($this-&gt;recordset)-1)) { $this-&gt;current_index++; return 1; } else return 0; } public function moveBack() { if($this-&gt;current_index&gt;=1) { $this-&gt;current_index--; return 1; } else return 0; } public function recordCount() { return sizeof($this-&gt;recordset); } public function get($field) { return $this-&gt;recordset[$this-&gt;current_index][$field]; } public function getRow() { return $this-&gt;recordset[$this-&gt;current_index]; } public function hasError() { return $this-&gt;has_error; } } </code></pre> <p>Thank you for your help.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USluz
    1. USluz
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODuplicate entries in MySQL after performing a check (PHP)
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PODuplicate entries in MySQL after performing a check (PHP)
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PODuplicate entries in MySQL after performing a check (PHP)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou could just add a unique index on the `manga_cid` column. This will prevent duplicate entries and you could catch the error on insert. Or use the mysql `update into` syntax.
      singulars
      1. PODuplicate entries in MySQL after performing a check (PHP)
      1. USJonathan Kuhn
    2. COIs this what you're using for your query class? http://code.google.com/p/class-query/
      singulars
      1. PODuplicate entries in MySQL after performing a check (PHP)
      1. USCamden S.
    3. COThe Query class was coded by myself and it directly invokes the mysqli api.
      singulars
      1. PODuplicate entries in MySQL after performing a check (PHP)
      1. USluz
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload