Description:
Works: ODBC 8.0.18, 8.0.27 Unicode. Broken: ODBC 8.0.33, 9.x (tested as 26.7.1). Server MariaDB 12.3.3, table utf8mb3_unicode_ci / utf8mb4_unicode_ci. Might very well be related to #70297 / #110274, yet this bug remains prevalent on newest versions.
Linked MariaDB/MySQL table with VARCHAR PK containing German umlaut/accent (e.g. Müller, Bär, café) shows #Deleted in every field when opened in MS-Access table view / form / query UI via Unicode driver 8.0.33+.
Same row via code (DAO.OpenRecordset, ADODB, export to local table, report) reads fine. Data on server intact. Non-PK columns with same characters display fine. Only UI row re-lookup WHERE PK=? fails.
This is standard for European locales. Umlauts/accents in natural keys (e.g. Länder, Währungen) are required, surrogate INT-PK rewrite across hundreds of customer DBs is not an option.
How to repeat:
1. Create repro table like our production table 'Absagegründe'
- (VARCHAR primary key holding a German umlaut or other non-ASCII symbol)
CREATE TABLE absagegruende_repro (
Absagegrund VARCHAR(50) NOT NULL PRIMARY KEY,
timestamp TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
INSERT INTO absagegruende_repro (Absagegrund) VALUES
('keine Liefermöglichkeit'),
('Liefermöglichkeit'),
('nicht realisiert'),
('zu spät angeboten');
- 2. Check in HeidiSQL / Workbench: all 4 rows readable
SELECT * FROM absagegruende_repro;
1. In Access, link the table via ODBC Unicode driver (DSN-less is enough):
'DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=<host>;DATABASE=<db>;USER=<user>;PASSWORD=<pw>;FOUND_ROWS=1'
once with 8.0.27 (good), once with 8.0.33+ (broken).
2. Double-click the linked table (datasheet view).
3. Result:
* 8.0.27: 'Liefermöglichkeit' displays normally.
* 8.0.33+: exactly that row (and every row with ä/ö/ü in the PK) shows '#Deleted' in all fields.
4. Code check (works on both drivers, proving no data loss):
?DLookup("Absagegrund","absagegruende_repro","Absagegrund='Liefermöglichkeit'")
returns 'Liefermöglichkeit'.
Suggested fix:
Make UI row re-fetch (WHERE on text PK) charset/collation-safe again as in 8.0.27. Bind PK lookup with connection charset, no silent 0-row match on non-ASCII bytes. TIMESTAMP/FOUND_ROWS must not be required for this.
Description: Works: ODBC 8.0.18, 8.0.27 Unicode. Broken: ODBC 8.0.33, 9.x (tested as 26.7.1). Server MariaDB 12.3.3, table utf8mb3_unicode_ci / utf8mb4_unicode_ci. Might very well be related to #70297 / #110274, yet this bug remains prevalent on newest versions. Linked MariaDB/MySQL table with VARCHAR PK containing German umlaut/accent (e.g. Müller, Bär, café) shows #Deleted in every field when opened in MS-Access table view / form / query UI via Unicode driver 8.0.33+. Same row via code (DAO.OpenRecordset, ADODB, export to local table, report) reads fine. Data on server intact. Non-PK columns with same characters display fine. Only UI row re-lookup WHERE PK=? fails. This is standard for European locales. Umlauts/accents in natural keys (e.g. Länder, Währungen) are required, surrogate INT-PK rewrite across hundreds of customer DBs is not an option. How to repeat: 1. Create repro table like our production table 'Absagegründe' - (VARCHAR primary key holding a German umlaut or other non-ASCII symbol) CREATE TABLE absagegruende_repro ( Absagegrund VARCHAR(50) NOT NULL PRIMARY KEY, timestamp TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci; INSERT INTO absagegruende_repro (Absagegrund) VALUES ('keine Liefermöglichkeit'), ('Liefermöglichkeit'), ('nicht realisiert'), ('zu spät angeboten'); - 2. Check in HeidiSQL / Workbench: all 4 rows readable SELECT * FROM absagegruende_repro; 1. In Access, link the table via ODBC Unicode driver (DSN-less is enough): 'DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=<host>;DATABASE=<db>;USER=<user>;PASSWORD=<pw>;FOUND_ROWS=1' once with 8.0.27 (good), once with 8.0.33+ (broken). 2. Double-click the linked table (datasheet view). 3. Result: * 8.0.27: 'Liefermöglichkeit' displays normally. * 8.0.33+: exactly that row (and every row with ä/ö/ü in the PK) shows '#Deleted' in all fields. 4. Code check (works on both drivers, proving no data loss): ?DLookup("Absagegrund","absagegruende_repro","Absagegrund='Liefermöglichkeit'") returns 'Liefermöglichkeit'. Suggested fix: Make UI row re-fetch (WHERE on text PK) charset/collation-safe again as in 8.0.27. Bind PK lookup with connection charset, no silent 0-row match on non-ASCII bytes. TIMESTAMP/FOUND_ROWS must not be required for this.