MySQL

In our last installment we found our hero investigating the tracing changes found in Connector/Net 6.2.  This time we’ll take a closer look at the format of the trace data and how developers can use that information to write new and interesting trace listeners.

Understanding the plumbing

The first thing we need to understand is a little about how the trace messages are routed.  The main method we are interested in is TraceSource.TraceEvent.  Here is the signature.

public void TraceEvent(
    TraceEventType eventType,
    int id,
    string format,
    params Object[] args)

All the other TraceSource methods like TraceInformation eventually boil down to a call to TraceEvent.  TraceEvent will run through all the attached listeners and call the TraceEvent method on each.

The default behavior of listeners is to eventually call String.Format using the format given.  It plugs the parameters in and comes out with a string that can be sent to the console, to a file, or some other output.  Thankfully Microsoft saw fit to make TraceEvent virtual allowing derivative listeners to override and do interesting things.

How we use TraceEvent

Here is the signature for TraceListener.TraceEvent.

public virtual void TraceEvent(
    TraceEventCache eventCache,
    string source,
    TraceEventType eventType,
    int id,
    string format,
    params Object[] args)

eventCache is provided by the framework and contains information such as process id, thread id, timestamp, etc.

source  is the name of the TraceSource that provided the event

eventType is the .NET defined type of this event.  This can have values such as Information, Warning, Error, etc.

id  is the application provided event id.  This value is application defined and we’ll explain how we use it later in this post.

format is the parameters string message that listeners such as ConsoleTraceListener would use for output

args is the array of arguments available to plug into the format string.  This is the actual data for the event.

The key to using the data provided in TraceEvent is simply to know that the data always comes in a specific order and format.  The following information only applies to events coming from the mysql source.  The eventCache, source, eventType, and format parameters are self explanatory so we’ll start with id.

The id parameter is our MySql-centric event id.  We wanted to give very specific information about the type of event.  We have a public enum available in the 6.2 assembly called MySqlTraceEventType.  Here is the definition.

public enum MySqlTraceEventType : int
{
    ConnectionOpened = 1,
    ConnectionClosed,
    QueryOpened,
    ResultOpened,
    ResultClosed,
    QueryClosed,
    StatementPrepared,
    StatementExecuted,
    StatementClosed,
    NonQuery,
    UsageAdvisorWarning,
    Warning,
    Error
}

Before we talk about the event-specific data points, let me mention about a small problem we needed to solve.  We wanted to have a numeric id attached to each event that would allow all the events belonging to a given query to be gathered together.  The only way we could accomplish that was to use a counter that gets incremented each time a driver is opened.  (remember we needed a value that is unique on the same thread so we can’t use process id, thread id, or event MySQL server thread id). We’ll call this the driver id and every event that comes from the mysql source has this driver id as the first member in the args array.

Here’s a table that lists each mysql event and what the arguments array looks like.  Please keep in mind that these arguments start in the second element (index == 1)

Here’s the definition of the publicly available UsageAdvisorWarningFlags enum.

public enum UsageAdvisorWarningFlags
{
    NoIndex = 1,
    BadIndex,
    SkippedRows,
    SkippedColumns,
    FieldConversion
}

So that’s it.  This information will be documented and may change slightly as we find problems.  In fact we’ve already identified a small issue and may add a “current database” parameter to the connection opened event since the current database can be different than what is set on the connection string.  Be sure, when we make these changes they’ll be documented and by checking the version of the assemblies involved you can do the right thing.

Armed with this information you should be able to go forth and make very interesting trace sniffing apps for MySQL.  If you accept such a challenge, drop me a line.  I would love to know what you think!

MySQL Connector/Net 6.2.2, a new version of the all-managed .NET driver for MySQL has been released.This is our latest GA release and is suitable for use in all scenarios against servers ranging from version 4.1 to 5.4!

It is now available in source and binary here and mirror sites (note that not all mirror sites may be up to date at this point of time – if you can’t find this version on some mirror, please try again later or choose another download site.)

The new features or changes in this release are:

• Connection pool cleanup timer.  We now utilize a timer that cleans idle connections that are no longer connected every 3 minutes
• We are now using stream and TCP-based timeouts to handle command timeouts.  This is more inline with what SqlClient does and should be more reliable than our old timer based approach
• Completely refactored MySqlConnectionStringBuilder
• We now support the TableDirect query type
• Completely refactored our logging system.  Our trace data is now published using TraceSource and has a specific format so to allow third party listeners to be created.
• Lots of bug fixes

Please let us know what else we broke and how we can make it better!

MySQL Connector/Net 6.1.3, a new version of the all-managed .NET driver for MySQL has been released. This is our latest GA release and is suitable for use in all scenarios against servers ranging from version 4.1 to 5.4!

It is now available in source and binary form from [http://dev.mysql.com/downloads/connector/net/6.1.html] and mirror sites (note that not all mirror sites may be up to date at this point of time – if you can’t find this version on some mirror, please try again later or choose another download site.)

This release builds on the cool new features introduced with 6.1.2 and adds the following changes/bugfixes.

• fixed session state provider table definition to allow more than 64K per-session data (bug#47339)
• fixed compilation problem in NativeDriver inside ExecuteDirect  (bug #47354)
• fixed default collation bug with session provider table (bug #47332)
• in sessionState provider, timeout value was read from the wrong (root) web.config (bug#47815)
• fixed crash that can occur when oldGuids are used and binary(16) column  used for GUID contains a null value (thanks Troy!) (bug#47928)
• fixed indexes schema collection so that it still works with bad table names such as b“a`d (bug #48101)
• fixed guid type so that multi-byte character sets will not effect how it works.  A column would be considered a guid if it has a *character* length of 36, not a *byte* length of 36 (bug #47985)
• fixed unsigned types in views when used as entities (bug # 47872)
• now exposing the MySqlDecimal type along with GetMySqlDecimal methods on data reader (bug #48100)   
• applied user-suggested patch to enable type-safe cloning (bug #48460)

Thank you for using our product!