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.