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: [email protected] With regards, Apache Git Services
