[MDEV-15577] CONNECT engine JDBC remote index prevents UPDATE Created: 2018-03-15  Updated: 2018-05-06  Resolved: 2018-03-27

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.2.13
Fix Version/s: 10.0.35, 10.1.33, 10.2.15

Type: Bug Priority: Major
Reporter: Robert Dyas Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: None
Environment:

centos7


 Description   

If I declare a CONNECT table (test case is using MS SQL Server) I can easily issue simple UPDATE statements like:

UPDATE MyConnectTable SET name = 'bob' WHERE id = 23

The above works great. However, if I add a remote index to the table as follows (or during the CREATE TABLE statement) the UPDATE will fail:

ALTER TABLE MyConnectTable ADD INDEX (id)

For some reason it appears that CONNECT won't let me issue an UPDATE if I declare the primary key column of the remote table as indexed.

 Comments   
Comment by Robert Dyas [ 2018-03-22 ]

Olivier - am I missing something, or is this a bug?

Comment by Olivier Bertrand [ 2018-03-23 ]

Yes, it is a trivial regression bug not met until you did because creating an index on a JDBC table is almost never useful. I don't know what you mean by "CONNECT won't let me issue an UPDATE" but for me it just made the server crash!
The rare cases creating a remote index make sens is when a big table is joined to another or queried with an ORDER BY clause, which is never the case for queries other than SELECT ones.
Until I publish the fix, the easy and obvious turnaround is: don't index JDBC tables.

Comment by Robert Dyas [ 2018-03-27 ]

I'm not seeing this listed in the 10.2.14 change log.
https://mariadb.com/kb/en/library/mariadb-10214-changelog/

Comment by Olivier Bertrand [ 2018-03-27 ]

Of course, I just pushed the fix today.

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