MySQL Bugs: #46930: Database name embedded in CommandText

Bug #46930	Database name embedded in CommandText
Submitted:	26 Aug 2009 7:37	Modified:	26 Aug 2009 10:52
Reporter:	The Assimilator	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.0.4	OS:	Windows (XP Pro SP2)
Assigned to:		CPU Architecture:	Any
Tags:	.net, commandtext, dataset, designer, TableAdapter

Description:
I have created an ASP.NET web application with a DataSet. During development I used a test server/database, and now I need to deploy the application using a different server/DB. I thought that I could simply update the connecting string in the web.config file when deploying, but after I did that and attempted to run the live webapp, I got the following exception:

Procedure or function '`my_stored_proc`' cannot be found in database '`test_db`'.

After debugging the code, I determined that the MySQL Visual Studio integration was to blame, because the wizard was setting the CommandText on the DataSet to contain both the database and stored procedure name (see attached screenshot "vs-designer.png"). Manually removing the database name causes the exception shown by "commandtext-error.png", and if "Yes" is chosen in that dialog then the DataSet breaks completely (loses all procedure parameters etc.).

The only way to get this to work correctly is to go into the generated DataSet Designer.cs file and edit the CommandText to remove the database name, then saving that file and compiling/deploying the project.

In summary, any attempt to circumvent this issue using the Visual Studio Designer results in a broken DataSet, which is why I am flagging this bug as Critical. Under the circumstances I have outlined, it makes the Designer almost completely unusable.

Using Visual Studio 2008 with SP1.

How to repeat:
1. Compile a simple SELECT stored procedure on a MySQL database, e.g. "select * from mysql.user".
2. Create a new ASP.Net Web Application in Visual Studio. Add a DataSet and use the Visual Studio wizard to add a DataTable based on the stored procedure create in (1).
3. Configure the webapp so that the stored procedure is invoked when the main page is viewed.
4. Test the webapp to verify that it works.
5. Compile the stored procedure from (1) on a *different database* on a different server.
6. Copy the webapp to a different server. Change the connection string in its web.config to point to the database that you used in step (5).
7. Attempt to run the webapp again and watch it crash.

Suggested fix:
Don't encode the database name into the CommandText; always read it from the connection string specified by the user.

Fix your designer so that it doesn't cause Visual Studio to throw NullReferenceExceptions.

Incorrect CommandText

Attachment: commandtext-error.PNG (image/png, text), 11.93 KiB.

VS Designer NullReferenceException

Attachment: vs-designer.PNG (image/png, text), 9.14 KiB.

Apologies, descriptions for uploads are switched, please refer to bug report for correct context.

Hi "Assimilator" and thanks for your report.

I believe it to be a duplicate of Bug#33870 but will not change status till we agree on it. Can you please check it and tell me what you think.

Yes, the majority of this bug is a duplicate of 33870 - I guess my searching skills aren't as good as I thought. :p

However, the issue with the NullReferenceException in the Designer is a separate one; would you like me to open a new bug containing only that so you can close this bug as a Duplicate?

Also I'm a bit disappointed to see that the issue with databasename.procname was reported and verified as "Serious" well over 18 months ago, yet is still not fixed. Is someone actively investigating this? If not, would you mind if I have a look at it and perhaps submit a patch?

> Yes, the majority of this bug is a duplicate of 33870 - I guess my searching skills aren't as good as I thought. :p

Happens to everyone.

> However, the issue with the NullReferenceException in the Designer is a separate one; would you like me to open a new bug containing only that so you can close this bug as a Duplicate?

I think that issue is also addressed in one of the reports. Can't remember which one right now though.

> Also I'm a bit disappointed to see that the issue with databasename.procname was reported and verified as "Serious" well over 18 months ago, yet is still not fixed. Is someone actively investigating this? If not, would you mind if I have a look at it and perhaps submit a patch?

Well, there's always timeline for everything we do. In any case I assigned it to Reggie to get his attention.
As for patches, we are always happy to receive one, but please do it in diff format.

Closing the report as duplicate of Bug#33870, for now.