Finding the source of problematic queries

Java, MySQL, , , , , ,

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

MySQL, ,

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

MySQL, ,

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

Java, MySQL, , , ,

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

MySQL, , ,

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

MySQL, , ,

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 →

Not-so-light reading: 5.6.11 changelog

MySQL,

Looking for some substantial reading material as the days grow longer?  You might consider dedicating a good chunk of time to review the MySQL 5.6.11 changelog.  The MySQL Engineering team at Oracle has been busy, and it shows in this maintenance release.  Stewart Smith recently noted the growth of the code base in 5.6 compared to 5.5.  That may or may not be the best measure of productivity, but the number of fixed bugs in a maintenance release like 5.6.11 is sure a good indicator.  A few general observations based on my quick study: Continue reading Not-so-light reading: 5.6.11 changelog →

MySQL Utilities meets the world’s ugliest table

MySQL, , ,

In case you missed it, MySQL Utilities 1.3.0 (alpha) was released last week.  MySQL Utilities is a component of MySQL Workbench, but it’s been broken out into a separate download allowing command-line users access unencumbered by a pretty GUI interface.  Plus, it has some new features – most importantly to me, a utility (mysqlfrm) which can read .FRM files and produce CREATE TABLE statements as a result.  This will be very useful for recovery processes, in conjunction with InnoDB transportable tablespaces in situations where the InnoDB system tablespace is corrupted, and you don’t know the exact table definition.

When we first started testing the FRM reader, it didn’t bootstrap mysqld to decipher .FRM file contents – Chuck Bell spent hours staring into the abyss that is FRM file formats and trying to shed light in the many dark corners that exist.  We started with simple tests, like:  can it read all the .FRM files in the data/mysql directory?  Then I hacked a quick program that created tables with many different data types, modifiers and variants.  But these tests were all dwarfed by the contribution of my colleague and bug-finder extraordinaire, Shane Bester, who gave us what may well be the world’s ugliest table.  Behold:

Continue reading MySQL Utilities meets the world’s ugliest table →

Last week to submit for MySQL Connect!

MySQL, ,

The Call for Papers for MySQL Connect 2013 will close this Friday.  This is the premier Oracle-sponsored MySQL conference, and a great opportunity for community users and customers alike to connect with Oracle staff from engineering, support, consulting and other organizations, as well as community partners and peers.  Maybe you’d like to talk about your experiences integrating one of the new MySQL 5.6 features.  Or maybe you’d like to share best practices you’ve developed for integrating with Hadoop.  Perhaps you’ve extended MySQL and want to tell the community about it.  Take advantage of this opportunity and submit your proposal this week!