MySQL client 8.0 showing "fwrite: Broken pipe" on large query when using a pager

Question

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 |
|    3 | a1209273-3df6-4a7f-a94e-adcebe005e46.jpg  | image/jpeg      |     76467 |   600 |    600 |           1 |              16 | 2019-11-18 07:03:56.000000 | 2019-11-18 07:03:56.000000 |       16054 | Clover::User        | 2019-11-18 07:03:56.000000 |
|    4 | 09ef833b-128c-42bb-b2f8-63a3c91e5266.jpg  | image/jpeg      |     76467 |   600 |    600 |           1 |              16 | 2019-11-18 07:04:13.000000 | 2019-11-18 07:04:13.000000 |       16054 | Clover::User        | 2019-11-18 07:04:13.000000 |
|    5 | 52744941-2e06-450b-b4d4-3e996158bc5b.jpg  | image/jpeg      |    122074 |  1440 |    866 |        NULL |              16 | 2019-11-19 08:17:21.000000 | 2019-11-19 08:17:21.000000 |        NULL | NULL                | 2019-11-19 08:17:21.000000 |
|    6 | 45d952c0-2d28-4a55-a620-02cbb1a21108.jpg  | image/jpeg      |    122074 |  1440 |    866 |        NULL |              16 | 2019-11-19 08:17:37.000000 | 2019-11-19 08:17:37.000000 |        NULL | NULL                | 2019-11-19 08:17:37.000000 |
|    7 | ee3ae126-a639-4c67-97de-36355cd2abd1.jpg  | image/jpeg      |     95989 |  1200 |   1600 |           1 |              16 | 2019-11-19 08:18:00.000000 | 2020-08-15 10:21:30.000000 |        NULL | NULL                | 2018-01-11 00:27:13.000000 |
|    8 | b2c1e2f0-28aa-4c97-9041-76227cf2bc2e.jpg  | image/jpeg      |     76467 |   600 |    600 |           1 |              16 | 2019-11-19 08:18:21.000000 | 2019-11-19 08:18:21.000000 |        NULL | NULL                | 2019-11-19 08:18:21.000000 |
|   23 | f9436ac5-1d6e-4dcf-bd38-ab26078d37ec.jpg  | image/jpeg      |     76467 |   600 |    600 |           1 |              16 | 2019-11-19 08:39:06.000000 | 2019-11-19 08:39:06.000000 |        NULL | NULL                | 2019-11-19 08:39:06.000000 |
|   24 | de272f71-c397-4059-b7f8-da62038de195.jpg  | image/jpeg      |     63100 |   960 |   1280 |        NULL |              28 | 2019-11-19 08:59:06.000000 | 2019-11-21 10:11:08.000000 |        NULL | NULL                | 2019-11-19 08:59:06.000000 |
|   25 | 043c98b5-fe90-46fa-948c-e40ef53e1d8c.jpg  | image/jpeg      |     51291 |   960 |   1280 |        NULL |              28 | 2019-11-19 08:59:16.000000 | 2019-11-19 08:59:16.000000 |        NULL | NULL                | 2019-11-19 08:59:16.000000 |
|   26 | 10481989-e723-420f-9cfc-ffb16a467b55.jpg  | image/jpeg      |     49650 |  1280 |    960 |        NULL |              28 | 2019-11-19 08:59:27.000000 | 2019-11-19 08:59:27.000000 |        NULL | NULL                | 2019-11-19 08:59:27.000000 |
|   27 | 1449a37b-a1e2-4ba2-89b2-8baa48eb9908.jpg  | image/jpeg      |     63100 |   960 |   1280 |        NULL |              28 | 2019-11-19 08:59:27.000000 | 2019-11-19 08:59:27.000000 |        NULL | NULL                | 2019-11-19 08:59:27.000000 |
|   28 | d2ab04c2-debe-46b5-a85a-c52d2c96edf9.jpg  | image/jpeg      |     51291 |   960 |   1280 |        NULL |              28 | 2019-11-19 08:59:27.000000 | 2019-11-19 08:59:27.000000 |        NULL | NULL                | 2019-11-19 08:59:27.000000 |
|   29 | 655f1911-6ec5-491e-b2f6-c328051c3b31.jpg  | image/jpeg      |     63759 |   960 |   1280 |        NULL |              28 | 2019-11-19 08:59:27.000000 | 2019-11-21 10:10:51.000000 |        NULL | NULL                | 2019-11-19 08:59:27.000000 |
|   30 | 5329c902-fe4a-4b1e-b71d-d30b2fbbb34b.JPG  | image/jpeg      |   2751465 |  5184 |   3456 |           1 |              28 | 2019-11-19 08:59:45.000000 | 2020-08-15 10:21:31.000000 |        NULL | NULL                | 2019-07-03 08:45:22.000000 |
|   31 | e03c9a83-d282-4e57-b6f4-aab09b342561.JPG  | image/jpeg      |   2697520 |  5184 |   3456 |           1 |              28 | 2019-11-19 08:59:45.000000 | 2020-08-15 10:21:31.000000 |        NULL | NULL                | 2019-07-03 08:43:40.000000 |
|   58 | 1941f326-78b9-445d-adce-69631964136e.jpg  | image/jpeg      |    122074 |  1440 |    866 |        NULL |              28 | 2019-11-19 10:41:53.000000 | 2019-11-19 10:41:53.000000 |        NULL | NULL                | 2019-11-19 10:41:53.000000 |
|   59 | ae3b1113-c4e5-4481-94de-ab492aad334f.jpg  | image/jpeg      |    122074 |  1440 |    866 |        NULL |              28 | 2019-11-19 10:45:51.000000 | 2019-11-19 10:45:51.000000 |        NULL | NULL                | 2019-11-19 10:45:51.000000 |
|   60 | 10f254a6-d1b4-45f5-af0e-450a5ecfca05.png  | image/png       |     23282 |   175 |    175 |        NULL |              28 | 2019-11-19 10:45:51.000000 | 2019-11-21 10:11:03.000000 |        NULL | NULL                | 2019-11-19 10:45:51.000000 |
|   61 | dfb94c00-d28a-404b-8c11-b3dcf3653eef.jpg  | image/jpeg      |    122074 |  1440 |    866 |        NULL |              28 | 2019-11-19 10:52:06.000000 | 2019-11-21 10:10:54.000000 |        NULL | NULL                | 2019-11-19 10:52:06.000000 |
|   62 | b2e24738-c140-45ca-89c1-2349e4244379.jpg  | image/jpeg      |     95989 |  1200 |   1600 |           1 |              28 | 2019-11-19 10:52:24.000000 | 2020-08-15 10:21:32.000000 |        NULL | NULL                | 2018-01-11 00:27:13.000000 |
|   63 | 7c049beb-2a37-4bc5-af07-ae102f70e4ce.jpg  | image/jpeg      |    122074 |  1440 |    866 |        NULL |              28 | 2019-11-19 10:52:24.000000 | 2019-11-19 10:52:24.000000 |        NULL | NULL                | 2019-11-19 10:52:24.000000 |
|   64 | 4428ff0d-2714-4c96-b649-405b60fc6833.jpg  | image/jpeg      |   4186415 |  4000 |   3000 |           1 |              25 | 2019-11-20 02:11:10.000000 | 2019-11-20 02:11:10.000000 |       15054 | Clover::User        | 2019-11-20 02:11:10.000000 |
|  146 | 483cf7ac-4a96-4cc2-bf79-4ed1002bd047.png  | image/png       |     76578 |  2481 |   2481 |        NULL |              28 | 2019-11-21 15:41:43.000000 | 2019-11-26 05:48:08.000000 |       15054 | Clover::User        | 2019-11-21 15:41:43.000000 |
|  147 | e401414e-7b2d-4588-9d53-825d5d614694.png  | image/png       |     23282 |   175 |    175 |        NULL |              28 | 2019-11-22 03:30:50.000000 | 2019-11-26 05:07:40.000000 |       16054 | Clover::User        | 2019-11-22 03:30:50.000000 |
|  148 | 4d965b81-0f0b-4967-b402-27c0be360994.jpeg | image/jpeg      |     94853 |  1080 |   1440 |        NULL |              26 | 2019-11-22 09:44:12.000000 | 2019-11-22 09:50:53.000000 |       15054 | Clover::User        | 2019-11-22 09:44:12.000000 |
|  149 | 890f70a8-2dc6-4287-ba63-d2eb821ffce6.jpeg | image/jpeg      |     95042 |  1080 |   1440 |        NULL |              26 | 2019-11-22 09:44:12.000000 | 2019-11-22 09:49:19.000000 |       15054 | Clover::User        | 2019-11-22 09:44:12.000000 |
|  150 | bc107f90-3784-4080-ab5b-24a1d514a95f.jpeg | image/jpeg      |    130836 |  1080 |   1440 |        NULL |              26 | 2019-11-22 09:44:13.000000 | 2019-11-22 09:44:13.000000 |       15054 | Clover::User        | 2019-11-22 09:44:13.000000 |
|  151 | f4c71856-64a7-424d-a012-3309bc57f2a6.jpeg | image/jpeg      |    144424 |  1080 |   1440 |        NULL |              26 | 2019-11-22 09:44:13.000000 | 2019-11-22 09:50:12.000000 |       15054 | Clover::User        | 2019-11-22 09:44:13.000000 |
|  152 | 775be35d-c0db-4c6d-8b90-90a8542c3b86.jpg  | image/jpeg      |    228831 |   960 |   1280 |           1 |              29 | 2019-11-22 09:44:42.000000 | 2019-11-22 09:48:53.000000 |       16054 | Clover::User        | 2019-11-22 09:44:42.000000 |
|  153 | 576e2e9b-227e-4472-ab90-2dbc4d6567a1.JPG  | image/jpeg      |   4576317 |  5184 |   3456 |           8 |              29 | 2019-11-22 09:44:47.000000 | 2019-11-22 09:52:37.000000 |       16054 | Clover::User        | 2019-11-20 03:00:09.000000 |
|  154 | 650af5ce-23a5-4bc1-ac20-34a6aaad4d6a.JPG  | image/jpeg      |   5773855 |  5184 |   3456 |           8 |              29 | 2019-11-22 09:44:48.000000 | 2019-11-22 09:52:38.000000 |       16054 | Clover::User        | 2019-11-20 02:59:39.000000 |
|  155 | b6b3830a-0649-4d6c-88ee-b66b85a6b76f.JPG  | image/jpeg      |   4744838 |  5184 |   3456 |           8 |              29 | 2019-11-22 09:44:49.000000 | 2019-11-22 09:52:40.000000 |       16054 | Clover::User        | 2019-11-20 02:59:31.000000 |
|  156 | 9fe39409-4604-4ce0-941d-007a9010dd2f.jpg  | image/jpeg      |    122074 |  1440 |    866 |        NULL |              29 | 2019-11-22 09:44:59.000000 | 2019-11-22 10:00:10.000000 |       16054 | Clover::User        | 2019-11-22 09:44:59.000000 |
|  157 | ed17d28c-9172-4fdb-8305-edf9f8fe049f.jpeg | image/jpeg      |   1732122 |  4032 |   3024 |           6 |              29 | 2019-11-22 09:47:23.000000 | 2019-11-22 09:47:23.000000 |       16054 | Clover::User        | 2019-11-05 04:36:39.000000 |
|  158 | f982f60f-fdcb-4666-8865-420ee545da29.jpeg | image/jpeg      |    497403 |  3648 |   2736 |           0 |              29 | 2019-11-22 09:48:03.000000 | 2019-11-22 09:48:03.000000 |       16054 | Clover::User        | 2019-09-13 15:39:54.000000 |
|  159 | 3c9eeb96-47ef-4e34-b081-4f52679bc71b.jpeg | image/jpeg      |   2047502 |  4032 |   3024 |           1 |              26 | 2019-11-22 09:51:41.000000 | 2019-11-22 09:51:52.000000 |       15054 | Clover::User        | 2019-11-21 08:11:08.000000 |
|  160 | 13ca5568-7c1d-4c80-b833-b75d7a13d44d.JPG  | image/jpeg      |   4576317 |  5184 |   3456 |           8 |              55 | 2019-11-29 08:29:07.000000 | 2019-11-29 10:01:53.000000 |       16054 | Clover::User        | 2019-11-20 03:00:09.000000 |
|  161 | ec66d057-4ab2-4886-a935-074b13446e88.jpg  | image/jpeg      |    228831 |   960 |   1280 |           1 |              55 | 2019-11-29 08:29:17.000000 | 2019-12-01 09:46:57.000000 |       16054 | Clover::User        | 2019-11-29 08:29:17.000000 |
|  162 | 3d76aed4-0f58-43f8-9825-339119bf5958.JPG  | image/jpeg      |   5773855 |  5184 |   3456 |           8 |              55 | 2019-11-29 08:29:23.000000 | 2019-12-01 09:46:40.000000 |       16054 | Clover::User        | 2019-11-20 02:59:39.000000 |
|  163 | c0485c94-f792-46af-92fe-6cd36d169b2b.JPG  | image/jpeg      |   4744838 |  5184 |   3456 |           8 |              55 | 2019-11-29 08:29:33.000000 | 2019-12-01 09:47:32.000000 |       16054 | Clover::User        | 2019-11-20 02:59:31.000000 |
|  164 | 5fd036b3-53d0-40c0-9bae-c712bd8cb983.jpg  | image/jpeg      |    228831 |   960 |   1280 |           1 |              55 | 2019-12-05 03:26:27.000000 | 2019-12-05 03:29:53.000000 |       16054 | Clover::User        | 2019-12-05 03:26:27.000000 |
|  165 | 482030f8-69f4-44fc-8b7b-d18e62bdf7f1.JPG  | image/jpeg      |   4744838 |  5184 |   3456 |           8 |              55 | 2019-12-05 03:27:51.000000 | 2019-12-05 03:29:06.000000 |       16054 | Clover::User        | 2019-11-20 02:59:31.000000 |
|  166 | bf0f6e65-4922-4407-bc4f-f2a73684aceb.JPG  | image/jpeg      |   5773855 |  5184 |   3456 |           8 |              55 | 2019-12-05 03:27:51.000000 | 2019-12-05 03:27:51.000000 |       16054 | Clover::User        | 2019-11-20 02:59:39.000000 |
|  167 | 4638d444-3bd6-4a82-a1a0-4e340d24a813.JPG  | image/jpeg      |   4576317 |  5184 |   3456 |           8 |              57 | 2019-12-05 03:35:21.000000 | 2019-12-09 02:05:20.000000 |       16054 | Clover::User        | 2019-11-20 03:00:09.000000 |
|  168 | 56a78d51-254b-4c76-88a4-df13596251c1.JPG  | image/jpeg      |   5773855 |  5184 |   3456 |           8 |              57 | 2019-12-05 03:35:21.000000 | 2019-12-09 02:05:13.000000 |       16054 | Clover::User        | 2019-11-20 02:59:39.000000 |
|  169 | 84974ca6-c000-4a0d-88f8-7693019784bc.jpg  | image/jpeg      |    228831 |   960 |   1280 |           1 |              57 | 2019-12-05 03:35:21.000000 | 2019-12-05 07:36:20.000000 |       16054 | Clover::User        | 2019-12-05 03:35:21.000000 |
|  170 | 41563fe0-5945-4c83-bd6b-ad1594109457.JPG  | image/jpeg      |   4744838 |  5184 |   3456 |           8 |              57 | 2019-12-05 03:35:22.000000 | 2019-12-06 09:42:38.000000 |       16054 | Clover::User        | 2019-11-20 02:59:31.000000 |
|  171 | 6f5c7a6d-81c0-43be-ae84-af5c256363ff.png  | image/png       |     38667 |  1300 |    551 |        NULL |               3 | 2019-12-05 05:10:38.000000 | 2019-12-05 05:11:09.000000 |       16055 | Clover::User        | 2019-12-05 05:10:38.000000 |
|  172 | 2a583861-6779-4cff-b1be-1d3f20ba58d1.jpeg | image/jpeg      |     95042 |  1080 |   1440 |        NULL |              58 | 2019-12-08 04:46:30.000000 | 2019-12-08 04:47:14.000000 |       15054 | Clover::User        | 2019-12-08 04:46:30.000000 |
|  173 | 461e86df-b3c6-4c2b-9d65-494fd9892001.jpeg | image/jpeg      |     94853 |  1080 |   1440 |        NULL |              58 | 2019-12-08 04:46:30.000000 | 2019-12-08 04:47:53.000000 |       15054 | Clover::User        | 2019-12-08 04:46:30.000000 |
|  174 | 654db564-d3ae-4d96-a9d5-23bd9ac2e444.jpeg | image/jpeg      |     83010 |  1080 |   1440 |        NULL |              58 | 2019-12-08 14:16:43.000000 | 2019-12-08 14:16:43.000000 |       15054 | Clover::User        | 2019-12-08 14:16:43.000000 |
|  175 | 91e8dddc-5ef7-4fea-876e-18aa9968c789.jpeg | image/jpeg      |     77288 |  1080 |   1440 |        NULL |              58 | 2019-12-08 14:16:43.000000 | 2019-12-08 14:16:43.000000 |       15054 | Clover::User        | 2019-12-08 14:16:43.000000 |
|  176 | ec391232-8c9d-4c77-801b-351f6509a9fd.jpeg | image/jpeg      |     85038 |  1080 |   1440 |        NULL |              58 | 2019-12-08 14:16:43.000000 | 2019-12-08 14:16:43.000000 |       15054 | Clover::User        | 2019-12-08 14:16:43.000000 |
|  177 | df130d1b-aa6e-46a2-b11d-df5fae6fe289.jpeg | image/jpeg      |     89938 |  1080 |   1440 |        NULL |              58 | 2019-12-08 14:16:43.000000 | 2019-12-08 14:16:43.000000 |       15054 | Clover::User        | 2019-12-08 14:16:43.000000 |
|  178 | 977a8600-683d-40ca-b603-6a09f7c83930.jpeg | image/jpeg      |     73886 |  1080 |   1440 |        NULL |              58 | 2019-12-08 14:16:43.000000 | 2019-12-08 14:16:43.000000 |       15054 | Clover::User        | 2019-12-08 14:16:43.000000 |
|  179 | 3049c423-dc8c-4262-8ee0-87b086c754db.jpeg | image/jpeg      |     72657 |  1080 |   1440 |        NULL |              58 | 2019-12-08 14:16:43.000000 | 2019-12-08 14:16:43.000000 |       15054 | Clover::User        | 2019-12-08 14:16:43.000000 |
|  180 | 507e8b77-5e3c-4aad-b272-3be276eee2fb.jpeg | image/jpeg      |    144424 |  1080 |   1440 |        NULL |              58 | 2019-12-08 14:17:41.000000 | 2019-12-08 14:17:41.000000 |       16065 | Clover::User        | 2019-12-08 14:17:41.000000 |
|  181 | 3dac7119-704b-4389-a387-c3d5482785aa.jpeg | image/jpeg      |    130836 |  1080 |   1440 |        NULL |              58 | 2019-12-08 14:17:41.000000 | 2019-12-08 14:17:41.000000 |       16065 | Clover::User        | 2019-12-08 14:17:41.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 remained the same when using less without the -SFX options.
• 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.

Answer 1

The "fwrite: Broken pipe" error occurs when you're using the MySql client with the pager process.

The issue triggers when the pager process (in your case less) closes the output stream before the MySQL client finish writing its output. Sometimes you find this error when you "violently" stop the pager or if your output hits a certain limit.

Now, how to fix that? There are many ways to avoid this.

First, you can try adding -F flag to your less pager to avoid any sudden interruptions if a long stream occurs. With -F flag you're allowing the MySql client to finish the output.

Secondly, you can use -X and -S flags to format your output and check the output even if your lesscommand is stopped.

Another useful flag is the -j flag to start viewing your output from a specific point. This can be helpful if you know that your MySQL will vomit a long output.

If I was in your place I would play with different combination of flags in less command

e.g. --pager="less -SFX -j<line_number>"

to find the most suitable for my case.