MySQL Bugs: #27958: Cannot use Data Source Configuration Wizard on large databases

Bug #27958	Cannot use Data Source Configuration Wizard on large databases
Submitted:	19 Apr 2007 20:33	Modified:	13 Nov 2007 12:46
Reporter:	Don Lopez
Status:	Closed
Category:	Connector/Net	Severity:	S2 (Serious)
Version:	5.1.0	OS:	Any
Assigned to:		Target Version:

Description:
Connection times out while trying to retrieve table listing from server if the server has
a large number of tables.  This is related to the known issue:

http://bugs.mysql.com/bug.php?id=19588

Attempts to fix this by adjusting the Connect Timeout property do not work.  The
connection times out at 30 seconds no matter what this property is set to.

How to repeat:
1)  Create a MySQL database.
2)  Add tables until the following command takes longer than 30 seconds to run:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE != 'VIEW';

3)  Start a new visual studio project
4)  Create a windows form.
5)  Under Data menu, choose Add New Datasource...
6)  Highlight Database & click Next
7)  Setup the connection & click Next  (you can click Advanced and choose any value for
Connect Timeout)
8)  Save the connection string & click Next
9)  Connection will time out

Suggested fix:
Have plugin use Connect Timeout property of data connection as the timeout setting for
the connection which retrieves the table information.

Moving this to the Connector/Net category since we are no longer going to accept bug
reports into this category.

Hi Don and thanks for your report. Alas, you can not adjust Command timeout in wizard. You
can only adjust Connection timeout.

Now, how many tables is enough? I can't repeat with:
C:\mysql507\bin>mysql -uroot information_schema
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.38-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*) from tables;
+----------+
| count(*) |
+----------+
|     1913 |
+----------+
1 row in set (1 min 38.72 sec)

mysql> select count(*) from columns;
+----------+
| count(*) |
+----------+
|    18320 |
+----------+
1 row in set, 1 warning (56.81 sec)

That DB should have caused the timeout if the queries to information_schema where taking
longer than 30 seconds.  I was using VS 2005.

So what now Don? I guess I'll give it a one more try before closing as "Can't repeat" ...

I to use VS2005 Pro but with SP1 installed.

I have finally been able to repeat this bug like described with test case attached. Thanks
Don.

Test case to generate hugh number of tables

Attachment: Bug27958.vbs (application/octet-stream, text), 2.00 KiB.

... and the result:

Attachment: 27958.jpg (image/jpeg, text), 13.85 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37354

Fixed in 5.1.4.  I added a connection string option named "default command timeout".  You
can set this value to a number of seconds you want the commands to use unless the
physical command timeout property on the command object is set.  If so, that takes over.

A note has been added to the 5.1.4 changelog: 

When accessing certain statements, the command would timeout
        before the command completed. Because this cannot always be
        controlled through the individual command timeout options, a
        default command timeout has been added to the
        connection string options.

Added the 'default command timeout' to list of supported connection string options.