Strange behavior with findFirst

Question

Strange behavior with findFirst

Luckas Frigo Furtado Sep '14

Created Sep '14	Last Reply Sep '14	Replies 3	Views 922	Votes 0

Luckas Frigo Furtado 964

Sep '14

Hi, sorry for posting this here, but I didin't find an answer for this behavior anywhere. This may be DB related, but again, I couldn't find a clue.

I have the following code inside a while block, where $row is an associative array containing the data to be inserted/updated:

    if(isset($row['ext_id'])) {
        $entity = Robot::findFirst("ext_id='{$row['ext_id']}'");
        if(!$entity){
            $entity = new Robot();
        } 
        $entity->save($row);
    }

The problem is that if I already have a row in the DB with ext_id column ending in 'eu' (e.g. ext_id=ABCeu), when I try to insert the row ending in 'fT' (e.g. ext_id="ABCfT") it overwrites that one. Someway, Phalcon is retrieving both as they are the same. I'm telling this because every time I do

    $entity = $model::findFirst("ext_id='ABCfT'");

I get

    echo $entity->ext_id; //prints 'ABCeu'

There're 3 things I have to make notice of:

The ext_id column uses utf8_bin collation because of its case-sensitive capabilities, while the whole database uses utf8_general_ci. I've tried changing the column collation and the results are the same.
This only happens on Phalcon. If I try to run the query (SELECT * FROM robot WHERE ext_id="ABCfT") it returns no results.
This one is not the only case. This is actually a pattern. Every time I use a substring formed by 'f' plus a uppercase letter (e.g. 'T'), it will get the result as if it was 'e' plus the lowercase letter that follows the uppercase one (in this case 'u')

Hope I had made myself clear. Thanks in advance.

Ps.: I'm using Phalcon 1.3.2 / PHP 5.4.16 / MySQL 5.5.37

Dylan Anderson · Answer 1 · 2014-09-15T12:20:21-07:00

Ya - weird. Try turning on query logging for your database, and see what query actually gets sent to MySQL. Sometimes that provides insight. Phalcon has functionality for enabling logging of queries, but I find it easier to just turn on query logging for MySQL, then tail -f that file.

By the way, very good, clear post. Thought I'd mention it because such clarity is rare.

Luckas Frigo Furtado · Answer 2 · 2014-09-17T11:51:42-07:00

Hi, @quasipickle. Thanks for the reply.

I turned the logging on and here is the result:

SELECT `robot`.`id`, `robot`.`last_update`, `robot`.`type`, `robot`.`ext_id`, `robot`.`name`, `robot`.`info` FROM `robot` WHERE `robot`.`ext_id` = '001G000000oi1eu' LIMIT 1
UPDATE `robot` SET `ext_id` = '001G000000oi1fT', `name` = 'ICEX' WHERE `id` = 21

Just to remember, that's what I do:

$entity = Robot::findFirst("ext_id='{$row['ext_id']}'");
$entity->save($row);

If I print the $row["ext_id"] (before or after findFirst) what I get is 001G000000oi1fT. But if I print $entity->ext_id (after findFirst) I get 001G000000oi1eu. Just to note, this happens in the middle of a bulk operation (happens in 2 cases among 98), so I'm guessing Phalcon is doing some kind of caching. Unfortunatelly I don't know how to properly debug and fix it.

Dylan Anderson · Answer 3 · 2014-09-17T12:51:38-07:00

Hmm. I don't have a clear idea. You say you've changed the column collation - have you changed it to the same collation as the database? I think your connection to the database can also have a collation - maybe that's causing the problem?

Database interactivity is handled by PDO, so that should all be handled - but I'm not sure where else the problem could be.

Have you tried running the query from PHP, but not using Phalcon/PDO? Just raw mysqli*() methods? I'm not sure what that would give you to be honest, but it would possibly narrow down where the problem lies.

Luckas Frigo Furtado · Answer 4 · 2014-09-17T13:31:24-07:00

Yes, I've changed it to the same collation de whole schema is using (i.e. utf8_genarel_ci), and my DB config is like the following:

    $di->set('db', function() use (&$dbVersion, &$dbName){
        return new \Phalcon\Db\Adapter\Pdo\Mysql(array(
            "host" => "localhost",
            "username" => "my_user",
            "password" => "XXX",
            "dbname" => "my_db",
            "charset" => "utf8"
        ));
    });

Just tried running the findFirst() isolated and the result comes as it is expected.

One thing that just came to my mind is that the bulk data is ordered by the ext_id before the while block is executed.

So, is there ay chances that, instead of returning false in case findFirst() fails, the Model is giving me the last successful result? If so, is that a bug or it is the expected behaviour?

Luckas Frigo Furtado · Answer 5 · 2014-09-22T08:42:24-07:00

So, I' making sure the $entity variable is unset. I also tried running a query that I know will return no results right before every findFirst():

    Robot::findFirst("id=0");
    $entity = Robot::findFirst("ext_id='{$row['ext_id']}'");

But the last valid value still persists. Should I report this as a bug?

Thanks again for the attention.

Dylan Anderson · Answer 6 · 2014-09-24T15:42:43-07:00

The only thing I can think of is maybe the problem is with how you're saving. I never pass an array to save(), I manually set the properties, then just call save() without parameters. Maybe try that?

If not - try replicating the problem in a completely different system. If you can - yay - that'll help lend credence to your bug report. If you can't, then you're missing something (which isn't incredibly helpful, but at least you know it's something you can fix yourself).