MySQL Bugs: #54669: Aggregator reports wrong total rows

Bug #54669	Aggregator reports wrong total rows
Submitted:	21 Jun 2010 15:16	Modified:	28 Jan 2011 23:18
Reporter:	Diego Medina	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Aggregator	Severity:	S1 (Critical)
Version:	2.2.2.1722	OS:	Any
Assigned to:	Michael Schuster	CPU Architecture:	Any

Description:
if you send the exact same json data to the aggr during one minute, it will end up sending wrong counts to the dashbaord

How to repeat:
1- Install and start the aggr
2- get the server uuid of a server you are monitoring
3- use this text file and edit the server uuid:

$ cat json-data-999.txt 
{
        "version":"1",
        "action":"query",
        "server_id":"7ce2233d-30fb-4549-b03c-9d905a1478fe",
        "thread_id":112998,
        "data":{
                "query_type":"SELECT",
                "query_text":"SELECT  name, symbol, conversion_rate FROM currencies WHERE status = 'Active' and deleted = 0",
                "query_id":2,
                "duration":166,
                "host_to":"bur02.norway.sun.com",
                "user":"sugarcrm",
                "database":"sugarcrm",
                "host_from":"tyr55"
        },
        "stat":{
                "bytes_out":49,
                "bytes_in":195,
                "rows":0,
                "affected_rows":1
        }
}
===============

4- run:
for x in {1..1000}; do
  curl -H"Content-Type: application/json" http://agent:mysql@127.0.0.1:14000/cta/aggregate -T json-data-999.txt  ;
done ;

5- Go to the dashboard and notice the total number of executions and total number of rows, they should be the same, but they are not



Attachment: 10400_EM-4614.png (image/png, text), 48.00 KiB.

Diego Medina writes: 
Aggr log for those who may want to look at it

Attachment: 10401_mysql-monitor-agent.log.gz (application/x-gzip, text), 5.05 KiB.

Diego Medina writes: 
http://127.0.0.1:58080/v2/rest/instance/mysql/StatementSummary/7ce2233d-30fb-4549-b03c-9d9...
gives me

{noformat}
parent: /instance/mysql/Server/7ce2233d-30fb-4549-b03c-9d905a1478fe
children:
/instance/mysql/Statement/7ce2233d-30fb-4549-b03c-9d905a1478fe.sugarcrm.deedd1acb862fa05e00209744084556f.1
avg_exec_time = "0.29071803852889666"
bytes = 244
bytes_rel = 244
count = 571
count_rel = 571
database = "sugarcrm"
errors = 0
exec_time = 166
exec_time_rel = 166
max_bytes = 244
max_exec_time = 166
max_rows = 1
min_bytes = 244
min_exec_time = 166
min_rows = 1
no_good_index_used = null
no_index_used = null
query_type = "SELECT"
rows = 1
rows_rel = 1
text = "SELECT name , symbol , conversion_rate FROM currencies WHERE status = ? AND deleted = ? "
text_hash = "deedd1acb862fa05e00209744084556f"
warnings = 0

{noformat}

============

http://127.0.0.1:58080/v2/rest/instance/mysql/Statement/7ce2233d-30fb-4549-b03c-9d905a1478...

gives me

{noformat}
parent: /instance/mysql/StatementSummary/7ce2233d-30fb-4549-b03c-9d905a1478fe.sugarcrm.deedd1acb862fa05e00209744084556f
bytes = 244
comment = ""
connection_id = 112998
database = null
errors = 0
exec_time = 166
explain_plan = null
host_from = "tyr55"
host_to = "bur02.norway.sun.com"
no_good_index_used = null
no_index_used = null
query_type = null
rows = 1
source_location = ""
text = "SELECT name, symbol, conversion_rate FROM currencies WHERE status = 'Active' and deleted = 0"
user = "sugarcrm"
warnings = 0

{noformat}

Diego Medina writes: 
you can omit rows form the json and just use affected_rows:

{noformat}
$ cat  json-data-999.txt 
{
        "version":"1",
        "action":"query",
        "server_id":"7ce2233d-30fb-4549-b03c-9d905a1478fe",
        "thread_id":112998,
        "data":{
                "query_type":"SELECT",
                "query_text":"SELECT  name1, symbol, conversion_rate FROM currencies WHERE status = 'Active' and deleted = 0",
                "query_id":2,
                "duration":166,
                "host_to":"bur02.norway.sun.com",
                "user":"sugarcrm",
                "database":"sugarcrm",
                "host_from":"tyr55"
        },
        "stat":{
                "bytes_out":49,
                "bytes_in":195,
                "affected_rows":1
        }
}
{noformat}

Diego Medina writes: 
if yo use rows instead of  afftected_rows you see the same problem

