$this->db->affected_rows() not working

[eluser]chuckleberry13[/eluser]
Looking for some help here. I am using CI's active record class with a MySql database and found that the $this->db->affected_rows() function would return 1 everytime instead of the number of updated/affected rows. What puzzled me more was I also tried the built in php function mysql_affected_rows() and it also returned 1.

Has anybody had this problem before or would know of a solution? Thanks ahead of time for the help.

This was the code I tried and updated several rows followed by the whole table and no matter how many rows I actually updated the result was always 1.

Code:

$num_rows = $this->db->update('user',array('role'=>5));
echo 'returned value'.$num_rows.'<br />';
echo 'count results'.$this->db->count_all_results().'<br />';
echo 'affected rows'.$this->db->affected_rows().'<br />';
echo 'build in:'.mysql_affected_rows();

These were the queries that were ran against the databae

Code:

0.0004      SELECT * FROM (`ci_sessions`) WHERE `session_id` = '321c8d852a69076026a7253caf4e2b8c' AND `ip_address` = '127.0.0.1' AND `user_agent` = 'Mozilla/5.0 (Macintosh; U; Intel Mac OS X; en-US; '
0.0003      UPDATE ci_sessions SET last_activity = 1213816300, session_id = '0602b5c8cff8000ac8260381361f0173' WHERE session_id = '321c8d852a69076026a7253caf4e2b8c'
0.0003      UPDATE `user` SET `role` = 5
0.0001      SELECT COUNT(*) AS `numrows`

[eluser]smidoid[/eluser]
Have you got the Query_Cache on in MySQL? (If you're on a shared service you might have to ask the host for help.)

This caught me out in a similar fashion... when the cache is active, if you do something that doesn't actually WRITE to the tables, the update returns 0 rows affected. There might be something similar happening here.

[eluser]TheFuzzy0ne[/eluser]
I've been experiencing the same problem with MPTTree when moving a node. I'm supposed to get the results back as an array:

Code:

array($new_lft, $new_rgt, $this->db->insert_id());

For some reason it always passes 0 back, even when I call on $this->db->insert_id() directly. I assumed this was a problem with MPTTree.

EDIT: Sorry, I just realised we aren't actually talking about the same thing here...

[eluser]smidoid[/eluser]
LOL. Been there, done that.

I've relied on the update code (affected_rows) quite a bit and now I have a query cache I'm having to to pre-flight checks just in case the rows aren't changed.

Ironically, it's almost as fast to do a SELECT before UPDATE - if the query cache is operating and you get a cache hit.

Overall, the query cache can speed things up a LOT but only if your server/host has it turned on. By putting a very small cache on (about 80K) I noticed a great improvement in Expression Engine's control panel. The exact size of the cache depends on the app. and your mileage will vary.

What's really cool is that you can turn the cache OFF on a per query basis - provided you have the correct setting in the config file. Best consult the MYSQL documentation for the available options.

For performance tuning, see video here: MYSQL Performance Hints

and here for the cache discussion: Cache configuration

[eluser]Phil Sturgeon[/eluser]
One thing to remember on this subject is that if you update something and the value doesn't actually change, it will not "affect" the record.

[eluser]chuckleberry13[/eluser]
Thanks for the replies. Kind of funny you guys posted a year later on the date. Lol.

How would I got about checking if the query_cache is on or not. I was running the tests on my local dev machine.

[eluser]smidoid[/eluser]

Code:

SHOW VARIABLES LIKE 'have_query_cache';

and

Code:

SHOW VARIABLES LIKE 'query_cache_size';

must be larger than about 40K for it to work at all. This thread may help someone else now it's answered - that's great thing about search engines.

[eluser]chuckleberry13[/eluser]
Yeah I'm glad it got answered.

on my local machine

Code:

SHOW VARIABLES LIKE 'have_query_cache';

is on but

Code:

SHOW VARIABLES LIKE 'query_cache_size';

is 0.

on my host

Code:

SHOW VARIABLES LIKE 'query_cache_size';

is 33554432.

I could do more testing to see if $this->db->affected_rows() is still returning 1 now that I am using CodeIgniter 1.7.1. I believe at the time of the post I was using an older version.

[eluser]Thorpe Obazee[/eluser]
[quote author="chuckleberry13" date="1245357300"]Thanks for the replies. Kind of funny you guys posted a year later on the date. Lol.

How would I got about checking if the query_cache is on or not. I was running the tests on my local dev machine.[/quote]

Exactly a year later

[eluser]gtech[/eluser]
happy birthday!