Stop of Dependency not working and after that the Start Method create one more Check

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
alegri
Posts: 1
Joined: Fri 22 Jul 2022 05:40

Stop of Dependency not working and after that the Start Method create one more Check

Post by alegri » Fri 22 Jul 2022 05:53

Hello everybody,

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 @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @[email protected]@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 [email protected]_SQL_MODE;
SET [email protected]_FOREIGN_KEY_CHECKS;
SET [email protected]_UNIQUE_CHECKS;


DmitryGm
Devart Team
Posts: 121
Joined: Fri 11 Dec 2020 10:27

Re: Stop of Dependency not working and after that the Start Method create one more Check

Post by DmitryGm » Wed 03 Aug 2022 16:57

Thank you for your report. We have reproduced the issue within .NET 6 project using NuGet packages.

Since you create .NET Framework project, you should use local assemblies (Devart.Data.dll, Devart.Data.MySql.dll) shipped with the installation of dotConnect for MySQL (not a NuGet). In that case MySqlDependency.Stop() works properly.

We try to fix the issue with .NET Core assemblies in the next releases of dotConnect for MySQL.

Post Reply