[unixODBC-support] trouble with mysql driver on mono

Martin J. Evans martin.evans at easysoft.com
Fri May 14 19:46:21 BST 2010


James Smith wrote:
> Nick, Martin,
> 
>  
> 
> thanks for the replies.

You might find
http://www.easysoft.com/developer/interfaces/odbc/linux.html useful.

Also parts of
http://www.easysoft.com/products/data_access/odbc_odbc_bridge/manual/connection.html

are relevant.

>  
> 
> Nick, apologies for not looking at the log files more carefully. I'm
> glad it's not a sockets issue at least. I did as you said and increased
> the lengths of the log lines, getting some strange results. Notice that
> I've changed the username to 'soot' for reasons which will become apparent:
> 
>  
> 
> ---
> 
>  
> 
> Trying with a DSN but including the username and password in the
> connecting string as the Mono documentation advises:
> 
>  
> 
m> OdbcConnection handle = new OdbcConnection(
> "DSN=MySQL;UID=soot;PWD=########;" );
> 
>  
> 
> Access denied for user 's'@'localhost' (using password: YES)

Does not look right as you say. However, it does tell is that the MySQL
ODBC driver saw something of the UID and PWD and decided to connect to a
MySQL Server on the local machine.

This illustrates the complexity of an ODBC connection. I'll explain what
happens as it might help you find where it is going wrong. What you need
to know is what ODBC API "OdbcConnection" is using and what it passes
down as there is SQLConnect and SQLDriverConnect. Now, since you
supplied one string containing the usual ODBC attributes DSN/UID/PWD I'd
suggest this means OdbcConnection is calling SQLDriverConnect (the
unixODBC log file would verify this but IIRC, you are not getting one).

Run odbcinst -j to find out where the system odbc.ini and odbcinst.ini
file is located. e.g.,:

$ odbcinst -j
unixODBC 2.2.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /home/martin/.odbc.ini

Check the DRIVERS and SYSTEM DATA SOURCES files are readable by everyone
and add the following to the DRIVERS file:

[ODBC]
Trace           = no
TraceFile               = /tmp/unixodbc.log

Create /tmp/unixodbc.log and chmod it to 777:

chmod 777 /tmp/unixodbc.log

Find the mono binary and run ldd on it to ensure it is using the
unixODBC you installed.

Now we should get log output which will help and you can verify
OdbcConnect has at least passed the whole connection string to unixODBC.

The ODBC Driver Manager parses that string, finds the DSN, looks it up
in the odbc.ini file, then finds the driver and looks it up in the
odbcinst.ini file, loads the driver and finally calls SQLAllocHandle(for
environment), SQLSetConnectAttr(to set ODBC version), SQLAllocHandle(for
connection) and finally SQLDriverConnect. In this case the string you
supplied is passed to SQLDriverConnect.

The ODBC Driver is supposed to parse the connection string to obtain
attributes and split them out so one would hope the MySQL driver at
least finishes this stage with:

DSN=MySQL
UID=soot
PWD=########

Then, depending on other arguments passed to SQLDriverConnect (e.g.,
SQL_DRIVER_COMPLETE, SQL_DRIVER_NOPROMPT etc), the ODBC Driver can take
the DSN and use it to look up other attributes it needs to connect i.e.,
if SQL_DRIVER_COMPLETE is used the driver can "complete" the attributes
it needs by looking at the "MySQL" DSN in the odbc.ini file to find say
the hostname etc. It does this if it links against libodbcinst library
via the SQLPrivateProfileString API and I've no idea if MySQL does this.
If you straced it and it did not look in the odbc.ini file then I guess
it does not.

If the MySQL driver is not linked with libodbcinst library you will have
to pass ALL the attributes MySQL needs to connect in the connection
string or the MySQL driver might guess/default e.g., you error message says:

Access denied for user 's'@'localhost' (using password: YES)

Is your MySQL database on the local machine? If not you will need to add
whatever attribute MySQL needs to define the host.

>  
> 
> So the first letter of the username is picked out only.

True and looks wrong but it does suggest the MySQL driver understands
something of UID=soot as your later examples end up as root.

>  
> 
> ---
> 
>  
> 
> Trying with a DSN without the username and password in the connection
> string:
> 
>  
> 
> OdbcConnection handle = new OdbcConnection( "DSN=MySQL" );
> 
>  
> 
> Access denied for user 'root'@'localhost' (using password: NO)

I'd guess this is because MySQL defaults to this or you are running mono
as root and it does not read the odbc.ini file.


>  
> 
> It fails. I've changed the username in the 'odbc.ini' file to 'soot' as
> well to make the point that it doesn't appear to be being read at all.
> Something decides that it's best just to default to 'root'.

There is also a mysql.ini (or something like it) file which the MySQL
driver might read. I'd check the documentation on the MySQL ODBC Driver
to see what files it looks in.

>  
> 
> ---
> 
>  
> 
> Trying without a DSN and using a full connection string:
> 
>  
> 
> OdbcConnection handle = new OdbcConnection(     "DRIVER={MySQL ODBC 3.51
> Driver};" +
> 
>                                                 "SERVER=localhost;" +
> 
>                                                
> "DATABASE=jaspermusicstore-shop;" +
> 
>                                                 "USERNAME=soot;" +
> 
>                                                 "PASSWORD=########;" +
> 
>                                                 "OPTION=3"  );

Are you thinking MySQL knows the attributes USERNAME and PASSWORD - you
need to check this as the usual ODBC ones are UID/PWD.


>  
> 
> Access denied for user 'root'@'localhost' (using password: NO)

So neither the USERNAME or PASSWORD fields were picked up by the MySQL
driver which kind of proves it does not know these attributes but from
above case it does know UID/PWD.

>  
> 
> Predictably, if fails. Again the username is incorrect and no password
> appears to be being used.
> 
>  
> 
> ---
> 
>  
> 
> I've tried swapping USERNAME/PASSWORD for UID/PWD in all the applicable
> cases above but to no avail.
> 
>  
> 
> Sorry, I know this is probably getting very tedious.
> 
>  
> 
> My impression is that the Mono ODBC implementation is buggy, neither
> locating the 'odbc.ini' file nor parsing connecting strings properly
> and, given the lack of feedback on the Mono mailing lists, I should try
> to connect via something other than ODBC. The fact that iSQL works, not
> to mention your kind responses, suggests the problem doesn't lie with
> unixODBC.

I am confident the problem does not lie with unixODBC. I see no reason
why mono should read any odbc.ini files but there is an argument the
MySQL ODBC driver should and maybe does not. I think the problem lies
with the MySQL driver OR mono calls SQLDriverConnect without the
SQL_DRIVER_PROMPT argument (asking the driver to complete the DSN
attributes not provided).

You best bet is to pass all attributes the MySQL ODBC Driver needs in
the connection string. However, since UID=soot became "s" I'm not sure
you are going to get much further.

isql works differently from some newer ODBC applications (unless Nick
has changed it) in that it calls/called SQLConnect. SQLConnect takes a
ServerName (read DSN), username and password.

>  
> 
> Any thoughts, as always, would be greatly appreciated.
> 

That's all the thoughts I have bar installing mono and MySQL myself and
I've no inclination or time to do either.

> 
> Kind regards,
> 
>  
> 
> James
> 

Martin


More information about the unixODBC-support mailing list