Bug #74116 Connector fails to use mysql.proc to enumerate stored procedures

• Submitted: 27 Sep 2014 11:53
• Modified: 29 Jun 2016 20:24
• Reporter: Trevor Jobling (OCA)
• Status: Closed
• Category: Connector / NET
• Severity: S3 (Non-critical)
• Version: 6.9.4
• OS: Windows
• Assigned to: Gabriela Martinez Sanchez
• CPU Architecture: Any
• Tags: performance, stored procedure

[27 Sep 2014 11:53] Trevor Jobling

Description:
Connector/NET does not attempt to enumerate stored procedures via mysql.proc(). Instead it looks up stored procedures in INFORMATION_SCHEMA.ROUTINES. This is slow and can lead to serious performance degradation in certain scenarios.

Be design Connector should attempt to use mysql.proc, and then fallback to I_S if permission is denied. See http://lists.mysql.com/commits/122765 for reference. This doesn't happen - Connector never even attempts the mysql.proc call.

The behaviour depends on the property HasRootAccess in MySqlConnectionStringBuilder. This is correctly initialised to true in the constructor, but this value is lost on Clear(). I suggest resetting it to true in Clear().

Observed in 6.8.3 and 6.9.4. 

How to repeat:
Just use Connector/NET to call stored procedures and log the SQL issued. One expects to see 

SELECT * FROM mysql.proc WHERE 1=1 AND db LIKE 'SuSi' AND name LIKE 'spAuthenticateUser';

but instead see

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE 'SuSi' AND ROUTINE_NAME LIKE 'spAuthenticateUser';

Suggested fix:
diff -r bbe50d62c68e -r be095df8023a mysql-connector-net-6.9.4-src/Source/MySql.Data/MySqlConnectionStringBuilder.cs
--- a/mysql-connector-net-6.9.4-src/Source/MySql.Data/MySqlConnectionStringBuilder.cs	Sat Sep 27 12:14:23 2014 +0100
+++ b/mysql-connector-net-6.9.4-src/Source/MySql.Data/MySqlConnectionStringBuilder.cs	Sat Sep 27 12:35:25 2014 +0100
@@ -1004,6 +1004,7 @@
           else
             values[option.Keyword] = null;
       }
+      HasProcAccess = true;
     }
 
     internal void SetValue(string keyword, object value)

[14 Oct 2014 13:59] Trevor Jobling

Mercurial patch file, but it's a trivial single line change

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: MySQL Connector.Net bug 74116.patch (application/octet-stream, text), 793 bytes.

[27 Apr 2015 4:41] MySQL Verification Team

Hello Trevor Jobling,

Thank you for the report and contribution.

Thanks,
Umesh

[15 May 2015 5:43] Tushar Agarwal

I am using 6.9.6. 
In my connection string I have added 
Use Procedure Bodies=true;ProcedureCacheSize=2500 ,
given all permissions on mysql.proc to my user but it doesn't work. I_S is being queried.

Is there something that I am missing ?

[15 May 2015 7:55] Trevor Jobling

Hi Tushar,
FWIW adding ProcedureCacheSize=2000 didn't help me. 
Have you tried implementing the patch I suggested in my comment from September?

[16 May 2016 7:47] Chiranjeevi Battula

http://bugs.mysql.com/bug.php?id=81427 marked as duplicate of this one.

[29 Jun 2016 20:21] Philip Olson

Posted by developer:
 
Fixed as of the upcoming Connector/Net 6.8.8 release, and here's the changelog entry:

The connector did not attempt to enumerate stored procedures via
mysql.proc(). Instead it looked up stored procedures in
INFORMATION_SCHEMA.ROUTINES. This could have led to performance
degradation in certain scenarios.

Thank you for the bug report.