Bug #109297 MySQL client 8.0 showing "fwrite: Broken pipe" on large query when using a pager
Submitted: 6 Dec 2022 12:03 Modified: 6 Dec 2022 13:08
Reporter: Xinjie Wang Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Shell General / Core Client Severity:S3 (Non-critical)
Version:8.0.31 OS:CentOS (7.9.2009)
Assigned to: CPU Architecture:x86

[6 Dec 2022 12:03] Xinjie Wang
Description:
I have always been using a pager (`--pager="less -SFX"` to be more specific) for MySQL client. Previously I was on version 5.6 of MySQL client/server and it was doing great.  Recently I started to use MySQL client/server version 8.0, and the client started showing `fwrite: Broken pipe` on large query result:

```
+------+-------------------------------------------+-----------------+-----------+-------+--------+-------------+-----------------+----------------------------+----------------------------+-------------+---------------------+----------------------------+----------------
| id   | file                                      | content_type    | file_size | width | height | orientation | cln_customer_id | created_at                 | updated_at                 | uploader_id | uploader_type       | taken_at                   | customer_visibl
+------+-------------------------------------------+-----------------+-----------+-------+--------+-------------+-----------------+----------------------------+----------------------------+-------------+---------------------+----------------------------+----------------
|    1 | 9f3c8b92-9501-475e-8848-a115141a546f.png  | image/png       |     41665 |  1300 |    551 |        NULL |              15 | 2019-11-18 02:50:10.000000 | 2019-11-18 02:50:10.000000 |       16054 | Clover::User        | 2019-11-18 02:50:10.000000 |
|    2 | 655c35f2-d7a7-41a9-8814-a56d7398a5fd.png  | image/png       |     36674 |  1300 |    551 |        NULL |              15 | 2019-11-18 02:50:20.000000 | 2019-11-18 02:50:20.000000 |       16054 | Clover::User        | 2019-11-18 02:50:20.000000 |
......
......
|  182 | f750220a-8860-4f49-92df-8165501841c4.jpg  | image/jpeg      |   1768676 |  4032 |   3024 |           1 |              58 | 2019-12-08 14:21:26.000000 | 2019-12-08 14:21:26.000000 |       16065 | Clover::User        | 2019-08-07 03:14:54.000000 |
|  183 | c4a9edd6-a0a9-4403-8b55-4e90e7ee9290.jpg  | image/jpeg      |   1785633 |  4032 |   3024 |           1 |              58 | 2019-12-08 14:21:26.000000 | 2019-12-08 14:21:26.000000 |       16065 | Clover::User        | 2019-08-07 03:15:06.000000 |
fwrite: Broken pipe
fwrite: Broken pipe
fwrite: Broken pipe
2632 rows in set (0.01 sec)
mysql>
```

Some notes on the error:
* The error messages got printed on the screen as soon as I hit the `q` key.
* The number of lines of error messages becomes less when more part of the query result gets viewed (for example, by navigating down with the `pagedown` key before hitting the `q` key). If the last page of the query result is reached (either all the way through with the `pagedown` key or directly with the `end` key skipping all the pages in-between), then no error message will show up after I hit the `q` key.
* By saying "large query result", I have't found a precise measure yet. It's definitely not just "lager than one screen page". It seems it's not just about the number of rows of the query result. The error occurred on a 2632 rows result with 22 columns, but didn't occur on a 157161 rows result with 5 columns.
* The problem gets worse on even larger query result. I intentionally executed a query with several `JOIN` on large tables, and the pager seemed not doing its job at all. The query result flooded on the screen without stopping for my keyboard input and the MySQL client process eventually consumed up all running memory of the machine. This never happened before when I used the 5.6 client with a pager, no matter how large the query result was.
* The problem seems to be relevant only to the client program. I tried using the 8.0 client to connect to a 5.6 server (the one which worked well with the 5.6 client before) and the error still popped up.
* The problem is not bound to the specific pager program `less`. I tried using `more` as the pager and it was about the same.
* The exact version of the MySQL client in problem is `8.0.31`.
* I am on `Centos 7.9`.

I did quite a search for similar issues but no luck so far.

How to repeat:
Use `mysql --pager="less -SFX"` to launch the MySQL shell and execute a query expecting a large query result.  Hit the `q` key after the first page of the query result is shown.
[6 Dec 2022 13:08] MySQL Verification Team
Hi Mr. Wang,

Thank you for your bug report.

However, we are not able to repeat it. On Oracle Linux, it works just fine. Exactly as specified in our Manual.

 Do note that this option functions only in the interactive mode and never in the batch mode.

Do note also, that CentOS is not among supported platforms in our Community Server Download pages.

Can't repeat.