Escape invalid characters [#2916783]

The geofield module creates a blob field in the database and inserts binary data into the field. When dumping this data with backup_migrate the resulting file becomes very hard to import because some of the data is invalid. phpMyAdmin, for instance, offers to write binary data in hexadecimal format, which makes it easier to import.

I'd like to propose this for backup_migrate as well. I will add a patch shortly to demonstrate my idea.

Comment	File	Size	Author
#13	2916783-backup_migrate-encode-binary-data-13.patch	3.21 KB	Jorrit
#13	7.x-3.x: PHP 7.1 & MySQL 5.5, D7 17 pass PHP 7.4 & MySQL 5.7, D7 Build Successful
#8	2916783-backup_migrate-encode-binary-data-8.patch	2.99 KB	Jorrit
#8	7.x-3.x: PHP 5.3 & MySQL 5.5, D7 2 pass PHP 7.1 & MySQL 5.5, D7 Patch Failed to Apply
#3	binarydataexample.png	1.67 KB	Jorrit
#2	2916783-backup_migrate-encode-binary-data-2.patch	1.34 KB	Jorrit
#2

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

17 October 2017 at 12:39

Jorrit created an issue. See original summary.

Comment #2

Jorrit CreditAttribution: Jorrit at nCode for DOM Digital Online Media GmbH commented 17 October 2017 at 12:57

File	Size
2916783-backup_migrate-encode-binary-data-2.patch	1.34 KB

Before:

binary data

After:

0x0101000000122cb3ad0b241340794ec46c13ca4940

I noticed that destinations.db.mysql.inc and sources.db.mysql.inc have some duplicated code. I only changed sources.db.mysql.inc in the patch. If the solution is acceptable, I will copy the changes to destinations.db.mysql.inc and upload a new patch

The solution also changes how integers are written: when possible, they will not be surrounded with quotes.

Before:

'node','oa_event','0','568','632','und','0','1506668400','1506672000'

After:

'node','oa_event',0,568,632,'und',0,1506668400,1506672000

If you don't like that, I can remove it. The most important part is the binary data encoding.

Comment #3

Jorrit CreditAttribution: Jorrit at nCode for DOM Digital Online Media GmbH commented 17 October 2017 at 12:56

File	Size
binarydataexample.png	1.67 KB

A better example of what the SQL looks like before this patch.

Comment #4

Jorrit CreditAttribution: Jorrit at nCode for DOM Digital Online Media GmbH commented 17 October 2017 at 12:57

Status:

Active

» Needs review

Comment #5

RAFA3L CreditAttribution: RAFA3L commented 20 October 2017 at 18:57

Thanks Jorrit, I notice this problem with geofield just yesterday importing into another server and discover that Sequel Pro export in hexadecimal too, phpMyAdmin is not good for import/export large databases. For example with this big database when I press GO to export in phpMyadmin download the html of the export page. I'll try the patch.

Comment #6

Leeteq CreditAttribution: Leeteq commented 22 October 2017 at 16:36

Priority:

Normal

» Major

Comment #7

DamienMcKenna

NH, USA

CreditAttribution: DamienMcKenna at Mediacurrent commented 24 January 2018 at 14:23

Status:

Needs review

» Needs work

This needs some slight cleaning up to match the Drupal coding standards and comments explaining what's going on. Thanks.

Comment #8

Jorrit CreditAttribution: Jorrit at nCode for DOM Digital Online Media GmbH commented 24 January 2018 at 15:44

Status:

Needs work

» Needs review

File	Size
2916783-backup_migrate-encode-binary-data-8.patch	2.99 KB
7.x-3.x: PHP 5.3 & MySQL 5.5, D7 2 pass PHP 7.1 & MySQL 5.5, D7 Patch Failed to Apply

Done. Thanks for your attention.

Comment #9

wombatman CreditAttribution: wombatman commented 15 February 2018 at 17:42

Thank you for this. Geofield data was being incorrectly encoded by Backup and Migrate dump which was corrupting database when restored using MySQL Workbench. Applied the patch and all looks to be working again.