Title: database backup wrong
Last modified: March 22, 2017

---

# database backup wrong

 *  Resolved [valedelsol](https://wordpress.org/support/users/wordpress2075/)
 * (@wordpress2075)
 * [9 years, 1 month ago](https://wordpress.org/support/topic/database-backup-wrong/)
 * The database backup (including plugin Wordfence Security tables) does not produce
   an UTF8 SQL text file, only latin1 or ascii. So it is useless.

Viewing 12 replies - 1 through 12 (of 12 total)

 *  Thread Starter [valedelsol](https://wordpress.org/support/users/wordpress2075/)
 * (@wordpress2075)
 * [9 years, 1 month ago](https://wordpress.org/support/topic/database-backup-wrong/#post-8944845)
 * This means that BackWPup cannot backup binary data of Wordfence Security.
 *  Thread Starter [valedelsol](https://wordpress.org/support/users/wordpress2075/)
 * (@wordpress2075)
 * [9 years, 1 month ago](https://wordpress.org/support/topic/database-backup-wrong/#post-8963858)
 * No answer? That means BackupWPup cannot back up binary data as of WordFence Security.
 *  Plugin Contributor [Brandon Olivares](https://wordpress.org/support/users/cocreation/)
 * (@cocreation)
 * [9 years, 1 month ago](https://wordpress.org/support/topic/database-backup-wrong/#post-8980719)
 * Do you have the constant `DB_CHARSET` set in your wp-config.php? Setting this
   to `utf8` may help. The plugin uses the default charset if none is defined.
 *  Thread Starter [valedelsol](https://wordpress.org/support/users/wordpress2075/)
 * (@wordpress2075)
 * [9 years, 1 month ago](https://wordpress.org/support/topic/database-backup-wrong/#post-8981062)
 * I have:
    /** Database Charset to use in creating database tables. */ define(‘
   DB_CHARSET’, ‘utf8’);
 * /** The Database Collate type. Don’t change this if in doubt. */
    define(‘DB_COLLATE’,”);
 *  Plugin Contributor [Brandon Olivares](https://wordpress.org/support/users/cocreation/)
 * (@cocreation)
 * [9 years, 1 month ago](https://wordpress.org/support/topic/database-backup-wrong/#post-8982406)
 * Okay, in your sql file, what is listed as the charset? For instance, in the header
   I have this:
 * `/*!40101 SET NAMES utf8 */;`
 * And before each table I have this:
 * `/*!40101 SET character_set_client = 'utf8' */;`
 *  Thread Starter [valedelsol](https://wordpress.org/support/users/wordpress2075/)
 * (@wordpress2075)
 * [9 years, 1 month ago](https://wordpress.org/support/topic/database-backup-wrong/#post-8983892)
 * I have the same statements in my SQL backup file.
 * Here you see that binary data are not displayed correctly in the BackupWPup SQL
   file (with MySQL Workbench you can only import it as latin1):
 * DROP TABLE IF EXISTS `wp_wffilemods`;
    /*!40101 SET @saved_cs_client = @@character_set_client*/;/*!
   40101 SET character_set_client = ‘utf8’ */; CREATE TABLE `wp_wffilemods` ( `filenameMD5`
   binary(16) NOT NULL, `filename` varchar(1000) NOT NULL, `knownFile` tinyint(3)
   unsigned NOT NULL, `oldMD5` binary(16) NOT NULL, `newMD5` binary(16) NOT NULL,
   PRIMARY KEY (`filenameMD5`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET
   character_set_client = @saved_cs_client */;
 * —
    — Backup data for table `wp_wffilemods` —
 * LOCK TABLES `wp_wffilemods` WRITE;
    /*!40000 ALTER TABLE `wp_wffilemods` DISABLE
   KEYS */; INSERT INTO `wp_wffilemods` (`filenameMD5`, `filename`, `knownFile`,`
   oldMD5`, `newMD5`) VALUES (‘\0&7ª‘%a(þ‰s\”‘, ‘wp-content/plugins/ultimate-faqs/
   Functions/Register_EWD_UFAQ_Posts_Taxonomies.php’, 1, ‘\0\0\0\0\0\0\0\0\0\0\0\
   0\0\0\0\0’, ‘\Z½x¬gìªT\”ž0G)5ÒE’), (‘\0ß@uœ¥œ2¶îX綒, ‘wp-content/plugins/contact-
   form-7/modules/date.php’, 1, ‘\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0’, ‘V„­CÑC0—`Àî2g’),…
 * If I do not backup with BackupWPup but with HeidiSQL I get the same, correctly
   readable as UTF8:
 * — Exportiere Struktur von Tabelle xyz.wp_wffilemods
    DROP TABLE IF EXISTS `wp_wffilemods`;
   CREATE TABLE IF NOT EXISTS `wp_wffilemods` ( `filenameMD5` binary(16) NOT NULL,`
   filename` varchar(1000) NOT NULL, `knownFile` tinyint(3) unsigned NOT NULL, `
   oldMD5` binary(16) NOT NULL, `newMD5` binary(16) NOT NULL, `stoppedOnSignature`
   varchar(255) NOT NULL DEFAULT ”, `stoppedOnPosition` int(10) unsigned NOT NULL
   DEFAULT ‘0’, PRIMARY KEY (`filenameMD5`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 * — Exportiere Daten aus Tabelle xyz.wp_wffilemods: ~7.523 rows (ungefähr)
    /*!
   40000 ALTER TABLE `wp_wffilemods` DISABLE KEYS */; INSERT INTO `wp_wffilemods`(`
   filenameMD5`, `filename`, `knownFile`, `oldMD5`, `newMD5`, `stoppedOnSignature`,`
   stoppedOnPosition`) VALUES (_binary 0x00052637AA911325611628FE89731222, ‘wp-content/
   plugins/ultimate-faqs/Functions/Register_EWD_UFAQ_Posts_Taxonomies.php’, 0, _binary
   0x1ABD78AC67ECAA54229E30472935D245, _binary 0x1ABD78AC67ECAA54229E30472935D245,”,
   0), (_binary 0x0013DF4075189CA59C3205B6EE58E7B6, ‘wp-content/plugins/contact-
   form-7/modules/date.php’, 1, _binary 0x00000000000000000000000000000000, _binary
   0x5684AD43D143309760C0EE321B08671F, ”, 0), …
    -  This reply was modified 9 years, 1 month ago by [valedelsol](https://wordpress.org/support/users/wordpress2075/).
 *  Plugin Contributor [Brandon Olivares](https://wordpress.org/support/users/cocreation/)
 * (@cocreation)
 * [9 years, 1 month ago](https://wordpress.org/support/topic/database-backup-wrong/#post-8984031)
 * All pointers indicate that it is being saved as UTF8. It’s possible that whatever
   you’re using to view it is just garbling the characters. Have you tried importing
   it to a test database and seeing if it imports properly?
 *  Thread Starter [valedelsol](https://wordpress.org/support/users/wordpress2075/)
 * (@wordpress2075)
 * [9 years, 1 month ago](https://wordpress.org/support/topic/database-backup-wrong/#post-8984099)
 * I have imported the database BackupWPup in a test database. Import stops at Wordfence
   Security data tables with follwing error:
 * /* SQL Fehler (1062): Duplicate entry ” for key ‘PRIMARY’ */
 *  Thread Starter [valedelsol](https://wordpress.org/support/users/wordpress2075/)
 * (@wordpress2075)
 * [9 years, 1 month ago](https://wordpress.org/support/topic/database-backup-wrong/#post-8984119)
 * I think the developer of BackupWPup can test it himself with Wordfence Security.
   For me it does not work.
 *  Plugin Contributor [Brandon Olivares](https://wordpress.org/support/users/cocreation/)
 * (@cocreation)
 * [9 years, 1 month ago](https://wordpress.org/support/topic/database-backup-wrong/#post-8984346)
 * I have taken the data you gave above from HeidiSQL’s backup, and inserted it 
   into my database. I then backed up that table using BackWPup, and imported it
   back into my database. The data is precisely the same both times, which I’ll 
   demonstrate below.
 * The only difference is that HeidiSQL is casting the data to binary using _binary,
   and converting it to a hex string, whereas BackWPup escapes the data and inserts
   it as a string. Both seem to work equally from my testing.
 *     ```
       mysql> CREATE TABLE IF NOT EXISTS wp_wffilemods (
           -> filenameMD5 binary(16) NOT NULL,
           -> filename varchar(1000) NOT NULL,
           -> knownFile tinyint(3) unsigned NOT NULL,
           -> oldMD5 binary(16) NOT NULL,
           -> newMD5 binary(16) NOT NULL,
           -> stoppedOnSignature varchar(255) NOT NULL DEFAULT '',
           -> stoppedOnPosition int(10) unsigned NOT NULL DEFAULT 0,
           -> PRIMARY KEY (filenameMD5)
           -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       Query OK, 0 rows affected (0.00 sec)
   
       mysql> INSERT INTO wp_wffilemods (filenameMD5, filename, knownFile, oldMD5, newMD5, stoppedOnSignature, stoppedOnPosition) VALUES
           -> (_binary 0x00052637AA911325611628FE89731222, 'wp-content/plugins/ultimate-faqs/Functions/Register_EWD_UFAQ_Posts_Taxonomies.php', 0, _binary 0x1ABD78AC67ECAA54229E30472935D245, _binary 0x1ABD78AC67ECAA54229E30472935D245, '', 0),
           -> (_binary 0x0013DF4075189CA59C3205B6EE58E7B6, 'wp-content/plugins/contact-form-7/modules/date.php', 1, _binary 0x00000000000000000000000000000000, _binary 0x5684AD43D143309760C0EE321B08671F, '', 0);
       Query OK, 2 rows affected (0.01 sec)
       Records: 2  Duplicates: 0  Warnings: 0
   
       mysql> 
       ```
   
 * Then if I select this data it looks like this (only selecting the filenameMD5
   column for simplicity’s sake):
 *     ```
       mysql> select hex(filenameMD5) as filenameMD5 from wp_wffilemods;
       +----------------------------------+
       | filenameMD5                      |
       +----------------------------------+
       | 00052637AA911325611628FE89731222 |
       | 0013DF4075189CA59C3205B6EE58E7B6 |
       +----------------------------------+
       2 rows in set (0.00 sec)
   
       mysql> 
       ```
   
 * This is exactly what your above data looked like, so this is good.
 * After running a job on this table only, here is the file output (relevant lines
   only):
 *     ```
       --
       -- Table structure for wp_wffilemods
       --
   
       DROP TABLE IF EXISTS wp_wffilemods;
       /*!40101 SET @saved_cs_client     = @@character_set_client */;
       /*!40101 SET character_set_client = 'utf8' */;
       CREATE TABLE wp_wffilemods (
         filenameMD5 binary(16) NOT NULL,
         filename varchar(1000) NOT NULL,
         knownFile tinyint(3) unsigned NOT NULL,
         oldMD5 binary(16) NOT NULL,
         newMD5 binary(16) NOT NULL,
         stoppedOnSignature varchar(255) NOT NULL DEFAULT '',
         stoppedOnPosition int(10) unsigned NOT NULL DEFAULT '0',
         PRIMARY KEY (filenameMD5)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       /*!40101 SET character_set_client = @saved_cs_client */;
   
       --
       -- Backup data for table wp_wffilemods
       --
   
       LOCK TABLES wp_wffilemods WRITE;
       /*!40000 ALTER TABLE wp_wffilemods DISABLE KEYS */;
       INSERT INTO wp_wffilemods (filenameMD5, filename, knownFile, oldMD5, newMD5, stoppedOnSignature, stoppedOnPosition) VALUES 
       ('\0&7ª‘%a(þ‰s\"', 'wp-content/plugins/ultimate-faqs/Functions/Register_EWD_UFAQ_Posts_Taxonomies.php', 0, '\Z½x¬gìªT\"ž0G)5ÒE', '\Z½x¬gìªT\"ž0G)5ÒE', '', 0),
       ('\0ß@uœ¥œ2¶îXç¶', 'wp-content/plugins/contact-form-7/modules/date.php', 1, '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0', 'V„­CÑC0—Àî2g', '', 0);
       /*!40000 ALTER TABLE wp_wffilemods ENABLE KEYS */;
       UNLOCK TABLES;
       ```
   
 * Now if I import this file:
 *     ```
       $ mysql -h dbhost -u dbuseruser -p dbname < backup.sql 
       Enter password: 
       $
       ```
   
 * No errors. Now I’m running the same query as above:
 *     ```
       mysql> select hex(filenameMD5) as filenameMD5 from wp_wffilemods;
       +----------------------------------+
       | filenameMD5                      |
       +----------------------------------+
       | 00052637AA911325611628FE89731222 |
       | 0013DF4075189CA59C3205B6EE58E7B6 |
       +----------------------------------+
       2 rows in set (0.00 sec)
   
       mysql> 
       ```
   
 * What are you using to import your data? Is it possible that that could be messing
   with the encoding of that data? From the above I’m not seeing any issues on BackWPup’s
   end. Backing up of the data as well as importing seemed to work perfectly fine
   on my end.
 *  Thread Starter [valedelsol](https://wordpress.org/support/users/wordpress2075/)
 * (@wordpress2075)
 * [9 years, 1 month ago](https://wordpress.org/support/topic/database-backup-wrong/#post-8986035)
 * Thanks for your investigation.
 * With a newer BackWPup of database the SQL file can be loaded without stream error(
   means load with UTF8). This is a first step forward, but:
 * HeidiSQL import stops at table ‘wp_wfblockediplog’ with binary data with error‘/*
   SQL Fehler (1062): Duplicate entry ” for key ‘PRIMARY’ */’. If I use instead 
   MySQL Workbench I get the same error. Here is the detail view from HeidiSQL:
 * CREATE TABLE `wp_wfblockediplog` (
    `IP` binary(16) NOT NULL DEFAULT ‘\0\0\0\
   0\0\0\0\0\0\0\0\0\0\0\0\0’, `countryCode` varchar(2) NOT NULL, `blockCount` int(
   10) unsigned NOT NULL DEFAULT ‘0’, `unixday` int(10) unsigned NOT NULL, PRIMARY
   KEY (`IP`,`unixday`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client
   = @saved_cs_client */; — — Backup data for table `wp_wfblockediplog` —
 * LOCK TABLES `wp_wfblockediplog` WRITE;
    /*!40000 ALTER TABLE `wp_wfblockediplog`
   DISABLE KEYS */; INSERT INTO `wp_wfblockediplog` (`IP`, `countryCode`, `blockCount`,`
   unixday`) VALUES (‘\0\0\0\0\0\0\0\0\0\0ÿÿe’, ‘IN’, 1, 17222), (‘\0\0\0\0\0\0\
   0\0\0\0ÿÿ4%¨’, ‘VN’, 1, 17246), (‘\0\0\0\0\0\0\0\0\0\0ÿÿŒZ’, ‘GB’, 1, 17222),(‘\
   0\0\0\0\0\0\0\0\0\0ÿÿ31\”‘, ‘AE’, 1, 17242), (‘\0\0\0\0\0\0\0\0\0\0ÿÿ>Áˆ’, ‘RU’,
   1, 17228), (‘\0\0\0\0\0\0\0\0\0\0ÿÿ>̈’, ‘RU’, 2, 17237), (‘\0\0\0\0\0\0\0\0\
   0\0ÿÿ>åN’, ‘RU’, 1, 17236), (‘\0\0\0\0\0\0\0\0\0\0ÿÿ?_)’, ‘RU’, 1, 17219), (‘\
   0\0\0\0\0\0\0\0\0\0ÿÿ\\Q®’, ‘RU’, 1, 17220), (‘\0\0\0\0\0\0\0\0\0\0ÿÿ]Ä’, ‘RU’,
   1, 17231), (‘\0\0\0\0\0\0\0\0\0\0ÿÿ]\’#’, ‘RU’, 1, 17220), (‘\0\0\0\0\0\0\0\0\
   0\0ÿÿ]nd’, ‘RU’, 1, 17234), (‘\0\0\0\0\0\0\0\0\0\0ÿÿ]›4’, ‘RU’, 1, 17226), (‘\
   0\0\0\0\0\0\0\0\0\0ÿÿ^=š’, ‘RU’, 1, 17235), (‘\0\0\0\0\0\0\0\0\0\0ÿÿ^J’, ‘RU’,
   1, 17221), (‘\0\0\0\0\0\0\0\0\0\0ÿÿ^¨A’, ‘RU’, 1, 17223), (‘\0\0\0\0\0\0\0\0\
   0\0ÿÿahÄ’, ‘GB’, 1, 17228), (‘\0\0\0\0\0\0\0\0\0\0ÿÿäGÄ’, ‘IT’, 1, 17220), (‘\
   0\0\0\0\0\0\0\0\0\0ÿÿ^’, ‘UA’, 1, 17223), (‘\0\0\0\0\0\0\0\0\0\0ÿÿ2ò’, ‘RU’, 
   1, 17238), (‘\0\0\0\0\0\0\0\0\0\0ÿÿÆ’, ‘RU’, 1, 17235), (‘\0\0\0\0\0\0\0\0\0\
   0ÿÿUè’, ‘RU’, 1, 17238), (‘\0\0\0\0\0\0\0\0\0\0ÿÿUè’, ‘RU’, 1, 17239), (‘\0\0\
   0\0\0\0\0\0\0\0ÿÿ‰X’, ‘RU’, 1, 17226), (‘\0\0\0\0\0\0\0\0\0\0ÿÿ‰X’, ‘RU’, 2, 
   17230), (‘\0\0\0\0\0\0\0\0\0\0ÿÿ‰X’, ‘RU’, 2, 17234), (‘\0\0\0\0\0\0\0\0\0\0ÿÿ‰
   X’, ‘RU’, 1, 17240), (‘\0\0\0\0\0\0\0\0\0\0ÿÿ¬Á’, ‘RU’, 1, 17234), (‘\0\0\0\0\
   0\0\0\0\0\0ÿÿ¬Â’, ‘RU’, 1, 17224), (‘\0\0\0\0\0\0\0\0\0\0ÿÿ¬Â’, ‘RU’, 1, 17225),(‘\
   0\0\0\0\0\0\0\0\0\0ÿÿ²’, ‘LT’, 1, 17220), (‘\0\0\0\0\0\0\0\0\0\0ÿÿ*.e’, ‘RU’,
   1, 17241), (‘\0\0\0\0\0\0\0\0\0\0ÿÿ6! ‘, ‘GR’, 1, 17239), (‘\0\0\0\0\0\0\0\0\
   0\0ÿÿ9’, ‘LB’, 1, 17229), (‘\0\0\0\0\0\0\0\0\0\0ÿÿLÁÆ’, ‘KZ’, 1, 17233), (‘\0\
   0\0\0\0\0\0\0\0\0ÿÿLÛÄ’, ‘KZ’, 1, 17236), (‘\0\0\0\0\0\0\0\0\0\0ÿÿRÌE’, ‘SA’,
   1, 17220), (‘\0\0\0\0\0\0\0\0\0\0ÿÿS|™’, ‘IT’, 1, 17219), (‘\0\0\0\0\0\0\0\0\
   0\0ÿÿféÁ’, ‘IL’, 1, 17238),/* lange Abfrage (49,9 KiB), abgeschnitten bei 2.000
   Zeichen */ /* SQL Fehler (1062): Duplicate entry ” for key ‘PRIMARY’ */
 *  Plugin Contributor [Brandon Olivares](https://wordpress.org/support/users/cocreation/)
 * (@cocreation)
 * [9 years ago](https://wordpress.org/support/topic/database-backup-wrong/#post-9054729)
 * Hi [@wordpress2075](https://wordpress.org/support/users/wordpress2075/), in the
   next release, we will treat binary columns as hex strings, which should nullify
   any issue with encoding.
 * I’m going to mark this as resolved since it will be fixed in the upcoming release.

Viewing 12 replies - 1 through 12 (of 12 total)

The topic ‘database backup wrong’ is closed to new replies.

 * ![](https://ps.w.org/backwpup/assets/icon-256x256.png?rev=3236141)
 * [BackWPup – WordPress Backup & Restore Plugin](https://wordpress.org/plugins/backwpup/)
 * [Support Threads](https://wordpress.org/support/plugin/backwpup/)
 * [Active Topics](https://wordpress.org/support/plugin/backwpup/active/)
 * [Unresolved Topics](https://wordpress.org/support/plugin/backwpup/unresolved/)
 * [Reviews](https://wordpress.org/support/plugin/backwpup/reviews/)

 * 13 replies
 * 2 participants
 * Last reply from: [Brandon Olivares](https://wordpress.org/support/users/cocreation/)
 * Last activity: [9 years ago](https://wordpress.org/support/topic/database-backup-wrong/#post-9054729)
 * Status: resolved