Bug #13930 views via ODBC
Submitted: 11 Oct 2005 14:44 Modified: 8 Mar 2007 1:21
Reporter: Benoit LEFEVRE Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51.12 OS:Windows (Windows XP)
Assigned to: Bogdan Degtyariov CPU Architecture:Any

[11 Oct 2005 14:44] Benoit LEFEVRE
Description:
Using MySql 5.0.13 ODBC 3.51 and MS Office 2k3

I have 2 table 
CREATE TABLE `CRCI` (
  `identr` int(10) unsigned NOT NULL auto_increment,
  `APE` text NOT NULL,
  `RS` text NOT NULL,
  `siret` text NOT NULL,
  `adresse` text NOT NULL,
  `CP` int(10) unsigned NOT NULL default '0',
  `Ville` text NOT NULL,
  `Tel` text NOT NULL,
  `Fax` text NOT NULL,
  `www` text NOT NULL,
  `Email` text NOT NULL,
  `Groupe` text NOT NULL,
  `Nationalit` text NOT NULL,
  `Effectif` decimal(10,0) unsigned NOT NULL default '0',
  `Qualification` text NOT NULL,
  `Civil_res` text NOT NULL,
  `Nom_res` text NOT NULL,
  `Function_res` text NOT NULL,
  `juridique` text NOT NULL,
  `cedex` text NOT NULL,
  PRIMARY KEY  (`identr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

this table as about 13000 records

CREATE TABLE `CP` (
  `Ville` char(100) NOT NULL default '',
  `CP` int(5) unsigned NOT NULL default '0',
  `zone` int(10) unsigned default '0',
  PRIMARY KEY  (`CP`,`Ville`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 DELAY_KEY_WRITE=1;

and this one about 400 records

and one view
CREATE VIEW zone_ASud AS
SELECT CRCI.* from CRCI
LEFT JOIN CP on CP.cp=CRCI.cp and CP.Ville like concat(CRCI.Ville,"%")
WHERE CP.zone=3;

witch hit about 300 records

And all is ok from my linux box

How to repeat:
And now the funy part 

I've made a DSN for my database using options "Allow big result" and "Use Compressed protocol"

When I import data from zone_ASud into Excel via ODBC, I have 300 records and all is ok ! Idem from word!

When I import (or link) same datas (zone_ASud) into Access via the same ODBC connexion, I have 14000 results ie all rows from CRCI table and no error!
[11 Oct 2005 15:55] MySQL Verification Team
Could you please provide the dump of the tables ?
You can upload it at:

ftp://ftp.mysql.com:/pub/mysql/upload

Please use a filename like bug13930.zip

Thanks in advance.
[14 Oct 2005 18:02] MySQL Verification Team
Sorry I was unable for to open the file uploaded, it is corrupted.
Could you please try to upload again? Thanks.
[15 Oct 2005 13:15] Benoit LEFEVRE
dump of tables

Attachment: bug13930.sql.bz2.aa (audio/audible, text), 200.00 KiB.

[15 Oct 2005 13:15] Benoit LEFEVRE
part 2

Attachment: bug13930.sql.bz2.ab (application/octet-stream, text), 200.00 KiB.

[18 Oct 2005 23:32] MySQL Verification Team
Thank you for the feedback I was able to repeat.
[1 Mar 2006 15:15] Michael Zwach
I am now facing this problem! MyODBC5 does not work at all - it does not get any data.

So is there a solution for MySQL5 and ÓDBC 3.51?
[26 Apr 2006 8:45] Martijn Starrenburg
What's the status regarding this issue?
I tested MyODBC 2.51.13 but the problem is still there.
Martijn
[1 Jun 2006 6:57] Olle Hints
Still facing this problem with MS Access 2003, MyODBC 3.51.12 and MySQL 5.0.22. It makes the long awaited views totally unusable for those poor fellows who use Access as the front-end to the data. Well, that's alright (though views could make developers life much easier in many cases). What worries me much more is that in this case the server returns more data via a view than expected. It is like if Access and MyODBC define a completely new view which is not aware of the criteria (like WHERE privilege>0) of the original view saved in MySQL server. Isn't that a bad thing? It seems to be pretty much of a security issue as well if someone (on the server side) uses views to restrict data access and neat users with MS Access and MyODBC can get much more out of it. 
So how can it be only the bug in MyODBC? Aren't there some problems on the server side too?
[9 Jun 2006 17:24] Bogdan Degtyariov
It is a problem in the client library.
See bug #19671. Its priority has been increased to high.
[8 Mar 2007 1:21] Jim Winstead
This was a server problem which was fixed in MySQL 5.0.25.