My MySQL wishlist (revised, again)

 Just like I did in 2007 and 2009, this is my updated whishlist for MySQL.

My 2007 List:
Per user and/or per database quota
I guess that this will be implemented together with catalog support.
 
External Authentication 
Got it in 5.5! Thanks a lot! And the new MySQL Cluster even has support for sharing user credentials.
 
Database Locator
There is still no TNSnames like support.

Saving extra metadata about the database.
Using the comment field of tables for things like svn release, customer number and more still just feels wrong. And a database still can't have a comment... Using I_S is possible, but it's not supported to create FK's for that, so consistency is not guaranteed.

better protection against run-away queries
With mk-kill this is now easy.

restore manager
I still have to use thinks like awk for this...

My 2009 List:
SNMP for statistics
no changes
 
SNMP for alerting
MySQL Enterprise Manager is quite good at this.

Auditing
There is much more possible with the new plugin features in 5.5

And now the new entries:

SequencesThis could make single-row uuid tables redundant
 
Check constraints
See this blog entry from endpoint.com for the reason why.
 
Multiple triggers with the same action time and event
 
Host cache info
As explained in Bug #59404

Crash-safe UDFs
A badly written UDF can crash the whole server. This will make the use of UDF's a bit too risky for some.

What's on your whishlist? A scalable query cache? JSON Features? Multithreaded Slave? Custom datatypes? IPv6?

Explaining what the default PROXY privilege for root does

In a default MySQL 5.5.8 installation there is one PROXY privilege:

GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

What this does is:
If USER() and CURRENT_USER() don't match root is still allowed to grant the proxy privilege.

So if you connect using someuser@localhost using LDAP and LDAP tells you're root then you're still allowed to grant proxy privileges. This will only work if your user has the privilege to proxy to root.

The documentation for PROXY is here.

MySQl Enterprise Monitor 2.3.2

It's the second point release of MySQL Enterprise Monitor 2.3. And there some nice new features and bugfixes.

It's now possible to enable or disable event blackout for specific servers easily.

It should now graph InnoDB with multiple buffer pools correctly. It won't work for 5.5.8 however, see Bug #60777 and Bug #60831 for more info on that.

Read the complete changelogs for all the details.

The Advisors/Rules were also updated. But there is still room for improvement:

• Bug #60828 Add rule to detect duplicate foreign keys
• Bug #60697 False Positives for Root Account Can Login Remotely
• Bug #60695 False positives for tables w/o keys for 5.5 with perf schema
• Bug #60677 "User Has Rights To Database That Does Not Exist" gives false positives
• Bug #60676 Add rule to monitor if the timezone info is loaded
• Bug #60587 Advice for Root Account Without Password is not correct
• Bug #60586 key buffer size check gives false positives

See also my previous blog posts:

• MySQL and Packaging
• How the MySQL Monitor Agent broke Sendmail
• MySQL Timezone support: Updating and Monitoring 

MySQL Enterprise is very easy to install, update and configure. Extending is very well possible using custom SQL queries and Lua scripting, but not really easy. Adding more graphs is also quite hard.

The graphs look way better than many other tools available. The size is easily configurable so that the graph is as wide as your screen.

The combination with the Query Analyzer is nice, but I'm not really using it very often as it requires a MySQL Proxy instance to capture the query info. They're fixing that my integrating query capture in Connector/J, Connector/NET and the PHP Connector (the later is only a beta). There are also other ways to capture queries in MySQL: using the sqlstats plugin, so there are lots of possibilities for the query analyzer to become much more useful.

The downside of MySQL Enterprise Monitor is that the tool itself is not free or opensource. It does use an opensource environment: Tomcat, Lua and many opensource Java classes.

Reporting Bugs and Feature request works really well, and lots and lots of issues which I encountered were fixed.

MySQL Monitor is one of the top reasons to choose the MySQL Enterprise Subscription.

And some questions for the readers of this post:

