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 18:33	Modified:	13 Nov 2007 11:46
Reporter:	Don Lopez	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.1.0	OS:	Any
Assigned to:		CPU Architecture:	Any

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.