Bug #59831 ndbinfo - counters not increasing after first query
Submitted: 31 Jan 2011 6:06 Modified: 2 Feb 2011 11:20
Reporter: John David Duncan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:7.1.6 OS:Any
Assigned to: Magnus Blåudd CPU Architecture:Any

[31 Jan 2011 6:06] John David Duncan
Description:
This is a cluster with 4 ndb nodes and 4 mysql nodes.

On mysql server 1, I run a query on ndbinfo.counters: 

 select counter_name, sum(val) from counters group by counter_name ;

+--------------+-------------+
| counter_name | sum(val)    |
+--------------+-------------+
| ABORTS       |      213837 |
| ATTRINFO     | 34928498498 |
| COMMITS      |   167175488 |
| OPERATIONS   |   906758587 |
| RANGE_SCANS  |    19795243 |
| READS        |    47803182 |
| SIMPLE_READS |    14291870 |
| TABLE_SCANS  |    39823557 |
| TRANSACTIONS |   169340279 |
| WRITES       |   364367808 |
+--------------+-------------+

Now, whenever I run this same query on this mysql server, I will get the same results (even though the server is busy running a load test):

mysql#1> select counter_name, sum(val) from counters group by counter_name ;
+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.02 sec)

+--------------+-------------+
| counter_name | sum(val)    |
+--------------+-------------+
| ABORTS       |      213837 |
| ATTRINFO     | 34928498498 |
| COMMITS      |   167175488 |  .... etc.

Note that these values are the same as the first set, even if 1 or 10 or 20 minutes have passed. 

If I go to a different mysql server and run the query, I get new values.   But then they become "stuck" on that server and do not update any more.

mysql#2> select counter_name, sum(val) from counters group by counter_name ;  select sleep(60); select counter_name, sum(val) from counters group by counter_name ;
+--------------+-------------+
| counter_name | sum(val)    |
+--------------+-------------+
| ABORTS       |      219573 |
| ATTRINFO     | 34991936326 |
| COMMITS      |   167407810 |
| OPERATIONS   |   908892657 |
| RANGE_SCANS  |    21528917 |
| READS        |    48798757 |
| SIMPLE_READS |    14751198 |
| TABLE_SCANS  |    42065757 |
| TRANSACTIONS |   169660730 |
| WRITES       |   364931312 |
+--------------+-------------+
10 rows in set (0.00 sec)

+-----------+
| sleep(60) |
+-----------+
|         0 |
+-----------+
1 row in set (1 min 0.03 sec)

+--------------+-------------+
| counter_name | sum(val)    |
+--------------+-------------+
| ABORTS       |      219573 |
| ATTRINFO     | 34991936326 |
| COMMITS      |   167407810 |
| OPERATIONS   |   908892657 |
| RANGE_SCANS  |    21528917 |
| READS        |    48798757 |
| SIMPLE_READS |    14751198 |
| TABLE_SCANS  |    42065757 |
| TRANSACTIONS |   169660730 |
| WRITES       |   364931312 |
+--------------+-------------+
10 rows in set (0.01 sec)

mysql> 

How to repeat:
n/a
[31 Jan 2011 6:23] John David Duncan
It turns out this is due to the query cache being enabled!

So, maybe not really a bug, but still "surprising" behavior.
[1 Feb 2011 13:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/130126
[1 Feb 2011 14:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/130137
[1 Feb 2011 15:05] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.22 (revid:magnus.blaudd@oracle.com-20110201145855-p71typ0fhvh7z2fe) (version source revid:magnus.blaudd@oracle.com-20110201145821-pmfqmdoi2xmreq2r) (merge vers: 5.1.51-ndb-7.0.22) (pib:24)
[2 Feb 2011 8:00] Magnus Blåudd
Pushed to 7.0.22 and 7.1.11
[2 Feb 2011 11:20] Jon Stephens
Documented as follows in the NDB-7.0.22 and 7.1.11 changelogs:

        Successive queries on the ndbinfo.counters table from the same
        SQL node returned unchanging results. To fix this issue, and to
        prevent similar issues from occurring in the future, ndbinfo
        tables are now excluded from the query cache.

Behaviour change also noted at http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-ndbinfo.html.

Closed.