Tag Archives: MySQL

Practical P_S: Extending PROCESSLIST

MySQL 5.6 introduced major advances to monitoring made via PERFORMANCE_SCHEMA, but also made a change in how it binds to the network by default.  In MySQL 5.5, the –bind-address configuration option defaulted to “”, meaning only IPv4.  This changed to “*” in MySQL 5.6, accepting connections on both IPv6 and IPv4 interfaces.  Somehow (I’ve not looked into it yet), my (unsupported) WindowsXP installation now refuses to bind to IPv4, which caused surprising problems for certain tools that seem to internally map “localhost” to IPv4-specific, where connections fail.  In working through this problem, I found myself wishing that PROCESSLIST output included information about which mechanism or interface was being used by each connection.  Fortunately, we can leverage PERFORMANCE_SCHEMA to extend PROCESSLIST in meaningful ways – this post aims to demonstrate how to do this by adding information about the interface as an example.

Continue reading Practical P_S: Extending PROCESSLIST

Practical P_S: Finding which accounts fail to properly close connections

I’ve previously written about several problems which can benefit from additional visibility provided by PERFORMANCE_SCHEMA in MySQL 5.6, and it’s time to add to that list.  A very common problem involves connections which are not properly closed – they simply idle until they reach wait_timeout (or interactive_timeout, depending on the client flags set), and the server terminates the connection.  Who knows what the root cause is – perhaps the client terminated without cleaning up connections, or maybe there was just no load, or maybe the network cable was unplugged.  It’s something application developers – particularly those using persistent connections managed by a pool – run into frequently.

If you are a DBA rather than a developer, though, your only real clue that something is wrong may be a perpetually increasing Aborted_clients status variable counter. The manual has a page dedicated to solving such (and related) connection problems, and it references tools such as the general query log and error log.  The Aborted_clients status variable is useful to answer the question, “how many connections have been closed without an explicit quit request from the client?”  And prior to 5.6, that’s about as much information as you could expect to get:

mysql> show global status like 'aborted_clients';
| Variable_name   | Value |
| Aborted_clients | 5     |
1 row in set (0.00 sec)

With PERFORMANCE_SCHEMA in 5.6, we can isolate the problem to specific accounts, and we can calculate the percentage of client connections which were terminated without an explicit quit command from the client.  You can do that with the following query:

    performance_schema.events_statements_summary_by_account_by_event_name ess
    performance_schema.accounts a ON (ess.USER = a.USER AND ess.HOST = a.HOST)
    ess.EVENT_NAME = 'statement/com/Quit'

The easiest way to test this is to make a handful of connections, issue SET @@session.wait_timeout = 1 – here’s the result of the above query after doing so:

| USER | HOST      | not_closed | pct_not_closed |
| root | localhost |          4 |        44.4444 |
| ODBC | localhost |          1 |       100.0000 |
2 rows in set (0.00 sec)

Knowing which accounts are failing to properly close connections can help quickly spotlight where further investigation should be focused.  And with MySQL 5.6, DBAs can get that information without resorting to the general query log or via application logs.

mysql_upgrade is now version-specific by default

You’ve just completed an upgrade from MySQL 5.5 to 5.6.  You followed the upgrade instructions in the manual, and ran mysql_upgrade.  But when you start MySQL 5.6, you still see the following error messages like the following in the server error log:

2013-03-26 16:45:51 5040 [ERROR] Column count of mysql.events_waits_current is w
rong. Expected 19, found 16. Created with MySQL 50520, now running 50610. Please
use mysql_upgrade to fix this error.
2013-03-26 16:45:51 5040 [ERROR] Column count of mysql.events_waits_history is w
rong. Expected 19, found 16. Created with MySQL 50520, now running 50610. Please
use mysql_upgrade to fix this error.

What went wrong?

Continue reading mysql_upgrade is now version-specific by default

Finding the source of problematic queries

Many MySQL users are familiar with using slow query logs and tools such as mysqldumpslow to identify poor-performing SQL commands, and MySQL 5.6 introduces new powerful tools in PERFORMANCE_SCHEMA.  Both allow you to identify the date/time and the user account from which the command was issued, which is helpful – but if you’re using MySQL Enterprise Monitor (MEM), you can immediately identify the actual line of code responsible for the SQL command in question.  This happens to be one of my favorite and powerful features of MEM, but it’s frequently overlooked by new and experienced MEM users alike, so I’m writing the post to highlight it.

MySQL Enterprise Monitor, of course, is a commercial product that’s part of the MySQL Enterprise subscription.  But it’s freely-available under 30-day trial terms for evaluation from Oracle Software Delivery Cloud – if you aren’t a commercial customer, consider downloading MEM to see what it can do for you.  And if you are a MySQL Enterprise subscriber who hasn’t deployed MEM, or haven’t yet explored some of the more advanced features, now’s the time to do so.

