| Bug #14318 | Instance Manager cannot connect to instances (to monitor) | ||
|---|---|---|---|
| Submitted: | 26 Oct 2005 8:02 | Modified: | 2 May 2006 3:23 |
| Reporter: | mike mike | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | mysql-5.0.15 source distrib | OS: | Linux (Debian-amd64 / Linux 2.6.11.12) |
| Assigned to: | Mike Hillyer | CPU Architecture: | Any |
[26 Oct 2005 8:09]
mike mike
modified it to be under the right category (it did not show subcategories on first "Add bug" screen)
[26 Oct 2005 8:29]
mike mike
sorry - it's supposed to say 5.0.15 NOT 5.0.5
[26 Oct 2005 19:37]
Jonathan Foo
Repeated the problem with the Linux x86 static 5.0.15 binary. I'm experiencing pretty much exactly what mike described.
I do have a manager passwd file set up, and it does contain a couple entries. I _am_ able to access the IM using the mysql command-line client, provided I give it both the hostname and the port that I have the manager running on, along with one of the login names and passwords in that passwd file.
prompt> mysql -u<user> -h<host> -P<manager_port> -p
When I connect this way, I'm able to use IM commands ("show instances;", "show instance status mysqld", etc), but I'm unable to use standard mysql commands ("show databases").
When I connect mysql without specifying the IM hostname/port, the opposite is true - I can issue the "standard" mysql commands and SQL I've always used with the mysql shell, but none of the new IM commands.
prompt> mysql -u<user> -p
For now, I've removed the 'MySQL_Instance_Manager' user from my server, and changed the IM monitoring-interval from 2 (seconds) to 3600 (seconds) in my.cnf to cut down on the 'Access Denied' messages in my general query log.
my.cnf:
[manager]
socket=/usr/local/mysql/logs/manager.sock
pid-file=/usr/local/mysql/logs/manager.pid
password-file=/etc/mysqlmanager.passwd
monitoring-interval = 3600
port = 1999
bind-address = 192.168.1.10
log = /usr/local/mysql/logs/manager.log
run-as-service
[mysqld]
datadir=/var/lib/mysql/data
log = /usr/local/mysql/logs/std.log
log-error = /usr/local/mysql/logs/err.log
log-slow-queries = /usr/local/mysql/logs/slow.log
long-query-time = 3
pid-file=/usr/local/mysql/logs/mysqld.pid
skip-external-locking
socket=/usr/local/mysql/logs/mysql.sock
[27 Oct 2005 15:17]
Valeriy Kravchuk
mike: Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/instance-manager-security.html). You have to create a sepatae user accounts for the Instance Manager. Jonathan: Similar with you. IM has its own set of commands. See http://dev.mysql.com/doc/refman/5.0/en/instance-manager-commands.html. It just uses the same protocol, so you can connect to it mysql. I hope this will help both of you. Please, inform about the results.
[27 Oct 2005 15:18]
Valeriy Kravchuk
mike: Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/instance-manager-security.html). You have to create a sepatae user accounts for the Instance Manager. Jonathan: Similar with you. IM has its own set of commands. See http://dev.mysql.com/doc/refman/5.0/en/instance-manager-commands.html. It just uses the same protocol, so you can connect to it mysql. I hope this will help both of you. Please, inform about the results.
[28 Oct 2005 15:41]
Jonathan Foo
Valeriy - I read and followed the IM docs linked from http://dev.mysql.com/doc/refman/5.0/en/instance-manager.html, including the two links you provided. The page on security is fairly short, and simply spells out how to get a username and password into the /etc/mysqlmanager.passwd file. As I mentioned earlier, I do have that file set up with a username and password per the docs. I'm able to log in to the IM using the username and password in the mysqlmanager.passwd file with no problem. # mysql -ujon -h192.168.1.10 -P1999 -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 0.2-alpha Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show instances; +---------------+--------+ | instance_name | status | +---------------+--------+ | mysqld | online | +---------------+--------+ 1 row in set (0.00 sec) However, when I view my MySQL server's general query log, I'm seeing the following entries: 051028 10:05:44 1 Connect Access denied for user 'MySQL_Instance_M'@'localhost' (using password: YES) 051028 10:05:46 2 Connect Access denied for user 'MySQL_Instance_M'@'localhost' (using password: YES) 051028 10:05:48 3 Connect Access denied for user 'MySQL_Instance_M'@'localhost' (using password: YES) ...etc - one entry every two seconds. My IM is set to monitor every two seconds in /etc/my.cnf (monitoring-interval=2). These entries in the general logfile are the source of my concern and, if I'm reading his original report correctly, mike's as well. This page: http://dev.mysql.com/doc/refman/5.0/en/instance-manager-command-line-options.html says the following: # --monitoring-interval=seconds Interval to monitor instances in seconds. Instance manager will try to connect to each of monitored instances to check whether they are alive/not hanging. In the case of a failure IM will perform several (in fact many) attempts to restart the instance. One can disable this behavior for particular instances with the nonguarded option in the appropriate instance section. If no value was given, the default of 20 seconds will be used. (for emphasis) ...Instance manager will try to connect to each of monitored instances... This is the only reference I've been able to find in the docs mentioning the IM attempting to connect to an instance. It does not mention how the IM will "try to connect". Is the fact that it's being denied access all the IM needs for it to understand that the instance in question is running, and therefore the "Access denied" messages are not a bug at all, but simply the Way Things Should Be? If that is the case, perhaps it should be documented somewhere obvious so that folks using the IM understand that their general logfiles are supposed to be filling up with "Access denied" messages. If that is not the case, and the IM _should_ be able to log in to a monitored instance, can you point me at documentation that explains: - Why the instance manager feels it needs to log in to a given instance. The IM appears to be able to respond to commands about the instance without logging in (see 'show instances' command and result, above), and, as you mentioned, the IM has its own set of commands. If I am unable to issue SQL commands to a given instance using the IM, AND the IM is able to determine that the instance in question is running without logging in, why does it even attempt to log in to the instance? - If there is a good reason to allow the IM to log in to an instance, how do I do so? I'm running 5.0.15-standard-log (MySQL-provided static binary) on x86 Linux.
[29 Oct 2005 20:02]
mike mike
exactly. i can login just fine TO the IM. the problem is the IM is not able to login to the instances (i'm assuming to monitor status/versions/queries per second maybe/additional future features) what password is it trying to connect with for that MySQL_Instance_M user? the security documentation does not discuss that -at all- that is what we need help with - he may need some other help about the IM client vs. the normal mysql client (i don't need that explanation, i understand) however, this is still a bug, and is causing extra wasted server resources.
[30 Oct 2005 9:25]
Valeriy Kravchuk
Thank you for the additional explanation of the problem. I'll try to figure out is it a bug or a request to the documentation team to desribe the things properly. Maybe, we just missing something obvious... Let me check.
[3 Nov 2005 9:08]
mike mike
if i set it to nonguarded, it doesn't seem to try to login (except when i do "show instances") so there's clearly some simple things here to look at: - ability to turn off ALL monitoring by connecting to the daemon (basically nonguarded w/ zero attempts to connect even with SHOW INSTANCES) - ability to allow the IM to connect to the instance - and customize the monitoring username/password also, i'm not sure what the IM needs to connect for at the moment; if it's non-critical information that doesn't have to be grabbed every $monitoring-interval, it would be nice to have a "status interval" setting too. that way, we can keep the server closely monitored for downtime, but not be requesting mysql status every 2 seconds as well.
[8 Nov 2005 16:47]
Valeriy Kravchuk
OK. I've finally got some time to figure out what happens. So, IM is checking that instance is running using the following function (in the instance.cc):
bool Instance::is_running()
{
MYSQL mysql;
uint port= 0;
const char *socket= NULL;
static const char *password= "check_connection";
static const char *username= "MySQL_Instance_Manager";
static const char *access_denied_message= "Access denied for user";
bool return_val;
if (options.mysqld_port)
port= options.mysqld_port_val;
if (options.mysqld_socket)
socket= strchr(options.mysqld_socket, '=') + 1;
pthread_mutex_lock(&LOCK_instance);
mysql_init(&mysql);
/* try to connect to a server with a fake username/password pair */
if (mysql_real_connect(&mysql, LOCAL_HOST, username,
password,
NullS, port,
socket, 0))
{
/*
We have successfully connected to the server using fake
username/password. Write a warning to the logfile.
*/
log_info("The Instance Manager was able to log into you server \
with faked compiled-in password while checking server status. \
Looks like something is wrong.");
pthread_mutex_unlock(&LOCK_instance);
return_val= TRUE; /* server is alive */
}
else
return_val= test(!strncmp(access_denied_message, mysql_error(&mysql),
sizeof(access_denied_message) - 1));
mysql_close(&mysql);
pthread_mutex_unlock(&LOCK_instance);
return return_val;
}
It is intended, "fake" account that is used to try to connect. There should be NO real user with this hardcoded name in any of the MySQL instances managed. And if one exists, the hardcoded error message is written to the log. This is the intended behaviour.
The fact that an attempt is performed to connect USING THIS USER NAME AND PASSWORD to any guarded instance each --monitoring-interval seconds should be documented (for administrators to not worry about) in a more clear way. I'll send a documentation request on this issue to our Documentation team.
As for wastage of resources - I do not think it is a problem (connect attempts is not wasting much resources in MySQL and you are controlling the monitoring interval). If you do not need to check if server is still alive each 2 seconds, just don't do it.
[30 Nov 2005 2:32]
mike mike
How is this a valuable feature though if it doesn't work as designed? The documentation will only be useful if it explains how to define the mysql instance login information so the IM can connect successfully...
[30 Nov 2005 2:32]
mike mike
changed back to open!
[30 Nov 2005 3:12]
KimSeong Loh
I think there is probably some misundertanding on the feature. The test connection using a non existed user/password is probably only used to determine if the server is still running, no need to be able to connect successfully to the server, as long as a connection fail message is returned to the IM, then it is safe to assume that the server is still running, otherwise the IM may try to restart the server again.
[30 Nov 2005 3:12]
KimSeong Loh
Hope I am right on the previous comment, it is a pure guess from me.
[30 Nov 2005 7:19]
Valeriy Kravchuk
It is not a bug, but an intended behaviour. You should not create a separate user for IM. Moreover, a warning is produced if you did created it. If you want to add some new functions or suggest to change something in the implementation of IM, please, send a feature request.
[30 Dec 2005 15:12]
Valeriy Kravchuk
Based on the discussion in this bug report, looks like the documentation should be updated to properly explain this feature, and the resulting entries in server logs.
[25 Apr 2006 4:01]
Konstantin Osipov
Stefan, this is an instance manager documentation issue. Could you please follow up on this? Thank you.
[2 May 2006 3:23]
Mike Hillyer
Added note to IM doc about login attempts and general query log entries to monitoring-interval section:
<para>
The interval in seconds for monitoring instances. The
default value is 20 seconds. Instance Manager tries to
connect to each monitored instance using the non-existing
<literal>MySQL_Instance_Manager</literal> user account to
check whether it is alive/not hanging. In the case of a
failure to connect, IM performs several attempts to
restart the instance. The <literal>nonguarded</literal>
option in the appropriate instance section disables this
behavior for a particular instance. The monitoring process
will produce messages in the general query log similar to
the following:
<programlisting>
Access denied for user 'MySQL_Instance_M'@'localhost' (using password: YES)
</programlisting>
</para>
Description: I installed and configured the Instance Manager as the docs say - it can control taking the server up and down properly, but it cannot monitor it. Somehow, it's trying to use a user that is not setup by default, and I cannot figure out the password. It's not the root password, nor the password defined in my mysqlmanager.passwd file (which is the same anyway) 051026 0:39:00 1 Connect Access denied for user 'MySQL_Instance_M'@'localhost' (using password: YES) 051026 0:39:39 2 Connect Access denied for user 'MySQL_Instance_M'@'localhost' (using password: YES) 051026 0:46:27 4 Connect Access denied for user 'MySQL_Instance_M'@'localhost' (using password: YES) I've tried to grant privileges to "MySQL_Instance_M" with the root/mysqlmanager password. It does not work. I googled for help, but couldn't find anything except some code snippets, of which I found this: const char *password= "check_connection"; const char *username= "MySQL_Instance_Manager"; Please note that the username there is too long for the mysql.user table - it truncates it at "MySQL_Instance_M" - so does the login routine. I tried granting the privileges with the password "check_connection" - which works, but throws the hardcoded error: /* We have successfully connected to the server using fake username/password. Write a warning to the logfile. */ log_info("The Instance Manager was able to log into you server \ with faked compiled-in password while checking server status. \ Looks like something is wrong."); Meanwhile, I can't get it to connect properly, and I cannot find any results or help anywhere about what password it -is- trying... my.cnf: [manager] socket = /tmp/manager.sock pid-file = /tmp/manager.pid monitoring-interval = 2 bind-address = 192.168.0.5 port = 2273 password-file = /usr/local/mysql5/etc/mysqlmanager.passwd [mysqld] socket = /tmp/mysql.sock port = 3306 user = mysql datadir = /usr/local/mysql5/var log = /usr/local/mysql5/var/log.log #nonguarded pid-file = /usr/local/mysql5/var/raid01.pid myisam-recover = FORCE max_connections = 300 server-id = 1 skip-concurrent-insert skip-name-resolve skip-thread-priority skip-external-locking skip-innodb skip-bdb expire_logs_days = 7 How to repeat: Setup mysql 5.0.5 from source. Nothing non-standard.