Viewing MariaDB Trace Information
This section describes how to set up your application to view MariaDB 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 MariaDB...');
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 MariaDB... 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 MariaDB 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 MariaDB event messages, rather than simply writing these to an output device.
It is also possible to add listeners to the MariaDB 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 MariaDB Enterprise Monitor server to monitor. The parameter postInterval is how often to post the data to MariaDB Enterprise Monitor, in seconds.