22.2.5.14. Using the MySQL Connector/Net Trace Source Object

TraceSource ts = new TraceSource("mysql");

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

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

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

ts.TraceInformation("Informational message");ts.TraceEvent(TraceEventType.Error, 3, "Optional error message");ts.TraceData(TraceEventType.Error, 3, ex); // pass exception objectts.Flush();...ts.Close();

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:

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.

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:

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