22.2.5.14. Using the MySQL Connector/NET Trace Source Object

MySQL Connector/NET 6.2 introduced support for .NET 2.0 compatible tracing, using TraceSource objects.

The .NET 2.0 tracing architecture consists of four main parts:

  • Source - This is the originator of the trace information. The source is used to send trace messages. The name of the source provided by MySQL Connector/NET is mysql.

  • Switch - This defines the level of trace information to emit. Typically, this is specified in the app.config file, so that it is not necessary to recompile an application to change the trace level.

  • Listener - Trace listeners define where the trace information will be written to. Supported listeners include, for example, the Visual Studio Output window, the Windows Event Log, and the console.

  • Filter - Filters can be attached to listeners. Filters determine the level of trace information that will be written. While a switch defines the level of information that will be written to all listeners, a filter can be applied on a per-listener basis, giving finer grained control of trace information.

To use tracing a TraceSource object first needs to be created. To create a TraceSource object in MySQL Connector/NET you would use code similar to the following:

TraceSource ts = new TraceSource("mysql");

To enable trace messages you also need to configure a trace switch. There are three main switch classes, BooleanSwitch, SourceSwitch, and TraceSwitch. Trace switches also have associated with them a trace level enumeration, these are Off, Error, Warning, Info, and Verbose. The following code snippet illustrates creating a switch:

ts.Switch = new SourceSwitch("MySwitch", "Verbose");

This creates a SourceSwitch, called MySwitch, and sets the trace level to Verbose, meaning that all trace messages will be written.

It is convenient to be able to change the trace level without having to recompile the code. This is achieved by specifying the trace level in application configuration file, app.config. You then simply need to specify the desired trace level in the configuration file and restart the application. The trace source is configured within the system.diagnostics section of the file. The following XML snippet illustrates this:

<configuration>
  ...
  <system.diagnostics>
    <sources>
      <source name="mysql" switchName="MySwitch"
              switchType="System.Diagnostics.SourceSwitch" />
      ...
    </sources>
    <switches>
      <add name="MySwitch" value="Verbose"/>
      ...
    </switches>
  </system.diagnostics>
  ...
</configuration>

By default trace information is written to the Output window of Microsoft Visual Studio. However, there are a wide range of listeners than can be attached to the trace source, so that trace messages can be written out to various destinations. It is also possible to create custom listeners to allow trace messages to be written to other destinations as mobile devices and web services. A commonly used example of a listener is ConsoleTraceListener, which writes trace messages to the console.

To add a listener at run time you can use code such as the following:

ts.Listeners.Add(new ConsoleTraceListener());

You can then call methods on trace source object to generate trace information. For example, the TraceInformation(), TraceEvent(), or TraceData() methods can be used.

The TraceInformation() method simply prints a string passed as a parameter. The TraceEvent() method, as well as the optional informational string, requires a TraceEventType value to be passed to indicate the trace message type, and also an application specific ID. The TraceEventType can have a value of Verbose, Information, Warning, Error, and Critical. Using the TraceData() method you can pass any object, for example an exception object, instead of a message.

To ensure than these generated trace messages gets flushed from the trace source buffers to listeners, you need to invoke the Flush() method. When you are finished using a trace source, you should call the Close() method. The Close() method first calls Flush(), to ensure any remaining data is written out. It then frees up resources, and closes the listeners associated with the trace source.

ts.TraceInformation("Informational message");
ts.TraceEvent(TraceEventType.Error, 3, "Optional error message");
ts.TraceData(TraceEventType.Error, 3, ex); // pass exception object
ts.Flush();
...
ts.Close();
22.2.5.14.1. Viewing MySQL Trace Information

This section describes how to set up your application to view MySQL trace information.

The first thing you need to do is create a suitable app.config file for your application. An example is shown in the following code:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.diagnostics>
    <sources>
      <source name="mysql" switchName="SourceSwitch"
        switchType="System.Diagnostics.SourceSwitch" >
        <listeners>
          <add name="console" />
          <remove name ="Default" />
        </listeners>
      </source>
    </sources>
    <switches>
      <!-- You can set the level at which tracing is to occur -->
      <add name="SourceSwitch" value="Verbose" />
      <!-- You can turn tracing off -->
      <!--add name="SourceSwitch" value="Off" -->
    </switches>
    <sharedListeners>
      <add name="console"
        type="System.Diagnostics.ConsoleTraceListener"
        initializeData="false"/>
    </sharedListeners>
  </system.diagnostics>
</configuration>

This ensures a suitable trace source is created, along with a switch. The switch level in this case is set to Verbose to display the maximum amount of information.

In the application the only other step required is to add logging=true to the connection string. An example application could be:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using MySql.Data;
using MySql.Data.MySqlClient;
using MySql.Web;


namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            string connStr = "server=localhost;user=root;database=world;port=3306;password=******;logging=true;";
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();

                string sql = "SELECT Name, HeadOfState FROM Country WHERE Continent='Oceania'";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    Console.WriteLine(rdr[0] + " -- " + rdr[1]);
                }

                rdr.Close();

                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            Console.WriteLine("Done.");
        }
    }
}

