CODEWITHSUNDEEP
mysqlsqlzend-framework
max4ever
max4ever

Reputation: 12142

select count(*) wrong result, cache or what?

i have a sql query where i count the number of rows

SELECT count(*) AS `nr` FROM `ordine` WHERE (ditta_id = '3') AND (YEAR(`data`) = '2012') AND (stato = 'approvato' or stato = 'consegnato') LIMIT 1

I update some row changing stato into 'approvato', sometimes count() gives me the right number, sometimes it gives me the old number

Here is a piece of my logs:

2012-03-23T10:42:45+01:00 DEBUG (7): Row id = 1809
2012-03-23T10:42:45+01:00 DEBUG (7): SELECT count(*) AS `nr` FROM `ordine` WHERE (ditta_id = '3') AND (YEAR(`data`) = '2012') AND (stato = 'approvato' or stato = 'consegnato') LIMIT 1
2012-03-23T10:42:45+01:00 DEBUG (7): Result = 140
...change stato into 'approvato' thus count must give me 141 next time, requery the row, check changes are saved


2012-03-23T10:44:52+01:00 DEBUG (7): Row id = 1810
2012-03-23T10:44:52+01:00 DEBUG (7): SELECT count(*) AS `nr` FROM `ordine` WHERE (ditta_id = '3') AND (YEAR(`data`) = '2012') AND (stato = 'approvato' or stato = 'consegnato') LIMIT 1
2012-03-23T10:44:52+01:00 DEBUG (7): Result = 140 ?!!! why not 141?

ditta_id, agente_id, stato are indexes

I am using Zend Framework 1.11.11 and MySQL 5.1.49-3

Upvotes: 2

Views: 353

Answers (1)

MatBailie
MatBailie

Reputation: 86765

For the row you updated, I assume one of the following to be true...

  • ditta_id is not '3'
  • YEAR(data) is not '2012'
  • stato was already 'approvato'
  • stato was already 'consegnato'
  • The new value in stato has a typo

Are you able to show us these fields before and after you change the stato field?

Upvotes: 3

Related Questions