Magento Migration: Fixing the Magento 2.1 and Magento 2.2 serialization fields.

fix-serializer.php

<?php

$conn = mysqli_connect("HOST", "USER", "PASS", "DBNAME");
var_dump($conn->get_charset());

if (!$conn) {
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}

/* change character set to utf8 */
if (!$conn->set_charset("utf8")) {
    exit();
}

//$corruptedString = 'a:3:{s:14:"component_mode";s:4:"ebay";s:14:"payment_method";s:16:"channel_order_id";s:26:"183047553537-1784463558008";s:17:"channel_final_fee";d:2.5099999999999998;s:21:"cash_on_delivery_cost";d:0;s:12:"transactions";a:0:{}s:6:"tax_id";s:0:"";s:12:"method_title";s:15:"M2E Pro Payment";}';
//$repaired = repairSerializedArray_R($corruptedString);
//print_r($repaired);
//die;

fixSerializationData($conn, "quote_item_option", "value", "option_id");
fixSerializationData($conn, "sales_order_item", "product_options", "item_id");
fixSerializationData($conn, "sales_payment_transaction", "additional_information", "transaction_id");
fixSerializationData($conn, "sales_order_payment", "additional_information", "entity_id");
fixSerializationData($conn, "wishlist_item_option", "value", "option_id");


mysqli_close($conn);


############################## FUNCTION ENVIROMENT ##############################


/**
 * Fix data serialization in Magento 2.x
 *
 * @param $conn
 * @param $table
 * @param $field
 * @param $fieldIdx
 */
function fixSerializationData($conn, $table, $field, $fieldIdx){

    echo "<h3>Fixing the serialization of the $table [field: $field] </h3>";

    $result = $conn->query("SELECT * FROM `$table`");
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {

            if(!empty($row[$field])){
                $repaired = preg_replace_callback(
                    '/s:\d+:"(.*?)";/s',
                    function ($m) {
                        return 's:' . strlen($m[1]) . ":\"{$m[1]}\";";
                    },
                    $row[$field]
                );

                if(@unserialize($repaired) === false){
                    $repaired = repairSerializedArray_R($repaired);
                    $repaired = serialize($repaired);
                }
            }else{
                $repaired = "N;";
            }

            $sql = "UPDATE $table SET $field='$repaired' WHERE $fieldIdx=" . $row[$fieldIdx];

            if ($conn->query($sql) === TRUE) {
                echo "#" .  $row[$fieldIdx] . " - Record updated successfully<br>";
            } else {
                echo "Error updating record: " . $conn->error;
            }
        }
    } else {
        echo "0 results";
    }
}

/**
 * @param $serialized
 * @return array
 */
function repairSerializedArray($serialized)
{
    $tmp = preg_replace('/^a:\d+:\{/', '', $serialized);
    return repairSerializedArray_R($tmp); // operates on and whittles down the actual argument
}

/**************************************************************************
 * repairSerializedArray_R()
 * --------------------------------------------------------------------------
 * The recursive function that does all of the heavy lifing. Do not call directly.
 **************************************************************************/
function repairSerializedArray_R(&$broken)
{
    // array and string length can be ignored
    // sample serialized data
    // a:0:{}
    // s:4:"four";
    // i:1;
    // b:0;
    // N;
    $data = array();
    $index = null;
    $len = strlen($broken);
    $i = 0;

    while (strlen($broken)) {
        $i++;
        if ($i > $len) {
            break;
        }

        if (substr($broken, 0, 1) == '}') // end of array
        {
            $broken = substr($broken, 1);
            return $data;
        } else {
            $bite = substr($broken, 0, 2);
            switch ($bite) {
                case 's:': // key or value
                    $re = '/^s:\d+:"([^\"]*)";/';
                    if (preg_match($re, $broken, $m)) {
                        if ($index === null) {
                            $index = $m[1];
                        } else {
                            $data[$index] = $m[1];
                            $index = null;
                        }
                        $broken = preg_replace($re, '', $broken);
                    }
                    break;

                case 'i:': // key or value
                    $re = '/^i:(\d+);/';
                    if (preg_match($re, $broken, $m)) {
                        if ($index === null) {
                            $index = (int)$m[1];
                        } else {
                            $data[$index] = (int)$m[1];
                            $index = null;
                        }
                        $broken = preg_replace($re, '', $broken);
                    }
                    break;

                case 'b:': // value only
                    $re = '/^b:[01];/';
                    if (preg_match($re, $broken, $m)) {
                        $data[$index] = (bool)$m[1];
                        $index = null;
                        $broken = preg_replace($re, '', $broken);
                    }
                    break;

                case 'a:': // value only
                    $re = '/^a:\d+:\{/';
                    if (preg_match($re, $broken, $m)) {
                        $broken = preg_replace('/^a:\d+:\{/', '', $broken);
                        $data[$index] = repairSerializedArray_R($broken);
                        $index = null;
                    }
                    break;

                case 'N;': // value only
                    $broken = substr($broken, 2);
                    $data[$index] = null;
                    $index = null;
                    break;
            }
        }
    }

    return $data;
}
?>