Mysql/PDO Integrity Violation Duplicate Primary - although nothing already in table

Question

I've already looked at SQL primary key constraint although record does not exist which of all the questions on SO seems closest to my problem but isn't the same.

I've no doubt I'm probably doing something stupid but here goes:

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.

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).

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.

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.

  1 <?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->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 20
 21         // connect to target
 22         $target = new PDO($target_dsn, $target_username, $target_password);
 23         $target->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 24
 25         //TODO Generalise this statement to all database types.
 26         $stmt = $source->prepare("SELECT * FROM sqlite_master WHERE type='table';");
 27         $stmt->execute();
 28
 29         // get all tables
 30         while($tablerow = $stmt->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->closeCursor();
 37
 38         // for each table, load data
 39         foreach($transfer_data as $tablename => $void)
 40         {
 41                 $stmt = $source->prepare("SELECT * FROM $tablename;");
 42                 $stmt->execute();
 43                 // load data row at a time
 44                 while($datarow = $stmt->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 => $void)
 53                                 {
 54                                         $t_data[] = $colname;
 55                                 }
 56                                 $table_data[$tablename] = $t_data;
 57                         }
 58                 }
 59                 $stmt->closeCursor();
 60                 echo "Read $tablename\n";
 61         }
 62
 63         //start a transaction (if driver supports transactions / if not then this is noop)
 64         $target->beginTransaction();
 65         // for each table clear existing data and insert copied data
 66         foreach($table_data as $tablename => $columns)
 67         {
 68                 // not using an empty/truncate because mysql and possibly others autocommit
 69                 $stmt = $target->prepare("DELETE FROM $tablename;");
 70                 $stmt->execute();
 71                 $stmt->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 => $column)
 76                 {
 77                         $columns[$k] = ':'.$column;
 78                 }
 79                 // using named placeholders so order doesn't matter
 80                 $querystr = $querystr.join(", ",$columns).");";
 81                 $stmt = $target->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 => $rowvalue)
 88                         {
 89                                 $stmt->bindParam(':'.$rowname, $rowvalue);
 90                         }
 91                         $stmt->execute();
 92                         $stmt->closeCursor();
 93                         $rowcount++;
 94                 }
 95                 echo "Written $rowcount rows to $tablename\n";
 96         }
 97         $target->commit();
 98
 99 }
100 catch (PDOException $e)
101 {
102         echo 'PDO Error: '.get_class($e).' - '.$e->getMessage()."\n";
103         echo 'Query String was: '.$querystr."\nData:\n";
104         var_export($transfer_data[$tablename]);
105         if($target->inTransaction()){
106                 $target->rollBack();
107         }
108 }
109

Now I have a table in my target database which is:

+-------------------+------------------+------+-----+---------+----------------+
| 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    |                |
+-------------------+------------------+------+-----+---------+----------------+

The output is:

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 =>
  array (
    'channel_id' => '1',
    'channel_parent_id' => '0',
    'server_id' => '1',
  ),
  1 =>
  array (
    'channel_id' => '24',
    'channel_parent_id' => '0',
    'server_id' => '1',
  ),
  2 =>
  array (
    'channel_id' => '34',
    'channel_parent_id' => '0',
    'server_id' => '1',
  ),

Answer 1

Glad you resolved this. However, this is meant to address the reason why bindParam() didn't work for you. It is not a bug, it works this way by design.

As per the docs:

Binds a PHP variable to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement. Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

(emphasis mine)

Considering the above, this:

 87                         foreach($rowdata as $rowname => $rowvalue)
 88                         {
 89                                 $stmt->bindParam(':'.$rowname, $rowvalue);
 90                         }

...would bind every parameter to $rowvalue by reference which, at the time of query execution, will always be 1 (the last element of $rowdata)

The way to make it work using bindParam() would be something like:

 87                         foreach($rowdata as $rowname => $rowvalue)
 88                         {
 89                                 $stmt->bindParam(':'.$rowname, $rowdata[$rowname]);
 90                         }

...or, maybe, even:

 87                         foreach($rowdata as $rowname => &$rowvalue)
 88                         {
 89                                 $stmt->bindParam(':'.$rowname, $rowvalue);
 90                         }

...so that each parameter would reference the corresponding array element.

An alternative, as quoted above, would be bindValue() which binds parameters by value rather than by reference. Meaning that the parameter would be evaluated at the time bindValue() is called and not at the time it is actually needed (ie query execution):

 87                         foreach($rowdata as $rowname => $rowvalue)
 88                         {
 89                                 $stmt->bindValue(':'.$rowname, $rowvalue);
 90                         }

Of course, the other alternative is feeding execute() with an array of parameters, which lets execute() resolve the binding part (thus my personal favorite!).

Answer 2

Print your data as you are trying to insert it in the loop. If you are in a transaction, and try to insert 2 records with the same primary key in the same transaction, you'll get the error on the second one, and the table will stay empty if you roll back.

Disabling the auto_increment is not necessary in MySQL. Explicit pk values on inserts should just advance the id counter for the next insert that isn't explicit.

Answer 3

I'm pretty sure setting an insert value for an auto_increment field will fail if the value you are attempting to manually insert is the same as the number the auto increment is on. You can ignore the auto_increment field when inserting except to get the last_insert_id()

It's auto_increment for a reason ;-). Let MySQL set it.