DB Backup: import error

[pdlc]

Hi,

wordpress site up to date  + woocommerce
DB Total Size is: 286.67 MB / 293,552 KB
MySQL DB Server Version: 10.6.22-MariaDB

Problem when importing my backup created with the DB Backup tool: ERROR 1064 (42000). The .sql dump is not correct, quotes are missing around some variables:
Incorrect :
INSERT INTO wp_wc_product_meta_lookup ( product_id, sku, virtual, downloadable, min_price, max_price, onsale, stock_quantity, stock_status, rating_count, average_rating, total_sales, tax_status, tax_class, global_unique_id )
VALUES ( 17, ‘TSANJFRO’, 0, 0, 4.2000, 4.2000, 0, 45, ‘instock’, 0, 0.00, 1537, ‘taxable’, ”, ” );

Correct :
INSERT INTO wp_wc_product_meta_lookup (product_id, sku, virtual, downloadable, min_price, max_price, onsale, stock_quantity, stock_status, rating_count, average_rating, total_sales, tax_status, tax_class, global_unique_id) VALUES (17, ‘TSANJFRO’, 0, 0, 4. 2000, 4.2000, 0, 45, ‘instock’, 0, 0.00, 1537, ‘taxable’, ”, ”),

How can I solve this problem? (apart from making backups with phpmyadmin)
best regard,

Monica

[AITpro Admin]

Yeah that is odd. The BPS DB Backup code adds single quotes to all DB Values when it writes the .sql backup file.  Use PhpMyAdmin and check your database to see what values are in wp_wc_product_meta_lookup database table. My guess is the values have some additional characters like unescaped double quotes. Let me know what you see.

[AITpro Admin]

I took a look at a website that I manage that has WooCommerce on it and checked the wc_product_meta_lookup database table and see standard number values without any unusual characters. So the BPS DB backup code is not handling these number values correctly. Will need to fix this. For now just do a PhpMyAdmin dump/backup.

[pdlc]

Thank you for your reply.
In the PhpMyAdmin dump, there are single quote around the name of the table ‘wp_wc_product_meta_lookup’ and around its fields. In BPS Pro’s dump, however, the single quote around the fields are absent, generating an error when the DB is imported.

BPS Pro’s DUMP is much heavier than PhpMyAdmin’s: 116 MB vs. 271 MB. In fact, when inserting values into the database, BPS Pro will write  :

INSERT INTO wp_wc_product_meta_lookup ( product_id, sku, virtual, downloadable, min_price, max_price, onsale, stock_quantity, stock_status, rating_count, average_rating, total_sales, tax_status, tax_class, global_unique_id )
VALUES ( … )

as many times as there are values to insert…

Best regards,
Monica

[AITpro Admin]

Yep, BPS DB Backup uses loops instead of the mysqldump command (what PhpMyAdmin uses) since the PHP exec() function is required to run mysqldump in PHP code, which is very dangerous and usually disabled by default on web hosts. With that said, the BPS loops can be improved to reduce DB backup size.

[pdlc]

I checked the other backups generated by BPS pro for my other websites and discovered that 2 of them show an error on import:
ERROR 1367 (22007).

[AITpro Admin]

I finally got around to doing some work on the BPS DB Backup feature and everything is working normally – single quote code characters are being added to each value. What I think might be the cause of your problem could be something to do with the Engine (InnoDB vs MyISAM) or maybe the CHARSET. Possible problem scenarios: Importing a DB Backup SQL file to an InnoDB Database or vice versa. Or maybe even exporting from a MyISAM Database in itself. All of my DB’s use the InnoDB Engine. So I cannot test that theory. This is the most likely cause of this type of problem.

I did add some new CHARSET code to BPS Pro 17.4 released on 7-8-2023 that fixed issues with character sets not being interpreted correctly, but that fixed issues with numbers being seen as scientific notation instead of regular numerals. I guess it’s possible that if the original charset was not utf8 when the DB backup was exported, then importing it as utf8 might cause this type of problem? That seems highly unlikely to me.

The only other logical thing I can think of is that maybe a plugin that optimizes your database could accidentally strip out single quote characters from DB values during a DB export. That also seems highly unlikely to me.

[Author]

Posts