Bug #47675 extra connection for FEDERATED tables with MEM
Submitted: 28 Sep 2009 14:29 Modified: 25 May 2012 6:18
Reporter: Lig Isler-Turmelle Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Enterprise Monitor: Agent Severity:S3 (Non-critical)
Version:2.2+ OS:Any
Assigned to: John Russell CPU Architecture:Any

[28 Sep 2009 14:29] Lig Isler-Turmelle
Description:
When monitoring a system with MEM 2.1 it was noticed that the server with the base tables of FEDERATED tables had extra connections that were not expected.  

This did not occur when using MEM 2.0.

How to repeat:
** 5.0.82 with agent:

mysql> select version();
+--------------------------+
| version() |
+--------------------------+
| 5.0.82sp1-enterprise-gpl |
+--------------------------+
1 row in set (0.00 sec)

mysql> show full processlist;
+----+------+-----------------+-------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+-------+---------+------+-------+-----------------------+
| 1 | root | localhost | world | Query | 0 | NULL | show full processlist |
| 4 | lig | localhost:54534 | NULL | Sleep | 35 | | NULL |
+----+------+-----------------+-------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)

- Note: my connection (user:root) and the agent (user:lig)

** 5.0.70 without agent but with base table created and loaded:

mysql> select version();
+-----------------------+
| version() |
+-----------------------+
| 5.0.70-enterprise-gpl |
+-----------------------+
1 row in set (0.00 sec)

mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 3 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

** 5.0.82 with agent:

mysql> CREATE TABLE `City` (
-> `ID` int(11) NOT NULL auto_increment,
-> `Name` char(35) NOT NULL default '',
-> `CountryCode` char(3) NOT NULL default '',
-> `District` char(20) NOT NULL default '',
-> `Population` int(11) NOT NULL default '0',
-> PRIMARY KEY (`ID`)
-> ) ENGINE=FEDERATED
-> DEFAULT CHARSET=latin1
-> CONNECTION='mysql://test@127.0.0.1:3314/world/City';
Query OK, 0 rows affected (0.00 sec)

mysql> show full processlist;
+----+------+-----------------+-------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+-------+---------+------+-------+-----------------------+
| 1 | root | localhost | world | Query | 0 | NULL | show full processlist |
| 4 | lig | localhost:54534 | NULL | Sleep | 59 | | NULL |
+----+------+-----------------+-------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)

** 5.0.70 without agent but with base table created and loaded:

mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 3 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

** install agent on 5.0.70 (in my case had 1 agent monitor 2 instances
** restart agent

Macintosh-6:~ ligaya$ ./mysql-monitor-agent restart
Shutting down MySQL Enterprise agent service...
. SUCCESS!
Starting MySQL Enterprise agent service...
SUCCESS!

** 5.0.82 with agent:

mysql> show full processlist;
+----+------+-----------------+-------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+-------+---------+------+-------+-----------------------+
| 1 | root | localhost | world | Query | 0 | NULL | show full processlist |
| 5 | lig | localhost:54832 | NULL | Sleep | 21 | | NULL |
+----+------+-----------------+-------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)

** 5.0.70 with agent and base table created and loaded:

- Note the 2 additional connections - 1 for the agent (user: lig) and one now on the FEDERATED table (user:test)

mysql> show full processlist;
+----+------+-----------------+-------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+-------+---------+------+-------+-----------------------+
| 3 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
| 37 | lig | localhost:54831 | NULL | Sleep | 58 | | NULL |
| 49 | test | localhost:54849 | world | Sleep | 65 | | NULL |
+----+------+-----------------+-------+---------+------+-------+-----------------------+
3 rows in set (0.00 sec)

Suggested fix:
if it can not be fixed - at least document the behavior.
[25 May 2012 6:18] Philip Olson
Documentation has been added.