MySQL Bugs: #100511: MYSQL_FIELD length different when MySQL client and server version do not match

Bug #100511	MYSQL_FIELD length different when MySQL client and server version do not match
Submitted:	13 Aug 2020 9:17	Modified:	13 Aug 2020 13:16
Reporter:	Sebastien FLAESCH	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / C	Severity:	S2 (Serious)
Version:	8.0.19	OS:	Debian (10)
Assigned to:		CPU Architecture:	x86
Tags:	MYSQL_FIELD length

Description:
When connecting with a MySQL client 8.0 to a MySQL server 5.7 or 5.6, the length attribute returned in the MYSQL_FIELD column description structure returns a number of characters instead of a number of bytes.

With a UTF-8 database, MySQL client 8.0 + server 8.0, the length attribute for a VARCHAR(25) column is 100 (25 * 4 where 4 is the mbmaxlen)

But with MySQL client 8.0 + server 5.7.19 or 5.6.16, the length is 25 ...

As long as the client and server match, the length is ok (100) ...

I have created this new bug related to https://bugs.mysql.com/bug.php?id=100401, because that other bug report has been treated as a documentation bug.

I am still convinced that something is wrong, see details below.

How to repeat:
I have several MySQL versions installed on my development system, 8.0, 5.7, 5.6.

The issue occurs when connecting with a MySQL client 8.0 to 5.7 server:

My ~/.my.cnf contains:

[client]
default-character-set="utf8mb4"

When I execute:

  show variables like "character_set_database";

it always returns "utf8mb4", no matter the 8.0/5.7 Client/Server combination.

No matter if I set LC_ALL to POSIX or en_US.utf8 (I guess MySQL client does not read C locale settings)

1) Connecting with MySQL client 5.7 to my MySQL 5.7 server:

show variables like "character_set_client";
... returns "utf8mb4"

2) Connecting with MySQL client 8.0 to my MySQL 8.0 server:

show variables like "character_set_client";
... returns "utf8mb4"

All good to me...

3) Connecting with MySQL client 8.0 to my MySQL 5.7 server:

show variables like "character_set_client";
... returns "latin1"

Not expected...

Could you just try to reproduce?

With column info (using MySQL client 8.0):

sf@toro:/opt3/dbs/mys$ mysql test1 --column-type-info --protocol=tcp --host=localhost --port=3308 -u mysuser -p
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 'abc';
Field   1:  `abc`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     3
Max_length: 3
Decimals:   31
Flags:      NOT_NULL 

+-----+
| abc |
+-----+
| abc |
+-----+
1 row in set (0.00 sec)

mysql> show variables like "character_set_client";
Field   1:  `Variable_name`
Catalog:    `def`
Database:   ``
Table:      `session_variables`
Org_table:  `session_variables`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     64
Max_length: 20
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE 

Field   2:  `Value`
Catalog:    `def`
Database:   ``
Table:      `session_variables`
Org_table:  `session_variables`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     1024
Max_length: 6
Decimals:   0
Flags:      

+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| character_set_client | latin1 |
+----------------------+--------+
1 row in set (0.01 sec)

Furthermore:

I have checked what mysql_get_character_set_info() returns in my C program,
with MySQL Client 8.0 connecting to MySQL server 5.7:

In my MY_CHARSET_INFO structure:

- name contains "utf8mb4_0900_ai_ci"
- mbmaxlen contains 4

So far so good, but when executing:

 show variables like 'character_set_client'

from my program, it returns 'latin1' (like with previous mysql command line test).

And mysql_fetch_field() sets the length attribute of MYSQL_FIELD to 3 (bytes), when executing SELECT 'abc' ...

Something is not consistent here... do you agree?

Can you try to repro?

Suggested fix:
When using the combination MySQL Client 8.0 + MySQL Server 5.7, the client database set should always show "utf8mb4", and in the C API, the MYSQL_FIELD.length attribute should return a length of 100 bytes for a VARCHAR(25) column ( 25 * 4 utf8 = 100 bytes)

Hi Mr. FLAESCH,

This bug is a duplicate of the bug #100401.

 Your report in the bug #100401 has been analysed by the experts that are working on the code and it was concluded that it is not a code bug, but documentation bug.

Since this has been noted in our documentation, that bug is closed and so is this one.

Please reconsider this:

I think you don't understand the problem I describe here!

The documentation update is about clarifying the fact that MYSQL_FIELD.length is expressed as a number of bytes and that it depends on the connection/client charset. This doc update is ok and welcome.

However, the real issue is about getting a different MYSQL_FIELD.length when connecting from a MySQL client 8.0 to a MySQL server 5.7.

Please find or setup a MySQL Client 8.0 env and MySQL Server 5.7 server and try to reproduce what I have reported here with simple SQL commands.

What I experience in my C program is following.

For a VARCHAR(25), with client charset defined as utf8mb4:

Client 8.0 => Server 8.0  :   length = 100 (25 * 4)   => OK
Client 5.7 => Server 5.7  :   length = 100 (25 * 4)   => OK
Client 8.0 => Server 5.7  :   length =  25 (25 * 1)   => NOT OK

This behavior IS NOT described in the documentation.

I suspect that something is wrong because in the 8.0/5.7 config, the C API returns as expected a utf8mb4 charset in MY_CHARSET_INFO from mysql_get_character_set_info(), but MYSQL_FIELD.length is 25 bytes instead of 100 bytes for a VARCHAR(25) column. This is NOT consistent.

Please just try with mysql command interpreter what I have reported here.