MySQL Bugs: #40033: NET Connector subject to crash

Bug #40033	NET Connector subject to crash
Submitted:	15 Oct 2008 5:16	Modified:	28 Oct 2008 1:50
Reporter:	Jared Sullivan (Silver Quality Contributor)
Status:	Duplicate
Category:	Connector/Net	Severity:	S3 (Non-critical)
Version:	5.2.3	OS:	Microsoft Windows (Any)
Assigned to:		Target Version:
Tags:	qc, key, dictionary

Description:
Hi,

Given Query which parses in QB, .NET Connector fails.

How to repeat:
	public MySql.Data.MySqlClient.MySqlDataReader DTR0; //handles NULLs alot better
	public MySql.Data.MySqlClient.MySqlConnection CON0;
	public MySql.Data.MySqlClient.MySqlCommand CMD0;

	private void Form1_Load(object sender, System.EventArgs e)
	{

		CON0 = new MySql.Data.MySqlClient.MySqlConnection();
		CON0.ConnectionString =
"server=localhost;database=information_schema;uid=root;pwd=;port=3306";
		CON0.Open();

	}
	private void Button1_Click(object sender, System.EventArgs e)
	{

	string zSQL = null;

		zSQL = "SELECT *, columns.ordinal_position FROM KEY_COLUMN_USAGE INNER JOIN COLUMNS ON
COLUMNS.TABLE_SCHEMA = KEY_COLUMN_USAGE.table_schema and columns.table_name =
KEY_COLUMN_USAGE.table_name and columns.column_name = KEY_COLUMN_USAGE.column_name WHERE
CONSTRAINT_SCHEMA  = 'cyclops' and constraint_name <> 'PRIMARY' ORDER BY
KEY_COLUMN_USAGE.table_name, columns.ordinal_position ASC";
		CMD0 = new MySql.Data.MySqlClient.MySqlCommand(zSQL, CON0);

			try
			{
			DTR0 = CMD0.ExecuteReader();
			}
			catch (Exception ex)
			{
			Trace.WriteLine(ex.Source.ToString() + " - " + ex.Message.ToString());
			System.Diagnostics.Debugger.Break();
			}

	}

Suggested fix:
Elaborate err msg, or work around the retardation present in NET Connector.

Sample Project

Attachment: NetConQuery.zip (application/x-zip-compressed, text), 11.71 KiB.

Hi Jared and thanks for your report.

What I believe is happening is that you expired some/several timeouts as this query truly
takes way too long to execute (bug is already filed against I__S being slow). On my almost
empty box it takes 30+ seconds in cl client:
0 rows in set (31.27 sec)

So, I'd suggest you to increase timeouts when dealing with I__S. To me it appears as !Bg.

-- SUCCEEDS
"select 1 as ex1, 2 as ex1"

-- FAILS
"SELECT *, columns.ordinal_position FROM KEY_COLUMN_USAGE INNER JOIN COLUMNS ON
COLUMNS.TABLE_SCHEMA = KEY_COLUMN_USAGE.table_schema and columns.table_name =
KEY_COLUMN_USAGE.table_name and columns.column_name = KEY_COLUMN_USAGE.column_name WHERE
CONSTRAINT_SCHEMA  = 'test' and constraint_name <> 'PRIMARY' ORDER BY
KEY_COLUMN_USAGE.table_name, columns.ordinal_position ASC"

-- ERROR
A first chance exception of type 'System.ArgumentException' occurred in mscorlib.dll
mscorlib - Item has already been added. Key in dictionary: 'ORDINAL_POSITION'  Key being
added: 'ordinal_position'

Please investigate again using 5.2.3 production release and ensure your error matches the
error I am reporting as a bug.  You may need to create table called `test` or `cyclops`
depending on my sample I gave.

Ok Jared, got it. Did you tried against MySQL server 6.x?

Jared, you know it does not work that way... I'm obliged to use latest sources there are.
So, using branches\5.2 fully updated I can not repeat your problem. All I see is what I
already suggested; query taking too long thus getting killed:
081015 15:50:59	      5 Connect     root@localhost on information_schema
		      5 Query       SHOW VARIABLES
		      5 Query       SHOW COLLATION
		      5 Query       SET NAMES utf8;SET character_set_results=NULL
		      5 Init DB     information_schema
		      5 Query       SELECT *, columns.ordinal_position FROM KEY_COLUMN_USAGE INNER JOIN
