MySQL Bugs: #28212: Unable to link tables from MS Access

Bug #28212	Unable to link tables from MS Access
Submitted:	3 May 2007 7:22	Modified:	3 May 2007 13:58
Reporter:	Waöter Schütz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.00.11	OS:	Windows (XP SP2, all secuity fixes)
Assigned to:		CPU Architecture:	Any
Tags:	1142, statistics

Description:
I have a MySQL database hosted on a remote server. Previously, I could always connect to those databases using the 3.51 ODBC connector and linking the tables in Access. 

Due to a change by my ISP I was no longer able to access my database. I was recommended to use the new Connector/ODBC 5.0. After that I was able to connect to the database, but as soon as I tied to link to a table I got the following Message:

ODBC -- call failed. 

[MySQL][MyODBC 5.00.11][MySQL] 1142 SELECT command denied to user (myusername)@(myisp) for table 'STATISTICS' #1142

The dadabase is successfully attached: I can see all my tables, but linking seems impossible. So it's not a username/password problem.

The problem was described by 2 other independant users: http://forums.mysql.com/read.php?10,125559,125559#msg-125559

There seems no workaround using Connector/ODBC 5.0. In my case it turned out, that the original problem was not caused by a change in the autentication system of MySQL (as suspected by my ISP's supporter) but a change in the way I had to specify the domain: myDomain.ch instead of http://www.myDomain.ch. So I was able to reinstall and use Connector/ODBC 3.51.14

How to repeat:
See above.

Just in case it should matter: I used Access 2002 (10.6501.6825) SP3 for those tests.

Hi Walter and thanks for your report. Actually your ISP's administrator was right. connector/ODBC v5 makes extensive use of INFORMATION_SCHEMA database to retrieve necessary table(s) info so you need privileges over that database. What happens and why do you see tables listed:

070503 11:06:20	     47 Connect     root@localhost on test
		     47 Query       SET NAMES utf8
070503 11:06:21	     47 Query       SELECT database()
		     47 Query       SELECT database()
		     47 Query       SHOW TABLES FROM test LIKE '%' << Not using I__S
070503 11:06:24	     47 Query       SELECT database()
		     47 Query       SELECT database()
		     47 Query       SHOW TABLES FROM test LIKE '%' << Not using I__S
		     47 Query       SELECT * FROM `test`.`b22340` LIMIT 0
		     47 Query       SELECT database()
		     47 Query       SELECT * FROM   INFORMATION_SCHEMA.STATISTICS WHERE  TABLE_SCHEMA='test' AND TABLE_NAME='b22340'  << Using I__S for metadata and here's where you fail...
070503 11:06:31	     47 Query       SELECT `b22340`.`ID` FROM `b22340`

MyODBC 3.51.14 is bound to work as it doesn't use I__S database.

Hi Tonci

...so you need privileges over that database...
Please excuse my ignorance: is this something I can do by myself or something that has to be done by the ISP's administrator? I looked at my database and tables by means of phpMyAdmin and HeidiSQL but I could not find INFORMATION_SCHEMA.STATISTICS.

Generally: do you recommend the use of MyODBC 5.00.xxx or should I remain with MyODBC 3.51.14. I use this database productive, but it's operated just by myself as the developer of the MSAccess-Frontend.

Kind regards, Walter