Score:0

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

cn flag

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.

ua flag
Sounds like this should be a bug report: bugs.mysql.com
goodbyeera avatar
cn flag
@RickJames I already submitted a bug report, but the verification team said they can't reproduce the issue on Oracle Linux and took it as a conclusion. https://bugs.mysql.com/bug.php?id=109297
ua flag
Does it fail without `-SFX`? In which case, which option is causing the problem?
goodbyeera avatar
cn flag
@RickJames I did try `less` without any options, the problem remained the same. I should have included this in the question post. Thank you for bringing this up.
Ginnungagap avatar
gu flag
@goodbyeera, can you clarify what you're asking to solve. Is the fact than an error message is shown the issue or is there some other, more severe issue that actually breaks some functionality? There are two issues described here, the presence of an error message with little to no consequence and the malfunctioning pager when JOINing large tables. The broken pipe seems "normal" to me insofar as you're effectively disconnecting before draining the result, the pager not working seems to be the only real issue but also the less detailed one.
Score:1
jp flag

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.

I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.