COLUMNS ON COLUMNS.TABLE_SCHEMA = KEY_COLUMN_USAGE.table_schema and columns.table_name =
KEY_COLUMN_USAGE.table_name and columns.column_name = KEY_COLUMN_USAGE.column_name WHERE
CONSTRAINT_SCHEMA  = 'cyclops' and constraint_name <> 'PRIMARY' ORDER BY
KEY_COLUMN_USAGE.table_name, columns.ordinal_position ASC
081015 15:51:00	      1 Query       SHOW /*!50000 GLOBAL */ VARIABLES
		      1 Query       SHOW INNODB STATUS
		      1 Query       SHOW /*!50000 GLOBAL */ STATUS
081015 15:51:29	      6 Connect     root@localhost on information_schema
		      6 Query       SHOW VARIABLES
		      6 Query       SHOW COLLATION
081015 15:51:30	      6 Query       SET NAMES utf8;SET character_set_results=NULL
		      6 Init DB     information_schema
		      6 Query       KILL QUERY 5
		      6 Quit       

Also, you're probably using VS2008 while I have only VS2005.

My test case (passes):
	        MySql.Data.MySqlClient.MySqlDataReader DTR0;
	        MySql.Data.MySqlClient.MySqlConnection CON0;
	        MySql.Data.MySqlClient.MySqlCommand CMD0;

		    CON0 = new MySql.Data.MySqlClient.MySqlConnection();
		    CON0.ConnectionString =
"server=localhost;database=information_schema;uid=root;pwd=;port=";
		    CON0.Open();

	        string zSQL = null;

		    zSQL = "SELECT *, columns.ordinal_position FROM KEY_COLUMN_USAGE INNER JOIN COLUMNS
ON COLUMNS.TABLE_SCHEMA = KEY_COLUMN_USAGE.table_schema and columns.table_name =
KEY_COLUMN_USAGE.table_name and columns.column_name = KEY_COLUMN_USAGE.column_name WHERE
CONSTRAINT_SCHEMA  = 'cyclops' and constraint_name <> 'PRIMARY' ORDER BY
KEY_COLUMN_USAGE.table_name, columns.ordinal_position ASC";
		    CMD0 = new MySql.Data.MySqlClient.MySqlCommand(zSQL, CON0);
            CMD0.CommandTimeout = 50;

			try
			    {
			        DTR0 = CMD0.ExecuteReader();
                    DTR0.Close();
                    CMD0.Dispose();
                    CON0.Close();
                    CON0.Dispose();
                    Trace.WriteLine("Done!");
			    }
			catch (Exception ex)
			    {
			        Trace.WriteLine(ex.Source.ToString() + " - " + ex.Message.ToString());
			        System.Diagnostics.Debugger.Break();
			    }

        }

If I remove "CMD0.CommandTimeout = 50;" I get error from log above.

So? Will you build latest sources and recheck? Or am I missing something?

You appearing to hit a server bug, I know it is not any of the following:

- 5.1.28
- 5.0.67

Please inform of Server you use so I can log split bug.  Please retry repo with a Servers
from above.

Jared, what are you talking about? General query log from server (5.0.68 btw) is perfectly
clear and expected.

Don't have x.x.68 on hand to view the results you are getting first hand.  I don't why a
'query lock' is clear and expected behaviour.  I think sister bug report needs to be
generated at this point with x.x.68 server.  The fact that you can not repeat this bug on
latest sources only suspends this issue.

Jared, of course it only suspends the report. Now, please take TortoiseSVN, or any other
SVN client you might prefer, and check out c/NET repository at:
  http://svn.mysql.com/svnpublic/connector-net

Latest version (5.3) should be in trunk. Other versions are in appropriate places. You
might need to comment out signing of assembly in AssemblyInfo file. Please build new
driver and retest.

Killing of query from separate thread is what's to be expected as original I__S query
takes too long time to complete, see my test case again.

I do not see a server bug here, so far, nor there is anything in what you wrote to
convince me otherwise. My opinion is that there is something in your code producing
reported error. Does my test case fail the same way yours does on your machine (toggle
command timeout)? Does new c/NET version 5.2 from SVN repo fail the same way?

Jared,

I have received the same results that Tonci has. I_S has some performance problems.
However, it is not considered as a bug. It is just a known issue that I believe will be
resolved (though not right now).

Your application has either to catch the timeout exception or increase the command
timeout. If you want to get the results of your SELECT query I would suggest bigger
command timeout, which has already been advised by Tonci.

If something is not working fast as you want it does not always mean the bug in the
server or connector driver. The current case is exactly such sort of situations.

Duplicate of http://bugs.mysql.com/bug.php?id=37239

Can verify fix in 5.3 (which is not released!)

Duplicate of bug #37239.

Direct SVN link
http://svn.mysql.com/svnpublic/connector-net/trunk/MySql.Data