Not your usual MySQL4 + ODBC connection issue

I've go开发者_Go百科t a problem with ODBC that has me stumped, and I can't find an answer anywhere that addresses my problem. Short version: I can't connect to my MySQL4 installation on Ubuntu Jaunty from my WinXP ODBC System DNS, even tho I can connect just fine using the command line client both on that particular machine and others.

Huge amount of details starts here!

I have a standard MySQL5 install on Ubuntu Jaunty 9.04. I manually installed MySQL4 from precompiled binaries in /usr/local/mysql4. Here's the startup command:

/usr/local/mysql4/bin/mysqld_safe --user=mysql4 --port=3307 --socket=/var/run/mysql4d/mysql4d.sock --basedir='/usr/share/mysql4/' --datadir='/var/lib/mysql4/' --log-error='/var/log/mysql4.err' --log='/var/log/mysql4.log' --language='/usr/local/mysql-standard-4.1.22-pc-linux-gnu-i686-glibc23/share/mysql/english' &

This seems to work quite well. I have no problems connecting using the mysql client from either localhost or from a remote machine over TCP/IP:

mysql -u inContact -h myhost.com --password='blah' --port=3307

On this same server, I have virtualbox running XP Pro. I can also connect no probs using mysql on the command line from it. However, using the same settings for the ODBC system DSN don't work. I get the message "Host 'myhost.com' is not allowed to connect to this MySQL server".

The only message I'm seeing about it is in mysql4.log:

091124 10:56:50 5 Connect inContact@myhost.com on inContact

Anyone have some insight? I'm totally at a loss here...

You need to give permissions to the user you want to connect with. Use the GRANT statement.

Should have mentioned that privs were GRANTed already, sorry!

The issue turned out to be corrupted mysql.user and mysql.db tables. Repairing the tables wasn't successful, so I tried dropping most records and then repairing the table (which worked) and then re-added the users and GRANTs. Worked after that :)