database backup wrong

This means that BackWPup cannot backup binary data of Wordfence Security.

No answer? That means BackupWPup cannot back up binary data as of WordFence Security.

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.

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’, ”);

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' */;

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&7a?%a(t?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ìaT\”?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 7 years, 11 months ago by valedelsol.

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?

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’ */

I think the developer of BackupWPup can test it himself with Wordfence Security. For me it does not work.

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&7a‘%a(t‰s\"', 'wp-content/plugins/ultimate-faqs/Functions/Register_EWD_UFAQ_Posts_Taxonomies.php', 0, '\Z?x?gìaT\"?0G)5òE', '\Z?x?gìaT\"?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.

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??2’, ‘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’ */

Hi @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.