I have a question on MySQLDirect for .NET 2.0 Version 3.20.6,
I am getting the Error during query, but this error should be a different one. Here is the detailed explanation and my Environment info:
Environment Info.
Windows XP Pro SP2
Visual Studio 2005, Project – WinForms in C#
MySQL Server 5.0.18
MySQLDirect .NET2 Version 3.20.6 in “Direct” mode and in “Unicode”
Using InnoDB storage engine.
Error Details: Using Two concurrent Connections (Client 1, Client 2)
Client 1 steps:
Open Connection
Set AUTOCOMMIT = 0
START TRANSACTION
Issue “SELECT * FROM … WHERE … FOR UPDATE
Show Form1, where the user can modify the record
Client 2 steps:
Open Connection
Set AUTOCOMMIT=0
START TRANSACTION
Issue “SELECT * FROM … WHERE … FOR UPDATE” for the same record as Client 1
innodb_lock_wait_timeout occurs, but in the try-catch block MySqlException shows: CODE=2013 “Lost Connection during query”
It suppose to give me Error code 1205 “lock wait timeout exceeded, try to restart transaction” instead of the error above.
Then I try to Rollback the Transaction, but it still gives me CODE=2013 “Lost Connection during query”. Instead of 1205 “lock wait timeout exceeded, try to restart transaction”.
Show Form1, where the user can modify the record
…….change the data
Click OK button
if ERROR then ROLLBACK, else COMMIT
SET AUTOCOMMIT=1
Close Connection
Client 1 steps:
…….change the data
Click OK button
if ERROR then ROLLBACK, else COMMIT
SET AUTOCOMMIT=1
Close Connection
Also, when I simulate the same scenario but using MySQL command line client, I get the correct Error: 1205 after the timeout on the locked record.
Here are SQL statements below for table creation:
Code: Select all
USE test;
CREATE table table1 (
id int AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
description VARCHAR(254) NULL,
UNIQUE INDEX namekey(name)) ENGINE = InnoDB;
insert into table1(name, description) values("Record1", "Description1");
insert into table1(name, description) values("Record2", "Description2");
Program.cs
Code: Select all
using System;
using System.Collections.Generic;
using System.Windows.Forms;
namespace MySQLDirectTest {
static class Program {
///
/// The main entry point for the application.
///
[STAThread]
static void Main() {
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
}
Code: Select all
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using CoreLab.MySql;
namespace MySQLDirectTest {
public partial class Form1 : Form {
private MySqlTransaction myTransaction;
private Int32 currentRecordID;
private String recordNameStr;
private String recordDescriptionStr;
private Boolean commited = false;
private MySqlCommand setAutocommitCommand;
public Form1() {
InitializeComponent();
currentRecordID = 1;
}
private void formShown(object sender, EventArgs e) {
// Open Connection
try {
mySqlConnection1.Open();
}
catch (MySqlException ex) {
MessageBox.Show(ex.Message);
this.Close();
}
// Turn AUTOCOMMIT off
try {
setAutocommitCommand = new MySqlCommand("SET AUTOCOMMIT=0",mySqlConnection1);
setAutocommitCommand.ExecuteNonQuery();
}
catch (MySqlException mysqlEx1) {
MessageBox.Show(mysqlEx1.Message);
}
// Start Transaction
try {
myTransaction = this.mySqlConnection1.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
}
catch (MySqlException ex) {
myTransaction.Rollback();
MessageBox.Show(ex.Message);
this.Close();
}
selectRecordForUpdate();
}
private void selectRecordForUpdate() {
if (mySqlConnection1.State != ConnectionState.Open) {
mySqlConnection1.Open();
}
MySqlCommand myCommand = new CoreLab.MySql.MySqlCommand("SELECT * FROM table1 WHERE id = " + currentRecordID + " FOR UPDATE", this.mySqlConnection1, myTransaction);
MySqlDataReader myReader = null;
try {
myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow);
while (myReader.Read() && myReader.ResultCount == 1) {
this.textBox1.Text = myReader.GetInt32(myReader.GetOrdinal("id")).ToString();
this.textBox2.Text = myReader.GetString(myReader.GetOrdinal("name"));
this.textBox3.Text = myReader.GetString(myReader.GetOrdinal("description"));
}
}
catch (MySqlException mysqlEx) {
String mess = mysqlEx.Message;
try {
mySqlConnection1.Rollback();
}
catch (MySqlException ex1) {
String mess1 = ex1.Message;
MessageBox.Show(ex1.Message);
this.Close();
}
}
catch (Exception ex) {
String mess = ex.Message;
MessageBox.Show(ex.Message);
try {
mySqlConnection1.Rollback();
}
catch (MySqlException ex1) {
MessageBox.Show(ex1.Message);
this.Close();
}
}
finally {
}
}
private void okBtn_Click(object sender, EventArgs e) {
String queryText = "";
queryText = "UPDATE table1 SET name = '" + this.textBox2.Text +
"', description = '" + this.textBox3.Text +
"' WHERE id = " + this.currentRecordID;
try {
MySqlCommand myCommand = new MySqlCommand(queryText, this.mySqlConnection1);
Int32 rowsAffected = myCommand.ExecuteNonQuery();
textBox2.Enabled = false;
textBox3.Enabled = false;
this.mySqlConnection1.Commit();
}
catch (MySqlException ex) {
MessageBox.Show(ex.Message);
}
// Turn AUTOCOMMIT back on
try {
setAutocommitCommand.CommandText = "SET AUTOCOMMIT=0";
setAutocommitCommand.ExecuteNonQuery();
}
catch (MySqlException mysqlEx1) {
}
}
private void cancelBtn_Click(object sender, EventArgs e) {
this.mySqlConnection1.Rollback();
// Turn AUTOCOMMIT back on
try {
setAutocommitCommand.CommandText = "SET AUTOCOMMIT=0";
setAutocommitCommand.ExecuteNonQuery();
}
catch (MySqlException mysqlEx1) {
}
this.Close();
}
private void formClosed(object sender, FormClosedEventArgs e) {
this.mySqlConnection1.Close();
}
}
}
Code: Select all
namespace MySQLDirectTest {
partial class Form1 {
///
/// Required designer variable.
///
private System.ComponentModel.IContainer components = null;
///
/// Clean up any resources being used.
///
/// true if managed resources should be disposed; otherwise, false.
protected override void Dispose(bool disposing) {
if (disposing && (components != null)) {
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///
private void InitializeComponent() {
this.mySqlConnection1 = new CoreLab.MySql.MySqlConnection();
this.okBtn = new System.Windows.Forms.Button();
this.cancelBtn = new System.Windows.Forms.Button();
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.label3 = new System.Windows.Forms.Label();
this.textBox1 = new System.Windows.Forms.TextBox();
this.textBox2 = new System.Windows.Forms.TextBox();
this.textBox3 = new System.Windows.Forms.TextBox();
this.SuspendLayout();
//
// mySqlConnection1
//
this.mySqlConnection1.ConnectionString = "User Id=root;Password=password;Host=localhost;Database=test;Unicode=True;";
this.mySqlConnection1.Name = "mySqlConnection1";
this.mySqlConnection1.Unicode = true;
//
// okBtn
//
this.okBtn.Location = new System.Drawing.Point(262, 211);
this.okBtn.Name = "okBtn";
this.okBtn.Size = new System.Drawing.Size(75, 23);
this.okBtn.TabIndex = 0;
this.okBtn.Text = "OK";
this.okBtn.UseVisualStyleBackColor = true;
this.okBtn.Click += new System.EventHandler(this.okBtn_Click);
//
// cancelBtn
//
this.cancelBtn.Location = new System.Drawing.Point(352, 211);
this.cancelBtn.Name = "cancelBtn";
this.cancelBtn.Size = new System.Drawing.Size(75, 23);
this.cancelBtn.TabIndex = 1;
this.cancelBtn.Text = "Cancel";
this.cancelBtn.UseVisualStyleBackColor = true;
this.cancelBtn.Click += new System.EventHandler(this.cancelBtn_Click);
//
// label1
//
this.label1.AutoSize = true;
this.label1.Location = new System.Drawing.Point(12, 35);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(59, 13);
this.label1.TabIndex = 2;
this.label1.Text = "Record ID:";
//
// label2
//
this.label2.AutoSize = true;
this.label2.Location = new System.Drawing.Point(12, 73);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(76, 13);
this.label2.TabIndex = 3;
this.label2.Text = "Record Name:";
//
// label3
//
this.label3.AutoSize = true;
this.label3.Location = new System.Drawing.Point(13, 111);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(63, 13);
this.label3.TabIndex = 4;
this.label3.Text = "Description:";
//
// textBox1
//
this.textBox1.Location = new System.Drawing.Point(119, 32);
this.textBox1.Name = "textBox1";
this.textBox1.ReadOnly = true;
this.textBox1.Size = new System.Drawing.Size(142, 20);
this.textBox1.TabIndex = 5;
//
// textBox2
//
this.textBox2.Location = new System.Drawing.Point(119, 70);
this.textBox2.Name = "textBox2";
this.textBox2.Size = new System.Drawing.Size(205, 20);
this.textBox2.TabIndex = 7;
//
// textBox3
//
this.textBox3.Location = new System.Drawing.Point(119, 108);
this.textBox3.Name = "textBox3";
this.textBox3.Size = new System.Drawing.Size(365, 20);
this.textBox3.TabIndex = 8;
//
// Form1
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(496, 255);
this.Controls.Add(this.textBox3);
this.Controls.Add(this.textBox2);
this.Controls.Add(this.textBox1);
this.Controls.Add(this.label3);
this.Controls.Add(this.label2);
this.Controls.Add(this.label1);
this.Controls.Add(this.cancelBtn);
this.Controls.Add(this.okBtn);
this.Name = "Form1";
this.Text = "Form1";
this.FormClosed += new System.Windows.Forms.FormClosedEventHandler(this.formClosed);
this.Shown += new System.EventHandler(this.formShown);
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
private CoreLab.MySql.MySqlConnection mySqlConnection1;
private System.Windows.Forms.Button okBtn;
private System.Windows.Forms.Button cancelBtn;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.TextBox textBox1;
private System.Windows.Forms.TextBox textBox2;
private System.Windows.Forms.TextBox textBox3;
}
}
So, am i doing something wrong, or it is a bug?
Thank you for your time and consideration.
Kaz