Diego Medina writes: 
To enable the aggregator on the "proxy process" you will need to add these lines to your mysql-monitor-ini file (the one you specify with --defaults-file

aggr-mem-user=agent
aggr-lua-script=lib/mysql-aggregator/lua/aggr.lua
aggr-mem-baseurl=https://127.0.0.1:58443/v2/rest
aggr-mem-password=mysql
aggr-address=:14000

(change settings to fit your path)

you will also need plugins=aggr

=================

the mysql server yo uare monitoring will always show red, because the "component" that makes it show green on the UI (Service manager / dashboard) is the agent, so, if you prefer to see the server as green to see that all is working ok, you also need to enable the "agent" plugin, this is a complete ini file I use:

{noformat}

[mysql-proxy]

# Common Parameters
#plugins=proxy,agent,aggr
plugins=aggr
keepalive = true

log-file = mysql-monitor-agent.log
pid-file=/Applications/mysql/enterprise/2.2/agent-2.2.2.1722/mysql-monitor-agent.pid

# Agent Parameters
agent-mgmt-hostname = https://127.0.0.1:58443/heartbeat
agent-mgmt-username = agent
agent-mgmt-password = mysql
mysqld-instance-dir= etc/instances
agent-item-files = share/mysql-monitor-agent/items/quan.lua,share/mysql-monitor-agent/items/items-mysql-monitor.xml,share/mysql-monitor-agent/items/custom.xml
agent-uuid = f2ab6c8c-3361-4ce5-a5b3-c879e8d4d72a

# Proxy Parameters
proxy-address=:54040
proxy-backend-addresses = 127.0.0.1:5132
proxy-lua-script = lib/mysql-monitor-agent/lua/quan.lua

aggr-mem-user=agent
aggr-lua-script=lib/mysql-aggregator/lua/aggr.lua
aggr-mem-baseurl=https://127.0.0.1:58443/v2/rest
aggr-mem-password=mysql
aggr-address=:14000

log-level=debug
{noformat}

Diego Medina writes: 
The way the aggregator (aggr or agg for short) works is as follows:

A user runs his/her application using one of our connectors, let's say they use the Connector/C. Once they enable the connector/c plugin on their application, every time they send a query to their mysql database, the plugin will send a json packet to the aggregator, a sample json packet was included on this bug report (see json-data-999.txt ).

The aggregator receives this information, and every 10 seconds, it "aggregates" this data and sends it up to the service manager (also known as the dashboard or the UI)
This data is only related to quan (Query analyzer data). and you can also see it by going to the dashboard, login -> Tab Query Analyzer. This page will show you information  about the queries that were sent to the dashboard by the aggregator (also note that the proxy send similar data when used)

See this screen shot to see where the total rows column is on the dashboard

http://skitch.com/fmpwizard/dkdsg/browse-queries-mysql-enterprise-dashboard

Andy Bang writes: 
In build 2.2.2.1729.

Diego Medina writes: 
2.2.2.x does not have the aggregator option on the installer, please do a 2.3 build so that I can test this bug.

(adding the aggr to the plugin list and adding the config options gives this error:

{noformat}

2010-07-08 16:18:35: (critical) MySQL Monitor Agent 2.2.2.1729 started.
2010-07-08 16:18:35: (critical) aggr_mainloop.c:757: lua(/Applications/mysql/enterprise/2.2/agent-2.2.2.1729/lib/mysql-aggregator/lua/aggr.lua) failed: ...2/agent-2.2.2.1729/lib/mysql-aggregator/lua/aggr.lua:4: module 'quan.math' not found:
        no field package.preload['quan.math']
        no file '/Applications/mysql/enterprise/2.2/agent-2.2.2.1729/lib/mysql-proxy/lua/quan/math.lua'
        no file '/Applications/mysql/enterprise/2.2/agent-2.2.2.1729/lib/mysql-monitor-agent/lua/quan/math.lua'
        no file '/Applications/mysql/enterprise/2.2/agent-2.2.2.1729/lib/mysql-proxy/lua/quan/math.so'
        no file '/Applications/mysql/enterprise/2.2/agent-2.2.2.1729/lib/mysql-monitor-agent/lua/quan/math.so'
        no file '/Applications/mysql/enterprise/2.2/agent-2.2.2.1729/lib/mysql-proxy/lua/quan.so'
        no file '/Applications/mysql/enterprise/2.2/agent-2.2.2.1729/lib/mysql-monitor-agent/lua/quan.so'
{noformat}

Michael Schuster writes: 
please build a 2.3 version w. this fix in

Andy Bang writes: 
In build 2.3.0.2027.

Diego Medina writes: 
Verified fixed on  2.3.0.2027

Added to 2.3.0 changelog:

If identical JSON-format data was submitted to the Aggregator within
the same 60-second window, the counts displayed on the Dashboard
could be incorrect.