Not your usual MySQL4 + ODBC connection issue...

Question

I've got 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 [email protected] on inContact

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

Answer 1

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

Answer 2

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 :)