MySQL Bugs: #23418: Agent does not limit the amount of VARCHAR data returned by collections

Bug #23418	Agent does not limit the amount of VARCHAR data returned by collections
Submitted:	18 Oct 2006 10:21	Modified:	21 Feb 2007 18:55
Reporter:	Mark Leith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Agent	Severity:	S2 (Serious)
Version:		OS:
Assigned to:	Jan Kneschke	CPU Architecture:	Any
Tags:	Agent

Description:
Currently the agent does not limit the amount of data that is returned to a VARCHAR variable from a (custom) data collection, internally. 

A custom data collection could then return large amounts of data to the agent (increasing memory), that would then also get transferred to the Service Manager (increasing network bandwidth), which would try to insert it in to the dc_VARCHAR table, which has "value" set as VARCHAR(255) - probably causing truncation warnings, as we are not running in a strict environment. 

How to repeat:
Put the following in custom collection xml file:

<?xml version="1.0" encoding="utf-8"?>
<ITEMS>
	<ITEM>
		<NAME>some_variable</NAME>
		<FIELD>some_variable</FIELD>
		<SCOPE>table</SCOPE>
		<CODE><![CDATA[SELECT GROUP_CONCAT(some_varchar_field) as some_variable FROM some_big_table GROUP BY NULL]]></CODE>

		<NAMESPACE>mysql</NAMESPACE>
		<RETURNS>INTEGER</RETURNS>

		<SOURCE>table</SOURCE>
		<INSTANCE>test.some_big_table</INSTANCE>
	</ITEM>
</ITEMS>

Fill some_big_table with lots of values in some_varchar_field in the test database.

Create a rule to return some_variable in it's output (%some_variable% IS NOT NULL).

Suggested fix:
Constrain the amount of data that can be collected at the agent level, with truncation warnings in the log file.

should be fixed in 1.1.0-r4781

Does not appear to be fixed, though we do get the warning message now:

2007-02-21 16:43:35: (message) agent/src/merlin-agent.c.1455: task nodes: task, id=61, command=collect_data

2007-02-21 16:43:35: (debug) agent/src/agent_target.c.159: target-uri = merlin:mysql://net-sup1-merlin:3306/table/local?attrib=big_value
2007-02-21 16:43:35: (message) agent/src/scheduler.c.200: scheduling collect_mysql for mysql::table->big_value
2007-02-21 16:43:35: (message) agent/src/job_collect_mysql.c.433: collect::mysql -> mysql::table->big_value (local)
2007-02-21 16:43:35: (message) mysql-query: SELECT REPEAT('A', 300) as big_value
2007-02-21 16:43:35: (message) job (task 61 (collect_mysql)) executes again in 60 sec
2007-02-21 16:43:35: (message) mysql::table->big_value = AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
2007-02-21 16:43:35: (warning) Collecting 'mysql::table->big_value' resulted in a overlong string (300 chars) which will be truncated to 256 chars.
2007-02-21 16:43:36: (message) --> sending heartbeat
2007-02-21 16:43:36: (debug) --> sending: <doc><agentId>5af4310c-5538-42c9-ac40-3f31d638289a</agentId><agentUtc>2007-02-21T15:43:36.810Z</agentUtc><hostname>net-sup1-merlin</hostname><uuid>a6ce020f-1f99-40cb-823e-71646d5e6d07</uuid><version>1.1.0.4785</version><shutdown>false</shutdown><tasks><task><taskId>61</taskId><command>collect_data</command><utc>2007-02-21T15:43:35.825Z</utc><data><datum><target><![CDATA[merlin:mysql://net-sup1-merlin:3306/table/local?attrib=big_value]]></target><utc>2007-02-21T15:43:35.825Z</utc><value>AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</value></datum></data></task>
</tasks></doc>

Note that we are still sending a value with a length of 300 characters up in the XML:

mysql> select length('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA') as len;
+-----+
| len |
+-----+
| 300 | 
+-----+
1 row in set (0.00 sec)

Setting to "Verified" again

in r4810 a new option was added:

[mysqld].truncate-string-len 

it defaults to 255 which matches the length of strings in the dc_varchar table.

0 or lower disables truncation.

Fixed in 1.1.0.4810

2007-02-21 19:52:00: (message) agent/src/job_collect_mysql.c.433: collect::mysql -> mysql::table->big_value (local)
2007-02-21 19:52:00: (message) mysql-query: SELECT REPEAT('A', 300) as big_value
2007-02-21 19:52:00: (message) mysql::table->big_value = AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
2007-02-21 19:52:00: (warning) Collecting 'mysql::table->big_value' resulted in a overlong string (300 chars) which will be truncated to 255 chars.
<task><taskId>61</taskId><command>collect_data</command><utc>2007-02-21T18:52:00.791Z</utc><data><datum><target><![CDATA[merlin:mysql://net-sup1-merlin/table/local?attrib=big_value]]></target><utc>2007-02-21T18:52:00.791Z</utc><value>AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</value></datum></data></task>

mysql> select length('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA') as len;
+-----+
| len |
+-----+
| 255 | 
+-----+
1 row in set (0.00 sec)