| Bug #52747 | Continuous exception throwing on an open connection after first exception | ||
|---|---|---|---|
| Submitted: | 11 Apr 2010 19:31 | Modified: | 12 Apr 2010 15:21 |
| Reporter: | Jalal Chaer | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / NET | Severity: | S3 (Non-critical) |
| Version: | 6.0.4 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[12 Apr 2010 7:46]
Tonci Grgin
Hi Jalal and thanks for your report. You got me confused... You mention *batch* update yet there is *no* batch update in your test case. You (say you) use batch update and you use GUID's yet I do not see your connection string on which these options heavily depend... and so on. Now, can you please check on my test case in, say, Bug#50123 and modify your test case so that it includes all the necessary info. Change the synopsis too, if needs be, cause how I see it it has nothing to do with batch updates. Now, if I disregard batching and GUIDs completely, I have no problems running my test case against trunk (latest c/NET sources) so I'd say this is not a bug: using (MySqlConnection cn = new MySqlConnection("DataSource=**;Database=test;UserID=**;Password=**;PORT=**;logging=True;charset=utf8;Allow User Variables=true;Allow Batch=true; Ignore prepare=false; Use old syntax=false")) { cn.Open(); MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS bug52747", cn); cmdCreateTable.ExecuteNonQuery(); cmdCreateTable.CommandText = "CREATE TABLE `bug52747`(`column1` VARCHAR(16) NOT NULL PRIMARY KEY, `column2` VARCHAR(16) NOT NULL, UNIQUE KEY_Col2 (column2)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"; cmdCreateTable.ExecuteNonQuery(); MySqlCommand comm = new MySqlCommand(); comm.Connection = cn; comm.CommandText = "INSERT INTO `bug52747` (`column1`, `column2`) VALUES ('row_1_col1', 'row_1_col2'), ('row_2_col1', 'row_2_col2'), ('row_3_col1', 'row_3_col2')"; try { comm.ExecuteNonQuery(); } catch (Exception ex) { Console.Out.WriteLine(DateTime.UtcNow.ToLongTimeString() + " " + "Exception: " + ex.Message); //throw; } comm.CommandText = "SELECT * FROM bug52747"; MySqlDataReader reader = comm.ExecuteReader(); while (reader.Read()) { Console.WriteLine("GetString: " + reader.GetString(0)); } reader.Close(); //Faulty record comm.CommandText = "INSERT INTO `bug52747` (`column1`, `column2`) VALUES ('row_4_col1', 'row_1_col2')"; try { comm.ExecuteNonQuery(); } catch (Exception ex) { Console.Out.WriteLine(DateTime.UtcNow.ToLongTimeString() + " " + "Exception: " + ex.Message); //throw; } comm.CommandText = "SELECT * FROM bug52747"; reader = comm.ExecuteReader(); reader.Read(); Console.Out.WriteLine("First col, first row: " + reader.GetString("column1")); reader.Close(); } Output: ... GetString: row_1_col1 GetString: row_2_col1 GetString: row_3_col1 ... A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll 7:42:51 Exception: Duplicate entry 'row_1_col2' for key 'KEY_Col2' First col, first row: row_1_col1
[12 Apr 2010 8:45]
Jalal Chaer
I don't know why you changed the test, its clear and obvious, forget about GUIDs its not the issue. What I meant by a *batch* is simply a bunch of inserts in a single connection session, no connection string parameters whatsoever (defaults). try to run it as is please and tell me what you get ... thanks
[12 Apr 2010 8:48]
Jalal Chaer
By the way, your test is wrong. You are adding the violating record at the end, hence no record after it. What I was testing is an error thrown in the *middle* of a transaction, hence the continuous re-throwing of the exception I hope this clears the issue. thanks
[12 Apr 2010 9:03]
Tonci Grgin
> I don't know why you changed the test, its clear and obvious, forget about GUIDs its not the issue. I changed it because *you* did not provide all info necessary for test to show unique error caused by single failure in our code. Too many places were left blank. If GUID's are not the issue then you should have written the test without them as GUID behavior is connection-string dependent and you did not provide it. Is it clearer now? > What I meant by a *batch* is simply a bunch of inserts in a single connection session, no connection string parameters whatsoever (defaults). If we are to run each others code and communicate in English we should be as punctual as possible and not use words as we like. So, please check c/NET documentation and learn the meaning of "batch inserts" as opposed to "a bunch of inserts". > try to run it as is please and tell me what you get ... Sure > By the way, your test is wrong. You are adding the violating record at the end, hence no record after it. Noted. Although if the error block is cleared (as appears obvious from my opening datareader after faulty block succeeding) I do not expect the test to fail. > What I was testing is an error thrown in the *middle* of a transaction, hence the continuous re-throwing of the exception. I hope this clears the issue. No it does not as there are no transactions nor transaction scope in your test. I believe what you meant is "... the faulty command is in the middle of my INSERT's...", right? Nothing to do with transactions, right? So, I think I finally get it... Issue a bunch of INSERT's in the loop where the middle one is faulty against the UNIQUE key and try to open datareader after each of the inserts and you'll see that error-block is not cleared. Does this sound about right?
[12 Apr 2010 9:39]
Tonci Grgin
using (MySqlConnection cn = new MySqlConnection("DataSource=**;Database=**;UserID=**;Password=**;PORT=**;"))
{
//logging=True;charset=utf8;Allow User Variables=true;Allow Batch=true; Ignore prepare=false; Use old syntax=false
cn.Open();
MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS bug52747", cn);
cmdCreateTable.ExecuteNonQuery();
cmdCreateTable.CommandText = "CREATE TABLE `bug52747`(`column1` VARCHAR(16) NOT NULL PRIMARY KEY, `column2` VARCHAR(16) NOT NULL, UNIQUE KEY_Col2 (column2)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
cmdCreateTable.ExecuteNonQuery();
MySqlCommand comm = new MySqlCommand();
comm.Connection = cn;
comm.CommandText = "INSERT INTO `bug52747` (`column1`, `column2`) VALUES ('row_1_col1', 'row_1_col2'), ('row_2_col1', 'row_2_col2'), ('row_3_col1', 'row_3_col2')";
try
{
comm.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.Out.WriteLine(DateTime.UtcNow.ToLongTimeString() + " " + "Exception: " + ex.Message);
//throw;
}
comm.CommandText = "SELECT * FROM bug52747";
MySqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("GetString: " + reader.GetString(0));
}
reader.Close();
//Faulty record
comm.CommandText = "INSERT INTO `bug52747` (`column1`, `column2`) VALUES ('row_4_col1', 'row_1_col2')";
try
{
comm.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.Out.WriteLine(DateTime.UtcNow.ToLongTimeString() + " " + "Exception: " + ex.Message);
//throw;
}
comm.CommandText = "SELECT * FROM bug52747";
reader = comm.ExecuteReader();
reader.Read();
Console.Out.WriteLine("First col, first row: " + reader.GetString("column1"));
reader.Close();
//Correct records again
comm.CommandText = "INSERT INTO `bug52747` (`column1`, `column2`) VALUES ('row_4_col1', 'row_4_col2'), ('row_5_col1', 'row_5_col2'), ('row_6_col1', 'row_6_col2')";
try
{
comm.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.Out.WriteLine(DateTime.UtcNow.ToLongTimeString() + " " + "Exception: " + ex.Message);
//throw;
}
comm.CommandText = "SELECT * FROM bug52747";
reader = comm.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("GetString: " + reader.GetString(0));
}
reader.Close();
//Plain insert worked, let's try PS
for (int i = 0; i < 10; i++)
{
MySqlCommand cmd = new MySqlCommand("INSERT INTO bug52747 VALUES(@one, @two)", cn);
if (i != 5) {
cmd.Parameters.AddWithValue("@one", i + "-1-PS");
cmd.Parameters.AddWithValue("@two", i + "-2-PS");
} else {
cmd.Parameters.AddWithValue("@one", i + "-1-PS");
cmd.Parameters.AddWithValue("@two", "3-2-PS"); //faulty record against UNIQUE key
}
cmd.Prepare();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.Out.WriteLine(DateTime.UtcNow.ToLongTimeString() + " " + "Exception: " + ex.Message);
//throw;
}
cmd.Parameters.Clear();
cmd.CommandText = "SELECT * FROM bug52747 WHERE column1 = @one";
cmd.Parameters.AddWithValue("@one", i + "-1-PS");
using (MySqlDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
Console.WriteLine(r.GetString(0));
}
}
}
}
GetString: row_1_col1
GetString: row_2_col1
GetString: row_3_col1
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
9:35:47 Exception: Duplicate entry 'row_1_col2' for key 'KEY_Col2'
First col, first row: row_1_col1
GetString: row_1_col1
GetString: row_2_col1
GetString: row_3_col1
GetString: row_4_col1
GetString: row_5_col1
GetString: row_6_col1
0-1-PS
1-1-PS
2-1-PS
3-1-PS
4-1-PS
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll
9:35:47 Exception: Duplicate entry '3-2-PS' for key 'KEY_Col2'
6-1-PS
7-1-PS
8-1-PS
9-1-PS
[12 Apr 2010 9:43]
Jalal Chaer
Sorry for troubling you with my *own* terms. I thought we are developers and we understand the words without implying our *strict* definitions for them, if not , the test case will speak for itself I think, yes, that's pretty much it. I don't know if GUIDs has any effect on the bug, you can check with it or without it. Thanks
[12 Apr 2010 9:45]
Tonci Grgin
Jalal, as you can see, I tried your code in second part of my test and it still works.
There is a lot of bad habits in your FOR loop. Although compiler will allow you to use constructs such as "MySqlCommand cmd = new MySqlCommand(" inside the loop (as it creates/destroys objects on each iteration) you should be aware that GC is *not* running on each destroy so may end up filling your memory rather quickly and your program is slower for the execution time of each variable with constructor. It would be better if everything was defined just once and outside the loop.
Also, I noticed you did not clear the parameters used on same command object in two different contexts (two parameters insert and one parameter select). Depending on connection string options used this could pose a problem too.
All in all, I don't know what to recommend to you but to try c/NET 6.2 or 6.3 as we no longer support 6.0.
[12 Apr 2010 9:57]
Jalal Chaer
Well... I agree. But thats a test. Not a production code ! Even though, you didn't try to run my test and find out why the bug is there. You are rewriting the functionality your way, which may not show the bug. I tried to upload my project its above 500K ... I don't know where to put it. The real bug appears on NHiberate when I try to open a Session and insert about 200 records, one of them is faulty and the rest are not inserted. Unfortunetly, NHibernate MySql driver is stable with 6.0.4 version (if you know anything please tell) Thanks
[12 Apr 2010 10:04]
Tonci Grgin
Jalal, entire project is not relevant if you can confirm your code still fails against newer c/NET version (and that mine works against 6.0.4). Also, I honestly have no idea what NHibernate is and how it works. My suggestion would be to file a bug report to them and see how they handle propagation of errors thrown. My guess is NHibernate is the one breaking further execution. I still see no bug in our SW here.
[12 Apr 2010 10:36]
Jalal Chaer
great conclusion Can you tell me why my test is failing ? Thanks
[12 Apr 2010 10:45]
Tonci Grgin
Jalal, no. And here's why: o I am not to test deprecated versions as there will be no fixes for them anyways. o I am not NHibernate beta tester nor does *MySQL* BugsDB present good place for testing 3rd party SW. o If I do everything what will be your job? ;-) You are free to take away NHibernate level and test yours and my code against whichever version of c/NET you like drawing the conclusions that might even lead to reopening of this report or filing the report to NHibernate. Until I see you testing without NHibernate I will wonder what this report does in our BugsDB. So, now what do the NHibernate devs say about this? Or are they too precious to be bothered?
[12 Apr 2010 11:05]
Jalal Chaer
What are you talking about? !!!!!!!!!!!!!!!! > Until I see you testing without NHibernate I will wonder what this report does in our BugsDB. I gave you a simple test with nothing NHibernate about it. You simply didn't bother to run MY TEST and started making your own assumptions. If this is no a place to "Report a Bug", or you are not willing to corporate, please let someone more experienced solve it. Very simple....
[12 Apr 2010 11:09]
Jalal Chaer
public static void GuidOnWhereClauseWithException()
{
//private static string connectionString = "server=localhost;user id=root;password=your_password;persist security info=True;database=testGuid";
execSQL("DROP TABLE IF EXISTS Test");
execSQL(@"CREATE TABLE Test(id INT, g BINARY(16), n varchar(20), PRIMARY KEY (g), UNIQUE KEY idx_name (n))");
var conn = new MySqlConnection(connectionString);
var passed = false;
try
{
conn.Open();
for (int i = 0; i < 5; i++)
{
if (i == 3 && !passed) // try to violate 'Unique' key constraint
{
i = 2;
passed = true;
}
Guid guid = Guid.NewGuid();
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(1, @g, @n)", conn);
cmd.Parameters.Add(new MySqlParameter("@g", MySqlDbType.Guid));
cmd.Parameters[0].Value = guid;
cmd.Parameters.Add(new MySqlParameter("@n", MySqlDbType.VarChar));
cmd.Parameters[1].Value = "name" + i;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception exc)
{
exc.ToString();
}
cmd.CommandText = "SELECT * FROM Test WHERE g=@g";
cmd.Parameters[0].Value = guid.ToString("D");
using (MySqlDataReader r = cmd.ExecuteReader())
{
r.Read();
Guid g = r.GetGuid("g");
Console.WriteLine((guid == g) ? "They are equal" : "They are not equal");
}
}
}
finally
{
conn.Close();
}
}
private static void execSQL(string query)
{
var conn = new MySqlConnection("server=localhost;user id=root;password=your_password;persist security info=True;database=testGuid");
MySqlCommand cmd = new MySqlCommand(query, conn);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
}
}
[12 Apr 2010 12:39]
Tonci Grgin
> [12 Apr 13:05] Jalal Chaer <jalchr@hotmail.com> What are you talking about? !!!!!!!!!!!!!!!! Sorry, my mistake. As I said I know nothing of that. > I gave you a simple test with nothing NHibernate about it. You simply didn't bother to run MY TEST and started making your own assumptions. Jalal, I am not here for you to abuse but here are some more "assumptions" you never bothered to provide more info on, which are more than relevant in your case: o What engine is your table? o What MySQL server version do you use? o What is your connection default charset? and so on and so on... Asking a good question is hard and by asking correctly you show respect for me and what I do. Just count how many things I had to ask you since you posted the report. > [12 Apr 10:45] Jalal Chaer I don't know why you changed the test, its clear and obvious, forget about GUIDs its not the issue. So, how does my test differ from yours (taking in account the above)? Do you want me to do code-review for free? That is just not an option. It is indeed different in that I extended your test to check *both* complete INSERT statements and INSERT via PS. I also run a batch insert test as I was mislead by your definition of problem. And where are we now? I still don't know if yours (or mine) stand alone test case works on GA releases of c/NET on your box, I do not know which version of MySQL server you are using, I do not know the engine of your table nor the charset used for communication. And I could have dealt with several reports by other people in all this time. However, you are free to continue hostile behavior towards me, instead of doing some work, and wait for someone else to pick up on this although it is less than likely. Switching to Java or ODBC or PHP also won't cut it as I do those too :) So much for "experience".
[12 Apr 2010 15:21]
Jalal Chaer
As for details, you should fix the "Report a bug" form, before blaming the submitter, why he forget so many *important* details to mention. As for your experience, I was talking about "Human Skills" experience which seems you have none Finally, whenever someone submits a test case, run the test case as is... exactly as he typed it, no one ever asked you to do a code check ... I don't need your technical expertise. I agree its my fault to come here the first place and trying to contribute to other developers hard work I hope someone other than you continue with this, if any !!
[8 Feb 2012 4:16]
Jeff Gilbert
Tonci Grgin has no business being allowed to communicate with other humans. What an A$$. I came looking for information and all I get is an Oracle employee abusing a customer.
Description: I have opened a connection to the database, I was trying to add 5 records in a batch. The 3 record throw a constraint violation exception was thrown (Unique constraint). The error is handled appropriately, and the execution continues. Unfortunately, the error continues to be thrown for the rest of the records. Something is wrong with the connector. Although, this appears on 6.0.4 (the version which is compatible with NHibernate), I think the bug is still there for later versions. How to repeat: public static void GuidOnWhereClauseWithException() { // Add by code the record to the database with this/any GUID ... this will always work execSQL("DROP TABLE IF EXISTS Test"); execSQL(@"CREATE TABLE Test(id INT, g BINARY(16), n varchar(20), PRIMARY KEY (g), UNIQUE KEY idx_name (n))"); var conn = new MySqlConnection(connectionString); var passed = false; try { conn.Open(); for (int i = 0; i < 5; i++) { if (i == 3 && !passed) // try to violate 'Unique' key constraint { i = 2; passed = true; } Guid guid = Guid.NewGuid(); MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(1, @g, @n)", conn); cmd.Parameters.Add(new MySqlParameter("@g", MySqlDbType.Guid)); cmd.Parameters[0].Value = guid; cmd.Parameters.Add(new MySqlParameter("@n", MySqlDbType.VarChar)); cmd.Parameters[1].Value = "name" + i; try { cmd.ExecuteNonQuery(); } catch (Exception exc) { exc.ToString(); // intended exception thrown } cmd.CommandText = "SELECT * FROM Test WHERE g=@g"; cmd.Parameters[0].Value = guid.ToString("D"); using (MySqlDataReader r = cmd.ExecuteReader()) { r.Read(); Guid g = r.GetGuid("g"); // This will error when the exception is thrown earlier Console.WriteLine((guid == g) ? "They are equal" : "They are not equal"); } } } finally { conn.Close(); } } Suggested fix: I have no idea. May be you have to clear the buffers somewhere in the connector when an error happens