[MCOL-169] Properly configured <CrossEngineSupport> in Calpont.xml still results in error on cross engine join Created: 2016-06-22  Updated: 2016-11-29  Resolved: 2016-11-29

Status: Closed
Project: MariaDB ColumnStore
Component/s: Documentation
Affects Version/s: None
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: Justin Swanhart (Inactive) Assignee: David Thompson (Inactive)
Resolution: Fixed Votes: 0
Labels: None


 Description   

<CrossEngineSupport>
<Host>192.168.1.144</Host>
<Port>3306</Port>
<User>root</User>
<Password/>
</CrossEngineSupport>

MariaDB [test]> select fact.i1, fact_myisam.i1 from fact join fact_myisam on fact.seq = fact_myisam.seq where fact.i1 != fact_myisam.i1;
ERROR 1815 (HY000): Internal error: IDB-2034: At least one DBRoot required for that query is offline.

CREATE TABLE `fact` (
`seq` bigint(20) DEFAULT NULL,
`i1` tinyint(4) DEFAULT NULL,
`i2` smallint(6) DEFAULT NULL,
`i4` int(11) DEFAULT NULL,
`i8` bigint(20) DEFAULT NULL,
`i1u` tinyint(3) unsigned DEFAULT NULL,
`i2u` smallint(5) unsigned DEFAULT NULL,
`i4u` int(10) unsigned DEFAULT NULL,
`i8u` bigint(20) unsigned DEFAULT NULL,
`d5` decimal(15,5) DEFAULT NULL,
`f1` float DEFAULT NULL,
`f2` float(2,1) DEFAULT NULL,
`f23` float(23,4) DEFAULT NULL,
`f24` float(24,10) DEFAULT NULL,
`f53` float(53,20) DEFAULT NULL,
`p1` double DEFAULT NULL,
`p2` double(2,1) DEFAULT NULL,
`p23` double(23,4) DEFAULT NULL,
`p24` double(24,10) DEFAULT NULL,
`p53` double(53,20) DEFAULT NULL,
`r1` double DEFAULT NULL,
`d` date DEFAULT NULL,
`dt` datetime DEFAULT NULL,
`c1` char(1) DEFAULT NULL,
`c2` char(255) DEFAULT NULL,
`vc1` varchar(1) DEFAULT NULL,
`vc2` varchar(255) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=latin1;

CREATE TABLE `fact_myisam` (
`seq` bigint(20) DEFAULT NULL,
`i1` tinyint(4) DEFAULT NULL,
`i2` smallint(6) DEFAULT NULL,
`i4` int(11) DEFAULT NULL,
`i8` bigint(20) DEFAULT NULL,
`i1u` tinyint(3) unsigned DEFAULT NULL,
`i2u` smallint(5) unsigned DEFAULT NULL,
`i4u` int(10) unsigned DEFAULT NULL,
`i8u` bigint(20) unsigned DEFAULT NULL,
`d5` decimal(15,5) DEFAULT NULL,
`f1` float DEFAULT NULL,
`f2` float(2,1) DEFAULT NULL,
`f23` float(23,4) DEFAULT NULL,
`f24` float(24,10) DEFAULT NULL,
`f53` float(53,20) DEFAULT NULL,
`p1` double DEFAULT NULL,
`p2` double(2,1) DEFAULT NULL,
`p23` double(23,4) DEFAULT NULL,
`p24` double(24,10) DEFAULT NULL,
`p53` double(53,20) DEFAULT NULL,
`r1` double DEFAULT NULL,
`d` date DEFAULT NULL,
`dt` datetime DEFAULT NULL,
`c1` char(1) DEFAULT NULL,
`c2` char(255) DEFAULT NULL,
`vc1` varchar(1) DEFAULT NULL,
`vc2` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

 Comments   
Comment by Justin Swanhart (Inactive) [ 2016-06-22 ]

Actually this seems to be side effect of:
sudo service restart columnstore
sudo service restart columnstop-MySQL

Comment by Justin Swanhart (Inactive) [ 2016-06-22 ]

I created user 'ce@localhost' with password 'ce'.
I configured CrossEngineJoins to use user ce and password ce
I did:
`service stop columnstore`
`service stop mysql-Columnstore`
`service start columnstore`
`service start mysql-Columstore'

Error is now:

MariaDB [test]> select fact.i1, fact_myisam.i1 from fact join fact_myisam on fact.seq = fact_myisam.seq where fact.i1 != fact_myisam.i1;
ERROR 1815 (HY000): Internal error: fatal error executing query in crossengine client lib(17)(17)

Comment by David Thompson (Inactive) [ 2016-10-03 ]

I can reproduce this by configuring cross engine joins to use a minimal privilege user ce that only has access to the table to be queried. Looking in /var/log/mariadb/columnstore/info.log there are some clues:
Oct 3 11:48:35 centos7 joblist[123504]: 35.199821 |0|0|0| I 05 CAL0000: QUERY to foreign engine: SELECT seq, i1 FROM lookup
Oct 3 11:48:35 centos7 joblist[123504]: 35.200220 |4|0|0| C 05 CAL0000: fatal error executing query in crossengine client lib(17)(17)

Now when i try to query the table as user ce it returns an error:
MariaDB [test]> SELECT seq, i1 FROM lookup;
ERROR 1044 (42000): Access denied for user 'ce'@'127.0.0.1' to database 'infinidb_vtable'

granting privileges to ce to have access to this db resolves the issue:
grant all privileges on infinidb_vtable.* to ce@127.0.0.1;

Some more time should be spent documenting what columnstore is doing here and what are the minimal privilege grants should the remote user not be privileged.

Comment by David Thompson (Inactive) [ 2016-11-29 ]

The documentation has been revised. Also the stated error indicates a system issue most likely because a node was brought down and so the data cannot be retrieved from that node until it is restored or the dbroot is moved to an active pm node.

Generated at Thu Feb 08 02:19:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.