[ODBC-119] MariaDB connected through ODBC does not match MSAccess table Created: 2017-10-18  Updated: 2018-02-11  Resolved: 2018-01-28

Status: Closed
Project: MariaDB Connector/ODBC
Component/s: General
Affects Version/s: 3.0.2
Fix Version/s: 2.0.16, 3.0.3

Type: Bug Priority: Major
Reporter: Ingo Korndoerfer Assignee: Lawrin Novitsky
Resolution: Fixed Votes: 0
Labels: None
Environment:

Windows 10


 Description   

Hello,

I am using the connector version 3.02.

Why do my MariaDB tables not match what I get in MSACCESS?

I will show the top three records here:

MariaDB:
'327', '110', '6704', '2017-10-10 12:53:07', NULL
'326', '110', '6689', '2017-09-28 16:07:55', NULL
'325', '110', '6688', '2017-09-28 16:07:49', NULL

MSAccess:
325 110 6688 28.09.2017 16:07:49
325 110 6688 28.09.2017 16:07:49
325 110 6688 28.09.2017 16:07:49

Please pay attention to the first column.

Here is the create statement for my table on MariaDB:

CREATE TABLE `ctbl_compoundbundles_compounds` (
`BundleCompoundID` int(11) NOT NULL AUTO_INCREMENT,
`CompoundBundleID` int(11) NOT NULL,
`CompoundID` int(11) NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`DateTime_ODBCWriteConflict_Fix` datetime DEFAULT NULL,
PRIMARY KEY (`BundleCompoundID`),
UNIQUE KEY `UNIQUE` (`CompoundBundleID`,`CompoundID`),
KEY `INDEX` (`BundleCompoundID`)
) ENGINE=InnoDB AUTO_INCREMENT=328 DEFAULT CHARSET=latin1;
SELECT * FROM crystaltrain.ctbl_compoundbundles_compounds;

 Comments   
Comment by Ingo Korndoerfer [ 2017-10-18 ]

Reverted back to Connector 3.0 since 3.02 throws an "Error 3000" in MSACCESS upon attempting to connect the tables. "Error 3000" does not appear anymore there, but tables remain garbled.

Comment by Ingo Korndoerfer [ 2017-10-18 ]

Table comes across ungarbled when I add the Primary key into the "unique" specification:
...
UNIQUE KEY `UNIQUE` (`CompoundBundleID`,`CompoundID`,`BundleCompoundID`),
...
But that is, naturally, not what I want, since it will not protect me from assigning duplicate `CompoundBundleID`,`CompoundID` combinations. It's just curious that this would result in a correctly linked table.

Comment by Lawrin Novitsky [ 2017-10-19 ]

Thank you for your report. Looks like I need to pay attention to all columns, and not only to the 1st one.
And looks also that we have 2 bug reports now, since that error with 3.0.2

Comment by Lawrin Novitsky [ 2018-01-22 ]

The workaround would be to remove KEY `INDEX` (`BundleCompoundID`), and you don't need it since there is primary key on BundleCompoundID

Comment by Lawrin Novitsky [ 2018-01-22 ]

Hmm... all of a sudden I cannot recreate the problem with current repository version. I have checked everything twice already. I will verify if everything is correct tomorrow morning.
But if I give you binaries, would it be possible to verify if it solves the problem for you too?

Comment by Lawrin Novitsky [ 2018-01-26 ]

A short update - I could again re-create the problem, and then it'd gone for me again. And all that with same connector version. That is probably an evidence of some bug inside Access, but that does not mean that there is no bug in the connector. I was distracted by other issues since then, now I am back to this one

Comment by Lawrin Novitsky [ 2018-01-28 ]

The connector ordered SQLStatistics results using wrong columns. That could cause MS Access to pick wrong column as a unique index. And that, in its turn, could cause mangled data shown for the linked table. Also that caused that the bug was not always reproducable - sometimes rows in resultset were in "correct" order.
Commit(in odbc-3.0) 740c862. Will be merged into odbc-2.0, since it is also vulnerable.

Generated at Thu Feb 08 03:26:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.