Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
1.0.12, 1.1.2
-
None
-
AWS server, m4.xlarge. Ubuntu 16.04 launched from AMI ami-2581aa40.
100gb EBS as root volume with 5000 provisioned iops.
-
2018-08
Description
Hello, I'm having trouble with cross-engine join queries. I have a single node with MariaDB column store on it. This one machine has both UM and PM. Followed the very simple install process here: https://mariadb.com/kb/en/library/preparing-for-columnstore-installation-11x/
The install is extremely vanilla. No configuration changes after install.
I loaded some sample data. Lookup tables are innodb, but a large table with bulk data is "columnstore" engine. The intent is to query this columnstore table and join with an innodb lookup table to translate values.
The tables in question are:
CREATE TABLE `keymap` ( |
`keymap_id` int(11) NOT NULL, |
`keymap_group_id` int(11) NOT NULL, |
`key1` varchar(100) NOT NULL, |
`value` varchar(100) DEFAULT NULL, |
PRIMARY KEY (`keymap_id`), |
KEY `Refkeymap_group29` (`keymap_group_id`), |
CONSTRAINT `Refkeymap_group29` FOREIGN KEY (`keymap_group_id`) REFERENCES `keymap_group` (`keymap_group_id`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
 |
CREATE TABLE `study_response_1` ( |
`record_id` int(11) NOT NULL, |
`col1` varchar(1000) DEFAULT NULL, |
`col2` varchar(1000) DEFAULT NULL, |
`col3` varchar(1000) DEFAULT NULL, |
`col4` varchar(1000) DEFAULT NULL |
) ENGINE=Columnstore DEFAULT CHARSET=latin1' |
The keymap table has about 5000 rows. The study_response_1 table has about 7.7 million rows.
If I query only the study_response_1 table by itself, it works just fine:
select col4, count(*) from study_response_1 group by col4; |
|
But if I join keymap and study_response_1, the ExeMgr process dies and forces restart. I get this error message:
select k.value as make, count(*) |
from study_response_1 s |
left join keymap k on k.key1 = s.col25 and k.keymap_group_id = 1 |
group by k.value; |
ERROR 1815 (HY000): Internal error: Lost connection to ExeMgr. Please contact your administrator
I've tried this on both 1.0.12 and 1.1.2, both fresh installs, and the same thing happens. I used mariadb-columnstore-1.0.12-1-xenial.x86_64.deb.tar.gz and mariadb-columnstore-1.1.2-1-xenial.x86_64.deb.tar.gz.
The only difference between the versions is that version 1.0.12 required CrossEngineSupport to be manually set up in Columnstore.xml, but 1.1.2 already had it set up and working. That section of Columnstore.xml is here (default from 1.1.2, I didn't change anything):
<CrossEngineSupport> |
<Host>127.0.0.1</Host> |
<Port>3306</Port> |
<User>root</User> |
<Password/> |
</CrossEngineSupport> |
If I convert the keymap table to ENGINE=columnstore, then the query works just fine. So its clearly only an issue when joining two tables with the different storage engines.
I'm attaching tails of the various logs from /var/log/mariadb/columnstore.
As soon as I execute the query, I see this in warning.log:
Jan 25 21:40:10 s_columnstore@ip-172-31-47-54 messagequeue[8766]: 10.738349 |0|0|0| W 31 CAL0000: MessageQueueClient::write: error writing 2741 bytes to IOSocket: sd: 57 inet: 127.0.0.1 port: 8601. Socket error was InetStreamSocket::write error: Broken pipe -- write from InetStreamSocket: sd: 57 inet: 127.0.0.1 port: 8601
|
I then see this in debug.log:
Jan 25 21:40:10 s_columnstore@ip-172-31-47-54 ExeMgr[10765]: 10.775769 |4|0|0| D 16 CAL0041: Start SQL statement: select k.value as make, count(*) from study_response_1 s left join keymap k on k.key = s.col25 and k.keymap_group_id = 1 group by k.value; |mtab|
|
Jan 25 21:40:10 s_columnstore@ip-172-31-47-54 ProcessMonitor[2442]: 10.894383 |0|0|0| D 18 CAL0000: STOPPING Process: ExeMgr
|
Jan 25 21:40:10 s_columnstore@ip-172-31-47-54 ProcessMonitor[2442]: 10.894468 |0|0|0| D 18 CAL0000: StatusUpdate of Process ExeMgr State = 1 PID = 0
|
Jan 25 21:40:10 s_columnstore@ip-172-31-47-54 ProcMon[2442]: 10.894453 |0|0|0| D 18 CAL0000: Send SET Alarm ID 13 on device ExeMgr
|
Jan 25 21:40:10 s_columnstore@ip-172-31-47-54 ProcessMonitor[2442]: 10.897010 |0|0|0| D 18 CAL0000: statusControl: REQUEST RECEIVED: Set Process pm1/ExeMgr State = AUTO_OFFLINE
|
Jan 25 21:40:10 s_columnstore@ip-172-31-47-54 ProcessMonitor[2442]: 10.897247 |0|0|0| D 18 CAL0000: statusControl: Set Process pm1/ExeMgr State = AUTO_OFFLINE PID = 0
|
All the rest of the log output seems to be related ExeMgr restarting itself.
Is this a bug or a misconfiguration? Any known way to work around this other than converting all my tables to columnstore?
I can provide sample data if needed for reproducing the problem.