MySqlConnection open object reference not set to an instance of an object

[ic2]

This sounds simple but I've been working on this for hours without a result yet.


From both C# and X# web programs we open MySQL databases residing on the same server as from where the webprogram runs. That works. Now we want to approach the MySQL database from a local Pc or server. That requires the server to be defined as an IP address and a port to be added, documented in the hosters "How to connect to a MySQL database externally" documentation.



We have the following X# program for that:

Method RunSQLCommand(cCommand As String , cConnect As String) As String
Local oSQLconn As MySqlConnection
Local cReturn As String
Local oSQLcmd As MySqlCommand
// e.g. cConnect = "server=123.456.78.9;database=somedb;username=somenname;password=somepw;port=9876"
// cCommand is the query to execute


oSQLconn := MySqlConnection{cConnect}
oSQLcmd:=MySqlCommand{cCommand,oSQLconn}
oSQLconn:Open()

I just show the first 3 lines, because I can see that the instantiation of MySqlConnection has worked, and in the 2nd line oSQLcmd looked good as well; when inspecting the variables within oSQLcmd I can see the connection details, I can see the query, all looks well. But as soon as I run the 3rd line, oSQLconn:Open() , I get an exception object reference not set to an instance of an object . Despite that oSQLconn has been instantiated 2 lines earlier!

Somewhere I read that this error could occur when the connection string is incorrect. In the meantime I checked/tried the following:

1 Tried it in C#. Same error
2 Entered the exact same connection data in MySQLWorkbench. It connected without problems so that would mean that I can indeed approach the database externally with the credentials I use. So why doesn't it work from my program??
3 Added pooling=false;connection reset=false to the connection string. Same error.
4 Removed the database from the connection string and changed the query to UPDATE MyDatabase.Tablename set ... where ... Same error
5 Used the MySqlConnectionStringBuilder (see C# code below). It resulted in the same connection string I already passed, so same error.
6 Replaced the keywords username and passwords with Uid and Pwd. Same resulting connectionstring (as I can see in the debugger) but same error.
7 Tried the connection string we use (working) in the web programs. Then the Open command does not directly give the "object reference" error but "Unable to connect to any of the specified MySQL hosts."

I am currently out of options. The exception details does not learn me anything, mostly null values.

Has anybody approached MySQL databases externally? If so, what should I do differently? Is there any explanation for this error? What else can I try to get this to work?

Dick

C# to construct the connection string

oConStr.Server = "123.456.78.9;
oConStr.UserID = "someuser";
oConStr.Password = "somepw";
oConStr.Database = "somedb";
oConStr.Port = 9876;

cConnect = oConStr.ToString();

[FFF]

Dick,
i have no clue of MySQL, but there should be some config on the server. Could it be that only "Localhost" ist accepted there? If MySQLWorkbench sits on you local pc, that would foul my reasoning - or there might be whitelist in the config, where MySQLWorkbench is allowed..
Just fishing...

[robert]

Dick,

Are you maybe missing a DLL on your machine that MySql needs to connect from its Ado.Net provider ?

And I did find this: https://bugs.mysql.com/bug.php?id=91136
I am not sure if this is related ?

Robert

[ic2]

Hello Robert, Karl,

Thanks for your replies! For Karl's suggestion I checked with the hoster if they can tell anything. About the second: I only have:
#Using MySql.Data.MySqlClient

and included a reference to MySql.Data.dll (8.0.19.0) by browsing to it. That's what I found in most links as required.
I do not know much about Ado.Net, are you referring to this:

https://dev.mysql.com/downloads/connector/net/

If so, I will try that and otherwise see if the bug report could give a clue.

Dick

[Jamal]

Dick,

The connection string should be something like this:

Server=myServerAddress;Port=1234;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

See: https://www.connectionstrings.com/mysql/

Also see: https://dev.mysql.com/doc/connector-net ... tring.html

If you are interested, you may email me privately with a test database connection string, and I'll try on my end.

HTH,
Jamal

[lumberjack]

Hi Dick,

From both C# and X# web programs we open MySQL databases residing on the same server as from where the webprogram runs

Not familiar with MySQL, but from a PostgreSQL experience this looks like your MySQL is probably not setup to allow connections from other sources than LOCALHOST.

In PG you need to specifically set in the .conf file the IPv4/6 addresses allowed to connect.

HTH,

[TimothyS]

Try setting up an ODBC source and see if that works. I use MySql remotely quite often from ODBC and ADO.net and it works fine.

[TerryB1]

Dick

I have no direct experience of your scenario, but "object reference not set to an instance of an object" is a catch all error message and the object not being set may well not be the object you think it is referring to.

The problem then is how to track down the trouble.

You dismiss the idea that in your program oSQLconn could not be a problem since you instatiated two lines before use. This reasoning may be wrong. The fact is that when your program runs it takes time do things, in this case instantiate oSQLcon. Nothing necessarily to do with the order of your code.

Your description of what is happening suggests to me a timing problem.

So I suggest that you include something like a MessageBox in your code before trying to use the connection. This will stop things for a while - hopefully long enough for the connection to be established. If this works you can the introduce some "waiting" code for use in practice.

Just a thought

Terry

[ic2]

First, I want to thank everybody for the input. @Timothy: we do have some working ODBC connections but these require setup on the receiving side and the whole reason we started this "direct connection", via X#, was that we didn't want to rely on VO & ODBC for MySQL tables. @Johan, the MySQL database does accept external connections, as MySQL Workbench could reach it as well.

I was about to accept Jamal's kind offer to check my connection string in his program but in the meantime it works, and I think Terry was somehow on the right track. These are the lines of code we use eventually, in the simplest form:

oSQLconn := MySqlConnection{cConnect}
oSQLconn:Open()

oSQLcmd:=oSQLconn:CreateCommand()
oSQLcmd:CommandText:=cCommand
oSQLcmd:ExecuteNonQuery()


Well, this works! cConnect is the full connection string and cCommand the query. However, in an earlier version it did not work (not sure why not) so it makes sense to use the VS debugger, right? And this is what happens when I step through the second line of that code:

NullReferenceException.jpg (37.99 KiB) Viewed 759 times

While it (now) all works without issues when running directly it gives this stupid error when using the VS debugger. I would say it's the opposite of a timing problem Terry suggests but your approach Terry seems to makes sense.

What I am wondering: I've taken almost every opportunity to write how much I hate Visual Studio. I think when using VS 3/4 of my programming time is trying solve these kind of (non) issues. Could anybody tell me if I am doing something wrong to get hours consuming VS problems like this? Or is this something everyone considers normal in VS and am I the only one thinking that an exception like this does not make sense?

I will work further with this code, without debugger, and hopefully it keeps working....

Dick

[robert]

Dick,
I suspect this is an exception inside the Open() code that is actually handled inside that code. So you can probably press Continue inside VS and see that the app continues without problems.

VS allows you to break on all exceptions, even the ones that are handled.
Maybe the error will disappear if you enable the 'Just my code' option in the debugger options ?

Robert