Magento 2.1.9 upgrade to 2.2.0 - Error on setup:upgrade with Magento_Sales table

Question

Upgrading magento from 2.1.9 to 2.2.0 and during the automatic bin/magento setup:upgrade process it runs all upgrades fine until it reaches the magento_sales table where it produces this error.

There are around another 500 id's within the error but I have removed them for the purpose of keeping the post short.

I had migrated sales, customer, product data from our Magento Enterprise existing live site to magento 2.1 but not had this issue when doing subsequent updates.

Module Magento_Widget:

Module Magento_Sales:

Upgrading data.. Error converting field product_options in table sales_order_item where item_id= 2, 3, 5, 7, 10, 13, 17, 21, 26, 43, 46, 48, 69, 70, 71, 76, 81, 82, 91, 98, 101, 105, 108, 111, 116, 117, 122, 123, 127, 131, 135, 139, 141, 144, 145, 154, 164, 166, 171, 172, 173, 7659, 7660, 7671, 7672, 7681 using Magento\Sales\Setup\SerializedDataConverter. Fix data or replace with a valid value.

Failure reason: 'Unable to unserialize value.'

Any information on how to resolve would be much appreciated

Answer 1

I had a similar issue:

Error converting field `info` in table `magento_logging_event` where `log_id`=3 using Magento\Logging\Setup\ObjectConverter.

Turned out that a long serialized value in magento_logging_event was truncated and could not be unserialized. Deleting the row in question solved the problem. Of course, deleting rows with errors is acceptable for logs but not for sales tables!

Answer 2

Setting the field contents to NULL solved my issue.

UPDATE `sales_order_item` SET `product_options` = NULL WHERE `product_options` = '';

UPDATE `sales_order_payment` SET `additional_information` = NULL WHERE `additional_information` = '';

UPDATE `sales_payment_transaction` SET `additional_information` = NULL WHERE `additional_information` = '';

UPDATE `catalog_eav_attribute` SET `additional_data` = NULL WHERE `additional_data` = '';

Answer 3

Setting the field contents to NULL solved my issue.

Answer 4

The product_options column in the table sales_order_item should store valid serialized object as a string. This error means that some of values invalid. To resolve this issue select all values specified in the error message and check them using php:

unserialize($value);

where in the $value one of the values in which setup script detect error. This way you can get error like this:

Notice: unserialize(): Error at offset 14 of 33 bytes

Then just check you string, correct it and insert manually in the row from where it was taken (see ids in error message).

You can get more info about serialization here (php.net) or here

PS: setup script converts serialized values to the JSON type since Magento version 2.2.0.

Answer 5

Have you had any luck with this? The commonality I've noticed is on the item ids listed, the product_options field is blank.

Answer 6

The problem is with field metadata containing '' instead NULL To identify fields with issue: SELECT * FROM url_rewrite where metadata='' ;

and to change value '' with NULL use: UPDATE url_rewrite SET metadata=NULL WHERE metadata='';

If multiple schemas include your schema name UPDATE something.url_rewrite