MySQL Bugs: #106204: Unable to add mysql views to ms access.(Cannot define Field more than once)

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

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.

all version above 8.0.26.0 are affected. 8.0.26.0 seems to be working

Bug #106235 marked as duplicate of this one.

Hello Krish,

Thank you for the bug report.
Verified as described.

Regards,
Ashwini Patil

8.0.28 test results

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

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.

Bug #106277 marked as duplicate of this one.

@Rafal Somla. Sorry for the confusion, i'm unable to edit the initial text.
as the bug category says it's  MySQL Connector ODBC.

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.

Hi Bogdan,

I managed to reproduce the problem with exactly your SQL statements. See attachment.

8.0.28 test results

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

Just try to connect with a user that can see both schemas a and b, or the bug won't repoduce.

Also, try to connect the table, not the view

Posted by developer:
 
Thank you Marco,
I was able to repeat the problem when linking the table instead of the view.

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.

Posted by developer:
 
The fix and the corresponding unit tests are now in the source repository.

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

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.

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