Ths simple application will then generate the following output:

Connecting to MySQL...
mysql Information: 1 : 1: Connection Opened: connection string = 'server=localhost;User Id=root;database=world;port=3306
;password=******;logging=True'
mysql Information: 3 : 1: Query Opened: SHOW VARIABLES
mysql Information: 4 : 1: Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=272, skipped rows=0, size (bytes)=7058
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SHOW COLLATION
mysql Information: 4 : 1: Resultset Opened: field(s) = 6, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=127, skipped rows=0, size (bytes)=4102
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SET character_set_results=NULL
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 10 : 1: Set Database: world
mysql Information: 3 : 1: Query Opened: SELECT Name, HeadOfState FROM Country WHERE Continent='Oceania'
mysql Information: 4 : 1: Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1
American Samoa -- George W. Bush
Australia -- Elisabeth II
...
Wallis and Futuna -- Jacques Chirac
Vanuatu -- John Bani
United States Minor Outlying Islands -- George W. Bush
mysql Information: 5 : 1: Resultset Closed. Total rows=28, skipped rows=0, size (bytes)=788
mysql Information: 6 : 1: Query Closed
Done.
mysql Information: 2 : 1: Connection Closed

The first number displayed in the trace message corresponds to the MySQL event type:

EventDescription
1ConnectionOpened: connection string
2ConnectionClosed:
3QueryOpened: mysql server thread id, query text
4ResultOpened: field count, affected rows (-1 if select), inserted id (-1 if select)
5ResultClosed: total rows read, rows skipped, size of resultset in bytes
6QueryClosed:
7StatementPrepared: prepared sql, statement id
8StatementExecuted: statement id, mysql server thread id
9StatementClosed: statement id
10NonQuery: [varies]
11UsageAdvisorWarning: usage advisor flag. NoIndex = 1, BadIndex = 2, SkippedRows = 3, SkippedColumns = 4, FieldConversion = 5.
12Warning: level, code, message
13Error: error number, error message

The second number displayed in the trace message is the connection count.

Although this example uses the ConsoleTraceListener, any of the other standard listeners could have been used. Another possibility is to create a custom listener that uses the information passed using the TraceEvent method. For example, a custom trace listener could be created to perform active monitoring of the MySQL event messages, rather than simply writing these to an output device.

It is also possible to add listeners to the MySQL Trace Source at run time. This can be done with the following code:

MySqlTrace.Listeners.Add(new ConsoleTraceListener());

MySQL Connector/NET 6.3.2 introduced the ability to switch tracing on and off at run time. This can be achieved using the calls MySqlTrace.EnableQueryAnalyzer(string host, int postInterval) and MySqlTrace.DisableQueryAnalyzer(). The parameter host is the URL of the MySQL Enterprise Monitor server to monitor. The parameter postInterval is how often the data should be periodically posted to MySQL Enterprise Monitor, in seconds.

22.2.5.14.2. Building Custom Listeners

To build custom listeners that work with the MySQL Connector/NET Trace Source, it is necessary to understand the key methods used, and the event data formats used.

The main method involved in passing trace messages is the TraceSource.TraceEvent method. This has the prototype:

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

This trace source method will process the list of attached listeners and call the listener's TraceListener.TraceEvent method. The prototype for the TraceListener.TraceEvent method is as follows:

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

The first three parameters are used in the standard as defined by Microsoft. The last three parameters contain MySQL-specifc trace information. Each of these parameters is now discussed in more detail.

int id

This is a MySQL-specific identifier. It identifies the MySQL event type that has occurred, resulting in a trace message being generated. This value is defined by the MySqlTraceEventType public enum contained in the MySQL Connector/NET code:

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

The MySQL event type also determines the contents passed using the parameter params Object[] args. The nature of the args parameters are described in further detail in the following material.

string format

This is the format string that contains zero or more format items, which correspond to objects in the args array. This would be used by a listener such as ConsoleTraceListener to write a message to the output device.

params Object[] args

This is a list of objects that depends on the MySQL event type, id. However, the first parameter passed using this list is always the driver id. The driver id is a unique number that is incremented each time the connector is opened. This enables groups of queries on the same connection to be identified. The parameters that follow driver id depend of the MySQL event id, and are as follows:

MySQL-specific event typeArguments (params Object[] args)
ConnectionOpenedConnection string
ConnectionClosedNo additional parameters
QueryOpenedmysql server thread id, query text
ResultOpenedfield count, affected rows (-1 if select), inserted id (-1 if select)
ResultClosedtotal rows read, rows skipped, size of resultset in bytes
QueryClosedNo additional parameters
StatementPreparedprepared sql, statement id
StatementExecutedstatement id, mysql server thread id
StatementClosedstatement id
NonQueryVaries
UsageAdvisorWarningusage advisor flag. NoIndex = 1, BadIndex = 2, SkippedRows = 3, SkippedColumns = 4, FieldConversion = 5.
Warninglevel, code, message
Errorerror number, error message

This information will allow you to create custom trace listeners that can actively monitor the MySQL-specific events.

Copyright © 2010-2020 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout