You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by "SeaSoonKeun (via GitHub)" <gi...@apache.org> on 2023/02/17 02:43:47 UTC

[GitHub] [drill] SeaSoonKeun opened a new issue, #2758: I encountered some problems when I was using Drill 1.20.2 to connect MySQL database

SeaSoonKeun opened a new issue, #2758:
URL: https://github.com/apache/drill/issues/2758

   Hello everyone, I encountered some problems when I was using Drill 1.20.2 to connect MySQL database. When I execute the following SQL statement, an error occurred:
   
   SQL statement as follows:
   
   ```  SELECT t.name AS apiName, t.param_name AS requestParamName, t.unit_field_name AS unitFieldName FROM (SELECT t1.id AS api_id, t3.api_request_param_id AS id, t1.name AS name, t1.created_by AS created_by, t2.param_name AS param_name, t2.param_type AS param_type, t3.unit_field_name AS unit_field_name FROM mysql11.drillTest.`api_request_param` t2 LEFT JOIN mysql11.drillTest.`api_config` t1 ON t2.api_id = t1.id LEFT JOIN mysql11.drillTest.`api_request_param_unit_assoc` t3 ON t2.id = t3.api_request_param_id) t WHERE t.api_id = '240'```
   Error message as follows:
   
   
   ```  org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query.
     Sql: SELECT `t0`.`name` AS `apiName`, `t0`.`param_name` AS `requestParamName`, `api_request_param_unit_assoc`.`unit_field_name` AS `unitFieldName`
     FROM (SELECT `api_request_param`.`id`, `api_request_param`.`api_id`, `api_request_param`.`param_name`, `api_request_param`.`param_type`, `api_request_param`.`op`, `api_request_param`.`sample`, `api_request_param`.`description`, `api_request_param`.`is_required`, `api_request_param`.`is_public`, `api_request_param`.`create_time`, `api_request_param`.`update_time`, `api_request_param`.`is_deleted`, CAST(`t`.`id` AS SIGNED) AS `id0`, `t`.`name`, `t`.`api_type`, `t`.`request_type`, `t`.`description` AS `description0`, `t`.`return_type`, `t`.`return_limit_num`, `t`.`expire_ttl`, `t`.`is_buffered`, `t`.`buffer_expire_ttl`, `t`.`api_sql`, `t`.`status`, `t`.`created_by`, `t`.`user_id`, `t`.`create_time` AS `create_time0`, `t`.`updated_by`, `t`.`update_time` AS `update_time0`, CAST(`t`.`is_deleted` AS TINYINT) AS `is_deleted0`
     FROM `drillTest`.`api_request_param`
     INNER JOIN (SELECT *
     FROM `drillTest`.`api_config`
     WHERE `id` = '240') AS `t` ON `api_request_param`.`api_id` = `t`.`id`) AS `t0`
     LEFT JOIN `drillTest`.`api_request_param_unit_assoc` ON `t0`.`id` = `api_request_param_unit_assoc`.`api_request_param_id`
     Fragment: 0:0
   
     [Error Id: c1c179d8-f849-4a84-b798-93bf4dfcd159 on master1:31010]```
   
   
   This is my storage related configuration:
   
   
   ```  {
       "type": "jdbc",
       "driver": "com.mysql.jdbc.Driver",
       "url": "jdbc:<mysql://182.44.3.11:31150/drillTest>",
       "username": "xxxx",
       "password": "xxxx",
       "sourceParameters": {
         "keepaliveTime": 0,
         "minimumIdle": 0,
         "idleTimeout": 3600000,
         "maximumPoolSize": 10,
         "maxLifetime": 21600000
       },
     "writerBatchSize": 10000,
     "enabled": true
   }```
   How do we solve this problem? Thank you!
   
   [Slack Message](https://apache-drill.slack.com/archives/CG5CH5571/p1676601412231049)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [drill] SeaSoonKeun commented on issue #2758: I encountered some problems when I was using Drill 1.20.2 to connect MySQL database

Posted by "SeaSoonKeun (via GitHub)" <gi...@apache.org>.
SeaSoonKeun commented on issue #2758:
URL: https://github.com/apache/drill/issues/2758#issuecomment-1434280707

   Does Drill support multi-table left join queries in MySQL? I have found that the corresponding statement of EXPLAIN PLAN FOR cannot be executed in MySQL.
   
   drill query sql:
   
   ```sql
   SELECT t.name AS apiName, t.param_name AS requestParamName, t.unit_field_name AS unitFieldName FROM (SELECT t1.id AS api_id, t3.api_request_param_id AS id, t1.name AS name, t1.created_by AS created_by, t2.param_name AS param_name, t2.param_type AS param_type, t3.unit_field_name AS unit_field_name FROM mysql11.drillTest.`api_request_param` t2 LEFT JOIN mysql11.drillTest.`api_config` t1 ON t2.api_id = t1.id LEFT JOIN mysql11.drillTest.`api_request_param_unit_assoc` t3 ON t2.id = t3.api_request_param_id) t WHERE t.api_id = '240'
   ```
   
   expliain plan for sql:
   ```sql
   SELECT
   	`t0`.`name` AS `apiName`,
   	`t0`.`param_name` AS `requestParamName`,
   	`api_request_param_unit_assoc`.`unit_field_name` AS `unitFieldName` 
   FROM
   	(
   	SELECT
   		`api_request_param`.`id`,
   		`api_request_param`.`api_id`,
   		`api_request_param`.`param_name`,
   		`api_request_param`.`param_type`,
   		`api_request_param`.`op`,
   		`api_request_param`.`sample`,
   		`api_request_param`.`description`,
   		`api_request_param`.`is_required`,
   		`api_request_param`.`is_public`,
   		`api_request_param`.`create_time`,
   		`api_request_param`.`update_time`,
   		`api_request_param`.`is_deleted`,
   		CAST( `t`.`id` AS SIGNED ) AS `id0`,
   		`t`.`name`,
   		`t`.`api_type`,
   		`t`.`request_type`,
   		`t`.`description` AS `description0`,
   		`t`.`return_type`,
   		`t`.`return_limit_num`,
   		`t`.`expire_ttl`,
   		`t`.`is_buffered`,
   		`t`.`buffer_expire_ttl`,
   		`t`.`api_sql`,
   		`t`.`status`,
   		`t`.`created_by`,
   		`t`.`user_id`,
   		`t`.`create_time` AS `create_time0`,
   		`t`.`updated_by`,
   		`t`.`update_time` AS `update_time0`,
   		CAST( `t`.`is_deleted` AS TINYINT ) AS `is_deleted0` 
   	FROM
   		`drillTest`.`api_request_param`
   		INNER JOIN ( SELECT * FROM `drillTest`.`api_config` WHERE `id` = '240' ) AS `t` ON `api_request_param`.`api_id` = `t`.`id` 
   	) AS `t0`
   	LEFT JOIN `drillTest`.`api_request_param_unit_assoc` ON `t0`.`id` = `api_request_param_unit_assoc`.`api_request_param_id`
   ```
   
   mysql execute info:
   ![image](https://user-images.githubusercontent.com/82697297/219589945-0cc3d4fe-0a43-4e4b-9f2f-31b2b79ede85.png)
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@drill.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org