You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/02/12 05:26:16 UTC
[GitHub] [incubator-shardingsphere] Arnow opened a new issue #4255: use CAPI
in stmt mode ,
mysql_stmt_prepare and mysql_stmt_execute return incorrect response
Arnow opened a new issue #4255: use CAPI in stmt mode ,mysql_stmt_prepare and mysql_stmt_execute return incorrect response
URL: https://github.com/apache/incubator-shardingsphere/issues/4255
## Bug Report
### Which version of ShardingSphere did you use?
4.0.0
### Which project did you use? Sharding-JDBC or Sharding-Proxy?
apache-shardingsphere-incubating-4.0.0-sharding-proxy-bin
### Expected behavior
### Actual behavior
two questions :
Question 1:
use CAPI in stmt mode, mysql_stmt_prepare get incorrect response according to mysql protocol, packages of column definition information is lost
Question 2:
Also use CAPI in stmt mode, mysql_stmt_bind_result return error, msg is "Using unsupported buffer type: 15 (parameter: 1)"
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
sharding rule configuration:
```
schemaName: sbtest
#
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:8905/sbtest?serverTimezone=UTC&useSSL=false
username: test
password: test
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
ds_1:
url: jdbc:mysql://127.0.0.1:8906/sbtest?serverTimezone=UTC&useSSL=false
username: test
password: test
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
shardingRule:
tables:
sbtest1:
actualDataNodes: ds_${0..1}.customer
keyGenerator:
type: SNOWFLAKE
column: id
sbtest2:
actualDataNodes: ds_${0..1}.order_line
keyGenerator:
type: SNOWFLAKE
column: id
bindingTables:
- sbtest1,sbtest2
defaultDatabaseStrategy:
inline:
shardingColumn: id
algorithmExpression: ds_${id % 2}
defaultTableStrategy:
none:
```
create table:
```
CREATE TABLE `customer` (
`aid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`c_id` int(11) NOT NULL,
`c_d_id` tinyint(4) NOT NULL,
`c_w_id` smallint(6) NOT NULL,
`c_first` varchar(16) NOT NULL,
`c_middle` char(2) NOT NULL,
`c_last` varchar(16) NOT NULL,
`c_street_1` varchar(20) NOT NULL,
`c_street_2` varchar(20) NOT NULL,
`c_city` varchar(20) NOT NULL,
`c_state` char(2) NOT NULL,
`c_zip` char(9) NOT NULL,
`c_phone` char(16) NOT NULL,
`c_since` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`c_credit` char(2) NOT NULL,
`c_credit_lim` bigint(20) NOT NULL,
`c_discount` decimal(4,2) NOT NULL,
`c_balance` decimal(12,2) NOT NULL,
`c_ytd_payment` decimal(12,2) NOT NULL,
`c_payment_cnt` smallint(6) NOT NULL,
`c_delivery_cnt` smallint(6) NOT NULL,
`c_data` text NOT NULL,
PRIMARY KEY (`aid`),
UNIQUE KEY `c_w_id` (`c_w_id`,`c_d_id`,`c_id`),
KEY `idx_customer` (`c_w_id`,`c_d_id`,`c_last`,`c_first`)
) ENGINE=InnoDB AUTO_INCREMENT=432069856137838593 DEFAULT CHARSET=utf8mb4
```
execute sql:
```
SELECT c_last FROM customer WHERE c_w_id = ?;
```
Pseudocode:
```
#define SELECT_QUERY "SELECT c_last FROM customer WHERE c_w_id = ?"
stmt = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt, SELECT_QUERY, strlen(SELECT_QUERY)); // questions 1
mysql_stmt_bind_param(stmt, param);
mysql_stmt_execute(stmt);
column.buffer_type = MYSQL_TYPE_STRING;
column.buffer = c_last;
column.buffer_length = sizeof(c_last);
column.length= &length;
column.error= &error;
column.is_null= &is_null;
mysql_stmt_bind_result(stmt, column); // question 2: return error
```
### Reason analyze (If you can)
Question 1:
I use some way to capture packets, here is the response packages return from two sources: connect to sharding proxy and connect to mysql
mysql_stmt_prepare response from sharding proxy:
```
Source [127.0.0.1:54992] -> Dest [127.0.0.1:5059]:
[0000] 4b 00 00 00 16 53 45 4c 45 43 54 20 63 5f 6c 61 K....SELECT c_la
[0016] 73 74 20 20 46 52 4f 4d 20 63 75 73 74 6f 6d 65 st FROM custome
[0032] 72 20 57 48 45 52 45 20 63 5f 77 5f 69 64 20 3d r WHERE c_w_id =
[0048] 20 3f 20 20 41 4e 44 20 63 5f 64 5f 69 64 20 3d ? AND c_d_id =
[0064] 20 3f 20 41 4e 44 20 63 5f 69 64 20 3d 20 3f ? AND c_id = ?
2020-02-10 10:38:24.386146 - Read >>>
Source [127.0.0.1:54992] <- Dest [127.0.0.1:5059]:
[0000] 0c 00 00 01 00 34 00 00 00 00 00 03 00 00 00 00 .....4..........
2020-02-10 10:38:24.386185 - Read >>>
Source [127.0.0.1:54992] <- Dest [127.0.0.1:5059]:
[0000] 22 00 00 02 03 64 65 66 04 74 70 63 63 08 63 75 "....def.tpcc.cu
[0016] 73 74 6f 6d 65 72 00 00 00 0c 21 00 64 00 00 00 stomer....!.d...
[0032] 0f 00 00 00 00 00 ......
2020-02-10 10:38:24.386193 - Read >>>
Source [127.0.0.1:54992] <- Dest [127.0.0.1:5059]:
[0000] 22 00 00 03 03 64 65 66 04 74 70 63 63 08 63 75 "....def.tpcc.cu
[0016] 73 74 6f 6d 65 72 00 00 00 0c 21 00 64 00 00 00 stomer....!.d...
[0032] 0f 00 00 00 00 00 ......
2020-02-10 10:38:24.386201 - Read >>>
Source [127.0.0.1:54992] <- Dest [127.0.0.1:5059]:
[0000] 22 00 00 04 03 64 65 66 04 74 70 63 63 08 63 75 "....def.tpcc.cu
[0016] 73 74 6f 6d 65 72 00 00 00 0c 21 00 64 00 00 00 stomer....!.d...
[0032] 0f 00 00 00 00 00 ......
2020-02-10 10:38:24.386207 - Read >>>
Source [127.0.0.1:54992] <- Dest [127.0.0.1:5059]:
[0000] 05 00 00 05 fe 00 00 02 00 .........
```
mysql_stmt_prepare response from mysql:
```
Source [127.0.0.1:54992] -> Dest [127.0.0.1:5059]:
[0000] 4b 00 00 00 16 53 45 4c 45 43 54 20 63 5f 6c 61 K....SELECT c_la
[0016] 73 74 20 20 46 52 4f 4d 20 63 75 73 74 6f 6d 65 st FROM custome
[0032] 72 20 57 48 45 52 45 20 63 5f 77 5f 69 64 20 3d r WHERE c_w_id =
[0048] 20 3f 20 20 41 4e 44 20 63 5f 64 5f 69 64 20 3d ? AND c_d_id =
[0064] 20 3f 20 41 4e 44 20 63 5f 69 64 20 3d 20 3f ? AND c_id = ?
2020-02-10 11:11:34.349068 - Read >>>
Source [127.0.0.1:36438] <- Dest [127.0.0.1:8901]:
[0000] 0c 00 00 01 00 01 00 00 00 01 00 03 00 00 00 00 ................
2020-02-10 11:11:34.349116 - Read >>>
Source [127.0.0.1:36438] <- Dest [127.0.0.1:8901]:
[0000] 17 00 00 02 03 64 65 66 00 00 00 01 3f 00 0c 3f .....def....?..?
[0016] 00 00 00 00 00 fd 80 00 00 00 00 ...........
2020-02-10 11:11:34.349132 - Read >>>
Source [127.0.0.1:36438] <- Dest [127.0.0.1:8901]:
[0000] 17 00 00 03 03 64 65 66 00 00 00 01 3f 00 0c 3f .....def....?..?
[0016] 00 00 00 00 00 fd 80 00 00 00 00 ...........
2020-02-10 11:11:34.349147 - Read >>>
Source [127.0.0.1:36438] <- Dest [127.0.0.1:8901]:
[0000] 17 00 00 04 03 64 65 66 00 00 00 01 3f 00 0c 3f .....def....?..?
[0016] 00 00 00 00 00 fd 80 00 00 00 00 ...........
2020-02-10 11:11:34.349159 - Read >>>
Source [127.0.0.1:36438] <- Dest [127.0.0.1:8901]:
[0000] 05 00 00 05 fe 00 00 02 00 .........
2020-02-10 11:11:34.349167 - Read >>>**（this piece of packages lost in sharding proxy）**
Source [127.0.0.1:36438] <- Dest [127.0.0.1:8901]:
[0000] 36 00 00 06 03 64 65 66 04 74 70 63 63 08 63 75 6....def.tpcc.cu
[0016] 73 74 6f 6d 65 72 08 63 75 73 74 6f 6d 65 72 06 stomer.customer.
[0032] 63 5f 6c 61 73 74 06 63 5f 6c 61 73 74 0c 08 00 c_last.c_last...
[0048] 10 00 00 00 fd 01 50 00 00 00 ......P...
2020-02-10 11:11:34.349198 - Read >>>
Source [127.0.0.1:36438] <- Dest [127.0.0.1:8901]:
[0000] 05 00 00 07 fe 00 00 02 00 .........
```
according to above packages , sharding proxy lost column defination information, just return param definitions .
Question 2:
column type in response from sharding proxy to mysql_stmt_execute is incorrect,
column 'c_last' is char,it should return `MYSQL_TYPE_VAR_STRING` （0xfd）, but returns `MYSQL_TYPE_VARCHAR`(0x0f).
column type will stored in struct `MYSQL_STMT` (fields.type) ,then call mysql_stmt_bind_result will get error ,because MYSQL_TYPE_VARCHAR is not allowed to bind result.
the same to column type varchar.
captured packages:
mysql_stmt_execute response from sharding proxy:
```
2020-02-10 10:38:24.393292 - Read >>>
Source [127.0.0.1:54992] <- Dest [127.0.0.1:5059]:
[0000] 01 00 00 01 01 .....
2020-02-10 10:38:24.393333 - Read >>>
Source [127.0.0.1:54992] <- Dest [127.0.0.1:5059]:
[0000] 36 00 00 02 03 64 65 66 04 74 70 63 63 08 63 75 6....def.tpcc.cu
[0016] 73 74 6f 6d 65 72 08 63 75 73 74 6f 6d 65 72 06 stomer.customer.
[0032] 63 5f 6c 61 73 74 06 63 5f 6c 61 73 74 0c 21 00 c_last.c_last.!.
[0048] 10 00 00 00 **{0f: this byte is column type}** 00 00 00 00 00 ..........
2020-02-10 10:38:24.393344 - Read >>>
Source [127.0.0.1:54992] <- Dest [127.0.0.1:5059]:
[0000] 05 00 00 03 fe 00 00 02 00 .........
2020-02-10 10:38:24.393349 - Read >>>
Source [127.0.0.1:54992] <- Dest [127.0.0.1:5059]:
[0000] 0c 00 00 04 00 00 09 42 41 52 42 41 52 42 41 52 .......BARBARBAR
2020-02-10 10:38:24.393353 - Read >>>
Source [127.0.0.1:54992] <- Dest [127.0.0.1:5059]:
[0000] 05 00 00 05 fe 00 00 02 00 .........
```
mysql_stmt_execute response from mysql:
```
2020-02-10 11:11:34.351845 - Read >>>
Source [127.0.0.1:36438] <- Dest [127.0.0.1:8901]:
[0000] 01 00 00 01 01 .....
2020-02-10 11:11:34.351876 - Read >>>
Source [127.0.0.1:36438] <- Dest [127.0.0.1:8901]:
[0000] 36 00 00 02 03 64 65 66 04 74 70 63 63 08 63 75 6....def.tpcc.cu
[0016] 73 74 6f 6d 65 72 08 63 75 73 74 6f 6d 65 72 06 stomer.customer.
[0032] 63 5f 6c 61 73 74 06 63 5f 6c 61 73 74 0c 08 00 c_last.c_last...
[0048] 10 00 00 00 **{fd : this byte is column type}** 01 50 00 00 00 ......P...
2020-02-10 11:11:34.351890 - Read >>>
Source [127.0.0.1:36438] <- Dest [127.0.0.1:8901]:
[0000] 05 00 00 03 fe 00 00 02 00 .........
2020-02-10 11:11:34.351896 - Read >>>
Source [127.0.0.1:36438] <- Dest [127.0.0.1:8901]:
[0000] 0c 00 00 04 00 00 09 42 41 52 42 41 52 42 41 52 .......BARBARBAR
2020-02-10 11:11:34.351901 - Read >>>
Source [127.0.0.1:36438] <- Dest [127.0.0.1:8901]:
[0000] 05 00 00 05 fe 00 00 02 00 .........
```
### Example codes for reproduce this issue (such as a github link).
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] tuohai666 commented on issue #4255: use
CAPI in stmt mode ,
mysql_stmt_prepare and mysql_stmt_execute return incorrect response
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #4255: use CAPI in stmt mode ,mysql_stmt_prepare and mysql_stmt_execute return incorrect response
URL: https://github.com/apache/incubator-shardingsphere/issues/4255#issuecomment-591226070
OK.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] Arnow commented on issue #4255: use CAPI
in stmt mode ,
mysql_stmt_prepare and mysql_stmt_execute return incorrect response
Posted by GitBox <gi...@apache.org>.
Arnow commented on issue #4255: use CAPI in stmt mode ,mysql_stmt_prepare and mysql_stmt_execute return incorrect response
URL: https://github.com/apache/incubator-shardingsphere/issues/4255#issuecomment-590863483
em, either does MYSQL_TYPE_BLOB
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] Arnow commented on issue #4255: use CAPI
in stmt mode ,
mysql_stmt_prepare and mysql_stmt_execute return incorrect response
Posted by GitBox <gi...@apache.org>.
Arnow commented on issue #4255: use CAPI in stmt mode ,mysql_stmt_prepare and mysql_stmt_execute return incorrect response
URL: https://github.com/apache/incubator-shardingsphere/issues/4255#issuecomment-590860176
hi， I think it's same problem with MYSQL_TYPE_TIMESTAMP in mysql_bind_result() , you can test it .
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] Arnow commented on issue #4255: use CAPI
in stmt mode ,
mysql_stmt_prepare and mysql_stmt_execute return incorrect response
Posted by GitBox <gi...@apache.org>.
Arnow commented on issue #4255: use CAPI in stmt mode ,mysql_stmt_prepare and mysql_stmt_execute return incorrect response
URL: https://github.com/apache/incubator-shardingsphere/issues/4255#issuecomment-586590649
> Perfect analysis. Would you like to fix these problems?
Thank you for your reply,but im not good at java ...
I have not tried Connector/C or Connector/C++, but I think it's probably the same problem.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] tuohai666 commented on issue #4255: use
CAPI in stmt mode ,
mysql_stmt_prepare and mysql_stmt_execute return incorrect response
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #4255: use CAPI in stmt mode ,mysql_stmt_prepare and mysql_stmt_execute return incorrect response
URL: https://github.com/apache/incubator-shardingsphere/issues/4255#issuecomment-586262711
Perfect analysis. Would you like to fix these problems?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu closed issue #4255: use CAPI
in stmt mode ,
mysql_stmt_prepare and mysql_stmt_execute return incorrect response
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #4255: use CAPI in stmt mode ,mysql_stmt_prepare and mysql_stmt_execute return incorrect response
URL: https://github.com/apache/incubator-shardingsphere/issues/4255
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] tuohai666 commented on issue #4255: use
CAPI in stmt mode ,
mysql_stmt_prepare and mysql_stmt_execute return incorrect response
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #4255: use CAPI in stmt mode ,mysql_stmt_prepare and mysql_stmt_execute return incorrect response
URL: https://github.com/apache/incubator-shardingsphere/issues/4255#issuecomment-586323032
BTW, is there same problem when use Connector/C or Connector/C++?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] Arnow edited a comment on issue #4255:
use CAPI in stmt mode ,
mysql_stmt_prepare and mysql_stmt_execute return incorrect response
Posted by GitBox <gi...@apache.org>.
Arnow edited a comment on issue #4255: use CAPI in stmt mode ,mysql_stmt_prepare and mysql_stmt_execute return incorrect response
URL: https://github.com/apache/incubator-shardingsphere/issues/4255#issuecomment-590863483
em, neither does MYSQL_TYPE_BLOB
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] tuohai666 commented on issue #4255: use
CAPI in stmt mode ,
mysql_stmt_prepare and mysql_stmt_execute return incorrect response
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #4255: use CAPI in stmt mode ,mysql_stmt_prepare and mysql_stmt_execute return incorrect response
URL: https://github.com/apache/incubator-shardingsphere/issues/4255#issuecomment-586802659
OK. I'll resolve this issue in a few days.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services