Problems with Mono MySQL 4 and up connectivity

Post here for help with installing or upgrading mojoPortal pre-compiled release packages. When posting in this forum, please provide all relevant details. You may also want to review the installation or upgrading documentation.

If you have questions about using the source code or working with mojoPortal in Visual Studio, please post in the Developer forum.

Post here for help with installation of mojoPortal pre-compiled release packages

When posting in this forum, please try to provide as many relevant details as possible. Particularly the following:

  • What operating system were you running when the bug appeared?
  • What database platform is your site using?
  • What version of mojoPortal are you running?
  • What version of .NET do you use?
  • What steps are necessary to reproduce the issue? Compare expected results vs actual results.

You may also want to review the installation or upgrading documentation.

If you have questions about using the source code or working with mojoPortal in Visual Studio, please post in the Developer forum.

This thread is closed to new posts. You must sign in to post in the forums.
1/1/2005 3:06:07 PM
Gravatar
Total Posts 20

Re: Problems with Mono MySQL 4 and up connectivity

I am starting a thread to document a problem I found getting Mono to connect to MySQL above 4.0 databases, that affects the ability to install MojoPortal.

I installed on Debian Linux running XSP off Mono 1.0.0 with Mysql 4.0

When I originally installed MojoPortal, I got the following error:

ByteFX.Data.MySqlClient.MySqlException: Unable to connect to any of the specified MySQL hosts

in <0x00158> ByteFX.Data.MySqlClient.Driver:Open (ByteFX.Data.MySqlClient.MySqlConnectionString)
in <0x0003c> ByteFX.Data.MySqlClient.MySqlInternalConnection:Open ()
in <0x000ef> ByteFX.Data.MySqlClient.MySqlPool:CreateNewPooledConnection ()
in <0x00214> ByteFX.Data.MySqlClient.MySqlPool:GetPooledConnection ()
in <0x00052> ByteFX.Data.MySqlClient.MySqlPool:GetConnection ()
in <0x00112> ByteFX.Data.MySqlClient.MySqlPoolManager:GetConnection (ByteFX.Data.MySqlClient.MySqlConnectionString)
in <0x0007c> ByteFX.Data.MySqlClient.MySqlConnection:Open ()

I confirmed that the database was indeed located on localhost, and I was able to reproduce the problem on the remote server with the following small C# Program:

using System;
using ByteFX.Data.MySqlClient;
//using MySql.Data.MySqlClient;
namespace hello
{
??? class MainClass
??? {
??? ??? public static void Main(string[] args)
??? ??? {
??? ??? ??? Console.WriteLine("Hello World!");
??? ??? ??? MySqlConnection conn = new
MySqlConnection("Server=localhost;Database=luke_my;User ID=luke_my;Password=foo;");

??? ??? ??? MySqlCommand cmd = new MySqlCommand("SELECT * FROM foo;",conn);
??? ??? ??? cmd.Connection.Open( );

??? ??? ??? cmd.ExecuteNonQuery( );

??? ??? ??? cmd.Connection.Close( );
??? ??? ??? Console.WriteLine("Done");
??? ??? }
??? }
}

When compiled, this produced the exact same error.
On trying the same thing on my local computer running windows with MySql 4.17, It gave me an error with authentication, saying that ByteFX is an outdated client and can't support MySQL's new encrypted log ins. I did a little homework, and found that ByteFX has been replaced with this:
http://dev.mysql.com/downloads/connector/net/1.0.html
For the newer MySQL releases.
Which is the current connector, made by the makers of ByteFX.
It seems to have all the same functions as the old ByteFX ones, so very little needs to be changed.I tried it out on WinXP under .NET SDK 1.1, and it worked! I then tried it on Mono 1.0.1 on my winXP box, and Mono 1.0 on the Linux server, and got a new error on each one,(same for each)? which says something to the effect of MySQL returned Unknown encoding, 'latin1' .
This it turns out is due to a bug in Mono in versions less than 1.0.4 (I think) I upgraded to Mono 1.0.5 from 1.0.1.
It worked!
I have not told my sysadmin to upgrade yet, so the final test? is not yet complete. But based on current evidence, this looks like the cause of the original problem: ByteFX can't work with the encrypted password system, and the newer connector is needed. And in order to use the newer connection on Mono without bugs, a version of Mono above 1.0.4 (I think, I can confirm that 1.0.5 works )is needed.

Anyway, I just wanted to run all this by the author, wonder what you think.
1/1/2005 3:15:35 PM
Gravatar
Total Posts 20

Re: Problems with Mono MySQL 4 and up connectivity

btw, the bug in question with Mono is here:
http://bugzilla.ximian.com/show_bug.cgi?id=68930
1/1/2005 7:04:17 PM
Gravatar
Total Posts 20

Re: Problems with Mono MySQL 4 and up connectivity

Well, My admin made the change and it DID NOT WORK. I am still facing the same error message that I started with,  completly baffled.

Any ideas are welcome at this point. I shell into the domain name of my site.
1/2/2005 1:25:48 AM
Gravatar
Total Posts 18444

Re: Problems with Mono MySQL 4 and up connectivity

My test machine is Fedora Core 2 with mono 1.0 and MySQL 4.0.2 and I have never had this problem. I also have not seen this problem on monoForge which currently uses mono 1.1.3 but previously had mono 1.04 and also uses MySQL 4.0.2. I can't speak for other versions of mono or MySQL.

I continue to use the ByteFx instead of the Connector for .NET because Connector is not released as part of mono yet and would require a separate install and I have not had any trouble with ByteFx. The bug you list is a bug with Connector for .NET not with the original ByteFx.  I will eventually upgrade to the new provider once it ships with mono, but even then it would break compatibility with older versions of mono unless the user can install the provider on top of the older mono version.

