Title: Problem restoring database backup created using iThemes Security Last modified: August 30, 2016 --- # Problem restoring database backup created using iThemes Security * [John O](https://wordpress.org/support/users/jossoway/) * (@jossoway) * [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/) * Hi, * I have installed iThemes and set up regular database backups on a website I am working on, and wanted to test the backup was working correctly, so I exported my current database using phpMyadmin and put it aside as a manual backup, then dropped the database and tried to import the latest backup sql file created using iThemes backup. * I got an error: #1062 – **Duplicate entry for key ‘PRIMARY’** with a big list of letters and numbers in groups like \x00\x00\ which I do not understand. * I restored my current database from the manual backup I created via phpMyAdmin and everything is back to normal, so I know the database is fine. * Can anyone help me understand why the backup created using iThemes not working? Where am I going wrong? Thanks. * John * [https://wordpress.org/plugins/better-wp-security/](https://wordpress.org/plugins/better-wp-security/) Viewing 15 replies - 1 through 15 (of 27 total) 1 [2](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/page/2/?output_format=md) [→](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/page/2/?output_format=md) * [dwinden](https://wordpress.org/support/users/dwinden/) * (@dwinden) * [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/#post-6677264) * [@john](https://wordpress.org/support/users/john/) O * You will probably need to find out which table the INSERT statement is failing on. The last table created during import is probably the one you are looking for. * The iTSec backup (.sql) file structure is quite simple. It repeats 3 statements for every table: * DROP TABLE IF EXISTS `wp_table_name`; * CREATE TABLE `wp_table_name` ( `histid` int(11) NOT NULL AUTO_INCREMENT, `user_caps` varchar(70) NOT NULL DEFAULT ‘guest’, `action` varchar(255) NOT NULL, `object_type` varchar(255) NOT NULL, `object_subtype` varchar(255) NOT NULL DEFAULT ”, `object_name` varchar(255) NOT NULL, `object_id` int(11) NOT NULL DEFAULT ‘0’, `user_id` int( 11) NOT NULL DEFAULT ‘0’, `hist_ip` varchar(55) NOT NULL DEFAULT ‘127.0.0.1’,` hist_time` int(11) NOT NULL DEFAULT ‘0’, PRIMARY KEY (`histid`) ) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8; * INSERT INTO `wp_table_name` VALUES(“1″,”administrator”,”logged_out”,”User”,””,” duhh”,”1″,”1″,”127.0.0.1″,”1442939160″); * It’s one of the INSERT statements that is probably failing. Difficult to say anything more without additional info. * Once you know which table it is failing on post the above 3 commands for that table so we can have a look at it. * dwinden * [mending](https://wordpress.org/support/users/mending/) * (@mending) * [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/#post-6677372) * I got the same problem! * It’s NOT possible to import an iThems Security database backup directly to phpMyAdmin as it is if the backup was exported from phpMyAdmin itself. * This is clearly a bug. * Thread Starter [John O](https://wordpress.org/support/users/jossoway/) * (@jossoway) * [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/#post-6677386) * Hi, * It’s this table, so here are the SQL commands – I’ve not posted all the INSERT commands, just an example one, as there are a lot of records. * Also – in the IP` binary(16) NOT NULL DEFAULT field there are actually a series of slashes and zeroes that it will not let me paste here. * DROP TABLE IF EXISTS `wp_wfThrottleLog`; * CREATE TABLE `wp_wfThrottleLog` ( `IP` binary(16) NOT NULL DEFAULT ”, `startTime` int(10) unsigned NOT NULL, `endTime` int(10) unsigned NOT NULL, `timesThrottled` int(10) unsigned NOT NULL, `lastReason` varchar(255) NOT NULL, PRIMARY KEY (` IP`), KEY `k2` (`endTime`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; * INSERT INTO `wp_wfThrottleLog` VALUES(“ˇˇVåv‚”,”1433493323″,”1435570972″,”8″,” Exceeded the maximum number of page not found errors per minute for humans.”); * [dwinden](https://wordpress.org/support/users/dwinden/) * (@dwinden) * [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/#post-6677387) * [@john](https://wordpress.org/support/users/john/) O * It seems the origin of the wp_wfThrottleLog table is the WordFence plugin. * So I had a quick peek at how WordFence defines this table. (The thought behind this is to have something to compare with). Weird thing is the IP field datatype doesn’t match … WordFence (6.0.20) defines it in the lib/wfSchema.php file as: * > IP int UNSIGNED NOT NULL PRIMARY KEY * So it should be an ‘int UNSIGNED’ but according to your iTSec plugin backup file it is ‘binary(16)’ … with a weird DEFAULT value that you cannot paste into this forum … * If there is something wrong here that when corrected might have a positive impact on importing the iTSec plugin backup file I think that is well worth investigating… * Perhaps you are using an old wp_wfThrottleLog table definition which was never changed (upgraded) to the current one … * I would be interested to see a phpMyAdmin screenshot clearly displaying (Structure tab) this table’s definition in your database … * Perhaps there are even more WordFence tables with the same issue … * Are you using the latest WordFence plugin release (6.0.20) ? * dwinden * Thread Starter [John O](https://wordpress.org/support/users/jossoway/) * (@jossoway) * [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/#post-6677388) * Yes running latest version of Wordfence – version 6.0.20 * That is the table as created by Wordfence – I have not made any changes to it. * Here is a screenshot of the table in question. I have dropped it temporarily into a folder o my website rather than my client’s website, as I don’t think I can upload images here: * [Screenshot here](http://www.ragingtrifle.com/images/screenshot.png) * I hope this helps. * As I said – when I manually export and import this database, I do not get the error. Only when I try to import the iThemes backup version. * [dwinden](https://wordpress.org/support/users/dwinden/) * (@dwinden) * [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/#post-6677389) * [@john](https://wordpress.org/support/users/john/) O * Ok, it turns out WordFence executes the php\SQL command below after the table is created: * ``` $db->queryWriteIgnoreError("ALTER TABLE {$prefix}{$ip_table} MODIFY IP BINARY(16)"); ``` * It even repeats this SQL command for another 12 tables … Apparently the datatype change is required for supporting IPv6 (as of the 6.0.1 release). * So let’s get back on topic … Why is the iTSec plugin backup failing to import? * > #1062 – Duplicate entry ‘\xCB\x87\xCB\x87V\xC3\xA5v\xE2\x80\x9A’ for key ‘ > PRIMARY’ * The error suggests there is a duplicate primary key issue with one of the insert statements. * I think we need to find out which insert fails. Check the number of inserted rows in the wp_wfThrottleLog table after the import fails. That number + 1 is probably the insert statement in the import file for this table we need to take a look at. * dwinden * [dwinden](https://wordpress.org/support/users/dwinden/) * (@dwinden) * [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/#post-6677390) * I think there is some incorrect conversion taking place for the values of the ID field when the iTSec plugin creates the backup. * I’m pretty sure the value of ID is not displayed as “ˇˇVåv‚” when you look at this ID value in the original table while using phpMyAdmin … What kind of IP address is “ˇˇVåv‚” ? Makes no sense … * When you export from phpMyAdmin the resulting exported ID value looks completely different compared to what the iTSec plugin is exporting. * So the bottom line could be that the iTSec plugin is not correctly exporting binary datatype fields. * Just a couple of additional thoughts … * dwinden * [dwinden](https://wordpress.org/support/users/dwinden/) * (@dwinden) * [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/#post-6677394) * Correction … * WordFence stores IP addresses using the PHP inet_pton() function (or if inet_pton() is not available a custom replacement function), converting a human readable IP address to its packed in_addr representation. * For a v4 IP address this results in a 4 char string. For a v6 IP address this results in a 16 char string. * WordFence converts the 4 char string to a 16 char string using: * ``` $pton = str_pad(inet_pton($ip), 16, "\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\x00\x00\x00\x00", STR_PAD_LEFT); ``` * I think the iTsec plugin Backup feature dumps the binary data as plain text converting hexs to utf8 chars. It would probably be better to prevent this conversion and do something like this: * ``` if ( $data['type'] = 'binary' ) { $data['value'] = '0x' . bin2hex($data['value']); } ``` * The binary IP values are then written just like phpMyAdmin export does: * > 0x00000000000000000000ffff56c3a576 * instead of (replace % with 10 backslash zero values) : * > “%ˇˇVåv‚” * So in its current form you probably won’t be able to import the backup file. I haven’t tested my theory but it makes sense (to me) … 😉 * dwinden * Thread Starter [John O](https://wordpress.org/support/users/jossoway/) * (@jossoway) * [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/#post-6677396) * Hi, * Sorry I have been away without access to my computer. Ok, so which should I try first? * How do I implement this: * ``` if ( $data['type'] = 'binary' ) { $data['value'] = '0x' . bin2hex($data['value']); } ``` * Where in WP do I do that? * Thanks for all the help so far btw – I had no idea what to do! * Thread Starter [John O](https://wordpress.org/support/users/jossoway/) * (@jossoway) * [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/#post-6677397) * Hi, * I fixed the import! * I found record 270 was one after the error, and it is this: * [Screenshot link](http://www.ragingtrifle.com/images/record-screenshot.png) * I have added it as an image link as every time I paste it in, the content changes. * I deleted this record, and tried the import again and it worked fine. * Any idea what is wrong with this record? * Thanks for your help with this. * [dwinden](https://wordpress.org/support/users/dwinden/) * (@dwinden) * [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/#post-6677398) * Ok great, though I would not call that a fix. More cheating … haha 🙂 * Somehow that ID value already exists. It was already inserted by one of the previous 270 inserts … * Please verify whether there is an identical ID value in one of the previous 270 inserts in the backup file … * Then compare the ID values of these 2 records as stored in the original table in the database while using phpMyAdmin. * If my theory is correct. The ID hex values are different in the database but the to UTF8 converted values in the backup file are identical. This would proof binary data should not be dumped as plain text like the iTSec plugin Backup feature is doing right now. * Testing fixed Backup code as we speak … * dwinden * Thread Starter [John O](https://wordpress.org/support/users/jossoway/) * (@jossoway) * [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/#post-6677399) * You are indeed correct! * I found several records with identical primary keys because of the conversion from hex to UTF8. The ID hex values are different in the database but the UTF8 converted values in the backup file are identical. * Thread Starter [John O](https://wordpress.org/support/users/jossoway/) * (@jossoway) * [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/#post-6677416) * Hi – just to clarify, when you say: * > Testing fixed Backup code as we speak … * Does this mean there will be an update to the plugin that will resolve this issue? * Thanks. * [dwinden](https://wordpress.org/support/users/dwinden/) * (@dwinden) * [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/#post-6677419) * [@john](https://wordpress.org/support/users/john/) O * You can log a bug with iThemes [here](https://ithemes.com/security/bugs/). * Meanwhile simply replace the better-wp-security/core/modules/backup/class-itsec- backup.php file with [this](http://orcl0610.home.xs4all.nl/class-itsec-backup_510.zip) fixed release 5.1.0 copy. It’s a **fake** .zip file to facilitate the download. Once downloaded simply rename it from class-itsec-backup_510.zip to class-itsec- backup.php * Tested the fix and it should work. Would still be interested in receiving your confirmation though. * dwinden * Thread Starter [John O](https://wordpress.org/support/users/jossoway/) * (@jossoway) * [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/#post-6677420) * Hi, * Sorry I misunderstood, I thought you were an iThemes support chap! I will report the bug. * What should I write in the bug report to make sure it is clear what the problem is? * And thank you again for all your help with this. Viewing 15 replies - 1 through 15 (of 27 total) 1 [2](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/page/2/?output_format=md) [→](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/page/2/?output_format=md) The topic ‘Problem restoring database backup created using iThemes Security’ is closed to new replies. * ![](https://ps.w.org/better-wp-security/assets/icon.svg?rev=3529351) * [Kadence Security – Password, Two Factor Authentication, and Brute Force Protection](https://wordpress.org/plugins/better-wp-security/) * [Frequently Asked Questions](https://wordpress.org/plugins/better-wp-security/#faq) * [Support Threads](https://wordpress.org/support/plugin/better-wp-security/) * [Active Topics](https://wordpress.org/support/plugin/better-wp-security/active/) * [Unresolved Topics](https://wordpress.org/support/plugin/better-wp-security/unresolved/) * [Reviews](https://wordpress.org/support/plugin/better-wp-security/reviews/) ## Tags * [database](https://wordpress.org/support/topic-tag/database/) * [sql](https://wordpress.org/support/topic-tag/sql/) * 27 replies * 3 participants * Last reply from: [dwinden](https://wordpress.org/support/users/dwinden/) * Last activity: [10 years, 6 months ago](https://wordpress.org/support/topic/problem-restoring-database-backup-created-using-ithemes/page/2/#post-6677453) * Status: not resolved