| Bug #115889 | MySQL Command Line Client showing incorrect results | ||
|---|---|---|---|
| Submitted: | 22 Aug 2024 4:40 | Modified: | 26 Aug 2024 10:14 |
| Reporter: | Yoseph Phillips | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Command-line Clients | Severity: | S3 (Non-critical) |
| Version: | 8.0.39 | OS: | Windows (Windows 11 Pro) |
| Assigned to: | CPU Architecture: | Any | |
[22 Aug 2024 5:45]
Yoseph Phillips
I have narrowed down the issue: DROP FUNCTION IF EXISTS foo; DELIMITER $$ CREATE FUNCTION foo() RETURNS VARCHAR(16) DETERMINISTIC BEGIN RETURN '1 µs'; END $$ DELIMITER ; SELECT foo() AS 'actual', '1 µs' AS 'expected'; DROP FUNCTION IF EXISTS foo; It doesn't like the 'µ' when it is returned from a function using the MySQL Command Line client.
[22 Aug 2024 7:06]
Daniël van Eeden
You *might* want to use a builtin function: https://dev.mysql.com/doc/refman/8.4/en/performance-schema-functions.html#function_format-... This *might* be related to the terminal/prompt application you're using and if it has full Unicode support.
[22 Aug 2024 7:19]
Yoseph Phillips
FORMAT_PICO_TIME(...) is helpful for us with clients using more recent versions of MySQL, however we still have some clients using older versions of MySQL that don't have this function. This build in function returns us instead of µs - us is also a workaround but doesn't solve the issue. The terminal / prompt does support full Unicode support as the test result shows: mysql> SELECT foo() AS 'actual', '1 µs' AS 'expected'; +--------+----------+ | actual | expected | +--------+----------+ | 1 | 1 µs | +--------+----------+
[22 Aug 2024 10:25]
MySQL Verification Team
Hi Mr. Phillips, Thank you for your bug report. However, the bug is not in our command-line clients. A bug is in the terminal that you are actually using. We are using iTerm and it returns the results very correctly: actual expected 1 µs 1 µs We do not know which terminal you are using, but you can try using a terminal within the Visual Studio Code, for example ...... Can't repeat.
[22 Aug 2024 10:28]
MySQL Verification Team
Hi,
To confirm our findings, we ran your original, big test case.
This is the output:
*************************** 1. row ***************************
System Query: DROP FUNCTION IF EXISTS `formatTime`
Count: 1
Total of Query Times: 140.99 ms
Minimum Query Time: 140.99 ms
Average Query Time: 140.99 ms
Maximum Query Time: 140.99 ms
Total of Lock Times: 9.00 µs
Total Errors: 0
Total Warnings: 1
Total Rows Affected: 0
Total Rows Sent: 0
Total Rows Examined: 0
Total Created Temporary Disk Tables: 0
Total Created Temporary Tables: 0
Total CPU Time: 0 ps
Maximum Controlled Memory: 39,296
Maximum Total Memory: 368,598
First Seen Date & Time: 22/08/2024 13:26
Last Seen Date & Time: 22/08/2024 13:26
Sample Query: DROP FUNCTION IF EXISTS formatTime
*************************** 2. row ***************************
System Query: CREATE FUNCTION `formatTime` ( `picoseconds` INT8 ) RETURNS VARCHARACTER (?) DETERMINISTIC BEGIN RETURN CASE WHEN `picoseconds` >= ? THEN `time_format` ( `sec_to_time` ( `picoseconds` / ? ) , ? ) WHEN `picoseconds` >= ? THEN `concat` ( `round` ( `picoseconds` / ?, ... ) , ? ) WHEN `picoseconds` >= ? THEN `concat` ( `round` ( `picoseconds` / ?, ... ) , ? ) WHEN `picoseconds` >= ? THEN `concat` ( `round` ( `picoseconds` / ?, ... ) , ? ) WHEN `picoseconds` >= ? THEN `concat` ( `round` ( `picoseconds` / ?, ... ) , ? ) ELSE `concat` ( `picoseconds` , ? ) END ; END
Count: 1
Total of Query Times: 2.02 ms
Minimum Query Time: 2.02 ms
Average Query Time: 2.02 ms
Maximum Query Time: 2.02 ms
Total of Lock Times: 3.00 µs
Total Errors: 0
Total Warnings: 0
Total Rows Affected: 0
Total Rows Sent: 0
Total Rows Examined: 0
Total Created Temporary Disk Tables: 0
Total Created Temporary Tables: 0
Total CPU Time: 0 ps
Maximum Controlled Memory: 18,720
Maximum Total Memory: 430,476
First Seen Date & Time: 22/08/2024 13:26
Last Seen Date & Time: 22/08/2024 13:26
Sample Query: CREATE FUNCTION formatTime(picoseconds BIGINT) RETURNS VARCHAR(16) DETERMINISTIC
BEGIN
RETURN
CASE
WHEN picoseconds >= 60000000000000 THEN time_format(sec_to_time(picoseconds / 1000000000000), '%H:%i:%s')
WHEN picoseconds >= 1000000000000 THEN concat(round(picoseconds / 1000000000000, 2), ' s')
WHEN picoseconds >= 1000000000 THEN concat(round(picoseconds / 1000000000, 2), ' ms')
WHEN picoseconds >= 1000000 THEN concat(round(picoseconds / 1000000, 2), ' µs')
WHEN picoseconds >= 1000 THEN concat(round(picoseconds / 1000, 2), ' ns')
ELSE concat(picoseconds, ' ps')
END;
END
*************************** 3. row ***************************
System Query: SELECT @@`version_comment` LIMIT ?
Count: 1
Total of Query Times: 143.00 µs
Minimum Query Time: 143.00 µs
Average Query Time: 143.00 µs
Maximum Query Time: 143.00 µs
Total of Lock Times: 0 ps
Total Errors: 0
Total Warnings: 0
Total Rows Affected: 0
Total Rows Sent: 1
Total Rows Examined: 1
Total Created Temporary Disk Tables: 0
Total Created Temporary Tables: 0
Total CPU Time: 0 ps
Maximum Controlled Memory: 16,720
Maximum Total Memory: 72,080
First Seen Date & Time: 22/08/2024 13:26
Last Seen Date & Time: 22/08/2024 13:26
Sample Query: select @@version_comment limit 1
-----------------------------------------------------
Can't repeat ........
[23 Aug 2024 0:12]
Yoseph Phillips
Hi, As written in the description it works for us using some clients including MySQL Workbench. It does not work when using the Windows Command prompt (which is why it is so strange). * Use a File Explorer to navigate to your bin directory (for example: C:\Program Files\MySQL\MySQL Server 8.0\bin) * Type cmd into the location selector and press enter * It will show you a screen with: Microsoft Windows [Version 10.0.22631.4037] (c) Microsoft Corporation. All rights reserved. C:\Program Files\MySQL\MySQL Server 8.0\bin> * Enter mysql -u... -p * Run the simplified test case
[23 Aug 2024 1:23]
Yoseph Phillips
Some more information for you:
Even using SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci; in both working and non working clients doesn't work.
Using SHOW CREATE FUNCTION foo; from both working and non working clients shows that the RETURN statement is getting messed up:
CREATE in non working client SHOW in non working client:
CREATE FUNCTION `foo`() RETURNS varchar(16) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
DETERMINISTIC
BEGIN
RETURN '1 µs';
END
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
Database Collation: utf8mb4_unicode_ci
CREATE in non working client SHOW in working client:
CREATE FUNCTION `foo`() RETURNS varchar(16) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
DETERMINISTIC
BEGIN
RETURN '1 �s';
END
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
Database Collation: utf8mb4_unicode_ci
CREATE in working client SHOW in non working client:
CREATE FUNCTION `foo`() RETURNS varchar(16) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
DETERMINISTIC
BEGIN
RETURN '1 ┬Ás';
END
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
Database Collation: utf8mb4_unicode_ci
CREATE in working client SHOW in working client:
CREATE FUNCTION `foo`() RETURNS varchar(16) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
DETERMINISTIC
BEGIN
RETURN '1 µs';
END
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci
Database Collation: utf8mb4_unicode_ci
[23 Aug 2024 9:37]
MySQL Verification Team
Hi Mr. Phillips, This is not a bug. We have proven that this works just fine in a terminal that has properly defined environment. All you have to do is change settings of your terminal to accept Unicode characters, preferably utf8. This is a bug in your terminal and not in MySQL.
[26 Aug 2024 5:12]
Yoseph Phillips
You have not explained why the actual and expected are different: mysql> SELECT foo() AS 'actual', '1 µs' AS 'expected'; +--------+----------+ | actual | expected | +--------+----------+ | 1 | 1 µs | +--------+----------+ It would appear that when the MySQL client is creating functions it is using one code base / character set / collation and when it is doing regular queries it is using a different one. What you are saying would make sense if the expected wasn't showing 1 µs either. MySQL might be using WriteConsoleW in one place and not the other. We cannot go through and change the defaults of every command prompt to match the MySQL character set / collation whenever MySQL is going to be called. Please ask a developer if this is an issue or not. If my suspicion is correct then MySQL can just use WriteConsoleW to use the character set / collation of MySQL when sending the create function statement to the server.
[26 Aug 2024 7:27]
MySQL Verification Team
Please start the mysql client with --default-character-set=utf8mb4
option. It works fine on my windows.
D:\>chcp
Active code page: 437
D:\>mysql -uroot test --default-character-set=utf8mb4
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 474
Server version: 9.1.0 Source distribution
<cut>
mysql>
-> DELIMITER $$
mysql>
-> CREATE FUNCTION foo() RETURNS VARCHAR(16) DETERMINISTIC
-> BEGIN
-> RETURN '1 µs';
-> END $$
Query OK, 0 rows affected (0.00 sec)
mysql>
-> DELIMITER ;
mysql>
mysql> SELECT foo() AS 'actual', '1 µs' AS 'expected';
+--------+----------+
| actual | expected |
+--------+----------+
| 1 µs | 1 µs |
+--------+----------+
1 row in set (0.00 sec)
Then, make sure your function and DB collation is correct;
mysql> show create database test \G
*************************** 1. row ***************************
Database: test
Create Database: CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)
mysql> show create function foo \G
*************************** 1. row ***************************
Function: foo
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `foo`() RETURNS varchar(16) CHARSET utf8mb4
DETERMINISTIC
BEGIN
RETURN '1 µs';
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
[26 Aug 2024 10:14]
Yoseph Phillips
Thanks. Either using default-character-set on the command line or better still for us adding it to the my.ini under [client] works, so this is an acceptable solution. The documentation is a little unclear about this however on https://dev.mysql.com/doc/refman/8.0/en/charset-connection.html it does show the rules it is using to determine the charset. Irrespective of the default-character-set and which client this is called from all of the collation of character set variables are always the same: mysql> SHOW VARIABLES LIKE '%character_set%'; +--------------------------+---------------------------------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8mb3 | | character_sets_dir | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ | +--------------------------+---------------------------------------------------------+ 8 rows in set (0.00 sec) The terminal code base was already the same as yours so for some reason this default-character-set is required for us using the Windows Command prompt. Maybe the logic for deciding on the default character set as documented on https://dev.mysql.com/doc/refman/8.0/en/charset-connection.html could be improved as I am sure this will cause issues for others. If it simply used utf8mb4 when no default-character-set is specified then we would not have had an issue. Other MySQL documentation specified that in MySQL 8.0 the default character set is utf8mb4 which lead to some confusion here. Anyway the important thing is your solution is acceptable for us.
[16 Sep 2024 9:14]
MySQL Verification Team
Thank you , Mr. Phillips.
Description: MySQL Command Line Client showing incorrect results. When running the script below from MySQL Workbench this script works perfectly. When running it from the command line some of the units from formatTime(...) are missing. Total of Query Times: 1.08 ms Minimum Query Time: 1.08 ms Average Query Time: 1.08 ms Maximum Query Time: 1.08 ms Total of Lock Times: 265.00 Total of Query Times: 948.40 Minimum Query Time: 948.40 Average Query Time: 948.40 Maximum Query Time: 948.40 Total of Lock Times: 75.00 This is very strange. How to repeat: Run the following: DROP FUNCTION IF EXISTS formatTime; DELIMITER $$ CREATE FUNCTION formatTime(picoseconds BIGINT) RETURNS VARCHAR(16) DETERMINISTIC BEGIN RETURN CASE WHEN picoseconds >= 60000000000000 THEN time_format(sec_to_time(picoseconds / 1000000000000), '%H:%i:%s') WHEN picoseconds >= 1000000000000 THEN concat(round(picoseconds / 1000000000000, 2), ' s') WHEN picoseconds >= 1000000000 THEN concat(round(picoseconds / 1000000000, 2), ' ms') WHEN picoseconds >= 1000000 THEN concat(round(picoseconds / 1000000, 2), ' µs') WHEN picoseconds >= 1000 THEN concat(round(picoseconds / 1000, 2), ' ns') ELSE concat(picoseconds, ' ps') END; END $$ DELIMITER ; SELECT e.digest_text AS 'System Query', format(e.count_star, 0) AS 'Count', formatTime(e.sum_timer_wait) AS 'Total of Query Times', formatTime(e.min_timer_wait) AS 'Minimum Query Time', formatTime(e.avg_timer_wait) AS 'Average Query Time', formatTime(e.max_timer_wait) AS 'Maximum Query Time', formatTime(e.sum_lock_time) AS 'Total of Lock Times', format(e.sum_errors, 0) AS 'Total Errors', format(e.sum_warnings, 0) AS 'Total Warnings', format(e.sum_rows_affected, 0) AS 'Total Rows Affected', format(e.sum_rows_sent, 0) AS 'Total Rows Sent', format(e.sum_rows_examined, 0) AS 'Total Rows Examined', format(e.sum_created_tmp_disk_tables, 0) AS 'Total Created Temporary Disk Tables', format(e.sum_created_tmp_tables, 0) AS 'Total Created Temporary Tables', formatTime(e.sum_cpu_time) AS 'Total CPU Time', format(e.max_controlled_memory, 0) AS 'Maximum Controlled Memory', format(e.max_total_memory, 0) AS 'Maximum Total Memory', date_format(e.first_seen, '%d/%m/%Y %H:%i') AS 'First Seen Date & Time', date_format(e.last_seen, '%d/%m/%Y %H:%i') AS 'Last Seen Date & Time', e.query_sample_text AS 'Sample Query' FROM performance_schema.events_statements_summary_by_digest e ORDER BY e.sum_timer_wait DESC LIMIT 20\G DROP FUNCTION IF EXISTS formatTime;