title: Mysql/PDO Integrity Violation Duplicate Primary - although nothing already in table
body: <p>I've already looked at <a href="https://stackoverflow.com/questions/16520884/sql-primary-key-constraint-although-record-does-not-exist">SQL primary key constraint although record does not exist</a> which of all the questions on SO seems closest to my problem but isn't the same.</p>
  
  <p>I've no doubt I'm probably doing something stupid but here goes:</p>
  
  <p>I'm trying to write a script (in php) that will migrate data (no structure, it assumes structure is already done) from any given PDO database to any other given PDO database - in my case I'm testing it on sqlite3 -> mysql.</p>
  
  <p>When I run the script on my test databases I get "Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY'" which I don't quite understand because there is no data in the table (even before the script runs it's DELETE statement).</p>
  
  <p>I'm assuming it's related to the fact that the primary key is an auto_increment but I have tried setting the next increment value to something other than any value that is being inserted (think I tried setting it to 80) - made no difference.</p>
  
  <p>I've looked for a method of disabling the auto_increment for the duration of the transaction but short of altering the table before hand and then altering back it afterwards I can't think of a way - and altering the whole table just seems wrong and I didn't really want to have any DDL involved.</p>
  
  <pre><code>  1 &lt;?php
    2
    3 $abspath = dirname(__FILE__)."/";
    4
    5 $source_dsn = 'sqlite:'.$abspath.'db.sqlitedb';
    6 $source_username = null;
    7 $source_password = null;
    8 $target_dsn = "mysql:dbname=name;host=127.0.0.1";
    9 $target_username = "name";
   10 $target_password = "pass";
   11
   12 $transfer_data = array();
   13 $table_data = array();
   14
   15 try {
   16
   17         // connect to source
   18         $source = new PDO($source_dsn, $source_username, $source_password);
   19         $source-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   20
   21         // connect to target
   22         $target = new PDO($target_dsn, $target_username, $target_password);
   23         $target-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   24
   25         //TODO Generalise this statement to all database types.
   26         $stmt = $source-&gt;prepare("SELECT * FROM sqlite_master WHERE type='table';");
   27         $stmt-&gt;execute();
   28
   29         // get all tables
   30         while($tablerow = $stmt-&gt;fetch(PDO::FETCH_ASSOC))
   31         {
   32                 // TODO Generalise these statements to all database types.
   33                 $transfer_data[$tablerow['tbl_name']] = array();
   34                 $table_data[$tablerow['tbl_name']] = array();
   35         }
   36         $stmt-&gt;closeCursor();
   37
   38         // for each table, load data
   39         foreach($transfer_data as $tablename =&gt; $void)
   40         {
   41                 $stmt = $source-&gt;prepare("SELECT * FROM $tablename;");
   42                 $stmt-&gt;execute();
   43                 // load data row at a time
   44                 while($datarow = $stmt-&gt;fetch(PDO::FETCH_ASSOC))
   45                 {
   46                         // store data for later
   47                         $transfer_data[$tablename][] = $datarow;
   48                         // if we haven't gained column data yet, do so now
   49                         if(!array_key_exists($tablename,$table_data))
   50                         {
   51                                 $t_data = array();
   52                                 foreach($datarow as $colname =&gt; $void)
   53                                 {
   54                                         $t_data[] = $colname;
   55                                 }
   56                                 $table_data[$tablename] = $t_data;
   57                         }
   58                 }
   59                 $stmt-&gt;closeCursor();
   60                 echo "Read $tablename\n";
   61         }
   62
   63         //start a transaction (if driver supports transactions / if not then this is noop)
   64         $target-&gt;beginTransaction();
   65         // for each table clear existing data and insert copied data
   66         foreach($table_data as $tablename =&gt; $columns)
   67         {
   68                 // not using an empty/truncate because mysql and possibly others autocommit
   69                 $stmt = $target-&gt;prepare("DELETE FROM $tablename;");
   70                 $stmt-&gt;execute();
   71                 $stmt-&gt;closeCursor();
   72
   73                 // prepare the insert statement - we don't know how many columns so is dynamic
   74                 $querystr = "INSERT INTO $tablename (".join(", ",$columns).") VALUES (";
   75                 foreach($columns as $k =&gt; $column)
   76                 {
   77                         $columns[$k] = ':'.$column;
   78                 }
   79                 // using named placeholders so order doesn't matter
   80                 $querystr = $querystr.join(", ",$columns).");";
   81                 $stmt = $target-&gt;prepare($querystr);
   82                 //echo "Using: $querystr\n";
   83                 $rowcount = 0;
   84                 // for each row of data, bind data and execute insert statement
   85                 foreach($transfer_data[$tablename] as $rowdata)
   86                 {
   87                         foreach($rowdata as $rowname =&gt; $rowvalue)
   88                         {
   89                                 $stmt-&gt;bindParam(':'.$rowname, $rowvalue);
   90                         }
   91                         $stmt-&gt;execute();
   92                         $stmt-&gt;closeCursor();
   93                         $rowcount++;
   94                 }
   95                 echo "Written $rowcount rows to $tablename\n";
   96         }
   97         $target-&gt;commit();
   98
   99 }
  100 catch (PDOException $e)
  101 {
  102         echo 'PDO Error: '.get_class($e).' - '.$e-&gt;getMessage()."\n";
  103         echo 'Query String was: '.$querystr."\nData:\n";
  104         var_export($transfer_data[$tablename]);
  105         if($target-&gt;inTransaction()){
  106                 $target-&gt;rollBack();
  107         }
  108 }
  109
  </code></pre>
  
  <p>Now I have a table in my target database which is:</p>
  
  <pre><code>+-------------------+------------------+------+-----+---------+----------------+
  | Field             | Type             | Null | Key | Default | Extra          |
  +-------------------+------------------+------+-----+---------+----------------+
  | channel_id        | int(11)          | NO   | PRI | NULL    | auto_increment |
  | channel_parent_id | int(10) unsigned | YES  |     | NULL    |                |
  | server_id         | int(10) unsigned | NO   | MUL | NULL    |                |
  +-------------------+------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>The output is:</p>
  
  <pre><code>PDO Error: PDOException - SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1' for key 'PRIMARY'
  Query String was: INSERT INTO channels (channel_id, channel_parent_id, server_id) VALUES (:channel_id, :channel_parent_id, :server_id);
  Data:
  array (
    0 =&gt;
    array (
      'channel_id' =&gt; '1',
      'channel_parent_id' =&gt; '0',
      'server_id' =&gt; '1',
    ),
    1 =&gt;
    array (
      'channel_id' =&gt; '24',
      'channel_parent_id' =&gt; '0',
      'server_id' =&gt; '1',
    ),
    2 =&gt;
    array (
      'channel_id' =&gt; '34',
      'channel_parent_id' =&gt; '0',
      'server_id' =&gt; '1',
    ),
  </code></pre>