Unirgy News

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.

This entry was posted on Thursday, April 22nd, 2010 at 4:46 pm and is filed under Bugs, MySQL, Tips. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.