MySQL Bugs: #22014: Problems when opening a VIEW using the MyODBC in Access.

Bug #22014	Problems when opening a VIEW using the MyODBC in Access.
Submitted:	5 Sep 2006 12:17	Modified:	6 Sep 2006 14:11
Reporter:	Stefaan Lemaire	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	MyODBC 3.51.12	OS:	Windows (WinXP)
Assigned to:		CPU Architecture:	Any
Tags:	MyODBC, VIEW

Description:
When I try to open a linked VIEW in Access, I get to following error:

[mysqld-5.0.24-standard] Unknown column 'mydate3' in 'field list' (#1054)

If you try to open the view in the "MySQL Query Browser" everything works fine.
So I think it is related to the ODBC connection.

How to repeat:
MySQL server version 5.0.24 is running on Linux.
MyODBC driver version 3.51.12 was used.

The table create statement used is :

CREATE TABLE `dmy_bug_test` (
  `empid` char(5) NOT NULL,
  `mydate` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Insert some values into the tables with a few duplicates.

This is the create statement for the VIEW:

create view myview as select empid, min(mydate) as mydate3 from dmy_bug_test group by empid;

Then link the table in Access 2000 and try to open it.

Hi Stefaan and thanks for your problem report.
I was unable to verify it:
  MySQL server 5.0.25BK on Suse 10.0 host
  MyODBC 3.51.12 GA
  MS Access 2003
  Win XPPro SP2
  I added "Option=3" to my connect string.
I suggest you try updating OS and/or reinstall MyODBC/Access.

Access test - view

Attachment: 22014-1.jpg (image/jpeg, text), 52.14 KiB.

Access test - table

Attachment: 22014-2.jpg (image/jpeg, text), 17.34 KiB.

DDL:
mysql -uroot -hmunja --port=3307 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `dmy_bug_test` (
    ->   `empid` char(5) NOT NULL,
    ->   `mydate` datetime NOT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into dmy_bug_test values (1,"2006-01-01"),(2,"2006-01-01");
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into dmy_bug_test values (1,"2006-02-01"),(2,"2006-02-01");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into dmy_bug_test values (1,"2006-04-01"),(2,"2006-05-01");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into dmy_bug_test values (1,"2006-01-01"),(2,"2006-01-01");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create view bug22014 as select empid, min(mydate) as mydate3 from dmy_bug
_test group by empid;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from bug22014;
+-------+---------------------+
| empid | mydate3             |
+-------+---------------------+
| 1     | 2006-01-01 00:00:00 |
| 2     | 2006-01-01 00:00:00 |
+-------+---------------------+
2 rows in set (0.01 sec)

mysql>

Software used:
- RedHat Enterprise Server 3
- Ms Access 2000
- MySQL Binairies v. 5.0.24a 
- MyODBC 3.51.12

I see you are using another OS, other version of MySQL and Ms Access 2003.
So it is really hard to compare or pin out where the problem is coming from.

Is it possible to obtain the RPM packages for RHEL3 of version 5.0.25?
In that way I can exclude that it is coming from the MySQL server software.

I can reproduce the error on different machines, all give the same error.

Tnx
Stefaan

Hi Stefaan.
> I see you are using another OS, other version of MySQL and Ms Access 2003.
Bug was entered with XP as OS, so I don't see the problem there. All SW we work on has to be registered so I can't have everything on my test machines. If there's problem with Access 2000 or some SP I really don't see how I can locate it. As for server version, we always test on latest sources but I really don't think the problem lies there.
Just to be on the safe side, I asked my colleague to test on Access 2000.

Hi Stefaan.
Shawn Green verified the reported behavior with Access 2000 on various hosts. So let me summarize:
  - MySQL server works, on any host and in several versions from 5.0.20 to 5.0.25, as expected
  - MyODBC 3.51.12 works with MS Access 2003 as expected
  - MyODBC 3.51.12 does not work with Access 2000
At this point I'd suggest you to upgrade your Access and test.

Hi Stefaan.
Earlier versions of MS Access requested information about tables in an unusual way provoking MyODBC to return non-existing table names. Newer versions probably fixed that but that's really a question for Microsoft.
From our side, if you don't want to upgrade Access, you should wait until we release MyODBC linked against 5.0.25 libraries. When will this version be ready I really can't tell you now.