Bug #105309 client binary protocol MYSQL_TYPE_BIT can't sent parameter
Submitted: 23 Oct 2021 13:42 Modified: 28 Oct 2021 15:28
Reporter: Mark Simonyi Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / C Severity:S3 (Non-critical)
Version:8.0.27 OS:MacOS (11.4)
Assigned to: CPU Architecture:x86

[23 Oct 2021 13:42] Mark Simonyi
Description:
I use client binary protocol sent MYSQL_TYPE_BIT parameter , bug server response  "Incorrect arguments to mysqld_stmt_execute". 
sql: UPDATE mysql_types AS t SET t.my_bit64 = ? WHERE t.id = ? .
no support query attribute.
below execute packet hex :
1A000000170300000000010000000001100008000100E300000000000000

How to repeat:
use client binary protocol sent MYSQL_TYPE_BIT parameter.

Suggested fix:
fix it
[25 Oct 2021 10:46] MySQL Verification Team
Hi,

Not sure what connector are you using?
As you can see libmysqlclient supports it properly:

master [localhost:21627] {msandbox} (test) > create table t1 (id int, somebits BIT(10));
Query OK, 0 rows affected (0.01 sec)

master [localhost:21627] {msandbox} (test) > insert into t1 (id, somebits) values (1,b'1010111001');
Query OK, 1 row affected (0.01 sec)

master [localhost:21627] {msandbox} (test) > select id, somebits, bin(somebits) from t1;
+------+--------------------+---------------+
| id   | somebits           | bin(somebits) |
+------+--------------------+---------------+
|    1 | 0x02B9             | 1010111001    |
+------+--------------------+---------------+
1 row in set (0.00 sec)

master [localhost:21627] {msandbox} (test) > prepare xx from 'update t1 as t set t.somebits = ? where t.id = 1';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

master [localhost:21627] {msandbox} (test) > set @b = b'1111100111';
Query OK, 0 rows affected (0.00 sec)

master [localhost:21627] {msandbox} (test) > execute xx using @b;
Query OK, 1 row affected (0.18 sec)
Rows matched: 1  Changed: 1  Warnings: 0

master [localhost:21627] {msandbox} (test) > select id, somebits, bin(somebits) from t1;
+------+--------------------+---------------+
| id   | somebits           | bin(somebits) |
+------+--------------------+---------------+
|    1 | 0x03E7             | 1111100111    |
+------+--------------------+---------------+
1 row in set (0.00 sec)

master [localhost:21627] {msandbox} (test) >

Can we get reproducible test case?
thanks
[27 Oct 2021 13:14] Mark Simonyi
1. I don' use any connector from MySQL official website.
2. I am developing a reactive mysql driver.
3. You do like below:

master [localhost:21627] {msandbox} (test) > prepare xx from 'update t1 as t set t.somebits = ? where t.id = 1';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

master [localhost:21627] {msandbox} (test) > set @b = b'1111100111';
Query OK, 0 rows affected (0.00 sec)

master [localhost:21627] {msandbox} (test) > execute xx using @b;
Query OK, 1 row affected (0.18 sec)
Rows matched: 1  Changed: 1  Warnings: 0.

then You still use ComQuery text protocol not COM_STMT_PREPARE binary protocol.

If you want reproducible test case,you only write a simple client or review MySQL server code.

I follow https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_binary_resultset.html#sect... to set MYSQL_TYPE_BIT type parameter ,get a error "Incorrect arguments to mysqld_stmt_execute" .
[27 Oct 2021 13:54] MySQL Verification Team
Hi Mark,

you are asking me to verify a but in your code based on error message.

All our supported connectors will pass the bit so there is obviously way to do it. If you can write a small C example where you experience error I can try to see where the problem is (if we documented it falsely or your code is not ok), but based on error I can't say anything.
[27 Oct 2021 22:06] Mark Simonyi
ok ,I again comment after reactive mysql driver publisher. but I think that easy to verify ,eg: review MySQL server code,follow binary protocol. By the way I believe MySQL team will like my MySQL reactive driver.
[27 Oct 2021 22:14] Mark Simonyi
You said "All our supported connectors will pass the bit so there is obviously way to do it." No connector/j mapping bit as boolean,this is error,bit(64) couldn't mapping to boolean,and connector/j never send MySQL MYSQL_TYPE_BIT in PreparedStatement ,it just convert bit(boolean) as int,and  send int as MYSQL_TYPE_LONG,You can review connector/j code,at com/mysql/cj/AbstractQueryBindings.java:761.
[28 Oct 2021 15:28] MySQL Verification Team
Hi,

does not make much sense to me but I'll pass this to the connector team, they might be able to help you out.