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
";
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
";
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
";
96 }
97 $target->commit();
98
99 }
100 catch (PDOException $e)
101 {
102 echo 'PDO Error: '.get_class($e).' - '.$e->getMessage()."
";
103 echo 'Query String was: '.$querystr."
Data:
";
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',
),
