title: Mysql: Select after Insert failing to find rows just inserted
body: <p>I'm not using transactions in my program, so I thought all database operations would be committed immediately.</p>
  
  <p>I have a php program looking for a value in a table and return the id if it is found. If it isn't found, it writes a new row. Later on it repeats and it should find the data I just inserted, but it isn't, so I get a duplicate error.</p>
  
  <p>How do I force mysql to write the data so I get a valid result back after that insert? I've tried flushing tables, but that didn't change my results.</p>
  
  <p>More details:</p>
  
  <p>I thought it was a generic mysql issue, but it seems to be related to using Cakephp's Model Query. </p>
  
  <p>I'm using the controller to parse a CSV file into multiple tables and so bind a model on the fly and use query calls to insert data into tables as I encounter it. I'm using CakePHP for the rest of the application, but the parsing ability is kind of a throw-away functionality so I didn't spend a lot of time trying to make it elegant. </p>
  
  <p>The Import model does not have an associated table, so I bind to the Tracking model manually.</p>
  
  <p>Paraphrasing the code to the relevant queries:</p>
  
  <pre><code>    $this-&gt;Import-&gt;bindModel(array('hasOne' =&gt; array('Tracking')));
  
      $insert =&lt;&lt;&lt;DONE
  INSERT INTO tracking (id, created, employee_id, client_id, borrower_name, loan_number,
                    loan_amount, activity_id, program_id, rate_id, lock_period,
                    lo_price, investor_price,  committed_price, purchase_advice_price,
                    lock_type, investor_id, time_taken, notes,audited,audit_notes,
                    import_notes, import_error, uniqueness)
                    VALUES (NULL, '%s', %d,  %d,  '%s', '%s',
                            %.2f, %d, %d,  %d, %d,
                            %.5f, %.5f, %.5f, %.5f,
                            %d, %d, %d, '%s', %d, '%s',
                            '%s', %d, %d)
  DONE;
  while ($row = fgetscsv($fh))
      # Lots of processing deleted
  
      if (! $this-&gt;_exists($row))
      {
         $sql = vsprint($insert, $row);
         $this-&gt;Import-&gt;Tracking-&gt;query($sql); # This is where I'll get a duplicate error
      }
  } # end of function
  
  # I've created a unique index on columns that should be unique and I do the query on    
  # them here.
  private _exists($mydata)
  {
         $find = "SELECT id FROM tracking WHERE created = '%s' AND employee_id = %d" AND
              activity_id = $d AND ...";
         $sql = sprintf($find, $mydata[0], $mydata[1]);
         return $this-&gt;Import-&gt;Tracking-&gt;query($sql);
  }
  </code></pre>
  
  <p>So, the issue seems to be Cakephp and whatever it is doing with model queries.</p>
  
  <p>I can probably yank this code out of Cake and use a stand alone php program, but since the I was already using Cakephp I figured I'd throw the import model into there. </p>
  
  <p><strong>Additional comment:</strong> The select is right, but I didn't transcribe it verbatim because it was long, I used ellipses to show that. It does work as I can copy/paste into a mysql session and select the data. And running it the second time, it'll find the data.</p>
  
  <p>Does it sound to anyone else that Cakephp might be wrapping the entire thing into a big transaction that doesn't get committed until you exit the controller? Wouldn't that explain why the 2nd time it runs it will find those rows? </p>
  
  <p>I guess I either need to figure out how to get Cakephp to commit those inserts or maybe I shouldn't be using Model->query for this and do it another way, or not use Cakephp for this part of my project.</p>
  
  <p>====</p>
  
  <p>Thanks!</p>