Bug #106204 Unable to add mysql views to ms access.(Cannot define Field more than once)
Submitted: 19 Jan 2022 1:05 Modified: 6 Mar 2022 22:54
Reporter: Krish KM Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:>8.0.26.0 OS:Windows
Assigned to: CPU Architecture:Any
Tags: MS Access, MySQL, ODBC Connector

[19 Jan 2022 1:05] Krish KM
Description:
I'm upgrading mysql .net connector from 8.0.25 to newer version (have tried 26,27,28) all of them has a bug that doesn't work with Ms Access importing/linking odbc tables.

The bug is only appearing when there are two databases and both containing same table name + same field names (doesn't need to be 100% clone)
I.e. 
Schema_A has a TableA with FieldA,B,C
Schema_B has a tableA with FieldA,C.

In above case Schema A and B have a table called TableA and both containing fields called FieldA and FieldC.

When TableA is linked to Ms Access front end via odbc, an error message is thrown "Cannot define Field more than once". 

Why this is happening: I can only think of this. To link the table, the odbc connector is requesting field names for the given table but getting field names from all database schemas for the same table name. Causing potential duplicate field names.

version 8.0.25 is safe but all newer versions are affected by this bug.

I have to add, this error message is only appearing if you are trying to add a new linked table. already linked tables can be opened and worked without any issues. Adding new linked tables or refreshing the linked table causing this error.

I have marked this as serious as "adding new linked table" is quite an important part in my application. Staying with 8.0.25.0 is getting tougher as I'm being forced to upgrade to newer odbc connectors due several security issues.

How to repeat:
To repeat, you need a windows environment and Microsoft Office installed.(O365 or office 2019 doesn't matter)

In MySql side: 
Create SchemaA, TableA with fields and records, ViewA (Select * from SchemaA.TableA)

Create another SchemaB, ViewB (Select * from SchemaA.ViewA)

Now try to add link/import the SchemaA.ViewA or SchemaB.ViewB to Ms Access.
(https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-access-link... tutorial is bit outdated but the concept is still the same)

For me it's failed with an error message "Cannot define Field more than once".

Suggested fix:
Suggestion to Mysql Team:
If my doubt is correct, the odbc connector should apply schema_name filter when requesting for field names for a table. Otherwiese, a user account with access to more than one schemas may receive duplicate field names from all the schemas.

Suggestion to Ms Access users:
This is caused by having objects with same name with similar fields in two different schemas. If your work environment allows, you can rename one table/view to fix this. OR wait for a fix from MySql team.
[19 Jan 2022 10:24] Krish KM
all version above 8.0.26.0 are affected. 8.0.26.0 seems to be working
[21 Jan 2022 13:52] MySQL Verification Team
Bug #106235 marked as duplicate of this one.
[25 Jan 2022 12:57] MySQL Verification Team
Hello Krish,

Thank you for the bug report.
Verified as described.

Regards,
Ashwini Patil
[25 Jan 2022 12:58] MySQL Verification Team
8.0.28 test results

Attachment: 106204_test_results.PNG (image/png, text), 38.92 KiB.

[25 Jan 2022 13:19] Rafal Somla
Posted by developer:
 
The first sentence of the initial bug description is confusing:
~~~~
I'm upgrading mysql .net connector from 8.0.25 to newer version (have tried
26,27,28) all of them has a bug that doesn't work with Ms Access
importing/linking odbc tables.
~~~~

Is this bug about connector/.NET or connector/ODBC? For now I assume the latter, because the rest of description speaks about ODBC.
[27 Jan 2022 12:50] MySQL Verification Team
Bug #106277 marked as duplicate of this one.
[28 Jan 2022 10:53] Krish KM
@Rafal Somla. Sorry for the confusion, i'm unable to edit the initial text.
as the bug category says it's  MySQL Connector ODBC.
[7 Feb 2022 9:50] Bogdan Degtyariov
Hi Krish,

I am not able to reproduce the problem.
Would it be possible for you to send the exact SQL statements that can help reproduce the problem?

I used the following ones and there was no error after importing/linking `view_b`:

drop schema if exists schema_a;
drop schema if exists schema_b;
create schema schema_a;
create schema schema_b;
create table schema_a.table_a (id int primary key auto_increment, a varchar(16), b int, c datetime);
create table schema_b.table_a (id int primary key auto_increment, a varchar(16), c datetime);
insert into schema_a.table_a values (1, 2, "3", NOW()), (4, 5, "6", NOW()), (7, 8, "9", NOW());
create view schema_a.view_a as select * from schema_a.table_a;
create view schema_b.view_b as select * from schema_a.view_a;

I also consulted with Ashwini who succeeded in reproducing the problem, but then the error stopped showing up. The import and linking went without errors.

Thanks.
[9 Feb 2022 13:41] Marco Chiapparini
Hi Bogdan,

I managed to reproduce the problem with exactly your SQL statements. See attachment.
[9 Feb 2022 13:43] Marco Chiapparini
8.0.28 test results

Attachment: 106204_test_results-2.PNG.png (image/png, text), 1.43 MiB.

[9 Feb 2022 13:48] Marco Chiapparini
Just try to connect with a user that can see both schemas a and b, or the bug won't repoduce.
[9 Feb 2022 13:53] Marco Chiapparini
Also, try to connect the table, not the view
[11 Feb 2022 10:43] Bogdan Degtyariov
Posted by developer:
 
Thank you Marco,
I was able to repeat the problem when linking the table instead of the view.
[14 Feb 2022 23:28] Bogdan Degtyariov
Posted by developer:
 
The problem is with SQLColumns() function, which was recently migrated to use Information_Schema.
When Catalog and Schema are not specified (NULL, but not empty string "") the function is supposed to return columns in the tables from the current database, not all databases.
The error occurs if a table with the same name and the same set of columns exists in another database. In this case MS Access sees result rows that are identical except the database name, which for some reason it does not check.

Anyway, the correct behavior for SQLColumns() would be not to return the columns from the tables in other databases unless specifically requested.
[15 Feb 2022 7:29] Bogdan Degtyariov
Posted by developer:
 
The fix and the corresponding unit tests are now in the source repository.
[17 Feb 2022 13:10] James Stanford
Same issue for myself - Sadly unable to amend the table structure to fix issue so will need to continue to use 8.0.25 until fixed
[6 Mar 2022 22:54] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 8.0.29 release, and here's the proposed changelog entry from the documentation team:

When a Catalog or Schema is not specified, and if a table with the same
name and set of columns existed in another database, SQLColumns() would
return data from multiple databases instead of only the current database.
In this case, MS Access would yield an error as the database name was not
checked for. Now only data from the current database is returned, unless
specifically specified.

Thank you for the bug report.
[4 Apr 2022 9:44] James Matthews
Took me several hours to find this. Do we know roughly when 8.0.29 is due to be released as this is causing a lot of issues for us and we dont want to downgrade.

James