Details
-
Bug
-
Status: Open
-
Critical
-
Resolution: Unresolved
-
1.7.0, 1.8.0
-
None
Description
I am using
MySQL Version: 5.7.8 Drill Version: 1.7 and 1.8 MySQL JDBC Connector: mysql-connector-java-5.1.39-bin.jar
I have following 2 tables created using:
CREATE DATABASE IF NOT EXISTS mydb; USE `mydb`; CREATE TABLE `customer` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(100) DEFAULT NULL, PRIMARY KEY (`Id`), KEY `fk_` (`Id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; CREATE TABLE `customercontact` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `CustomerId` int(11) DEFAULT NULL, `ContactValue` varchar(100) DEFAULT NULL, PRIMARY KEY (`Id`), KEY `fk_cust_idx` (`CustomerId`), CONSTRAINT `fk_cust` FOREIGN KEY (`CustomerId`) REFERENCES `customer` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
I have created a view on MySQL using the following query:
create or replace view viewcustomercontact as
select
`c`.`id` as `cusomertid`,
`contact`.`contactvalue` as `contactvalue`
from
mydb.customer as c
left join (
select
`cc`.`CustomerId` as `CustomerId`,
group_concat(`cc`.`ContactValue`,',') as `ContactValue`
from
mydb.customerContact as cc
group by `cc`.`CustomerId`) as contact
on `c`.`Id` = `Contact`.`CustomerId`;
In Drill I have created a storage plugin named mysql with following configuration:
{ "type": "jdbc", "driver": "com.mysql.jdbc.Driver", "url": "jdbc:mysql://localhost:3306", "username": "xxxx", "password": "xxxx", "enabled": true }
When I issue the following query from sqlline.bat on windows:
select * from mysql.`mydb`.`viewcustomercontact `;
I get the following error:
Error: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query. sql SELECT * FROM mydb.viewcustomercontact plugin mysql Fragment 0:0