Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
2.0.11, 2.0.12
-
None
-
None
-
MS Access 2010 + MariaDB connector (Windows 7) + MariaDB 10.1.17 (Debian 8)
Description
In my company we use a MS Access application with a MariaDB Galera cluster. For the moment we use the MySQL connector, but we would like to migrate to the MariaDB connector at some point.
Therefore we are performing some test right now and realised that the development version of our application fails to open one specific window. The changes made in that window, and which definitely are causing the issue (the simple query works), are just a query change from this simple query:
SELECT id, titleen as title FROM tblorg |
to this query:
SELECT tblomain.id, tblomain.title |
FROM ( |
SELECT tbloaux1.id, tbloaux1.titleen as title |
FROM tblorg tbloaux1 |
UNION |
SELECT tbloaux2.id, tbloaux2.titlelongen as title |
FROM tblorg tbloaux2) |
AS tblomain |
ORDER BY tblomain.title; |
When including this last query, the application fails to open the window correctly and crashes.
I am aware that this query is somehow weird, but this is quite a special request from our users and access sql is also a bit weird for certain things sometimes.
Thanks in advance for having a look to this issue.
Attachments
Activity
Thank you for your report!
Even weird queries should behave and not crash applications.
Would it be possible to provide structures of tables in the query?
Thank you in advance
Yes, of course. There is only one table being used, although referenced a few times. This is the structure:
CREATE TABLE `tblorg` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`niveau` int(11) DEFAULT NULL, |
`mother` int(11) DEFAULT NULL, |
`titleEn` varchar(50) CHARACTER SET latin1 DEFAULT NULL, |
`titleLongEn` varchar(100) CHARACTER SET latin1 DEFAULT NULL, |
`country` int(11) DEFAULT NULL, |
`type` int(11) DEFAULT NULL, |
`titleDe` varchar(50) CHARACTER SET latin1 DEFAULT NULL, |
`titleLongDe` varchar(100) CHARACTER SET latin1 DEFAULT NULL, |
`titleFr` varchar(50) CHARACTER SET latin1 DEFAULT NULL, |
`titleLongFr` varchar(100) CHARACTER SET latin1 DEFAULT NULL, |
`titleEs` varchar(50) CHARACTER SET latin1 DEFAULT NULL, |
`titleLongEs` varchar(100) CHARACTER SET latin1 DEFAULT NULL, |
`titlePt` varchar(50) CHARACTER SET latin1 DEFAULT NULL, |
`titleLongPt` varchar(100) CHARACTER SET latin1 DEFAULT NULL, |
`IsLegalEntity` tinyint(4) NOT NULL DEFAULT '0', |
`statute` int(2) DEFAULT NULL, |
`since` int(4) DEFAULT NULL, |
`affiliation` varchar(50) CHARACTER SET latin1 DEFAULT NULL, |
`street` varchar(150) CHARACTER SET latin1 DEFAULT NULL, |
`streetzip` varchar(30) CHARACTER SET latin1 DEFAULT NULL, |
`pobox` varchar(30) CHARACTER SET latin1 DEFAULT NULL, |
`poboxZip` varchar(30) CHARACTER SET latin1 DEFAULT NULL, |
`town` varchar(50) CHARACTER SET latin1 DEFAULT NULL, |
`www` varchar(200) CHARACTER SET latin1 DEFAULT NULL, |
`remark` text CHARACTER SET latin1, |
`phone` varchar(50) CHARACTER SET latin1 DEFAULT NULL, |
`fax` varchar(50) CHARACTER SET latin1 DEFAULT NULL, |
`email` varchar(50) CHARACTER SET latin1 DEFAULT NULL, |
`staffFull` int(7) DEFAULT NULL, |
`staffPart` int(7) DEFAULT NULL, |
`bank` varchar(150) CHARACTER SET latin1 DEFAULT NULL, |
`account` varchar(50) CHARACTER SET latin1 DEFAULT NULL, |
`bankcode` varchar(50) CHARACTER SET latin1 DEFAULT NULL, |
`swift` varchar(20) CHARACTER SET latin1 DEFAULT NULL, |
`debtornum` varchar(10) DEFAULT NULL, |
`creditornum` varchar(10) DEFAULT NULL, |
`VATnum` varchar(20) DEFAULT NULL, |
`CodeDate` datetime DEFAULT NULL, |
`CodePerson` datetime DEFAULT NULL, |
`director` int(11) DEFAULT NULL, |
`orgid` int(11) DEFAULT NULL, |
`organisationsid` int(11) DEFAULT NULL, |
`clientsid` int(11) DEFAULT NULL, |
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
PRIMARY KEY (`id`), |
UNIQUE KEY `uq_tblo_debtornum` (`debtornum`), |
UNIQUE KEY `uq_tblo_creditornum` (`creditornum`), |
UNIQUE KEY `uq_tblo_VATnum` (`VATnum`), |
KEY `oldId` (`orgid`), |
KEY `fk_tblorg_country_tblco` (`country`), |
KEY `fk_tblorg_type_tbltp` (`type`), |
CONSTRAINT `fk_tblorg_country_tblco` FOREIGN KEY (`country`) REFERENCES `tblcountry` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, |
CONSTRAINT `fk_tblorg_type_tbltp` FOREIGN KEY (`type`) REFERENCES `tbltype` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION |
) ENGINE=InnoDB AUTO_INCREMENT=9869 DEFAULT CHARSET=utf8; |
The fix iand the bugfix have been pushed. The reason was that MS Access adds parenthesis around each SELECT in the UNION statement. And that prevented connector to correctly establish type of query(needed for some optimizations)
Unfortunately it haven't made to 2.0.13(which has been already built), and appear first in 3.0.0alpha
Great, thanks! We will keep on testing the connector with the hope of being able to migrate at some point.
All the best
Just a heads up. With a simplified version of the query, it does not seem to work either: