Magento PayPal price mismatch at checkout

Written by Peter Davies on .

We recently discovered that the Magento API has no number formatting for data submitted through the API or dataflow process. This means that any calculated product price that has been created from a calculation (VAT include/exclude) might have more than 2 decimal places.

The resulting effect is a mismatch at checkout where tax is calculated both forwards in one case, and reverse in another, producing a possible +/- 1 penny. This discrepancy in total value causes PayPal to spit its dummy out and reject the payment, and more specifically stop the customer from checking out!

We needed a quick fix to "round" the product values to the correct value so we created the following UPDATE query in one line of SQL:

UPDATE `catalog_product_entity_decimal` SET `value`=FORMAT(`value`, 2) WHERE `value` IS NOT NULL;
# 3796 rows affected. ( Query took 0.0745 sec )

Magento's suggested solution to this issue was to click through all records, not likely on 3796 rows