Continue reading Finding the source of problematic queries

How to tell whether MySQL Server uses yaSSL or OpenSSL

Starting with MySQL 5.6, MySQL commercial-license builds use OpenSSL.  yaSSL – previously used as the default SSL library for all builds – remains the implementation for Community (GPL) builds, and users comfortable building from source can choose to build with OpenSSL instead.  Daniel van Eeden recently requested a global variable to indicate which SSL library was used to compile the server (bug#69226), and it’s a good request.  It’s something I’ve previously requested as well, having been fooled by the use of have_openssl as a synonym for have_ssl (I’m sure it made sense at the time, right?).  Continue reading How to tell whether MySQL Server uses yaSSL or OpenSSL

MySQL 5.6 general query log behavior change

The MySQL general query log can be a useful debugging tool, showing commands received from clients.  In versions through MySQL 5.5, you could count on the GQL to log every command it received – the logging happened before parsing.  That can be helpful – for example, the GQL entries might have records of somebody unsuccessfully attempting to exploit SQL injection vulnerabilities that result in syntax exceptions.

Here’s a sample, which I’ll run in both 5.5 and 5.6 and show the resulting GQL: Continue reading MySQL 5.6 general query log behavior change

Connector/J 5.1.25 Released

MySQL Connector/J 5.1.25 has been released, and is available in Community and Enterprise flavors on dev.mysql.com and My Oracle Support, respectively.  I’ve already noted the addition of support for connection attributes for MySQL 5.6 – 5.1.25 adds this functionality.  This release also includes a fix for Bug#68733, which caused the special light-weight ping operation to execute only against the master and currently-selected slave, rather than the master plus all active slave connections.  The ping operation exists to help ensure connections aren’t left idle for excessive durations in the internal load-balancing and replication “pools”, so failure to ping *all* active slave connections will make CommunicationFailureExceptions more likely after a rebalance operation selects a non-active, long-idle physical connection.  This bug was found by Dominic Tootell and reported via the community forums in a wonderfully-researched post.  It’s a great example of the type of community feedback and interaction that helps improve MySQL quality – thanks for the great contribution, Dominic!

In working through the bug Dominic reported, I found a couple other small bugs to fix.  The first is Bug#68763, which caused ReplicationConnection.isMasterConnection() to incorrectly always report false.  The second (Bug#68664) was an Ant build script bug which caused builds in Eclipse to produce a .JAR file – but it contained no .class files.  Our standard builds are done from the Ant script rather than Eclipse, and I’ve typically worked around it by running Ant from the command-line when I needed to package (compiling has worked in Eclipse for some time thanks to Mark Matthews, just not packaging).  Now you can package a .JAR file without leaving Eclipse.  In another bug report, Alex Soklakov fixed Bug#68801, making Connector/J play nicely with Maven.

I hope you enjoy Connector/J 5.1.25!

My Oracle Support 6.5: Key new features

If you are a MySQL support customer, the recent release of My Oracle Support (MOS) 6.5 has some features which may interest you (if you’re not a customer, this post likely won’t interest you). MOS 6.5 was introduced on 06 April, and with it came the ability to opt in to receive service request (SR) update details via email. This was a feature some MySQL Support customers missed after the migration to MOS. Thanks to feedback from MySQL Support customers and others with similar needs, this feature has now been implemented. Because email is an inherently insecure delivery mechanism, not all customers will wan this, and the feature requires customers to explicitly opt in before SR update content is sent via email.  Coupled with the MOS Mobile interface, Support customers have a number of flexible ways to access and manage SRs.

The second major enhancement is the ability to participate in live chat with support staff via MOS. You can set your availability flag in MOS, and a support engineer can see your status to initiate a chat, if needed. For me, chats are better than emails when interaction is more “conversational”, and better than phone calls when specific instructions or diagnostic information needs to be shared.

If either of these new features interest you, Chris Warticki’s blog post or the actual release notes for MOS 6.5 are good references for more information.

Thanks again to MySQL Support customers who advocated for these features – Oracle is listening, and your efforts improve the support experience for all Oracle support customers.

Fresh dogfood: Migrating to InnoDB fulltext search on bugs.mysql.com

Even frequent visitors to bugs.mysql.com can sometimes miss the little note in the bottom right corner of each page:

Page generated in 0.017 sec. using MySQL 5.6.11-enterprise-commercial-advanced-log

That text changed this past weekend, going from MySQL Enterprise 5.6.10 to 5.6.11.  But more importantly, the collection of MyISAM tables which support the bugs system were also converted to InnoDB.  There’s a little story to tell here about eating this particular helping of dogfood which also amplifies changelog comments, so here it is:
Continue reading Fresh dogfood: Migrating to InnoDB fulltext search on bugs.mysql.com