I've created a database export of my database using my own php script.
To export the table structure I use SHOW CREATE TABLE
, to export the contents of my tables I select everything that's in the table and than create a query from it that looks like this: INSERT INTO tablename VALUES (...), (..), etc.
I save everything to a text file, first the create statements, than all the insert statements.
When I try to import the text file with phpMyAdmin I get an error:
#1062 - Duplicate entry '1' for key 'name1012_parent_id'
This is the create statement of the table:
CREATE TABLE `pages` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(11) unsigned NOT NULL DEFAULT '0',
`templates_id` int(11) unsigned NOT NULL DEFAULT '0',
`name` varchar(128) CHARACTER SET ascii NOT NULL,
`status` int(10) unsigned NOT NULL DEFAULT '1',
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`modified_users_id` int(10) unsigned NOT NULL DEFAULT '2',
`created` timestamp NOT NULL DEFAULT '2015-12-18 06:09:00',
`created_users_id` int(10) unsigned NOT NULL DEFAULT '2',
`published` datetime DEFAULT NULL,
`sort` int(11) NOT NULL DEFAULT '0',
`name1012` varchar(128) CHARACTER SET ascii DEFAULT NULL,
`status1012` int(10) unsigned NOT NULL DEFAULT '1',
`name1013` varchar(128) CHARACTER SET ascii DEFAULT NULL,
`status1013` int(10) unsigned NOT NULL DEFAULT '1',
`name1027` varchar(128) CHARACTER SET ascii DEFAULT NULL,
`status1027` int(10) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `name_parent_id` (`name`,`parent_id`),
UNIQUE KEY `name1012_parent_id` (`name1012`,`parent_id`),
UNIQUE KEY `name1013_parent_id` (`name1013`,`parent_id`),
UNIQUE KEY `name1027_parent_id` (`name1027`,`parent_id`),
KEY `parent_id` (`parent_id`),
KEY `templates_id` (`templates_id`),
KEY `modified` (`modified`),
KEY `created` (`created`),
KEY `status` (`status`),
KEY `published` (`published`)
) ENGINE=InnoDB AUTO_INCREMENT=1051 DEFAULT CHARSET=utf8;
And the insert queries:
INSERT INTO `pages` (`id`,`parent_id`,`templates_id`,`name`,`status`,`modified`,`modified_users_id`,`created`,`created_users_id`,`published`,`sort`,`name1012`,`status1012`,`name1013`,`status1013`,`name1027`,`status1027`)
VALUES
("1","0","1","en","9","2017-03-06 11:49:05","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","de","1","fi","1","nl","1"),
("2","1","2","processwire","1035","2017-02-28 14:26:06","40","2017-02-28 14:25:36","2","2017-02-28 14:25:36","6","","1","","1","","1"),
("3","2","2","page","21","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","","1","","1","","1"),
("6","3","2","add","21","2017-02-28 14:26:15","40","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","","1","","1","","1"),
("7","1","2","trash","1039","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","7","","1","","1","","1"),
("8","3","2","list","1045","2017-02-28 14:26:18","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","1","","1","","1","","1"),
("9","3","2","sort","1047","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","2","","1","","1","","1"),
("10","3","2","edit","1045","2017-02-28 14:26:18","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","3","","1","","1","","1"),
("11","22","2","template","21","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","","1","","1","","1"),
("16","22","2","field","21","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","2","","1","","1","","1"),
("21","2","2","module","21","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","2","","1","","1","","1"),
("22","2","2","setup","21","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","1","","1","","1","","1"),
("23","2","2","login","1035","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","4","","1","","1","","1"),
("27","1","29","http404","1035","2017-02-28 14:25:36","41","2017-02-28 14:25:36","3","2017-02-28 14:25:36","5","","1","","1","","1"),
("28","2","2","access","13","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","3","","1","","1","","1"),
("29","28","2","users","29","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","","1","","1","","1"),
("30","28","2","roles","29","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","1","","1","","1","","1"),
("31","28","2","permissions","29","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","2","","1","","1","","1"),
("32","31","5","page-edit","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","2","","1","","1","","1"),
("34","31","5","page-delete","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","3","","1","","1","","1"),
("35","31","5","page-move","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","4","","1","","1","","1"),
("36","31","5","page-view","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","","1","","1","","1"),
("37","30","4","guest","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","","1","","1","","1"),
("38","30","4","superuser","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","1","","1","","1","","1"),
("40","29","3","guest","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","1","","1","","1","","1"),
("41","29","3","admin","1","2017-02-28 14:26:06","40","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","","1","","1","","1"),
("50","31","5","page-sort","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","5","","1","","1","","1"),
("51","31","5","page-template","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","6","","1","","1","","1"),
("52","31","5","user-admin","25","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","10","","1","","1","","1"),
("53","31","5","profile-edit","1","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","13","","1","","1","","1"),
("54","31","5","page-lock","1","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","8","","1","","1","","1"),
("300","3","2","search","1045","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","5","","1","","1","","1"),
("301","3","2","trash","1047","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","5","","1","","1","","1"),
("302","3","2","link","1041","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","6","","1","","1","","1"),
("303","3","2","image","1041","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","7","","1","","1","","1"),
("304","2","2","profile","1025","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","5","","1","","1","","1"),
("1000","1","26","search","1025","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","2","suche","1","haku","1","","1"),
("1001","1","29","about","1","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","uber","1","tietoja","1","","1"),
("1002","1001","29","child-page-example-1","1","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","0","unterseite-seite-beispiel-1","1","alasivu-sivu-esimerkki-1","1","","1"),
("1004","1001","29","child-page-example-2","1","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","1","unterseite-beispiel-2","1","alasivu-esimerkki-2","1","","1"),
("1005","1","34","site-map","1","2017-03-06 11:49:05","41","2017-02-28 14:25:36","2","2017-02-28 14:25:36","1","sitemap","1","sivukartta","1","","1"),
("1006","31","5","page-lister","1","2017-02-28 14:25:36","40","2017-02-28 14:25:36","40","2017-02-28 14:25:36","9","","1","","1","","1"),
("1007","3","2","lister","1","2017-02-28 14:25:36","40","2017-02-28 14:25:36","40","2017-02-28 14:25:36","8","","1","","1","","1"),
("1009","22","2","languages","16","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","","1","","1","","1"),
("1010","1009","43","default","16","2017-03-09 13:55:50","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","0","","1","","1","","1"),
("1011","22","2","language-translator","1040","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","3","","1","","1","","1"),
("1012","1009","43","de","1","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","1","","1","","1","","1"),
("1013","1009","43","fi","1","2017-02-28 14:25:36","41","2017-02-28 14:25:36","41","2017-02-28 14:25:36","2","","1","","1","","1"),
("1015","3","2","recent-pages","1","2017-02-28 14:26:05","40","2017-02-28 14:26:05","40","2017-02-28 14:26:05","9","","0","","0","","1"),
("1016","31","5","page-edit-recent","1","2017-02-28 14:26:05","40","2017-02-28 14:26:05","40","2017-02-28 14:26:05","10","","1","","1","","1"),
("1017","22","2","logs","1","2017-02-28 14:26:12","40","2017-02-28 14:26:12","40","2017-02-28 14:26:12","4","","0","","0","","1"),
("1018","31","5","logs-view","1","2017-02-28 14:26:12","40","2017-02-28 14:26:12","40","2017-02-28 14:26:12","11","","1","","1","","1"),
("1019","31","5","logs-edit","1","2017-02-28 14:26:12","40","2017-02-28 14:26:12","40","2017-02-28 14:26:12","12","","1","","1","","1"),
("1022","31","5","page-edit-front","1","2017-03-01 13:01:39","41","2017-03-01 13:01:39","41","2017-03-01 13:01:39","13","","1","","1","","1"),
("1027","1009","43","nl","1","2017-03-09 12:57:23","41","2017-03-02 15:52:40","41","2017-03-02 15:52:40","3","","1","","1","","1"),
("1028","3","2","clone","1024","2017-03-02 16:04:07","41","2017-03-02 16:04:07","41","2017-03-02 16:04:07","10","","0","","0","","0"),
("1029","31","5","page-clone","1","2017-03-02 16:04:07","41","2017-03-02 16:04:07","41","2017-03-02 16:04:07","14","","1","","1","","1"),
("1030","31","5","page-clone-tree","1","2017-03-02 16:04:07","41","2017-03-02 16:04:07","41","2017-03-02 16:04:07","15","","1","","1","","1"),
("1046","22","2","jumplinks","1","2017-03-10 09:59:09","41","2017-03-10 09:59:09","41","2017-03-10 09:59:09","5","","0","","0","","0"),
("1047","31","5","jumplinks-admin","1","2017-03-10 09:59:09","41","2017-03-10 09:59:09","41","2017-03-10 09:59:09","16","","1","","1","","1"),
("1048","22","2","db-backups","1","2017-03-14 15:50:17","41","2017-03-14 15:50:17","41","2017-03-14 15:50:17","6","","0","","0","","0"),
("1049","31","5","db-backup","1","2017-03-14 15:50:17","41","2017-03-14 15:50:17","41","2017-03-14 15:50:17","17","","1","","1","","1"),
("1050","31","5","page-edit-protected","1","2017-05-18 10:42:59","41","2017-05-18 10:42:59","41","2017-05-18 10:42:59","18","","1","","1","","1");
Undoubtedly there's an insert statement with the same values. What I don't understand is why it is there. I fetch all rows from the original table and I put them in a text file. As I see it now, the original table should have duplicate entries as well but that shouldn't be possible because of the unique key.
The way I write it to a file is like this (simplified):
$tables = $this->getTables(); // returns a list of tables
$output = [
'tables' => '',
'inserts' => ''
];
foreach ($tables as => $table) {
$output['tables'][] = $this->getCreateStatement(); // uses SHOW CREATE TABLE `tableName`
}
foreach ($tables as => $table) {
foreach($this->getTableRows() as $row){
$output['inserts'][] = "INSERT INTO `$table` ($this->fieldsToSql(array_keys($row))) VALUES($this->valuesToSql($row))"
}
}
$fileContent = implode(';', $output['table']) . ' ' . implode(';', $output['inserts']);
file_put_contents('test.sql', $fileContent);
Again, this is NOT the actual code, this example above is a simplified version of my code. My code basically does the same thing.
The valuesToSql()
method works like this:
$output = [];
foreach($arr as $val) {
$output[] = '".$val."';
}
return implode(',', $output);
The problem is that your
valuesToSql()
method translatesnull
values into empty string. In MySQL the unique indexes allow multiplenull
values being present in the index values:However, empty string is not the same as
null
and triggers the duplicate key error.Furthermore, your code translates all values into strings, regardless what the real underlying value is. This may cause issues if the target MySQL server has
strict_all_tables
sql mode enabled, since a string is not a valid data for a numeric field.To handle the
null
issue, I would check if the value isnull
and handle it differently:You can add further checks to the above loop based on the type of the data and modify the output accordingly.