The query builder failed. Unable to connect to any of the specified MySQL hosts.

Don VonderBurg
Don VonderBurg used Ask the Experts™
on
I have a large Windows desktop project written in C# using Visual Studio 2013 Pro. There are 8 datasets that connect remotely to a MySQL database. This code has worked for years. The connection string in the project string settings will connect just fine. When I open a dataset in designer view and attempt to add a new table clicking the "Query builder..." button it produces the error "The query builder failed. Unable to connect to any of the specified MySQL hosts." (attached screen shot). If I attempt to enter a query manually at the end of the wizard there is an error "Object reference not set to an instance of an object" for a simple select statement.  

The only changes are MySql.Data updated from  6.7.4 to 6.10.4 via NuGet and .NET Framework went from 4 to 4.5.2 (to match the dependency of the new MySQL upgrade). I have checked the Machine.config file and the proper versions updated properly.

Another issue is after the upgrade I lost the designer.cs files to the datasets. I attempted to run the Custom Tool on each of the .xsd files for the datasets and get "the custom tool 'MSDataSetGenerator' failed. Failed to generate code. Object reference not set to an instance of an object." I think the two issues are related. I am pressed to get this fixed for release.

Error when custom tool is run.Successful test in string area.Error when attempting to add a table adapter.Manual entry of query.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Don VonderBurgPresident/CEO

Author

Commented:
Update: I can connect with MySQL Workbench to the remote MySQL server and perform all actions. So I know all my ports and connections are in tune.
Don VonderBurgPresident/CEO

Author

Commented:
Update: I Started a brand new project and used NuGet to get MySQL.Data. Same error when attempting to create a table adapter in a new dataset.
John TsioumprisSoftware & Systems Engineer

Commented:
I will take a wild guess....do you try to connect to a remote mySQL instance (not locally on your machine) and you are using user 'root'?
If so recently the 'root' has being denied of remote connectivity and it needs some trickery to bypass it....so try with another user that has all the necessary GRANT's and report back...
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Don VonderBurgPresident/CEO

Author

Commented:
John - Thanks for the input. I am not using 'root' in my connection string I have a different user added with the settings I need. The desktop application is still working in the field with our customers for the version they have installed. The issue is with Visual Studio 2013 solution/project. I needed to add another table adapter for a feature request to our software and my Nuget stated MySQL had updates so I performed them. I updated and now I cannot get any of the datasets that use MySQL to allow me to add another table adapter.
Don VonderBurgPresident/CEO

Author

Commented:
I have been reading this site https://dev.mysql.com/doc/visual-studio/en/visual-studio-install.html for more information. I have rolled back to MySQL For VisualStudio 1.2.7 and .NET Connector 6.9.10. I am still having the same issues. I think I need to roll back further now that I have figured out that NuGet can install picked version.
President/CEO
Commented:
Fixed! I will add solution here in case someone else is searching later.

1. I uninstalled all Nuget references to MySQL.
2. Uninstalled MySQL for Visual Studio and the .NET Connector from the OS.
3. Restarted computer.
4. Downloaded older version of MySQL for Visual Studio v1.1.4 and installed to OS.
5. Downloaded older .NET v6.8.8 and installed to OS.
6. In the NuGet command window used command "install-package MySql.Data -Version 6.8.8"
7. In server explorer of Visual Studio added connections to MySQL.
A T

Commented:
Thanks to Don VonderBurg above for the fix! I had the exact same issue. However, the problem is with MySQL Connector/Net 6.9.10 ONLY. You do not have to revert to older version of MySQL for Visual Studio. Once you roll back MySQL Connector/Net from 6.9.10 to 6.8.8 (immediate previous release), the problem went away. Cannot believe MySQL team released MySQL Connector/Net 6.9.10 with such a bug.
Don VonderBurgPresident/CEO

Author

Commented:
Found the answer.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial