Archive for the ‘Bugs’ Category

Yesterday encountered a weird bug in MySQL 5.0.90, not sure about other 5.0.x versions, but 5.1.x seems to be OK.

The issue presented itself here:

// $skus = array('A','B','123');
$_conn->fetchAll("select entity_id, sku from catalog_product_entity where sku in (?)", $skus);

Normally this would select only the records which has `sku` one of provided in the $skus array. But it seemed like it returned ALL records in the table, which was very curious.

The reason was that some of $skus were numeric, and PDO automatically converted them to numbers instead of keeping them as strings, and it confused this specific version of MySQL to have both numbers and strings in the “IN” condition, and it returned ALL records that started with alphanumeric character.

Isolated test case:

-- create table
create table test (sku varchar(255) primary key);
-- create test data
insert into test values ('a'),('b'),('c');

-- this will show ALL records with SKU starting with a letter
select * from test where sku in ('',1);

This is a very dangerous issue, which can lead not only to huge memory consumption and performance degradation, but also unwarranted updates of records that should not be updated.

My workaround was to generate the SQL condition manually:

// $skus contains all the SKUs to be selected
// $conn is the DB connection
// $table is 'catalog_product_entity' with correct prefix
$skus1 = array();
foreach ($skus as $sku) {
    $skus1[] = is_numeric($sku) ? "'".$sku."'" : $conn->quote($sku);
}
$select = $conn->select()->from($table)
    ->where('sku in ('.join(',', $skus1).')');

As MySQL 5.0.90 is still installed on many servers, make sure to account for this bug in your development.