Spec-Zone .ru
спецификации, руководства, описания, API

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:

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, 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. There are a wide range of listeners that can be attached to the trace source, so that trace messages can be written out to various destinations. You can also 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 runtime, use code such as the following:

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

Then, call methods on the 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, invoke the Flush() method. When you are finished using a trace source, 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 objectts.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.");        }    }}

This 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 VARIABLESmysql Information: 4 : 1: Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1mysql Information: 5 : 1: Resultset Closed. Total rows=272, skipped rows=0, size (bytes)=7058mysql Information: 6 : 1: Query Closedmysql Information: 3 : 1: Query Opened: SHOW COLLATIONmysql Information: 4 : 1: Resultset Opened: field(s) = 6, affected rows = -1, inserted id = -1mysql Information: 5 : 1: Resultset Closed. Total rows=127, skipped rows=0, size (bytes)=4102mysql Information: 6 : 1: Query Closedmysql Information: 3 : 1: Query Opened: SET character_set_results=NULLmysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0mysql Information: 6 : 1: Query Closedmysql Information: 10 : 1: Set Database: worldmysql 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 = -1American Samoa -- George W. BushAustralia -- Elisabeth II...Wallis and Futuna -- Jacques ChiracVanuatu -- John BaniUnited States Minor Outlying Islands -- George W. Bushmysql Information: 5 : 1: Resultset Closed. Total rows=28, skipped rows=0, size (bytes)=788mysql Information: 6 : 1: Query ClosedDone.mysql Information: 2 : 1: Connection Closed

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

Event Description
1 ConnectionOpened: connection string
2 ConnectionClosed:
3 QueryOpened: mysql server thread id, query text
4 ResultOpened: field count, affected rows (-1 if select), inserted id (-1 if select)
5 ResultClosed: total rows read, rows skipped, size of resultset in bytes
6 QueryClosed:
7 StatementPrepared: prepared sql, statement id
8 StatementExecuted: statement id, mysql server thread id
9 StatementClosed: statement id
10 NonQuery: [varies]
11 UsageAdvisorWarning: usage advisor flag. NoIndex = 1, BadIndex = 2, SkippedRows = 3, SkippedColumns = 4, FieldConversion =5.
12 Warning: level, code, message
13 Error: 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 runtime. 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 runtime. 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 to post the data 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-specific 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 on the MySQL event id, and are as follows:

MySQL-specific event type Arguments (params Object[] args)
ConnectionOpened Connection string
ConnectionClosed No additional parameters
QueryOpened mysql server thread id, query text
ResultOpened field count, affected rows (-1 if select), inserted id (-1 if select)
ResultClosed total rows read, rows skipped, size of resultset in bytes
QueryClosed No additional parameters
StatementPrepared prepared sql, statement id
StatementExecuted statement id, mysql server thread id
StatementClosed statement id
NonQuery Varies
UsageAdvisorWarning usage advisor flag. NoIndex = 1, BadIndex = 2, SkippedRows = 3,SkippedColumns = 4, FieldConversion = 5.
Warning level, code, message
Error error number, error message

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