we have a problem with your MySql dependency. In the code behind you will find a corresponding program and a SQl file to set up the test database.
Our used Version of Devart Data MySql is 8.21.2066 and MariaDB in Version 10.6.5.
Within the main method of the source code the dependency is stopped and restarted several times if the user does not exit the application with q.
Stopping seems to have no effect and starting always starts a new instance of the monitoring. Which leads to a double query load at the second start. This new instance is created as often as the start is called.
However, the old instances continue to run despite the stop and can lead to a significant system load.
The whole thing can be observed very well in the DbMonitor.
For us it looks like that the connection is not found at stop based on the connection string and therefore the connection is not terminated cleanly.
It would be great if you could take a look at this.
If you have any questions, I am of course at your disposal.
Best regards
Code: Select all
using Devart.Data.MySql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
namespace Dependency_TestApp
{
/// <summary>
/// Class Program.
/// </summary>
class Program
{
#region private member
private static string m_strUser = "test";
private static string m_strPassword = "test";
private static string m_strIp = "127.0.0.1";
private static string m_strDbName = "test";
private static MySqlMonitor m_mysqlMonitor;
private static string m_strOldHashValue = "";
private static object m_objlock;
private static int m_iCount = 0;
private static MySqlConnection m_connLocal;
#endregion
#region Propertys
#region Connection String
/// <summary>
/// Gets the connection string.
/// </summary>
/// <value>The connection string.</value>
private static string ConnectionString
{
get
{
MySqlConnectionStringBuilder myCSB = new MySqlConnectionStringBuilder();
myCSB.Host = m_strIp;
myCSB.UserId = m_strUser;
myCSB.Password = m_strPassword;
myCSB.Database = m_strDbName;
myCSB.Unicode = true;
return myCSB.ConnectionString;
}
}
#endregion
#endregion
#region Main
/// <summary>
/// Defines the entry point of the application.
/// </summary>
/// <param name="args">The arguments.</param>
public static void Main(string[] args)
{
m_mysqlMonitor = new MySqlMonitor();
m_mysqlMonitor.IsActive = true;
m_objlock = new object();
m_connLocal = new MySqlConnection(ConnectionString);
m_connLocal.Open();
MySqlCommand commandDepTblTestTime = new MySqlCommand("Select * from information_schema.global_status", m_connLocal);
MySqlCommand commandDepTblTest = new MySqlCommand("Select * from tbltest", m_connLocal);
MySqlDependency dependency = new MySqlDependency(commandDepTblTestTime, 100);
dependency.AddCommandDependency(commandDepTblTest);
dependency.OnCheck += Dependency_OnCheck;
dependency.OnChange += Dependency_OnChange;
var s = "";
do
{
MySqlDependency.Start(ConnectionString);
Console.WriteLine("Press q for quit or Return for restart!");
s = Console.ReadLine();
MySqlDependency.Stop(ConnectionString);
}
while (!s.Equals("q"));
dependency.OnCheck -= Dependency_OnCheck;
}
#endregion
#region On Check Event Handler for Change Tracking
/// <summary>
/// Handles the OnCheck event of the Dependency control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="OnCheckEventArgs"/> instance containing the event data.</param>
/// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
private static bool Dependency_OnCheck(object sender, OnCheckEventArgs e)
{
try
{
if (Monitor.TryEnter(m_objlock))
{
string value = string.Empty;
if (!e.Connection.State.Equals(ConnectionState.Open))
return false;
if (e.TableName == "test.tbltest")
{
StringBuilder str = new StringBuilder();
str.AppendLine($"Select ID, md5({m_iCount}) from tbltest Limit 1");
e.CheckCommand = new MySqlCommand(str.ToString(), e.Connection);
using (MySqlDataReader reader = e.CheckCommand.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
value = reader.GetString(1);
if (!string.IsNullOrEmpty(value))
{
if (string.IsNullOrEmpty(m_strOldHashValue) || !m_strOldHashValue.Equals(value))
{
m_strOldHashValue = value;
m_iCount++;
return true;
}
}
}
}
}
}
return false;
}
catch (Exception ex)
{
return false;
}
finally
{
Monitor.Exit(m_objlock);
}
}
#endregion
#region Handle on Change Event on each Change of On Check
/// <summary>
/// Handles the OnChange event of the Dependency control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="MySqlTableChangeEventArgs"/> instance containing the event data.</param>
private static void Dependency_OnChange(object sender, MySqlTableChangeEventArgs e)
{
if (e.TableName == "test.tbltest") RefreshLog();
}
#endregion
#region Log Refresh
/// <summary>
/// Refresh Log
/// </summary>
private static void RefreshLog()
{
var commandtext = $"SELECT s1.variable_value / s2.variable_value as query_per_second FROM information_schema.global_status " +
$"s1, information_schema.global_status s2 WHERE s1.variable_name = 'queries' AND s2.variable_name = 'uptime';";
if (!m_connLocal.State.Equals(ConnectionState.Open))
m_connLocal.Open();
var sqlcommand = new MySqlCommand(commandtext, m_connLocal);
using (MySqlDataReader reader = sqlcommand.ExecuteReader())
{
if(reader.HasRows)
{
reader.Read();
Console.WriteLine($"Query per Seconds {reader.GetString(0)} {m_iCount}");
}
}
}
#endregion
}
}
Code: Select all
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema test
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `test` ;
-- -----------------------------------------------------
-- Schema test
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8mb3 ;
USE `test` ;
-- -----------------------------------------------------
-- Table `test`.`tbltest`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test`.`tbltest` ;
CREATE TABLE IF NOT EXISTS `test`.`tbltest` (
`ID` INT(11) NOT NULL AUTO_INCREMENT,
`TimestampID` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
`Total` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`ID`))
ENGINE = InnoDB
AUTO_INCREMENT = 11
DEFAULT CHARACTER SET = utf8mb3;
INSERT INTO `test`.`tbltest`
(`TimestampID`, `Total`)
VALUES
('2022-06-10 07:11:53.000000', 1),('2022-06-10 07:14:53.000000',2),('2022-06-10 07:17:53.000000',3),('2022-06-10 07:21:53.000000',4),
('2022-06-10 07:23:53.000000',5),('2022-06-10 07:27:53.000000',6),
('2022-06-10 07:29:53.000000',7),('2022-06-10 07:31:53.000000',8),('2022-06-10 07:37:53.000000',9),('2022-06-10 07:44:53.000000',10);
DROP USER IF EXISTS 'test'@'%';
CREATE USER IF NOT EXISTS 'test'@'%' IDENTIFIED BY 'test';
GRANT EVENT, LOCK TABLES, SELECT, SHOW DATABASES ON *.* TO 'test'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, EVENT, TRIGGER, DELETE HISTORY ON test.* TO 'test'@'%';
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;