• What are you using for monitoring?
• Are you using MEM? Are you satisfied with it? Why? Why not?
• If you are using Nagios/Cacti: Which checks/templates do you use?

MySQL and Packaging

The MySQL Server from Oracle comes in a two different flavours: Community Edition and Enterprise Edition. The first one is under the GPLv2 license and the later is under the GPLv2 or Commercial license.

The Enterprise Edition was always available from https://enterprise.mysql.com (which now has an expired SSL certificate) under the GPLv2 license. This download page was restricted to paying customers. Since the Enterprise downloads were moved to https://edelivery.oracle.com the downloads are available for everyone (as long as it's not restricted by export regulations and accept the trial license agreement). The license is now 'Commercial'. The download be named V24071-01.zip or something like that, which is annoying. The latest version for the Enterprise release on edelivery is 5.5.8 while the latest Community version is 5.5.11. Previously there were two enterprise releases: Advanced (With partitioning) and Professional (Without partitioning). If you use https://support.oracle.com you can search for patch 12332498 which claims to contain MySQL Advanced Server 5.5.11. The download is named 'p12332498_55_Linux-x86-64.zip' which is a bit better than the filenames which edelivery generates.

Oracle does provide RPM's and Generic Tarballs, but no Debian packages. And Debian/Ubuntu is used a lot, even in bigger companies. There is no yum repository available for MySQL Enterprise, so there a no auto updates and easy installs. There is an official and public yum repository available but that's only for Oracle Enterprise Linux and Oracle VM. And their Solaris repository also doesn't have the latest MySQL versions (Only 5.0 and 5.1).

The MySQL Enterprise Subscription also contains MySQL Enterprise Monitor, MySQL Enterprise Backup and MySQL Workbench. There were RPM's available for the MySQL Enterprise Agent, but it turn's out that that was a mistake. The official way is to use the installer (Yes unattended installation is possible). But no update via yum/apt or any other method. Just run de agent update installer on every machine.

For the Community version there is also no YUM or APT repository and that might be one of the reasons why every one sticks to the old versions which are shipped with their Linux distribution of choice. It would be much easier to install packages with a hard dependency on mysql together with the latest official mysql if there were deb packages available. For more info about when not to use yum/apt read Shlomi Noach's blogpost.

There are deb's for MySQL Workbench available and there are also RPM's, but unfortunately the dependencies are not complete RHEL6.

Percona is a good example about how it should be done. Their repositories are accessible via APT and YUM. There are no repositories for Solaris and Windows yet, but I don't know if anyone is really interested in that.

How do most folks keep there mysql servers updated? How do you monitor if there are any security related updates available? Do you use an in house YUM/APT/WSUS repository?

How the MySQL Monitor Agent broke Sendmail

For MySQL Enterprise Monitor an agent is required. I've downloaded
V23981-01.zip from https://edelivery.oracle.com which contains mysqlmonitoragent-2.3.1.2044-linux-glibc2.3-x86-64bit-installer.rpm

When I was testing the email alerting for my backup script I got an error:

/etc/mail/submit.cf: line 544: fileclass: cannot open '/etc/mail/trusted-users': Group
writable directory

Luckily RPM was able to tell me that mysqlmonitoragent had set / to the wrong permissions.

# rpm -qf /
filesystem-2.4.0-3.el5
mysqlmonitoragent-2.3.1.2044-0
# rpm -qvl mysqlmonitoragent-2.3.1.2044-0 | head -1
drwxrwxr-x    2 root    root                0 Nov 25 01:51 /
# rpm -qvl filesystem-2.4.0-3.el5 | head -1
drwxr-xr-x    2 root    root                0 Oct  1  2009 /
# echo test | mail -s test [email protected]
# /etc/mail/submit.cf: line 544: fileclass: cannot open '/etc/mail/trusted-users': Group
writable directory

I've filed Bug #60752 for this. So watch out for this bug if you're using mysqlmonitoragent RPM's!

The work-around is to do a "chmod 755 /".