Details
-
Type:
Bug
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 10.0.15
-
Fix Version/s: 10.1
-
Component/s: Storage Engine - Connect, Storage Engine - Federated
-
Labels:
-
Environment:Linux x86_64
Description
[Remote]
Version: MariaDB 10.0.15
The num of records: approximately 1bn
[Local]
Version: MariaDB 10.0.15
Engine: FEDERATED
I found that LIMIT clause is not passed to federated/connect table query and full table select is done in remove server.
When simple select with LIMIT clause, lets say, "SELECT * FROM XXX LIMIT 100" out of 1bn records, simple full table select query is executed in remote server. (never finishes!)
Moreover, simple "select count" also results in full table select.
This behaviour is the same with CONNECT engine.
But it's NOT seen in MySQL 5.6. (should be MariaDB specific bug)
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Hi,
It is an 8-year old MySQL bug http://bugs.mysql.com/bug.php?id=19054, and from what I see both in the bug status and in my installation of MySQL 5,6,21, the problem still exists there (I mean for Federated of course, since there is no Connect in MySQL).
It is very easy to see just as described in the verification part of the upstream report, if you enable the general log on the remote side (where the underlying table lives).
Lets say we have
create table t1 (i int) engine=MyISAM;
with some records in it, and on another server
create table tfed (i int) ENGINE=FEDERATED CONNECTION='mysql://<other-server>/test/t1'
Now, if you do
select * from tfed limit 2
you'll see that the remote server receives
SELECT `i` FROM `t1`
No LIMIT clause there at all.
As I said, it works exactly the same way on MySQL 5.6.21. If you see it different, please provide some confirmation of it – logs, show status like 'Handler%' etc.