MySQL 客户端 8.0 使用分页器执行大型查询时显示“fwrite:管道损坏”

MySQL 客户端 8.0 使用分页器执行大型查询时显示“fwrite:管道损坏”

我一直在使用--pager="less -SFX"MySQL 客户端的分页器(更具体一点)。以前我使用的是 MySQL 客户端/服务器 5.6 版,效果很好。最近我开始使用 MySQL 客户端/服务器 8.0 版,客户端开始fwrite: Broken pipe在大型查询结果上显示:

+------+-------------------------------------------+-----------------+-----------+-------+--------+-------------+-----------------+----------------------------+----------------------------+-------------+---------------------+----------------------------+----------------
| 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>

关于错误的一些说明:

  • 我一按下键,错误消息就打印在屏幕上q
  • 当查看查询结果的更多部分时(例如,在pagedown按下键之前先用键向下导航q),错误消息的行数会变少。如果到达查询结果的最后一页(用pagedown键一直浏览或直接用end键跳过中间的所有页面),则按下键后不会出现任何错误消息q
  • 所谓“查询结果很大”,我目前还没有找到一个精确的衡量标准。绝对不只是“大于一屏页面”。似乎不只是查询结果的行数。错误发生在 2632 行 22 列的结果上,但没有发生在 157161 行 5 列的结果上。
  • 查询结果越大,问题就越严重。我故意对JOIN大型表执行了包含多个查询的查询,但分页器似乎根本没有发挥作用。查询结果大量显示在屏幕上,没有停下来等待我的键盘输入,MySQL 客户端进程最终耗尽了机器的所有运行内存。以前,当我使用带有分页器的 5.6 客户端时,无论查询结果有多大,都不会发生这种情况。
  • 问题似乎只与客户端程序有关。我尝试使用 8.0 客户端连接到 5.6 服务器(之前与 5.6 客户端配合良好的服务器),但错误仍然弹出。
  • less当不使用这些选项时,问题仍然存在-SFX
  • 问题并不局限于特定的寻呼机程序less。我尝试使用more寻呼机,结果大致相同。
  • 有问题的 MySQL 客户端的具体版本是8.0.31
  • 我在Centos 7.9

我对类似的问题进行了大量的搜索,但至今没有找到。

答案1

当您将 MySql 客户端与寻呼机进程一起使用时,会发生“fwrite:Broken pipe”错误。

当分页器进程(在您的例子中less)在 MySQL 客户端完成输出写入之前关闭输出流时,会触发此问题。有时,当您“猛烈”停止分页器或输出达到特定限制时,您会发现此错误。

那么,该如何解决这个问题呢?有很多方法可以避免这种情况。

首先,您可以尝试-Fless寻呼机中添加标​​志,以避免在出现长流时突然中断。使用-F标志,您可以允许 MySql 客户端完成输出。

其次,你可以使用-X和标志来格式化您的输出,并且即使您的命令停止-S也可以检查输出。less

另一个有用的标志是-j从特定点开始查看输出的标志。如果你知道你的 MySQL 将呕吐一个长输出。

如果我是你,我会用不同的旗帜组合来玩less指挥

例如--pager="less -SFX -j<line_number>"

找到最适合我的情况的。

相关内容