Skip to main content

How to find serialized values in your MySQL database

· 2 min read
Stephan Hochdörfer

As outlined in the last blog post, we needed to convert serialized data into JSON format to make Magento happy. Since Magento’s Magento\Framework\DB\AggregatedFieldDataConverter implementation needs to know which database fields to convert, we had to find them. The easiest way seems to be to go through the whole database and figure out which fields contain serialized data. Doing this by hand is no fun, so I wrote a little script that helped me with that task:

<?php

$tablesAndFields = [];

$pdo = new PDO(
'mysql:host=127.0.0.1;dbname=magento',
'root',
'magento',
);

$result = $pdo->query('SHOW TABLES');
$tables = $result->fetchAll(PDO::FETCH_COLUMN);

foreach($tables as $table) {
$result = $pdo->query('DESCRIBE '.$table);
$description = $result->fetchAll();
$fields = [];
foreach ($description as $field) {
if ((!isset($field[0])) || (!isset($field[1]))) {
echo "Missing info for table '".$table."' and field '".$field."'!\n";
continue;
}

if ((strpos($field[1], 'varchar') !== false) ||
(strpos($field[1], 'text') !== false)) {
$fields[] = $field[0];
}
}

// check if the fields contain serialized data
foreach ($fields as $field) {
$result = $pdo->query('SELECT COUNT(`'.$field.'`) as cnt FROM '. $table .
'WHERE `'.$field."` like '%:{s:%' OR `".$field."` like ':\"s:' OR `".
$field."` like 'a:0:{}'");
if ($result === false) {
continue;
}

$count = $result->fetchAll();
if (is_array($count) && isset($count[0], $count[0][0]) &&
$count[0][0] > 0) {
if(!isset($tablesAndFields[$table])) {
$tablesAndFields[$table] = [];
}
$tablesAndFields[$table][] = $field;
}
}
}

var_dump($tablesAndFields);