活动记录WHERE子句中的额外合取：它们来自哪里？

Question

I'm getting a very strange database error when a CodeIgniter application I'm working with attempts a particular UPDATE operation.

The Active Record call is:

$this->db->update('eval_events',
                  array('eval_event_totalscore'=>$result['average_score'],
                        'eval_event_average_totalscore=>$result['average_score']),
                  array('eval_event_id'=>$eval_event_id));

And the reported error is:

Error Number: 1054

Unknown column 'id' in 'where clause'

UPDATE `eval_events` SET `eval_event_totalscore` = '40.0000', `eval_event_average_totalscore`
= '40.0000' WHERE `id` = '581' AND `id` = '581' AND `id` = '581' AND `id` = '581' AND `id` = 
'581' AND `id` = '581' AND `id` = '581' AND `id` = '581' AND `id` = '581' AND `id` = '581' AND 
`id` = '581' AND `id` = '581' AND `id` = '581' AND `id` = '581' AND `id` = '581' AND `id` = '581'
AND `eval_event_id` = '565'

Huh? Where the heck are all those extra conjuncts involving `id` coming from?

Clearly I'm not passing them, and my reading of CI_active_record.php has given me no clues.

Three additional pieces of information that may be relevant:

1. This failure is only happening on my development machine, as far as I can tell. The query seems to be fine on the production machine.
2. If I comment out this call to update(), the following call to update() gets corrupted in exactly the same way.
3. The value '581' is significant in the overall context of the operation that these updates are part of, but it's a key in a different table (and anyway, it's bound to a column named `pid`, not `id`).

It feels like the Active Record code has cached that `id` = '581', and something is causing it to cough the contents of that cache out into my UPDATE statement at this point.

I'll admit that I don't understand what Active Record's start_cache()/stop_cache()/flush_cache() methods are really supposed to be good for -- but it shouldn't matter, because grep -r tells me that there's no call to start_cache() anywhere in the application's codebase.

Just for grins I tried putting a call to $this->db->flush_cache() immediately before the failing update() call, but it didn't change anything.

I have no idea where to look next to try to figure this out.

Any ideas? Anyone?

Answer 1

Okay: echo, var_export() and debug_print_backtrace() to the rescue.

It turns out that there's a function that's called a total of 16 times before the function that makes the failing update() call. And 16 just happens to be the number of extra `id` = '581' conjuncts in the bad UPDATE statement.

And in that earlier function is the following code (I didn't write any of this garbage, BTW):

$this->db->where('id',$pid);  // <=== WTF???
$sql = "SELECT id FROM project_score WHERE pid=$pid AND uid=$uid AND scoretype=1";
$result = $this->db->query($sql)->row_array();

What's wrong with this picture?

Well, apart from the dubious choice of using Active Record at all,* the Active Record query() method doesn't use anything that's been squirreled away by prior calls to where().

Consequently, that queue of 16 bogus conditions is still hanging around, waiting to attach itself to the first unsuspecting update() (or select(), or whatever) call that comes along.

Why doesn't this also happen on the production system?

Well, on my development system, I temporarily commented out something else (that I didn't really care about) that was failing because of some missing config in my local PHP -- which I didn't feel like rebuilding right then.

Apparently whatever I commented out contained an Active Record call that the got the queue of 16 conditions foisted off on it -- but they happened to be benign for that particular call.

Sheesh!

*So whose idea was this "Active Record" travesty, anyway?

How is it a good idea to have functions like where() that queue things up on a global object? Wouldn't a better design have been to make each SQL statement a different object, so that mistakes made while constructing one couldn't corrupt another in an entirely different part of the application?