Bug #57800 MySQL prefPane on MacOSX polls the server without being asked to
Submitted: 28 Oct 2010 10:53 Modified: 14 Jan 2013 17:45
Reporter: Giuseppe Maxia Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Installing Severity:S3 (Non-critical)
Version:5.1.52, 5.5.29 OS:Mac OS X (10.6.5,10.8.2)
Assigned to: Assigned Account CPU Architecture:Any
Tags: dmg, installer, macosx, polling, Widget

[28 Oct 2010 10:53] Giuseppe Maxia
Description:
In Mac OSX, after you install the server from the native package (.dmg), the server is polled EVERY TWO SECONDS by an anonymous user.

The polling keeps coming as long as the server is kept insecure, i.e. as long as the anonymous users are allowed.
Until that moment, you can observe in the server STATUS variables that 'com_admin_commands' and 'connections' are growing every two seconds. If you enable the general logs, you will notice that an anonymous user is connecting and disconnecting every two seconds.
When the anonymous users are disabled, though, these connections fail, and you will see that 'connections' is still growing, but 'com_admin_commands' (whatever that is) is not growing anymore. However 'aborted_connects' is now growing steadily. If anyone is relying on the monitoring of 'abort_connects' to react to intrusions, this behavior will defy the purpose.

How to repeat:
1) install the server, the startup item, and the preference panel widget from a .dmg package. 
2) connect to the database as root
3) SHOW GLOBAL STATUS LIKE 'com_admin_commands';
4) SHOW GLOBAL STATUS LIKE 'connections'
5) repeat the above commands a few times
   The numbers for com_admin_commands and connections are growing
6) enable the general log, and inspect the log after a few seconds
7) edit a my.cnf, adding these lines:
   [client]
   user=nosuchuser
   password=nosuchpassword
8) Look at the status again. com_admin_commands does not grow. connections does,
   and 'aborted_connects" is growing steadily
9) remove the my.cnf. The com_admin_commands keep growing.
10) Secure the server (disable the anonymous users and set a password for root) and see that the connections keep going, but they are all abort_connects.

Suggested fix:
A simple workaround is to remove the MySQL widget from the preference panel, and then restart the MySQL server and the preference panel application.
This will prevent the user from starting the server from a GUI though.

The right fix is to remove the polling altogether. If the polling should stay, it should be documented (what is the purpose of it? what happens if the polling fails?) and it should be documented.
[28 Oct 2010 13:07] Alfredo Kojima
The connections are opened by mysqladmin ping, to see if the server is still up and update the status accordingly.

Does the polling stop when you quit the app?
[28 Oct 2010 13:41] Giuseppe Maxia
I don't see any mysqladmin in the process list (ps auxw)
But when I remove stop the preference panel itself, the polling stops.

Now, there are two problems here: 

1) the user does not know where the polling comes from. Finding the item responsible for the amount of connections every two seconds is a long exercise.

2) Once found the culprit, the user doesn't know how to stop it or change its behavior.

This behavior is the same adopted by the now deprecated instance manager, and I filed a bug about a similar behavior in 2005, for the same reason stated here. Increasing the number of abort_connects for the sake of monitoring the server is damaging for the DBA, who can't tell these fake connections from real failed attempts to connect to the server.
[28 Oct 2010 13:43] Peter Laursen
Why will it need to poll MySQL? 
The OS must return info is the service/daemon is running?
What did the old MA tray applet for Windows do?
[28 Oct 2010 15:22] Alfredo Kojima
mysqladmin runs and exits quickly each time, so unless you keep polling ps auxw very quickly, you won't see it.

Now, the 2 other ways to check if the server is up that I could think of were:
- check ps auxw and see if mysqld is there. That is how it used to work until Tiger. After Tiger, things broke and it was determined that for some reason, preference panes cannot run suid root programs anymore (which is the case of ps)
- use support-files/mysql.server status. This doesn't work in OSX:

kojima@Woodstock wb52$ /usr/local/mysql/support-files/mysql.server status
/usr/local/mysql/support-files/mysql.server: line 420: pidof: command not found
 ERROR! MySQL is not running
kojima@Woodstock wb52$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
[28 Oct 2010 15:25] Peter Laursen
" .. you won't see it" is not true.  You will see it in status variables.  And status variables are used for monitoring. And that is the problem.
[28 Oct 2010 15:33] Giuseppe Maxia
Alfredo, there is a third way of checking the status of the server, by monitoring the Process ID recorded in the PID file.
[28 Oct 2010 15:59] Alfredo Kojima
Peter: I was talking about the mysqladmin process.

Giuseppe: Where is the the pid file stored? I think it's in the datadir, and the data dir is only readable to the _mysql user/group. The Preferences app process doesn't run as _mysql, so I would need to get the admin password to check for files in there.
[28 Oct 2010 16:49] Giuseppe Maxia
Alfredo,
Since you need the superuser password to start/stop the server, you could use that to get access to the PID file.
But this is beside the point. The behavior that I am complaining about is not wanted, nor tolerated.
Either I have an out-option, or a way of configuring it. As it is, it's only confusing.
The server is started through mysqld_safe, and that will take care of restarting the server if it terminates abnormally. I don't need an application meddling with my status values for it.
So, let's please stick to the core of this bug report. The current behavior is wrong. It is not documented (if it is, please tell me where) and it is contrary to established best practices.
[16 Nov 2010 22:17] Sveta Smirnova
Thank you for the report.

Do you see same problem with version 5.1.52? Which version of Mac OSX do you use?
[17 Nov 2010 7:24] Giuseppe Maxia
Nothing has changed in 5.1.52.
My operating system is Mac OSX 10.6.5
[14 Jan 2013 17:45] Matthew Lord
I verified this using OSX 10.8.2 and the MySQL 5.5.29-community DMG.

It looks like we need an improved way of checking the status of MySQL in the pref panel. It seems to do this:
  mysqladmin -u root ping

When you change the default password for root@localhost then it of course fails with "access denied".

I don't even see why this ping call is necessary? The status polling within the pref panel is still correct, even when the mysqladmin ping calls are failing.

I verified this by starting and stopping MySQL outside of the pref panel.

Can we simply remove the unnecessary mysqladmin ping calls? That seems like the best resolution here.