Lock wait timeout exceeded; try restarting transaction in MySQL

Today while I was trying to clean up the MySQL Database, I got the following error:
Error Code : 1205
Lock wait timeout exceeded; try restarting transaction
(0 ms taken)

I was surprise to see this error as the same query was working fine before few minutes and then what happen all of sudden. The error came because of some other guy was trying to delete records simultaneously without my knowledge and so the transaction got locked. After doing some Google search I could figure out the solution for it.

Here is what you have to do:

Just run “SHOW INNODB STATUS” in your query browser you will get the status of INNODB which contains LIST OF TRANSACTIONS FOR EACH SESSION. In that just find all the Active thread id’s, say you find one thread id = 148 and then just select and kill that thread id, like this: KILL 148.


That’s all you have to do.

Hope that helps.


Please leave your comments to help me improve my blog.
 
Thank You for visiting the Blog. Happy Coding! :)

How to find Max/Min ID in DataTable

These days I'm working on both ASP.NET and Silvelight. Though I'm working on ASP.NET after a year now, thought posting some of the articles on ASP.NET which I had in my mind but couldnt devote much time. Here is one of them.
How to find the Max/Min ID in DataTable. DataTable has Select Method by which you can get the related records as you do it in SQL or for any database. I'm creating an Employee DataTable which has EmployeeID, FirstName and Lastname columns and fill that with some dummy data.


DataTable objDT = new DataTable();
DataColumn objDC = new DataColumn("EmployeeID", System.Type.GetType("System.Int32"));
objDT.Columns.Add(objDC);
objDC = new DataColumn("FirstName", System.Type.GetType("System.String"));objDT.Columns.Add(objDC);
objDC = new DataColumn("LastName", System.Type.GetType("System.String"));

objDT.Columns.Add(objDC);

DataRow objDR = objDT.NewRow();
objDR["EmployeeID"] = 1;
objDR["FirstName"] = "Lalit";
objDR["LastName"] = "Sharma";
objDT.Rows.Add(objDR);

 objDR = objDT.NewRow();
objDR["EmployeeID"] = 2;
objDR["FirstName"] = "Aditi";
objDR["LastName"] = "Deodhar";
objDT.Rows.Add(objDR);

objDR = objDT.NewRow();
objDR["EmployeeID"] = 3;
objDR["FirstName"] = "Nimish";
objDR["LastName"] = "Navadkar";
objDT.Rows.Add(objDR);

objDR = objDT.NewRow();
objDR["EmployeeID"] = 4;
objDR["FirstName"] = "Pragati";
objDR["LastName"] = "Dukale";
objDT.Rows.Add(objDR);

objDR = objDT.NewRow();
objDR["EmployeeID"] = 5;
objDR["FirstName"] = "Rumpa";
objDR["LastName"] = "Gune";
objDT.Rows.Add(objDR);

objDR = objDT.NewRow();
objDR["EmployeeID"] = 6;
objDR["FirstName"] = "Abhijit";
objDR["LastName"] = "Parkhi";
objDT.Rows.Add(objDR);

//This is the select query for Max
string selectCommand = "Max(EmployeeID)";

//returns the Max EmploeeID
int nextID = Convert.ToInt32((object)objDT.Compute(selectCommand, string.Empty));
Likewise we can find Min ID in DataTable.
 
Hope that helps.
 
Please leave your comments to help me improve my blog.

Thankyou for visiting the Blog. Happy Coding! :)

How to find DataTable Row Number without ID column.

Hello All,

In ASP.NET one of my favourite topic is playing with DataSet and DataTable. Today one of my colleague asked me "how to find row number of DataTable if we dont have ID column".

Here is a simple way to do it:

int iRowNumber = objDT.Rows.IndexOf(objDT.Select("Name = Varsha")[0]);

iRowNumber will have Row number.

Hope that helps.

Please leave your comments to help me improve my blog.

Thank You for visiting the Blog. Happy Coding! :)

Query to MySQL from C# returns System.Byte[]

Hello All,

I'm working on Mysql for almost a year now but yesterday I got one issue which was killing me. I wrote a Stored Procedure in MySQL and execute it in MySQL Browser and it execute very well but when I execute the same Stored Procedure in asp.net and bind the returned DataSet with GridView, it always shows System.Byte[] in the Title Column.

The problem was that the columns which I had selected in that query was having DataType as LongText and LONGTEXT type is a kind of BLOB type (set of byte), so it was returning System.Byte[].

I checked it on google, tried different ways to fix it but nothing helped me and finally I thought firing a query on it and it worked. Something like this:

SELECT
           ID
           ,TitleName
FROM (

                   SELECT
                                  ID
                                  ,REPLACE(Title, "$", "'") AS TitleName
                   FROM
                                  tbEmployee
         ) A

This it what I did and its working fine.

Hope this helps you all.

Happy Coding :)