MySQL has very granular permission allowing users to connect only from hosts they have permission to connect from. I suspect that this is where the issue lies. To grant permission to a user so they can connect from any host you would use something like this from the MySQL command line

GRANT ALL PRIVILEGES ON dbName.* TO 'username'@'%' IDENTIFIED BY 'userpassword' WITH GRANT OPTION;

Note that the '%' indicates the user can connect from any host.
See the MySQL documentation for more detail.

I would also experiment by putting the server name instead of localhost and see if that has any effect.
1/2/2005 4:58:09 PM
Gravatar
Total Posts 20

Its fixed!

FINALLY got it fixed!!!!!

What it turned out was that the mysql on my site was not set up to accept TCP-IP connections.  I finally reproduced the problem on my local box by reinstalling to reject TCP-IP, and got my host to set it up. That fixed my connection problems.

Yeah, my guess was that was happening that MySQL was silently failing authentication, which it did verbosely on WinXP. Turns out that I was making the problem more complex than it had to be.
1/2/2005 5:03:42 PM
Gravatar
Total Posts 20

Re: Problems with Mono MySQL 4 and up connectivity

SWEETNESS!

My mojoportal is up an running at last!!! Never let them say perseverance doesn't pay!
1/3/2005 12:38:25 AM
Gravatar
Total Posts 18444

Re: Problems with Mono MySQL 4 and up connectivity

Cool! are you using the version I released yesterday? If your site is public, let me know and I'll put a link on my showcase page.

Joe
1/3/2005 2:24:29 PM
Gravatar
Total Posts 20

Re: Problems with Mono MySQL 4 and up connectivity

No, but I will download it. Right now my host is setting up Apache-Mono integration. I get the feeling that I am the first person on this host to actually do anything with with ASP, which is fine beause their good at setting things up when I ask. However when that's complete and everything is custumized and set up I will send you a link.
1/3/2005 2:34:52 PM
Gravatar
Total Posts 20

Re: Problems with Mono MySQL 4 and up connectivity

Oh, one thing I thought I might mention...Mojoportal seems to ignore port numbers when it sets up its URLS. Mine right now is set up at:
http://www.sittingfrog.com:8025/mojoportal/
But the images and such all link to
http://www.sittingfrog.com/mojoportal/
This shouldn't matter when I get Apache and mono integrated properly, but I thought I'd mention it.
7/26/2005 6:52:08 AM
Gravatar
Total Posts 3

Re: Problems with Mono MySQL 4 and up connectivity

Hi, I am having the same problem and havent been able to fix it. I have mono 1.1.8 and mysql 4.0.23 on a linux box (ubuntu hoary). I made a little asp page that *should* let it connect but it will not. I have pleny of users with and without passwords, on localhost, on the comp name, on %, etc. but I always get the "Unable to connect to any of the specified MySQL hosts" error. The asp page runs on localhost so it should work jst fine, but it does not. All my php junk (my admin, phpbb, etc) connects to the database fine so I suppose mysql is working.

My guess is that byteFX is to blame. Luke, how did you install the MySql.Data.dll on the linux box? Did you have to recompile it with mono? Or did you just dump the dll (compiled in win) into the mono lib dir and add it to the gac?

I did try the table editor sample that came with mysql.data but I couldnt connect to any of the users@%. le sigh... This uber frusturating spending hours upon hours just trying to get the stupid thing to connect.

heres the code if you're interested:

            try
            {
                string connectionString =
                    "Server="+TextSvr.Text.Trim()+";" +
                    "Database="+TextDb.Text.Trim()+";" +
                    "User ID="+TextUid.Text.Trim()+";" +
                    "Password="+TextPw.Text.Trim()+";";

                if(TextDb.Text.Trim() == "")
                {
                    connectionString =
                        "Server="+TextSvr.Text.Trim()+";" +
                        "User ID="+TextUid.Text.Trim()+";" +
                        "Password="+TextPw.Text.Trim()+";";
                }

                IDbConnection dbcon;
                dbcon = new MySqlConnection(connectionString);
                dbcon.Open();

                this.Label1.Text = "it works!";

                dbcon.Close();
            }
            catch(Exception ex)
            {
                this.Label1.Text = ex.Message+ " poooooooooo";
            }

Ben
7/26/2005 1:25:54 PM
Gravatar
Total Posts 18444

Re: Problems with Mono MySQL 4 and up connectivity

Are you having this problem with mojoportal or with your own app? I'm guessing your own app since mojoportal no longer uses the ByteFX driver.

It could be your problem would go away with the newer Connector for .NET available on the MySQL site but I know I used ByteFX with MySQL 4.0.23 before myself and it worked for me.

You are using the same user in the connection string as for the phpmysql and all of it is running on the same server localhost?

In answer to the question about compiling. I compiled ByteFX and also the newer connector on windows and used it on linux no problem. When you compile in VS.NET you have to use Rebuild instead of Build so that it doesn't do an incremental build.
I've never tried installing it in the GAC on linux, but  for a compiled web app just drop the dll in the bin folder with everything else and it will use it.

It really sounds like it has to be one of a few possible things
 - MySQL is not configured to accept tcp/ip connections
-  The user does not have permission to connect from the host that the web is running on to the host that the db is running on
-  The web server is able to resolve the name of the db server
- a bad connection string, your looks ok but mine is slightly different so it may be worth an experiment

example:
Data Source=localhost;Database=mojo;User ID=mojo;Password=mojo

It could possibly be a bug in mono 1.1.8 but that is not my first suspect

Are you developing on linux or windows?

Are you compiling your web code to a dll or running it inline in scripts tags

The fact that the table editor couldn't connect supports a permission issue as a possibility

You might also post at www.gotmono.com forums
You must sign in to post in the forums. This thread is closed to new posts.