Access frontend. MariaDb backend. Linked using DSN-less connections (but also happens if using regular DSN).

64b Access 16.0.15427.20194, Windows 11 (also happens Windows 10)

Problem occurs on:
Maria ODBC connector 3.1.16, Maria Server 10.3.22
Maria ODBC connector 3.1.16, Maria Server 10.8.3

Problem does NOT occur on:
Maria ODBC connector 3.0.8, Maria Server 10.3.22

Connection string:
DRIVER=

;SERVER=localhost;PORT=3306;DATABASE=ctu;USER=root;OPTION=67108914;Trusted_Connection=Yes;

Thank you for your report. I would imagine that could be connected to ODBC-361, than to 360
But could you please provide some more data? Like table structure.
As for parameter names - if there is OPTION in connection string after named option value, OPTION should override. What goes later in the connection string, takes precedence, and connection string options take precedence over options set in the DSN. If you see something, that you think is wrong with connection string processing, please open a separate ticket for that problem.

The database has a lot of tables, but here is a sample of a couple:
– Dumping structure for table demodata.AccountClasses
CREATE TABLE IF NOT EXISTS `AccountClasses` (
`AccountClass` smallint(6) NOT NULL COMMENT 'Classification code for this type of account',
`Description` varchar(50) NOT NULL COMMENT 'Description for this account classification',
`TimeCreated` datetime DEFAULT current_timestamp(),
`TimeModified` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`AccountClass`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Option 101';

– Dumping structure for table demodata.ApplicationDefaults
CREATE TABLE IF NOT EXISTS `ApplicationDefaults` (
`ApplicationType` varchar(50) NOT NULL COMMENT 'Type of application that this item applies to',
`ApplicationItemID` varchar(50) NOT NULL COMMENT 'Item that is usually required for this type of application',
`TimeCreated` datetime DEFAULT current_timestamp(),
`TimeModified` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`ApplicationType`,`ApplicationItemID`),
KEY `FK_ApplicationDefaults_ApplicationItems` (`ApplicationItemID`),
CONSTRAINT `FK_ApplicationDefaults_ApplicationItems` FOREIGN KEY (`ApplicationItemID`) REFERENCES `ApplicationItems` (`ApplicationItemID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_ApplicationDefaults_ApplicationTypes` FOREIGN KEY (`ApplicationType`) REFERENCES `ApplicationTypes` (`ApplicationType`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Option 163 Bottom of Screen';

– Dumping structure for table demodata.AccountTypes
CREATE TABLE IF NOT EXISTS `AccountTypes` (
`AccountType` varchar(20) NOT NULL COMMENT 'Code for this type of account',
`FileFlag` smallint(6) NOT NULL COMMENT 'Which type of account is this: 1=Person 2=Organization',
`AccountClass` smallint(6) NOT NULL COMMENT 'Classification for this type of account',
`TrackAcademicDelinq` smallint(6) NOT NULL DEFAULT -1 COMMENT 'Should academic delinquency be reported? Yes/No',
`Default` smallint(6) NOT NULL DEFAULT 0 COMMENT 'Answer Yes if this is the default for new Accounts',
`ClassRankFlag` smallint(6) NOT NULL DEFAULT 0 COMMENT 'Are these Accounts included in Class Ranks: Yes/No',
`AllowNewGrades` smallint(6) NOT NULL DEFAULT -1 COMMENT 'Are new gradebooks entries allowed? -1=Yes, 0=No',
`CompletionStatus` smallint(6) NOT NULL COMMENT 'Code that identifies the completion status',
`WebConnectAuthorizationAcctType` smallint(6) NOT NULL DEFAULT 0 COMMENT 'Web Connect Authorization: -1=Disable All WC Screens; 0=Enable All WC Screens; 1&Up: Disable Specific WC Screens',
`AutoGeneratePrimaryEmail` smallint(6) NOT NULL DEFAULT 0 COMMENT '-1=Yes, 0=No',
`IncludeInSchoolDocsExport` smallint(6) NOT NULL DEFAULT 0,
`TimeCreated` datetime DEFAULT current_timestamp(),
`TimeModified` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`LongDescription` varchar(255) DEFAULT NULL,
PRIMARY KEY (`AccountType`),
KEY `FK_AccountTypes_AccountClasses` (`AccountClass`),
KEY `FK_AccountTypes_CompletionStatuses` (`CompletionStatus`),
CONSTRAINT `FK_AccountTypes_AccountClasses` FOREIGN KEY (`AccountClass`) REFERENCES `AccountClasses` (`AccountClass`) ON UPDATE CASCADE,
CONSTRAINT `FK_AccountTypes_CompletionStatuses` FOREIGN KEY (`CompletionStatus`) REFERENCES `CompletionStatuses` (`CompletionStatus`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Option 100';

I decided to try a very simple table. Still no joy (as expected). The attached SQL was a database with a very simple, single table.

Do you and your team have any insights into this problem? Are there settings or actions you'd like me to try?

test1.sql

I've removed the link as I am not sure that the issues are even connected.
I guess there is little you can do before we fix. The only thing - most of table descriptions you showed here have varchar primary key, and that promises issues due to ODBC-360 you linked.

Ok, I've just tried couple of your tables - those w/out foreign key constraints, and my access shows PK for all of them, and for other linked tables I have. The difference is probably in the Access version - I have 32b Microsoft® Access® 2019 MSO (Version 2207 Build 16.0.15427.20166) 32-bit. I'll get 64b to try.

You must define the index column as follows:
1. Default value: Auto Increment
2. Create index Primary
3. Create index Unique

This works for me when connecting to MS-Access with odbc

The attached file creates a database and a table tbl_colors for testing. Does it work for you?

tbl_colors.sql

I tried Jorg's suggestion of adding a unique index on the PK's (in addition to PK). Although by definition a PK has to be unique, I gave it a try. No joy. It does not resolve the problem. I wanted to just note the test result and thank Jorg for his suggestion.

Today I finally got back to investigating this problem and I believe I've found an important clue: this problem appears to happen with recent ODBC connector version against recent MariaDb server running on Windows. I have used the latest ODBC connector (3.1.17 Win64) to successfully connect to a version of our database on LINUX server (running MariaDb 10.7.1). All PK's working. I ran the usual test against latest MariaDb 10.11.2 for Win64 and the PK's are not working. I also tested the latest ODBC connector (3.1.17 Win64) against the old, tried and true, MariaDb server for Windows 10.3.22 ... and the problem exists (no PK's read). So the latest ODBC Connector is not reading PK's the same as the older connector ... so we can't use the latest ODBC Connector either. I am about experiment with a Linux VM running a more recent mariadb server ... however, we have customers using Windows for their MariaDb server so this is still a problem even if that works.

Also, just for reference... HeidiSQL has no problem ascertaining the keys/indexes etc ... this is an ODBC thing. Perhaps only Access via ODBC thing.

Jorg Sorry, I didn't look at the tbl_colors at the time. The problem with it is probable unique key on the `id` field. Why do you need it? Primary key is quite enough and it is unique. I linked your table, and it behaved... strange, I am looking into it as your table structure is still valid, but when I removed the UNIQUE key from it - things look normal.

thrunch Search and use of primary keys was added in one of recent versions. Before the condition to identify a row was build using all columns. That can explain what you see.

Anyway, I am on this ticket now.

thrunch Well, I was talking about use of primary keys in the data manipulations in Access, that is probably not related to your problem. But catalog functions returning lists of indexes were fixed recently as well.

But I have a question - how do you actually check if table has primary key. I open it in design view, and primary key field has key next to it, and also the row Indexed says "Yes(No Duplicates)" Just verified that once again with aforementioned tbl_colors in both its versions.

Ok, I've obtained 64bit Access, and still no luck - all tables have primary keys, all operations are fine.
In initial example the table has varchar primary key, thus if the problem is with operations on rows involving key field values with non-ascii symbols, then it's a duplicate of ODBC-360. Otherwise I can't repeat it.
p.s. I've noticed smth, that I'd missed before and gonna try with 10.11

I've tried against 10.11 - all works just like with other version, i.e. fine. I'm stopping with the ticket so far. I need something more to understand/repeat the problem.
JorgBtw, tbl_colors with redundant unique key also works well. The cause was in my settings for the DSN I use(it had force forward_only+no cache options - this causes "commands out of order" DM error with Access)

Lawrin Novitsky: Apologies for my delayed response as we have several things going on at once. Based on your comments I decided to re-run my tests looking carefully at the results using different connectors and Maria servers. You will notice that the problem exists with Maria servers running on windows ... and notice NO primary keys are returned, regardless of type of field. In the cases where it returns the unique field instead of the actual PK ... as our actual schema is known to our application. But with no workable PK returned, the application cannot run because Access cannot operate on the table.

I examined the PK results for 3 tables:
Accounts table having varchar PK and also a unique "AltGUID" in varchar field.
StudentAidPackages having autonumber PK and also a unique "AltGUID" in varchar field.
StudentAidPackageAwardResponses having only autonumber PK

3.0.8 ODBC connector against Windows MariaDb 10.3.22
[correct] Accounts table has varchar PK
[ ok ] StudentAidPackages reads AltGUID as PK instead of actual autonumber PK
[correct] StudentAidPackageAwardResponses table has autonumber PK

3.0.8 ODBC connector against Linux MariaDb 10.5.16
[correct] Accounts table has varchar PK
[ ok ] StudentAidPackages reads AltGUID as PK instead of actual autonumber PK
[correct] StudentAidPackageAwardResponses table has autonumber PK

3.0.8 ODBC connector against Windows MariaDb 10.11.2
[correct] Accounts table has varchar PK
[ ok ] StudentAidPackages reads AltGUID as PK instead of actual autonumber PK
[correct] StudentAidPackageAwardResponses table has autonumber PK

3.1.17 ODBC connector against Windows MariaDb 10.3.22
[ FAIL ] Accounts table has no PK
[ FAIL ] StudentAidPackages has no PK
[ FAIL ] StudentAidPackageAwardResponses has no PK

3.1.17 ODBC connector against Linux MariaDb 10.5.16
[correct] Accounts table has varchar PK
[ ok ] StudentAidPackages reads AltGUID as PK instead of actual autonumber PK
[correct] StudentAidPackageAwardResponses table has autonumber PK

3.1.17 ODBC connector against Windows MariaDb 10.11.2
[ FAIL ] Accounts table has no PK
[ FAIL ] StudentAidPackages has no PK
[ FAIL ] StudentAidPackageAwardResponses has no PK

In all cases the connection settings included:
NO_PROMPT=1 (OPTION 16)
FOUND_ROWS=1 (OPTION 2)
DYNAMIC_CURSOR=1 (OPTION 32)
MULTI_STATEMENTS=1 (OPTION 67108864)
Trusted_Connection=Yes

Are there particular settings for the connector that you want me to try?

As an aside: Access does not allow multiple statements in single query definition, although they can refer to other queries. So multi is probably not needed.

Also also wik: I also ran a test against a Microsoft Azure MariaDb instance. I saw similar results between the 2 connectors (old 3.0.8 ok/acceptable, newer 3.1.17 gets no PK's).

Just couple of comments
How do you actually examine PK? I asked that already above.
I primarily use servers running on Windows. Also, I would expect, that the platform, where the server runs should not matter. Unless you have something wrong with letter cases in your names. On Windows schema(aka catalog in the ODBC driver)/table names are case insensitive, while they are case sensitive on Linux. But if this was the reason, I'd expect problems with Linux server, and not Windows. Server version could matter, but out of top of my head can't really think of the difference between 10.5 and 10.3, that could affect this. It looks very strange to me. Btw, 10.3 is eol'ed in less than 2 months.
For the case it picks unique instead of primary, can you change your unique index name, so it will greater than primary key name(i.e. > PRIMARY)? I wonder if this will amend

You can tell by:
(1) You can see whether Access thinks the linked table has a PK by opening it, from within Access, in design view. As you described, you will see the little key next to the field (or fields) that make up the PK for the table.
(2) You can also tell by manually trying to edit a row of data in the worksheet view ... if there is no PK then Access will complain that the data cannot be edited (this behavior is only true for ODBC linked tables).
(3) You can also tell when you manually link a table using External Data, selecting DSN, selecting table. If Access cannot detect the PK then Access will prompt you to identify the PK via popup dialog.

Our table names are case sensitive and our MariaDb servers are running with the setting "lower_case_table_names = 2". We are also running with "character-set-server=utf8" and "collation-server=utf8_general_ci"

We are aware of the EOL on 10.3 and we have customers wanting us to resolve this problem so they can update.

Yes, I will run a test where the name of the index is alphabetically after "PRIMARY" ... I'll post when I have the result.

StudentAidPackages having autonumber PK and unique "QAltGUID" in varchar field.

3.0.8 ODBC connector against Windows MariaDb 10.3.22
[correct if index name >PRIMARY] StudentAidPackages reads actual autonumber PK instead of unique key

3.0.8 ODBC connector against Linux MariaDb 10.5.16
[correct if index name >PRIMARY] StudentAidPackages reads actual autonumber PK instead of unique key

3.0.8 ODBC connector against Windows MariaDb 10.11.2
[correct if index name >PRIMARY] StudentAidPackages reads actual autonumber PK instead of unique key

3.1.17 ODBC connector against Windows MariaDb 10.3.22
[ FAIL ] StudentAidPackages has no PK

3.1.17 ODBC connector against Linux MariaDb 10.5.16
[correct if index name >PRIMARY] StudentAidPackages reads actual autonumber PK instead of unique key

3.1.17 ODBC connector against Windows MariaDb 10.11.2
[ FAIL ] StudentAidPackages has no PK

After the above tests, I edited out the setting "lower_case_table_names=2" and restarted the Mariadb server. It did not change the results ... still no PK's against Windows MariaDb server.

So, basically we do it the same way. Thus it's a bit strange why I can see primary keys everywhere, and you can not

Could you please provide one of your tables structure? Maybe of StudentAidPackageAwardResponses - as it's simplest for our case. or maybe all of them, if that is not a problem

As for case sensitivity - as I said, if it was the reason, I'd expect problems with Linux servers, and not Windows. But it's other way around for you.

I don't know, why I did not ask you before. Probably because I can't repeat the issue. Could you please provide ODBC trace of linking of any of tables you see the problem with. Maybe with opening of the table, to be safe. Although I think linking is enough. Would be great along with with table "create table ..."

This happens to every table, even the simple test table I set up and sent as attachment test1.sql.

I have to hang my hat up for the day. I will run a test with ODBC trace of linking tomorrow (Thursday). But I've not had to do that before so a quick primer on setting up trace would be appreciated, even if it is just the link to documentation/page - shorten my search/read. Thanks

and here we are - for the table in test1.sql I can see the pk

Tracing is easy - in ODBC Data Sources(you need the one for 64bit, since your Access and drivers are 64b afair) there is tab Tracing, you are turning it on there, you also can pick the trace file name and location. Then start Access and do linking and open table. Then you may turn tracing off, and send me the file.

Apparently not so easy for me. I've tried and no log file seems to get written. I've run it as administrator and with machine wide tracing. No joy on any log file. Am I missing something?

Push Start Tracing Now > Apply/Ok -> Start Access> Link and open Tables ->Back to Tracing Tab to Stop Tracing Now, go and take PK TEST 1.LOG in c:\gpmaster\DEV19
The tracing should be on before you connect to data source. or maybe even before driver dll is loaded. Thus it's safer, if access is closed when you turning tracing on

I already had Access open (but not linked) when I tried it last night. I was successful in capturing your trace. I opened a blank database and linked manually (not using our code) to a Maria database which includes the test table per test1.sql ... no PK's after linking.

The trace is attached. I hope this reveals the problem! Thank you for your patience and diligence. PK TEST 1.LOG

I can only thank you for your patience

thrunchThe log contains only part for table TestAccount, for which I don't have the structure Well, from the log I can see it's small - free fields `TestAccountID` ,`LastName` and `FirstName`. All seem to be varchar. The only thing that is not clear if there is an index in it, probably on TestAccountID. If there is, then I have one idea to verify.

I'm sorry, I thought I had attached a SQL file having the table. Here it is:

– --------------------------------------------------------
– Host: 127.0.0.1
– Server version: 10.3.22-MariaDB - mariadb.org binary distribution
– Server OS: Win64
– HeidiSQL Version: 11.2.0.6213
– --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

– Dumping structure for table test.TestAccount
CREATE TABLE IF NOT EXISTS `TestAccount` (
`TestAccountID` varchar(50) NOT NULL,
`LastName` varchar(255) DEFAULT NULL,
`FirstName` varchar(255) DEFAULT NULL,
PRIMARY KEY (`TestAccountID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

– Dumping data for table test.TestAccount: ~0 rows (approximately)
DELETE FROM `TestAccount`;
/*!40000 ALTER TABLE `TestAccount` DISABLE KEYS */;
INSERT INTO `TestAccount` (`TestAccountID`, `LastName`, `FirstName`) VALUES
('J101', 'Smith', 'John'),
('J102', 'Kent', 'Clark');
/*!40000 ALTER TABLE `TestAccount` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;

Sorry, my bad - found TestAccount structure. It was in the attached file, and there were few tables posted separately in comments

thrunchCould you please run following queries on your server:

select @@lower_case_file_system, @@lower_case_table_names;

and

SELECT TABLE_SCHEMA AS TABLE_CAT,NULL AS TABLE_SCHEM,TABLE_NAME, IF(NON_UNIQUE=0 AND (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS s2 WHERE s2.INDEX_NAME=s1.INDEX_NAME AND s2.TABLE_SCHEMA=s1.TABLE_SCHEMA AND NULLABLE='YES') > 0,1,NON_UNIQUE) AS NON_UNIQUE,NULL AS INDEX_QUALIFIER,INDEX_NAME,3 AS TYPE,SEQ_IN_INDEX AS ORDINAL_POSITION,COLUMN_NAME,COLLATION AS ASC_OR_DESC, CARDINALITY,NULL AS PAGES,NULL AS FILTER_CONDITION FROM INFORMATION_SCHEMA.STATISTICS s1 WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME = 'TestAccount' ORDER BY NON_UNIQUE, INDEX_NAME, ORDINAL_POSITION;

The latter must be run having demodata_newmaria(or where is your TestAccount is located) as the current database, or you need to change in the query "TABLE_SCHEMA=DATABASE()" to "TABLE_SCHEMA=`<TestAccount's_Schema_Name>`"

Aren't you or DB server on Mac btw?

I think I found the reason - at least I could re-create your problem. I opened separate ticket for it to give the better description and linked to this as the one causing this one.
I think your server has lower_case_table_names set to 2, and that caused the problem with Access. if that is not so, then you can re-open the issue. If so - the workaround(before the fixed driver is released) would be to use all lowercase table names, or change lower_case_table_names to 1

Lawrin, that is indeed the situation: lower_case_table_names set to 2

Thanks for your diligence on this.

We can't use that workaround so we will wait for the bug fix. Thanks.