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: | |
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
[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